Sign in to follow this  
Followers 0
tweaker

failures with mysql.au3

16 posts in this topic

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

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

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 by tweaker

Share this post


Link to post
Share on other sites

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 & "'")

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by tweaker

Share this post


Link to post
Share on other sites

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Dialogue box reads:

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

Share this post


Link to post
Share on other sites

Dialogue box reads:

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

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by tweaker

Share this post


Link to post
Share on other sites

Anybody have any idea how to fix this?

Share this post


Link to post
Share on other sites

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Perfect! Thank you for the help with this.

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