From mysql_* to mysqli_*

[ricardo arraes]

Hey everybody!

As some of you guys know, I developed a MySQL and MariaDB connection class (https://github.com/ricbarraes/TMySQL) applying some good practices about security...

But after a great point suggested by Charly, I realized that I'm using mysql_* functions to manipulate the database...
but what's the problem with that?

Well, the problem is that basically mysql_* functions are deprecated and to avoid troubles it is recommended to migrate it to mysqli_* functions...

https://en.wikipedia.org/wiki/MySQLi

This migration process is not THAT complicated, it's pretty simple (at least in theory...) but I'm stuck in some situations, and I'd like to ask you guys for some help!

right here I got a simple Query method, where I'm going to process my SELECT requests...

Código: Seleccionar todo

METHOD Query(cSQL, aParam)
   
   LOCAL oTable,nCount,nArq

   if ! Empty( ::pLib )
      ::hMySQL = mysql_init(::pLib)        
      
      if ::hMySQL != 0
         ::hConnection = mysql_real_connect( ::pLib,::cHost, ::cUser,IF(::cPsw # NIL,::cPsw, AP_GETENV( 'PASSWORD' )), ::cSchema, ::nPort, ::hMySQL )
         
         if ::hConnection != ::hMySQL
            nArq := FCreate("c:\xampp\htdocs\MySql.log")
            FWrite( nArq, mysql_error( ::hMySQL ) )
            FClose( nArq )
            RETURN NIL
         endif
      endif 

      IF .NOT. Empty(aParam)
         FOR nCount:=1 TO Len(aParam) 
            mysql_real_escape_string_quote(::pLib,::hConnection,@aParam[nCount])
            cSQL:=StrTran(cSQL,"PARAM"+StrZero(nCount,2),IF(Type(aParam[nCount]) # "C",IF(Type(aParam[nCount]) = "D", Dtoc(aParam[nCount]),hb_ValToStr(aParam[nCount])),aParam[nCount]))
         NEXT nCount
      ENDIF

      ::nRetVal := mysql_real_query( ::pLib, ::hConnection, cSQL )     
      //::nRetVal := mysql_query( ::pLib, ::hConnection, cSQL )     

      if ::nRetVal != 0
         nArq := FCreate("c:\xampp\htdocs\MySql.log")
         FWrite( nArq, "erro "+hb_ValToStr(::nRetVal)  )
         FClose( nArq )
         oTable:=NIL
      else
         nArq := FCreate("c:\xampp\htdocs\MySql.log")
         FWrite( nArq, "passou 1"  )
         FClose( nArq )
         oTable = MySqlTable():New(Self)
      endif 

      mysql_close(::pLib,::hConnection)
   else
      nArq := FCreate("c:\xampp\htdocs\MySql.log")
      FWrite( nArq, ::cLibName + " not available"  )
      FClose( nArq )
      RETURN NIL
   endif   
 RETURN oTable 

As you can see, I'm calling some mysql methods all over the code, so here are their declaration:

Código: Seleccionar todo

//----------------------------------------------------------------------------//
 
 function mysql_init(pLib)
 
 return hb_DynCall( { "mysqli_init", pLib, hb_bitOr( hb_SysLong(),;
                    hb_SysCallConv() ) }, NULL )
 
 //----------------------------------------------------------------//
 
 function mysql_close( pLib, hMySQL )
 
 return hb_DynCall( { "mysqli_close", pLib,;
                    hb_SysCallConv(), hb_SysLong() }, hMySQL )
 
 //----------------------------------------------------------------//
 
 function mysql_real_connect( pLib, cServer, cUserName, cPassword, cDataBaseName, nPort, hMySQL )
 
    if nPort == nil
       nPort = 3306
    endif   
 
 return hb_DynCall( { "mysqli_real_connect", pLib, hb_bitOr( hb_SysLong(),;
                      hb_SysCallConv() ), hb_SysLong(),;
                      HB_DYN_CTYPE_CHAR_PTR, HB_DYN_CTYPE_CHAR_PTR, HB_DYN_CTYPE_CHAR_PTR, HB_DYN_CTYPE_CHAR_PTR,;
                      HB_DYN_CTYPE_LONG, HB_DYN_CTYPE_LONG, HB_DYN_CTYPE_LONG },;
                      hMySQL, cServer, cUserName, cPassword, cDataBaseName, nPort, 0, 0 )
                      
 //----------------------------------------------------------------//
 
 function mysql_query( pLib, hConnect, cQuery )   
 
 return hb_DynCall( { "mysqli_query", pLib, hb_bitOr( HB_DYN_CTYPE_INT,;
                    hb_SysCallConv() ), hb_SysLong(), HB_DYN_CTYPE_CHAR_PTR },;
                    hConnect, cQuery )


function mysql_real_query( pLib, hConnect, cQuery )  

return hb_DynCall( { "mysqli_real_query", pLib, hb_bitOr( HB_DYN_CTYPE_INT,;
                   hb_SysCallConv() ), hb_SysLong(), HB_DYN_CTYPE_CHAR_PTR },;
                   hConnect, cQuery )                                        
                   
                   
function mysql_real_escape_string(pLib, hConnect, cQuery)
   
return hb_DynCall( { "mysqli_real_escape_string", pLib, hb_bitOr( hb_SysLong(),;
hb_SysCallConv() ), hb_SysLong(), HB_DYN_CTYPE_CHAR_PTR, HB_DYN_CTYPE_CHAR_PTR, HB_DYN_CTYPE_LONG },;
hConnect, @cQuery, cQuery,  Len(cQuery))

So I'm trying to execute a query like this:

"SELECT * FROM table "

Código: Seleccionar todo

App():oBD:Query("SELECT * FROM table",{})

(I'm not using any parameters, it's just for testing...)

So what happens here is this:

mysql_init() and mysql_real_connect() are working just fine, they are not displaying any error... So i guess the migration from mysql_* to mysqli_* worked well.

But when the mysql_real_query() method executes, it is returning NIL, and the documentation says that it was suppose to return true or false (1 or 0, i guess).
And now I just can't figure out what's wrong with the migration.

Does anyone have a clue?

[ramirezosvaldo]

Ricardo

Please test with mysql_query() , to see if the result is ok

[ricardo arraes]

Hey Osvaldo

I already did, and it's returning NIL as well...

mysql_query() and mysql_real_query() are not working.

That's how I called hb_DynCall to get these methods from the lib:

Código: Seleccionar todo

function mysql_query( pLib, hConnect, cQuery )   
 
 return hb_DynCall( { "mysqli_query", pLib, hb_bitOr( HB_DYN_CTYPE_INT,;
                    hb_SysCallConv() ), hb_SysLong(), HB_DYN_CTYPE_CHAR_PTR },;
                    hConnect, cQuery )


function mysql_real_query( pLib, hConnect, cQuery )  

return hb_DynCall( { "mysqli_real_query", pLib, hb_bitOr( HB_DYN_CTYPE_INT,;
                   hb_SysCallConv() ), hb_SysLong(), HB_DYN_CTYPE_CHAR_PTR },;
                   hConnect, cQuery )