MySQL External Storage Formats |
Virtual Machine |
 |
When defining a MySQL table in a PROIV File and File Variable Definition, the external file type MYSQL must be specified. PROIV fields should be defined with external data types as shown in the table below, for the appropriate MySQL data type to which they relate. When defining fields for MYSQL data type NUMBER the external storage format must define the required numeric precision and scale in the format:
{S}b.a
where S indicates a signed value, b is the number of digits before the decimal point, and a is the number of digits after the decimal point.
For example, a storage format of S8.2 defines a precision of 10 (ten digits total) and a scale of 2 (two digits after the decimal point including sign). The MySQL column definition for such a numeric format is NUMBER(10,2).
Note: The display field in PROIV allows space for the sign (if specified) and decimal point (if digits after decimal point) in addition to the digits required.
|
MySQL Data Type |
PROIV Data Type |
PROIV Maximum Length |
PROIV External Datatype |
PROIV External Storage Format |
|
CHAR |
A |
1-250 |
ALPHA |
{S}b.a |
|
VARCHAR |
A |
1-250 |
ALPHA |
{S}b.a |
|
NUMERIC/DECIMAL |
N |
1-12 |
NUMBER |
{S}b.a (2) |
|
REAL/FLOAT |
N |
1-12 |
NUMBER/FLOAT |
{S}b.a (2) |
|
DOUBLE |
N |
1-12 |
NUMBER/DOUBLE |
{S}b.a (2) |
|
INTEGER (SMALLINT) |
A, N or L(1) |
1-12 |
NUMBER |
{S}b.a |
|
DATETIME (3) |
A |
20 |
DATE |
‘YYYY-MM-DD HH:MM:SS’ |
MySQL Data Types and PROIV
(1) Type L is not allowed for Key variables. Additionally, for type L the length is 1-20.
(2) Only required when using PROIV Data Types N or L.
(3) Note that the format used within PROIV is always a PROIV internal date (number of days since 31st Dec 1799) for numerics and YYYYMMDD for alphas.
Topic ID: 560022