sql standards

[Jo]

Do DB2 SQL statements run on MS SQL Server?
What would be the best way to keep one version of the application
source code which would work with both DB2 and SQL Server databases?
rather than maintaining 2 diff. source codes of the same application.
Would appreciate any advice.
Thanks a lot.

[Serge Rielau]

Jo wrote:

Do DB2 SQL statements run on MS SQL Server?
What would be the best way to keep one version of the application
source code which would work with both DB2 and SQL Server databases?
rather than maintaining 2 diff. source codes of the same application.
Would appreciate any advice.

If your test your SQL Statements on DB2 you'll have a better chance
getting them to work on SQL Server than the other way around. (I assume
you turn all those SQL server knobs like ANSINULL etc on).
The most common troublemakers are violations of the rules for
identifiers. HelloWorld is Capitalized by SQL. "HelloWorld" is not.
There are no exceptions. (no [...] etc, etc...).
DB2 also enforces strong typing as required by the standard.
Again if you start with DB2 you won't get into trouble when porting.

One vendor with whom I have been working recently stores all their SQL
in a common file. Proprietary statements go into exception files.
Seems to work for them. In that case the app was developed on SQL Server
and the DB2 exception file was about 15% the size of the master.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Jo]

Would you suggest the best way to port an application (that works with
MS SQL Server) would be to make the changes to the SQL statements so
that they would work with DB2. And consequently maintain 2 versions of
the application, one for DB2 and one for SQL Server? Is that the normal
way its done out there?
Thanks a lot !

[Purple-D]

I guess the main issue would be the use of db2 functions (or sql
functions) which are not compatible. For eg the substring function in
db2 is SUBSTR() while in SQL server its SUBSTRING(). Also another
example would be char() in db2 converts a non character value to a
character value (i.e. casts it to char) ..while the same function in
SQL server would try to find the ascii equivalent of the input ..(which
is similar to chr() in db2)..
I guess the approach to take would be to maintain two different
versions of the app..each with its own sql ..

Jo wrote:

Would you suggest the best way to port an application (that works with
MS SQL Server) would be to make the changes to the SQL statements so
that they would work with DB2. And consequently maintain 2 versions of
the application, one for DB2 and one for SQL Server? Is that the normal
way its done out there?
Thanks a lot !

[Serge Rielau]

Jo wrote:

Would you suggest the best way to port an application (that works with
MS SQL Server) would be to make the changes to the SQL statements so
that they would work with DB2. And consequently maintain 2 versions of
the application, one for DB2 and one for SQL Server? Is that the normal
way its done out there?
Thanks a lot !

It's not an easy one to answer and depends on teh complexity of your
SQL. Your typical PHP app is simple enough that there existsa shared
SQL92 way that works on both DBMS. So using the DB2 changes as a
feedback (and enforcing the required coding style changes) will save you
in the long run. These are often trivial requirements such as using
ansi-join syntax and the like.
If you app is more compelx (perhaps using triggers, procedures, etc.
then 2 versions is often what you end up with, sadly enough.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Larry Menard]

For an example of a PHP app that supports multiple databases, look at
"http://sourceforge.net/projects/gallery". It supports PostgreSQL,
MySQL, DB2, Oracle, and SQL Server (at least it will support SQL Server
in the next release, which will hopeffully be in the next few weeks or
month).

The trick is in abstraction. The non-uniform SQL (like
SUBSTR/SUBSTRING) is generated dynamically. In the Gallery2 source
tree, see "modules/core/classes/GalleryStorage/Db2Storage.class",
function "getFunctionSql()". There is an equivalent file for each
supported RDBM.

In addition, Gallery uses another PHP abstraction package called ADOdb,
see "http://sourceforge.net/projects/adodb".
Serge Rielau wrote:

Jo wrote:

Would you suggest the best way to port an application (that works with
MS SQL Server) would be to make the changes to the SQL statements so
that they would work with DB2. And consequently maintain 2 versions of
the application, one for DB2 and one for SQL Server? Is that the normal
way its done out there?
Thanks a lot !

It's not an easy one to answer and depends on teh complexity of your
SQL. Your typical PHP app is simple enough that there existsa shared
SQL92 way that works on both DBMS. So using the DB2 changes as a
feedback (and enforcing the required coding style changes) will save you
in the long run. These are often trivial requirements such as using
ansi-join syntax and the like.
If you app is more compelx (perhaps using triggers, procedures, etc.
then 2 versions is often what you end up with, sadly enough.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/