different SQL backends

todd aron, senior developer, software development taron at competitrack.com
Thu May 8 16:35:52 EDT 2008

"	<omnisdev-en_lists.omnis-dev.com.lists.omnis-dev.com>"
List-Unsubscribe: <http://lists.omnis-dev.com/mailman/listinfo/omnisdev-en_lists.omnis-dev.com>,
"	<mailto:omnisdev-en-request at lists.omnis-dev.com?subject=unsubscribe>"
List-Archive: <http://lists.omnis-dev.com/pipermail/omnisdev-en_lists.omnis-dev.com>
List-Post: <mailto:omnisdev-en at lists.omnis-dev.com>
List-Help: <mailto:omnisdev-en-request at lists.omnis-dev.com?subject=help>
List-Subscribe: <http://lists.omnis-dev.com/mailman/listinfo/omnisdev-en_lists.omnis-dev.com>,
"	<mailto:omnisdev-en-request at lists.omnis-dev.com?subject=subscribe>"
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Sender: omnisdev-en-bounces at lists.omnis-dev.com
Errors-To: omnisdev-en-bounces at lists.omnis-dev.com


assumed is you are using studio? how does your application get 
distributed? (internal? "hey, Joe, there's a new library on the server!")

i've been doing most of the conversion to oracle from DML here and have 
found useful something Eric Azarcon introduced to me (thanks, Eric! tho, 
of course, many others have done it as well).

have your data access in modules (be they code objects, whatever). we're 
still in o7, so we have these methods in a menu.

consider setting a reference to your data access object, and then 
reference the different methods from that. that way, you can distribute 
your library in its entirety (ie w/o having to determine what to 
distribute), and have some mechanism which sets the references to the 
appropriate data access object.

you may find it helpful to use standard names (in that there's lotsa SQL 
back ends, insert, update, select seem good choices).

     if vAccess = "oracle"
        set reference gDataRef to $menus.mOracle
     else if vAccess = "postgresql"
        set reference gDataRef to $menus.mPostgresql
     else if vAccess = "omnis"
        set reference gDataRef to $menus.mOmnis

   <somewhere else>
     call procedure [dataRef().$name].$personInsert()

one challenge you may face is that thinking in terms of DML can be 
somewhat different than thinking in terms of SQL. for example, oracle 
supports a notion similar to the DML "next" (SQL fetch next), but not a 
DML "previous", altho some databases do (SQL fetch previous).

the standard of what i've done in SQL is done in lists, eg user performs 
a "search" and a list of items is shown; user then selects from amongst 
those records and the record selected is loaded into a detail window or 
section of the list window.

= you may find your DML find tables behave slightly differently than do 
your SQL select tables, esp in a multiuser environment.

of course, there's lots more, but i need to get back to the grindstone.

good luck!

Christine Penner wrote:
> Hi All,
> We are converting our entire program from DML to SQL. As we convert we are still 
> using an Omnis data file. When we are done we want to be able to use something 
> different. We would like to leave it open so we can use any backend without 
> having to go back and re write all of our SQL statements. I know this is not 
> something unusual we are doing because it has come up on this list before. I 
> have 2 questions.
> 1. How do some of you deal with that? (for statements used with table classes 
> and without)
> 2. What are some of the areas we need to watch for as we are converting? I know 
> of some; right joins and like syntax. I also know there are things that are 
> supported by other backends that aren't by Omnis data files. We will always have 
> some clients using an Omnis data file so we have to keep that in mind too. Can 
> anyone give me more details on these?
> Any suggestions are appreciated.
> Christine Penner
> Ingenious Software
> 250-352-9495
> christine at ingenioussoftware.com <mailto:christine at ingenioussoftware.com>
> ------------------------------------------------------------------------
> _______________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com


be well,

todd ARON                    p 718.482.4206
Competitrack                 f 718.482.4286
36-36 33rd st, suite 501     <taron at competitrack.com>
long island city, NY 11106   <http://www.competitrack.com/>


A little learning is a dangerous thing; drink deep, or taste not the 
Pierian spring: there shallow draughts intoxicate the brain, and 
drinking largely sobers us again.
- Alexander Pope

More information about the omnisdev-en mailing list