Oracle
Oracle is a widely used relational database management system.
In our final testing of the connection to Oracle, we used Oracle 9.02.0010. The ODBC driver used was version 9.02.00.00.
Automatically Assigning Record Numbers
Oracle does not support an auto-increment type attribute. The only way to let Oracle automatically assign record numbers is by using triggers. A trigger is a database event that fires when a certain operation is executed on a table. One can define triggers on insert (creating records), update (modifying existing records), or delete operations.
Oracle supports sequences. The best way to automatically assign record numbers for every table accessed through the DataFlex ODBC Driver is to take the following steps for each individual table:
- Define a sequence starting at the (highest record number + 1) incrementing by 1.
- Define a trigger before insert that uses the next value of the table’s sequence and assigns it to the record number.
- Change the table level attributes DF_FILE_GENERATE_RECORD_ID_METHOD to
RIM_EXTERNAL, and DF_FILE_GET_RID_AFTER_CREATE toTrue.
Sample Conversion Source Code
It is not a difficult task to write a DataFlex program that automates this process. Sample code that automates this using Embedded SQL is listed below.
//***
//*** Procedure: ConvertOrclToSequenceAndTrigger
//*** Purpose : Convert all Oracle tables in the current filelist to use a sequence
//*** and a trigger to fill the record identity.
//***
Procedure ConvertOrclToSequenceAndTrigger
Handle hTable
String sRootName
String sDriver
String sRevision
Boolean bIsOpen
String sDisplayName
Handle hoDone
//*** Create a set to keep track of the tables that are already done, this
//*** takes care of alias tables
Get Create U_Set To hoDone
//*** Make sure failure to open does not abort the program
Send IgnoreError of Error_Object_Id DFERR_CANT_OPEN_DATA_FILE
//*** Show that we are starting this
Showln
Showln "Changing Oracle tables so record numbers are filled automatically"
Showln "Scanning Filelist"
Showln
//*** Traverse filelist
Move 0 to hTable
Repeat
Get_Attribute DF_FILE_NEXT_USED of hTable to hTable
If (hTable > 0) Begin
//*** Get the rootname to use in error reporting
Get_Attribute DF_FILE_ROOT_NAME of hTable to sRootName
//*** If name is in set, it’s already done so skip it.
If (Find_Element(hoDone, Uppercase(sRootName)) = -1) Begin
//*** Open the table
Open hTable
Move (Found) to bIsOpen
If (bIsOpen) Begin
//*** Make sure it is an Oracle table
Get_Attribute DF_FILE_DRIVER of hTable to sDriver
If (sDriver = "ODBC_DRV") Begin
Get_Attribute DF_FILE_REVISION of hTable to sRevision
If (Uppercase(Left(sRevision, 6)) = "ORACLE") Begin
//*** Register as done
Send Add_Element of hoDone (Uppercase(sRootName))
//*** Show progress
Get_Attribute DF_FILE_DISPLAY_NAME of hTable to sDisplayName
Showln "Handling table " hTable ", " sRootName ", " sDisplayName
Send ChangeGenerateRecordIdMethod hTable
//*** Open the table
Open hTable
Move (Found) to bIsOpen
If (bIsOpen) ;
Send CreateSequenceAndTriggerForTable hTable
End
Else ;
Showln "Table: " hTable ", " sRootName " is not an Oracle table."
End
Else ;
Showln "Table: " hTable ", " sRootName " is not an ODBC table."
//*** If still open, close table
Get_Attribute DF_FILE_OPENED of hTable To bIsOpen
If (bIsOpen) ;
Close hTable
End
Else ;
Showln "Unable to open table: " hTable ", " sRootName "."
End
Else ;
Showln "Table already handled (alias): " hTable ", " sRootName "."
End
Until (hTable = 0)
//*** Make sure failure to open works as it did before this procedure
Send Trap_Error of Error_Object_Id DFERR_CANT_OPEN_DATA_FILE
//*** Destroy set
Send Destroy of hoDone
End_procedure // ConvertOrclToSequenceAndTrigger
//***
//*** Procedure: CreateSequenceForTable
//*** Purpose : Create a sequence and a trigger for the passed table.
//*** Sequence will be named _DFSQNC, trigger will be
//*** named _DFTRG.
//***
Procedure CreateSequenceAndTriggerForTable Handle hTable
Handle hoSQL
Handle hdbc
Handle hstmt
String sTableName
String sSchema
String sRecnumName
String sSQLStatement
String sSequenceName
String sTriggerName
Integer iMaxRecnum
Get Create U_cSQLHandleManager to hoSQL
If (hoSQL <> 0) Begin
Get SQLFIleConnect of hoSQL hTable to hdbc
If (hdbc <> 0) Begin
//*** Get table info
Get_Attribute DF_FILE_TABLE_NAME of hTable to sTableName
Get_Attribute DF_FILE_OWNER of hTable to sSchema
//*** Assemble names
Move "" To sSequenceName
If (sSchema <> "") ;
Move (Append(sSequenceName, '"' + sSchema + '".')) To sSequenceName
Move (Append(sSequenceName, '"' + sTableName + '_DFSQNC"')) to sSequenceName
Move "" To sTriggerName
If (sSchema <> "") ;
Move (Append(sTriggerName, '"' + sSchema + '".')) To sTriggerName
Move (Append(sTriggerName, '"' + sTableName + '_DFTRG"')) to sTriggerName
//*** Get name of recnum
Get_Attribute DF_FIELD_NAME of hTable 0 to sRecnumName
//*** Create the sequence
Get SQLOpen of hdbc to hstmt
If (hstmt <> 0) Begin
//*** Get last used recnum
Clear hTable
vFind hTable 0 Lt
If (Found) ;
Get_Field_Value hTable 0 to iMaxRecnum
Else ;
Move 0 to iMaxRecnum
//*** Build the statement
Showln " Creating sequence: " sSequenceName
Move 'CREATE SEQUENCE ' to sSQLStatement
Move (Append(sSQLStatement, sSequenceName)) to sSQLStatement
Move (Append(sSQLStatement, ' INCREMENT BY 1 START WITH ')) to sSQLStatement
Move (Append(sSQLStatement, String(iMaxRecnum + 1))) to sSQLStatement
Move (Append(sSQLStatement, ' NOMAXVALUE MINVALUE 1')) to sSQLStatement
Move (Append(sSQLStatement, ' NOCYCLE NOCACHE NOORDER')) to sSQLStatement
Send SQLExecDirect of hstmt sSQLStatement
Send SQLClose of hstmt
End
//*** Create the trigger
Get SQLOpen of hdbc to hstmt
If (hstmt <> 0) Begin
//*** Build the statement
Showln " Creating trigger: " sTriggerName
Move 'CREATE OR REPLACE TRIGGER ' to sSQLStatement
Move (Append(sSQLStatement, sTriggerName)) to sSQLStatement
Move (Append(sSQLStatement, ' BEFORE INSERT ON ')) to sSQLStatement
If (sSchema <> "") ;
Move (Append(sSQLStatement, '"' + sSchema + '".')) To sSQLStatement
Move (Append(sSQLStatement, '"' + sTableName + '"')) to sSQLStatement
Move (Append(sSQLStatement, ' REFERENCING OLD AS OLD NEW AS NEW')) to sSQLStatement
Move (Append(sSQLStatement, ' FOR EACH ROW Begin Select ')) to sSQLStatement
Move (Append(sSQLStatement, sSequenceName)) to sSQLStatement
Move (Append(sSQLStatement, '.NEXTVAL INTO :new.')) to sSQLStatement
Move (Append(sSQLStatement, '"' + sRecnumName + '"')) to sSQLStatement
Move (Append(sSQLStatement, ' FROM DUAL; End;')) to sSQLStatement
Send SQLExecDirect of hstmt sSQLStatement
Send SQLClose of hstmt
End
Send SQLDisconnect of hdbc
End
Send Destroy of hoSQL
End
End_Procedure // CreateSequenceForTable
//***
//*** Procedure: ChangeGenerateRecordIdMethod
//*** Purpose : Set the DF_FILE_GENERATE_RECORD_ID_METHOD attribute of
//*** the table to RIM_EXTERNAL.
//***
Procedure ChangeGenerateRecordIdMethod Handle hTable
Showln " Changing DF_FILE_GENERATE_RECORD_ID_METHOD"
Structure_Start hTable
Set_Attribute DF_FILE_GENERATE_RECORD_ID_METHOD of hTable To RIM_EXTERNAL
Set_Attribute DF_FILE_GET_RID_AFTER_CREATE of hTable To True
Structure_End hTable DF_STRUCTEND_OPT_NONE "." Self
End_Procedure // ChangeGenerateRecordIdMethod
//***
//*** Function: Callback
//*** Purpose : Handle progress information for the Structure_end operation
//***
Function Callback String CallbackMsg Integer CallbackType Returns Integer
If CallbackType NE DF_MESSAGE_PROGRESS_VALUE ;
Showln " - " CallbackMsg
Function_Return False
End_Function // Callback
## Database Configuration File
The name of the database configuration file for Oracle is: `Oracle.int`.
A configuration file with recommended settings is part of the installation. Most settings reported by Oracle are accurate. In this file, the following settings are made:
### Default_Default_Date
The default value for date columns is ‘0001-01-01’. Oracle does not accept that format, so we overwrite it in the database configuration file with a format it does understand, `{d '0001-01-01'}`.
### Column_Create_Default_Clause
Oracle supports setting up defaults for columns but reports it does not.
### Dummy_Zero_Date_Value
The default dummy zero date value for columns is ‘0001-01-01’. Oracle does not accept that format, so we overwrite it in the database configuration file with a format it does understand, `{d '0001-01-01'}`.
### Index_Desc
The Oracle ODBC driver reports that descending index segments are not supported. In version 9.2, they are supported.
### Duplicate Record Keywords
The duplicate record keywords are set to the Oracle duplicate record error state and number as follows:
```dataflex
Duprec_Errornumber 1
Duprec_State 23000
Max_Active_Statements
The Max_Active_Statements keyword is set to 0.
Native_Lockerror
The deadlock and lock timeout errors of Oracle are defined as follows:
Native_LockError 60
Native_LockError 4021