I have supported applications that talk to SQL Anywhere, Sybase ASE and SQL Server depending on an option switch. I seem to remember one was also talking to Oracle vis ODBC as well.

Chris' list seems to address most of the issues

Thanks all for your valuable inputs.

In our earlier application, we used SQL Server with stored procedures for many reports.   No we are moving away from SPs.   

We have developed in SQL Server and now in PostGreSQL.   New to SQLite.   For few customers, where portability is required, we plan to use SQLite.  Complete app with DB in a pendrive / external HDD, which can be used in any computer.  Yes we may need to install SQLite drivers, if not available.  TopwizProgramming's ODBC API and RunAndWait will be handy in those situations.

We should choose a common minimum features available in all database servers.

Also, We need to find a way to solve the following issues

1.  As already pointed out, we need a way to fetch Top N rows ...   the syntax differs from SQL Server to PostGreSQL.   No idea about SQLite.  Is there any common (ansi) way to fetch the top N records?  Can this limit be set in a datawindow (graphics mode), that it can translate to equivalent DB ?

2.  Computed Columns
          In PostGreSQL
                Date field        Cast(null as date) as field2  returns date as datatype in datawindow, ODBC
          In SQL Server
                 Date field       cast(null as date) as field2   returns char(10) as datatype, OLE DB.
                                                                              returns date as datatype, SNC SQL Native Client

Should go with different dw objects, if required.

Will analyze further in the coming days.

Happiness Always
BKR Sivaprakash

1.  Do all databases interactions with a DataWindow

2.  Leave all of the DataWindows in graphic mode.  That will allow PowerBuilder to target the SQL generated for the target database.

At my very first PowerBuilder job (ca 1995) we connected to either Sybase or Oracle.

Make sure you adhere to ANSI Standards in your SQL.

Hi Siva;

  I would recommend...

• Keep all DB designs consistent using common data types.
• Do not use inline DML 
• Do not use  DBMS specific features.
• Only use DataWindows to process all DML requests.

HTH

Regards ... Chris 

yes.

the single most important thing is to use ansi sql when you can and minimize the use of sql specific to a single dbms, things like use coalesce instead of isnull/nvl

You can also *usually* create a set of user functions to allow you to use a single standard function list (assuming you use these). for example: 

Len vs length / getdate() vs now()  / substring/SUBSTR ....

consider using views/stored procedures to handle some of the specific differences.

generally, all datawindows are designed to work in all databases.  You don't want to have to maintain the same datawindow for different databases.

SQL functions can be different. The getdate function for example has different syntax on different database servers.