|
Dave
Many thanks for your response.
In general I check the type of database
using SQR compiler directives:
If the database is Oracle then
dates are formatted accordingly.
Unfortunately, the program in question is not mine
and the Oracle date format has been hard coded.
(As an aside: Date formats are stored in Oracle 8
with the year as two digits.
SQL tools undertake the formatting.
e.g. If a
SQL ...SELECT oracleDate... is made against an Oracle database then
the oracleDate is returned:
12-JAN-1987 23:56:48
If ... SELECT oracleDate ... LIKE '12-JAN-1987' ... is
made then no value is found for oracleDate
However,
if ... SELECT oracleDate ... LIKE '12-JAN-87'... is made then
the oracleDate is returned:
12-JAN-1987 23:56:48
With regard to the end-select on line 145
this should have been exit select.
Rather than copy and paste this code snippet from
the program I just typed it on the email - Without checking the
syntax!
A number of years ago,when programming in Gupta
(Now Centura), the application had to run against three databases (SQLBase,
Sybase, Informix).
On launching the application, the database type was
identified and a databaseFlag was set.
Before every SQL statement the databaseFlag was
checked.
Depending on the state of the databaseFlag, the
relevant SQL statement for the particular database was executed.
The solution worked - However, it was as you may
suspect a rather messy solution.
If I knew that the fuel crisis was going to last
for more than a few days then I would clear out the hard coded Oracle date
format in this program.
However, I can see that the process of
resolving the date problem in this program on a case by case basis will be long,
messy and involved.
The program will have an added feature that is of
no benefit.
This is why I asked the question.
It is beginning to look likely that the fuel crisis
will be over by the end of this weekend.
Before asking the question, I had decided that on
my return to work, I will mirror my Oracle work environment on a separate
removable (slotable) hard drive form my home machine.
I probably will be able to persuade the Company to
put a removable (slotable) hard drive in my machine at work...
This would be a clean and secure
solution.
Many thanks again for your input.
Andrew
----- Original Message -----
Sent: Friday, September 15, 2000 3:50
AM
Subject: Re: Running SQR progams against
different databases
Hi Andrew,
The only way to resolve problem one is to
restrict coding to elements that are common to both Oracle and SQLBase.
I did such a thing with Oracle and Ingres, and let me tell you there are
better ways to spend a Sunday afternoon.
If you can't write
extremely vanilla SQL that will do the job in both environments, you can
either use #if directives to select which elements to compile in a particular
environment, or, in the particular case you mention here, you might be able to
use SQR date processing instead of database date processing.
In regard
to problem two, don't you mean "exit-select" on line 145? I doubt this
works in either environment.
Now, you observe: An approach in which the compiler errors are resolved on a one by one
basis when the SQR file is in the Environment Home is impractical and
unproductive.
On returning to the Environment Work compiler errors
would be expected which will again need to be resolved on a one by one
basis.
Sorry, but I don't know another way to do this in a way
that your boss will want to pay for. We did some work with an SQR
program that would turn Oracle syntax into Ingres syntax. This was
interesting, but really only practical with minor elements that are easily
recognizable syntactically.
My suggestion would be to ditch SQL Server
and get Personal Oracle for Environment Home, unless you need to have this
application working under SQL Server too. Also, I don't know Maximo, but
if you can use native Oracle access instead of ODBC, do so. ODBC
supports vanilla Oracle pretty well, but I understand there are things it will
not do as well or as quickly as the native drivers.
Hope this of some
use to you.
Dave
At 12:56 AM 9/15/2000 +0100, you
wrote:
Hi all!
I have a problem of running an SQR file against different
databases.
Background
As a result
of the fuel crisis in the UK, I worked at home today and will be so doing
until the fuel crisis is over...
This led to a problem working
at home on a SQR file which was working without any problems at
work.
Environments
Environment Work
NT
Client
-Server
Maximo 4.0.1 with Patch
01
Oracle 8.00.5.00 Maximo database with multiple
schema
Environment Home
Windows 95
Single user on
one machine
Maximo
4.0.1
SQLBase as installed
Maxdemo database
Problems
A SQR
file tested and operation in Environment Work failed in
Environment Home
First problem
In Environment Home the file fails to compile with an error message
which appears to be related to date.
This is not surprising since different databases invariable have different
formats for the date.
For example, line 790
...
Begin-Sql
Declare
v_date
date;;
line
790
Begin
...
gave the compiler error:
(SQR 5528) ODBC SQL dbsql: SQLPrepare error 917 in cursor
10:
[INTERSOLV][ODBC SQLBase driver][SQLBase]00917 PRS ISC Invalid SQL
statement
Error on line 790: (SQR 3716) Error in SQL statement.
Unfortunately, dates have been hard coded in
Oracle date format, for example:
let $Display_footer = '(Report:ImpEXCEL.SQR,
Version 1.10, Run Date:'||ltrim(edit($Current-date,'DD-Mon-YYYY HH24:MI'),'
')
Second problem
In Environment Home the following compiler error
occurred:
(SQR 5528) ODBC SQL dbsql: SQLPrepare error 902 in cursor
1:
[INTERSOLV][ODBC SQLBase driver][SQLBase]00902 PRS MFC Missing FROM
clause
Error on line 145: (SQR 3716) Error in SQL
statement.
The relevant line of code within a select statement is as
follows:
begin-select on-error etc.
...
if #maximumNumberOfCommitments >
{MAXIMUMNUMBEROFCOMMITMENTS}
end-select
line
145
end-if
...
end-select
(Commenting out the end-select on line 145 removed the compiler error. This
seems somewhat odd.)
Other
problems
A number of other compiler errors occurred in Environment
Home..
Requirement
To
resolve cleanly, efficiently and effectively the problem of compile errors
in the SQR file when in Environment Home.
This is so that:
I can work productively on
the file in the Environment Home.
When the fuel
crisis is over, I know that the file can be quickly changed so that it will
run securely without compiler errors in
Environment
Work.
An approach in which
the compiler errors are resolved on a one by one basis when the SQR file is
in the Environment Home is impractical and
unproductive.
On returning to the
Environment Work compiler errors would be expected which will again
need to be resolved on a one by one
basis.
If anyone has
any ideas on resolving this problem, I would be most
grateful...
Andrew
Dave
Donnelly <dave@isisbio.com>
ISIS
BioComp phone
(909) 677-2446 fax (781) 207-5533
|