Foreign Key to Custom MySQL Table GUID is Null

[hansf]

I found a LabVIEW VI in the NI Developer Zone that generates 38 character GUIDs via a DLL call.  I will try this today and post back the results.

Hans

[hansf]

Sorry, it took me awhile to get a chance to try out my workaround.  Here's the VI that I found in the LabVIEW Developer Zone to generate a GUID using ole32.dll.  Thanks to Enrique Vargas at vargas@jyestudio.com.  I tweaked the VI to remove nulls from the 38 Character GUID String.

I tried the workaround that involves using Create GUID.vi to generate a GUID that is written to the step_testcase table's primary key column (that part works), storing that generated GUID value in a global (works of course), and later manually writing that same GUID value to the foreign key column in the step_verifyimage table - that part results in the following MySQL error at database write time:

An error occurred calling 'LogResults' in 'ITSDBLog' of 'zNI TestStand Database Logging'

An error occurred executing a statement.

Schema: Robot Test System MySQL Insert.

Statement: STEP_VERIFYIMAGE.

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Cannot add or update a child row: a foreign key constraint fails (`robot teststand db/step_verifyimage`,

CONSTRAINT `STEP_VERIFYIMAGE_FK` FOREIGN KEY (`STEP_TESTCASE`) REFERENCES `step_testcase` (`GENERATED_ID`))

Description: [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Cannot add or update a child row: a foreign key constraint fails

(`robot teststand db/step_verifyimage`, CONSTRAINT `STEP_VERIFYIMAGE_FK` FOREIGN KEY (`STEP_TESTCASE`) REFERENCES `step_testcase` (`GENERATED_ID`))

Number: -2147467259

NativeError: 1452

SQLState: S1000

Reported by: Microsoft OLE DB Provider for ODBC Drivers

Source: TSDBLog

It looks to me that MySQL doesn't want to allow a table's foreign key values to be written manually. I tried editing the step_verifyimage table to allow that, but I couldn't find a way.

Any ideas?

Thanks,

Hans

[Scott_Richardson]

hansf -
It appears that the error is complaining that the constraint requirements are not being met, typically because the value in the foreign key that you are writing is not found in the primary key that you are referencing. One option to verify this is to remove the constraint, retry you test, and see if the referring row can be now be written. If yes, look at the data in the primary key and foreign key fields to see if they are identical.

[ebenek]

Hi,

You wrote as following,

"I tried the workaround that involves using Create GUID.vi to generate a GUID that is written to the step_testcase table's primary key column (that part works)" 

Could you tell me how did you do it? when we tried to write the data created in vi as primary key in teststand (database options), we get the attached error.

[TylerC1123]

Hi Ebenek,

As this thread is almost 11 years old you may want to start your own thread to be able to get more visibility.

That being said looking at the error you are getting it looks to be a SQL Server error not something inherent to TestStand, as it was "Reported by: Microsoft OLE DB Provider for SQL Server."

[ebenek]

In my database, I have three tables, all of these tables have Instance_ID (GUID type) and all of them must be same. I have established a vi to write this GUID variable (created with the vi in the discussion above) to database. This GUID variable was successfully written to database (in three tables). Database functions in TestStand and Labview are different? I do not get any casting error in Labview.