Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Tree: 5a2d5ba8a8
Fetching contributors…

Cannot retrieve contributors at this time

432 lines (329 sloc) 11.232 kB
/*============================================================
mysql.ahk
Provides a set of functions to connect and query a mysql database
Based upon the published lib of panofish
http://www.autohotkey.com/forum/topic67280.html
Offical Documentation of the C-API
http://dev.mysql.com/doc/refman/5.0/en/c.html
============================================================
*/
/*
Parses the given Connectionstring to a ConnectionData
An typical Connectionstring looks like:
Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Further Info: http://www.connectionstrings.com/mysql
*/
MySQL_CreateConnectionData(connectionString){
connectionData := {}
StringSplit, connstr, connectionString, `;
Loop, % connstr0
{
StringSplit, segment, connstr%a_index%, =
connectionData[segment1] := segment2
}
return connectionData
}
MySQL_StartUp(){
global MySQL_ExternDir
MySQL_ExternDir := A_WorkingDir
libDllpath := MySQL_DLLPath()
if(!FileExist(libDllpath))
{
msg := "MySQL Libaray not found!`n" libDllpath " (file missing)"
OutputDebug, %msg%
throw Exception(msg,-1)
}
hModule := DllCall("LoadLibrary", "Str", libDllpath)
if (hModule == 0)
{
msg := "LoadLibrary failed, can't load module:`n" libDllpath
OutputDebug, %msg%
throw Exception(msg, -1)
}else
return hModule
}
MySQL_DLLPath(forcedPath = "") {
static DLLPath := ""
static dllname := "libmySQL.dll"
if(DLLPath == ""){
; search the dll
prefix := (A_PtrSize == 8) ? "x64\" : ""
dllpath := prefix . dllname
if (FileExist(A_ScriptDir . "\" . dllpath))
DLLPath := A_ScriptDir . "\" . dllpath
else
DLLPath := A_ScriptDir . "\Lib\" . dllpath
}
if (forcedPath != "")
DLLPath := forcedPath
return DLLPath
}
/*****************************************************************
Connect to mysql database and return db handle
host:
user:
password:
database:
port: 3306(default)
******************************************************************
*/
MySQL_Connect(host, user, password, database, port = 3306){
db := DllCall("libmySQL.dll\mysql_init", "ptr", 0)
if (db = 0)
throw Exception("MySQL Error 445, Not enough memory to connect to MySQL", -1)
connection := DllCall("libmySQL.dll\mysql_real_connect"
, "ptr", db
, "AStr", host ; host name
, "AStr", user ; user name
, "AStr", password ; password
, "AStr", database ; database name
, "UInt", port ; port
, "UInt", 0 ; unix_socket
, "UInt", 0) ; client_flag
If (connection == 0)
throw Exception(BuildMySQLErrorStr(db, "Cannot connect to database"), -1)
;debugging only:
;MsgBox % "Ping database: " . MySQL_Ping(db) . "`nServer version: " . MySQL_GetVersion(db)
return db
}
MySQL_Close(db){
DllCall("libmySQL.dll\mysql_close", "ptr", db)
}
MySQL_GetVersion(db){
serverVersion := DllCall("libmySQL.dll\mysql_get_server_info", "ptr", db, "AStr")
return serverVersion
}
MySQL_Ping(db){
return DllCall("libmySQL.dll\mysql_ping", "ptr", db)
}
MySQL_GetLastErrorNo(db){
return DllCall("libmySQL.dll\mysql_errno", "ptr", db)
}
MySQL_GetLastErrorMsg(db){
return DllCall("libmySQL.dll\mysql_error", "ptr", db, "AStr")
}
/*
Retrieves a complete result set to the client.
*/
MySQL_Store_Result(db) {
return DllCall("libmySQL.dll\mysql_store_result", "ptr", db)
}
/*
Retrieves the resultset row-by-row
*/
MySQL_Use_Result(db) {
return DllCall("libmySQL.dll\mysql_use_result", "ptr", db)
}
/*
Returns a requestResult for the given query
*/
MySQL_Query(db, query){
return DllCall("libmySQL.dll\mysql_query", "ptr", db , "AStr", query)
}
MySQL_free_result(requestResult){
return DllCall("libmySQL.dll\mysql_free_result", "ptr", requestResult)
}
/*
Returns the number of columns in a result set.
*/
MySQL_num_fields(requestResult) {
Return DllCall("libmySQL.dll\mysql_num_fields", "ptr", requestResult)
}
/*
Returns the lengths of all columns in the current row.
*/
MySQL_fetch_lengths(requestResult) {
Return , DllCall("libmySQL.dll\mysql_fetch_lengths", "ptr", requestResult)
}
/*
Fetches the next row from the result set.
*/
MySQL_fetch_row(requestResult) {
Return , DllCall("libmySQL.dll\mysql_fetch_row", "ptr", requestResult)
}
/*
Fetches given Field
*/
Mysql_fetch_field_direct(requestResult, fieldnum) {
return DllCall("libmySQL.dll\mysql_fetch_field_direct", "ptr", requestResult, "Uint", fieldnum)
}
/*
Fetches the next field from the result set.
*/
Mysql_fetch_field(requestResult) {
return DllCall("libmySQL.dll\mysql_fetch_field", "ptr", requestResult)
}
/*
Fetches all fields of the resultSet
*/
MySQL_fetch_fields(requestResult){
global MySQL_Field
fields := []
fieldCount := MySQL_num_fields(requestResult)
Loop, % fieldCount
{
fptr := Mysql_fetch_field(requestResult)
fields[A_index] := new MySQL_Field(fptr)
}
return fields
}
/*
; mysql error handling
*/
BuildMySQLErrorStr(db, message, query="") {
errorCode := DllCall("libmySQL.dll\mysql_errno", "UInt", db)
errorStr := DllCall("libmySQL.dll\mysql_error", "UInt", db, "AStr")
Return, "MySQL Error: " message "Error " errorCode ": " errorStr "`n`n" query
}
;============================================================
; mysql get address
;============================================================
GetUIntAtAddress(_addr, _offset)
{
return NumGet(_addr+0,_offset * 4, "uint")
}
GetPtrAtAddress(_addr, _offset)
{
return NumGet(_addr+0,_offset * A_PtrSize, "ptr")
}
;============================================================
; internal: dump resultset from given Query to string
;============================================================
__MySQL_Query_Dump(_db, _query)
{
local resultString, result, requestResult, fieldCount
local row, lengths, length, fieldPointer, field
result := DllCall("libmySQL.dll\mysql_query", "UInt", _db , "AStr", _query)
If (result != 0)
throw new Exception(BuildMySQLErrorStr(_db, "dbQuery Fail", RegExReplace(_query , "\t", " ")), -1)
requestResult := MySql_Store_Result(_db)
if (requestResult = 0) { ; call must have been an insert or delete ... a select would return results to pass back
return
}
fieldCount := MySQL_num_fields(requestResult)
myfields := MySQL_fetch_fields(requestResult)
for each, fifi in myfields
{
MsgBox % "name: " fifi.Name() "`n org name: " fifi.OrgName() "`ntable: " fifi.Table() "`norg table: " fifi.OrgTable()
}
Loop
{
row := MySQL_fetch_row(requestResult)
if (!row)
break
; Get a pointer on a table of lengths (unsigned long)
lengths := MySQL_fetch_lengths(requestResult)
Loop %fieldCount%
{
length := GetUIntAtAddress(lengths, A_Index - 1)
fieldPointer := GetPtrAtAddress(row, A_Index - 1)
field := StrGet(fieldPointer, length, "CP0")
resultString := resultString . field
if (A_Index < fieldCount)
resultString := resultString . "|" ; seperator for fields
}
resultString := resultString . "`n" ; seperator for records
}
MySQL_free_result(requestResult)
resultString := RegExReplace(resultString , "`n$", "")
return resultString
}
;============================================================
; Escape mysql special characters
; This must be done to sql insert columns where the characters might contain special characters, such as user input fields
;
; Escape Sequence Character Represented by Sequence
; \' A single quote (“'”) character.
; \" A double quote (“"”) character.
; \n A newline (linefeed) character.
; \r A carriage return character.
; \t A tab character.
; \\ A backslash (“\”) character.
; \% A “%” character. Usually indicates a wildcard character
; \_ A “_” character. Usually indicates a wildcard character
; \b A backspace character.
;
; these 2 have not yet been included yet
; \Z ASCII 26 (Control+Z). Stands for END-OF-FILE on Windows
; \0 An ASCII NUL (0x00) character.
;
; example call:
; description := mysql_escape_string(description)
;============================================================
Mysql_escape_string(unescaped_string)
{
escaped_string := RegExReplace(unescaped_string, "\\", "\\") ; \
escaped_string := RegExReplace(escaped_string, "'", "\'") ; '
escaped_string := RegExReplace(escaped_string, "`t", "\t") ; \t
escaped_string := RegExReplace(escaped_string, "`n", "\n") ; \n
escaped_string := RegExReplace(escaped_string, "`r", "\r") ; \r
escaped_string := RegExReplace(escaped_string, "`b", "\b") ; \b
; these characters appear to insert fine in mysql
;escaped_string := RegExReplace(escaped_string, "%", "\%") ; %
;escaped_string := RegExReplace(escaped_string, "_", "\_") ; _
;escaped_string := RegExReplace(escaped_string, """", "\""") ; "
return escaped_string
}
/*
typedef struct st_mysql_field {
char *name; /* Name of column */
char *org_name; /* Original column name, if an alias */
char *table; /* Table of column if column was a field */
char *org_table; /* Org table name, if table was an alias */
char *db; /* Database for table */
char *catalog; /* Catalog for table */
char *def; /* Default value (set by mysql_list_fields) */
unsigned long length; /* Width of column (create length) */
unsigned long max_length; /* Max width for selected set */
unsigned int name_length;
unsigned int org_name_length;
unsigned int table_length;
unsigned int org_table_length;
unsigned int db_length;
unsigned int catalog_length;
unsigned int def_length;
unsigned int flags; /* Div flags */
unsigned int decimals; /* Number of decimals in field */
unsigned int charsetnr; /* Character set */
enum enum_field_types type; /* Type of field. See mysql_com.h for types */
void *extension;
} MYSQL_FIELD;
*/
/*
'mysql_port is a long
'mysql_unix port is a long (pointer)
'sizeof(MYSQL_FIELD)=32
Public Type API_MYSQL_FIELD
name As Long
table As Long
def As Long
type As API_enum_field_types
length As Long
max_length As Long
flags As Long
decimals As Long
End Type
*/
class MySQL_Field
{
ptr := 0
__new(ptr){
this.ptr := ptr
}
Name(){
adr := GetPtrAtAddress(this.ptr, 0)
return StrGet(adr, 255, "CP0")
}
OrgName(){
adr := GetPtrAtAddress(this.ptr, 4)
return StrGet(adr, 255, "CP0")
}
Table(){
adr := GetPtrAtAddress(this.ptr, 8)
return StrGet(adr, 255, "CP0")
}
OrgTable(){
adr := GetPtrAtAddress(this.ptr, 12)
return StrGet(adr, 255, "CP0")
}
}
Jump to Line
Something went wrong with that request. Please try again.