Connecting SQLWindows Objects to Databases : Connecting to Oracle

Connecting to Oracle
This chapter describes how to connect SQLWindows applications to Oracle databases using the Team Developer native router, SQLRouter/Oracle. It describes (in alphabetical order) various features of Oracle that can affect the way you write your SQLWindows application or the way it behaves when you connect to it with SQLRouter/Oracle.
Before you begin
Please read all of Chapter 1, Overview and Chapter 2, Initializing and Testing Your Connection before reading this chapter.
You connect SQLWindows applications to Oracle databases using either the native router, SQLRouter/Oracle, or the ODBC router, SQLRouter/ODBC. To learn about connecting with the native router, read this chapter; to learn about connecting with the ODBC router, read Chapter 7, Connecting to Databases using ODBC.
Autocommit
Oracle database servers support autocommit. Autocommit is off by default.
If you turn on autocommit, both DML (Data Manipulation Language) statements (such as UPDATE, INSERT, and DELETE) and DDL (Data Definition Language) statements (such as CREATE TABLE and DROP TABLE) are committed immediately.
Note: With an Oracle database, DDL statements are always committed immediately, even if autocommit is off.
If you turn on autocommit, you cannot prepare a SQL statement, then execute it multiple times. Once you execute the statement, the prepare context is lost and you must prepare it again. If you turn off autocommit and turn on cursor context preservation, however, you can re-execute a prepared state multiple times (possibly with new values for any bind variables in the prepared statement)—read section on page 2 for more information.
For background information about autocommit, read Autocommit on page 1-2.
Cursor context preservation
Oracle databases support cursor context preservation (CCP). In SQLWindows applications that connect to Oracle, CCP is off by default.
If CCP is on (and autocommit is off), you can continue to execute prepared SQL statements (without preparing them again) even after you commit the transaction that those statements are a part of. If CCP is off, however, you cannot re-execute those SQL statements once you commit the transaction unless you prepare them again.
For background information, read Cursor context preservation on page 1-7.
Data types
The table that follows shows the mapping between the data types supported by a SQLWindows application and the data types supported by an Oracle database server.


STRING
CHAR (254 or fewer bytes)
VARCHAR2 (254 or fewer bytes)
VARCHAR (254 or fewer bytes)
RAW
ROWID
MSLABEL



LONG STRING
CHAR (255 bytes)
LONG (255 to 232 – 1 bytes)
VARCHAR2 (255 to maxvarch bytes)
VARCHAR (255 to maxvarch bytes)
RAW (255 bytes)
LONG RAW (255 to 232 – 1 bytes)
BINARY LARGE OBJECT (BLOB, max 4 GB)
CHARACTER LARGE OBJECT (CLOB, max 4 GB)
MLSLABEL
The value of maxvarch in the table above is either 4,000 or 32,000. If you have a LONG STRING bind variable in an SQL statement, and that bind variable is associated with a VARCHAR or VARCHAR2 column, you can store at most 4,000 bytes in that variable. If you use a PL/SQL program variable that is associated with a VARCHAR or VARCHAR2 column, you can store at most 32,000 bytes in that variable.
Note: The MLSLABEL data type applies only to Trusted Oracle database servers.
DBA authority
The Unify router for Oracle allows you to indicate that you wish to connect to a database in the “sysdba” role. You do this from Connectivity Administrator by adding “as sysdba” to the Oracle connection string , as shown in this example line from SQL.INI:
remotedbname=ora9i,@ora9i as sysdba
Thereafter, you can set the user ID as “sys” during a login and the router will actually connect with “sys as sysdba”.
If you are connecting to Oracle using an OLE DB data source, the additional phrase in the connect string is not required; “sys” will connect with sysdba privileges.
Dynamic PL/SQL
SQLWindows applications use dynamic SQL statements to perform operations on the databases you connect to. These statements (such as SELECT, INSERT, UPDATE, CREATE TABLE, and so on) are compiled and executed “on the fly” (while the SQLWindows application is running).
Oracle supports a different (though similar) feature called dynamic PL/SQL. This feature lets you assign a block of PL/SQL statements to a string variable, then have the database compile and execute that block of statements at runtime.
You cannot execute dynamic PL/SQL blocks in your SQLWindows applications. However, you can achieve a similar effect in your application as follows: create a stored procedure that contains the PL/SQL statements you want to execute, invoke that stored procedure, then drop the procedure before you exit the application (so the procedure does not continue to be stored in the database).
Empty strings
On Oracle, if you try to insert the empty string into a varying-length character string column (or try to update such a column with the empty string), the database stores NULL instead.
For background information, read the section Nulls, empty strings, and spaces on page 1-15.
Isolation levels
Oracle does not support setting isolation levels if an application uses dynamic (rather than static) SQL. Because all SQLWindows applications use dynamic SQL statements, you cannot call SqlSetIsolationLevel in your SQLWindows application when connected to Oracle.
You cannot set isolation levels when connected to an Oracle database. However, you can affect data consistency and concurrent access to data by doing either of the following:
Locking an entire table is very heavy-handed and risky. No one other than the user who has locked the table can do any operations on the table until it is unlocked. Even if the application locks the table for only a short period of time, the penalty paid by other users is usually unacceptably high.
Locking only certain rows during a transaction is much more selective and allows other users to access all data in the database except for the locked rows. However, if the user who has the lock takes a long time to complete the transaction, all other users who need to access that data are forced to wait.
For some background on isolation levels, read Isolation levels on page 1-12.
Lock time-out
Oracle servers do not support lock time-out. However, you can enable autocommit (see section on page 2), which has the side effect of eliminating deadlocks.
For some background on lock time-outs, read Lock time-out on page 1-14.
Optimizing message traffic
To speed up the flow of data between a SQLWindows application and the database server, you can use the array processing feature. This feature consists of:
Array fetching—you fetch multiple rows from a result set, rather than one row at a time.
Bulk executeyou send the data for UPDATE, INSERT or DELETE operations to the server in batches, rather than row by row.
You can modify how array processing works with the fetchrow and longbuffer keywords in the SQL.INI file—see fetchrow on page 5-6 and longbuffer on page 5-8.
Positioned updates
SQLWindows applications can perform positioned updates against an Oracle database.
For more information, read Positioned updates on page 1-17.
Result set mode
If you turn on result set mode when your application is connected to Oracle, SQLWindows uses front-end result sets—Oracle does not support (backend) scrollable cursors.
For more information, read Result set mode on page 1-19 and Front end result sets on page 1-8.
SQL.INI keywords
This section describes the keywords you should use in the SQL.INI file when connecting a SQLWindows application to an Oracle database.
For more information about the SQL.INI file, read Initializing SQLWindows applications on page 2-5.
comdll
Identify the SQLRouters available between SQLWindows (or a client application created with SQLWindows) and the database.
Section
[win32client.dll]
Syntax
comdll=communication_dll_name
Description
This keyword identifies the filename of a SQLRouter DLL. You can put more than one comdll statement in the SQL.INI file. The value of communication_dll_name to use for SQLRouter/Oracle is sqlora32.
Example
comdll=sqlora32
Notes
For related information, read Connect search order on page 1-6.
fetchrow
This keyword specifies the maximum number of rows SQLRouter/Oracle retrieves per each network message during cursor operations (fetching) on a result set.
Section
[oragtwy]
Default
64
Syntax
fetchrow=number_of_rows
Description
number_of_rows must be a positive integer (but do not put a plus sign).
The fetchrow value specifies the number of rows that the OCI (i.e. Oracle Call Interface) layer "prefetches" and makes available to your application. If the value is not set or the value is less than 64, a default value of 64 is used.
You can improve performance by assigning a suitable value to fetchrow.
To achieve good performance, you must balance the number of rows per fetch (which increases the memory used) against the operating system's need for memory (which causes the OS to swap memory when not enough is available).
In general, on a slow network connection such as a WAN, higher values for fetchrow (up to the actual number of rows in a given query) give the best results.
Your SQLWIndows application still recieves a single row for each call to SqlFetchNext, regardless of the value assigned to fetchrow.
Example
This example indicates that you want to retrieve only 100 rows with each fetch.
fetchrow=100
This example indicates that you want to fetch only a single row (in effect, disabling the feature).
fetchrow=1
log
Use this keyword to define the database router activity log and enable logging.
Section
[win32client.ora32]
Syntax
log=fully_qualified_path_name
Description
Specify any valid file pathname after the keyword. If the file does not exist, it is created. If the file already exists, it is overwritten (unless the /Fa option is specified — see below). Once the log statement has been added to the SQL.INI file, logging is performed automatically.
You can specify one or more of the options listed below after the fully qualified pathname to customize the output:
/Bx—exclude bind variable values
/Fa—append to existing file
/FD—display fetch data
/Ld—display LONG data read or written
/Tx—exclude timestamps
Enter the options exactly as shown, keeping upper-case letters upper-case and lower-case letters lower-case.
Example
This example calls for a log file that does not include timestamps on entries (to conserve disk space), and displays the input and output of all LONG data (including TEXT and IMAGE data items).
[win32client.ora32]
log=c:\unify\oracle.log /Tx /Ld
longbuffer
This keyword specifies the maximum number of bytes of long data type columns to retrieve or send, and truncates excess bytes.
Sections
[oragtwy]
Default
32,768. Setting a value less than the default will cause the value to be reset to the default.
Syntax
longbuffer=number_of_bytes
Description
The maximum size of this keyword is limited only by operating system constraints. Reduce the value of this keyword to conserve memory or reduce network traffic. If you use longbuffer to limit the number of bytes to retrieve, the retrieved data is truncated. To avoid truncating long data, set the value equal to the largest data type size that your applications need to read.
The longbuffer keyword applies to all databases defined in [oragtwy] section. You cannot specify a longbuffer value larger than the maximum size for the data type to be retrieved. Maximum value for this setting is dependent on your operating system and database server environment..
Example
longbuffer=65536
Note: You can also set this environment variable in your SQLWindows application using the SqlSetParameterAll with the DBP_LONGBUFFER parameter. (Refer to the SQLWindows Function Reference and the on-line help for more information about this function and the parameter.)
nodefparse
Use this keyword to enable or disable deferred parsing.
Sections
[oragtwy]
Default
no
Syntax
nodefparse={yes|no}
Description
This keyword (a shortened form of “no deferred parse”) enables or disables deferred parsing.
SQLRouter/Oracle uses deferred parsing for improved performance at runtime. However, deferred parsing makes debugging more difficult because the router cannot return the position of a column that may have generated an error.
You should especially disable deferred parsing when debugging complex SQL statements with many columns in the result set. If deferred parsing is disabled and an error is raised, the cursor automatically highlights the offending column in the result set.
When you put an application into production, you should enable deferred parsing.
Example
This example disables deferred parsing.
nodefparse=yes
remotedbname
Use this keyword to specify database names and their associated connect strings.
Section
[oragtwy]
Syntax
remotedbname=db_name,connect_string
Description
db_name specifies the value you will use as SqlDatabase for SqlConnect()
connect_string is the character “@” followed by the Oracle “Service Name” as understood by SQL*Net. Only use the first portion of the service name- not the “.world” portion.
Example
This example assumes you have configured your Oracle 7 or Oracle 8 client for an Oracle Service Name of ARTHUR.world.
Remotedbname=ART,@ARTHUR
You assign the variable SQLDatabase to the value “ART” and then issue the SqlConnect() call. SqlRouter Oracle finds all of the necessary connection information and connect you to the Oracle server.
Additional Requirements:
SqlRouter/ Oracle depends upon the Oracle Call Inteface (OCI). This lets SQLRouter/Oracle communicate effectively with versions 7.1, 7.2, 7.3, 8.0.x, 9.x, and future versions of Oracle. To make sure the proper verson of Oracle’s “OCI” software is used, the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
contains a value for the parameter ORAOCI. This can be set to a specific verson of Oracle’s DLL:
Make sure the fully qualified path name is used in the registry entry.
Refer to the Oracle documentation for more information about CONFIG.ORA, TNSNAMES.ORA, and ORACLE.INI and how to set up these files.
Note: Oracle versions earlier than 8.1.5 are not supported by SqlRouter/Oracle.
Notes
For related information, read Connect search order on page 1-6.
substitute
Use this keyword to specify that the string in the first parameter should replace the string in the second parameter whenever the first parameter appears in an SQL statement.
Section
[oragtwy]
Syntax
substitute=original_string,substitute_string
Description
You can have an unlimited number of substitute statements in the initialization file. However, this statement changes all occurrences of the replaced word in your SQL statements: operators, WHERE clauses, object names, and literals (but not bind variables).
The second string can include the first, as in:
substitute=od,odd
Important: The strings can include embedded blank spaces, but not commas—you must use a comma to separate original_string from substitute_string.
Notes
Because each SQL statement is parsed, having substitute statements in your SQL.INI file adds a small amount of overhead to the compilation of those statements. If you are not running any Unify software that require substitution, you may want to remove these statements from the file.
If you use applications that rely on the “CATALOG” commands, such as Quest, Report Builder, and DB Explorer, you must include the following statement in your SQL.INI:
substitute=SYSSQL.,
Include this statement exactly as shown with the upper case letters ”SYSSQL” followed by a period followed by a comma. Without this, Quest, Report Builder, and or DB Explorer will not be able to use the “CATALOG” commands in your application.
Example
This example replaces double quotes, which Oracle does not accept, with nulls. This statement is useful to run a program or tool you cannot modify that
The statement causes SQLRouter/Oracle to strip off the double quotes before it sends the data to the database.
substitute=”,
uselob
Use this keyword to specify whether the SQLBase API should use Oracle Call Interface (OCI) version 8 to work with LOB datatypes (BINARY LARGE OBJECT and CHARACTER LARGE OBJECT), or use version 7, which cannot handle LOB datatypes..
Default
0
Section
[oragtwy]
Syntax
uselob=integer
Description
The LOB datatypes in Oracle version 9 and greater require the use of OCI version 8. Earlier versions of the router for Oracle used OCI version 7. If you need compatibility with Oracle LOB datatypes, set the value of integer to 1 to use OCI 8. If not, set the value to 0 to use OCI 7. The default is 0.
Note: You can also set this environment variable in your SQLWindows application using the SqlSetParameterAll with the DBP_ORAUSELOB parameter. (Refer to the SQLWindows Function Reference and the on-line help for more information about this function and the parameter.)
 
Stored procedures
SQLWindows applications can execute PL/SQL stored procedures on an Oracle database. These stored procedures can also generate trigger events. To execute a PL/SQL stored procedure, use the SqlPLSQLCommand function.
A PL/SQL stored procedure invoked by SqlPLSQLCommand can have no more than 254 arguments. Also, the string used to invoke the procedure cannot contain new line characters, nor can it execute stand-alone functions. However, the array arguments can be dynamic arrays—see section on page 15.
The SqlPLSQLCommand cannot process stored procedures without arguments. To execute a stored procedure without arguments, use SqlPrepareAndExecute. For example:
If NOT SqlPrepareAndExecute(hSql,
'BEGIN anonymous_PL/SQL_block;END;')
SqlPLSQLCommand supports only the following Oracle data types for both scalar and array arguments for stored procedures:
You must explicitly specify the size of CHAR, VARCHAR, or VARCHAR2 data types as arguments to a PL/SQL stored procedure. You can use either %type or define your own data type. For example, if the stored procedure takes a CHAR argument that is a value for a column in a table, use %type with table_name.column_name to declare that variable. If the argument does not tie in with a database column, you can define MYCHAR CHAR(20), then use %type with MYCHAR to declare your argument.
If you do not specify the size, SQLNetwork binds the arguments with the maximum size allowed for each data types.
Overloading stored procedures
SQLRouter/Oracle supports overloaded stored procedures in PL/SQL packages; however, you must:
Important: You cannot mix data types, nor can you use the DATE type in arguments for overloaded procedures.
For general information about stored procedures, read Stored procedures on page 1-21.
SqlPLSQLCommand
Use this function to execute PL/SQL stored procedures in your SQLWindows application. Call it once for each invocation of PL/SQL.
Syntax
bOk = SqlPLSQLCommand(hSql, strCommand)
Parameters
hSqlHandle—The connected SQL handle to an Oracle database.
strCommand—Command string used to invoke a PL/SQL procedure.
Description
The first parameter identifies the SQL handle to the database. The second parameter is a command string used to invoke a PL/SQL stored procedure. Like a stored procedure call, this command string must have a name.
You can use PL/SQL IN variables the same way you use any other parameter in SAL function calls.
PL/SQL OUT or IN/OUT variables must be valid SAL Receive parameters. These parameters, like SAL Receive parameters, are set when the call returns.
You can use arrays for IN, OUT, and IN/OUT parameters.
IN/OUT parameters can be made to pass data to the PL/SQL stored procedure and to receive data from a PL/SQL stored procedure in the same parameter.
Return Value
bOk is TRUE if the function succeeds and FALSE if it fails.
Example PL/SQL stored procedure
The following example shows a PL/SQL stored procedure named INVOICES that contains a procedure, INSERT_INVOICES. This procedure inserts one master record and a set of detail records into the database.
CREATE or REPLACE PACKAGE invoices AS
TYPE item_no_tbl IS TABLE OF invoice_detail.item_no%TYPE
INDEX BY BINARY_INTEGER;
TYPE quantity_tbl IS TABLE OF invoice_detail.quantity%TYPE
INDEX BY BINARY_INTEGER;
TYPE amount_tbl IS TABLE OF invoice_detail.amount%TYPE
INDEX BY BINARY_INTEGER;
PROCEDURE insert_invoice
(o_invoice_id OUT invoice_master.invoice_id%TYPE,
i_client_name IN invoice_master.client_name%TYPE,
i_invoice_date IN invoice_master.invoice_date%TYPE,
i_item_no IN item_no_tbl,
i_quantity IN quantity_tbl,
i_amount IN amount_tbl,
i_num_details IN NUMBER);
END INVOICES;
CREATE or REPLACE PACKAGE BODY invoices AS
next_invoice_id NUMBER;
PROCEDURE insert_invoice
(o_invoice_id OUT invoice_master.invoice_id%TYPE,
i_client_name IN invoice_master.client_name%TYPE,
i_invoice_date IN invoice_master.invoice_date%TYPE,
i_item_no IN item_no_tbl,
i_quantity IN quantity_tbl,
i_amount IN amount_tbl,
i_num_details IN NUMBER) IS
BEGIN
SELECT INVOICE_SEQ.NEXTVAL INTO next_invoice_id
FROM DUAL;
o_invoice_id:=next_invoice_id;
INSERT INTO INVOICE_MASTER
(INVOICE_ID,CLIENT_NAME,INVOICE_DATE)VALUES
(next_invoice_id,i_client_name,i_invoice_date);
FOR n IN 1..i_num_details LOOP
INSERT INTO INVOICE_DETAIL
(INVOICE_ID,ITEM_NO,QUANTITY,AMOUNT) VALUES
(next_invoice_id,i_item_no(n),i_quantity(n),
i_amount(n));
END LOOP;
COMMIT;
END insert_invoice;
END invoices;
Executing the sample stored procedure
The following example shows how to execute the example PL/SQL stored procedure from a SQLWindows application.
Number:ninv_id
String:strClient
String:strCommand
Date:dt_inv_dt
Number:nitem_No[3]
Number:nQnty[3]
Number:nAmt[3]
Number:nNum
Set ninv_id = 3
Set strClient = ’John Doe’
Set strCommand = ’INVOICES.INSERT_INVOICE’
Set strCommand = strCommand || ’(ninv_id,strClient,dt_inv_dt,’
Set strCommand = strCommand || ’nitem_No,nQnty,nAmt,nNum)’
Set dt_inv_dt = 1993-07-12
Set nitem_No[0] = 3
Set nitem_No[1] = 4
Set nitem_No[2] = 5
Set nQnty[0] = 13
Set nQnty[1] = 14
Set nQnty[2] = 15
Set nAmt[0] = 21
Set nAmt[1] = 22
Set nAmt[2] = 23
Set nNum = 3
SET bOK=SqlPLSQLCommand(hSql, strCommand)
Dynamic Array support for PL/SQL stored procedures
You can use dynamic arrays as input, output, and input/output arguments to Oracle8 PL/SQL stored procedures. The arrays can be of type NUMBER, STRING and DATE/TIME.
Dynamic Arrays as INPUT arguments
Dynamic arrays grow to the size of the largest index value used in an assignment statement using the dynamic array variables. For example, the following statements cause nDynVar to be a dynamic array of 10 elements:
nDynVar[0] = 0
nDynVar[1] = 1
nDynVar[9] = 9
When you pass a dynamic array as an INPUT argument to a PL/SQL stored procedure using SqlPLSQLCommand, the number of elements created on the client are available on the database server. For example, the stored procedure:
insert into foo values(nDynVar[9])
inserts the value 9 into the table, whereas the statement:
insert into foo values(nDynVar[10])
causes the error “Attempting to fetch beyond end of result set”.
Uninitialized values
Do not initialize dynamic arrays. The arrays are passed as NULL for data types STRING and DATE/TIME, and as zero for data type NUMBER. To pass NUMBER as a NULL, the user must explicitly assign it the value Number_Null.
Dynamic Arrays as OUTPUT arguments
The size of output arguments of type dynamic array is determined in the backend stored procedure. For example, the following statements return an array of ten elements to your SQLWindows application:
sOutVar(1) := ‘abc’;
sOutVar(10) := ‘def’;
Uninitialized values
Values for output arguments are not initialized on the backend, but are returned as NULL values.
Dynamic Arrays as INPUT/OUTPUT arguments
INPUT/OUTPUT dynamic arrays behave as INPUT dynamic arrays on input and as OUTPUT dynamic arrays on OUTPUT.
Transactions, disconnects, and exits
If autocommit is off in your application, you are responsible for explicitly committing or rolling back transactions. The rest of this section assumes that autocommit is off.
If you disconnect from the database without committing or rolling back a pending transaction, the database commits the transaction for you.
If you exit the application normally without committing or rolling back a pending transaction, the database commits the transaction for you.
If you exit the application abnormally without committing or rolling back a pending transaction, the database rolls back the transaction for you.
Writing RAW data
To INSERT or UPDATE columns that contain RAW data, you must first assign the data to be placed in that column to an appropriate bind variable, then prepare an INSERT or UPDATE statement using that bind variable.
The bind variable must be of type LONG STRING. Once you have prepared the INSERT or UPDATE statement, you must call SqlSetLongBindDatatype to bind the LONG STRING data to the RAW data type. You then execute the INSERT or UPDATE statement.
Important: You must prepare the INSERT or UPDATE statement without executing it, then call SqlSetLongBindDatatype, then execute the INSERT or UPDATE statement. The binding done by SqlSetLongBindDatatype lasts only until your application prepares the next SQL statement.
The call to SqlSetLongBindDatatype takes two arguments: the first is the ordinal position of the bind variable that has the LONG STRING data in the INSERT or UPDATE statement; the second is the constant 23—this constant denotes RAW data in the case of Oracle databases. (You can declare a user constant with a symbolic name like nRaw and assign it the value 23; this makes your code more self-documenting.)
For example, assume an Oracle table called PRODUCT with the following column data types:
INTEGER —for style ID information
VARCHAR(30) —for style information
VARCHAR(254)—for description information
DEMICAL(8,2)—for price information
LONG RAW—for picture information
Note: The example would work just the same if the last column’s datatype were CHARACTER LARGE OBJECT or BINARY LARGE OBJECT instead of LONG RAW.
Also assume the following variable declarations in your application:
Number: nStyleId
String: strStyle
String: strDesc
Numnber: nPrice
Long String: lstrPicture
Now assume you have assigned these variables values in your program. You can add a row to the PRODUCT table as follows:
Set strStmnt=’INSERT INTO PRODUCT VALUES’
Set strStmnt=strStmnt || ‘(:nStyleId,:strStyle,:strDesc,’
Set strStmnt=strStmnt || ‘:nPrice,:lstrPicture)’
Call SqlPrepare(hOracle,strStmnt)
Call SqlSetLongBindDatatype(5,23)
Call SqlExecute(hOracle)
The first argument to SqlSetLongBindDatatype is 5 because :lstrPicture is the bind variable that has the data to be inserted into the LONG RAW column, and it appears as the fifth bind variable in the INSERT statement. The second argument must be 23 (or a symbolic integer constant you define yourself that has that value).
 
Connecting Objects to Databases
 

Unify Corporation