DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

SQL_Result limited to 80 characters

Hi,
 
I have a test program that needs to dump a lot of data to a mySQL db. In one case, I need to dump an array of floating point numbers to the db (over 2000 points). The fastest way to do this for me is to place all these values into a comma delimited string and then using one ODBC transaction, dump it to the db.
 
When attempting to retrieve this data in Diadem I have difficulties. Because this string is very long, SQL_Result will only return a fraction of the data. Is there any way I can get all the data from this very long string?
 
Regards,
S
 
 
0 Kudos
Message 1 of 8
(3,987 Views)
Hi Sean,

Please go to the DIAdem help "F1" and search for SQL_BindVar, this should allow you to extract all the data from the SQL select command you have run and bind it to a channel in the data portal so you can then access the test data.

Regards
JamesC
NIUK and Ireland

0 Kudos
Message 2 of 8
(3,972 Views)

Hi There,

I'm still a little lost, the GPI variable used by SQL_BindVar can only store a Maximum of 255 characters. How do I get the entire contents of that column which is a string of approx 10000 characters.

Regards,

S

0 Kudos
Message 3 of 8
(3,962 Views)

Just to clarify the previous post, I should have said 'field' instead of 'column'.

 

0 Kudos
Message 4 of 8
(3,953 Views)
Hi Sean,

From what I understand the 255 character limit is a limit on the size of the variable name, not the amount of data that is called back.
However there are afew other options for you, the first is very similar to the method already suggested and so might be prone to the same issues.
1) SQL_BindTextChn

or

2) You can use a base ADO connection
In DIAdem please open the help system go to Index and type in ADO, you can then read how to connect directly to an ADO object.

Regards
JamesC
NIUK and Ireland

0 Kudos
Message 5 of 8
(3,946 Views)

Hi James,

I am still not having any success extracting this data. Could you type me up an example so I can insert it into my code and see if it is something silly I am doing here please?

Basically all the code needs to do is to extract a string value value from a single field called 'data' from a table called 'fftx'. I expect that this would be only a few short lines.

Regards,

S.

0 Kudos
Message 6 of 8
(3,922 Views)
Hi Sean,

I will get you some example code, but I have just reinstalled everything on muy machine, so I will need to reinstall MySQL and the ODBC driver, so it might take some time for me to get the example to you.

Regards
JamesC
NIUK and Ireland

0 Kudos
Message 7 of 8
(3,919 Views)
Hi Sean,

You will need to use an ADO connection rather than the VBS connection method.

An example script is below:

' create instances of required ADO objects
Set conn = CreateObject ("ADODB.Connection")
Set rs= CreateObject ("ADODB.Recordset")


conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=grschumi3; DATABASE=test; UID=tester; PWD=testing"

conn.open
' send SQL query
rs.Open "Select CSV From strings", conn

' create msgbox text with filed object
do until rs.EOF
    for each x in rs.Fields
       response =  x.value
       msgbox len(response) & " characters in response string"&vbcrlf& response
    next
    rs.MoveNext
loop


'destroy objets
rs.close
conn.close

Regards
JamesC
NIUK and Ireland

0 Kudos
Message 8 of 8
(3,828 Views)