Write a uniqueIdentifier in SQL DB

[logatto]

Hi, I have a SQL database with several tables. The tables are related each other by an ID. The type of the ID is UNIQUEIDENTIFIER. I should read the ID of the first table and write the same ID into the second Table. Ando so on for each table.

I am using LV Database Toolkit and I have already done the reading of the ID: 5240A2A6-9453-E011-80CD-406186EDCDB2. In LV the ID is a variant, but when I try to write into the 2nd table It writes a different value: 02802108-0000-0100-0006-008400010001.

What type of data is the UNIQUEIDENTIFIER in LV? Is it a Variant?

Thanks for the support.

[alex83]

Hi,

I've attached here a comment found on ni.com:

I've been looking into ways for you to write to the unique identifier field without getting errors and I have a few possibilities. 
1) We talked about reading all records in the Unique Identifier filed and searching for the one you want to write to make sure it is not present. You mentioned that this would be too slow. One possibility to increase this speed would be to retrieve the data from the database and use Labview to search for the new record. 
Alternatively you could keep a file on your PC that contains all of the unique identifiers written to the database. This would allow you to simply open a file and check for the unique identifier, which should be faster than using the database. 
2) If you simply want to increment the previous unique identifier you could read unique identifier of the last record in the database, then parse the returned value and increment it yourself in Labview. If this convention is always used it would prevent non-unique identifiers. 
3) You mentioned an SQL command to auto generate a unique identifier. If you use the DB Tools Execute Query VI you can execute any SQL statement. You may be able to build the appropriate SQL query to auto generate the column for you. 

Alex

[logatto · Accepted solution]

Hi, I solved the problem implementing a StoredProcedure in SQL database. I call the stored procedure in labview and I pass to it the uniqueidentifier as string.

Thanks Alex.

[PJS]

The actual key to resolving this lies in fixing NI laziness.  
The problem stems from a subVI named "DB Tools Type and Dir to ADO" which has a very limited subset of ADO datatypes.  One might think that the DB toolkit would be kept current, especially with the active X components.  But alas, no we are expected to do work arounds on their products that we pay big money for.  
Essentially to make the darn toolkit work, I had to modify three VIs, so far.  This could have been done to simplify the toolkit, but in that case there should have been a beginner and advanced set of VIs.

DataTypeEnum - ActiveX Data Objects (ADO) | Microsoft Docs (last updated 2017!!!)

Also if you want to be a perfectionist, You can alter the labview datatypes to SQL datatypes, but again, the enum is not fully extensed (not sure if that is a word, but again an incomplete enum.) You use the 'To OLE Variant' sub buried in the tool kit to make those conversions.  (Credit to Dave B on that one.)

SqlDbType Enum (System.Data) | Microsoft Docs