Re: Running SQR progams against different databases

[David Donnelly <Dave@ISISBIO.COM>]

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