| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
DirectConnect for Oracle Installation/Reference for Windows NT |
![[-]](/dwicons/i_colpse.gif)
| Chapter 4: Topics |
This chapter contains reference material for DirectConnect for Oracle.
The DirectConnect for Oracle's connect handler creates a child process and connects to ORACLE on behalf of the OmniConnect user.
The username and password are supplied by ASE or OmniConnect. ASE or OmniConnect also pass a server name for each login request. This server name must appear in the configuration file to ensure that the correct connect string is used. The connect string defines the target database location. The DirectConnect child process logs into the Oracle database. The connection remains open until a disconnect event occurs.
DirectConnect for Oracle operates in passthrough mode unless it is used with ASE or OmniConnect. In passthrough mode the DirectConnect will pass SQL directly to the Oracle database with no transformation.
DirectConnect for Oracle supports columns of datatype text and image when used with OmniConnect. DirectConnect for Oracle provides text pointers, which are handles that reference text or image values in readtext, writetext, and select commands.
Sybase text and image columns are similar to the Oracle LONG and LONG RAW columns. When ASE or OmniConnect generate a select textptr() statement for a column, DirectConnect for Oracle creates a textptr value based on the Oracle ROWID. The text pointer is the actual ROWID of the given row.
When ASE or OmniConnect request a text or image column's text pointer using a select textptr() function, DirectConnect for Oracle will return a 16-byte binary column.
When inserting and updating text or image columns using ASE or OmniConnect, use readtext and writetext commands for data lengths exceeding 450 bytes.
Select text and image datatypes using select or readtext.
When ASE or OmniConnect issues a select on a text or image column, it sends out a select textptr() statement to get the text pointer of the required row. Using the text pointer, OmniConnect issues a readtext command.
For writing text or image columns of more than 450 bytes, the writetext command is used. The data is read by DirectConnect for Oracle in chunks and written to the ORACLE database when all the chunks are received. There has to be enough memory to store all the chunks together before an Oracle update statement is executed.
When ASE or OmniConnect communicates with DirectConnect for Oracle, the OmniConnect client language and the default character set are passed as thread properties. DirectConnect for Oracle interacts with Oracle, conveying the client language and Open Server character set. OmniConnect then receives character data and error messages in the correct language and character set.
Passthrough mode allows a DirectConnect for Oracle client to interact directly with an Oracle database using native Oracle SQL. When DirectConnect for Oracle is operating as a standalone gateway, clients are automatically put into passthrough mode. When used with OmniConnect, DirectConnect for Oracle performs Transact-SQL parsing.
In passthrough mode the client program issues Oracle SQL statements directly to the Oracle database and the results are converted into a form the Open Client interface understands.
Oracle datatypes are converted, when in passthrough mode, to the following Open Client datatypes:
Oracle Datatype | Open Client Datatype number_mode=0 | Open Client Datatype number_mode=1 | Open Client Datatype number_mode=2 |
|---|---|---|---|
number(1,0) | tinyint | tinyint | numeric (1,0) |
number(2,0) | tinyint | tinyint | numeric (2,0) |
number(3,0) | smallint | smallint | numeric (3,0) |
number(4,0) | smallint | smallint | numeric (4,0) |
number(5,0) | int | int | numeric (5,0) |
number(p,0) where | int | int | numeric (p,0) |
number(p,0) where | numeric (p,0) | numeric (p,0) | numeric (p,0) |
number(p,s) where | numeric (p,s) | numeric (p,s) | numeric (p,s) |
number(p,s) where | float | See Table 3-2 | See Table 3-2 |
varchar2(n) | See Table 3-4 | See Table 3-4 | See Table 3-4 |
long | See Table 3-4 | See Table 3-4 | See Table 3-4 |
long raw | See Table 3-4 | See Table 3-4 | See Table 3-4 |
date | datetime | datetime | datetime |
raw(n) | binary(n) | binary(n) | binary(n) |
The following table illustrates datatype conversion that is performed when a create table or alter table statement is processed when using DirectConnect for Oracle with ASE or OmniConnect. DirectConnect for Oracle constructs syntax for the Oracle datatypes as shown here:
OmniConnect datatype | Oracle Datatype |
|---|---|
bit | char(1) |
tinyint | number(3,0) |
smallint | number(4,0) |
int | number(10,0) |
smallmoney | number(10,4) |
money | number(19,4) |
float | float |
real | float |
decimal(p,s) | number(p,s) |
numeric(p,s) | number(p,s) |
char(n) | char(n) |
varchar(n) | varchar(n) |
binary(n) | raw(n) |
varbinaray(n) | raw(n) |
timestamp | raw(16) |
text | long |
image | long raw |
datetime | date |
smalldatetime | date |
When a create existing table command is processed, the datatype for each column specifies the type of conversion to be performed from the Oracle to OmniConnect columns during query processing. The following table describes the allowable datatypes that can be used for existing Oracle datatypes.
OmniConnect Datatypes | Oracle Datatype |
|---|---|
int, smallint, tinyint, float, double, See "number_mode" | number |
datetime | date |
char(n) | char(n) |
varchar(n) | varchar2(n) where n <= 255 |
text or varchar See "truncate_text" | varchar2(n) where n > 255 or long |
binary(n) | raw(n) |
image or varbinary See "truncate_text" | long raw |
The following datatype considerations should be noted.
Oracle pads raw(n) datatypes with blanks when users insert values less than n. SQL Server pads binary(n) datatypes with 0s when users insert values less than n.
An error may occur if an ASE or OmniConnect user expects 0-padded data yet accesses blank-padded data. Likewise, there may be problems if native Oracle applications expect to see blank-padded data, yet access 0-padded data.
Oracle allows number datatypes to have a scale larger than the precision. SQL Server numeric datatypes do not allow this.
Oracle allows number datatypes to have negative scales. SQL Server numeric datatypes do not allow this. If Oracle precision and scale are not specified, precision defaults to 38.
If existing Oracle tables have datetime values with dates prior to the SQL Server minimum date (January 1, 1753 12:00:00:000AM), then DirectConnect for Oracle will convert these values to the SQL Server minimum.
Global variables are available by using the command:
select @@variable_name
The following is a list of available global variables:
@@compatibility_level - contains the current configured value for number_mode and truncate_text. See "Data Compatibility".
@@client_csname - contains the character set in use by the current connection.
@@connect_info - contains the configured value for 'connect_string'.
@@language - contains the language in use by the current connection.
@@nls_lang - contains the language, territory and character set in use by the Oracle connection.
@@oracle_version - contains the version of Oracle to which a connection has been established.
@@servername - contains the current server name to which the client has connected.
@@spid - contains the process id of the current orachild process that is handling the connection on behalf of the DirectConnect client.
@@version - contains the version string information for the orachild process
The command set traceon traceflag will turn on specific trace flags in the orachild program. The trace flags are described in the configuration section of the previous chapter. The set traceon command is active only while the child process is running.
The command set traceoff [traceflag] turns off specific trace flags. If no trace flag is provided, then all trace flags are cleared.
|
|