EASY.SQL.2025.09.01 (A lot of new stuff!)

[Roberto Lopez]

Hi All,

Changes on this Version (2025.09.01):

- Modified: 'Select' method. Second parameter can now (optionally) be a reference to a
standard or hash array. This array (if specified) will receive the resulting
recordset in the corresponding format (standard or hash).
In addition, two new optional parameters, allows to specify a grid and window
names to automatically fill it with the resulting recordset. In this case,
the second parameter is optional.
If grid is specified an additional parameter can be specified (codeblocks array)
for formating.

Hash array example...

Code: Select all

hRows := {=>} ; hRow := NIL
oSql:Select('SELECT * FROM hmgtest ORDER BY code', @hRows)

Fill a grid with the result...

Code: Select all

FOR EACH hRow IN hRows
Test.Grid_1.AddItem({ StrZero(hRow["code"],4), AllTrim(hRow["description"]), AllTrim(hRow["location"]), AllTrim(Str(hRow["stock"])), AllTrim(Str(hRow["price"])) } )
NEXT

Direct to grid with no Second parameter example...

Code: Select all

oSql:Select('SELECT * FROM hmgtest ORDER BY code',, 'Grid_1', 'Test')

As previous with formatting...

Code: Select all

aFmt := { ;
	NIL, ; 
	{|x| STRZERO( x, 4) } , ; 
	{|x| UPPER( x ) } , ; 
	NIL, ; 
	NIL , ; 
	{|x| TRANSFORM( x ,'999,999.99' ) } ;
	}

oSql:Select('SELECT * FROM hmgtest ORDER BY code',,'Grid_1','Test',aFmt)

- New: 'nErrorNo' variable. Contains current error code.

- New: GetLastInsertId() method. Return last AUTO_INCREMENT on INSERT.

- Fixed: Various problems with error processing.

- Modified: Demo, showing new features.


This class must be considered EXPERIMENTAL.

My idea to start this library, was to have a wrapper for 'Simple SQL Interface' contribution by Mindaugas Kavaliauskas that I've used for many years.

This wrapper aims to have the following features:

- Handle all possible runtime error situations (bad parameters, connection, SQL syntax, etc.).

- Show all required progress information and error messages to the user.

- Handle simultaneous connections in an easy/intuitive way.


This is a very very basic documentation:


METHODS:

AffectedRows(): Returns the number of rows modified by the last executed
INSERT, UPDATE, or DELETE command.

CloseAreas(): Close all workareas open with 'Select' method (if any).
Returns: Number of workareas closed.

Commit(): Executes 'COMMIT' command.
Returns: .T. if command was successfully executed (.F. otherwise).

Connect(cServer, cUser, cPassword [, cDatabase]): Connect to a MySql server.
Returns: .T. if connection was successful (.F. otherwise).

Delete( cTable, cWhere ): Delete rows based on 'cWhere' condition.
Returns: .T. if command was successfully executed (.F. otherwise).

Destroy(): Set all object variables to NIL, closes all workareas used by
'Select' method (if any) and disconnects.
Returns: NIL

Disconnect(): Disconnect from server.
Returns: NIL

Exec(cCommand): Executes a MySql command.
Returns: .T. if command was successfully executed (.F. otherwise).

GetLastInsertId(): Returns last AUTO_INCREMENT on INSERT.

Insert(cTable, aCols): Insert row. aCols, specifies cColumnname => xData pairs (hash array).
Returns: .T. if command was successfully executed (.F. otherwise).

IsConnected(): Returns .T. if the current connection still alive, .F. if not.

New(): Object Initialization.
Returns: SELF (refers to the instance of the class that is being constructed).

Reconnect(). Attempts to re-connect using the current parameters.
Returns: .T. if command was successfully executed (.F. otherwise).

RollBack(): Executes 'ROLLBACK' command.
Returns: .T. if command was successfully executed (.F. otherwise).

Select(cCommand,[cWorkArea|@aArray|@hHash],[cGridName,cWindowName][,abFormats]):
cCommand must be a MySql SELECT statement.
If cWorkArea is specified a new workarea will be created containing the
query result. If you, instead, specifies a reference to an array
(or hash array), the resulting recordset will be stored there in the
corresponding format.
If cGridName and cWindowName are specified, a grid will be filled with the
resulting recordset. In this case, second parameter (cWorkArea, aArray or
hArray) is optional.
If grid is specified an additional parameter can be specified (codeblocks array)
for formating.
Returns: .T. if query was successful (.F. otherwise).

StartTransaction(): Executes 'START TRANSACTION' command.
Returns: .T. if command was successfully executed (.F. otherwise).

Update(cTable,cWhere,aCols): Modify rows based on 'cWhere' condition. aCols, specifies
cColumnname => xData pairs (hash array).
Returns: .T. if command was successfully executed (.F. otherwise).

Use(cDatabase): Selects the default database. Useful when connecting without
specifying a database or when working with multiple databases.
Returns: .T. if command was successfully executed (.F. otherwise).


VARIABLES:

cErrorDesc (Read Only): Error description (Empty if no error).

cMsgLang (Read Only): Current language for error/progress messages.

cNoQuoteChar: Set leftmost character indicator to instruct Update and Insert
methods to not quote a character value on aColumns
(cColumnName,xColumnValue) array (default value is '@').

lAutoReconnect: When set to .T. (the default value) operations that requires
connection to the server, will check if the connection still alive, if not,
reconnection will be attempted.

lError (Read Only): .T. if an error occured on last operation (.F. otherwise).

lShowMsgs: Determines if error/progress messages will be shown.

lTrace: When set to .T., a file called 'trace.log' containing last command
executed.

nErrorNo (Read Only) Current error code.


MariaDB:

Last years, for new projects (even local-data ones), I've used portable MariaDB.

It is compact, fast, reliable, secure and requires no installation.

You can download it at https://mariadb.org/download/ and select 'ZIP file' on
'Package Type'. You can unpack it at any place (<base_folder>).

For newer versions, prior to start MariaDB, you must initialize data directory:

<base_folder>\bin\mysql_install_db.exe --datadir=<base_folder>\data

Then you can run MariaDB:

<base_folder>\bin\mysqld.exe.

The default user is root with no password.

Best database utility: HeidiSQL (https://www.heidisql.com/)


I hope this be useful for someone.


Roberto.

[tonton2]

Bonjour,
j'ai installé MariaDB et j'ai aussi mysql , mais je n'arrive a créer la base de donnée hmgData .ça me donne :"connexion error", je dois avoir manqué quelque chose
Traduction Google
I installed MariaDB and I also have mysql, but I can't create the hmgData database. It gives me: "connection error", I must have missed something

[Roberto Lopez]

Bonjour,
j'ai installé MariaDB et j'ai aussi mysql , mais je n'arrive a créer la base de donnée hmgData .ça me donne :"connexion error", je dois avoir manqué quelque chose
Traduction Google
I installed MariaDB and I also have mysql, but I can't create the hmgData database. It gives me: "connection error", I must have missed something

Hi,

If do you have two installations (MariaDb and Mysql) that could be the problem...

Be sure that you have only one installation and then attempt to connect with Heidi (heidisql.com). If you can connect with Heidi you should be able to connect with the provided demo too.

[vagblad]

Bonjour,
j'ai installé MariaDB et j'ai aussi mysql , mais je n'arrive a créer la base de donnée hmgData .ça me donne :"connexion error", je dois avoir manqué quelque chose
Traduction Google
I installed MariaDB and I also have mysql, but I can't create the hmgData database. It gives me: "connection error", I must have missed something

You can have both installed and run both server instances at the same time but they should be running on different ports. So check your connection method for the correct port for either of them. Default port is usually 3306 for both of them.

[mol]

I need to insert pictures into database.
Do you have a good working method to do it?

[martingz]

Mol, i use this function, don't remember who is the autor

varfoto:=File_To_Buff(altaempleado.image_1.picture)
query:= "Update Empleados set foto=' " + varfoto + "' where id=" + str(varid)



Function File_To_Buff(cFile)
local cBuff:="", fh, nLen
local lRetVal:=.f.
local cFile_in := cFile
fh:=fopen(cFile_in,0)
if fh > -1
nLen:= fseek(fh, 0, 2)
if nLen > 0
fseek(fh, 0, 0)
cBuff:=space(nLen)
fread(fh, @cBuff, nLen)
cBuff:=strtran(cBuff, chr(92), "\\")
cBuff:=strtran(cBuff, chr(0), "\0")
cBuff:=strtran(cBuff, chr(39), "\'")
cBuff:=strtran(cBuff, chr(34), '\"')
endif
else
cBuff := ""
endif
fclose(fh)
Return(cBuff)

[mol]

Thanks, I'm working this way.
But I have a problem, application is written in CodePage 1250 and server is installed on Debian with Collate UTF8.
To transfer image content, I need to convert it to utf-8 string. It increases size of field about 1,5 times
I need to avoid this size increment

[srvet_claudio]

Hi Marek,
try convert image into string base64.

base64.hbp

Code: Select all

base64.prg
-lcrypt32

base64.prg

Code: Select all

#include "hmg.ch"

FUNCTION Main
   LOCAL cBase64, lOk

   // Convert file into Base64 string
   cBase64 := FileToBase64( "BosTaurus_logo.PNG" )

   IF Empty( cBase64 )
      MsgInfo( "Error converting to Base64" )
      RETURN NIL
   ENDIF

   MsgInfo( "Base64 generated (first 100 chars): " + Left( cBase64, 100 ) + "..." )

   hb_memowrit("BosTaurus_logo.txt", cBase64)

   // Rebuild file from Base64 string
   lOk := Base64ToFile( cBase64, "Rebuild.PNG" )

   IF lOk
      MsgInfo( "File rebuilt ok" )
   ELSE
      MsgInfo( "Error rebuilding file" )
   ENDIF

RETURN NIL


#pragma BEGINDUMP

#define UNICODE

#include <windows.h>
#include <wincrypt.h>
#include "hbapi.h"
#include "hbapiitm.h"
#include "hbapierr.h"
#include "hbmemory.ch"

// --- FILETOBASE64 ---
// Convert a binary file into a Base64 string
// Harbour: FileToBase64( <cFileName> ) -> <cBase64>
HB_FUNC( FILETOBASE64 )
{
   const char * filename = hb_parc( 1 );
   FILE * file = fopen( filename, "rb" );
   if( !file )
   {
      hb_retc( "" );
      return;
   }

   // Get file size
   fseek( file, 0, SEEK_END );
   long filesize = ftell( file );
   rewind( file );

   // Allocate buffer and read file
   BYTE * buffer = (BYTE*) hb_xgrab( filesize );
   fread( buffer, 1, filesize, file );
   fclose( file );

   DWORD base64Len = 0;

   // First call to get required size
   CryptBinaryToStringW( buffer, filesize, CRYPT_STRING_BASE64 | CRYPT_STRING_NOCRLF, NULL, &base64Len );
   WCHAR * base64W = (WCHAR*) hb_xgrab( base64Len * sizeof(WCHAR) );

   // Second call does the actual conversion
   if( CryptBinaryToStringW( buffer, filesize, CRYPT_STRING_BASE64 | CRYPT_STRING_NOCRLF, base64W, &base64Len ) )
   {
      // Convert wide string to UTF-8 (Harbour uses UTF-8 internally)
      int len = WideCharToMultiByte( CP_UTF8, 0, base64W, -1, NULL, 0, NULL, NULL );
      char * utf8 = (char*) hb_xgrab( len );
      WideCharToMultiByte( CP_UTF8, 0, base64W, -1, utf8, len, NULL, NULL );
      hb_retc( utf8 );
      hb_xfree( utf8 );
   }
   else
      hb_retc( "" );

   hb_xfree( base64W );
   hb_xfree( buffer );
}

// --- BASE64TOFILE ---
// Convert a Base64 string back to a binary file
// Harbour: Base64ToFile( <cBase64>, <cOutputFile> ) -> <lSuccess>
HB_FUNC( BASE64TOFILE )
{
   const char * base64 = hb_parc( 1 );
   const char * outname = hb_parc( 2 );
   DWORD binLen = 0;

   // Convert UTF-8 Harbour string to wide string
   int wideLen = MultiByteToWideChar( CP_UTF8, 0, base64, -1, NULL, 0 );
   WCHAR * base64W = (WCHAR*) hb_xgrab( wideLen * sizeof(WCHAR) );
   MultiByteToWideChar( CP_UTF8, 0, base64, -1, base64W, wideLen );

   // First call to get required binary size
   if( !CryptStringToBinaryW( base64W, 0, CRYPT_STRING_BASE64, NULL, &binLen, NULL, NULL ) )
   {
      hb_xfree( base64W );
      hb_retl( FALSE );
      return;
   }

   BYTE * buffer = (BYTE*) hb_xgrab( binLen );

   // Second call does the actual conversion
   if( !CryptStringToBinaryW( base64W, 0, CRYPT_STRING_BASE64, buffer, &binLen, NULL, NULL ) )
   {
      hb_xfree( base64W );
      hb_xfree( buffer );
      hb_retl( FALSE );
      return;
   }

   hb_xfree( base64W );

   // Write buffer to output file
   FILE * file = fopen( outname, "wb" );
   if( !file )
   {
      hb_xfree( buffer );
      hb_retl( FALSE );
      return;
   }

   fwrite( buffer, 1, binLen, file );
   fclose( file );
   hb_xfree( buffer );

   hb_retl( TRUE );
}

#pragma ENDDUMP

[mol]

Hi!
Converting to BASE64 increases size of field, eg. from 9605 bytes to 12808.
It works fine for sending file.

But, I'm still searching a way to insert file without increasing place occupation.
Maybe is there a method to send binary file to f***d postgresql server (I really don't like it, but my client is stubborn).

It's possible with python, but I don't want to mix harbour with python.

[mol]

Thank you, Claudio! You suggested me a working solution with Base64 encoding
Of course, your function to encode file will be very useful!

Schema of my idea - encode value to base64 string - it will be 1.5 times bigger than source, but it can be transferred to a server without worries about UTF8 encoding, special chars etc.
After that, we can save our content decoding it with built-in postgres function.

Code: Select all

cContent := hb_Base64Encode( hb_memoread("place.jpg") )
cSQL := "INSERT INTO images (Zawartosc) VALUES (decode('" + cContent + "','base64' ) ) RETURNING Image_ID"
aTmp := pgSelectQuery(oServerMaster,cSQL, .t.)
if empty(aTmp)
	MsgDebug("Error!")
	return
endif

// retriving file from server
nId := aTmp[1,1]
	
cSQL := "SELECT encode(Zawartosc::ByteA, 'base64') FROM  images WHERE image_id = " + hb_ntos(nId)
aTmp := pgSelectQuery(oServerMaster,cSQL, .f.)
	
if !empty(aTmp)
	hb_memowrit('Odpowiedz.jpg', hb_Base64Decode(aTmp[1][1]))
	execute file 'odpowiedz.jpg'
endif

Hope it will help someone