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 (https://dev.mysql.com/doc/sakila/en/sakila-usage.html)
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 (https://stackoverflow.com/questions/8548622/mysql-check-table-and-number-of-rows-from-c) (doc (https://dev.mysql.com/doc/refman/5.7/en/mysql-num-rows.html))
.While 1
mov row$, mysql_fetch_row(resTable) ; no row found!
.Break .if !eax
PrintLine "row: [", row$, "]" ; no output...
.Endw
Output: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?
This is in UASM and 64-bit because I have no 32-bit mySQL here. Sorry.
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
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 :(
Quote from: jj2007 on September 02, 2017, 03:03:42 AM
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
Quote from: aw27 on September 02, 2017, 03:15:25 AMyou 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 ;)
Quote from: jj2007 on September 02, 2017, 04:47:49 AM
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. ;)
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 (http://masm32.com/board/index.php?topic=6504.msg69745#msg69745) nowadays ::)
Unless, of course, my favourite troll tells me now that qwords are returned in the lower half of xmm0 :P
You are so funny, JJ :biggrin:
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. :badgrin: :badgrin:
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:
SELECT username, user_password FROM `newworld_phpb1`.`phpbb_users` WHERE username = "fearless" AND user_password = "1746C58FB0B0305FDA550E548694095F"
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
@fearless
It may help JJ, my example was tested and works. :icon14:
Sorry, folks, will be offline for some time. Let's argue later...
Quote from: jj2007 on September 02, 2017, 07:27:47 PM
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:
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. :biggrin:
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' 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
.EndwOutput:
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 129170So 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.:
Quote from: aw27 on September 02, 2017, 06:08:11 PMthe 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:
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. :badgrin:
Quote from: aw27 on September 03, 2017, 02:14:38 AM
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 ;)
If you search the web you will find tips for reducing mySQL memory usage. If you do nothing it will grab as much as it can. :lol: