failures with mysql.au3

[tweaker]

I'm working on a very simple mysql import application that will read a txt file that is semicolon delimited and update the database. I'm getting the following error and I don't know where the error is at.

=======================================

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Tweaker\Desktop\test.au3"

C:\Program Files\AutoIt3\Include\MySQL.au3 (48) : ==> The requested action with this object has failed.:

Return $oConnectionobj.execute ($sQuery)

Return $oConnectionobj.execute ($sQuery)^ ERROR

>Exit code: 1 Time: 3.928

=======================================

My code

=======================================

#include 'MySQL.au3'

$hSql = _MySQLConnect("username","pwd","db","site")

$hTxt = FileOpen("regularprices.txt",0)

$sLine = FileReadLine($hTxt)

While @error=0

$iPos = StringInStr($sLine,";",1)

IF $iPos=0 THEN EXITLOOP

$sCode = StringLeft($sLine,$iPos-1)

$sPrice = StringStripWS(StringMid($sLine,$iPos+1),7)

_Query($hSql, "UPDATE `cscart_products` SET `list_price`="&$sPrice&" WHERE `product_code`="&$sCode)

$sId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`="&$sCode)

_Query($hSql, "UPDATE `cacart_product_prices` SET `price`="&$sPrice&" WHERE `product_id`="&$sId)

$sLine = FileReadLine($hTxt)

WEnd

FileClose($hTxt)

_MySQLEnd($hSql)

=========================

I am able to successfully connect to the database with my ODBC connection in windows xp.

Thomas

[DjDeep00]

See here...

[tweaker]

I'm sorry I don't understand the reference here.

I think it's my code but I don't see where. Here's something I did.

#include 'MySQL.au3'
$hSql = _MySQLConnect("username","pwd","db","site")
_Query($sql, "UPDATE `cscart_products` SET `list_price`='.80' WHERE `product_code`='DIS7'")
_MySQLEnd($hSql)

This works fine.

Thomas

Edited June 25, 2008 by tweaker

[PsaltyDS]

I'm sorry I don't understand the reference here.

He means you should add a COM Error Handler. Look it up in the COM/OBJ reference section of the help file.

I think it's my code but I don't see where. Here's something I did.

#include 'MySQL.au3'
$hSql = _MySQLConnect("username","pwd","db","site")
_Query($sql, "UPDATE `cscart_products` SET `list_price`='.80' WHERE `product_code`='DIS7'")
_MySQLEnd($hSql)

This works fine.

Thomas

Here you have '.80' with single quotes around it. In your original script you have $sPrice. Are the single quotes present in the $sPrice string? Perhaps this:

_Query($hSql, "UPDATE `cscart_products` SET `list_price`='" & $sPrice & "' WHERE `product_code`='" & $sCode & "'")

[tweaker]

MsgBox(0,"Code",$sCode)

    $sId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`='" & $sCode & "'")

MsgBox(0,"Result",$sId)

First box shows me DIS7.

Second box is blank.

In phpMyAdmin I can do the following:

SELECT `product_id` FROM `cscart_products` WHERE `product_code` = 'DIS7'

and the returned results are:

product_id

6794

There is something wrong with this code but I don't see where it is.

Edited June 25, 2008 by tweaker

[PsaltyDS]

MsgBox(0,"Code",$sCode)

    $sId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`='" & $sCode & "'")

MsgBox(0,"Result",$sId)

First box shows me DIS7.

Second box is blank.

In phpMyAdmin I can do the following:

SELECT `product_id` FROM `cscart_products` WHERE `product_code` = 'DIS7'

and the returned results are:

product_id

6794

There is something wrong with this code but I don't see where it is.

Are you sure the returned value is not in an array? Perhaps this will shed some light:

#include <Array.au3>

; ...the rest of the script

MsgBox(0, "$sCode", $sCode)
$sId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`='" & $sCode & "'")
$iErrSav = @error
If IsArray($sId) Then
    _ArrayDisplay($sId, "$sId is an array, @error = " & $iErrSav)
Else
    MsgBox(0, "Result", "$sId type: " & VarGetType($sId) & "  $sId = " & $sId & "  @error = " & $iErrSav)
EndIf

[tweaker]

Dialogue box reads:

$sId type: Object $sID = @error = 0

[PsaltyDS]

Dialogue box reads:

$sId type: Object $sID = @error = 0

Aha! You got a collection object, or more likely, a record set back.

[tweaker]

Aha! You got a collection object, or more likely, a record set back.

What does that mean? I can't get the information?

[PsaltyDS]

What does that mean? I can't get the information?

Not at all, you just have a little more research to do.

[tweaker]

That's great news but I'm afraid I don't understand the link you gave me. I feel like I'm staring right at the answer but I don't know how to intrepret what I'm reading to solve the problem.

[PsaltyDS]

That's great news but I'm afraid I don't understand the link you gave me. I feel like I'm staring right at the answer but I don't know how to intrepret what I'm reading to solve the problem.

A record set is an object. It has methods and properties like any other object. You will have to learn to use COM Objects to use the record set you get back from the MySQL query. A translation of weaponx's code from my earlier link to your situation in this topic:

; ...the rest of the script

MsgBox(0, "$sCode", $sCode)
$oId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`='" & $sCode & "'")
$iErrSav = @error
If IsObj($oId) Then
    $sId = "Results:  "
    With $oId
    ; For each row
        While Not .EOF
        ; For each field in the row
            For $oField In .Fields
            ; Add it to the string
                $sId &= $oField.value & ", "
            Next
        WEnd
    EndWith
    $sId = StringTrimRight($sId, 2); Remove trailing ", "
    MsgBox(64, "Results", $sId)
Else
    MsgBox(0, "Result", "$sId type: " & VarGetType($sId) & "  $sId = " & $sId & "  @error = " & $iErrSav)
EndIf

[tweaker]

Whoh, if I'm reading this code right, I have to query my entire table for each record before returning the result? I have over 16000 items in that table I'm trying to update with this script. I just tried to run the modified code and it gave me the error:

Error allocating memory.

Edited June 26, 2008 by tweaker

[tweaker]

Anybody have any idea how to fix this?

[PsaltyDS]

Whoh, if I'm reading this code right, I have to query my entire table for each record before returning the result? I have over 16000 items in that table I'm trying to update with this script. I just tried to run the modified code and it gave me the error:

Error allocating memory.

I don't have MySQL to test against, specifically, but I think it ran away just because I forgot the .MoveNext, so it was reading the first record over and over until it overflowed. Try it like this:

; ...the rest of the script

MsgBox(0, "$sCode", $sCode)
$oId = _Query($hSql, "SELECT `product_id` FROM `cscart_products` WHERE `product_code`='" & $sCode & "'")
$iErrSav = @error
If IsObj($oId) Then
    $sId = "Results:  "
    With $oId
        MsgBox(64, "Record Set", "There are " & .RecordCount & " records, with " & .Fields.Count & " fields")
    ; For each row
        While Not .EOF
        ; For each field in the row
            For $oField In .Fields
            ; Add it to the string
                $sId &= $oField.value & ", "
            Next
            $sId = StringTrimRight($sId, 2) & @CRLF; Remove trailing ", " and add new line
            .MoveNext; Move to the next record
        WEnd
        .Close; Close the record set
    EndWith
    MsgBox(64, "Results", $sId)
Else
    MsgBox(0, "Result", "$sId type: " & VarGetType($sId) & "  $sId = " & $sId & "  @error = " & $iErrSav)
EndIf

For more on using record sets, try this: ADO Recordset Object

[tweaker]

Perfect! Thank you for the help with this.