HMGSQL

SQL Bridge for MySQL, PostgreSQL, SQLite

HMG SQL is a collection of SQL RDBMS bridges for MySQL, PostgreSQL and SQLite database.

The main advantages of HMGSQL are:

1. It eliminates the differences of connection procedures and query execution of each RDBMS package.

2. It reduces change in the HMG codes when we want to switch over from one RDBMS to another RDBMS package.

3. All SQL activities are consolidated to a minimum number of functions which in-turn reduces the number of lines of coding in your programs a lot.

Usage of HMGSQL:

The following are the functions used in all the three libraries (viz., HMGMySQL,HMGPGSQL, HMGSQLite) for the purposes mentioned.

1. Connect2DB() -> oDBO - This is used to connect to the RDBMS database. This function returns the DataBase Connection Object which can be used in all other SQL activities. Even though the function name is the same, parameters passed to this function differs according to the RDBMS package chosen. This is explained below:

1. HMGMySQL – Syntax: Connect2DB(cHost,cUser,cPassword,cDBname).

Example: connect2db('localhost','user1','pass1','productdb')

2. HMGPGSQL – Syntax: Connect2DB(cHost,cUser,cPass,cDb,nPort). Default nPort is 5432.

Example:connect2db('localhost','user1','pass1','productdb',5432)

3. HMGSQLite – Syntax: Connect2DB(cDBname,lCreate).

Example: connect2db('c:\mydata\db.sqlite3',.t.)

2. SQL(oDBO,cQStr) –> aTable - This is used to execute only the “SELECT” type queries. This function returns a two dimensional array of the query result (even though the resultant number of rows and columns be 1). An empty array will be returned in case there are no rows returned.

Example: aTable := SQL(dbo,”select * from products order by name”)

3. MiscSQL(oDBO,cQStr) -> lOk – This is used to execute all the other miscellaneous commands like “Insert, Delete, Update, Create, Alter, Grant” etc., except “Select”. This function returns a logical value. If the query execution was successful, .t. is returned. Otherwise .f. is returned.

Example: lOk := MiscSQL(dbo,”insert into products values (1,'Product1')”)

4. C2SQL(xValue) -> xValue – This function is used to parse the values to be SQL safe which can be used in any query in-line. This function returns the parsed value of the value passed.

Example: aTable := SQL(dbo,”select * from ledger where date <= “+c2sql(date()))

5. CloseDB(oDBO) -> Nil – This function is used only in the case of PostgreSQL database connection. This is used to close the database connection.

Example: closedb(dbo)

Author:

S. Rathinagiri <srgiri@dataone.in>