|
[OCI] |
Connect to Oracle Server |
OPEN (chan[fileopt])"[OCI]sid[fileopt]"
|
File tag clause to inform PxPlus that it will be opening an Oracle database. | |||||||||||||
|
chan |
Channel or logical file number to open. | ||||||||||||
|
fileopt |
File options. Supported options include:
| ||||||||||||
|
sid |
Oracle System ID of file to open. If not supplied, then the value of the environment variable ORACLE_SID is used. String expression. |
The [OCI] tag is used as a prefix in an OPEN statement to denote that PxPlus is to route all file I/O requests to an external (Windows, not PxPlus) Oracle database file. (OCI is an acronym for Oracle Call Interface.) Once you open a channel for [OCI] use, you can use it just like any other channel (i.e. for file I/O). It remains open until you close it.
The OPEN options for connecting to an Oracle server are:
|
AUTO_INDEX=Y | N |
Used to include hints and index numbers to SELECT statements. |
|
Date format mask applying to all date fields in table. This can be a combination of Y M D with any other characters. Example: To convert dates to 4-character year, month and day: DATEFMT=YYYYMMDD Other characters are inserted as is. DATEFMT=YY/MM/DD with a date of March 1, 2004 would be returned as 04/03/01. | |
|
DEBUGIT=xxxx |
String to append to SQL statement along with program name and line number for debugging purposes. This must indicate the comment character(s) appropriate to the database. |
|
EXTROPT=xxxx |
Controls the format of the SELECT statement used to process an EXTRACT. By default, PVX generates a SELECT * FROM table FOR UPDATE WHERE... |
|
IND=fld |
Identifies a column that contains a sequential number starting at 0. This is used to emulate an indexed file. |
|
IOPROG=progname |
Emulates the embedded I/O program logic available with a true PxPlus file. |
|
KEY=fld, fld |
Identifies fields that make up the key(s). For named keys, enter *NAME:keyname. Use the :D option to indicate that the key segment is to be sorted in descending order. KEY=KeyFld1,KeyFld2:D,KeyFld3 |
|
KEYDATA=fld |
Identifies a column that represents the key. This is used to emulate an external key where the data is not duplicated in data. |
|
Reformats the contents of a date column to and from the Sage MAS 90 date format. | |
|
MAS90SET |
Sets flags for Sage MAS 90 emulation, such as turning on the MAS90DATE conversion. |
|
NONULLS= |
Inserts zero-length strings rather than nulls into the target database and does not generate WHERE clauses checking for IS NULL or IS NOT NULL. (INI Supported) Set to 1, Y or y to enable or 0, N or n to disable. If the application does not work correctly when moving from Version 5 or lower, then set NONULLS=P to indicate that keys are handled the same as pre-Version 6. |
|
NOSTRIP |
Keeps trailing spaces. (Default) |
|
NULLPADKEY=Y | N |
Set to 1, Y or y to force keys to be padded to full length with the null character, $00$. |
|
ORACLE= Y | N |
Indicates if the database uses ORACLE SQL sequence (either Y or N). If ORACLE= and TOP= are used, then SELECT commands are generated as SELECT * FROM (SELECT * FROM TABLE) WHERE ROWNUM < 1. Default is Y. |
|
PREPARE= Y | N |
Set to 1, Y or y to use prepared statements. Prepared statements are pre-compiled SQL that may improve performance. Default is N. |
|
PSWD=xxxx. |
Specifies password. |
|
REC=fld, fld, ... |
Provides the column names, type, and size. This is typically done to improve performance. If this information is not provided, then PxPlus must query the database for this information. |
|
RECDATA=fld |
Identifies a column to return as full record. This can be used for variant records that use complex rules to identify the record type. |
|
SHARED |
Sets all tables to share a single connection to the Oracle database. (Default) |
|
STDDATE |
Overrides the above formatting on individual columns. |
|
STRIP |
Removes trailing spaces from fields |
|
TEXTMAX=nnn |
Overrides maximum size for text fields. (Default is 8192 bytes.) |
|
TOP=nnn |
Specifies use of the TOP clause in SELECT statements (limits the number of rows to return in a result set). If TOP=n is non-zero, then the KEF( ) / KEL( ) functions issue a SELECT TOP 1...SQL statement, which improves system performance. If TOP=n > 0, then PVX issues SELECT TOP n to reduce the data transferred. TOP=-1 indicates the driver supports SELECT TOP, but normal reading should not use it. Default is 0 (TOP not supported). |
|
TYP=xxxx |
Sets identifier for different variant records. See ODB/OCI Variant Record Processing. |
|
UNIQUE=Y | N |
Set to 1, Y or y to have new OPENs be on a new unique connection to the database. Default is N (shared connection). |
|
USER=uuu |
Specifies login name. |
|
Set to 1, Y or y to validate the data. This option can be included in the respective INI section by database type. Date validation for database connections will only be included if the database OPEN specifies a date format using the DATEFMT= or MAS90DATE option. For additional data validation information, see IOLIST directive. (The VALIDATE option was added in PxPlus 2017.) |
Use of Global String Variables as Option Values
In database connection options, you can provide the value following the = (equals sign) in global string variables. When opening the connection to the database, the system will check to see if the value following the = (equals sign) starts with a % and ends with a $ and is the name of a valid global string variable. If so, the value is replaced with the contents of that global variable.
For example, the option USER=%UserName$ would be dynamically changed by the EXE on the OPEN to replace the text %UserName$ with whatever is in that global variable.
(Support to allow the use of global variables for defining database Link/Prefix files was added in PxPlus 2023 Update 1.)
READ Read Data from File
READ RECORD Read Record from File
SELECT Select/Query From ... Where
WRITE Add/Update Data in File
WRITE RECORD Write Record
OPEN Open a File for Processing
IOLIST Specify Variable List
[ADO] Microsoft SQL Server Interface
[DB2] DB2 Support
[MYSQL] Open MySql Native Database Link
[ODB] Open Database
Database Export Utility
The REC= phrase is used to control the formatting of the data record as viewed by the PxPlus application. The format consists of a series of field descriptors and/or literals, each separated by either a , (comma) or a + (plus sign).
The simple format is:
REC=fieldspec{ , | + } fieldspec …
Where:
fieldspec contains the name of the field and optional format, length and scale.
Fields are separated by either a , (comma) or + (plus sign). When comma-separated, then a field delimiter is inserted. When plus-separated, then the field is padded to full size, and no separator is inserted. Literals may be included if enclosed in apostrophes.
Example:
REC=CST_ID,NAME,ADDRESS
This results in a record with three fields, each separated by a field separator:
REC=CST_ID+NAME+ADDRESS
This results in a record consisting of three fields with each one padded to its full length and no intervening field separator. If CST_ID is 6 characters long and NAME and ADDRESS are both 30, then the record would be 67 characters long, including the record terminator.
Any column name can be followed by an optional colon and format specification. This format specification consists of a data type (if not numeric or string) followed by the field length. If the field is numeric, the length includes a decimal point followed by the number of decimal positions.
The possible data types are:
|
|
P |
Packed (BIN) data |
|
|
H |
Data is stored in Hex |
|
|
B |
Data is a Binary field |
|
|
D |
Field is a Date |
Example:
|
|
CST_ID:7 |
|
|
|
OWING:8.2 |
(8 digits with 2 decimal places) |
|
|
AMOUNT:P4.2 |
(4 bytes containing BIN value scaled by 100) |
|
|
NAME:B30 |
(30 byte binary field) |
It is a good idea to include the field descriptions for all fields since this prevents PxPlus from having to read the table's data dictionary to determine field sizes and types.
Hex and Binary values can be used to store non-printable and/or binary data that would cause problems otherwise when passed in a SQL statement.
Binary fields (type P) can be used to define numeric data that has been packed into a string using the BIN( ) and DEC( ) functions. If specified, the scale indicates the number of implied decimal places that the value contains.
Literals may be inserted within the record layout to insert padding where a field or column is not presently used but space has been reserved for it. Literals should be enclosed with apostrophes and separated by a comma or plus sign.
To emulate multi-record type files (variant records), the database record must contain all possible columns. If record type 1 consists of the fields Prefix and Value when Prefix="ABC", and record type 2 consists of the fields Prefix and Percentage when the second and third characters of Prefix="EF", the database record would contain three columns Prefix, Value and Percentage.
TYP= specifies the field(s) that determine the record type. Using a ? in the REC= clause defines the value. Special masking options for ? include:
|
|
Any one character (i.e. wildcard character) | |
|
|
[abc] |
Any one of bracketed characters |
|
|
Any character from 0 to 9 | |
|
|
Indicates end-of-field | |
|
|
Indicates records that don't match |
Example:
TYP=Prefix;REC=?"ABC",Prefix,Value,?".EF",Prefix,Percentage
If the table contains two records:
"ABC",9,0
"AEF",0,99.99
Using the statement read(chan)A$,B:
On the 1st READ, A$="ABC",B=9.
On the 2nd READ, A$="AEF",B=99.99.
The following would insert a new record into the database consisting of "XEF",0,50.5:
write (chan)"XEF",50.5