Writing database independent SQL
Using ODBC to connect 4GL applications to different database is great feature, and when you are writing new code is something you can consider from day one, but what about existing 4GL applications, that where expected to work with Informix database engines, and in most cases, where using non-standard, Informix specific syntax?
You can find good documentation about deference's between Informix database functionality and SQL, and Oracle, DB/2 and Sybase in 4Js documentation and migration guides. Querix also have documentation for Oracle versus Informix. Since both 4Js and Querix are using native connectivity to the non-Informix database, instead of ODBC connectivity, be aware that depending on the capabilities of particular ODBC drivers that you will be using, this information may or may not apply.
The remainder of this document will consider PostgreSQL, as a natural choice for GNU tool like Aubit 4GL. PostgreSQL is the most advanced RDBMS with GPL/GNU license, and at the same time, closest to implementation and concepts of Informix world. After all, the Informix CTO is the same guy who designed the grandfather of PostgreSQL, Postgress, long time ago (see PostgreSQL manual for more info).
To compare functionality between many commercial and GNU database, including Informix and PostgreSQL, see http://www.mysql.com/crash-me-choose.htmy
Missing syntax in supported keywords:
MATCHING (use LIKE)
FIRST (use LIMIT)
* (use %)
? (N/A)
(please fill blank...)
Missing SQL keywords in PostgreSQL compared to Informix:
|
Statement |
Risk |
SE |
Alternative |
Comments |
| ALLOCATE DESCRIPTOR | 0 | |||
| ALTER FRAGMENT | 0 | |||
| ALTER INDEX | 2 | |||
| CLOSE DATABASE | 0 | |||
| CONNECT | 0 | |||
| CREATE EXTERNAL TABLE | 0 | |||
| CREATE PROCEDURE | 2 | |||
| CREATE PROCEDURE FROM | 2 | |||
| CREATE ROLE | 1 | |||
| CREATE SCHEMA | 0 | |||
| CREATE SYNONYM | 1 | |||
| DATABASE | 0 | |||
| ALLOCATE DESCRIPTOR | 0 | |||
| DISCONNECT | 0 | |||
| DROP PROCEDURE | 2 | |||
| DROP ROLE | 1 | |||
| DROP SYNONYM | 1 | |||
| EXECUTE (..all..) | 0 | |||
| FLUSH | 0 | |||
| FREE | 0 | |||
| GET (..all..) | ||||
| INFO | ||||
| OPEN | ||||
| OUTPUT | 0 | |||
| PREPARE | 0 | |||
| PUT | 0 | |||
| RENAME (..all..) | 2 | |||
| SET (..some..) | ||||
| START VIOLATIONS TABLE | 0 | |||
| STOP VIOLATIONS TABLE | 0 | |||
| UNLOAD | ||||
| UNLOCK TABLE | 2 | |||
| WHENEVER | 0 | |||
Notes:
Many SQL statements where never executable from 4GL syntax (some additions to this functionality where made in I4GL 7.3/D4GL 3.0), and needed to be prepared anyway. And what you can prepare, you can still execute, of course against Informix engine, but not against PostgreSQL.
Many of this statements do not appear in Informix SE engines, and most of 4GL code that I know about is SE compatible
Options:
1) Use Informix syntax (which most other SQL's recognize except for 'outer'). If we want to get nasty, we can even add missing Informix syntax to PostgreSQL, this is what the OpenSource is for after all..
2) or you prepare it first (in which case the 4gl doesn't interpret the SQL and passes it straight back into ODBC) Good for new code, bad for old code, bad for coders, a lot of work.
Some of the missing keywords outlined are not used, eg 'CLOSE DATABASE' would be done via ODBC so its not an issue. Most of them are very Informix specific.
EXECUTE statement_id is not really part of the engine in Informix, and as such, it should work with prepared statements. EXECUTE procedure is a different story!
The syntax for the SQL in relatively unimportant, provided it can be read by the SQL parsing bit, have a look at sql*.rule
Most of the time its very simple to add statements in there, could even using #defs for specifics if you want:
#ifdef POSTGRES_TARGET
...
#endif
Then allow the user to specify which syntaxes it compiler will allow, whether the ODBC server will allow it is up to the developer to a large extent....
Alternatively go with the SQL standard mechanisms and BAN any extensions....!
I want to keep some degree of DBMS portability, allowing POSTGRESS only syntax is fine, INFORMIX only syntax etc. I suppose issuing a warning may be the best bet if the compiler encounters non-standard SQL...
Keywords existing in both Informix and PostgreSQL
|
statement |
supported keywords |
unsupported keywords |
comments |
| ALTER TABLE | |||
| BEGIN WORK | |||
| CLOSE | |||
| CLOSE DATABASE | |||
| COMMIT WORK | |||
| CREATE DATABASE | |||
| CREATE INDEX | |||
| CREATE TABLE | |||
| CREATE TRIGGER | |||
| CREATE VIEW | |||
| DECLARE | |||
| DELETE | |||
| DROP DATABASE | |||
| DROP INDEX | |||
| DROP TABLE | |||
| DROP TRIGGER | |||
| DROP VIEW | |||
| FETCH | |||
| GRANT | |||
| INSERT | |||
| LOAD | |||
| LOCK TABLE | |||
| REVOKE | |||
| ROLLBACK WORK | |||
| SELECT | |||
| SET | (..some..) | ||
| UPDATE |
Notes:
Actually, I did it by hand. I took the output of dbschema and modified it to match PostgreSQL syntax.
Example, PG table definition:
create sequence caltype_seq start1;
create table caltype
(
caltype_nmbr int4 default nextval('caltype_seq'),
ctype char(2),
descr varchar(21),
rpt_desc varchar(8),
evntnmbr int4
);
create unique index caltype_idx on caltype (caltype_nmbr);
create unique index caltype_ctype_idx on caltype
(ctype);
create index caltype_evnt_idx on caltype (evntnmbr);
Same Table with Informix:
create table caltype
(
caltype_nmbr serial not null ,
ctype char(2),
descr char(21),
rpt_desc char(8),
evntnmbr integer
);
create unique index caltype_idx on caltype (caltype_nmbr);
create unique index caltype_ctype_idx on caltype
(ctype);
create index caltype_evnt_idx on caltype (evntnmbr);
The only real difference is in handling the serial number. I'm not sure I will continue to use varchar. I understand there may be a performance hit with it.
I'm not having difficulty with NULLs in the database though I'm not happy
with how I had to load it. I was not able to use the copy command on the results of an Informix unload. I had to write a 4GL report to format the
data so PostgreSQL would not ralph on the first null date or decimal
field it tried to load.