Quiz - SQL CURSOR code for remote DB access / and SQLCURSOR D ecode syntax

[Whittall, Conrad]

Hi Leslie,

To quote from page 16 of the latest "PowerHouse and Relational Databases"
manual:

"Each SQL statement in PowerHouse is associated with a specific database
defined in the PowerHouse dictionary.

You specify the database using either the SET DATABASE statement or the IN
database option of SQL statements. The database name that you use must be
the name of a database defined in the current dictionary.

If you don't specify the IN database option, PowerHouse takes the default
from the SET DATABASE statement. If there is no SET DATABASE statement,
PowerHouse takes the default from the resource file. If there is no resource
file entry, PowerHouse sets the default to the name of the first DATABASE
statement that appears in the dictionary code. If the order of the
dictionary code is changed, the default changes."

So, to answer your first question, it would appear that the easiest way for
you to control which database your program accesses is to not qualify any of
the statements in your program with the "IN <database>" syntax and instead
include a conditional compilation statement near the start of the program,
such as:

@IF LIVEDB
   SET DATABASE sched1
@ELSE
   SET DATABASE simtst
@ENDIF

...then simply call QUIZ, or whatever component you want to use, with the
CC=(LIVEDB) command line parameter.

On your second question about using the Oracle DECODE function PowerHouse
does not support the use of vendor-specific SQL, only ANSI standard SQL. If
you wish to use any vendor-specific functionality you should declare a view
or stored procedure in your database and then access that through a
PowerHouse cursor -- default, as in the ACCESS statement, or declared, as
with the SQL DECLARE statement.

If you don't yet have the 3rd Edition of the PowerHouse Books PDF
documentation set I strongly recommend that you download it from the
PowerHouse web site at http://powerhouse.cognos.com -- just follow the
Documentation link under the "Related Links" section in navigation bar at
the right side of the page.

Hope this helps!

Best regards,
Conrad

Conrad Whittall
e-Learning Architect, Global Education Services
Cognos Incorporated, 3755 Riverside Drive, Ottawa, Ontario, K1G 4K9, Canada

-----Original Message-----
From: Leslie Tsukamoto [mailto:leslie.tsukamoto@cae.com]
Sent: Wednesday 9 October 2002 16:28
To: 'powerh-l@lists.swau.edu'
Subject: Quiz - SQL CURSOR code for remote DB access / and SQLCURSOR
Decod e syntax


I have posted several questions recently about how to code general quiz
access statements to allow remote connectivity to a database that is hosted
on a different box from the quiz code itself.  I was given great direction
in what definitions in the dictionary needed to exist and then how to
properly reference that data remotely.

Here are two databases currently defined within my local quiz dictionary:

database schdb & 
        type Oracle & 
        open ORACLE & 
        userid "sched1" password "prodpass" owner SCHED1

database simtst & 
        type Oracle & 
        open ORACLE & 
        userid "sched1@simtst" password "cpypass" owner SCHED1

The database referenced with the simtst tag is a copy of the sched1 database
but stored on a remote box - the databas instance name is different, but all
of the tables, views, etc are identical.  In general quiz code I can direct
quiz to access the data locally within my access statement by simply
stating:

access clients in schdb

but if I want to test against the remote database I simply code my access
statement to 

access clients in simtst 


now the issue arises that I have been given some very good directions on how
to code sql cursors within Quiz that allow for much faster and more
efficient data access - however - within that logic I was simply told to
code the select as:

SQL DECLARE X CURSOR FOR &                 
   SELECT * FROM CLIENTS &                              
  WHERE SUBSTRING(CLIENT_TYPE FROM 1 FOR 1) = 'C' &             
        AND STATUS = 'A' &                                   
    

I need to know how to properly qualify that CLIENTS table to properly tell
quiz which database to access - does anyone know the syntax of that
qualification?

Secondly - I have another question about sqlcursor command syntax - as you
can see the substring sqlcursor command is not coded in the same format as a
straight oracle substr - I have a need to send through the sql cursor a
DECODE statement - does anyone know the syntax of how to send through the
DECODE logic into a sqlcursor???

Help on these two issues is greatly appreciated.

Sincerely,  Leslie

Leslie J. Tsukamoto
Manager of Programming, CAE SimuFlite
ph:  407-445-0226
fax: 407-295-9493
email: leslie.tsukamoto@cae.com


= = = = = = = = = = = = = = = = = = = = = = = = = = = =


This message may contain privileged and/or confidential information.  If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so.  Thank you.