Database connectivity toolkit delay prevents missing records?

[Jon_S.]

The advantage of this VI is that it was designed to insert objects into databases. The DB Tool Execute Query.vi was designed to execute a query even though you can enter any SQL command into the query VI.  Try using DB Tools Insert Data.vi.  Does using this VI work better?  If you are stuck on using the query VI look at the cache input and experiment with it.

[kc64]

Joe--

Thanks for the tip but this VI does not fit the design pattern I am using.

kc64

[kc64]

Mike--

I decided to try out this approach to see how it works.  I have not succeeded yet.  My database execution returns an error -2147217900 that states:

"Error -2147217900 occurred at Conn Execute.vi->Database Query.vi->Database.vi

Possible reason(s):
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ''. in Conn Execute.vi->Database Query.vi->Database.vi"

I was going to paste the SQL in here but it doesn't paste because of all the strange characters.  Upon examination, I see no reason why the error was raised since there is a matching closing ' after  every string.  Any ideas?  I am also curious what happens if the string happens to have a ' as part of the data.

Thanks,

kc64

[mikeporter]

The procss for inserting a record containing BLOB fields is a bit different because (as you have found out) the binary data will contain byte-values that the SQL command processor interprets as control characters. Check out this thread, it contains a link for downloading a set of database drivers that I created. In particular look for a VI called Insert BLOB Data.vi. It illustrates the process, which basically consists of creating an ADO recordset and then using that recordset to transfer the data to the database. With this technique you don't run into the "special character" problem because the data is actually transfered as a variant.

When you have downloaded this I can walk you through the process of how to use this VI.

Mike...

[kc64]

Mike--

OK, thanks for the help.  I have downloaded the library (nicely done) and integrated the VIs in place of the NI DCT VIs.  The existing methods that I had developed with DCT work fine with your ADO toolkit.  Now about the BLOB insertion method, it is not obvious to me what to do next.  Could you provide an example that shows the use of the VI that inserts the BLOB?

Thanks,

kc64

[kc64]

Mike--

I've been exploring the Insert BLOB Data VI.  I have tried to feed it with a variant that I generated using various test step details and some waveforms.  When the Create New Record in Recordset VI is executed, the following error is raised.

"Error -2147217887 occurred at * Insert BLOB data.vi
>> SELECT TestDetailsID, TestSummaryID, TestTypeID, TestCriteriaID, StepID, SetID, Value1, Value2, Value3, Pass
FROM TestDetails
WHERE 0=1;
* Create New Record in Recordset.vi
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. in Create New Record
<EVENTCLASS:Database Exception>
"

Any ideas?

I am using LV 8.5.1 and SQLServer 2005.

Thanks,

kc64

[mikeporter]

The query looks right. The only thing I can see right off hand is your use of the ODBC driver. Modify your connection to use the Microsoft OLE DB Provider for SQL Server. It's the one I always use to connect to SQL Server. In addition, make sure that the data input to the BLOB insert VI consists of an array of 10 variants - one for each data item. The elements in the array should also be in the same order as the columns specified in the select statement.

Mike...

[kc64]

Mike--

I am using the SQL Server driver for this database.  I also watched execution of the code during the attempted write.  Your Create New Record in Recordset vi checks to make sure the number of variant items is matched before proceeding and my code passes that test.  I have tried it with the PK both included and not included and still have problems.  If I change the Recordset Parameters to adLockPessimistic, the process completes without error but leaves no data in the database.  I'm not sure what to try next.

kc64

[kc64]

If I don't include the PK in the SQL statement (which seems to be the correct method), I get a different error:

* Insert BLOB data.vi
>> SELECT TestSummaryID, TestTypeID, TestCriteriaID, StepID, SetID, Value1, Value2, Value3, Pass
FROM TestDetails
WHERE 0=1;
* Create New Record in Recordset.vi
Exception occured in ADODB.Recordset: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

[kc64]

Mike--

In your last post you mentioned the ODBC vs SQL Server driver.  I didn't realize that this was driven by the UDL file I am using to store my connection string.  I have reconfigured the connection string to use the SQL Server driver.  This has not caused any changes, however.  I still get the same error in the preceeding post.

kc64