[CLASS] MySQLAPI - MySQL wrapper

Post your working scripts, libraries and tools for AHK v1.1 and older
bobycom
Posts: 8
Joined: 26 Feb 2015, 07:13

Re: [CLASS] MySQLAPI - MySQL wrapper

19 Aug 2015, 08:10

I checked the A_LastError variable and it contains the value of 127, which means that it can not find the address of a function exported by another DLL. I then opened the Conector C 6.1 with Dependency Walker and found out that there are 3 missing functions inside my KERNEL32.DLL (InitializeConditionVariable, SleepConditionVariableCS and WakeConditionVariable). Then I saw in MSDN that the minimum supported client of those functions is Windows Vista.

If you think that this error code is unambiguous, please add a specific error message according to it.


P.S.
When the libmysql.dll file is missing, the error code is 126. I wasn't able to find a full list of the error codes of the LoadLibrary function.


Edit
I have manually edited the 2 dll's (6.0 and 6.1) in a text editor and deleted a few symbols. When I open the test script the LoadLibrary function returns error code 193. The error code is the same if I try to use the 64-bit version of the DLL or an empty .txt file renamed as .dll.

I hope that this is helpful :)
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

20 Aug 2015, 02:46

Hello bobycom,

thanks for your breakdown. What do you think about this change (replacement for the __New method:

Code: Select all

   ; ===================================================================================================================
   ; META FUNCTION __New
   ; Load and initialize libmysql.dll which is supposed to be in the sript's folder.
   ; Parameters:    LibPath  - Optional: Absolute path of libmysql.dll
   ; ===================================================================================================================
   __New(LibPath := "") {
      Static LibMySQL := A_ScriptDir . "\libmysql.dll"
      ; Do not instantiate unstances!
      If (This.Base.Base.__Class = "MySQLAPI") {
         MsgBox, 16, MySQL Error!, You mut not instantiate instances of MySQLDB!
         Return False
      }
      ; Load libmysql.dll
      If (LibPath)
         LibMySQL := LibPath
      If !(MySQLM := DllCall("Kernel32.dll\LoadLibrary", "Str", LibMySQL, "UPtr")) {
         If (A_LastError = 126) ; The specified module could not be found
            MsgBox, 16, MySQL Error!, Could not find %LibMySQL%!
         Else {
            ErrCode := A_LastError
            VarSetCapacity(ErrMsg, 131072, 0) ; Unicode
            DllCall("FormatMessage", "UInt", 0x1200, "Ptr", 0, "UInt", ErrCode, "UInt", 0, "Str", ErrMsg, "UInt", 65536, "Ptr", 0)
            MsgBox, 16, MySQL Error!, % "Could not load " . LibMySQL . "!`n"
                                      . "Error code: " . ErrCode . "`n"
                                      . ErrMsg
         }
         Return False
      }
      This.Module := MySQLM
      ; Init MySQL
      If !(MYSQL := This.Init()) {
         MsgBox, 16, MySQL Error!, Could not initialize MySQL!
         Return False
      }
      This.MYSQL := MYSQL
      If (This.Options("MYSQL_SET_CHARSET_NAME", "utf8") <> This.MySQL_SUCCESS) {
         MsgBox, 16, MySQL Error!, Set option MYSQL_SET_CHARSET_NAME failed!
         Return False
      }
      If (This.Options("MYSQL_OPT_RECONNECT", True) <> This.MySQL_SUCCESS) {
         MsgBox, 16, MySQL Error!, Set option MYSQL_OPT_RECONNECT failed!
         Return False
      }
      This.Connected := False
   }
Would you test it, please?
bobycom
Posts: 8
Joined: 26 Feb 2015, 07:13

Re: [CLASS] MySQLAPI - MySQL wrapper

20 Aug 2015, 03:15

I like your code :)

Test results on a Windows XP 32-bit machine:
  • When DLL is missing:
    "Could not find D:\Tests\libmysql.dll!"
  • When Using the 32-bit version of Connector C 6.1:
    "Could not load D:\Tests\libmysql.dll!
    Error code: 127
    The specified procedure could not be found."
  • When using a corrupted or a 64-bit DLL:
    "Could not load D:\Tests\libmysql.dll!
    Error code: 193
    %1 is not a valid Win32 application."
P.S.
I noticed a missing letter in the first message box of the function ("You muSt not instantiate instances of MySQLDB!").
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

20 Aug 2015, 04:02

Thanks again. I've updated the source and the OP.
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [CLASS] MySQLAPI - MySQL wrapper

15 Jan 2016, 19:08

Hello,

Have you an example to retrieve multiple data from MySQL and show data in msgbox.

Code: Select all

SQL := "SELECT ID, NAME FROM list"
If (DB.Query(SQL) = MySQL_SUCCESS) 
  {
     Result := DB.Store_Result()
     Id_Row := DB.Fetch_Row(Result)
     Result_Id_Row := StrGet(NumGet(Id_Row + 0, 0, "UPtr"), "UTF-8")
     Name_Row := DB.Fetch_Row(Result)
     Result_Name_Row := StrGet(NumGet(Name_Row + 0, 0, "UPtr"), "UTF-8")
     DB.Free_Result(Result)
  }
MsgBox, 0, , %Result_ID_Row% @ %Result_Name_Row%
Return
This returns only Result_ID_Row and no Result_Name_Row !

Thank you for your help :)
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

16 Jan 2016, 04:41

Both ID and Name are belonging to one row. So this might work:

Code: Select all

SQL := "SELECT ID, NAME FROM list"
If (DB.Query(SQL) = MySQL_SUCCESS)
  {
     Result := DB.Store_Result()
     Result_Row := DB.Fetch_Row(Result)
     Result_Id := StrGet(NumGet(Result_Row + 0, 0, "UPtr"), "UTF-8")
     Result_Name := StrGet(NumGet(Result_Row + 0, A_PtrSize, "UPtr"), "UTF-8")
     DB.Free_Result(Result)
  }
MsgBox, 0, , %Result_ID% @ %Result_Name%
Return
*untested*

But I'd suggest to use the additional wrapper functions GetNextRow() or GetResult():

Code: Select all

SQL := "SELECT ID, NAME FROM list"
If (DB.Query(SQL) = MySQL_SUCCESS)
  {
     Result := DB.Store_Result()
     Result_Row := DB.GetNextRow(Result)
     Result_Id := Result_Row[1]
     Result_Name := Result_Row[2]
     DB.Free_Result(Result)
  }
MsgBox, 0, , %Result_ID% @ %Result_Name%
Return
*also untested*
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [CLASS] MySQLAPI - MySQL wrapper

16 Jan 2016, 09:44

Thank you just me this work :)
Spoiler
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [CLASS] MySQLAPI - MySQL wrapper

17 Jan 2016, 19:44

Hi,

how to retrieve the total number (49) and show msgbox with 49 in 0.0002 ?

In phpmyadmin :

Showing rows 0 - 0 (49 total, Query took 0.0002 sec)
SELECT * FROM `mix` WHERE `MIX_OP` = 162

Thank you for your help.
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

18 Jan 2016, 03:06

Code: Select all

   ; ===================================================================================================================
   ; Returns the number of rows in a result set.
   ; Parameters:    MYSQL_RES - Pointer to a MYSQL_RES structure.
   ; Return values: An unsigned 64-bit integer representing the number of rows in a result set.
   ; ===================================================================================================================
   Num_Rows(MYSQL_RES) {
      Return DllCall("libmysql.dll\mysql_num_rows", "Ptr", MYSQL_RES, "UInt64")
   }
?
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [CLASS] MySQLAPI - MySQL wrapper

19 Jan 2016, 08:20

Yes sorry :/
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
hot hot 85
Posts: 18
Joined: 27 Dec 2015, 19:34

Re: [CLASS] MySQLAPI - MySQL wrapper

13 Feb 2016, 23:59

Hi all ,is my first time ,I try to use the library ,but appear the next error

---------------------------
MySQL Error!
---------------------------
Connection failed!

2003 - Can't connect to MySQL server on 'lenovo-PC' (10061)
---------------------------
Aceptar
---------------------------

I modify the next info
; Settings
; ======================================================================================================================
UserID := "lenovo" ; User name - must have privileges to create databases
UserPW := "DARIO" ; User''s password
Server := "lenovo-PC" ; Server''s host name or IP address

Database := "Test" ; Name of the database to work with
DropDatabase := False ; DROP DATABASE
DropTable := False ; DROP TABLE Address


I have the libmysql.dll from MySQL Connector C 6.1


The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.

Any ideas?
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

14 Feb 2016, 03:14

Did you try to use the IP address?
hot hot 85
Posts: 18
Joined: 27 Dec 2015, 19:34

Re: [CLASS] MySQLAPI - MySQL wrapper

14 Feb 2016, 11:43

just me wrote:Did you try to use the IP address?

I think I resolve the problem.Now I have this error

---------------------------
MySQL Error!
---------------------------
Connection failed!

1130 - Host 'fe80::6dae:72e2:f9ce:ca05%10' is not allowed to connect to this MySQL server
---------------------------
Aceptar
---------------------------
If I use IP Address

show the next error

---------------------------
MySQL Error!
---------------------------
Connection failed!

1130 - Host '192.168.2.5' is not allowed to connect to this MySQL server
---------------------------
Aceptar
---------------------------

Any Ideas?
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

14 Feb 2016, 12:02

No. I suppose that 192.168.2.5 is the client PC's IP address, is it?
hot hot 85
Posts: 18
Joined: 27 Dec 2015, 19:34

Re: [CLASS] MySQLAPI - MySQL wrapper

14 Feb 2016, 12:13

just me wrote:No. I suppose that 192.168.2.5 is the client PC's IP address, is it?

Thanks Just me I Log in I use

mysql> CREATE USER 'dario'@'192.168.2.5' IDENTIFIED BY 'dario';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dario'@'192.168.2.5'
-> WITH GRANT OPTION;

Now to learn how to use
:)


if I try to use the button add show this error

---------------------------
MySQL Error!
---------------------------
1146: Table 'test.address' doesn't exist

any ideas?
geminbot

Re: [CLASS] MySQLAPI - MySQL wrapper

16 Feb 2016, 15:49

Hi Just Me - Just wanted to thank you for an awesome upgrade to panofish's incredible mysql api wrapper.
It bailed me out of a tight spot.
One thing that gave me trouble was actually using the data after I queried it.
(Ignorance on my part - obviously)
But I wrote a demo AHK script that uses your wrapper.
It's not much but it may help others traveling a similar path to mine.
To use it a single table database is needed.
There should be some data in the table too.
(I did not include a CREATE TABLE sql in this demo.)
I hope this will be helpful -- Thanks again !

Code below:

Code: Select all

;====================================================================
; 
;  Demo using mysql 2 library
;
; Programmer: NJ  
; AutoHotkey: v1.1.04.00 (autohotkey_L ANSI version)
; Created to work with MySQLAPI
;   Wrapper class for MySQL C API functions        -> http://dev.mysql.com/doc/refman/5.5/en/c-api-functions.html
;
;====================================================================

#SingleInstance force
#NoENV              ; Avoids checking empty variables to see if they are environment variables (recommended for all new scripts and increases performance).
SetBatchLines -1    ; have the script run at maximum speed and never sleep
ListLines Off       ; a debugging option

outputdebug DBGVIEWCLEAR

#include mysql2.ahk     ; pull from local directory

mysql=
dbName = test
server = localhost
user = root
pswrd = password
tablename = persons

;============================================================
; Build gui:
;============================================================ 


;Gui, 1:Default 
Gui +resize


Gui, Add, Text, section w370, Database connect and Query Demo


Gui, Add, StatusBar

    ;-- buttons
    Gui, Add, Button,x100 y+10 w100 h30 gdb_connect, Connect 
    Gui, Add, Button, y+10 w100 h30 gdb_ping, Ping 
    Gui, Add, Button, y+10 w100 h30 gdb_select, Select 
    Gui, Add, Button, y+10 w100 h30 gdb_store, Use Data 
    Gui, Add, Button,x100 y+20 w100 h30 gdb_disconnect, Disconnect 

Gui, Add, Text, section y+30 w370 vDBSTAT, Database: Not Connected

Gui, Show, w600 h400,MySQL 2 debugger



return

;============================================================
; 
;============================================================ 


;------------------------------------------------------------
db_connect:
	try {
        mysql := new MySQLAPI      ; instantiates an object using this class
        mysql.Real_Connect(server, user, pswrd, dbName)
        }
    catch e
    {
        MsgBox,16, Error, % "Failed to create connection. Check your Connection string and DB Settings!`n`n"e
        ExitApp
    }
    gosub db_ping
    if (Result = 0)
    {
    ;MsgBox,,CONFIRM:, Database connected..      
    GuiControl,,DBSTAT, Database Connected..
    }
    else
    {
        MsgBox,,CONNECT FAILURE:, Failed to connect !      
    }
return

;------------------------------------------------------------
db_disconnect:
    mysql=
    GuiControl,,DBSTAT, Database Disconnected
return

;------------------------------------------------------------
db_select:
    result = -1
    sql = SELECT * FROM %dbName%.%tablename%        ;-- generic test script to get all data from the person table in the test database
    result := mysql.Query(sql)
    if result = 0
    {
        ;MsgBox,,CONFIRM:, Query executed `nresult = %result%       
        GuiControl,,DBSTAT, Database: Query executed 
    }
    else
    {
        MsgBox,,ERROR:, Query failed ! `nresult = %result%                
    }

return

;------------------------------------------------------------
db_ping:
    result := -1
    result := mysql.Ping()
    if result = 0
    {
        ;MsgBox,,CONFIRM:, Ping confirms connection `nresult = %result%       
        GuiControl,,DBSTAT, Database: Ping confirms connection 
    }
    else
    {
        MsgBox,,ERROR:, Ping failed ! ;`nresult = %result%                
    }
return 


;------------------------------------------------------------
db_store:

    resultPtr := mysql.Store_Result()
    if resultPtr > 0
    {
        fieldCount := mysql.Field_Count()       ;-- returns the number of columns in the data set
        rowCount := mysql.Num_Rows()            ;-- returns the number of row in the data set
        Loop                                   
        {
            ;thisRow := mysql.GetNextRow(resultPtr)
            row := mysql.Fetch_Row(resultPtr)     ;-- loop through rows until Fetch_Row returns NULL
            If (row = 0 || row == "")
                Break
            ; Get a pointer on a table of lengths (unsigned long)
            lengths := mysql.Fetch_Lengths(resultPtr) ;-- column lengths for the given row
            Loop %fieldCount%                   ;-- loop through the columns (fields) of this row
            {
                J := A_Index - 1
                If (Len := NumGet(Lengths + 0, 4 * J, "UInt"))
                    field := (StrGet(NumGet(row + 0, A_PtrSize * J, "UPtr"), Len, "UTF-8"))
                else
                    field=
                ;MsgBox,,CONFIRM:, field = %field% `n Len = %Len% `n J = %J%                 
                resultString := resultString . field                
                If (A_Index < fieldCount)
                    resultString := resultString . "|"     ; seperator for fields
            }
            resultString := resultString . "`n"          ; seperator for records  
        }
        ; remove last newline from resultString
        resultString := RegExReplace(resultString , "`n$", "") 	
       
        MsgBox,,CONFIRM:, resultString = %resultString%         
    }
    else
    {
        MsgBox,,ERROR:, Missing Data ! `nresult = %result%                
    }
return



;============================================================
; when you click x or close button
;============================================================ 

GuiClose:
        
ExitApp
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

17 Feb 2016, 03:22

Thanks, I added a link in the OP.
hot hot 85
Posts: 18
Joined: 27 Dec 2015, 19:34

Re: [CLASS] MySQLAPI - MySQL wrapper

17 Feb 2016, 21:37

Hi in the code above you are referring to #include mysql2.ahk , where is this file?
just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [CLASS] MySQLAPI - MySQL wrapper

18 Feb 2016, 03:11

I guess it's geminbot's private version of Class_MySQLAPI.ahk.
geminbot

Re: [CLASS] MySQLAPI - MySQL wrapper

21 Feb 2016, 20:33

Yes, I aliased the class and called it mysql2.ahk instead of Class_MySQLAPI.ahk.
Sorry about that. I should have changed that back when I sanitized my demo.

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: No registered users and 83 guests