SQL query generated by Insert Data/Update Data vi's?

[Arkady_V]

Hello!

I'm new to LabView, now evaluating it as my company's management is considering buying several developer's licences. I'm now playing with the Database Connectivity toolkit and trying to insert/update datasets. Everything is good with simple tables, but when I'm trying to run relatively complex queries there are often mistakes that LabView diagnoses just as "syntax error in Update statement" or something like this with no specific details.

Is it possible to read the actual SQL queries that are generated by these vi's or is there some (preferably free) tool to monitor all SQL queries invoked by my LabView program?

[mikeporter]

Yes, and step 1 is to not waste any money on the database connectivity toolkit. I will post a follow up tomorrow morning when I get into work that will be helpful to you

Mike...

[Arkady_V]

Thank you Mike, I love the idea to save 1K on the toolkit, and my bosses definitely will love it too...

[Yamaeda]

You should be able to open the VI's and see the SQL generated, or, if you want to handle it yourself, you can use Execute query and send your own sql command.

/Y

[Arkady_V]

>You should be able to open the VI's and see the SQL generated

Unfortunately the evaluation version doesn't allow me to open vi's from the Database palette.

[mikeporter · Accepted solution]

Here is the link I promised last night. It includes a discussion of the issues with the DCT and why it is unnecessary. Also included is a link to some drivers that let you do everything you need to do and are free - and a brief tutorial about accessing databases.

Mike...

[Mark_Yedinak]

I would also recommend that you use stored procedures to interface with your database. Maintaining your code is much easier and the stored procedures will execute faster than large conplex SQL statements.

[Arkady_V]

Thank you Mike.

I tried your drivers and found them working, and since there are no automatically generated SQL statements there is no issue with debugging queries. But now I have an issue connecting to my old local dBase format database.

I have a bunch of .dbf files saved in C:\SOFTWARE\DATABASE\BurnIn directory. All the files are in Visual dBase7 format. The file names are somewhat long, all of them are a little longer than 8 characters. The DCT has no trouble connecting to these tables.

I also created three new dbf files: test1.dbf, test2.dbf and test12345678.dbf. test1.dbf is in the same Visual dBase7 format when test2.dbf and test 12345678.dbf are in dBase IV format.

For your drivers I'm trying to use the following ADO connection string:

 Driver={Driver do Microsoft dBase (*.dbf)};DriverID=277;Dbq=C:\SOFTWARE\DATABASW\BurnIn;

When I'm trying to use the Create and Read Recordset.vi with my database files, only test2.dbf can be opened with no problems.

I receive error message: "External table is not in the expected format. -- LabVIEW:  (Hex 0x80004005)" with my dBase7 tables.

I receive different error message: " The Microsoft Jet database engine could not find the object 'test12345678'.  Make sure the object exists and that you spell its name and the path name correctly" with my dBase IV table of the long name.

I suspect that the problem is with the connection string, as the Database Connectivity Toolkit has no problem while using ODBC.

Can you suggest something?

I can convert all my tables into dBase IV format and rename them to be in 8+3 filename format, but isn't it weird?

[mikeporter]

The ODBC connection that you used for the DCT will work with my drivers as well because down inside the DCT it used the same connection mechanism as my drivers (ADO). ADO can connect to a variety of drivers, including ODBC. Normally you don't want to go through ODBC due to performance issues, but on old or poorly-supported database formats sometimes it's necessary.

Mike...

[Arkady_V]

Mike,

if I want to use ODBC with your drivers, what the connection string would look like? In the DCT I just use the Data Source name ('BurnIn' to be specific) as a Connection Information, but it doesn't work that way with your drivers.