'HMG EasySQL' a Simple SQL HMG library

[Roberto Lopez]

Hi All,

As I've recently commented, the last years I've been working primarily with MySql/MariaDB even for local/LAN-only projects.

I'm using portable MariaDB server. It is fast, compact and requires no configuration. You only must copy it to destination and simply works!.

I've started using rddsql contrib by Mindaugas Kavaliauskas, since it was the best choice for me (I want to have as much control as possible).

After finishing my first project, I've noticed that I could create some functions to make my code easier to write and maintain.

The worst part was 'construct' INSERT and UPDATE commands concatenating strings for tables with lots of columns.

With my functions, the code started to look like this:

Code: Select all

SqlConnect( blah, blah, blah)
SqlInsert(<table>)
SqlField(<name>,<value>)
SqlField(<name>,<value>)
SqlField(<name>,<value>)
SqlExec()

This worked fine, but it only allowed a connection/operation at a time, since design limitations.

So, I needed to upgrade to allow multiple connections at a time.

I've created a little class to handle that.

This is an usage example (CREATE TABLE):

Code: Select all

	WAIT WINDOW 'Processing...' NOWAIT	

	WITH OBJECT SQL():New()

		:Connect(_MYSQL_SERVER_, _MYSQL_USER_, _MYSQL_PASSWORD_, _MYSQL_DATABASE_)

		IF :lError
			WAIT CLEAR
			MsgStop(:cErrorDesc)
			RETURN
		ENDIF

		:Exec("DROP TABLE IF EXISTS hmgtest")

		IF :lError
			WAIT CLEAR
			MsgStop(:cErrorDesc)
			RETURN
		ENDIF

		:Exec("CREATE TABLE hmgtest (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code INT UNSIGNED UNIQUE, description CHAR(128), location CHAR(128), stock BIGINT UNSIGNED )")

		IF :lError
			WAIT CLEAR
			MsgStop(:cErrorDesc)
			RETURN
		ENDIF

		:Disconnect()

	END WITH

	WAIT CLEAR

To insert a row:

Code: Select all

	WAIT WINDOW 'Processing...' NOWAIT

	WITH OBJECT SQL():New()

		:Connect(_MYSQL_SERVER_, _MYSQL_USER_, _MYSQL_PASSWORD_, _MYSQL_DATABASE_)

		IF :lError
			WAIT CLEAR
			MsgStop(:cErrorDesc)
			RETURN
		ENDIF

		:Insert('hmgtest')

		:Field('code'        , nCode        )
		:Field('description' , cDescription )
		:Field('location'    , cLocation    )
		:Field('stock'       , nStock       )

		:Exec()

		IF :lError
			WAIT CLEAR
			MsgStop(:cErrorDesc)
			RETURN
		ENDIF

		:Disconnect()

	ENDWITH

	WAIT CLEAR

The library handles (or attempts to) all possible error types, hopefully, preventing crashes.

Here is the library (MUST BE CONSIDERED EXPERIMENTAL):

lib.sql.prg

Code: Select all

/*
 * 'HMG EasySQL' a Simple HMG library To Handle MySql/MariaDB 'Things'
 *
 * *** EXPERIMENTAL CODE ***
 *
 * Copyright 2024 Roberto Lopez <mail.box.hmg@gmail.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this software; see the file COPYING.txt.  If not, write to
 * the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307 USA (or visit the web site https://www.gnu.org/).
 *
 * As a special exception, the Harbour Project gives permission for
 * additional uses of the text contained in its release of Harbour.
 *
 * The exception is that, if you link the Harbour libraries with other
 * files to produce an executable, this does not by itself cause the
 * resulting executable to be covered by the GNU General Public License.
 * Your use of that executable is in no way restricted on account of
 * linking the Harbour library code into it.
 *
 * This exception does not however invalidate any other reasons why
 * the executable file might be covered by the GNU General Public License.
 *
 * This exception applies only to the code released by the Harbour
 * Project under the name Harbour.  If you copy code from other
 * Harbour Project or Free Software Foundation releases into a copy of
 * Harbour, as the General Public License permits, the exception does
 * not apply to the code that you add in this way.  To avoid misleading
 * anyone as to the status of such modified files, you must delete
 * this exception notice from them.
 *
 * If you write modifications of your own for Harbour, it is your choice
 * whether to permit this exception to apply to your modifications.
 * If you do not wish that, delete this exception notice.
 *
 */


#include 'hmg.ch'

#include 'common.ch'

#xcommand TRY => BEGIN SEQUENCE WITH { |e| break( e ) }
#xcommand CATCH [<!oErr!>] => RECOVER [USING <oErr>] <-oErr->
#xcommand FINALLY => ALWAYS

#include "lib.sql.ch"

#include "hbclass.ch"
#include "common.ch"

REQUEST SDDMY, SQLMIX

*--------------------------------------------------------------------------------------*
CLASS SQL 
*--------------------------------------------------------------------------------------*

	// data: 

	DATA	cCommandBuffer	
	DATA	cCommandWhere		
	DATA	nConnHandle	
	DATA	lError
	DATA	cErrorDesc

	// Methods: 

	METHOD New()
	METHOD Connect(cServer, cUser, cPassword, cDatabase)
	METHOD Use(cCommand,cWorkArea)
	METHOD Disconnect()

	METHOD Close(cWorkArea)
	METHOD Delete(cTable,cWhere)

	METHOD AffectedRows() 
	METHOD StartTransaction()
	METHOD Commit() 
	METHOD Rollback()
	METHOD Exec()

	METHOD Insert(cTable)
	METHOD Update(cTable,cWhere)
	METHOD Field(cField,xExpression,lRaw)

ENDCLASS

*--------------------------------------------------------------------------------------*
METHOD Close(cWorkArea) CLASS SQL
*--------------------------------------------------------------------------------------*

	CLOSE &cWorkArea

RETURN NIL

*--------------------------------------------------------------------------------------*
METHOD New() CLASS SQL
*--------------------------------------------------------------------------------------*

	::cCommandBuffer	:= ''
	::cCommandWhere		:= '' 
	::nConnHandle		:= 0
	::lError		:= .F.
	::cErrorDesc		:= ''

RETURN Self

*--------------------------------------------------------------------------------------*
METHOD Connect(cServer, cUser, cPassword, cDatabase) CLASS SQL
*--------------------------------------------------------------------------------------*

	::nConnHandle := RDDINFO(RDDI_CONNECT, {"MYSQL", cServer, cUser, cPassword, cDatabase }, "SQLMIX" ) 

	IF VALTYPE(::nConnHandle) <> 'N'

		::nConnHandle := 0

	ENDIF

	IF ::nConnHandle == 0
		::lError	:= .T.
		::cErrorDesc	:= 'Connection Error!'
	ENDIF

RETURN ::nConnHandle

*--------------------------------------------------------------------------------------*
METHOD Disconnect() CLASS SQL
*--------------------------------------------------------------------------------------*
LOCAL nRetVal
        
	nRetVal := RDDINFO( RDDI_DISCONNECT,,,::nConnHandle )

	IF valtype(nRetVal) <> 'N'
		nRetVal := 0
	ENDIF

RETURN nRetVal

*--------------------------------------------------------------------------------------*
METHOD Use(cCommand, cWorkArea) CLASS SQL
*--------------------------------------------------------------------------------------*
LOCAL oError

	TRY

		DBUSEAREA( .T.,"SQLMIX", cCommand, cWorkArea,,,,::nConnHandle )

		::lError	:= .F.

	CATCH oError

		::lError	:= .T.
		::cErrorDesc	:= oError:Description

	END

RETURN 

*--------------------------------------------------------------------------------------*
METHOD Field(cField,xExpression,lRaw) CLASS SQL
*--------------------------------------------------------------------------------------*
LOCAL cExpression


	DEFAULT lRaw TO .F.

	IF VALTYPE(xExpression) = 'D'
		cExpression := STRZERO(YEAR(xExpression),4) + '-' + STRZERO(MONTH(xExpression),2) + '-' + STRZERO(DAY(xExpression),2)
	ELSEIF VALTYPE(xExpression) = 'C'
		cExpression := alltrim(xExpression)
	ELSEIF VALTYPE(xExpression) = 'N'
		cExpression := alltrim(str(xExpression))
	ELSEIF VALTYPE(xExpression) = 'L'
		IF xExpression
			cExpression := '1'
		ELSE
			cExpression := '0'
		ENDIF
	ELSE
		::lError	:= .T.
		::cErrorDesc	:= 'SqlField: Expression Type Error!'
		RETURN
	ENDIF

	IF "'" $ cExpression 
		cExpression := StrTran(cExpression, "'" , "´")
	ENDIF

	IF lRaw

		::cCommandBuffer += cField + " = " + cExpression + ' ' + ','

	ELSE

		::cCommandBuffer += cField + " = " + "'" + cExpression + "'" + ','

	ENDIF

RETURN 

*--------------------------------------------------------------------------------------*
METHOD Insert(cTable) CLASS SQL
*--------------------------------------------------------------------------------------*

	::cCommandBuffer := ''
	::cCommandWhere  := ''

	::cCommandBuffer += "INSERT INTO " +  cTable + " SET " 		

RETURN

*------------------------------------------------------------------------------------------*
METHOD Delete(cTable,cWhere) CLASS SQL
*------------------------------------------------------------------------------------------*

	::cCommandBuffer := ''
	::cCommandWhere  := ''

	::cCommandBuffer += "DELETE FROM " +  cTable + " WHERE " + cWhere

	::Exec()

RETURN

*----------------------------------------------------------------------------------------------
METHOD AffectedRows() CLASS SQL
*----------------------------------------------------------------------------------------------
LOCAL nRetVal
        
	nRetVal := RDDINFO( RDDI_AFFECTEDROWS,,,::nConnHandle )

	IF valtype(nRetVal) <> 'N'
		nRetVal := 0
	ENDIF

RETURN nRetVal

*------------------------------------------------------------------------------------------*
METHOD StartTransaction() CLASS SQL
*------------------------------------------------------------------------------------------*

	::cCommandBuffer := ''
	::cCommandWhere  := ''

	::cCommandBuffer += "START TRANSACTION" 		

	::Exec()

RETURN

*------------------------------------------------------------------------------------------*
METHOD Commit() CLASS SQL
*------------------------------------------------------------------------------------------*

	::cCommandBuffer := ''
	::cCommandWhere  := ''

	::cCommandBuffer += "COMMIT" 		

	::Exec()

RETURN

*------------------------------------------------------------------------------------------*
METHOD Rollback() CLASS SQL
*------------------------------------------------------------------------------------------*

	::cCommandBuffer := ''
	::cCommandWhere  := ''

	::cCommandBuffer += "ROLLBACK" 		

	::Exec()

RETURN

*------------------------------------------------------------------------------------------*
METHOD Update(cTable,cWhere) CLASS SQL
*------------------------------------------------------------------------------------------*

	::cCommandBuffer := ''

	::cCommandWhere := cWhere

	::cCommandBuffer += "UPDATE " +  cTable + " SET " 		

RETURN

*--------------------------------------------------------------------------------------*
METHOD Exec(cCommand) CLASS SQL
*--------------------------------------------------------------------------------------*
LOCAL cRDD

	cRDD := RDDSETDEFAULT()

	RDDSETDEFAULT("SQLMIX")

	IF ValType(cCommand) = 'U' 

		IF Right( ::cCommandBuffer , 1 ) == ','

			::cCommandBuffer := LEFT( ::cCommandBuffer , LEN(::cCommandBuffer) - 1 )

		ENDIF

		IF .NOT. Empty(::cCommandWhere) 

			::cCommandBuffer += 'WHERE ' + ::cCommandWhere

		ENDIF

		::lError := .NOT. RDDINFO( RDDI_EXECUTE, ::cCommandBuffer , , ::nConnHandle )

		HB_MEMOWRIT( 'trace.log' , ::cCommandBuffer , .f. )

	ELSE

		HB_MEMOWRIT( 'trace.log' , cCommand , .f. )

		::lError := .NOT. RDDINFO( RDDI_EXECUTE, cCommand, , ::nConnHandle )

	ENDIF

	IF ValType(::lError) <> 'L'
		::lError := .T.
	ENDIF

	IF ::lError
		::cErrorDesc := rddinfo(RDDI_ERROR,,,::nConnHandle )
	else
		::cErrorDesc := ''
	ENDIF

	::cCommandBuffer := ''

	RDDSETDEFAULT(cRDD)

RETURN 

lib.sql.ch

Code: Select all

#define RDDI_CONNECT          1001
#define RDDI_DISCONNECT       1002
#define RDDI_EXECUTE          1003
#define RDDI_ERROR            1004
#define RDDI_ERRORNO          1005
#define RDDI_NEWID            1006
#define RDDI_AFFECTEDROWS     1007
#define RDDI_QUERY            1008

Attached to this message is the library with a full example.

All this thing must be considered experimental.

I hope it be useful for someone.

[serge_girard]

Thanks for sharing Roberto !

[ASESORMIX]

Saludos Sr. Roberto.

Muchísimas Gracias.

[edk]

Many thx, Roberto.

[vagblad]

Thank you Roberto. Much appreciated.

[mol]

Thanks for sharing!