MySQL support?

[eleria]

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

[weaponx]

You have to install the MySQL ODBC Connector.

http://dev.mysql.com/downloads/connector/odbc/5.1.html

[eleria]

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, $ver

Dim $geo, $file, $x1, $y1, $x2, $y2

Dim $x11, $y11, $x21, $y21

Dim $quer

Const $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!")

Exit

EndIf

#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(St

ringReplace($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)

EndSwitch

WEnd

And I'm getting an error in the mysql.au3 at

CODE

Func _Query($oConnectionObj, $sQuery)

If IsObj($oConnectionObj) Then

Return $oConnectionobj.execute ($sQuery)

EndIf

If @error Then

SetError(1)

Return 0

EndIf

More 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

[weaponx]

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

[eleria]

Connection Successful !

Actually the querry SELECT * FROM spawnlist; give a blank result in a msgbox

Edited May 6, 2008 by eleria

[weaponx]

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

[eleria]

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

[weaponx]

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=mysql

There 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.

[eleria]

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

[weaponx]

#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

[eleria]

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;

with

CODE

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

[weaponx]

$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

[eleria]

Woah thank you alot for everything weaponx I now even understand most of what's going on in this UDF

[eleria]

I've got a new problem which is that subqueries aren't processed (EXISTS and everything after this keyword...)

CODE

SELECT 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 list

Thanks 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

[weaponx]

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

[eleria]

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 );'

[weaponx]

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.

[tweaker]

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.