Exporting large numbers of data channels to a database

[SolarTech]

I am trying to export a large number of data channels to a database.  So far I have been using the following type of commands to write to a database...

Z1 = "File_Name,Engine_Name,Ticket_Number,Sub_Test_Number,Test_Date"

Z2 = "Trim_Start,Trim_End"

Z5 = "'"&str(FName)&"'"&","&"'"&str(Engine)&"'"&","&"'"&str(TicketNo)&"'"&","&"'"&str(SubTest)&"'"&","&"'"&str(Date1)&"'"

Z6 = "'"&str(RTT((Chnval(C,"/Start Time")),"hh:nn:ss"))&"'"&","&"'"&str(RTT((Chnval(C,"/End Time")),"hh:nn:ss"))&"'"

T9 = "INSERT INTO " & SQL_Table & " (" & Z1 & "," & Z2 & ")" & " VALUES (" & Z5 & "," & Z6 & ")"

Call SQL_ExecDirect("T9")

This seems to be working fine in initial testing.  It creates a new record in the access database and then populates the specified columns with the specified values.  I am having trouble expanding this to cover enough channels.  I have approximately 400 channels.  I wrote a little cycle to build these text strings for all the data channels and it seems to crash diadem.  My assumption right now is that the length of the text strings are too large?  When I try to break up the number columns into smaller pieces and then add them to the database one at a time, it works but it creates a new record for each "chunk."  Any ideas of the best way to approach this?  Thanks!

Karl

[Perry_S]

Dear Karl,

Can you specify how large a chunk is? Is the example representative of one chunk? I see that you are using the insert command which creates a new record every time it is initiated. You will need to use the Update command in order to make changes to an existing record. I hope that this helps.

Regards,

Perry S.

[SolarTech]

Hello Perry,

The above does represent a "chunk" since it is a text string containing just several of the 400 channels that I need it to contain.  If I make it long enough to contain all of the channels things go very wrong.  I have looked at the update command and feel that it is the best way to go but I have been unable to make it work.  To use the update I need to be able to specify which record I want to update and I am not sure how to specify the last updated record so that I can keep updating the same record until all the channels have been updated in the record.  I have thought about adding a control channel just for this purpose but that seems clumsy.  Thanks.   

Karl

[Perry_S]

Hi Solar Tech,

To use the update command you have to specify a unique identifier to identify the entry that you want to change.

The update command would then look something like:

UPDATE tableName SET Field1= 'Joe', Field2 = 'Bloggs' Field3 = 12345
WHERE Field4 = 7

This will update Field1 to Joe, Field2 to Bloggs and Field3 to 12345 in every row of the table where Field 4 contains the value 7.

So the WHERE qualifier would be the thing to use. I hope that this helps.

Regards,

Perry S.

[Brad_Turpin]

Hey Karl,

If I remember correctly, the SQL_Execute("T9") approach is limited to 10,000 characters.  You should be able to avoid that limitation by switching to the ADO approach, since it would accept a variant variable of subtype=string that has no character limitation.  Of course, your data base may have some sort of character limitation for one SQL transaction, and DIAdem can't help you there.  Seems like you ought to be able to chunk it by rows instead of columns.  Are you wanting to insert BLOBs that contain the full channel value array in each column's field, or are you wanting each channel value in a data table field?

Brad Turpin

DIAdem Product Support Enigineer

National Instruments