eleria Posted May 6, 2008 Share Posted May 6, 2008 (edited) Hi I've downloaded a UDF that supposedly handles sql querries and stuff but it gives errors in the actual UDF, and it is WAY beyond my understanding. CODE #include-once #cs Function Name: _MySQLConnect Description: Initiate a connection to a MySQL database. Parameter(s): $username - The username to connect to the database with. $password - The password to connect to the database with. $Database - Database to connect to. $server - The server your database is on. $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}" Requirement(s): Autoit 3 with COM support Return Value(s): On success returns the connection object for subsequent functions. On failure returns 0 and sets @error @Error = 1 Error opening connection @Error = 2 MySQL ODBC Driver not installed. Author(s): cdkid #ce Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 5.1 Driver}", $iPort=3306) Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v) If @error or $val = "" Then SetError(2) Return 0 EndIf $ObjConn = ObjCreate("ADODB.Connection") $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort) If @error Then SetError(1) Return 0 Else Return $ObjConn EndIf EndFunc ;==>_MySQLConnect #cs Function name: _Query Description: Send a query to the database Parameter(s): $oConnectionObj - As returned by _MySQLConnect. $query - The query to execute Return Value(s):on success returns the query result. On failure returns 0 and sets @error to 1 Requirement(s):Autoit3 with COM support Author(s): cdid #ce Func _Query($oConnectionObj, $sQuery) If IsObj($oConnectionObj) Then Return $oConnectionobj.execute ($sQuery) EndIf If @error Then SetError(1) Return 0 EndIf EndFunc ;==>_Query #cs Function name: _MySQLEnd Description: Closes the database connection (see notes!) Parameter(s): $oConnectionObj - The connection object as returned by _MySQLConnect() Requirement(s):Autoit 3 with COM support Return Value(s):on success returns 1. On failure returns 0 and sets @error to 1 Author(s): cdkid #ce Func _MySQLEnd($oConnectionObj) If IsObj($oConnectionObj) Then $oConnectionObj.close Return 1 Else SetError(1) Return 0 EndIf EndFunc ;==>_MySQLEnd #cs Function name: _AddRecord Description: Adds a record to the specified table Note(s): to add to multiple columns use an array with one blank element at the end as the $sColumn, and $value parameter Parameter(s): $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in $row - The row to put the record in. $value - The value to put into the row $vValue - OPTIONAL default will be default for the column (will not work with array, see notes) Requirement(s): Autoit 3 with COM support Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1. Author(s): cdkid #ce Func _AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "") If IsObj($oConnectionObj) Then $query = "INSERT INTO " & $sTable & " (" If IsArray($vRow) Then For $i = 0 To UBound($vRow, 1) - 1 If $i > 0 And $i <> UBound($vRow, 1) - 1 Then $query = $query & "," & $vRow[$i] & "" ElseIf $i = UBound($vRow, 1) - 1 And $vRow[$i] <> "" Then $query = $query & "," & $vRow[$i] & ") VALUES(" ElseIf $i = 0 Then $query = $query & "" & $vRow[$i] & "" ElseIf $vRow[$i] = "" Then $query = $query & ") VALUES(" EndIf Next EndIf If Not IsArray($vRow) And Not IsArray($vValue) And Not IsInt($vValue) Then $oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')") return 1 ElseIf IsInt($vValue) And Not IsArray($vRow) And Not IsArray($vValue) Then $oconnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")") return 1 EndIf If IsArray($vValue) Then For $i = 0 To UBound($vValue, 1) - 1 If $i > 0 And $i <> UBound($vValue, 1) - 1 And Not IsInt($vValue[$i]) Then $query = $query & ",'" & $vValue[$i] & "'" ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" And Not IsInt($vValue[$i]) Then $query = $query & ",'" & $vValue[$i] & "');" ElseIf $i = 0 And Not IsInt($vValue[$i]) Then $query = $query & "'" & $vValue[$i] & "'" ElseIf $vValue[$i] = "" Then $query = $query & ");" ElseIf IsInt($vValue[$i]) And $vValue[$i] <> "" Then $query = $query & "," & $vValue[$i] EndIf Next EndIf If StringRight($query, 2) <> ");" Then $query = $query & ");" EndIf $oconnectionobj.execute ($query) EndIf If Not IsObj($oConnectionObj) Then SetError(2) Return 0 EndIf If @error And IsObj($oConnectionObj) Then Return 0 SetError(1) Else Return 1 EndIf EndFunc ;==>_AddRecord #cs Function name: _DeleteRecord Description: Deletes a record from the specified table Parameter(s): $oConnectionObj - As returned by _MySQLConnect. $sTable - The table to delete from. $sColumn - The column to check value (see the example in the next post) $vRecordVal - The value to check in $sColumn (see example). $iLimit (optional) - the max number of record to delete if multiple match the criteria (default 1) Return Value(s): On success returns 1. If there $oConnectionObj is not an object returns 0 and sets @error to 1. If there are any other errors returns 0 and sets @error to 2 Requirement(s): Autoit 3 with COM support #ce Func _DeleteRecord ($oConnectionObj, $sTable, $sColumn, $vRecordVal, $iLimit = 1) If IsObj($oConnectionObj) And Not IsInt($vRecordVal) Then $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vRecordVal & "' LIMIT " & $iLimit & ";") ElseIf IsInt($vRecordVal) Then $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = " & $vRecordVal & " LIMIT " & $iLimit & ";") If Not @error Then Return 1 ElseIf Not IsObj($oConnectionObj) Then SetError(1) Return 0 ElseIf @error And IsObj($oConnectionObj) Then SetError(2) Return 0 EndIf EndIf EndFunc ;==>_DeleteRecord #cs Function name: _CreateTable() Description: Creates a table Parameters: $oConnectionObj - as returned by _MySQLConnect, $sTbl - The name of the table to create, $sPrimeKey - The name of the primary key column. $keytype - The datatype of the primary key (default is integer), $sNotNull - "yes" = must be filled out whenever a record is added "no" does not need to be filled out ("yes" default). $keyautoinc - "yes" = Auto incrememnts "no" = does not. $sType - The table type (default is InnoDB) Requirements: Autoit V3 with COM support Return value(s): on success returns 1 on failure sets @error to 1 and returns 0 Author: cdkid #ce Func _CreateTable($oConnectionObj, $sTbl, $sPrimeKey, $keytype = "INTEGER", $sNotNull = "yes", $keyautoinc = "yes", $sType = "InnoDB") If IsObj($oConnectionObj) And Not @error Then $str = "CREATE TABLE " & $sTbl & " " & "(" & $sPrimeKey & " " & $keytype & " UNSIGNED" If $sNotNull = "yes" Then $str = $str & " NOT NULL" EndIf If $keyautoinc = "yes" Then $str = $str & " AUTO_INCREMENT," EndIf $str = $str & " PRIMARY KEY (" & $sPrimeKey & " )" & " ) " & "TYPE = " & $sType & ";" $oConnectionObj.execute ($str) Return 1 ElseIf @error Then Return 0 SetError(1) EndIf EndFunc ;==>_CreateTable #cs Function Name: _CreateColumn Description: Creates a column in the given table Requirements: AutoitV3 with COM support Parameters: $oConnectionObj - as returned by _MySQLConnect. $sTable - the name of the table to add the column to. $sAllowNull - if 'yes' then does not add 'NOT NULL' to the SQL statement (default 'yes') $sDataType - The data type of the column default('VARCHAR(45)'). $sAutoInc - if 'yes' adds 'AUTO_INCREMENT' to the MySQL Statement (for use with Integer types) default('no'). $sUnsigned - if 'yes' adds 'UNSIGNED' to the MySQL statement. default('no') $vDefault - the default value of the column default('') Author: cdkid #ce Func _CreateColumn($oConnectionObj, $sTable, $sColumn, $sAllowNull = "no", $sDataType = "VARCHAR(45)", $sAutoInc = "no", $sUnsigned = "no", $vDefault = '') If IsObj($oConnectionObj) And Not @error Then $str = "ALTER TABLE `" & $sTable & "` ADD COLUMN `" & $sColumn & "` " & $sDataType & " " If $sAllowNull = "yes" Then $str = $str & "NOT NULL " EndIf If $sAutoInc = 'yes' Then $str = $str & "AUTO_INCREMENT " EndIf If $sUnsigned = 'yes' Then $str = $str & "UNSIGNED " EndIf $str = $str & "DEFAULT '" & $vDefault & "';" $oConnectionObj.execute ($str) Return 1 Else SetError(1) Return 0 EndIf EndFunc ;==>_CreateColumn #cs Function Name: _DropCol() Description: Delete a column from the given table Requirements: AutoitV3 with COM support Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - The name of the table to delete the column from $sColumn - THe name of the column to delete Author: cdkid #ce Func _DropCol($oConnectionObj, $sTable, $sColumn) If IsObj($oConnectionObj) & Not @error Then $oConnectionObj.execute ("ALTER TABLE " & $sTable & " DROP COLUMN " & $sColumn & ";") Return 1 ElseIf @error Then SetError(1) Return 0 EndIf EndFunc ;==>_DropCol #cs Function Name: _DropTbl() Description: Deletes a table from the database Requirements: AutoitV3 with COM support Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to delete Author: cdkid #ce Func _DropTbl($oConnectionObj, $sTable) If IsObj($oConnectionObj) And Not @error Then $oConnectionObj.execute ("DROP TABLE " & $sTable & ";") Return 1 Else SetError(1) Return 0 EndIf EndFunc ;==>_DropTbl #cs Function name: _CountRecords() Description: Get the number of records in the specified column Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table that the column is in $value - If not = "" then it is put in the select statement in the WHERE clause (default "") Return value(s): On success returns the number of records. On failure sets @error to 1 and returns 0 Author: cdkid #ce Func _CountRecords($oConnectionObj, $sTable, $sColumn, $vValue = '') If IsObj($oConnectionObj) And Not @error Then If $sColumn <> "" And $vValue <> "" And Not IsInt($vValue) Then $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vValue & "'" ElseIf $sColumn <> "" And $vValue = '' And Not IsInt($vValue) Then $constr = "SELECT " & $sColumn & " FROM " & $sTable ElseIf IsInt($vValue) And $sColumn <> '' And $vValue <> '' Then $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = " & $vValue EndIf $sql2 = ObjCreate("ADODB.Recordset") $sql2.cursorlocation = 3 $sql2.open ($constr, $oConnectionObj) With $sql2 $ret = .recordcount EndWith $sql2.close Return $ret Else SetError(1) Return 0 EndIf EndFunc ;==>_CountRecords #cs Function name: _CountTables Description: Counts the number of tables in the database Parameter(s): $oConnectionObj - As returned by _MySQLConnect Return value(s): if error - returns 0 and sets @error to 1. on success returns the number of tables in the database Author: cdkid #ce Func _CountTables($oConnectionObj) If IsObj($oConnectionObj) Then $quer = $oConnectionObj.execute ("SHOW TABLES;") $i = 0 With $quer While Not .EOF $i = $i + 1 .MoveNext WEnd EndWith Return $i EndIf If @error Then SetError(1) Return 0 EndIf EndFunc ;==>_CountTables #cs Function name: _GetColNames Description: Get's the names of all columns in a specified table Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to get the column names from Return values: On success returns an array where $array[0] is the number of elements in the array and all the rest are column names. On failure returns 0 and sets @error to 1 Author: cdkid #ce Func _GetColNames($oConnectionObj, $sTable) If IsObj($oConnectionObj) And Not @error Then Dim $ret[1], $rs $rs = $oConnectionObj.execute ("SHOW COLUMNS FROM " & $sTable & ";") With $rs While Not .EOF ReDim $ret[uBound($ret, 1) + 1] $ret[uBound($ret, 1) - 1] = $rs.Fields (0).Value .MoveNext WEnd EndWith $ret[0] = UBound($ret, 1) - 1 Return $ret EndIf If @error Then Return 0 SetError(1) EndIf EndFunc ;==>_GetColNames #cs Function name: _GetTblNames Description: Gets the names of all tables in the database Parameters: $oConnectionObj - As returned by _MySQLConnect Return value(s): On success returns an array where $array[0] is the number of tables and $array[n] is the nth table's name on failure - returns 0 and sets @error to 1 Author: cdkid #ce Func _GetTblNames($oConnectionObj) If IsObj($oConnectionObj) Then Dim $ret[1] $quer = $oConnectionObj.execute ("SHOW TABLES;") With $quer While Not .eof ReDim $ret[uBound($ret, 1) + 1] $ret[uBound($ret, 1) - 1] = .fields (0).value .movenext WEnd EndWith $ret[0] = UBound($ret, 1) - 1 Return $ret EndIf EndFunc ;==>_GetTblNames #cs Function name: _GetColVals Description: Gets all of the values of a specified column in a specified table Parameters: $oConnectionObj - As returned by _MySQLConnect(), $sTable - the table that the column is in $sColumn - the column to get values from. Return value(s): On success returns an array where $array[0] is the number of values and $array[n] is the Nth value On failure sets @error to 1 and returns 0 Author: cdkid #ce Func _GetColVals($oConnectionObj, $sTable, $sColumn) If IsObj($oConnectionObj) Then Dim $ret[1] $quer = $oConnectionObj.execute ("SELECT " & $sColumn & " FROM " & $sTable & ";") With $quer While Not .EOF ReDim $ret[uBound($ret, 1) + 1] $ret[uBound($ret, 1) - 1] = .Fields (0).value .MoveNext WEnd EndWith $ret[0] = UBound($ret, 1) - 1 Return $ret EndIf EndFunc ;==>_GetColVals #cs Function name: _GetColCount Description: Gets the number of columns in the specified table Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table to count the columns in Return Value(s): On success returns the number of columns in the table. On failure returns -1 and sets @error to 1 Author: cdkid #ce Func _GetColCount($oConnectionObj, $sTable) If IsObj($oConnectionObj) Then $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable) With $quer $i = 0 While Not .eof $i = $i + 1 .movenext WEnd EndWith Return $i EndIf If @error Then Return -1 SetError(1) EndIf EndFunc ;==>_GetColCount #cs Function name: _GetColType Description: Gets the DATA TYPE of the specified column Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table that the column is in. $sColumn - the column to retrieve the data type from. Return value(s): On success returns the data type of the column. On failure returns 0 and sets @error to 1 Author: cdkid #ce Func _GetColType($oConnectionObj, $sTable, $sColumn) If IsObj($oConnectionObj) Then $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable) With $quer $i = 0 While Not .eof If .fields (0).value = $sColumn Then $ret = .fields (1).value EndIf .MoveNext WEnd EndWith Return $ret EndIf If @error Then Return 0 SetError(1) EndIf EndFunc ;==>_GetColType #cs Function: _GetDBNames Description: Get a count and list of all databases on current server. Parameters: $oConObj - As returned by _MySQLConnect Return Value(s): Success - An array where $array[0] is the number of databases and $array[n] is the nth database name. Failure - -1 and sets @error to 1 Author: cdkid #ce Func _GetDBNames($conobj) If IsObj($conobj) Then Local $arr[1], $m $m = $conobj.Execute ("SHOW DATABASES;") With $m While Not .eof ReDim $arr[uBound($arr, 1) + 1] $arr[uBound($arr, 1) - 1] = .Fields (0).Value .MoveNext WEnd EndWith $arr[0] = UBound($arr, 1) - 1 Return $arr Else SetError(1) Return -1 EndIf EndFunc ;==>_GetDBNames #cs Function: _ChangeCon Description: Change your connection string Parameters: $oConnectionObj As returned by _MySQLConnect $username OPTIONAL: the new username to use If omitted, the same username will be used. $password OPTIONAL: the new password to use If omitted, the same password will be used. $database OPTIONAL: the new database to connect to If omitted, the same database will be used. $driver OPTIONAL: the new driver to use If omitted, the MySQL ODBC 3.51 DRIVER will be used. $server OPTIONAL: the new server to connect to If omitted, the same server will be used. $iPort OPTIONAL: the new port to be used to connect if omitted, the default port (3306) will be used Return Value: On success, a new connection object for use with subsequent functions. On failure, -1 and sets @error to 1 Author: cdkid #ce Func _ChangeCon($oConnectionObj, $username = "", $password = "", $database = "", $driver = "", $server = "", $iPort = 0) Local $constr, $db, $usn, $pwd, $svr If IsObj($oConnectionObj) Then $constr = $oConnectionObj.connectionstring $constr = StringReplace($constr, 'Provider=MSDASQL.1;Extended Properties="', '') $constr = StringSplit($constr, ";") For $i = 1 To $constr[0] If StringLeft($constr[$i], 3) = "UID" Then If $username <> "" Then $usn = $username Else $usn = StringMid($constr[$i], 5) EndIf $usn = StringTrimRight($usn, 1) EndIf If StringLeft($constr[$i], 3) = "PWD" Then If $password <> "" Then $pwd = $password Else $pwd = StringMid($constr[$i], 5) EndIf EndIf If StringLeft($constr[$i], 8) = "DATABASE" Then If $database <> "" Then $db = $database Else $db = StringMid($constr[$i], 10) EndIf EndIf If StringLeft($constr[$i], 6) = "SERVER" Then If $server <> "" Then $svr = $server Else $svr = StringMid($constr[$i], 8) EndIf EndIf If StringLeft($constr[$i], 6) = "DRIVER" Then If $driver <> "" Then $dvr = $driver Else $dvr = "{MySQL ODBC 3.51 DRIVER}" EndIf EndIf If StringLeft($constr[$i], 4) = "PORT" Then if $iport <> 0 Then $port = $iport Else $port = 3306 EndIf EndIf Next $oConnectionObj.close $oConnectionObj.Open ("DATABASE=" & $db & ";DRIVER=" & $dvr & ";UID=" & $usn & ";PWD=" & $pwd & ";SERVER=" & $svr & ";PORT=" & $port & ";") Return $oConnectionObj Else SetError(1) Return -1 EndIf EndFunc ;==>_ChangeCon If someone could help me fix that, or if someone had another way of connecting and sending querries to a mysql server I'd really appreciate : / Edited May 6, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 You have to install the MySQL ODBC Connector.http://dev.mysql.com/downloads/connector/odbc/5.1.html Link to comment Share on other sites More sharing options...
eleria Posted May 6, 2008 Author Share Posted May 6, 2008 (edited) Alright this is my source code CODE#include <GuiConstantsEx.au3>#include <Mysql.au3>#include <ButtonConstants.au3>#include <EditConstants.au3>#include <StaticConstants.au3>Dim $header, $auth, $file, $verDim $geo, $file, $x1, $y1, $x2, $y2Dim $x11, $y11, $x21, $y21Dim $querConst $ini = "Options.ini"Const $sServer = Iniread($ini, "SQL", "Server", 1)Const $sDatabase = Iniread($ini, "SQL", "Database", 1)Const $fAuthMode = Iniread($ini, "SQL", "AuthorisationMode", 1)Const $sUsername = Iniread($ini, "SQL", "Username", 1)Const $sPassword = Iniread($ini, "SQL", "Password", 1)Const $sDriver = Iniread($ini, "SQL", "Driver", 1)Const $sQuery = Iniread($ini, "SQL", "Query", 1)Const $rgxpsearch = Iniread($ini, "Regexp", "Regexpsearch", 1)$header = FileRead("header")If (@error = 1) Then MsgBox(0, "ERROR", "Could not open header file!") ExitEndIf#Region ### START Koda GUI section ### Form=C:\Program Files\AutoIt3\l2j\elah lite2.kxf$Form2 = GUICreate("Eleria's Lazy Ass Helper", 135, 223, 344, 250)$Group1 = GUICtrlCreateGroup("SQL_Creator", 1, 0, 133, 189, $BS_CENTER)$geox_ = GUICtrlCreateInput("", 32, 41, 33, 21, BitOR($ES_CENTER,$ES_AUTOHSCROLL,$ES_NUMBER))$geoy_ = GUICtrlCreateInput("", 67, 41, 33, 21, BitOR($ES_CENTER,$ES_AUTOHSCROLL,$ES_NUMBER))$auth_ = GUICtrlCreateInput("", 44, 80, 45, 21)$poi_ = GUICtrlCreateInput("", 8, 120, 58, 21)$file_ = GUICtrlCreateInput("", 68, 120, 58, 21)$com_ = GUICtrlCreateInput("", 19, 159, 97, 21)$Label1 = GUICtrlCreateLabel("Author", 50, 65, 35, 13)$Label3 = GUICtrlCreateLabel("P.O.I.", 20, 105, 31, 13, $SS_CENTER)$Label4 = GUICtrlCreateLabel("Filename", 73, 105, 46, 13, $SS_CENTER)$Label11 = GUICtrlCreateLabel("Query Comments", 28, 144, 84, 13)$Label5 = GUICtrlCreateLabel("Geo_y", 68, 25, 35, 13)$Label6 = GUICtrlCreateLabel("Geo_x", 30, 25, 35, 13)$Create = GUICtrlCreateButton("Create", 42, 194, 53, 25, $BS_DEFPUSHBUTTON)GUISetState(@SW_SHOW)#EndRegion ### END Koda GUI section ###While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $create $header = FileRead("header") $auth = GUICtrlRead($auth_) $file = GUICtrlRead($file_) $poi = GUICtrlRead($poi_) $com = GUICtrlRead($com_) $geox = GUICtrlRead($geox_) $geoy = GUICtrlRead($geoy_) $ver = @YEAR & "." & @MON & "." & @MDAY $x1 = ($geox - 20) * 32768 $y1 = ($geoy - 18) * 32768 $x2 = ($geox - 19) * 32768 $y2 = ($geoy - 17) * 32768 $quer = StringReplace(StringReplace(StringReplace(StringReplace($quer, "%x1%", $x1), "%y1%", $y1), "%x2%", $x2), "%y2%", $y2) $header = StringReplace(StringReplace(StringReplace(StringReplace(StringReplace(StringReplace(StringReplace(StringReplace($header, "%ver%", $ver), "%auth%", $auth), "%x1%", $x1), "%y1%", $y1), "%x2%", $x2), "%y2%", $y2), "%poi%", $poi), "%geo%", $geox & "_" & $geoy) $oconn = _MYSQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver) msgbox(0,"",$squery) $quer = _Query($oConn, $sQuery) _MYSQLEnd($oConn) Msgbox(0,"",$quer & $oConn) $quer = StringRegExpReplace($quer, $rgxpsearch, "('" & $com & "', $1),") $quer = StringTrimRight($quer, 1) $quer &= ";" $header = StringReplace($header, "%quer%", $quer) If FileExists($file & ".sql") Then FileDelete($file & ".sql") EndIf FileWrite($file & ".sql", $header) EndSwitchWEndAnd I'm getting an error in the mysql.au3 atCODEFunc _Query($oConnectionObj, $sQuery) If IsObj($oConnectionObj) Then Return $oConnectionobj.execute ($sQuery) EndIf If @error Then SetError(1) Return 0 EndIfMore precisely at the lines: If IsObj($oConnectionObj) Then Return $oConnectionobj.execute ($sQuery)"The requested action with this object has failed"Even a simple Select * from spawnlist; doesn't work Edited May 6, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 Were you able to test the MySQL connection successfully through Control Panel > Administrative Tools > Data Sources (ODBC) > System DSN? Once that test is successful then you try a simple script. #include "mysql.au3" $SQLOBJ = _MySQLConnect("username","password","host","database") Switch @ERROR Case 0 MsgBox(0,"","Connection successful") Case 1 MsgBox(0,"","Error","Error opening connection") Case 2 MsgBox(0,"","MySQL ODBC Driver not installed.") EndSwitch Link to comment Share on other sites More sharing options...
eleria Posted May 6, 2008 Author Share Posted May 6, 2008 (edited) Connection Successful !Actually the querry SELECT * FROM spawnlist; give a blank result in a msgbox Edited May 6, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 Ok so take it a step further. expandcollapse popup#include "mysql.au3" $SQLOBJ = _MySQLConnect("username","password","host","database") Switch @ERROR Case 0 MsgBox(0,"","Connection successful") Case 1 MsgBox(0,"","Error","Error opening connection") Case 2 MsgBox(0,"","MySQL ODBC Driver not installed.") EndSwitch ;Retrieve array of all table names in database $aTables = _GetTblNames($SQLOBJ) If NOT @ERROR Then ;Dump array to console and show table count ConsoleWrite("########## TABLES (" & _CountTables($SQLOBJ) & ") ##########" & @CRLF) For $X = 1 to $aTables[0] ConsoleWrite($aTables[$X] & @CRLF) Next Else MsgBox(0,"Error","Something Went Wrong!") EndIf ConsoleWrite(@CRLF) ;Retrieve array of all column names in table $ret2 = _GetColNames($SQLOBJ, "spawnlist") If NOT @ERROR Then ;Dump array to console and show column count ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "spawnlist") & ")##########" & @CRLF) For $X = 1 to $ret2[0] ConsoleWrite($ret2[$X] & @CRLF) Next Else MsgBox(0,"Error","Something Went Wrong!") EndIf Link to comment Share on other sites More sharing options...
eleria Posted May 6, 2008 Author Share Posted May 6, 2008 btw you got your arguments for _MysqlConnect wrong, this is the corrected version that crashes in line 27 in mysql.au3 CODE#include "mysql.au3" $SQLOBJ = _MySQLConnect("username","password","database","host", "{MySQL ODBC 5.1 Driver}") Switch @ERROR Case 0 MsgBox(0,"","Connection successful") Case 1 MsgBox(0,"","Error","Error opening connection") Case 2 MsgBox(0,"","MySQL ODBC Driver not installed.") EndSwitch ;Retrieve array of all table names in database $aTables = _GetTblNames($SQLOBJ) If NOT @ERROR Then ;Dump array to console and show table count ConsoleWrite("########## TABLES (" & _CountTables($SQLOBJ) & ") ##########" & @CRLF) For $X = 1 to $aTables[0] ConsoleWrite($aTables[$X] & @CRLF) Next Else MsgBox(0,"Error","Something Went Wrong!") EndIf ConsoleWrite(@CRLF) ;Retrieve array of all column names in table $ret2 = _GetColNames($SQLOBJ, "spawnlist") If NOT @ERROR Then ;Dump array to console and show column count ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "spawnlist") & ")##########" & @CRLF) For $X = 1 to $ret2[0] ConsoleWrite($ret2[$X] & @CRLF) Next Else MsgBox(0,"Error","Something Went Wrong!") EndIf Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 I think we are using 2 different versions of the UDF. You probably got the file from here:http://www.autoitscript.com/forum/index.ph...14&hl=mysqlThere is a little link in that first post that says:I have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:kan2.sytes.net/publicsvn/mysql now at revision: I use the version from here:http://kan2.sytes.net/publicsvn/mysql/I believe the order of the parameters changed in that newer version. Link to comment Share on other sites More sharing options...
eleria Posted May 6, 2008 Author Share Posted May 6, 2008 (edited) Well now I get Connection Successful Edit: This is the output : ########## TABLES (1) ########## spawnlist ########## COLUMNS (13)########## id location count npc_templateid locx locy locz randomx randomy heading respawn_delay loc_id periodOfDay Edited May 6, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 #include <Array.au3> #include "mysql.au3" $SQLOBJ = _MySQLConnect("username","password","host","database") Switch @ERROR Case 0 MsgBox(0,"","Connection successful") Case 1 MsgBox(0,"","Error","Error opening connection") Case 2 MsgBox(0,"","MySQL ODBC Driver not installed.") EndSwitch ;Retrieve array of all table names in database $aTables = _GetTblNames($SQLOBJ) If NOT @ERROR Then _ArrayDisplay($aTables, "Tables") Else MsgBox(0,"Error","Something Went Wrong!") EndIf Link to comment Share on other sites More sharing options...
eleria Posted May 6, 2008 Author Share Posted May 6, 2008 (edited) It works without any problem and I get an output.Okay now I managed to get an output with my script but still I have no idea how to display for example the result of the query Select * FROM spawnlist;withCODE Dim $ret[1] With $quer While Not .eof ReDim $ret[uBound($ret, 1) + 1] $ret[uBound($ret, 1) - 1] = .fields (0).value .movenext WEnd $ret[0] = UBound($ret, 1) - 1 _Arraydisplay($ret, "whatever")I can only see one column... (sorry : /) Edited May 6, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 6, 2008 Share Posted May 6, 2008 (edited) $result = _MySQLExec($SQLOBJ, "SELECT * FROM web_news;") If NOT @ERROR Then With $result While NOT .EOF; is equal to "Do this until we run out of records" ;Write the value for every column For $X In .Fields ConsoleWrite($X.value & ",") Next .MoveNext;Move to the next row ConsoleWrite(@CRLF) WEnd EndWith Else MsgBox(0,"Error","Something Went Wrong!") EndIf Edited May 6, 2008 by weaponx Link to comment Share on other sites More sharing options...
eleria Posted May 7, 2008 Author Share Posted May 7, 2008 Woah thank you alot for everything weaponx I now even understand most of what's going on in this UDF Link to comment Share on other sites More sharing options...
eleria Posted May 7, 2008 Author Share Posted May 7, 2008 (edited) I've got a new problem which is that subqueries aren't processed (EXISTS and everything after this keyword...)CODESELECT location, count, npc_templateid, locx, locy, locz, randomx, randomy, heading, respawn_delay, loc_id, periodOfDay FROM spawnlist WHERE (spawnlist.locx >= " & $x1 & " AND spawnlist.locy >= " & $y1 & " AND spawnlist.locx < " & $x2 & " AND spawnlist.locy < " & $y2 & ") AND EXISTS ( SELECT idTemplate, type FROM npc WHERE type = L2Monster AND spawnlist.npc_templateid = idTemplate );What it is supposed to do: check if the entity exists and if it is a 'L2Monster' then it should be added to the SELECT listThanks in advance EDIT: error is "The requested action with this object has failed" on the line with Return $oConnectionObj.Execute($sQuery) Edited May 7, 2008 by eleria Link to comment Share on other sites More sharing options...
weaponx Posted May 7, 2008 Share Posted May 7, 2008 (edited) I don't really have a way to test your query. It may just be a line formatting issue: $sql = "SELECT location, count, npc_templateid, locx, locy, locz, randomx, randomy, heading, respawn_delay, loc_id, periodOfDay" & @CRLF & _ "FROM spawnlist" & @CRLF & _ "WHERE (spawnlist.locx >= " & $x1 & @CRLF & _ "AND spawnlist.locy >= " & $y1 & @CRLF & _ "AND spawnlist.locx < " & $x2 & @CRLF & _ "AND spawnlist.locy < " & $y2 & ")" & @CRLF & _ "AND EXISTS ( SELECT idTemplate, type FROM npc WHERE type = L2Monster " & @CRLF & _ "AND spawnlist.npc_templateid = idTemplate );" -or- $sql = 'SELECT location, count, npc_templateid, locx, locy, locz, randomx, randomy, heading, respawn_delay, loc_id, periodOfDay' $sql &= 'FROM spawnlist' $sql &= 'WHERE (spawnlist.locx >= ' & $x1 $sql &= 'AND spawnlist.locy >= ' & $y1 $sql &= 'AND spawnlist.locx < ' & $x2 $sql &= 'AND spawnlist.locy < ' & $y2 & ')' $sql &= 'AND EXISTS ( SELECT idTemplate, type FROM npc WHERE type = L2Monster' $sql &= 'AND spawnlist.npc_templateid = idTemplate );' EDIT: You may also want to verify your query works by using ConsoleWrite($sql) and paste the SciTe output into MySQL Query Browser Edited May 7, 2008 by weaponx Link to comment Share on other sites More sharing options...
eleria Posted May 7, 2008 Author Share Posted May 7, 2008 Alright thanks to scite's output I managed to get it working : CODE$sql = 'SELECT location, count, npc_templateid, locx, locy, locz, randomx, randomy, heading, respawn_delay, loc_id, periodOfDay ' $sql &= 'FROM spawnlist' $sql &= ' WHERE (spawnlist.locx >= ' & $x1 $sql &= ' AND spawnlist.locy >= ' & $y1 $sql &= ' AND spawnlist.locx < ' & $x2 $sql &= ' AND spawnlist.locy < ' & $y2 & ')' $sql &= ' AND EXISTS ( SELECT idTemplate, type FROM npc WHERE type = "L2Monster"' $sql &= ' AND spawnlist.npc_templateid = idTemplate );' Link to comment Share on other sites More sharing options...
weaponx Posted May 7, 2008 Share Posted May 7, 2008 PHP syntax allows you to do stuff like this: $sql = 'SELECT FROM table WHERE something = 1 ORDER BY date DESC'; AutoIt isn't that friendly though. Link to comment Share on other sites More sharing options...
tweaker Posted June 26, 2008 Share Posted June 26, 2008 I was pointed to this forum for assistance with my COM Object. expandcollapse popup#include 'mysql.au3' $hSql = _MySQLConnect("un","pwd","db","add") $hTxt = FileOpen("c:\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) 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 _Query($hSql, "UPDATE `cscart_products` SET `list_price`='" & $sPrice & "' WHERE `product_id`='" & $sId & "'") _Query($hSql, "UPDATE `cscart_product_prices` SET `price`='" & $sPrice & "' WHERE `product_id`='" & $sId & "'") $sLine = FileReadLine($hTxt) WEnd FileClose($hTxt) _MySQLEnd($hSql) The problem is that I need $sID to pull from the database the product_id where the product_code comes from the text file. 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