Has anyone used Crystal Reports 2008 with Spiceworks or SQLite?

[elsenorjose]

I am working on an existing report and need to change the table joins from INNER to LEFT OUTER but I get a strange error when I do so. First, I get a 'Failed to retrieve data from the database' error. Then I get a subsequent error saying 'Failed to retrieve data from the database. Details: HY000:unrecognized token: "{" )1_ [Database Vendor Code: 1]

It's not the common 'If tables are already linked then the join type cannot be changed.' error and it seems like it's database or application specific but no one at Spiceworks can figure out why I'm getting that error. They use SQLite as their database and to be honest, I'm not at all familiar with it.

Thanks.

 

[Turkbear]

Hi,
That error ( from SqlLIte's site) means:

#define SQLITE_ERROR 1 /* SQL error or missing database */

So, can you post the SQL generated by that report and any selection criteria formulas in it?

It looks like it may be a syntax issue.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fisheromacse]

make sure you have the correct SQLLite driver.

I was getting similar errors w/spiceworks until i changed the driver used.

Did you see this:

http://community.spiceworks.com/topic/12676

 

[fisheromacse]

FWIW
i just double-checked on my system, and if you use the SQLLite ODBC Driver or the SQLLite ODBC(UTF-8)Driver there are connectivity issues.
When i use the SQLLite3 ODBC Driver, all connects correctly.

 

[elsenorjose]

Thanks guys. Here's the SQL the report generates when using the LEFT OUTER JOIN:

 SELECT DISTINCT "devices1"."name", "devices1"."memory", "disks1"."free_space", "disks1"."size", "devices1"."bios_date", "devices1"."processor_type", "devices1"."service_pack_major_version", "devices1"."site_name", "devices1"."device_type", "devices1"."ip_address", "devices1"."serial_number", "devices1"."type", "devices1"."server_name", "devices1"."manufacturer", "devices1"."current_user"
 FROM   {oj "devices" "devices1" LEFT OUTER JOIN "disks" "disks1" ON "devices1"."id"="disks1"."id"}
 WHERE  ("devices1"."device_type"='Desktop' OR "devices1"."device_type"='Laptop' OR "devices1"."device_type"='Server') AND "devices1"."site_name"='ANSIRH Site'
 ORDER BY "devices1"."site_name"

I notice that just before the 'devices' table in the join, there's 'oj'. Also, the entire JOIN clause is surrounded in curly braces. That's not something I've seen before in SQL Server or other databases. If that's being sent down to the DB, it would make sense that it can't parse the brace and the error makes some more sense. Any ideas on a workaround?

I'll try changing the ODBC driver in the meantime.

 

[elsenorjose]

A quick update. We ARE using the SQLLite3 ODBC Driver so that's not the issue. Thanks.

 

[Turkbear]

Hi,
That syntax does look odd and not like any in the SqlLite docs ( that I can find):

http://www.sqlite.org/optoverview.html#joins

Can youi post the SQL if a 'standard' inner join is used in this report?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[elsenorjose]

Yep. The SQL is fine if we use INNER JOIN:

 SELECT DISTINCT "devices1"."name", "devices1"."memory", "disks1"."free_space", "disks1"."size", "devices1"."bios_date", "devices1"."processor_type", "devices1"."service_pack_major_version", "devices1"."site_name", "devices1"."device_type", "devices1"."ip_address", "devices1"."serial_number", "devices1"."type", "devices1"."server_name", "devices1"."manufacturer", "devices1"."current_user"
 FROM   "devices" "devices1" INNER JOIN "disks" "disks1" ON "devices1"."id"="disks1"."id"
 WHERE  ("devices1"."device_type"='Desktop' OR "devices1"."device_type"='Laptop' OR "devices1"."device_type"='Server') AND "devices1"."site_name"='ANSIRH Site'
 ORDER BY "devices1"."site_name"

So for some reason, ODBC driver or Crystal related, the LEFT OUTER JOIN is generating that weird syntax.

 

[elsenorjose]

Interesting. So in reading that post (thank you by the way) it seems to indicate that using ON in the JOIN clause isn't supported to begin with. That, in addition to the weird syntax, is probably contributing to the errors.

 

[fisheromacse]

FWIW - i was able to duplicate your error by adding a field from each of 2 tables and changing my linking to Outer Join.
so you are not alone!

 

[Turkbear]

Hi,
Some ODBC drivers when used with CR cannot do OUTER joins correctly and the syntax that the combination produces is often bad..Usually though, the OUTER join options are greyed out in CR when it is not supported.

You might check with SqLite about any wire-protocol or native drivers that may work better.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[elsenorjose]

Fish, thanks. Glad to see I'm not completely losing my mind.

Turk, I'll look into that. Can you clarify 'wire-protocol'? That's a new term for me.

 

[Turkbear]

Hi,
Wire-Protocol ODBC drivers make a direct connection to the database with far less middle-layer translation, so they are often as good as the native connection used by the database client itself.

They are mostly limited to major database engines but see if any here will work:

http://web.datadirect.com/products/odbc/index.html

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I wonder whether part of the problem is that you are adding selection criteria on the right hand table in the where clause (which effectively undoes a left outer). What happens if you leave in the left outer join without using any selection criteria?

Do you have the option of using a command with SQL Lite? You could then move the selection criteria to the From clause (if you get the report working once you have removed the selection criteria).

-LB

 

[elsenorjose]

Hi LB,

I'm not using any selection on the right table. Everything in the WHERE clause is from the left table, 'devices'.

I'm going to try creating a view in the database and just use that as the data source, thus eliminating any possibility of weird SQL syntax generated by CR. Hopefully that works.

 

[lbass]

I'm sorry, I misread the query--wasn't paying close enough attention.

-LB

 

[elsenorjose]

No worries. I went ahead and created a view and replaced the tables and the report is working fine now. Thanks again everyone for some great resources for SQLite. Not my favorite of databases by any means, but at least now I can navigate some of the pitfalls.