I'm a little bit away from the forum these days... 2021 has come with a lot of work (thanks God!), but now I'm here to leave some contribution to our community!
TMySQL is a class created by me (based on WDO and ORM) which allows the application to properly connect to a MySQL/MariaDB database .
simple as that...
In order to use TMySQL at you application, you must build it inside mercury. it means that you only have to save it inside the mercury/lib directory, include it at the mercury.prg and build mercury.
*Charly is analyzing TMySQL, possibly, in the future, WDO will have all this features as well
After rebuilding mercury, TMySQL will be a native class and all you gotta do is create some environment variable inside you .htaccess file like this:
SetEnv SGBD "MARIA"
SetEnv HOST "localhost"
SetEnv USER "user"
SetEnv PASSWORD "psw"
SetEnv SCHEMA "schema"
*SGBD must be MARIA or MYSQL
These environment variables will allow you to call App():oBD which is your application's connection object. I'll explain it's methods later in this post.
*If you need to create another connection, all you gotta do is call a TMySQL:New(cSgbd, cHost, cUser, cPsw, cSchema, nPort) with your connection parameters
Well, we all know that security is a requirement when we talk about the web, so when I created this class I implemented some good practices that are useful and can help you improve the security of you application.
I'll explain theses good practices right now:
Never insert your parameters directly at your query
Inserting your parameters at your query like this:
"Select * from table where '"+param01+"'"
without "sanitizing" your parameters, is not considered a good practice, because it exposes your application to many threats.
TMySQL has a soution to that:
1. Requesting data
We can use the Query method, like that:
Código: Seleccionar todo
LOCAL oTable, aParam:={}
Aadd(aParam,"x")
Aadd(aParam,"y")
oTable:=App():oBD:Query("Select * from table where field1='PARAM01' AND field2='PARAM02'",aParam)
Código: Seleccionar todo
METHOD Query(cSQL, aParam)
LOCAL oTable,nCount
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
? "Error on connection to server " + ::cHost,::cSchema,::cUser,::hConnection , ::hMySQL, ::cLibName, mysql_error(::pLib,::hMySQL)
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 )
if ::nRetVal != 0
oTable:=NIL
else
oTable = MySqlTable():New(Self)
endif
mysql_close(::pLib,::hConnection)
else
? ::cLibName + " not available"
RETURN NIL
endif
RETURN oTable
1. Manipulating data
Manipulating data is just as simple as requesting it, but the difference here is that we can process as many queries as we want with the same connection.
Código: Seleccionar todo
LOCAL aParam:={}, aQuery:={}
AAdd(aParam,{"x","y"})
AAdd(aParam,{"a","b","c"})
Aadd(aQuery, "DELETE FROM table WHERE field1='PARAM01' AND field2='PARAM02'")
Aadd(aQuery, "INSERT INTO table (field1, field2, field2) "+;
"VALUES ('PARAM01','PARAM02','PARAM03')")
App():oBD:Exec(aQuery,aParam)
Código: Seleccionar todo
METHOD Exec( aSQL, aParam )
LOCAL nArq, nCount, nCountCol
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
//? "Error on connection to server " + ::cHost,::hConnection , ::hMySQL
RETURN NIL
endif
endif
FOR nCount:=1 TO Len(aSQL)
IF aParam # NIL .AND. Len(aParam[nCount]) > 0
FOR nCountCol:=1 TO Len(aParam[nCount])
mysql_real_escape_string_quote(::pLib,::hConnection,@aParam[nCount,nCountCol])
aSQl[nCount]:=StrTran(aSQL[nCount],"PARAM"+StrZero(nCountCol,2),IF(Type(aParam[nCount,nCountCol]) # "C",IF(Type(aParam[nCount,nCountCol]) = "D", Dtoc(aParam[nCount,nCountCol]),hb_ValToStr(aParam[nCount,nCountCol])),aParam[nCount,nCountCol]))
NEXT nCountCol
ENDIF
mysql_real_query( ::pLib, ::hConnection, aSQL[nCount] )
NEXT nCount
if mysql_error( ::hMySQL ) # NIL
nArq := FCreate("MySql.log")
FWrite( nArq, mysql_error( ::hMySQL ) )
FClose( nArq )
endif
mysql_close(::pLib,::hConnection)
else
//? ::cLibName + " not available"
RETURN NIL
endif
RETURN NIL
TMySQL also has a method that allow us to convert a JSON object into a MySQLTable object, making it easier to exchange data between web applications or webservices.
Código: Seleccionar todo
METHOD JsonToTable(hJson)
LOCAL oTable
oTable = MySqlTable():New(Self,hJson)
RETURN oTable
Código: Seleccionar todo
METHOD New( oOrm, hJson ) CLASS MySQLTable
local n, m, hField, hRow
::Super:New( oOrm)
IF hJson = NIL
::hMyRes = mysql_store_result( oOrm:pLib, oOrm:hConnection )
if ::hMyRes == 0
? "mysql_store_results() failed"
else
::aFields = Array( mysql_num_fields( oOrm:pLib, ::hMyRes ) )
for n = 1 to Len( ::aFields )
hField = mysql_fetch_field( oOrm:pLib, ::hMyRes )
if hField != 0
::aFields[ n ] = Array( 4 )
::aFields[ n ][ 1 ] = PtrToStr( hField, 0 )
do case
case AScan( { 253, 254, 12 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
::aFields[ n ][ 2 ] = "C"
case AScan( { 1, 3, 4, 5, 8, 9, 246 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
::aFields[ n ][ 2 ] = "N"
case AScan( { 10 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
::aFields[ n ][ 2 ] = "D"
case AScan( { 250, 252 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
::aFields[ n ][ 2 ] = "M"
endcase
endif
next
::aRows = Array( mysql_num_rows( oOrm:pLib, ::hMyRes ), ::FCount() )
for n = 1 to Len( ::aRows )
if ( hRow := mysql_fetch_row( oOrm:pLib, ::hMyRes ) ) != 0
for m = 1 to ::FCount()
::aRows[ n, m ] = PtrToStr( hRow, m - 1 )
next
endif
next
endif
ELSE
::aFields:= hJson['header']
::aRows := hJson['body']
ENDIF
return Self
You can find TMySQL right here:
https://github.com/ricbarraes/TMySQL