tweaker Posted June 25, 2008 Share Posted June 25, 2008 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 Link to comment Share on other sites More sharing options...
DjDeep00 Posted June 25, 2008 Share Posted June 25, 2008 See here... Link to comment Share on other sites More sharing options...
tweaker Posted June 25, 2008 Author Share Posted June 25, 2008 (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 June 25, 2008 by tweaker Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 25, 2008 Share Posted June 25, 2008 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 25, 2008 Author Share Posted June 25, 2008 (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 June 25, 2008 by tweaker Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 25, 2008 Share Posted June 25, 2008 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 25, 2008 Author Share Posted June 25, 2008 Dialogue box reads: $sId type: Object $sID = @error = 0 Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 25, 2008 Share Posted June 25, 2008 Dialogue box reads:$sId type: Object $sID = @error = 0Aha! 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 25, 2008 Author Share Posted June 25, 2008 Aha! You got a collection object, or more likely, a record set back.What does that mean? I can't get the information? Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 26, 2008 Share Posted June 26, 2008 What does that mean? I can't get the information?Not at all, you just have a little more research to do. 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 26, 2008 Author Share Posted June 26, 2008 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. Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 26, 2008 Share Posted June 26, 2008 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 26, 2008 Author Share Posted June 26, 2008 (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 June 26, 2008 by tweaker Link to comment Share on other sites More sharing options...
tweaker Posted June 26, 2008 Author Share Posted June 26, 2008 Anybody have any idea how to fix this? Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 27, 2008 Share Posted June 27, 2008 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 Link to comment Share on other sites More sharing options...
tweaker Posted June 27, 2008 Author Share Posted June 27, 2008 Perfect! Thank you for the help with this. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now