Appendix B. Driver and Database Characteristics

General Information

Driver version

Feature summary

Transactions                           No
Locking                                Implicit, per-statement only
Table joins                            No
LONG/LOB datatypes                     Yes, up to 4 GB
Statement handle attributes available  After execute(  )
Placeholders                           Yes, "?" style
Stored procedures                      No
Bind output values                     No
Table name letter case                 Sensitive, partly depends on filesystem
Field name letter case                 Sensitive, stored with original letter case
Quoting of otherwise invalid names     No
Case-insensitive "LIKE" operator       Yes, "CLIKE"
Server table ROW ID pseudocolumn       No
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Jochen Wiedmann. He can be contacted via the dbi-users mailing list.

Supported database versions and options

It's important to note that while just about everyone thinks they know what the CSV file format is, there is actually no formal definition of the format, and there are many subtle differences.

Here's one description of a CSV file:

dbi:CSV:
dbi:CSV:attrs

where attrs is an optional semicolon-separated list of key=value pairs.

The number of database handles is limited by memory only. It is recommended to use multiple database handles for different table formats.

Commonly used attributes include:

f_dir=directory

By default, files in the current directory are treated as tables. The attribute f_dir makes the module open files in the given directory.

csv_eol

csv_sep_char

csv_quote_char

csv_escape_char

These attributes are used for describing the CSV file format in use. For example, to open /etc/passwd, which is colon-separated and line-feed terminated, as a table, one would use:

csv_eol=\n;csv_sep_char=:

The defaults are \r\n, comma (,), double quote ("), and double quote (") respectively. All of these attributes and defaults are inherited from the Text::CSV_XS module.

Datatypes

Numeric data handling

The SQL::Statement module, and hence the DBD::CSV driver, accepts the numeric types INTEGER and REAL in CREATE TABLE statements, but they are always stored as strings and, by default, retrieved as strings.

It is possible to read individual columns as integers or doubles, in which case they are converted to Perl's internal datatypes IV and NV -- integer and numeric value respectively. Unsigned values are not supported.

To assign certain datatypes to columns, you have to create metadata definitions. The following example reads a table table_name with columns I, N, and P of type INTEGER, DOUBLE, and STRING, respectively:

my $dbh = DBI->connect("DBI:CSV:", '', '');
$dbh->{csv_tables}->{table_name}->{types} = [
    Text::CSV_XS::IV(  ), Text::CSV_XS::NV(  ), Text::CSV_XS::PV(  )
];
my $sth = $dbh->prepare("SELECT id, sales, description FROM table_name");

String data handling

Similar to numeric values, DBD::CSV accepts more datatypes in CREATE TABLE statements than it really supports. You can use CHAR(n) and VARCHAR(n) with arbitrary numbers n, BLOB, or TEXT, but in fact these are always BLOBs, in a loose kind of way.

The one underlying string type can store any binary data including embedded NUL characters. However, many other CSV tools may choke if given such data.

Date data handling

No date or time types are directly supported.

LONG/BLOB data handling

BLOBs are equivalent to strings. They are only limited in size by available memory.

Other data handling issues

The type_info( ) method is supported.

Transactions, Isolation, and Locking

The driver doesn't support transactions.

No explicit locks are supported. Tables are locked while statements are executed, but the lock is immediately released once the statement is completed.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

Table joins are not supported.

Table and column names

Table and column names are case-sensitive. However, you should consider that table names are in fact filenames, so tables Foo and foo may both be present with the same data. However, they may be subject to different metadata definitions in $dbh->{csv_tables}.

Row ID

Row IDs are not supported.

Automatic key or sequence generation

Neither automatic keys nor sequences are supported.

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

Positioned updates and deletes are not supported.

Parameter Binding

Question marks are supported as placeholders, as in:

$dbh->do("INSERT INTO A VALUES (?, ?)", undef, $id, $name);

The :1 placeholder style is not supported.

Stored Procedures

Stored procedures are not supported.

Table Metadata

login:password:uid:gid:comment:shell:homedir
root:s34hj34n34jh:0:0:Superuser:/bin/bash:/root

If column names are not present, you may specify column names via:

$dbh->{csv_tables}->{$table}->{skip_rows} = 0;
$dbh->{csv_tables}->{$table}->{col_names} =
    [qw(login password uid gid comment shell homedir)];

in which case the first row is treated as a data row.

If column names are not supplied and not read from the first row, the names col0, col1, etc. are generated automatically.

Column names can be retrieved via the standard $sth->{NAME} attribute. The NULLABLE attribute returns an array of all ones. Other metadata attributes are not supported.

The table names, or filenames, can be read via $dbh->table_info() or $dbh->tables() as usual.

Driver-Specific Attributes and Methods

$dbh->{csv_tables}

csv_tables is used for specifying table metadata. It is a hash ref with table names as keys, the values being hash refs with the following attributes:

file

The filename being associated with the table. By default, the file name is $dbh->{f_dir}/$table.

col_names

An array ref of column names.

skip_rows

This number of rows will be read from the top of the file before reading the table data, and the first of those will be treated as an array of column names. However, the col_names attribute takes precedence.

types

This is an array ref of the Text::CSV_XS type values for the corresponding columns. Three types are supported and their values are defined by the IV(), NV(), and PV() functions in the Text::CSV_XS package.

General Information

Driver version

Feature summary

Transactions                           Yes
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner and outer
LONG/LOB datatypes                     Yes, up to 2 GB
Statement handle attributes available  After prepare(  )
Placeholders                           Yes, "?" (native)
Stored procedures                      Yes
Bind output values                     No
Table name letter case                 Insensitive, stored as uppercase
Field name letter case                 Insensitive, stored as uppercase
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       No
Server table ROW ID pseudocolumn       No
Positioned update/delete               Yes
Concurrent use of multiple handles     Unrestricted

Author and contact details

Support for the DBD::DB2 driver is provided by IBM through its service agreements for DB2 UDB. Any comments, suggestions, or enhancement requests can be sent via email to db2perl@ca.ibm.com. Please see the web site at:

dbi:DB2:database_name

There are no driver-specific attributes for the DBI->connect() method.

DBD::DB2 supports concurrent database connections to one or more databases.

Datatypes

Numeric data handling

SMALLINT
INTEGER
BIGINT
REAL
DOUBLE
FLOAT
DECIMAL or NUMERIC

A SMALLINT is a two-byte integer than can range from -32768 to +32767. The maximum precision is 5. Scale is not applicable.

An INTEGER is a four-byte integer that can range from -2147483648 to +2147483647. The maximum precision is 10. Scale is not applicable.

A BIGINT is an eight-byte integer that can range from -9223372036854775808 to +9223372036854775807. The maximum precision is 19. Scale is not applicable.

A REAL is a 32-bit approximation of a real number. The number can be or can range from -3.402e+38 to -1.175e-37, or from +1.175e-37 to +3.402e+38. The maximum precision is 7. Scale is not applicable.

A DOUBLE or FLOAT is a 64-bit approximation of a real number. The number can be or can range from -1.79769e+308 to -2.225e-307, or from 2.225e-307 to 1.79769e+308. The maximum precision is 15. Scale is not applicable.

A DECIMAL or NUMERIC value is a packed decimal number with an implicit decimal point that can range from -10**31+1 to +10**31-1. The maximum precision is 31 digits. The scale cannot be negative or greater than the precision.

Notice that DB2 supports numbers outside the typical valid range for Perl numbers. This isn't a major problem because DBD::DB2 always returns all numbers as strings.

String data handling

DB2 UDB supports the following string datatypes:

CHAR
CHAR FOR BIT DATA
VARCHAR
VARCHAR FOR BIT DATA
GRAPHIC
VARGRAPHIC

CHAR is a fixed-length character string that can be up to 254 bytes long. VARCHAR is a variable-length character string that can be up to 32672 bytes. The FOR BIT DATA variants are used for data not associated with a particular coded character set.

GRAPHIC is a fixed-length string of double-byte characters that can be up to 127 characters long.

VARGRAPHIC is a variable-length string of double-byte characters that can be up to 16336 characters long.

The CHAR and GRAPHIC types are fixed-length strings, padded with blanks.

For DB2 UDB, CHAR fields can be in mixed codesets (national character sets). The non-ASCII characters are handled according to the mixed code page definition. For example, Shift-JIS characters in the range 0x81 to 0x9F and 0xE0 to 0xFC are DBCS introducer bytes, and characters in the range 0xA0 to 0xDF are single-byte Katakana characters. Blank padding for CHAR fields is always with ASCII blank (single-byte blank). For UTF-8, characters with the sign bit set are interpreted according to the UTF-8 definition.

GRAPHIC datatypes are stored as pure double-byte in the default code page of the database, or in UCS-2 in the case of a Unicode database. Blank padding for GRAPHIC fields is always with the DBCS blank of the corresponding code page, or with the UCS-2 blank ( U+0020 ) in the case of a Unicode database.

Code page conversions between the client code page and the database code page are automatically performed by DB2 UDB.

Unicode support is provided with DB2 UDB Version 5 + FixPak 7 (DB2 UDB V5.2 is actually DB2 UDB V5 + FixPak 6). In a Unicode database, CHAR datatypes are stored in UTF-8 format and GRAPHIC datatypes are stored in UCS-2 format.

With DB2 UDB Version 6.1, the VARCHAR( ) function has been extended to convert graphic string datatypes to a VARCHAR, with the exception of LONG VARGRAPHIC and DBCLOB. This function is valid for UCS-2 databases only. For non-Unicode databases, this is not allowed.

All character types can store strings with embedded nul( "\0" ) bytes.

Strings can be concatenated using the || operator or the CONCAT(s1,s2) SQL function.

Date data handling

DB2 UDB supports the following date, time, and date/time datatypes:

DATE
TIME
TIMESTAMP

DATE is a three-part value consisting of year, month, and day. The range of the year part is 0001 to 9999. Two-digit years cannot be used with DB2 UDB. Years must be specified with all four digits.

TIME is a three-part value consisting of hour, minute, and second designates a time of day under a 24-hour clock.

TIMESTAMP is a seven-part value, consisting of year, month, day, hour, minute, second, and microsecond, that designates a date and time as defined above, except that the time includes a fractional specification of microseconds. If you specify a TIMESTAMP value without a time component, the default time is 00:00:00 (midnight).

The current date, time, and date/time can be retrieved using the CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP special registers.

DB2 UDB supports the following date, time, and date/time formats:

ISO   (International Standards Organization)
USA   (IBM USA standard)
EUR   (IBM European standard)
JIS   (Japanese Industrial Standard Christian era)
LOC   (site-defined, depends on database country code)

You can input date and date/time values in any supported format. For example:

create table datetest(dt date); 
insert into datetest('1991-10-27'); 
insert into datetest('10/27/1991');

The default output format for DATE, TIME, and TIMESTAMP is that format that is associated with the country code of the database (LOC format above). You can use the CHAR() function and specify an alternate format.

Datetime values can be incremented, decremented, and subtracted. DB2 UDB provides a wide range of date functions including DAY( ), DAYOFWEEK( ), DAYOFYEAR( ), MONTHNAME( ), and TIMESTAMPDIFF( ). See the DB2 UDB documentation for additional functions.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value to the corresponding database date/time (local time not GMT):

TIMESTAMP('1970-01-01','00:00') + seconds_since_epoch

There is no simple expression that will do the reverse. Subtracting timestamp('1970-01-01','00:00') from another timestamp gives a timestamp duration which is a DECIMAL(20,6) value with format yyyymmddhhmmss.zzzzzz.

DB2 does no automatic time zone adjustments.

LONG/BLOB data handling

DB2 UDB supports the following LONG/BLOB datatypes:

BLOB
CLOB
DBCLOB
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
LONG VARGRAPHIC

BLOB (binary large object) is a variable-length string measured in bytes that can be up to 2 GB long. A BLOB is primarily intended to hold non-traditional data such as pictures, voice, and mixed media. BLOBs are not associated with a coded character set (similar to FOR BIT DATA character strings; see below).

CLOB (character large object) is a variable-length string measured in bytes that can be up to 2 GB long. A CLOB is used to store large character-based data.

DBCLOB (double-byte character large object) is a variable-length string of double-byte characters that can be up to 1,073,741,823 characters long. A DBCLOB is used to store large DBCS character based data.

LONG VARCHAR is a variable-length character string that can be up to 32,700 bytes long. LONG VARCHAR FOR BIT DATA is used for data not associated with a coded character set.

LONG VARGRAPHIC is a variable-length string of double-byte characters that can be up to 16,350 characters long.

None of these types need to be passed to and from the database as pairs of hex digits.

Sadly, the DBD::DB2 driver does not yet support the LongReadLen and LongTruncOk attributes. Values of any length can be inserted and fetched up to the maximum size of the corresponding datatype although system resources may be a constraint.

The DBD::DB2 driver is unusual in that it requires heavy use of bind parameter attributes both for ordinary types and for LONG/BLOB types. For example, here's an attribute hash for a CLOB, which will have a maximum length of 100K in this particular application:

$attrib_clob = {
  ParamT => SQL_PARAM_INPUT,
  Ctype  => SQL_C_CHAR,
  Stype  => SQL_CLOB,
  Prec   => 100000
  Scale  => 0,
};

Other data handling issues

The DBD::DB2 driver does not yet support the type_info() method.

DB2 UDB does not automatically convert strings to numbers or numbers to strings.

Transactions, Isolation, and Locking

For the DBD::DB2 driver, the isolation level can be changed by setting the TXNISOLATION keyword in the db2cli.ini file to the desired value. This keyword is set in a database-specific section, meaning that it will affect all applications that connect to that particular database. There is no way to change the isolation level from SQL.

The default behavior for reading and writing is based on the isolation level. Rows returned by a SELECT statement can be explicitly locked by appending FOR UPDATE and a list of field names to the SELECT statement. For example:

SELECT colname1, colname2
FROM tablename
WHERE colname1 = 'testvalue'
FOR UPDATE OF colname1, colname2

The LOCK TABLE table_name IN lock_mode statement can be used to apply an explicit lock on an entire table.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

You can perform an equi-join, or inner join, using the standard WHERE a.field = b.field syntax. You can also use the following syntax:

SELECT tablea.col1, tableb.col1
 FROM tablea INNER JOIN tableb
 ON tableb.name = tablea.name

DB2 UDB supports left outer joins, right outer joins, and full outer joins. For example, to perform a left outer join, you can use the following statement:

SELECT tablea.col1, tablea.col2, tableb.col1, tableb.col2
 FROM tablea LEFT OUTER JOIN tableb
 ON tableb.name = tablea.name

Changing "LEFT" to "RIGHT" or "FULL" gives you the other forms of outer join.

Table and column names

In DB2 UDB Version 5.2, the maximum length of table names and column names is 18. In DB2 UDB Version 6.1, the maximum length of table names will be increased to 128 and the maximum length of column names will be increased to 30.

The first character must be a letter, but the rest can be any combination of uppercase letters, digits, and underscores.

Table and field names can be delimited by double quotes (") and can contain the same characters as described above plus lowercase letters.

Table and column names are stored as uppercase in the catalogs unless delimited. Delimited identifiers preserve the case. Two consecutive quotation marks are used to represent one quotation mark within the delimited identifier.

National characters can be used in table and column names.

Row ID

DB2 UDB does not support a "table row ID" pseudocolumn.

Automatic key or sequence generation

The GENERATE_UNIQUE function can be used to provide unique values (keys) in a table. For example:

CREATE TABLE EMP_UPDATE (
  UNIQUE_ID CHAR(13) FOR BIT DATA,  -- note the "FOR BIT DATA"
  EMPNO CHAR(6),
  TEXT VARCHAR(1000)
)
INSERT INTO EMP_UPDATE VALUES
  (GENERATE_UNIQUE(  ), '000020', 'Update entry...'),
  (GENERATE_UNIQUE(  ), '000050', 'Update entry...')

Sadly, DB2 does not provide any way to discover the most recent value generated by GENERATE_UNIQUE.

DB2 UDB does not support named sequence generators.

Automatic row numbering and row count limiting

There is no pseudocolumn that can be used to sequentially number the rows fetched by a SELECT statement. However, you can number the rows of a result set using the OLAP function ROWNUMBER. For example:

SELECT ROWNUMBER(  ) OVER (order by lastname) AS number, lastname, salary
FROM employee ORDER BY number;

This returns the rows of the employee table with numbers assigned according to the ascending order of last names, ordered by the row numbers.

A cursor can be declared with the FETCH FIRST n ROWS ONLY clause to limit the number of rows returned.

Positioned updates and deletes

DB2 UDB supports positioned updates and deletes. Since specific testing of this functionality has not been done with the DBD::DB2 driver, it's not officially supported; however, no problems are anticipated.

The syntax for a positioned update is as follows. DELETE has a similar syntax.

"UPDATE ... WHERE CURRENT OF $sth->{CursorName}"

Parameter Binding

The DBD::DB2 driver does not support the TYPE attribute exactly as described in the DBI documentation. Attribute hashes are used to pass type information to the bind_ param() method. An attribute hash is simply a collection of information about a particular type of data. (See the DBD::DB2 documentation for a list of predefined attribute hashes).

The following is an example of how a complete new attribute hash can be created:

$attrib_char = {
  ParamT => SQL_PARAM_INPUT,
  Ctype  => SQL_C_CHAR,
  Stype  => SQL_CHAR,
  Prec   => 254,
  Scale  => 0,
};

Stored Procedures

Stored procedures are invoked by using the following SQL syntax:

CALL procedure-name(argument, ...)

Table Metadata

DBD::DB2 does not yet support the table_info() method.

The SYSCAT.COLUMNS view contains one row for each column that is defined for all tables and views in the database.

The SYSCAT.INDEXES view contains one row for each index that is defined for all tables in a database. Primary keys are implemented as unique indexes.

Driver-Specific Attributes and Methods

General Information

Driver version

Feature summary

Transactions                           Yes
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner and outer
LONG/LOB datatypes                     Yes, up to 2 GB
Statement handle attributes available  After prepare(  )
Placeholders                           Yes, "?" (native)
Stored procedures                      Yes
Bind output values                     No
Table name letter case                 Sensitive, stored as defined
Field name letter case                 Sensitive, stored as defined
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       Yes, "MATCH"
Server table ROW ID pseudocolumn       Yes, "MS_RECORD_NUMBER"
Positioned update/delete               No
Concurrent use of multiple handles     Yes, with some restrictions

Author and contact details

Supported database versions and options

DBD::Empress supports Empress V6.10 and later. For further information refer to:

DBD::EmpressNet supports an unlimited number of concurrent database connections to one or more databases.

DBD::Empress also supports multiple concurrent database connections to one or more databases. However, these connections are simulated, and there are therefore a number of limitations. Most of these limitations are associated with transaction processing: 1) AutoCommit must be on or off for all connections; and 2) Switching processing from one database to another automatically commits any transactions on the first database.

Datatypes

Numeric data handling

DECIMAL(p,s)      1 to 15 digits
DOLLAR(p,type)    1 to 13 digits
REAL              Typically 4-byte single precision float
FLOAT(p)          Typically 4 or 8-byte float as required
LONGFLOAT         Typically 8-byte double precision float
SHORTINTEGER             -127 to 127 
INTEGER                -32767 to 32767 
LONGINTEGER       -2147483647 to 2147483647

The DBD driver supports Empress Generic datatypes only. This means that all data for a specific group will be retrieved as the same datatype. For example, SHORTINTEGER, INTEGER, and LONGINTEGER will all be retrieved as LONGINTEGER.

DBD::Empress always returns all numbers as strings.

String data handling

Empress RDBMS supports the following string datatypes:

CHAR (length, type)
NLSCHAR (length, type)        
TEXT (display_length, primary, overflow, extent)
NLSTEXT (display_length, primary, overflow, extent)

All arguments have default values. See Empress SQL Reference (A4) for details. The maximum size for all string types is typically 2**31-1 bytes (2 GB). None of the string types are blank padded.

NLSCHAR and NLSTEXT are can be used for storage of 8-bit and multibyte characters but UTF-8 is not currently supported.

Strings can be concatenated using the s1 CONCAT(s2) SQL function.

Date data handling

Empress RDBMS supports the following date/time datatypes:

DATE(t)            = 0000-01-01 to 9999-12-31 at 1 day resolution
TIME(t)            = 1970-01-01 to 2035-12-31 at 1 second resolution
MICROTIMESTAMP(t)  = 0000-01-01 to 9999-12-31 at 1 microsecond resolution

The (t) is the format type for default output. This is one of the nine types defined in the section on date/time formats.

Empress supports nine formats for date/time types:

Type  Date                Time                         MicroTimestamp
0     yyyymmdd            yyyymmddhhmmss               yyyymmddhhmmssffffff
1     dd aaaaaaaaa yyyy   dd aaaaaaaaa yyyy hh:mm:ss   dd aaaaaaaaa yyyy hh:mm:ss.
                                                       fffff
2     aaaaaaaaa dd, yyyy  aaaaaaaaa dd, yyyy hh:mm:ss  aaaaaaaaa dd, yyyy hh:mm:
                                                       ss.fffff
3     mm/dd/yy            mm/dd/yy hh:mm:ss            mm/dd/yy hh:mm:ss.ffffff
4     dd/mm/yy            dd/mm/yy hh:mm:ss            dd/mm/yy hh:mm:ss.ffffff
5     dd aaa yy           dd aaa yy hh:mm:ss           dd aaa yy hh:mm:ss.ffffff
6     aaa dd, yy          aaa dd, yy hh:mm:ss          aaa dd, yy hh:mm:ss.fffff
7     mm/dd/yyyy          mm/dd/yyyy hh:mm:ss          mm/dd/yyyy hh:mm:ss.ffffff
8     dd/mm/yyyy          dd/mm/yyyy hh:mm:ss          dd/mm/yyyy hh:mm:ss.ffffff

The date part for all types is not optional. If you specify a value without a time component, the default time is 00:00:00 (midnight). If only two digits of the year are input, then the century pivots on the Empress variable MSDATELIMIT. For Empress v8.xx and above, the default for this is 1950. Earlier versions of Empress defaulted to 1900.

Empress accepts any of the nine specified types as input. The only limitation is that you cannot insert a four-digit year into a date type that uses a two-digit format. It always uses MSDATELIMIT for input dates.

For output, DBD::Empress uses just yyyymmddhhmmssffffff and DBD:: EmpressNet uses just yyyy-mm-dd hh:mm:ss.ffffff. Empress does not support changing of the default display formats. It is not possible to format a date/time value in other styles for output. The best approach is to select the components of the date/time, using SQL functions like DAYOF(d) and MONTHOF(d), and format them using Perl.

The current date/time at the server, can be obtained using the NOW or TODAY pseudo constants. NOW returns the current date and time. TODAY returns the date portion only.

Date and time arithmetic can be done using the Empress date/time operators. For example:

NOW + 2 MINUTES + 5 SECONDS
TODAY - 3 DAYS

Empress provides a wide range of date functions including DAYOF( ), MONTHOF( ), YEAROF( ), HOUROF( ), MINUTEOF( ), SECONDOF( ), WEEKOFYEAR( ), DAYNAME( ), DAYOFWEEK( ), DAYOFYEAR( ), and DATENEXT( ).

The following SQL expression:

'1 jan 1970' + unix_time_field SECONDS

would convert to a local time from 1 Jan 1970, but the GMT base cannot be generated directly.

The number of seconds since 1 Jan 1970 for date granularity can be obtained for the local time zone (not GMT) using:

(date_field - '1 jan 1970') * 86400

Empress does no automatic time zone adjustments.

LONG/BLOB data handling

Empress RDBMS supports the following LONG datatypes:

TEXT      Variable length 7-bit character data
NLSTEXT   As TEXT but allows 8-bit characters
BULK      User Interpreted (Byte Stream)

The maximum size for all these types is typically 2**31-1 bytes (2 GB).

LongReadLen works as defined for DBD::EmpressNet but is ignored for DBD:: Empress. The maximum LongReadLen is limited to 2 GB typically. LongTruncOk is not implemented.

No special handling is required for binding LONG/BLOB datatypes. The TYPE attribute is currently not used when binding parameters. The maximum length of bind_ param( ) parameters is limited by the capabilities of the OS or the size of the C int, whichever comes first.

Other data handling issues

The type_info( ) method is not supported.

Empress automatically converts strings to numbers and dates, and numbers and dates to strings, as needed.

Transactions, Isolation, and Locking

Other transaction isolation levels are not explicitly supported. However Read Uncommited is supported on a single query basis. This is activated by adding the BYPASS option into each SQL statement.

For example:

SELECT BYPASS * FROM table_name

Record level locking is the default. Read locks do not block other read locks, but read locks block write locks, and write locks block all other locks. Write locks can be bypassed for read using the BYPASS option.

When in transaction mode (AutoCommit off), selected rows are automatically locked against update unless the BYPASS option is used in the SELECT statement.

The LOCK TABLE table_name IN lock_mode statement can be used to apply an explicit lock on a table. Lock mode can be EXCLUSIVE or SHARE. SHARE requires the user to have SELECT or UPDATE privileges on the table. EXCLUSIVE requires the user to have UPDATE, INSERT, or DELETE privileges.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

For outer joins, the Empress keyword OUTER should be placed before the table(s) that should drive the outer join. For example:

SELECT customer_name, order_date 
FROM OUTER customers, orders 
WHERE customers.cust_id = orders.cust_id;

This returns all the rows in the customer's table that have no matching rows in the orders table. Empress returns NULL for any select list expressions containing columns from the orders table.

Table and Column Names

The names of Empress identifiers, such as tables and columns, cannot exceed 32 characters in length. The first character must be a letter, but the rest can be any combination of letters, numerals, and underscores ( _ ). Empress table/column names are stored as defined. They are case-sensitive.

Empress tables and fields can contain most ASCII characters (except $ and ?) if they are quoted.

Any ISO-Latin characters can be used in the base product. Specific products for other languages, such as Japanese, can handle those character sets.

Row ID

A table row identifier can be referenced as MS_RECORD_NUMBER. It can be treated as a string during fetch; however, it must be treated as an integer when it is used in a WHERE clause. It is useful only for explicit fetch; inequalities are not allowed.

SELECT * FROM table_name WHERE MS_RECORD_NUMBER = ?

Automatic key or sequence generation

Empress has no "auto increment" or "system generated" key mechanism, and does not support sequence generators.

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

Positioned updates and deletes are not supported.

Parameter Binding

DBD::Empress recognizes the bind_ param() TYPE attribute SQL_BINARY. All other types are automatically bound correctly without TYPE being used. Unsupported types are ignored without warning.

Stored Procedures

DBD::Empress does not explicitly support stored procedures. Implicit support is available for stored procedures in SQL statements. For example:

$sth->prepare("SELECT func(attribute) FROM table_name");

Table Metadata

DBD::Empress does not support the table_info() method.

The SYS_ATTRS and SYS_TABLES system tables can be used to obtain detailed information about the columns of a table. For example:

SELECT * FROM sys_attrs
WHERE attr_tabnum = (SELECT tab_number FROM sys_tables WHERE tab_name='x')

However, this requires SELECT privileges on these system tables.

Detailed information about indices or keys cannot currently be easily retrieved though DBD::Empress. It is possible, though difficult, to interpret the contents of the system tables to obtain this information.

Driver-Specific Attributes and Methods

General Information

Driver version

Feature summary

Transactions                           Yes, if enabled when database was created
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner and outer
LONG/LOB datatypes                     Yes, up to 2 GB
Statement handle attributes available  After prepare(  )
Placeholders                           Yes, "?" (native)
Stored procedures                      Yes
Bind output values                     Yes
Table name letter case                 Configurable
Field name letter case                 Configurable
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       No
Server table ROW ID pseudocolumn       Yes, "ROWID"
Positioned update/delete               Yes
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Jonathan Leffler. He can be contacted via the dbi-users mailing list.

Supported database versions and options

The DBD::Informix module supports Informix OnLine and SE from version 5.00 onwards. There are some restrictions in the support for IUS (a.k.a., IDS/UDO). It uses Informix-ESQL/C (a.k.a., Informix ClientSDK). You must have a development license for Informix-ESQL/C (or the C-code version of Informix-4GL) to be able to compile the DBD::Informix code.

For more information, refer to:

dbi:Informix:connect_string

where connect_string is any valid string that can be passed to the Informix CONNECT statement (or to the DATABASE statement for version 5.x systems). The acceptable notations include:

dbase
dbase@server
@server
/path/to/dbase
//machine/path/to/dbase

There are no driver-specific attributes for the DBI->connect() method.

If you're using version 6.00 or later of ESQL/C, then the number of database handles is limited only by your imagination and the computer's physical constraints. If you're using 5.x, you're stuck with one connection at a time.

Datatypes

Numeric data handling

INTEGER           - signed 32-bit integer, excluding -2**31
SERIAL            - synonym for INTEGER as far as scale is concerned
SMALLINT          - signed 16-bit integer, excluding -2**15
FLOAT             - Native C 'double'
SMALLFLOAT        - Native C 'float'
REAL              - Synonym for SMALLFLOAT
DOUBLE PRECISION  - Synonym for FLOAT
DECIMAL(s)        - s-digit floating point number (non-ANSI databases)
DECIMAL(s)        - s-digit integer (MODE ANSI databases)
DECIMAL(s,p)      - s-digit fixed-point number with p decimal places
MONEY(s)          - s-digit fixed-point number with 2 decimal places
MONEY(s,p)        - s-digit fixed-point number with p decimal places
NUMERIC(s)        - synonym for DECIMAL(s)
NUMERIC(s,p)      - synonym for DECIMAL(s,p)
INT8              - signed 64-bit integer, excluding -2**63 (IDS/UDO)
SERIAL8           - synonym for INT8 as far as scale is concerned

DBD::Informix always returns all numbers as strings. Thus the driver puts no restriction on size of PRECISION or SCALE.

String data handling

Informix supports the following string datatypes:

VARCHAR(size)
NVARCHAR(size)
CHAR
CHAR(size)
NCHAR
NCHAR(size)
CHARACTER VARYING(size)
NATIONAL CHARACTER VARYING(size)
NATIONAL CHARACTER(size)
CHARACTER(size)
VARCHAR(size,min)   -- and synonyms for this type
NVARCHAR(size,min)  -- and synonyms for this type
LVARCHAR            -- IDS/UDO only

Arguably, TEXT and BYTE blobs should also be listed here, as they are automatically converted from/to strings.

CHAR types have a limit of 32767 bytes in OnLine and IDS and a slightly smaller value (325xx) for SE. For VARCHAR types, the limit is 255. LVARCHAR columns are limited to 2 KB; when used to transfer other datatypes, up to 32 KB. DBD::Informix 0.61 doesn't have fully operational LVARCHAR support.

The CHAR and NCHAR types are fixed-length and blank-padded.

Handling of national character sets depends on the database version (and is different for versions 5, for versions 6 and 7.1x, and for versions 7.2x and later). Details for version 8.x vary depending on x. It depends on the locale, determined by a wide range of standard (e.g., LANG, LC_COLLATE) and non-standard (e.g., DBNLS, CLIENT_LOCALE) environment variables. For details, read the relevant manual. Unicode is not currently directly supported by Informix (as of 1999-02-28).

Strings can be concatenated using the || operator.

Date data handling

There are two basic date/time handling types: DATE and DATETIME. DATE supports dates in the range 01/01/0001 through 31/12/9999. It is fairly flexible in its input and output formats. Internally, it is represented by the number of days since December 31 1899, so January 1 1900 was day 1. It does not understand the calendric gyrations of 1752, 1582-4, or the early parts of the first millenium, and imposes the calendar as of 1970-01-01 on these earlier times.

DATETIME has to be qualified by two components from the set:

YEAR MONTH DAY HOUR MINUTE SECOND FRACTION FRACTION(n) for n = 1..5

These store a date using ISO 8601 format for the constants. For example, DATE("29/02/2000") is equivalent to:

DATETIME("2000-02-29") YEAR TO DAY,

and The Epoch for POSIX systems can be expressed as:

DATETIME(1970-01-01 00:00:00) YEAR TO SECOND

There is no direct support for time zones.

The default date/time format depends on the environment locale settings and the version and the datatype. The DATETIME types are rigidly ISO 8601 except for converting one-digit or two-digit years to a four-digit equivalent, subject to version and environment.

Handling of two-digit years depends on the version, the bugs fixed, and the environment. In general terms (for current software), if the environment variable DBCENTURY is unset or is set to 'R', then the current century is used. If DBCENTURY is 'F', the date will be in the future; if DBCENTURY is 'P', it will be in the past; if DBCENTURY is 'C', it will be the closest date (50-year window, based on current day, month and year, with the time of day untested).

The current datetime is returned by the CURRENT function, usually qualified as CURRENT YEAR TO SECOND.

Informix provides no simple way to input or output dates and times in other formats. Whole chapters can be written on this subject.

Informix supports a draft version of the SQL2 INTERVAL datatype:

INTERVAL start[(p1)] [TO end[(p2)]]

(Where [] indicates optional parts.)

The following interval qualifications are possible:

YEAR, YEAR TO MONTH,
MONTH,
DAY, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND,
HOUR, HOUR TO MINUTE, HOUR TO SECOND,
MINUTE, MINUTE TO SECOND,
SECOND, FRACTION

p1 specifies the number of digits specified in the most significant unit of the value, with a maximum of 9 and a default of 2 (except YEAR that defaults to 4). p2 specifies the number of digits in fractional seconds, with a maximum of 5 and a default of 3.

Literal interval values may be specified using the following syntax:

INTERVAL value start[(p1)] [TO end[(p2)]]

For example:

INTERVAL(2) DAY
INTERVAL(02:03) HOUR TO MINUTE
INTERVAL(12345:67.891) MINUTE(5) TO FRACTION(3)

The expression "2 UNITS DAY" is equivalent to the first of these, and similar expressions can be used for any of the basic types.

A full range of operations can be performed on dates and intervals, e.g., datetime-datetime=interval, datetime+interval=datetime, interval/number=interval.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + seconds_since_epoch UNITS SECOND

Informix supports several transaction isolation levels: REPEATABLE READ, CURSOR STABILITY, COMMITTED READ, and DIRTY READ. Refer to the Informix documentation for their exact meaning. Isolation levels apply only to ONLINE and IDS and relatives; SE supports only a level somewhere in between COMMITTED READ and DIRTY READ.

The default isolation level depends on the type of database to which you're connected. You can use SET ISOLATION TO level to change the isolation level. If the database is unlogged (that is, it has no transaction support), you can't set the isolation level. In some more recent versions, you can also set a transaction to READ ONLY.

The default locking behavior for reading and writing depends on the isolation level, the way the table was defined, and on whether or not the database was created with transactions enabled.

Rows returned by a SELECT statement can be locked to prevent them being changed by another transaction, by appending FOR UPDATE to the select statement. Optionally, you can specify a column list in parentheses after the FOR UPDATE clause.

The LOCK TABLE table_name IN lock_mode statement can be used to apply an explicit lock on a table. The lock mode can be SHARED or EXCLUSIVE. There are constraints on when tables can be unlocked, and when locks can be applied. Row/page locking occurs with cursors FOR UPDATE. In some types of database, some cursors are implicitly created FOR UPDATE.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

All Informix versions support the basic WHERE a.field = b.field style join notation. Support for SQL-92 join notation depends on DBMS version; most do not.

Outer joins are supported. The basic version is:

SELECT * FROM A, OUTER B WHERE a.col1 = b.col2

All rows from A will be selected. Where there is one or more rows in B matching the row in A according to the join condition, the corresponding rows will be returned. Where there is no matching row in B, NULL will be returned in the B-columns in the SELECT list. There are all sorts of other contortions, such as complications with criteria in the WHERE clause, or nested outer joins.

Table and column names

For most versions, the maximum size of a table name or column name is 18 characters, as required by SQL-86. For the latest versions (Centaur, provisionally 9.2 or 7.4), the answer will be 128, as required by SQL-92. Owner (schema) names can be eight characters in the older versions and 32 in the versions with long table/column names.

The first character must be a letter, but the rest can be any combination of letters, numerals, and underscores ( _ ).

If the DELIMIDENT environment variable is set, then table and column and owner names can be quoted inside double quotes, and any characters become valid. To embed a double quote in the name, use two adjacent double quotes, such as "I said, ""Don't""". (Normally, Informix is very relaxed about treating double quotes and single quotes as equivalent, so often you could write 'I said, "Don''t"' as the equivalent of the previous example. With DELIMIDENT set, you have to be more careful.) Owner names are delimited identifiers and should be embedded in double quotes for maximum safety.

The case-preserving and case-sensitive behavior of table and column names depends on the environment and the quoting mechanisms used.

Support for using national character sets in names depends on the version and the environment (locale).

Row ID

Most tables have a virtual ROWID column which can be selected. Fragmented tables do not have one unless it is specified in the WITH ROWIDS clause when the table is created or altered. In that case, it is a physical ROWID column that otherwise appears as a virtual column (meaning SELECT * does not select it).

As with any type except the BLOB types, a ROWID can be converted to a string and used as such. Note that ROWIDs need not be contiguous, nor start at either zero or one.

Automatic key or sequence generation

The SERIAL and SERIAL8 datatypes are "auto incrementing" keys. If you insert a zero into these columns, the next previously unused key number is unrollbackably allocated to that row. Note that NULL can't be used; you have to insert a zero. If you insert a non-zero value into the column, the specified value is used instead. Usually, there is a unique constraint on the column to prevent duplicate entries.

To get the value just inserted, you can use:

$sth->{ix_sqlerrd}[1]

Informix doesn't support sequence generators directly, but you can create your own with stored procedures.

Automatic row numbering and row count limiting

Informix does not support a way to automatically number returned rows.

Some recent versions of Informix support a FIRST row count limiting directive on SELECT statements:

SELECT FIRST num_of_rows ...

Positioned updates and deletes

Positioned updates and deletes are supported using the WHERE CURRENT OF syntax. For example:

$dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

Parameter Binding

Parameter binding is directly supported by Informix. Only the standard ? style of placeholder is supported.

The TYPE attribute to bind_ param() is not currently supported, but some support is expected in a future release.

Stored Procedures

SELECT proc1(Col1) FROM SomeTable WHERE Col2 = proc2(Col3);

All stored procedures can be executed via the SQL EXECUTE PROCEDURE statement. If the procedure returns no values, it can just be executed. If the procedure does return values, even single values via a RETURN statement, then it can be treated like a SELECT statement. So after calling execute() you can fetch results from the statement handle as if a SELECT statement had been executed. For example:

$sth = $dbh->prepare("EXECUTE PROCEDURE CursoryProcedure(?,?)");
$sth->execute(1, 12);
$ref = $sth->fetchall_arrayref();

Table Metadata

Details of the columns of a table can be fetched using the private _columns() method.

The keys/indexes of a table can be fetched by querying the system catalog.

General Information

Driver version

Feature summary

Transactions                           Yes
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner and outer
LONG/LOB datatypes                     Yes, up to 2 GB
Statement handle attributes available  After prepare(  )
Placeholders                           Yes, "?" and ":1" styles (native)
Stored procedures                      Yes
Bind output values                     Yes
Table name letter case                 Insensitive, stored as uppercase
Field name letter case                 Insensitive, stored as uppercase
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       No
Server table ROW ID pseudocolumn       Yes, "tid"
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Henrik Tougaard. He can be contacted via the dbi-users mailing list.

Supported database versions and options

The DBD::Ingres module supports both Ingres 6.4 and OpenIngres (1.x & II). For more information about Ingres, refer to:

dbi:Ingres:dbname
dbi:Ingres:vnode::dbname
dbi:Ingres:dbname;options

Where options are the SQL option flags as defined in the CA-OpenIngres System Reference Guide.

There are no driver-specific attributes for the DBI->connect() method.

DBD::Ingres supports an unlimited number of concurrent database connections to one or more databases.

Datatypes

Numeric data handling

DBD::Ingres always returns all numbers as Perl numbers -- integers where possible, floating point otherwise. It is therefore possible that some precision may be lost when fetching DECIMAL types with a precision greater than Perl numbers (usually 16). If that's an issue, then convert the value to a string in the SELECT expression.

String data handling

Ingres and DBD::Ingres supports the following string datatypes:

VARCHAR(size)
CHAR(size)
TEXT(size)
C(size)

All string types have a limit of 2000 bytes. The CHAR, TEXT, and C types are fixed length and blank padded.

All string types can handle national character sets. The C type will accept only printing characters. CHAR and VARCHAR accept all character values including embedded nul characters ( "\0" ). Unicode is not formally supported yet.

Strings can be concatenated using the SQL + operator.

Date data handling

Ingres has just one date datatype: DATE. However, it can contain either an absolute date and time or a time interval. Dates and times are in second resolution between approximately 1-JAN-1581 and 31-DEC-2382. Intervals are stored to a one second resolution.

Ingres supports a variety of date formats, depending on the setting of the environment variable II_DATE_FORMAT. The default output format is US: DD-MMM-YYYY HH:MM:SS.

Many input formats are allowed. For the default format the following are accepted: MM/DD/YYYY, DD-MMM-YYYY, MM-DD-YYYY, YYYY.MM.DD, YYYY_MM_DD, MMDDYY, MM-DD, and MM/DD.

If you specify a DATE value without a time component, the default time is 00:00:00 (midnight). If you specify a DATE value without a date, the default date is the first day of the current month. If a date format that has a two-digit year, such as the YY in DD-MON-YY (a common default), then the date returned is always in the current century.

The following date-related functions are supported:

DATE(string)              - converts a string to a date
DATE_TRUNC(unit, date)    - date value truncated to the specified unit
DATE_PART(unit, date)     - integer containing the specified part
DATE_GMT(date)            - converts date to string "YYYY_MM_DD HH:MM:SS GMT"
INTERVAL(unit, interval)  - express interval as numeric count of units

The current date and time is returned by the DATE('now') function. The current date is returned by DATE('today').

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

DATE('01.01.1970 00:00 GMT')+DATE(CHAR(seconds_since_epoch)+' seconds')

And to do the reverse:

INT4(INTERVAL('seconds', DATE('now')-DATE('01.01.1970 00:00 GMT')))

A three-letter time zone name (from a limited set) can be appended to a date. If no time zone name is given, then the current client time zone is assumed. All datetimes are stored in the database as GMT and are converted back to the local time of the client fetching the data. All date comparisions in the server are done in GMT.

LONG/BLOB data handling

Ingres supports these LONG types:

LONG VARCHAR  - Character data of variable length up to 2 GB
LONG BYTE     - Raw binary data of variable length up to 2 GB

However, the DBD::Ingres driver does not yet support these types.

Other data handling issues

The DBD::Ingres driver supports the type_info( ) method.

Ingres supports automatic conversions between datatypes wherever it's reasonable.

Transactions, Isolation, and Locking

The reading of a record sets a read-lock preventing writers from changing that record and, depending on lock granularity, possibly other records. Other readers are not hindered in their reading. Writing a record sets a lock that prevents other writers from writing, and readers from reading.

The SET LOCKMODE statement allows you to change the locking granularity. It can be set to:

ROW    - lock only the affected rows (OpenIngres II only)
PAGE   - lock the page that contains the affected row
TABLE  - lock the entire table

With the statement SET LOCKMODE SESSION WHERE READLOCK=NOLOCK it is possible, but definitely not recommended, to set the isolation level to Read Uncommited.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

OpenIngres supports outer joins in ANSI SQL-92 syntax. Ingres 6.4 does not support outer joins.

Table and column names

The names of identifiers cannot exceed 32 characters. The first character must be a letter or an underscore ( _), but the rest can be any combination of letters, numerals, dollar signs ($), pound signs (#), and at signs ( @).

However, if an identifier is enclosed by double quotes ("), it can contain any combination of legal characters, including spaces but excluding quotation marks. This is not supported in Ingres 6.4.

Case significance is determined by the settings for the Ingres installation as set by the administrator when Ingres is installed.

National character sets can be used in identifiers, if enclosed in double quotes.

Row ID

The Ingres "row ID" pseudocolumn is called tid. It's an integer. It can be used without special handling. For example:

SELECT * FROM table WHERE tid=1029;

Automatic key or sequence generation

OpenIngres II supports "logical key" columns. They are defined by using a special datatype: TABLE_KEY WITH SYSTEM MAINTAINED. Ingres 6.4 required an extra-cost option to support that feature.

A column can be defined as either TABLE_KEY or OBJECT_KEY. Table_keys are unique in the table, whereas object_keys are unique in the entire database.

DBD::Ingres can't currently find the value of the last automatic key inserted, though it may do so in the future if enough people ask nicely, or someone contributes the code.

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

Positioned updates and deletes are supported in DBD::Ingres version 0.20 using the WHERE CURRENT OF syntax. For example:

$dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

The CursorName is automatically defined by DBD::Ingres for each prepared statement.

Parameter Binding

When using the bind_ param() method, the common integer, float, and char types can be defined using the TYPE attribute. Unsupported values of the TYPE attribute generate a warning.

Stored Procedures

Calling a stored procedure is done by the execute procedure statement. For example:

$dbh->do("execute procedure my_proc(param1='value')");

It is not yet possible to get results.

Table Metadata

The IICOLUMNS catalog contains information about all columns of a table.

The IIINDEXES catalog contains detailed information about all indexes in the database, one row per index. The IIINDEX_COLUMNS catalog contains information about the columns that make up each index.

Primary keys are indicated in the key_sequence field of the IICOLUMNS catalog.

Driver-Specific Attributes and Methods

ing_type

'i' for integer columns, 'f' for float, and 's' for strings

ing_ingtype

The numeric Ingres type of the columns

ing_length

The Ingres length of the columns (as used in the database)

DBD::Ingres supports just one private method:

General Information

Driver version

Feature summary

Transactions                           Yes
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner and outer
LONG/LOB datatypes                     Yes, up to 4 GB
Statement handle attributes available  After first row fetched
Placeholders                           Yes, "?" style (native)
Stored procedures                      Yes
Bind output values                     Yes
Table name letter case                 Insensitive, stored as uppercase
Field name letter case                 Insensitive, stored as uppercase
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       No
Server table ROW ID pseudocolumn       No
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Edwin Pratomo. He can be contacted via the dbi-users mailing list, or at ed.pratomo@computer.org.

Supported database versions and options

DBD::InterBase has been used to access InterBase 4.0 for Linux, and InterBase 5.5 for NT, and should also work with any version of InterBase above version 3.3 supported by IBPerl. DBD::InterBase also inherits all limitations applied to IBPerl 0.7, for instance, lack of metadata.

For further information about InterBase, refer to:

dbi:InterBase:attrs

where attrs is a semicolon-separated list of key=value pairs Valid attributes include:

database

Specifies the full path to the database within the server that should be made the default database.

host (optional)

Specify the host name of the InterBase server to connect to. Default to localhost.

role (optional)

Specify an SQL role name -- supported only in InterBase 5.0 and later.

charset (optional)

Specify the client character set to use. Useful if the client's default character set is different from the server. Using this will enable automatic character conversion from one character set to the other. Default to NONE.

DBD::InterBase supports an unlimited number of concurrent database connections to one or more databases.

Datatypes

Numeric data handling

FLOAT and INTEGER are always 32-bit, and SMALLINT is 16-bit. DOUBLE PRECISION is platform-dependent but generally 64-bit. Precision for NUMERIC/DECIMAL is from 1 to 15, and scale is from 1 to 15.

DBD::InterBase always returns all numbers as strings.

String data handling

InterBase supports the following string datatypes:

CHAR(size)      fixed length blank-padded
VARCHAR(size)   variable length with limit

Range of size is 1 to 32,767 bytes. The character set for each field may also be specified. For example:

CHAR(size) CHARACTER SET "ISO8859_1"
VARCHAR(size) CHARACTER SET "ISO8859_1"

InterBase also supports NCHAR(size) and NCHAR(size) VARYING as aliases for the CHARACTER SET "ISO8859_1" examples above.

Date data handling

InterBase supports one flexible date datatype: DATE, which includes either date, time, or date and time information. Data that will be stored as DATE datatype should be in format: DD MON YYYY HH:MM:SS, or DD-MON-YYYY HH:MM:SS. DD and MON parts must be supplied, other parts, if omitted, will be set to current year/time.

The DD MON YYYY parts can have any value from January 1, 100 AD to February 29, 32768 AD. HH:MM:SS ranges from 00:00:00 to 23:59:59.

The year part should be written in four digits, if it is only in two digits, then InterBase will infer the century number using a sliding window algorithm: subtracting the two-digit year number entered from the last two digits of the current year, if the absolute difference is greater than 50, then the century of the number entered is 20; otherwise, it is 19.

Fetched DATE values are formatted using a strftime( ) format string. This format string can be specified as DateFormat attribute when invoking prepare( ) method. If this attribute is left unspecified, then "%c" will be used as the format string. For example:

$stmt = "SELECT * FROM BDAY";
$opt = { 'DateFormat' => "%d %B %Y" };
$array_ref = $dbh->selectall_arrayref($stmt, $opt);

InterBase does not directly support SQL-92 DATE, TIME, and TIMESTAMP datatypes.

Date literals used by InterBase are: NOW, TODAY, YESTERDAY, and TOMORROW. For example:

CREATE TABLE SALES ( 
    ORDER_ID INTEGER NOT NULL,
    SHIP_DATE DATE DEFAULT "NOW" NOT NULL,
PRIMARY KEY(ORDER_ID));

LONG/BLOB data handling

InterBase supports a BLOB datatype. DBD::InterBase can handle BLOBs up to 4 GB, assuming you have that much memory in your system.

A BLOB column can be defined to hold either binary data or text data; if text, then a character set can also be specified. BLOB data is stored in segments, and the segment size (up to 64 KB) can also be specified for each BLOB column.

Other data handling issues

InterBase supports automatic conversions between datatypes wherever it is reasonable.

Transactions, Isolation, and Locking

Rows returned by a SELECT statement can be locked to prevent them being changed by another transaction, by appending FOR UPDATE to the select statement. Optionally, you can specify a column list in parentheses after the FOR UPDATE clause.

There is no explicit LOCK TABLE statement.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

Outer joins and inner joins are supported and are expressed using the ISO standard SQL syntax.

Table and column names

The maximum size of table and column names can't exceed 31 characters in length. Only alphanumeric characters can be used; the first character must be a letter.

InterBase converts all identifiers to uppercase.

Row ID

There is no "Row ID" concept.

Automatic key or sequence generation

A mechanism to create unique, sequential number that is automatically inserted at SQL operation such as INSERT, UPDATE is called GENERATOR. For example:

CREATE GENERATOR generator_name
SET GENERATOR generator_name TO integer_value

where integer_value is an integer value from -2**31 to 2**31 - 1. The SET GENERATOR command sets the starting value of a newly created generator, or resets the value of an existing generator.

To use the generator, InterBase's GEN_ID function should be invoked. For example:

INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, step))

There's no DROP GENERATOR command; here is how to delete a GENERATOR:

DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'generator_name'

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

InterBase does not support positioned updates or deletes.

Parameter Binding

The TYPE attribute of the bind_ param() as well as type_info() method are not yet supported.

Stored Procedures

InterBase does support stored procedures, but neither DBD::InterBase nor IBPerl has support for them that yet.

Table Metadata

DBD::InterBase hasn't yet supported the table_info() method.

Driver-Specific Attributes and Methods

General Information

Driver version

Version 1.20xx (even numbers) is the stable line, which is maintained for bug and portability fixes only. Version 1.21_xx (odd numbers) is used for development of the driver: all new features or interface modifications will be done in this line until it finally becomes 1.22xx.

Feature summary

Transactions                           No
Locking                                Yes, explicit (MySQL only)
Table joins                            Yes, inner and outer (inner only for mSQL)
LONG/LOB datatypes                     Yes, up to 4 GB
Statement handle attributes available  After execute(  )
Placeholders                           Yes, "?" (emulated)
Stored procedures                      No
Bind output values                     No
Table name letter case                 Depends on filesystem, stored as defined
Field name letter case                 Insensitive/Sensitive (MySQL/mSQL), stored
                                       as defined
Quoting of otherwise invalid names     No
Case-insensitive "LIKE" operator       Varies, see description below
Server table ROW ID pseudocolumn       Yes, "_rowid" (mSQL only)
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Jochen Wiedmann. He can be contacted via the mailing list Msql-Mysql-modules@lists.mysql.com.

Supported database versions and options

MySQL and mSQL are freely available, small, efficient database servers. MySQL has a rich feature set while mSQL is quite minimalist.

The DBD::mysql driver 1.20xx supports all MySQL versions since around 3.20. The DBD::mysql driver 1.21_xx supports MySQL 3.22 or later.

The DBD::mSQL drivers 1.20xx and 1.21_xx support all mSQL versions up to and including mSQL 2.0.x.

For further information about MySQL:

DBI:mysql:attrs
DBI:mSQL:attrs

where attrs is a semicolon-separated list of key=value pairs. Valid attributes include:

database=$database

The database name you want to connect to.

host=$host

The name of the machine running the server for the database you want to connect to, by default localhost.

msql_configfile=$file

Load driver-specific settings from the given file, by default InstDir/msql.conf.

mysql_compression=1

For slow connections, you may wish to compress the traffic between your client and the engine. If the MySQL engine supports it, this can be enabled by using this attribute. Default is off.

There are no driver-specific attributes applicable to the connect() method. The number of database and statement handles is limited by memory only. There are no restrictions on their concurrent use.

Datatypes

Numeric data handling

The type INT can be used as an alias for INTEGER. Other aliases include INT1=TINYINT, INT2=SMALLINT, INT3=MEDIUMINT, INT4=INT, INT8=BIGINT, and MIDDLEINT=MEDIUMINT.

Note that all arithmetic is done using signed BIGINT or DOUBLE values, so you shouldn't use unsigned big integers larger than the largest signed big integer (except with bit functions). Note that -, +, and * will use BIGINT arithmetic when both arguments are INTEGER values. This means that if you multiply two big integers (or multiply the results from functions that return integers), you may get unexpected results if the result is bigger than 9223372036854775807.

MySQL has three main non-integer datatypes: FLOAT, DOUBLE, and DECIMAL. Aliases FLOAT4 for FLOAT and FLOAT8 for DOUBLE also work.

In what follows, the letter M is used for the maximum display size or PRECISION in ODBC and DBI terminology. The letter D is used for the number of digits that may follow the decimal point. (SCALE in ODBC and DBI terminology).

Maximum display size (PRECISION) and number of fraction digits (SCALE) are typically not required. For example, if you use just "DOUBLE," then default values will be silently inserted.

DOUBLE(M,D)

A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157e+308 to -2.2250738585072014e-308, and 2.225073858 5072014e-308 to 1.7976931348623157e+308.

REAL and DOUBLE PRECISION can be used as aliases for DOUBLE.

FLOAT(M,D)

A small (single-precision) floating-point number. Allowable values are -3.402823466e+38 to -1.175494351e-38, and -1.175494351e-38 to 3.40282346 6e+38.

FLOAT(M)

A floating-point number. Precision (M) can be 4 or 8. FLOA