Jump to content
mrtweaver

SQL Timing

Recommended Posts

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

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×