[Openroad-users] Conversion to OpenSQL

Tue Jan 22 15:47:53 EST 2008

Good point on the GV, we also use that method.. However, we have allowed
this to become used too widely within the application, so adding new
values would be a cumbersome, hence the reason, and the benefit of
hindsight, why you should try to abstract the SQL away into generic
procedure/classes that remove the need for programmers to build CASE
statements in their code.

I believe if you are using the EA product, COPY is supported, but in a
limited manner - string objects are not the most efficient of mechanisms
for loading large volumes of data - again you could abstract

 The native bulk loaders for each database type into a procedure and use
those instead.

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Brian Risley
Sent: 22 January 2008 04:21
To: International OpenROAD Users
Subject: Re: [Openroad-users] Conversion to OpenSQL

Colin,
You have to do some, we look at some of the dbsession data to determine
what db we are connected to.  We set a GV to easily id it.  (Also
protects us if Ingres should ever change values, the routine that sets
the gv can handle any new values, the specific code doesn't have to.)
DBEvents are not supported by all the databases available through EA.
You may have to do some other methodology to handle what you are doing
now.
Have you read all the EA documentation?
Another thing to watch is table(view)/field name restrictions.   Make
sure you don't exceed these and watch for reserved words in
table/view/field.
Unfortunately, once you leave a single database, you often have to look
at the least common denominator between the databases and program to
them.

Brian

Colin Hay wrote:

That was my Plan B, to use PreDirectives or some other config data to
perform SQL to work on each given database

Colin Hay
Database Administrator
Fintechnix
Level 3,
55 Clarence Street,
Sydney

colin.hay at fintechnix.com <mailto:colin.hay at fintechnix.com>
<mailto:colin.hay at fintechnix.com>   
Phone: +61 2 8234 8276 

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Pete
Rabjohns
Sent: Tuesday, 22 January 2008 2:54 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Conversion to OpenSQL

Hi Colin,

With the exception of DB Events (our application does not use them), we
took the approach of abstracting all of the non standard SQL into
classes that would manage the differences of the underlying DBMS via
EXECUTE IMMEDIATE (or DIRECT EXECUTE IMMEDIATE). The reason for this was
that you don't necessarily want to limit your SQL to OpenSQL to achieve
the right performance. This was especially true of UPDATE/FROM.

As an example, our application sits on top of SQL Server, Ingres &
Oracle, and all three of them support some kind of UPDATE/FROM syntax,
albeit in slightly different ways.

Sorry, cant help on the DBEVENT question.

Regards,

Pete

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Colin Hay
Sent: 22 January 2008 03:31
To: International OpenROAD Users
Subject: [Openroad-users] Conversion to OpenSQL

Just starting a project whereby we have to make all our OpenROAD /
Ingres code OpenSQL compliant

I've identified the following statements that will not work:

*         COPY <tablename>

*         CREATE TABLE ... AS SELECT

*         CREATE TABLE (<column> ... WITH DEFAULT)

*         DECLARE GLOBAL TEMPORARY TABLE ... AS SELECT

*         DROP [TABLE|VIEW] :<tablename>

*         RAISE DBEVENT

*         REGISTER DBEVENT

*         SET LOCKMODE

*         SAVEPOINT

*         UPDATE <table> FROM

We can rewrite COPY's using StringObjects and UPDATE FROMs with SELECT
loops etc

but how does one handle DBEVENTs and SAVEPOINTs ??

Anyone had such experience of porting OpenROAD sp3 to run on DB2 ?

Colin Hay

Database Administrator
Fintechnix

Level 3,

55 Clarence Street,

Sydney

colin.hay at fintechnix.com <mailto:colin.hay at fintechnix.com>
<mailto:colin.hay at fintechnix.com>  
Phone: +61 2 8234 8276 

Specialist providers of back and front office systems for the financial
services industry.
Featuring: Fintechnix(r)
Disclaimer: 
Notice: This message contains privileged and confidential information
intended only for the use of the addressee named above. If you are not
the intended recipient of this message you are hereby notified that you
must not disseminate, copy or take any action in reliance on it.
Any views expressed in this message are those of the individual
sender,except where the sender specifically states them to be the views
of Fintechnix Pty Ltd.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

Specialist providers of back and front office systems for the financial
services industry.
Featuring:  Fintechnix(r)
Disclaimer: 
Notice: This message contains privileged and confidential information
intended only for the use of the addressee named above. If you are not
the intended recipient of this message you are hereby notified that you
must not disseminate, copy or take any action in reliance on it.
Any views expressed in this message are those of the individual
sender,except where the sender specifically states them to be the views
of Fintechnix Pty Ltd.

________________________________

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080122/427f63b0/attachment-0001.html 