Unfortunately, different DBMSs implement different syntax and when we develop cross-platform application we need to write different SQL statements for differnet DBMSs.
Most common are INSERT and UPDATE statements. It is not uncommon to see postings from new entrants asking how to format numerics, dates, etc to be used in such statements.
Let us consider the simplest case of INSERT statement.
We have data in the Harbour vaiables, cCustomer, cStreet, dInvDate, nInvNo, tDeliveryTime, nQty, nAmount.
We want to insert a row, assigning these values to fields CUSTOMER, STREET, INVDATE, INVNO, DELYTIME, QTY and AMOUNT into SUPPLIES table
We would be very happy to write a statement a like this, if allowed:
- Code: Select all Expand view
INSERT INTO SUPPLIES ( CUSTOMER,STREET,INVDATE,INVNO,DELYTIME,QTY,AMOUNT ) ;
VALUES ( cCustomer, cStreet, dInvDate, nInvNo, tDeliveryTime, nQty, nAmount )
But we know this is not permitted. We need to convert all values into literals that the DBMS can understand.
So, we proceed like this:
cSql := "INSERT INTO SUPPLIES ( CUSTOMER,STREET,INVDATE,INVNO,DELYTIME,QTY,AMOUNT ) VALUES ( " + ;
"'" + cCustomer + "','" + cStreet + "'.'" + Str(Year(dInvDate,4) + etc, etc, etc
and finally produce a statement like this
INSERT INTO SUPPLIES ( CUSTOMER,STREET,INVDATE,INVNO,DELYTIME,QTY,AMOUNT ) ;
VALUES ( 'Anthony', 'Barry's Street', '2013-07-05', 2034, '2013-07-03T15:20:35', 234.567, 10257.89 )
Even this is quite tedious.
This again has error because single quote is not escaped. We change 'Barry's Stret' as 'Barry''s Street' and then this works on MSSQL server.
Now this statement does not work on MySql. Oracle. etc.
We need to code the statement separately for other DBMSs in a conditional if else endif block.
Support provided by FWH:
FWH provides commands which allow us to write the code as we like to using Harbour variables. Please see the first code snippet.
These commands are provided in \fwh\include\fwsqlcmd.ch, which in turn is included in \fwh\include\adodef.ch and ado.ch.
So, we can write code like this:
- Code: Select all Expand view
#include "fivewin.ch"
#include "adodef.ch"
function Main()
local oCn, cSql
oCn := FW_OpenAdoConnection( "xbrtest.mdb" )
cSql := SQL INSERT INTO SUPPLIES ( CUSTOMER,STREET,INVDATE,INVNO,DELYTIME,QTY,AMOUNT ) ;
VALUES ( cCustomer, cStreet, dInvDate, nInvNo, tDeliveryTime, nQty, nAmount )
? cSql
// oCn:Execute( cSql )
oCn:Close()
return nil
When we connect to MSACCESS, this is the SQL statement generated by the command
// MSACCESS
INSERT INTO SUPPLIES ( [CUSTOMER],[STREET],[INVDATE],[INVNO],[DELYTIME],[QTY],[AMOUNT] )
VALUES
( 'Anthony','Barry''s Street','2013-07-05',2034,'2013-07-03 15:20:55',234.567,10257.89 )
Instead of connecting to access table, if we connect to a different DBMS, the same
statement produces different appropriate SQL statements as below:
// MYSQL
INSERT INTO SUPPLIES ( `CUSTOMER`,`STREET`,`INVDATE`,`INVNO`,`DELYTIME`,`QTY`,`AMOUNT` )
VALUES
( 'Anthony','Barry''s Street','2013-07-05',2034,'2013-07-03 15:20:55',234.567,10257.89 )
// ORACLE
INSERT INTO SUPPLIES ( CUSTOMER,STREET,INVDATE,INVNO, )
VALUES
( 'Anthony','Barry''s Street',DATE '2013-07-05',2034,TIMESTAMP '2013-07-03 15:20:55',234.567,10257.89 )
Similary a command for UPDATE also is provided: This time we check with binary data, like a photo/bmp.
- Code: Select all Expand view
#include "fivewin.ch"
#include "adodef.ch"
function Main()
local oCn, cSql
oCn := FW_OpenAdoConnection( "xbrtest.mdb" )
// oCn := FW_OpenAdoConnection( "test,db" )
cSql := SQL UPDATE PIX SET EMPNAME = "James", FOTO = MemoRead( "c:\fwh\bitmaps\check.bmp" ) ;
WHERE ID = 99
? cSql
// oCn:Execute( cSql )
oCn:Close()
return nil
// MSACCESS
UPDATE PIX SET [EMPNAME] = 'James',
[FOTO] = 0x424D160< ..other bytes...>F8F8F8
WHERE ID = 99
Now, we shall connect to SQLITE database, instead of ACCESS and see the result for the same code.
// SQLITE
UPDATE PIX SET "EMPNAME" = 'James',"
FOTO" = x'424D160<...other bytes...>F8F8F8'
WHERE ID = 99
This SQL works with SQLITE.
You can see the difference in formatting of binary data between Access and SqLite.
Using these commands for writing INSERT and UPDATE SQL statements has the advantages:
1) We can write the statement using (x)Harbour variables and expressions, the way we understand.
We need not take trouble to construct literal text for different kind of variables.
2) We need not prepare different statements for different DBMS.
