# Structure of ODBC jEDI Mapping Files

Created At: 11/26/2019 5:21:13 PM
Updated At: 12/30/2019 5:53:55 PM
Original Doc: structure-of-odbc-jedi-mapping-files
Original ID: 498441
Internal: Yes

# ODBC jEDI File Stub

When you create a file of type *ODBC,*a small file (stub) is generated. This stub instructs the jBASE file interface that it's interacting with an ODBC jEDI type file. When a jBASE program or command interacts with an ODBC type file, the jEDI driver parses the stub and uses the connect parameter (see example stub below) to retrieve the DSN connection information string from the jEDIdrivers.ini file. It then checks an internal RDBMSmap to determine if a connection has already been made to the target RDBMS. If not, a new connection is established. Connections are persisted until the last file for a given connection is closed.

# Note

Subsequent I/O operations (e.g. SELECT, READNEXT, READ, WRITE) involve preparing SQL statements and binding parameters. Each type of operation has its own statement, and is only prepared on initial use.

Example stub:

JBC__SOB JediInitODBC CUSTOMER exist=0 connect=default expand=2 uniquekey=1 def=ID,0,5,N,,,1<tab>Name,1,255,A<tab>FirstName,2,255,A<tab>Phone,3,15,A,Agency<tab>Address,4,255,A,Agency,,,1<tab>ZIP,4,5,A,Agency,,,2<tab>City,4,26,A,Agency,,,3<tab>OrderNumber,5,5,N,OrderNumber<tab>OrderZone,6,10,A,OrderNumber|Zone<tab>UpdDate,7|8,,DT<tab>
1

Dissecting the above stub:

JBC__SOB The jBASE file type (SOB stands for Shared Object).
JediInitODBC The initializing function used the first time an ODBCtype file is used by a jBASE process.
CUSTOMER The table name.
exist Denotes whether the table existed when the CREATE-FILE was run.
connect The label corresponding to the connection information string from the jEDIdrivers.inifile.
expand The multi-value expansion type: [0,1,2]. Not applicable for existing tables.
uniquekey Typically 1, but if there is no primary/unique column available in the table this must be 0.
def A copy of the csv definition used at CREATE-FILE time (with line-breaks converted to tabs).
Must have a trailing tab to terminate the definition.

# ODBC jEDI CSV Mapping Files

If you need to create a new table in the*RDBMS,*or want a bit more control over the mapping of an existing table, you will need to create a csv file. These can be generated from multi-value dictionaries, created in notepad, or in a spread-sheet editor to facilitate the mapping of the database table schema from jBASE to the desired RDMBS. The csv file format/mapping schema is as follows:


ColumnName
Valid SQL column name

Attribute
Typically a single numeric value, with the exception of DateTime (DT) types which can have two attributes separated by a
Width Mandatory integer value for character type fields
Type

Group Association Optional group name for repeating groups.

For sub-values the required format is: 
{multi-value****name
MvGroup Controlling Flag Y or 1 to set the flag, or it can be left blank.

This flag should be set against a field that will always have a value when more than one field has the same Group Association. In jBASE terms this is akin to a Controlling dictionary.
Not Null Flag Y or 1 (This column cannotbe null),
or N, 0, or leave blank (This column can be null).
Positional multi-value For multi-value (sub-value) fields that are non-repeating, you can enter the multi-value (or sub-value if within a multi-value group) position.
Note that if positional values are used in combination with a multi-value group association, this implies positional sub-values within a multi-value. If the group association is defined as a multi-sub-value association, this implies positional multi-values with repeating sub-values.
Compound Field Literal The literal character used for fields that are composite (e.g. date*time). It is appended to the column value when retrieving data and is intended to be used to combine multiple fields in the same attribute.
The literal value does not appear in the RDBMSbecause the driver adds/removes it when reading/writing from/to the RDBMS.
Foreign Source Facilitates foreign key relationships at the RDBMSlevel. Format must be table_name.column_name, and the table_name must exist in the RDBMS. Additionally, there must also be at least two lines, the first pointing to attribute 0, and at least one pointing to an attribute other than 0.

Below is an example of a csv file containing a valid dictionary mapping schema:

CUSTOMER.csv

CUSTNBR,0,10,AN,,,1 
SPLITDT,1|2,,DT,,,1 
MVDT,3,,DT,,,1 
COMPDT,4,,DT,,,1,,* 
SIZE,5,,N 
1
2
3
4
5
6
7

# Note

The definition above defines a primary key, column “CUSTNBR”, as a VARCHAR(10).

SPLITDT: Pulling from a DateTime column of the same name, the date will appear in attribute 1 and the time in attribute 2.

MVDT: Similar to SPLITDT, but will appear as date]time on attribute 3 (i.e. multi-values 1 and 2).

COMPDT: Another DateTime that will appear as date*time on attribute 4.

SIZE: A Number type column that will appear on attribute 5.

To generate a valid csv mapping file before running the ODBC jEDI, you can use the jCreateCSV command to generate one from an existing dictionary in jBASE.

# jCreateCSV Command Syntax

The command syntax is as follows:

jCreateCSV file_name target.csv {prefix_chars} {options}

Options:

-D {type} Dictionary type build driver valid
Types:
  • J for jDC,
  • P orU for Prime/Unidata
  • A option (appended to J) for A types only (i.e. do not include ‘S’ type dictionaries in the mapping).
- O Overwrite previous definition
Leading characters we want to strip from the dictionary when creating column names in a csv. This is useful in cases where a standard prefix has been used to key the dictionaries (many 4GLs do this), and you do not want this prefix used on the column names.

Introduction to the ODBC jEDI

Installing the ODBC jEDI on Linux/Unix

Installing the ODBC jEDI on Windows

Structure of ODBC jEDI Initialization Files

Configuring the ODBC jEDI on Linux/Unix

Configuring the ODBC jEDI on Windows

Using the ODBC jEDI