#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 3.51 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: _Query2array
Description: Send a query to the database
Parameters: $oConnectionObj - As returned by _MySQLConnect. $sQuery - The query to execute
Return values: On success returns the query result by array. On failure returns 0 and sets @error to 1
Requirement(s):Autoit3 with COM support
Author: WayneHu
#ce
Func _Query2array($oConnectionObj,$sQuery)
$var = _Query($oConnectionObj,$sQuery)
If @error Then
  SetError(1)
  Return 0
EndIf

With $var
  $i = 0
  While Not .eof
   $i = $i + 1
   .movenext
  WEnd
EndWith

$var.movefirst

Dim $array[$i+1][$var.Fields.count+1]
$array[0][0] = $i
$array[0][1] = $var.Fields.count

For $r = 1 To $i
  For $c = 0 To $var.Fields.count-1
   $array[$r][$c+1] = $var.Fields($c).value
  Next
  $var.MoveNext
Next
Return $array
EndFunc

#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
			;mysql_query("INSERT INTO users(name,email,password) VALUES('$name','$email','$password')")
			;$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")
			;$oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & "," & $vRow2 & "," &  $vRow3 & ") VALUES (" & $vValue & "," & $vValue2 & "," & $vValue3 & ")" )
			$oConnectionobj.execute ("INSERT INTO users(name,email,password) VALUES('ernesto jr','email@test.com','password')")
			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 = 1
			While Not .eof
				If .fields (0).value = $sColumn Then
					$ret = .fields (0).value
				EndIf
				.MoveNext
			WEnd
		EndWith
		Return $ret
	EndIf
	If @error Then
		Return 0
		SetError(1)
	EndIf
EndFunc   ;==>_GetColType

Func _READ($oConnectionObj, $sTable, $sColumn)
	If IsObj($oConnectionObj) Then
		;SHOW COLUMNS FROM mytable FROM mydb
		$quer = $oConnectionObj.execute ("SHOW COLUMNS FROM " & "users" & " FROM " & 'database')
		;$quer = $oConnectionObj.execute ("SHOW COLUMNS FROM " & "users" & " FROM " & '')
		With $quer
			$i = 0
			While Not .eof
				If .fields (1).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