DB Connectivity Toolkit Insert Data: Error -2147352567

[murchak]

Thanks.

I got the same error code.

Attached is my little VI that creates the table (Called Ceate_Table.vi).

I log in to the database, type select * from mytable, and I can see it has been created with 4 columns A, B, C, D and rows are empty.

This means that Labview communicates with our Oracle database, it is able to create table, but for some strange reason, it cannot insert data

into it.

Could it be data type matching problem?

Thanks,

[SteveChandler]

Hmm.. It looks like you created the table with all columns being strings. The VI I posted uses all strings. Do you have access to the server logs? The column names in the VI I posted were a, b, c and d - lower case. I am not sure that matters and I think you would get a different error.

I tested your vi and my vi with PostgreSQL and it works.

[murchak]

I dropped the table, and recreated it with lower case letters. No difference.

Then I created again with upper case letters, and turned your VI column names into upper case. No difference.

I am surprises as to why NI does not have a database of error codes. I remember reading about this yesterday, somebody

else had the same issue. I think it was due to datatype mismatch. But clearly my table is all strings and like you said

so is your VI.

I guess for now, maybe I can play with the advanced VIs (involing SQL queries) to see if I can get it to work.

I can also try to create a local MS Access database on my drive, and see if I can create a table and dump some data in it.

This quite puzzling to me.

Thanks for all your help and time!!

[SteveChandler]

Maybe it has something to do with encoding. I mean something like an ODBC Unicode setting in your driver. I am completely unfamiliar with Oracle.

[PhillipBrooks]

The error code returned is actually from ADO. You see error -2147352567 in the VI error cluster.

Convert this to hex (0x80020009). Search for this using Google and it might give you some paths to follow.

I loaded your VI and noticed that converting the array to a cluster results in a cluster with nine items. To create a cluster with the correct number of elements for your table, you must right click on the Array to Cluster function and set the number of elements. The error 0x80020009 may be that the underlying function has reached the last field in the record and you are attempting to add more (9 elements instead of 4).

[SteveChandler]

Since this works on my system I can not troubleshoot it. I am using PostgreSQL, LabVIEW 2010SP1, Windows 7.

But the error is coming from a property node in Conn Get Properties agrument 3 (DefaultDatase). Interestingly there is a comment under that property node that reads "DefaultDatabase is not supported (fires an error) for all providers.  mspersist is one of these...maybe check provider to filter out these cases."

The reason that you are able to create a table is because the get properties vi is called from create parameterized query which is not used by create table.

I don't know anything about Oracle or how to connect to it but I suspect that you might be able to work around this by looking at your connection settings. Something to do with default database?

You can try to manually create the query but as you can see it gets complex fast. You have to take into account the datatypes, escape characters apropriately, avoid SQL injection attacks all while maintaining your sanity. But you can try this just for a test.

VI also attached in LV 8.0 format.

[SteveChandler]

One more thing. The open connection VI is polymorphic. You wired a path to it but you can also wire a string constant and specify the DSN. Create a system DSN using the Windows control panel and put the name you give it in the constant. It is a shot in the dark but something worth trying. I know that people must be using Oracle with the toolkit.

[murchak]

I double-clicked on the DB Tools Insert Data.vi and looked at the block diagram.

The block diagram shows another VI which is DB Tools Build Insert Query. I put an indicator on the the insert query

coming out of this vi and it shows insert into mytable(A,B,C,D) values(?,?,?,?)

The data indicator shows values.

Why is it not reading values?

[SteveChandler]

The question marks will be replaced with the values in the data variant by the DB Tools module before the insert is sent to the database. The module will do things like escape unsafe characters and put quotes around strings.

Did the VI I posted above work?

[murchak]

I think I found where the error happens. Please open up my ppt. and see if this provides a clue.

Thanks,