MySQL programming

Everything else that doesn't fall into one of the other PB categories.
Post Reply
Patate
New User
New User
Posts: 3
Joined: Sat Aug 21, 2004 6:16 pm

MySQL programming

Post by Patate »

Hi,

I'm trying to program an interface to MySQL and hit a bump.

I can't seem to be able to use the mysql_data_seek command from the libmysql.dll library. Everytime I use a callfunction on this command, all my variables in memory change values. Is anyone familiar with that command ?

To help you understand my problem, I summed up my code into this snippet which connects to my database and retrieve the value from the third field of a record.

In this code, if you put the dataseek command in comments your gonna end up with the first record and the word "Salut". Now as I understand it, if we used mysql_data_seek command with an offset of 0, we would end up with the same word. But instead, it just stop executing because it changes the value of mlMYSQL_RES.

Code: Select all

#libmysql=1
#LONG_SIZE=4
#BYTE_SIZE=1
*lCurLen.l = 0
*lRowData.l = 0


If OpenLibrary(#libmysql,"libmysql.dll")
  CallDebugger
  dbHnd.l=CallFunction(#libmysql,"mysql_init",dbHnd)
  CallFunction(#libmysql,"mysql_real_connect", dbHnd, "erdrick.is-a-geek.com", "purebasic", "purebasic", "rpg", 3306, 0)
  CallFunction(#libmysql, "mysql_real_query", dbHnd, "Select * from speech", Len("Select * from speech"))
  mlMYSQL_RES.l = CallFunction(#libmysql, "mysql_store_result", dbHnd)
  
  ;CallFunction(#libmysql, "mysql_data_seek", mlMYSQL_RES, 0)
  
  mlMYSQL_ROW.l = CallFunction(#libmysql, "mysql_fetch_row", mlMYSQL_RES)
  mlMYSQL_FIELD_LENGTHS.l = CallFunction(#libmysql, "mysql_fetch_lengths", mlMYSQL_RES)
  
  ;Starting from 0, so the third field in this case
  fieldnumber = 2
  length=PeekL(mlMYSQL_FIELD_LENGTHS+4*fieldnumber)
  fieldptr=PeekL(mlMYSQL_ROW+4*fieldnumber)
  content.s=PeekS(fieldptr,length)
  
  Debug content.s
  
EndIf
Now I noticed that the data_seek command uses an offset in the longlong format which is a integer stored on 64 bits, could it be the problem ? Wouldn't a hardcoded offset of 0 be the same with any numeric type ?

Thank you for your numerous replies :wink:
Top
Max.²
Enthusiast
Enthusiast
Posts: 175
Joined: Wed Jul 28, 2004 8:38 am

Post by Max.² »

Hope this helps, though I don't use mysql_seek_data. I'm not even sure if there is a somewhat meaningful sense for using it.

You should be able to apply the retrieval of the data to fit with mysql_seek_data really easily, if you need.

Else, this snippet returns the result structure, parses it and plots it. There also are just minor differences in mysql_store_result as mysql_use_result, both from coding as from speed.

The procedures and definitions should be in a seperate include.

Code: Select all

host.s    = "localhost"
user.s    = "testuser"
passwd.s  = "password"
db.s      = "PBdb"
port.l    = 3306

dbHnd.l
SQL.s
row.s
i.l
j.l
affRows.l
fieldNum.l
rowsNum.l



#libmysql = 1

#MySQL_CLIENT_COMPRESS = 32

Global CFF_MySQL_Init.l
Global CFF_MySQL_ERRNO.l
Global CFF_MySQL_ERROR.l
Global CFF_MySQL_Real_Connect.l
Global CFF_MySQL_Real_Query.l
Global CFF_MySQL_Store_Result.l
Global CFF_MySQL_Field_Count.l
Global CFF_MySQL_Use_Result.l
Global CFF_MySQL_Fetch_Row.l
Global CFF_MySQL_Fetch_Lengths.l
Global CFF_MySQL_Free_Result.l
Global CFF_MySQL_Close.l

Procedure MySQL_Init()

If OpenLibrary(#libmysql,"libmysql.dll")
  
  CFF_MySQL_Init = IsFunction(#libmysql,"mysql_init")
  CFF_MySQL_ErrNo = IsFunction(#libmysql,"mysql_errno")
  CFF_MYSQL_Error = IsFunction(#libmysql,"mysql_error")
  CFF_MySQL_Real_Connect = IsFunction(#libmysql,"mysql_real_connect")
  CFF_MySQL_Real_Query = IsFunction(#libmysql,"mysql_real_query")
  CFF_MySQL_Store_Result = IsFunction(#libmysql,"mysql_store_result")
  CFF_MySQL_Field_Count = IsFunction(#libmysql,"mysql_field_count")
  CFF_MySQL_Use_Result = IsFunction(#libmysql,"mysql_use_result")
  CFF_MySQL_Fetch_Row = IsFunction(#libmysql,"mysql_fetch_row")
  CFF_MySQL_Fetch_Lengths = IsFunction(#libmysql,"mysql_fetch_lengths")
  CFF_MySQL_Free_Result = IsFunction(#libmysql,"mysql_free_result")
  CFF_MySQL_Close = IsFunction (#libmysql,"mysql_close")


  
  ProcedureReturn CallFunctionFast (CFF_MySQL_Init,dbHnd)
Else
  Result = MessageRequester("Critical Error","libmysql.dll not found")
  End
EndIf

EndProcedure 

Procedure.s MySQL_GetError(db_ID,requester)

Protected Errormsg.s, i.l, Error.l
  
  If CallFunctionFast(CFF_MySQL_ErrNo,db_ID) > 0
    *Error =CallFunctionFast(CFF_MySQL_Error,db_ID)        
    Errormsg=PeekS(*Error)
 
    If requester
      Result= MessageRequester("MySQL error",Errormsg,#PB_MessageRequester_Ok)
    EndIf
  EndIf

  ProcedureReturn Errormsg

EndProcedure



Procedure MySQL_Real_Connect (dbHnd,host.s,user.s,password.s,db.s,port.l,options.l)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect,dbHnd, host, user, password.s, db, port, 0, options) 

EndProcedure

Procedure MySQL_Real_Query (dbHnd,SQL.s)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, SQL, Len(SQL))

EndProcedure

Procedure MySQL_Store_Result (dbHnd)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Store_Result,dbHnd)

EndProcedure

Procedure MySQL_Field_Count (dbHnd)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Field_Count,dbHnd)
  
EndProcedure

Procedure MySQL_Use_Result (dbHnd)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Use_Result,dbHnd)
  
EndProcedure

Procedure MySQL_Fetch_Row (*mysqlResult)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Fetch_Row,*mysqlResult)

EndProcedure


Procedure MySQL_Fetch_Lengths (*mysqlResult)

  ProcedureReturn CallFunctionFast (CFF_MySQL_Fetch_Lengths,*mysqlResult)         

EndProcedure

Procedure MySQL_Free_Result (*mysqlResult)

  ProcedureReturn CallFunctionFast(CFF_MySQL_Free_Result,*mysqlResult) 

EndProcedure

Procedure MySQL_Close (dbHnd)

  CallFunctionFast(CFF_MySQL_Close,dbHnd)

EndProcedure


dbHnd = MySQL_Init()
If MySQL_Real_Connect (dbHnd, host, user, passwd, db, port, 32) = 0
    
      MySQL_GetError(dbHnd,1)
    Else
      CallDebugger
      SQL = "SELECT * FROM Table"
      
      Result= MySQL_Real_Query (dbHnd, SQL) 

      If Result
        MySQL_GetError(dbHnd,1)
      Else
      
        *mysqlResult=MySQL_Use_Result (dbHnd)
        Debug *mysqlResult
          ;no result returned
        If *mysqlResult=0
          ;no fields returned means error
          If MySQL_Field_Count(dbHnd)
            MySQL_GetError(dbHnd,1)
          ;fields are returned, so no error but query didn't return data
          Else
            
          EndIf
        ;results are returned
        Else
          
          
          ;affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)
          fieldNum  = CallFunction(#libmysql,"mysql_num_fields",*mysqlResult)
          ;rowsNum   = CallFunction(#libmysql,"mysql_num_rows",*mysqlResult)

          Debug affRows
          Debug fieldNum
          Debug rowsNum

   
          Repeat 
            *mysqlRow=MySQL_Fetch_Row(*mysqlResult)
            If *mysqlRow<>0
            *mysqlLen=MySQL_Fetch_Lengths(*mysqlResult)         

            row = ""
              
            ;length of given field
            For j=1 To fieldNum 
              length=PeekL(*mysqlLen+4*(j-1))           
              fieldptr=PeekL(*mysqlRow+4*(j-1))
              If fieldptr>0
                content.s=PeekS(fieldptr,length)
              Else
                ;zero pointer returend means empty field  
                content="NULL"
              EndIf
              row = row + content + ";"
            Next j
            
            Debug row
            EndIf
          Until *mysqlRow = 0 
          
          result.l=MySQL_Free_Result(*mysqlResult) 
          MySQL_Close(dbHnd)
        EndIf
      EndIf
    EndIf
Top
Patate
New User
New User
Posts: 3
Joined: Sat Aug 21, 2004 6:16 pm

Post by Patate »

Thank you for your quick reply Max.²

http://dev.mysql.com/doc/mysql/en/mysql_data_seek.html

I would need mysql_data_seek to parse the results in memory so I could create functions like FindFirst, FindNext, etc... that would enable the user to find a specific record inside the results of a previous SQL command without having to lauch another SQL command.

Of course, I could easily bypass the mysql_data_seek by putting all the results in my own created structure, but I feel that it would be more efficient to use the command that does exactly that, a.k.a. mysql_data_seek.

Of course, if I were to create such a structure, I would use mysql_use_result as you suggested.

Thanks again for the tremendous response, but I would still like to have help on the mysql_data_seek problem.
Top
Patate
New User
New User
Posts: 3
Joined: Sat Aug 21, 2004 6:16 pm

Post by Patate »

Success !!!

After a few days of hard search, I've succeeded...

The problem was from within the libmysql library or from the fact that purebasic doesn't support longlong type.

All I had to do was add #define NO_CLIENT_LONG_LONG 1 at line 93 of mysql.h and recompile the dll. Libmysql.dll already had code in case the client didn't support longlong types and you needed that line to activate it.

So now, all the functions of libmysql are working 100% :D

Thank you for your support,

Long live purebasic :wink:

Patate
Top
cristian

do you share with us the code?

Post by cristian »

hello.
Do you share with us the code ? or the library?
Top
Post Reply

Return to “General Discussion”