MYSQL example

[stanks]

Hi

Here is my simple example on how to connect to MYSQL server with simple database. I will only show how to fetch some data. Maybe i will have time to make some examples on storing data, etc. (it can't be simpler then simply sendind SQL query for storing/updating data with MYSQL API function mysql_real_query...i didn't check that...will later) In attach file you have headers which are converted to asm headers with my little knowledge of C, help from this forum and tool from japheth :) and RadASM project example.

First i connect to db and create a schema named test. Under this schema i created one table with name mysql-asm-test.



Now we fill some data into this table:



Now to code:

Code Select Expand


.data
szServer byte "winblowz",0 ;-> here you use your server name
szAdminUser byte "admin",0 ;-> here you use your user name
szAdminPass byte "password",0 ;-> here you use your name password
szDB byte "test",0 ;-> here your database name
szErr byte "Error!",0
szFmtError byte "%s",0 ;-> format error strng returned with mysql_error function
szQueryEverything byte "SELECT * FROM `test`.`mysql-asm-test`",0

...
.data?
...
mysql MYSQL <> ; MYSQL structure
conn dword ?
szError byte 512 dup(?)


AdminConnect2MYSQL proc hDlg:DWORD
invoke mysql_init,addr mysql
mov conn,eax
.if eax==NULL
; insufficient memory
; do what you want :)
.endif
invoke mysql_real_connect,conn,addr szServer,addr szAdminUser,addr szAdminPass,addr szDB,0,NULL,0
.if eax==NULL
; error connecting to server
invoke mysql_error,conn
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hDlg,addr szError,addr szErr,MB_ICONERROR
jmp exit_1
.endif
  exit_1:
ret
AdminConnect2MYSQL endp


Now when we are connected (if we are not msg box will show you the msg error) we can send queries :)

Code Select Expand


...
invoke mysql_real_query,conn,addr szQueryEverything,sizeof szQueryEverything
.if eax!=0
; SELECT statement is wrong
...
.endif
invoke mysql_use_result,conn
.if eax==NULL
; Error
...
.endif
; save result ptr
mov res,eax
.while TRUE
; take every row
invoke mysql_fetch_row,res
mov ebx,eax
test ebx,ebx
; if ebx is 0 then no more rows
jz exit_2
; if we set * in SELECT then we will get value from every field
; so if we have 5 fields, we have 5 values
; if we write only eg. 2 fields (col3 and col4 in SELECT statement) we will get only
; these 2 fileds value from every row
; ebx is first field value, ebx+4 second, ebx+8 third, etc.
; print everything (value of every field) on screen
invoke wsprintf,addr lpOut1,addr lpOutFmt,dword ptr [ebx],dword ptr [ebx+4],dword ptr [ebx+8],dword ptr [ebx+12],dword ptr [ebx+16]
invoke SendMessage,hEdit,EM_SETSEL,-1,0
invoke SendMessage,hEdit,EM_REPLACESEL,FALSE,addr lpOut1
.endw
  exit_2:
  ; free result
invoke mysql_free_result,res
; close connection
invoke mysql_close,conn
...

This is the end. It's not much but it works :-D
Check example code.

stanks

Example code: http://rapidshare.com/files/107775139/mysql-test.zip
I don't have a homepage and this is 1.8MB zip file (because of lib files)

P.S. Put mysqlclient.inc and libmysql.inc into masm include dir, and mysqlclient.lib/libmysql.lib into masm lib dir. These files are in main project dir.

[stanks]

MYSQL version is latest...5.x

[fearless]

Nicely done,

ive been looking for an example for a while, this is just the start i need to get going.

Thanks for posting your findings.

Cheers

[modchip]

Thank you very much! I've been looking for something like this for a long long time... :D

[hutch--]

stanks,

This might be senile decay setting in but I fiddled the source so it would build in masm32 and it builds OK. With any errors in server name, password, dbname etc... it shows the dialog but when all are correct the dialog does not show. What have I messed up ?

[stanks]

stanks,

This might be senile decay setting in but I fiddled the source so it would build in masm32 and it builds OK. With any errors in server name, password, dbname etc... it shows the dialog but when all are correct the dialog does not show. What have I messed up ?

Main win dialog doesn't show up? I really don't know where is the problem  ::) I have your last version of masm package, so maybe you add something, remove something, or...something
Maybe i didn't remove some int 3 from code. Check includes too.

[stanks]

I have tested this example on another win machine and it works ok. I don't know where is the problem hutch  :(

[hutch--]

stanks,

I am running win2000 sp4, would that matter ?

I changed these .DATA section variables in the code so they matched the MySQL setup I have on this dev box.

Code Select Expand


szServer byte "localhost",0
szAdminUser byte "myadmin",0
szAdminPass byte "mypassword",0
szDB byte "smf",0
szErr byte "Error!",0
szErrRes byte "Result error!",0
szFmtError byte "%s",0
; queries
szQueryEverything byte "SELECT * FROM `smf`.`smf_sessions`",0


LATER:

Stanks,

if fails here in the wsprintf statement. I have been testing at against an SMF mysql database that I use for a local setup of the forum. I wonder what format in the table is required noting that you use a large number of addresses based on EBX ? With the three lines commented out the dialog appears like normal.

Code Select Expand


Query1 proc uses ebx hDlg:DWORD
LOCAL lpOut1[255]:BYTE
LOCAL lpOut2[255]:BYTE

invoke SendMessage,hEdit,EM_SETSEL,0,-1
invoke SendMessage,hEdit,WM_SETTEXT,TRUE,0
invoke mysql_real_query,conn,addr szQueryEverything,sizeof szQueryEverything
.if eax!=0
; SELECT statement is wrong
invoke mysql_error,conn
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hDlg,addr szError,addr szSELErr,MB_ICONERROR
jmp exit_2
.endif
invoke mysql_use_result,conn
.if eax==NULL
; Error
invoke mysql_error,conn
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hDlg,addr szError,addr szErrRes,MB_ICONERROR
jmp exit_2
.endif

;;; jmp exit_2

mov res,eax
.while TRUE
; take every row
invoke mysql_fetch_row,res
mov ebx,eax
test ebx,ebx
jz exit_2
; if we set * in SELECT then we will get value from every field
; so if we have 5 fields, we have 5 values
; if we write only eg. 2 fields (col3 and col4 in SELECT statement) we will get only
; these 2 fileds value from every row
; ebx is first field value, ebx+4 second, ebx+8 third, etc.
; print everything on screen
; invoke wsprintf,addr lpOut1,addr lpOutFmt,dword ptr [ebx],dword ptr [ebx+4],dword ptr [ebx+8],dword ptr [ebx+12],dword ptr [ebx+16]
; invoke SendMessage,hEdit,EM_SETSEL,-1,0
; invoke SendMessage,hEdit,EM_REPLACESEL,FALSE,addr lpOut1
.endw
  exit_2:
  ; free result
invoke mysql_free_result,res
; close connection
invoke mysql_close,conn

ret
Query1 endp


LATER AGAIN:

I can get a result with the modification of the above procedure as follows.

Code Select Expand


.while TRUE
; take every row
invoke mysql_fetch_row,res

            pushad
              .if eax != 0
                print [eax],13,10
              .endif
            popad


[stanks]

hmm...maybe this will help you. Stupid question, but...did you try to print every field, or some fields? If you SELECT * FROM ... then you will get values from every field (10 fields, 10 values). If you SELECT field4, field2 FROM ... So for first example ascii values will be in ebx, ebx+4, ... In second example ebx will hold value from field4 and ebx+4 will hold value of field2. MYSQL returns ascii values. If you want numbers for fields of datatype number you will, of course, convert ascii to number. This also means that you have to change lpOutFmt variable. If you want 4 fields then use only 4 %s. If you want all fields and count of this is 10 then you will change lpOutFmt to 10 %s.
I don't have any win2k machine to test this  :( so i really don't know where would be the problem. Maybe someone from forum can help too if have win2k.
Everything works fine on XP SP2 (2 machines that i tested this code have this OS).

[hutch--]

I have got the exe to work and run OK so its not an OS version problem, only a database format error I am getting.

[Draakie]

Thanx Stanks for posting this.... and I did'nt even have to spam u're PM box to get it  :bdg

Could some-one please make the source (ZIP) available some-place else. Rapidshare sux.

Please please please please please Please.

Draakie

[thomas_remkus]

Do you have something similar to connect to a Microsoft SQL database? ADO is such a pain.

[stanks]

I don't know ADO. Seems to complicated to me, but when i search these forums i've seen some posts about ADO.

Here is the source only (no lib files...you can install it when you install MYSQL server)


[attachment deleted by admin]

[Roberto]

can anyone tell me how to set up radasm for use with mysql. I mean is there a mysql.dll and additional lib file for it?

[Roberto]

I saw the link in previous post, damn i'm getting old and blind lol