MySQL

[jj2007]

Everything works fine but the query returns zero rows... ::)

  mov dbase, mysql_init(0)
  .if eax
        Let dbName$="sakila" ; ... test_db-master\sakila\sakila-mv-data.sql"; see also usage examples
        test eax, mysql_real_connect(dbase, 0, "Jochen", "jj4MySql", dbName$, MYSQL_PORT, NULL, 0)
        je sqlErr
        PrintLine "Bingo, database found and connected!!!!"
        test eax, mysql_select_db(dbase, "sakila")
        jne sqlErr
        Print Str$("database selected, field_count=%i\n", mysql_field_count(dbase))
        Let q$="select * from actor"            ; actor is a 4-fields table in sakila-mv-data.sql
        test eax, mysql_query(dbase, q$)
        jne sqlErr
        PrintLine "query performed"
        mov resTable, mysql_store_result(dbase)
        test eax, eax
        je sqlErr
        Print Str$("query stored, table has %i columns", mysql_num_fields(resTable))   
       PrintLine Str$(" and %i rows", mysql_num_rows(resTable))        ; SOF: Check table and number of rows from C (doc)
        .While 1
                mov row$, mysql_fetch_row(resTable)     ; no row found!
                .Break .if !eax
                PrintLine "row: [", row$, "]"           ; no output...
        .Endw

Output:

Code Select Expand

Bingo, database found and connected!
database selected, field_count=0
query performed
query stored, table has 4 columns and 0 rows

Any MySQL experts around?

[aw27]

This is in UASM and 64-bit because I have no 32-bit mySQL here. Sorry.

Code Select Expand


option casemap :None
OPTION frame:auto
OPTION ARCH:SSE
OPTION WIN64:2
option LITERALS:ON

_MYSQL_ROW typedef ptr

includelib libmysql.lib"
mysql_init proto :ptr
mysql_real_connect proto :ptr, :ptr, :ptr, :ptr, :ptr, :sdword, :ptr, :dword
mysql_query proto :ptr, :ptr
mysql_store_result proto :ptr
mysql_num_fields proto :ptr
mysql_fetch_row proto :ptr
mysql_free_result proto :ptr
mysql_close proto :ptr

includelib \masm32\lib64\kernel32.lib
ExitProcess proto :dword

includelib \masm32\lib64\msvcrt.lib
printf proto :ptr, :vararg

.data
cr db 13,10,0

.code

main proc
LOCAL _MYSQL : ptr
LOCAL _MYSQL_RES : ptr
LOCAL numFields : dword
LOCAL tempRow : _MYSQL_ROW

invoke mysql_init ,0
.if rax==0
invoke printf, "Error in mysql_init"
jmp @exit
.endif
mov _MYSQL, rax

INVOKE mysql_real_connect, _MYSQL, "localhost", "username", "mypassword", "mysqltestDB", 3306,0,0
.if rax==0
invoke printf, "Error in mysql_real_connect"
jmp @exit
.endif

INVOKE mysql_query, _MYSQL, "SELECT * FROM mytable"
.if eax!=0
invoke printf, "Error in mysql_query"
                jmp @exit
.endif

INVOKE mysql_store_result, _MYSQL
.if rax==0
invoke printf, "Error in mysql_store_result"
jmp @exit
.endif
mov _MYSQL_RES, rax

INVOKE mysql_num_fields, _MYSQL_RES
mov numFields, eax

INVOKE printf, "Number of fields: %d", numFields
INVOKE printf, offset cr

INVOKE mysql_fetch_row, _MYSQL_RES
.while rax!=0
mov tempRow, rax
mov ebx, 0
.while ebx<numFields
mov rcx, tempRow
mov rcx, [rcx+rbx*sizeof qword]
.if rcx!=0
INVOKE printf, "%s ", rcx
.else
INVOKE printf, "null"
.endif
inc ebx
.endw
INVOKE printf, offset cr
INVOKE mysql_fetch_row, _MYSQL_RES
.endw
INVOKE mysql_free_result, _MYSQL_RES
INVOKE mysql_close, _MYSQL
@exit:
INVOKE ExitProcess,0
main endp

end


[jj2007]

Thanks, José. Your code is the 64-bit equivalent to mine, more or less. The problem must be in the query string, though, or the way the sample database sakila is loaded. The usage examples linked above are not very helpful, unfortunately. The database gets loaded, it seems (num_fields yields the correct result), but zero rows are available :(

[aw27]

Thanks, José. Your code is the 64-bit equivalent to mine, more or less. The problem must be in the query string, though, or the way the sample database sakila is loaded. The usage examples linked above are not very helpful, unfortunately. The database gets loaded, it seems, but zero rows are available :(

From what I can see in your code (without understanding much of it), you did not notice that mysql_fetch_row returns a pointer to a pointer.  :icon_rolleyes:

typedef char **MYSQL_ROW

[jj2007]

you did not notice

Correct, José, I didn't notice. I would have noticed that quickly, though, if the function had ever returned a non-zero value ;)

[aw27]

I would have noticed that quickly, though, if the function had ever returned a non-zero value ;)

The function returns the correct value when it is correctly used.
mysql_num_rows returns a qword and not a dword.   ;)

[jj2007]

OMG José!!! You are telling me that mysql_num_rows() returned zero in eax and non-zero in edx? That would mean I have a damn fat database loaded, and it would explain why my notebook has become so slow in loading VS Community nowadays ::)

Unless, of course, my favourite troll tells me now that qwords are returned in the lower half of xmm0 :P

[aw27]

You are so funny, JJ 
But you forget that the eax is pushed first on the stack and the edx is pushed second. This happens because parameters are pushed right to left in the stdcall and cdecl calling conventions. So your code takes the value of edx. Amazing, isn't it? The World is not fair for Visual Basic Masm.

The fault is never yours, in this case you are blaming as well  "The problem must be in the query string, though, or the way the sample database sakila is loaded." In other cases are the slow Visual Studio tools.   

[fearless]

I have some code and macros for MySQL in asm, but here is a code snippet that i dug out that might help:

A part of a login procedure that i was testing at the time, it uses the mysql_use_result call that saves the result to MySQLresult, which is used with the mysql_fetch_row to read each row for the username and password columns. It converted a passed password string to a hash, which is used in the query to check if user exists and password is correct (based on hashed password value) if username and password dont match then query fails and proc returns false. If it matches then query returns a result which means it was correct and proc returns true. Anyhow its just an example. I have some more code and macros if you want. One of the biggest issues i had was the line length limit in asm, so i had to create a few support functions and macros to help build the query string.

The query string built/used was:

Code Select Expand

SELECT username, user_password FROM `newworld_phpb1`.`phpbb_users` WHERE username = "fearless" AND user_password = "1746C58FB0B0305FDA550E548694095F"

Code Select Expand

    invoke mysql_query,MySQLconnection,addr MySQL_Query_String
    .if eax!=0
        ; SELECT statement is wrong
        invoke mysql_error,MySQLconnection
        invoke wsprintf,addr szError,addr szFmtError,eax
        invoke MessageBox,hWin,addr szError,CTEXT("Error"),MB_ICONERROR
        mov eax, FALSE
        ret
    ;.else
    ;    invoke MessageBox,hWin,CTEXT("Success: Fetched Username and Password"),CTEXT("Query"),MB_OK
    .endif   
    invoke mysql_use_result,MySQLconnection
    .if eax==NULL
        ; Error
        invoke mysql_error,MySQLconnection
        invoke wsprintf,addr szError,addr szFmtError,eax
        invoke MessageBox,hWin,addr szError,CTEXT("Error"),MB_ICONERROR
        mov eax, FALSE
        ret
    .endif
    mov MySQLresult,eax   
   
    invoke mysql_fetch_row,MySQLresult
    mov ebx,eax
    .IF eax != NULL
        invoke wsprintf,addr szMySQLUsername,CTEXT("%s"),dword ptr [ebx]
        invoke wsprintf,addr szMySQLPassword,CTEXT("%s"),dword ptr [ebx+4]
        PrintString szMySQLUsername
        PrintString szMySQLPassword
        ;invoke MessageBox,hWin,Addr szMySQLPassword,Addr szMySQLUsername,MB_OK
        invoke mysql_free_result,MySQLresult
        mov eax, TRUE
    .else
        ;invoke MessageBox,hWin,CTEXT("Failed to login"),CTEXT("Failed"),MB_OK
        invoke mysql_free_result,MySQLresult
        mov eax, FALSE
    .endif

[aw27]

@fearless
It may help JJ, my example was tested and works.  :icon14:

[jj2007]

Sorry, folks, will be offline for some time. Let's argue later...

[aw27]

Sorry, folks, will be offline for some time. Let's argue later...

No problem JJ, take your time. :t

I have also tested the Sakila DB with my code, which I will repeat here with the required modifications:

Code Select Expand


option casemap :None
OPTION frame:auto
OPTION WIN64:2
option LITERALS:ON

_MYSQL_ROW typedef ptr

includelib libmysql.lib
mysql_init proto :ptr
mysql_real_connect proto :ptr, :ptr, :ptr, :ptr, :ptr, :sdword, :ptr, :dword
mysql_query proto :ptr, :ptr
mysql_store_result proto :ptr
mysql_num_fields proto :ptr
mysql_fetch_row proto :ptr
mysql_free_result proto :ptr
mysql_close proto :ptr
mysql_num_rows proto :ptr

includelib \masm32\lib64\kernel32.lib
ExitProcess proto :dword
includelib \masm32\lib64\msvcrt.lib
printf proto :ptr, :vararg

.code

main proc
LOCAL _MYSQL : ptr
LOCAL _MYSQL_RES : ptr
LOCAL numFields : dword
LOCAL tempRow : _MYSQL_ROW

invoke mysql_init ,0
.if rax==0
invoke printf, "Error in mysql_init"
jmp @exit2
.endif
mov _MYSQL, rax
;C:\ProgramData\MySQL\MySQL Server 5.7\Data\sakila
INVOKE mysql_real_connect, _MYSQL, "localhost", "root", "xxxxxxxx", "sakila", 3306,0,0
.if rax==0
invoke printf, "Error in mysql_real_connect"
jmp @exit1
.endif

INVOKE mysql_query, _MYSQL, "SELECT * FROM actor"
.if eax!=0
invoke printf, "Error in mysql_query"
jmp @exit1
.endif

INVOKE mysql_store_result, _MYSQL
.if rax==0
invoke printf, "Error in mysql_store_result"
jmp @exit1
.endif
mov _MYSQL_RES, rax

INVOKE mysql_num_fields, _MYSQL_RES
mov numFields, eax
INVOKE printf, "Number of fields: %d\n", numFields
INVOKE mysql_num_rows, _MYSQL_RES
INVOKE printf, "Number of rows: %ld\n", rax

INVOKE mysql_fetch_row, _MYSQL_RES
.while rax!=0
mov tempRow, rax
mov ebx, 0
.while ebx<numFields
mov rcx, tempRow
mov rcx, [rcx+rbx*sizeof qword]
.if rcx!=0
INVOKE printf, "%s ", rcx
.else
INVOKE printf, "null"
.endif
inc ebx
.endw
INVOKE printf, "\n"
INVOKE mysql_fetch_row, _MYSQL_RES
.endw
INVOKE mysql_free_result, _MYSQL_RES
@exit1:
INVOKE mysql_close, _MYSQL
@exit2:
INVOKE ExitProcess,0
main endp

end


Let's see what output I got:

Number of fields: 4
Number of rows: 200
1 PENELOPE GUINESS 2006-02-15 04:34:33
2 NICK WAHLBERG 2006-02-15 04:34:33
3 ED CHASE 2006-02-15 04:34:33
4 JENNIFER DAVIS 2006-02-15 04:34:33
.....
197 REESE WEST 2006-02-15 04:34:33
198 MARY KEITEL 2006-02-15 04:34:33
199 JULIA FAWCETT 2006-02-15 04:34:33
200 THORA TEMPLE 2006-02-15 04:34:33

Worked super very fine. 

[jj2007]

Congrats, but it won't work on my machine. Somehow sakila is not loaded properly.
My code above works just fine with, for example
mysql_select_db(dbase, "world")
'select * from city limit 0, 20'

Code Select Expand

and ct, 0
.While 1
mov edi, mysql_fetch_row(resTable)
.Break .if !edi ; 0=no row found
inc ct
Print Str$("\nrow %i", ct)
xor ecx, ecx
.Repeat
Print Tb$, [edi+4*ecx]
inc ecx
.Until ecx>=columns
.Endw

Output:

Code Select Expand

query performed, field_count=5
query stored, table has 5 columns and 20 rows

row 1   1       Kabul   AFG     Kabol   1780000
row 2   2       Qandahar        AFG     Qandahar        237500
row 3   3       Herat   AFG     Herat   186800
row 4   4       Mazar-e-Sharif  AFG     Balkh   127800
row 5   5       Amsterdam       NLD     Noord-Holland   731200
row 6   6       Rotterdam       NLD     Zuid-Holland    593321
row 7   7       Haag    NLD     Zuid-Holland    440900
row 8   8       Utrecht NLD     Utrecht 234323
row 9   9       Eindhoven       NLD     Noord-Brabant   201843
row 10  10      Tilburg NLD     Noord-Brabant   193238
row 11  11      Groningen       NLD     Groningen       172701
row 12  12      Breda   NLD     Noord-Brabant   160398
row 13  13      Apeldoorn       NLD     Gelderland      153491
row 14  14      Nijmegen        NLD     Gelderland      152463
row 15  15      Enschede        NLD     Overijssel      149544
row 16  16      Haarlem NLD     Noord-Holland   148772
row 17  17      Almere  NLD     Flevoland       142465
row 18  18      Arnhem  NLD     Gelderland      138020
row 19  19      Zaanstad        NLD     Noord-Holland   135621
row 20  20      ´s-Hertogenbosch        NLD     Noord-Brabant   129170

So I am happy with my code but I must say MySql is real crap. Archaic syntax, intransparent, and a memory hog. When you type a query in the workbench, it may freeze (while typing, not for the query!) your whole machine. With a whole lot of patience, you might eventually succeed to activate task manager and discover that it uses several gigabytes for its simple built-in "world" database.

P.S.:

the eax is pushed first on the stack and the edx is pushed second. This happens because parameters are pushed right to left in the stdcall and cdecl calling conventions. So your code takes the value of edx.

Attention, not everything that is published on the Internet is true :icon_mrgreen:

[aw27]

mySQL is probably the most widely used DB engine in websites. The memory usage can be configured.
I also installed it in my IIS website, because is better than the  Microsoft SQL Server, in my opinion.

However, for desktop applications, SQLite is much more "light" and does the job as well. I have also SQLite in my website managing a 250GB database of passwords and hashes used though the Online Super Tools menu of the site and it never failed. I use also SQLIte for the IP Database and it is extremely fast. I have also a public application and some private ones using SQLite. It seems that I like it then. 

[jj2007]

mySQL is probably the most widely used DB engine in websites.

And Mllions of flies love to eat chyte, so it's probably delicious 8)

The world database at C:\Program Files (x86)\MySQL\Samples and Examples 5.7\Sample Databases\World\world-schema.sql is just under 400kByte, but the workbench blows it up to several GIGAbytes, while typing the query. That is just lousy programming, nothing else. Over a Million hits for mysql workbench crashes windows world database ;)