[SOLVED] MySQL and String problem

[michaelslamet]

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 January 4, 2013 by michaelslamet

[michaelslamet]

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

bring a Syntax Error

[jchd]

Did you try:

$try = .Fields($sField).value

[michaelslamet]

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

[jchd]

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 January 2, 2013 by jchd

[michaelslamet]

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

[michaelslamet]

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

[jchd]

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

[michaelslamet]

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?

[michaelslamet]

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 January 3, 2013 by michaelslamet

[jchd]

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!

[michaelslamet]

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

[jchd]

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.

[michaelslamet]

Ok, I will. Thanks a lot again

I learn a lot from this thread and this forum.