PH 7.10G on VMS 7.1.2 and Oracle

[Fry, Mark]

Hi Mike,

Just to add to Rob's comments, you will find differences in the default
Concurrency transaction model used by QUICK (and QTP for that matter) for
RDB and ORACLE.  For RDB we use 2 transactions - a QUERY transaction for
finding, and an UPDATE transaction for updates (and lookups).  For ORACLE we
use a single UPDATE transaction throughout.  Obviously you need to look out
for existing syntax in your screens that refers to the QUERY transaction
(eg. FILE xxx REFERENCE TRANSACTION QUERY AUTOCOMMIT would not be
appropriate syntax for ORACLE).  You may also find that your existing
application for RDB is using a variation of the Concurrency model called the
Optimistic model, where all finds (and lookups) use the QUERY transaction
and all updates use the UPDATE transaction.  This was often used to work
around index locking issues with RDB, but again it's not appropriate for use
with ORACLE.  Look for syntax in your dictionary on the SYSTEM OPTIONS
statement that reads something like DEFAULT TRANSACTION MODEL OPTIMISTIC
DEFAULT ENTRY AND FIND OPTIMISTIC SELECT IN OPTIMISTIC and get rid of it.

Also beware of code that declares designer transactions.  When you do this
with ORACLE, each new transaction uses a separate attach to the database
(and hence increments the licensed user count in ORACLE by 1).  10 users in
a screen utilising 3 transactions (the default UPDATE transaction and 2
designer transactions) would use 30 ORACLE user licenses.

As far as ORACLE dates go, you can change the date format globally for the
system without using element statements for each date column.  Go into
SYSTEM OPTIONS in the dictionary and specify the date format there (eg DATE
FORMAT DDMMYYYY SEPARATOR "/").

You can choose to allow or disallow null values on your database statement
in the dictionary, and in addition you can specify DEFAULT DATE IS NULL if
you want a null rather than a zero storing where the date is missing.

Good luck!

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com

Cognos Limited
Registered in England, number 1635598.  Registered office:  Westerly Point,
Market Street, Bracknell, Berkshire, RG12 1QB

This e-mail may contain confidential information and is confidential to the
user of the e-mail address to which it is addressed.  No-one else may copy
or forward all or any of this e-mail in any form.  If you receive this
e-mail in error please destroy this message and notify the sender by return
e-mail."     


-----Original Message-----
From: Rob Kool [mailto:rob.kool@cdp.co.nz]
Sent: 11 July 2001 00:53
To: 'PowerHouse List Server (E-mail)'
Subject: RE: PH 7.10G on VMS 7.1.2 and Oracle


Hi Mike,

I am currently involved in porting a number of PowerHouse applications from
Interbase 3.3 to Oracle 8i and there are some issues that I have
encountered. I guess that you will have to deal with similar issues when
porting from Oracle/RDB

For starters, you certainly have to change the dictionary. You have to
specify a different database type. Also the connection string for Oracle
will probably differ from the one for RDB (I am not a 100% sure on the
connection string, it's been a while when I last had to deal with RDB).

You will have to recompile all programs as there are no doubts differences
in the data types between RDB and Oracle. Even if the types are similar, the
way they are stored or handled may differ.

Then there is the issue of converting data types. One of the things I did,
was to convert a number of larger character fields to Oracles Varchar2. This
caused some problems when I tried to link to these fields using normal CHAR
temporary items. But that was easy to fix. Change the Temporary to Varchar
as well or truncate the item before linking.

A more annoying problem is the fact that Oracle treats an empty string "" as
NULL and as such it loses all string properties. If you for instance use it
in a function, the result will be NULL no matter what. Any linkage that uses
such a field will fail as well because NULL=NULL is FALSE!

Another problem I found was were a date field was not declared as an element
in the dictionary. When the screen was compiled, the field on the screen was
using the standard Oracle format for dates (including time). This messed up
some screen layouts. But that was fixed by putting the element in the
dictionary.

If you have any other applications using the same database, be aware that
PowerHouse stores blank dates as a 0 in Oracle. Oracle normally does not
allow this. I have seen an ODBC driver that crashed when it encountered such
a date. The Oracle client software handles this problem better.

I found a few other issues but most of them were reasonably easy to fix.

But you have to test you whole system!

Hope this helps a bit.

Rob Kool
Consultant
CDP Ltd.

-----Original Message-----
From: Lamers Mike [mailto:Mike.Lamers@phs.com]
Sent: Wednesday, 11 July 2001 06:37
To: 'Powerhouse List'
Subject: PH 7.10G on VMS 7.1.2 and Oracle


Hello ...
We currently run PH 7.10G on VMS 7.1.2 with an Oracle/RDB database
(V7.0-16).
We are considering converting the database from Oracle/RDB to "true" Oracle
7.3.3.6 (or higher).

Does anyone know if we would have to do anything special with any of our
Powerhouse code?  Would the dictionary be affected?  Would we need to
recompile all Quick screens?  Any other issues to consider?

Thanks

Mike Lamers
PacifiCare of Colorado 
Health Care Economics (CO82-214)
(719) 522 - 6758
mike.lamers@phs.com




This electronic message transmission, including any attachments, contains
information from PacifiCare Health Systems Inc. which may be confidential or
privileged. The information is intended to be for the use of the individual
or entity named above. If you are not the intended recipient, be aware that
any disclosure, copying, distribution or use of the contents of this
information is prohibited.  

If you have received this electronic transmission in error, please notify
the sender immediately by a "reply to sender only" message and destroy all
electronic and hard copies of the communication, including attachments.



= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.