UPDATE Database (using Schema) based on current START_DATE_TIME

[ShaneDonn]

Hi,

I  am using the TestStand Schema and a MYSQL datababse.

I have added a new table the MYSQL database that comes with TestStand. I am using the database tools to insert some local variable results into this table.

I also add the start date and time to this table, using

Str(RunState.Root.RunState.Sequence.Locals.StartDate.Month) + "-" +Str(RunState.Root.RunState.Sequence.Locals.StartDate.MonthDay) + "-" + Str(RunState.Root.RunState.Sequence.Locals.StartDate.Year) + " " + Str(RunState.Root.RunState.Sequence.Locals.StartTime.Hours) + ":" +Str(RunState.Root.RunState.Sequence.Locals.StartTime.Minutes) + ":" + Str(RunState.Root.RunState.Sequence.Locals.StartTime.Seconds)

I am trying to use the Schemato update the a colum with the parent ID, so I have it set up as a foreign key. I can get this to work when I use hardcode the the date/time in the SQP statement.

Example:

"UPDATE UUT_CTO_DATA SET ID=? ,UUT_RESULT=? where START_DATE_TIME='2009-03-20 15:13:24'"

However I would like to change this so the SQL statement using the TestSTand Variables to figure out which is the current record to update...Is this possible..All help is greatly appreciated.

Regards,

Don1.

[ShaneDonn]

Hi,

Just an update to this.

I have got this somewhat working by using the following statement in the Schema (Command Text Tab)

"UPDATE UUT_CTO_DATA SET ID=? ,UUT_RESULT=? where START_DATE_TIME= '"+Str(Logging.StartDate.Year) + "-" + Str(Logging.StartDate.Month) + "-" + Str(Logging.StartDate.MonthDay) + " " + Str(Logging.StartTime.Hours) + ":" + Str(Logging.StartTime.Minutes) + ":" + Str(Logging.StartTime.Seconds)+"'"

 My problem is it only updates thefor the first ID & UUT_RESULT columns for the first device in a batch. Anyone and ideas why this would be.??

The standard UUT_RESULT table is updating for each device..

Thanks & Regards,

Don1.

[ShaneDonn]

Speeling update:My problem is it only updates ID & UUT_RESULT columns for the first device in a batch. Anyone and ideas why this would be.??

[EadieU]

I need some clarification on the issue and your progress.  In the first poast you had said "I can get this to work when I use hardcode the the date/time in the SQP statement."  When you hardcode the date and time, does it update the ID & UUT_RESULT columns for all devices in a bath?  Are the time/date setting and the updating of the ID & UUT_RESULT columns separate issues? 

Also, which version of TestStand are you using? 

[ShaneDonn]

Hi Mark,

Thanks for the quick response,Some comments below: 

I need some clarification on the issue and your progress.  In the first poast you had said "I can get this to work when I use hardcode the the date/time in the SQP statement."  When you hardcode the date and time, does it update the ID & UUT_RESULT columns for all devices in a bath? 

• When I hardcode the update SQL statement with a specific date, Example: "UPDATE UUT_CTO_DATA SET ID=? ,UUT_RESULT=? where START_DATE_TIME= '2009-03-24 10:31:13'"   , it will update the ID & UUT_RESULT columns for all devices in a batch, (I manually delete it from the MYSQL database after each device in the batch to confirm it updates on subsquent runs)

• So do you think it now looks like my original SQL statement is somehow incorrect for all bar the first device in the batch?

• I did try the following:

  • Created a StationGlobal called Test.

  • Assigned it as follows, to ensure it matches the database record format ('2009-03-24 10:31:13')

    StationGlobals.Test =Str(RunState.Root.RunState.Sequence.Locals.StartDate.Year) + "-" +Str(RunState.Root.RunState.Sequence.Locals.StartDate.Month,"%02i") + "-" + Str(RunState.Root.RunState.Sequence.Locals.StartDate.MonthDay,"%02i") + " " + Str(RunState.Root.RunState.Sequence.Locals.StartTime.Hours,"%02i") + ":" +Str(RunState.Root.RunState.Sequence.Locals.StartTime.Minutes,"%02i") + ":" + Str(RunState.Root.RunState.Sequence.Locals.StartTime.Seconds,"%02i")

  • Change my SQL statement to "UPDATE UUT_CTO_DATA SET ID=? ,UUT_RESULT=? where START_DATE_TIME= '"+StationGlobals.Test+"'"

  • As before it updated for the first device in the batch and none of the rest.

Also, which version of TestStand are you using? 

• I am using TestStand 4.1.1

Thanks & Regards,

Don1

[EadieU]

The thread migrated to this Discussion Forum: Insert Local Data into a Schema