EASY.SQL.2025.08.08 [EDIT]

[Roberto Lopez]

Hi All,

Changes on this Version (2025.08.08)

- Fixed: AffectedRows() method not working when 'SQLMIX' is not the default RDD.

- Fixed: Disconnect() method not working when 'SQLMIX' is not the default RDD.


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

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

You can download it at https://mariadb.org/download/ and select 'ZIP file' on
'Package Type'. You can unpack it at any place and run <base_folder>\bin\mysqld.exe.
The default user is root with no password. I'm using 5.5 version since it is enough
for my needs (*).

This class must be considered EXPERIMENTAL.

My idea to start this small 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.

(*): On later MariaDB versions, before running mysqld.exe you must initialize data directory, ie: mysql_install_db.exe --datadir=C:\mariadb-10.11.14-winx64\data (or whatever you want to be your data directory). Then, create the database 'test'.

This is a very very basic documentation:


METHODS:


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


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


Select(cSelect,cWorkArea): cSelect must be a MySql SELECT statement.
A recordset is created with the name specified as cWorkArea.
Returns: .T. if query was successful (.F. otherwise).


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


Disconnect(): Disconnect from server.
Returns: NIL


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


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


Delete( cTable, cWhere ): Delete rows based on 'cWhere' condition.
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).


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


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


VARIABLES:


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

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

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

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

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 '@').


I hope this be useful for someone.

Roberto.

[serge_girard]

Thanks for sharing!

[mol]

THX, Roberto!

Starting MariaDB portable is described under this link https://www.mariadb.education/install-portable

But I can't connect - Test.exe shows error: Error de Conexion

Resolved!
I've connected with HeidiSQL and I've created database TEST.
After that, everything works OK!

[Roberto Lopez]

THX, Roberto!

Starting MariaDB portable is described under this link https://www.mariadb.education/install-portable

But I can't connect - Test.exe shows error: Error de Conexion

Resolved!
I've connected with HeidiSQL and I've created database TEST.
After that, everything works OK!

Hi!

If you use mariadb 5.5 (zip file distribution) the demo provided works without changes.

For newer versions, before running mysqld.exe you must initialize data directory, ie: b]mysql_install_db.exe --datadir=C:\mariadb-10.11.14-winx64\data[/b] (or whatever you want to be your data directory). Then, create the database 'test'.

As I've said, 5.5 version is enough for me, so, I've not tested with newer versions and I was not aware of the needed changes to run the demo..

Thanks for the advice. I've edited the post and added this info.

[mol]

I don't know if ver.5.5 is still accessible. I've downloaded newest version frowm mainpage of mariadb. The most important is that's running well in this newest version!
Thanks for sharing your great work

[Roberto Lopez]

I don't know if ver.5.5 is still accessible. I've downloaded newest version frowm mainpage of mariadb. The most important is that's running well in this newest version!
Thanks for sharing your great work

And...

I'm changing the demo since the language is set to spanish and the library messages are shown in spanish.

Code: Select all

/*
 * 'HMG EasySQL' Demo
 *
 * *** EXPERIMENTAL CODE ***
 *
 * 2025 Roberto Lopez <mail.box.hmg@gmail.com>
 *
 */

#include <hmg.ch>

#include <common.ch>

Function Main()

	* Must set the language for adequate library information / messages
	
	* SET LANGUAGE TO SPANISH 

	PUBLIC _MYSQL_SERVER_	:= alltrim( memoread('sql.server.dat') )
	PUBLIC _MYSQL_USER_	:= alltrim( memoread('sql.user.dat') )
	PUBLIC _MYSQL_DATABASE_	:= alltrim( memoread('sql.database.dat') )
	PUBLIC _MYSQL_PASSWORD_	:= descend( alltrim( memoread('sql.password.dat') ) )

        Load Window Main

        Main.Center

        Main.Activate

Return

The other thing is that in the [Create Table] option in main window I must force connection params setting (if were not defined yet) and warn the user about that the database to use must exist prior to attempt a connection.

Thanks for pointing me these issues.

[Steed]

I get , the next error, when i compile test sample:

undefined reference to `HB_FUN_SDDMY'