SQL Timing

[mrtweaver]

I have been working on a script that will read in data via the TCP and then populate a table in mysql. If need be i can post the script here but was wondering if there are speed limitations when sending the information from Autoit to mySQL? Right now mySql is set at defaults out of box. i run my script without the write to mysql the data changes roughly 3 times a sec. add in the write to mysql, sometimes it will write one record then shut down with error 3 problem with sql insert script or something close to that. sometimes it will write 3 records then shut down with same error. if you require script i can post it. thanks for reading and have a great weekend

[jchd]

Without more precise details or actual code it'll be pretty hard to give you useful advice.

[mrtweaver]

Ok here is the file, as i said i thought i took a screen shot of it but i must not have all i can remember is at the begining it said error 3: then there was a comment about the way in which the insert was formulated.

camera test 2.au3

[jchd]

I'm not a MySQL user myself but here's what I can tell you. Knowing the content of the error message would greatly help.

A/ know your data! Is your received data string or binary type? Read help under TCPRecv and look at when it switches to binary mode by itself.

B/ escape strings! If you receive string type, then escape it before insert: double every single quotes found in the string. $s = StringReplace($s, "'", "''") will do.

C/ if you get binary data, then use MySQL syntax to include binary in the SQL statement.

D/ check the DB schema: contrary to SQLite, MySQL is strongly typed and won't accept binary in a text column.

E/ beware encoding! Text encoding can also be an issue: declared text column encoding must match with send text encoding.

F/ fix the bug in the line where you concatenate inserts statements: you don't want to concatenate them! And if you really want to do that (?), then add a semicolumn at the end of the statement.

[mrtweaver]

I'm not a MySQL user myself but here's what I can tell you. Knowing the content of the error message would greatly help.

A/ know your data! Is your received data string or binary type? Read help under TCPRecv and look at when it switches to binary mode by itself.

B/ escape strings! If you receive string type, then escape it before insert: double every single quotes found in the string. $s = StringReplace($s, "'", "''") will do.

C/ if you get binary data, then use MySQL syntax to include binary in the SQL statement.

D/ check the DB schema: contrary to SQLite, MySQL is strongly typed and won't accept binary in a text column.

E/ beware encoding! Text encoding can also be an issue: declared text column encoding must match with send text encoding.

F/ fix the bug in the line where you concatenate inserts statements: you don't want to concatenate them! And if you really want to do that (?), then add a semicolumn at the end of the statement.

Ok lets cover these one at a time. First off I am fairly new to programming in Autoit and have very limited experience in other languages as well.

So to answer A: I know for a fact that the output from the camera is indeed a string. In the camera you set the output up with the command PSTR which says output string. So I know the output is def being put out as a string.

We will jump to D: In my schema the table value is set as varchar.

Now we will handle B: explain more about escaping strings. That command you gave do i replace s with my variable recv?

As for C and the part of A regarding binary mode, i read the tcp function and it said that just sitting there listening to the port will return binary but i didnt fully understand the binary section because it says using binary is prefered. So would it be best to just bring this in as binary then do the binary to string function? What are the benefits of one to the other?

And as for F are you saying the & concats the line? if so what would be a proper format?

Thanks and have a great day

[jchd]

Escaping literal string in SQL statements is necessary to guard against syntax errors. If you want to store a variable $name = "O'Reilly" this is what happens:

"insert into mytable (thename) values ('" & $name & "')";

Which translates into:
insert into mytable (thename) values 'O'Reilly';

The internal single quote is taken as the closing quote of the litteral. So you need to gaurd against that by using:
"insert into mytable (thename) values ('" &  StringReplace($name, "'", "''") & "')";
giving:

insert into mytable (thename) values 'O''Reilly';
The DB engine recognizes the internal dual single quotes when parsing the statement and then correctly stores the name O'Reilly.

About point F, line 101 of the code posted is:

$sMySqlStatement &= "INSERT INTO readinfo (readid) VALUES ('" & $recv & "')"

this concatenates (&=) the right hand literal to the variable. At round #2, your statement looks like this:

INSERT INTO readinfo (readid) VALUES ('123')INSERT INTO readinfo (readid) VALUES ('456')

The syntax is invalid after ('123').

Change the line 101 to: $sMySqlStatement = "INSERT INTO readinfo (readid) VALUES ('" & StringReplace($recv, "'", "''") & "');"

Or, better, create a function:

Func _Escape($s)
    Return StringReplace($s, "'", "''")
EndFunc

 

Edited June 27, 2015 by jchd