Sign in to follow this  
Followers 0
michaelslamet

[SOLVED] MySQL and String problem

14 posts in this topic

#1 ·  Posted (edited)

I try to create a function that read MySQL record based on a criteria and return a particular field value,

but can not find a way to pass a $sField parameter:

Func ReadDatabaseMySQL($sSearchField, $sSearchKey, $sTable, $sField, $sUserName, $sPassword, $sDatabase, $sServerName)
Local $SQLCode, $TableContents = ""
Local $searchresult = ""
local $try
$SQLInstance = _MySQLConnect($sUserName,$sPassword,$sDatabase,$sServerName)
$SQLCode = "SELECT * FROM " & $sTable & " Where " & $sSearchField & "='" & $sSearchKey & "'"
$TableContents = _Query ($SQLInstance, $SQLCode)
With $TableContents
While NOT .EOF
$try = ".Fields "& '("' & $sField & '").value'
     $searchresult = $try
     .MoveNext
WEnd
EndWith
_MySQLEnd($SQLInstance)
Return $searchresult
EndFunc

On the line 10, the correct command is something like:

$try = .Fields ("fieldname").value

But since this is a function, I need to bypass "fieldname" to whatever the value is the $sField parameter when this function get called

Try many variation including

$try = ".Fields " & '("' & $sField & '").value'

But then $try will contain string ".Fields ("fieldname").value" instead of execute that command and store the value.

Edited by michaelslamet

Share this post


Link to post
Share on other sites



$try = .Fields & '("' & $sField & '")'.value

bring a Syntax Error

Share this post


Link to post
Share on other sites

Did you try:

$try = .Fields($sField).value

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

Did you try:

$try = .Fields($sField).value

Shame on me :-( That is a correct way do do it! Thanks, jchd :-)

In my journey to solve the problem, I also found that we can use a coloum order instead of the field name:

Func ReadDatabaseMySQL($sSearchField, $sSearchKey, $sTable, $sField, $sUserName, $sPassword, $sDatabase, $sServerName)
    Local $SQLCode, $TableContents = ""
    Local $SearchResult = ""
    local $aSearch, $i = ""

    $SQLInstance = _MySQLConnect($sUserName,$sPassword,$sDatabase,$sServerName)
    $SQLCode = "SELECT * FROM " & $sTable & " Where " & $sSearchField & "='" & $sSearchKey & "'"

    $aSearch = _GetColNames($SQLInstance, $sTable)              
    $i = _ArraySearch($aSearch, $sField, 0, 0, 0, 2)        

    $TableContents = _Query ($SQLInstance, $SQLCode)
    With $TableContents
        While NOT .EOF
            $SearchResult = .Fields ($i-1).value        
            .MoveNext
        WEnd
    EndWith
    _MySQLEnd($SQLInstance)
    Return $SearchResult
EndFunc

Hope this can help somebody someday

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

You can make it much simpler but you have to decide anyway which precise value you want to return in case multiple rows satisfy the search condition.

Anyway and since you're only returning a single value, you can get rid of both the column lookup and the While loop.

Func ReadDatabaseMySQL($sSearchField, $sSearchKey, $sTable, $sField, $sUserName, $sPassword, $sDatabase, $sServerName)
Local $SQLCode, $TableContents, $SearchResult

$SQLInstance = _MySQLConnect($sUserName,$sPassword,$sDatabase,$sServerName)
$SQLCode = "SELECT " & $sSearchField &" FROM " & $sTable & " Where " & $sSearchField & "='" & StringReplace($sSearchKey, "'", "''") & "' LIMIT 1;"
$TableContents = _Query ($SQLInstance, $SQLCode)
$SearchResult = $TableContents.Fields(1).value
_MySQLEnd($SQLInstance)
Return $SearchResult
EndFunc

You may want to add an ORDER BY clause to explicitly state which row you're going to fetch the wanted value. Without an explicit ORDER BY clause, the SQL standard doesn't guarantee any precise row order, so it can be essentially random and even inconsistent accross successive runs of the same query within the same connection.

EDIT: you might need to escape schema names (tables, columns, etc.) and you need to escape string litterals. Imagine what your SQL statement would look like if you don't do the latter and I set $sSearchKey = "O'Connor"

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

Thanks a lot, jchd, for this very valuable input!

I'm going to modify my code.

About this code, I assume it will run faster because we get rid the column lookup and the while loop?

Func ReadDatabaseMySQL($sSearchField, $sSearchKey, $sTable, $sField, $sUserName, $sPassword, $sDatabase, $sServerName)
Local $SQLCode, $TableContents, $SearchResult

$SQLInstance = _MySQLConnect($sUserName,$sPassword,$sDatabase,$sServerName)
$SQLCode = "SELECT " & $sSearchField &" FROM " & $sTable & " Where " & $sSearchField & "='" & StringReplace($sSearchKey, "'", "''") & "' LIMIT 1;"
$TableContents = _Query ($SQLInstance, $SQLCode)
$SearchResult = $TableContents.Fields(1).value
_MySQLEnd($SQLInstance)
Return $SearchResult
EndFunc

Share this post


Link to post
Share on other sites

I try it, it's error with error message: "Item cannot be found in the collection corresponding to the requested name or ordinal."

Share this post


Link to post
Share on other sites

Are you sure that the select actually returns something?

You can test that condition by bracketing the assignment with If Not $TableContents.EOF Then ... EndIf


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

Yes, the select return 1 result, i'm sure about it :)

This bring that error I mention above:

$SearchResult = $TableContents.Fields(1).value

This also the same:

$SearchResult = $TableContents.Fields(2).value

This didn't result in error, but return the wrong field result:

$SearchResult = $TableContents.Fields(0).value

btw, searching by this way will faster, it that correct? because it doesn't search until EOF, or this is just another way to do it?

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Ok, got :) Instead of

$SQLCode = "SELECT " & $sSearchField &" FROM " & $sTable & " Where " & $sSearchField & "='" & StringReplace($sSearchKey, "'", "''") & "' LIMIT 1;"

and

$SearchResult = $TableContents.Fields(1).value

it should:

$SQLCode = "SELECT " & $sField &" FROM " & $sTable & " Where " & $sSearchField & "='" & StringReplace($sSearchKey, "'", "''") & "' LIMIT 1;"

and

$SearchResult = $TableContents.Fields(0).value

My question is: if it only return 1 result (and always return 1 result or doesn't return anything), will this method faster in searching?

Edited by michaelslamet

Share this post


Link to post
Share on other sites

Ha, I got confused by the parameter naming and the cryptic error message (would have expected "No such column" instead). Sorry for that.

Yes, keeping it simple will be faster, but it would be even faster to connect to the DB only once at program startup and close the connection at exit. Well, unless querying the DB is a rare occurence in which case it won't matter much if you open/close a connection only a few times during runs. OTOH it makes the query function self-contained: no need to save the connection object and have it global or passed around (it could be made Static also).

Something else: typical DB operation often implies multiple SQL statements. Don't write dozens of such functions for every use case of the DB (inserts, updates, queries) as they will become more confusing than inline code. And always sanitize your inputs to avoid SQL injection attacks!


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

jchd, thanks a lot for the advice, now my application is a lot faster! :-)

But I encounter new problem: after sometime (very short time, maybe in around 60 secs), it will error: Mysql Server has gone away.

How could we extend the time out (let say to unlimited time) until we close the SQL connection through AutoIT ?

Googling and searching through this forum bring me nothing.

Thanks :)

Share this post


Link to post
Share on other sites

I'm not a MySQL user at all so I don't know how/if one can set a longer connection timeout, should that be the issue. The issue might be inside the ADO (ODBC) layer itself, I don't know.

Try asking here with a new thread with a title describing your problem. There are some MySQL users here, but the problem has nothing to do with AutoIt AFAICT.

I advise to post in a MySQL-centric forum as well.


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

Ok, I will. Thanks a lot again :)

I learn a lot from this thread and this forum.

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
Sign in to follow this  
Followers 0