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 [email protected]. 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 [email protected].

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 [email protected].

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. FLOAT(4) is a single-precision number and FLOAT(8) is a double-precision number. These types are like the FLOAT and DOUBLE types described above. FLOAT(4) and FLOAT(8) have the same ranges as the corresponding FLOAT and DOUBLE types, but their display size and number of decimals is undefined.

DECIMAL(M,D)

The DECIMAL type is an unpacked floating-point number type. NUMERIC is an alias for DECIMAL. It behaves like a CHAR column; "unpacked" means the number is stored as a string, using one character for each digit of the value, and the decimal point. If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D.

NUMERIC can be used as an alias for DECIMAL.

The numeric datatypes supported by mSQL are much more restricted:

INTEGER corresponds to MySQL's INTEGER type.

UINT corresponds to MySQL's INTEGER UNSIGNED type.

REAL corresponds to MySQL's REAL type.

The driver returns all datatypes, including numbers, as strings. It thus puts no restriction on size of PRECISION or SCALE.

String data handling

The following string types are supported by MySQL, quoted from mysql.info where M denotes the maximum display size or PRECISION:

CHAR(M)

A fixed-length string that is always right-padded with spaces to the specified length. The range of M is 1 to 255 characters.

VARCHAR(M)

A variable-length string. Note that trailing spaces are removed by the database when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters.

ENUM('value1','value2',...)

An enumeration. A string object that can have only one value, chosen from the specified list of values (or NULL). An ENUM can have a maxiumum of 65535 distinct values.

SET('value1','value2',...)

A set. A string object that can have zero or more values, each of which must be chosen from the specified list of values. A SET can have a maximum of 64 members.

CHAR and VARCHAR types have a limit of 255 bytes. Binary characters, including the NUL byte, are supported by all string types. (Use the $dbh->quote( ) method for literal strings).

These aliases are also supported:

BINARY(num)      CHAR(num) BINARY
CHAR VARYING     VARCHAR
LONG VARBINARY   BLOB
LONG VARCHAR     TEXT
VARBINARY(num)   VARCHAR(num) BINARY

With DBD::mysql, the ChopBlanks attribute is always on. The MySQL engine itself removes spaces from the string's right end. Another "feature" is that CHAR and VARCHAR columns are always case-insensitive in comparisons and sort operations, unless you use the BINARY attribute, as in:

CREATE TABLE foo (A VARCHAR(10) BINARY)

With versions of MySQL after 3.23, you can perform a case-insensitve comparison of strings with the BINARY operator modifier:

SELECT * FROM table WHERE BINARY column = "A"

National language characters are handled in comparisons following the coding system that was specified at compile-time, by default ISO-8859-1. Non-ISO coding systems, and in particular UTF-16, are not supported.

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

The mSQL engine (and hence the DBD::mSQL driver) supports only the CHAR(M) string type, which corresponds to the MySQL's VARCHAR(M) type, and a TEXT(M) type, which is a cross between a CHAR and a BLOB. All string types have trailing spaces removed by mSQL. Also, mSQL has no way to concatenate strings.

Date data handling

The following date and time types are supported by MySQL, and quoted from mysql.info:

DATE

A date. The supported range is 0000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to assign values to DATE columns using these formats:

YYMMDD
YYYYMMDD
YY.MM.DD
YYYY.MM.DD

Where . may be any non-numerical separator, and a two-digit year is assumed to be 20YY if YY is less than 70.

DATETIME

A date and time combination. The supported range is 0000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL displays DATETIME values in YYYY-MM-DD HH:MM:SS format, but allows you to assign values to DATETIME columns using the formats shown for DATE above but with HH:MM:SS appended.

TIMESTAMP(M)

A timestamp. The range is 1970-01-01 00:00:00 to sometime in the year 2032 (or 2106, depending on the OS specific type time_t). MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. This output format behavior disagrees with the manual, so check your version because the behavior may change.

A TIMESTAMP column is useful for recording the time of an INSERT or UPDATE operation because it is automatically set to the time of the last operation if you don't give it a value yourself. You can also set it to the current time by giving it a NULL value.

TIME

A time. The range is -838:59:59 to 838:59:59. MySQL displays TIME values in HH:MM:SS format. You can assign values to TIME columns using these formats: [[[DAYS] [H]H:]MM:]SS[.fraction] or [[[[[H]H]H]H]MM]SS [.fraction].

YEAR

A year. The allowable values are 1901-2155, and 0000 in the four-digit year format, and 1970-2069 if you use the two-digit year format (70-69). On input, two-digit years in the range 00-69 are assumed to be 2000-2069. (YEAR is a new type for MySQL 3.22.)

If you are using two-digit years as in YY-MM-DD (dates) or YY (years), then they are converted into 2000-2069 and 1970-1999, repectively. Thus, MySQL has no Y2K problem, but a Y2070 problem!

Since both mSQL and MySQL currently execute statements from multiple clients one at a time (atomic), and don't support transactions, there's no need for a default locking behavior to protect transaction isolation.

With MySQL, locks can be explicitly obtained on tables. For example:

LOCK TABLES table1 READ, table2 WRITE

Locks are released with any subsequent LOCK TABLES statement, by dropping a connection or with an explicit command:

UNLOCK TABLES

There are also user-defined locks that can be manipulated with the GET_LOCK() and RELEASE_LOCK() SQL functions. You can't automatically lock rows or tables during SELECT statements; you have to do it explicitly.

And, as you might guess, mSQL doesn't support any kind of locking at the moment.

SQL Dialect

Case sensitivity of LIKE operator

With MySQL, case-sensitivity of all character comparison operators, including LIKE, requires on the presence of the BINARY attribute on at least one of the fields -- either on the field type in the CREATE TABLE statement or on the field name in the comparison operator. However, you can always force case-insensitivity using the TOLOWER function.

mSQL has three LIKE operators: LIKE is case-sensitive, CLIKE is case-insensitive, and RLIKE uses Unix style regular expressions.

Table join syntax

Joins are supported with the usual syntax:

SELECT * FROM a,b WHERE a.field = b.field

or, alternatively:

SELECT * FROM a JOIN b USING field

Outer joins are supported by MySQL, not mSQL, with:

SELECT * FROM a LEFT OUTER JOIN b ON condition

Outer joins in MySQL are always left outer joins.

Table and column names

MySQL table and column names may have at most 64 characters. mSQL table and column names are limited to 35 characters.

With MySQL, you can put single quotes around table or column names (you can use the standard double quotes if the database is started with the --ansi-mode option). You need to do that if the name contains special characters or matches a reserved word. Quoting identifiers isn't supported by mSQL.

Table names are limited by the fact that tables are stored in files and the table names are really file names. In particular, the case-sensitivity of table names depends on the underlying file system and some characters like . and / are not allowed.

Column names are case-insensitive with MySQL and case-sensitive with mSQL, but both engines store them without case conversions.

Names can include national character set characters (with the eighth bit set) in MySQL but not mSQL.

Row ID

MySQL doesn't have row IDs. mSQL has a pseudocolumn _rowid.

The mSQL _rowid column value is numeric and, since mSQL doesn't automatically convert strings to numbers, you must take care not to quote the value when using it in later SELECT statements.

Note that because transactions and locking aren't supported, there's a greater risk that the row identified by a _rowid value you just fetched may have been deleted and possibly replaced by a different row by the time you use the row ID value moments later.

Automatic key or sequence generation

All MySQL integer table fields can have an AUTO_INCREMENT attribute. That is, given a table:

CREATE TABLE a (
  id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
  ...)

and a statement:

INSERT INTO a (id, ...) VALUES (NULL, ...)

a unique ID will be generated automatically (similarly, if the ID field had not been mentioned in the insert statement at all). The generated ID can later be retrieved with:

$sth->{mysql_insertid}                (1.21_xx)
$sth->{insertid}                      (1.20_xx)

or, if you've used $dbh->do and not prepare/execute, then use:

$dbh->{mysql_insertid}                (1.21_xx)
$dbh->do("SELECT LAST_INSERT_ID(  )");  (1.20_xx)

MySQL does not support sequence generators directly, but they can be emulated with a little care (refer to the MySQL manual for details). For example:

UPDATE seq SET id=last_insert_id(id+1)

The mSQL database supports sequence generators, but just one per table. After executing:

CREATE SEQUENCE on A

you can later do:

SELECT _seq FROM A

to fetch the value. You can't refer directly to the sequence from an insert statement; instead, you have to fetch the sequence value and then execute an insert with that value.

Automatic row numbering and row count limiting

Neither engine supports automatic row numbering of SELECT statement results.

Both mSQL and MySQL support row count limiting with:

SELECT * FROM A LIMIT 10

to retrieve the first 10 rows only, but only MySQL supports:

SELECT * FROM A LIMIT 20, 10

to retrieve rows 20-29, with the count starting at 0.

Positioned updates and deletes

Neither positioned updates nor deletes are supported by MySQL or mSQL.

Parameter Binding

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

The :1 placeholder style is not supported.

In the above example, the driver attempts to guess the datatype of the inserted values by looking at Perl's own internal string versus number datatype hints. This is fine with MySQL, because MySQL can deal with expressions like:

INSERT INTO table (id_number) VALUES ('2')

where id_number is a numeric column. But this doesn't apply to mSQL, which would treat that as an error. So you sometimes need to force a datatype, either by using:

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

or by using the TYPE attribute of the bind_ param() method:

use DBI qw(:sql_types);
$sth = $dbh->prepare("INSERT INTO table VALUES (?, ?)");
$sth->bind_param(1, $id,   SQL_INTEGER);
$sth->bind_param(2, $name, SQL_VARCHAR);
$sth->execute();

Unsupported values of the TYPE attribute do not currently generate a warning.

Stored Procedures

Neither mSQL nor MySQL have a concept of stored procedures, although there are plans to add some stored procedure features to MySQL.

Table Metadata

To obtain information on a generic table, you can use the query:

LISTFIELDS $table

This will return a statement handle without result rows. The TYPE, NAME, ... attributes are describing the table.

With MySQL you can use:

SHOW INDEX FROM $table

to retrieve information on a table's indexes, in particular a primary key. The information will be returned in rows. The DBD::mSQL driver does support a similar thing using:

LISTINDEX $table $index

with $index being the name of a given index.

Driver-Specific Attributes and Methods

mysql_info

mysql_thread_id

mysql_insertid

These attributes correspond to the C calls mysql_info(), mysql_thread_id(), and mysql_insertid(), respectively.

The following driver-specific statement handle attributes are supported:

mysql_use_result

mysql_store_result

With DBD::mysql, there are two different ways the driver fetches results from the server. With mysql_store_result enabled, it fetches all rows at once, creating a result table in memory and returns it to the caller (a 100% row cache).

With mysql_use_result, it returns rows to the application as they are fetched. This is less memory-consuming on the client side, but should not be used in situations where multiple people can query the database, because it can block other applications. (Don't confuse that with locking!)

mysql_insertid

A previously generated auto_increment column value, if any.

mysql_is_blob

mysql_is_key

msql_is_num

mysql_is_num

msql_is_ pri_key

mysql_is_ pri_key

These attributes return an array ref with the given flags set for any column of the result set. Note you may use these with the LISTFIELDS query to obtain information about the columns of a table.

mysql_max_length

Unlike the PRECISION attribute, this returns the true actual maximum length of the particular data in the current result set. This can be helpful, for example, when displaying ASCII tables.

This attribute doesn't work with mysql_use_result enabled, since it needs to look at all the data.

msql_table

mysql_table

Similar to NAME, but the table names and not the column names are returned.

msql_type

mysql_type

Similar to TYPE, but they return the respective engine's native type.

msql_type_name

mysql_type_name

Similar to msql_type and mysql_type, but column names are returned, that you can use in a CREATE TABLE statement.

A single private method called admin() is supported. It provides a range of administration functions:

$rc = $drh->func('createdb', $db, $host, $user, $password, 'admin');
$rc = $drh->func('dropdb',   $db, $host, $user, $password, 'admin');
$rc = $drh->func('shutdown',      $host, $user, $password, 'admin');
$rc = $drh->func('reload',        $host, $user, $password, 'admin');

$rc = $dbh->func('createdb', $database, 'admin');
$rc = $dbh->func('dropdb',   $database, 'admin');
$rc = $dbh->func('shutdown',            'admin');
$rc = $dbh->func('reload',              'admin');

General Information

Driver version

Feature summary

Because DBD::ODBC acts as an interface to other database drivers, much of its behavior is governed by those drivers.

Transactions                           Dependent on connected data source
Locking                                Dependent on connected data source
Table joins                            Dependent on connected data source
LONG/LOB datatypes                     Dependent on connected data source
Statement handle attributes available  After prepare(  )
Placeholders                           Yes
Stored procedures                      Yes
Bind output values                     No
Table name letter case                 Dependent on connected data source
Field name letter case                 Dependent on connected data source
Quoting of otherwise invalid names     Dependent on connected data source
Case-insensitive "LIKE" operator       Dependent on connected data source
Server table ROW ID pseudocolumn       Dependent on connected data source
Positioned update/delete               Yes
Concurrent use of multiple handles     Dependent on connected data source

Author and contact details

The driver authors are Jeff Urlwin and Tim Bunce. The original work was based upon an early version of Thomas Wenrich's DBD::Solid. The authors can be contacted via the dbi-users mailing list.

Supported database versions and options

For Win32, the driver manager is included with the operating system. For Unix and variants, the iODBC driver manager source is included in the iodbcsrc directory. While iODBC acts as the driver manager, you still have to find an actual driver for your platform and database.

Driver providers include:

dbi:ODBC:odbc_dsn
dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\server\share\access.mdb

In the first example above, odbc_dsn is an ODBC Data Source Name (DSN). An ODBC DSN is simply a name you use to refer to a set of driver-specific connection parameters defined elsewhere. Connection parameters typically include the name of the ODBC driver to use, the database name, and any required connection details.

Under Win32, the best method of creating an ODBC DSN is by using the ODBC32 applet on the Windows Control Panel. Under Unix variants, you typically need to edit a text file called .odbc.ini in your home directory. Refer to your driver manager documentation for more details.

The second connection example above uses the driver-specific connection string. By specifying all the required information, you can bypass the need to use a previously defined DSN. In our example we're using the "Microsoft Access Driver (*.mdb)" driver to reach the data in the \\server\share\access.mdb Access database file.

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

Most ODBC drivers and databases let you make multiple concurrent database connections to the same database. A few do not.

Some ODBC drivers and databases, most notably Sybase and SQL Server, do not let you prepare and execute a new statement handle while still fetching data from another statement handle associated with the same database handle.

Datatypes

Numeric data handling

Unfortunately, the second critical set of factors are the ODBC driver manufacturer and version of the driver. For example, I have seen a great variety in the handling of numeric values between versions of Oracle's ODBC drivers. What works with one version, sadly, may not work with even a later version of Oracle's drivers. You will need to read your ODBC driver documentation for more information.

The DBI type_info( ) and type_info_all( ) methods provide information about the datatypes supported by the database and driver being used.

String data handling

As with numeric handling, string data handling is dependent upon the database and driver. Please see "Numeric Data Handling" above for more information.

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

Date data handling

As with numeric handling, date data handling is dependent upon the database and driver. Please see "Numeric Data Handling" above for more information.

You can use ODBC escape sequences to define a date in a database-independent way. For example, to insert a date of Jan 21, 1998 into a table, you could use:

INSERT INTO table_name (date_field) VALUES ({d '1998-01-21'});

You can use placeholders within escape sequences instead of literal values. For example:

INSERT INTO table_name (date_field) VALUES ({d ?});

Similar escape sequences are defined for other date/time types. Here's the full set:

{d 'YYYY-MM-DD'}                    -- date
{t 'HH:MM:SS'}                      -- time
{ts 'YYYY-MM-DD HH:MM:SS'}          -- timestamp
{ts 'YYYY-MM-DD HH:MM:SS.FFFFFFF'}  -- timestamp

If you specify a DATE value without a time component, the default time is 00:00:00 (midnight). There is also an interval escape clause which is constructed like this:

{interval [+|-] 'value' [interval_qualifier]}

For example:

{interval '200-11' YEAR(3) TO MONTH}

Please see an ODBC reference guide for more information.

The current date and time on the server can be found by using an ODBC scalar function escape sequence to call the appropriate function. For example:

INSERT INTO table_name (date_field) VALUES ({fn CURDATE});

The {fn ...} escape sequence isn't required if the entire SQL statement conforms to the level of SQL-92 grammar supported by your ODBC driver.

Other related functions include CURTIME( ), NOW( ), CURRENT_DATE( ), CURRENT_TIME( ), and CURRENT_TIMESTAMP( ). The last three require an ODBC v3 driver.

Other date/time related functions include: DAYNAME( ), DAYOFMONTH( ), DAYOFWEEK( ), DAYOFYEAR( ), EXTRACT( ), HOUR( ), MINUTE( ), MONTH( ), MONTHNAME( ), SECOND( ), WEEK( ), and YEAR( ).

Basic date/time arithmetic can be performed using the TIMESTAMPADD( ) and TIMESTAMPDIFF( ) functions.

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

TIMESTAMPADD(SQL_TSI_SECOND, seconds_since_epoch, {d '1970-01-01'})

to do the reverse you can use:

TIMESTAMPDIFF(SQL_TSI_SECOND, {d '1970-01-01'}, date_field)

ODBC itself does not have any support for time zones, though the database to which you are connected may.

LONG/BLOB data handling

Support for LONG/BLOB datatypes and their maximum lengths are very dependent on the database to which you are connected.

The LongReadLen and LongTruncOk attributes work as defined. However, the driver implementations do affect this. Some drivers do not properly indicate that they have truncated the data, or they have more data available than was actually returned. The DBD::ODBC tests attempt to determine correct support for this.

No special handling is required for LONG/BLOB datatypes. They can be treated just like any other field when fetching or inserting, etc.

Other data handling issues

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

Transactions, Isolation, and Locking

DBD::ODBC supports transactions if the databases you are connected to supports them.

Supported isolation levels, the default isolation level, and locking behavior are all dependent on the database to which you are connected.

SQL Dialect

• Case-sensitivity of LIKE operator

• Table and column names

• Row ID

• Automatic key or sequence generation

• Automatic row numbering and row count limiting

For more information, refer to the documentation for the drivers and the database being used.

Table join syntax

Table join syntax is partly dependent on the database to which you are connected and the ODBC driver you are using. The ODBC standard SQL defines the standard syntax for inner joins and an escape sequence to use for outer joins:

{oj outer_join}

where outer_ join is defined as:

table_name [LEFT | RIGHT | FULL]
  OUTER JOIN [ table_name | outer_join] ON condition

An outer join request must appear after the FROM clause of a SELECT but before a WHERE clause, if one exists.

Positioned updates and deletes

This is dependent on the database to which you are connected. Positioned updates and deletes are supported in ODBC SQL using the WHERE CURRENT OF syntax.

For example:

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

Parameter Binding

Parameter binding is supported by DBD::ODBC if the underlying ODBC driver driver supports it. Only the standard ? style of placeholders is supported.

The TYPE attribute to the bind_ param() method is supported.

Stored Procedures

Stored procedures can be called using the following ODBC escape sequence:

{call procedure1_name}
{call procedure2_name(?, ?)}
{?= call procedure3_name(?, ?)}

The last form would be used to return values from the procedure, but DBD::ODBC currently does not support output parameters.

Table Metadata

DBD::ODBC also supports many of the ODBC metadata functions that can be used to discover information about the tables within a database. These can be accessed as driver-specific private methods:

SQLGetTypeInfo   --  $dbh->func(xxx,        'GetTypeInfo')
SQLDescribeCol   --  $sth->func(colno,      'DescribeCol')
SQLColAttributes --  $sth->func(xxx, colno, 'ColAttributes')
SQLGetFunctions  --  $dbh->func(xxx,        'GetFunctions')
SQLColumns       --  $dbh->func(catalog, schema, table, column, 'columns')
SQLStatistics    --  $dbh->func(catalog, schema, table, unique, 'Statistics')
SQLPrimaryKeys   --  $dbh->func(catalog, schema, table, 'PrimaryKeys')
SQLForeignKeys   --  $dbh->func(pkc, pks, pkt, fkc, fks, fkt, 'ForeignKeys')

The DBI will provide standard methods for these soon, possibly by the time you read this book.

Driver-Specific Attributes and Methods

DBD::ODBC has no driver-specific handle attributes.

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 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, "ROWID"
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

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

Supported database versions and options

The DBD::Oracle module supports both Oracle 7 and Oracle 8.

Building for Oracle 8 defaults to use the new Oracle 8 OCI interface, which enables use of some Oracle 8 features including LOBs and "INSERT ... RETURNING ...".

An emulation module for the old Perl4 oraperl software is supplied with DBD::Oracle, making it very easy to upgrade oraperl scripts to Perl5.

For further information about Oracle, refer to:

dbi:Oracle:tnsname
dbi:Oracle:sidname
dbi:Oracle:host=hostname;sid=sid

Some other less common formats also work if supported by the Oracle client version being used.

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

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

Datatypes

Numeric data handling

INTEGER      = NUMBER(38)
INT          = NUMBER(38)
SMALLINT     = NUMBER(38)
DECIMAL(p,s) = NUMBER(p,s)
NUMERIC(p,s) = NUMBER(p,s)
FLOAT        = NUMBER
FLOAT(b)     = NUMBER(p)   where b is the binary precision, 1 to 126
REAL         = NUMBER(18)

The NUMBER datatype stores positive and negative fixed and floating-point numbers with magnitudes between 1.0 × 10-130 and 9.9...9 × 10125 (38 nines followed by 88 zeroes), with 38 digits of precision.

You can specify a fixed-point number using the following form: NUMBER(p,s) where s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

You can specify an integer using NUMBER(p). This is a fixed-point number with precision p and scale 0. This is equivalent to NUMBER(p,0).

You can specify a floating-point number using NUMBER. This is a floating-point number with decimal precision 38. A scale value is not applicable for floating-point numbers.

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

String data handling

Oracle supports the following string datatypes:

VARCHAR2(size)
NVARCHAR2(size)
CHAR
CHAR(size)
NCHAR
NCHAR(size)
RAW(size)

The RAW type is presented as hexadecimal characters. The contents are treated as non-character binary data and thus are never "translated" by character set conversions or gateway interfaces.

CHAR types and the RAW type have a limit of 2000 bytes. For VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in Oracle 8.

The NVARCHAR2 and NCHAR variants hold string values of a defined national character set (Oracle 8 only). For those types the maximum number of characters stored may be lower when using multibyte character sets.

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

Oracle automatically converts character data between the character set of the database defined when the database was created and the character set of the client, defined by the NLS_LANG parameter for the CHAR and VARCHAR2 types or the NLS_NCHAR parameter for the NCHAR and NVARCHAR2 types.

CONVERT(string, dest_char_set, source_char_set) can be used to convert strings between character sets. Oracle 8 supports 180 storage character sets. UTF-8 is supported. See the "National Language Support" section of the Oracle Reference manual for more details on character set issues.

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

Date data handling

Oracle supports one flexible date/time datatype: DATE. A DATE can have any value from January 1, 4712 BC to December 31, 4712 AD with a one second resolution.

Oracle supports a very wide range of date formats and can use one of several calendars (Arabic Hijrah, English Hijrah, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China), and Thai Buddha). We'll only consider the Gregorian calendar here.

The default output format for the DATE type is defined by the NLS_DATE_FORMAT configuration parameter, but it's typically DD-MON-YY, e.g., 20-FEB-99 in most western installations. The default input format for the DATE type is the same as the output format. Only that one format is recognized.

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 RR format can be used instead to provide a fifty-year pivot.

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Reference.

You can change the default date format for your session with the ALTER SESSION command. For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'

The TO_DATE( ) function can be used to parse a character string containing a date in a known format. For example:

UPDATE table SET date_field = TO_DATE('1999-02-21', 'YYYY-MM-DD')

The TO_CHAR( ) function can be used to format a date. For example:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL

The current date/time is returned by the SYSDATE( ) function.

You can add numbers to DATE values. The number is interpreted as numbers of days; for example, SYSDATE + 1 is this time tomorrow, and SYSDATE - (3/1440) is three minutes ago. You can subtract two dates to find the difference, in days, between them.

Oracle provides a wide range of date functions including ROUND( ), TRUNC( ), NEXT_DAY( ), ADD_MONTHS( ), LAST_DAY( ) (of the month), and MONTHS_BETWEEN( ).

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

to_date(trunc(:unixtime/86400, 0) + 2440588, 'J') -- date part
+(mod(:unixtime,86400)/86400)                     -- time part

To do the reverse you can use:

(date_time_field - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400

Oracle does no automatic time zone adjustments. However it does provide a NEW_TIME( ) function that calculates time zone adjustments for a range of time zones. NEW_TIME(d, z1, z2) returns the date and time in time zone z2 when the date and time in time zone z1 are represented by d.

LONG/BLOB data handling

Oracle supports these LONG/BLOB datatypes:

LONG      - Character data of variable length
LONG RAW  - Raw binary data of variable length
CLOB      - A large object containing single-byte characters
NCLOB     - A large object containing national character set data
BLOB      - Binary large object
BFILE     - Locator for external large binary file

The LONG types can hold up to 2 GB. The other types (LOB and FILE) can hold up to 4 GB. The LOB and FILE types are only available when using Oracle 8 OCI.

The LONG RAW and RAW types are passed to and from the database as strings consisting of pairs of hex digits.

The LongReadLen and LongTruncOk attributes work as defined. However, the LongReadLen attribute seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building DBD::Oracle with Oracle 8 OCI raises that limit to 4 GB.

The maximum length of bind_ param( ) parameter value that can be used to insert LONG data seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building DBD::Oracle with Oracle 8 OCI raises that limit to 4 GB.

The TYPE attribute value SQL_LONGVARCHAR indicates an Oracle LONG type. The value SQL_LONGVARBINARY indicates an Oracle LONG RAW type. These values are not always required but their use is strongly recommended.

No other special handling is required for LONG/BLOB datatypes. They can be treated just like any other field when fetching or inserting, etc.

Other data handling issues

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

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

Transactions, Isolation, and Locking

Oracle supports READ COMMITED and SERIALIZABLE isolation levels. The level may be changed once per-transaction by executing a SET TRANSACTION ISOLATION LEVEL x statement (where x is the name of the isolation level required).

Oracle also supports transaction-level read consistency. This can be enabled by issuing a SET TRANSACTION statement with the READ ONLY option.

In Oracle, the default behavior is that a lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers, and writers never block readers.

Rows returned by a SELECT statement can be locked to prevent them from 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 an entire table. A range of row and table locks are supported.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

Oracle supports inner joins with the usual syntax:

SELECT * FROM a, b WHERE a.field = b.field

To write a query that performs an outer join of tables A and B and returns all rows from A, the Oracle outer join operator ( + ) must be applied to all column names of B that appear in the join condition. For example:

SELECT customer_name, order_date 
FROM customers, orders 
WHERE customers.cust_id = orders.cust_id (+);

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

Table and column names

The names of Oracle identifiers, such as tables and columns, cannot exceed thirty characters in length.

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

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

Oracle converts all identifiers to uppercase unless enclosed in double quotes. National characters can also be used when identifiers are quoted.

Row ID

The Oracle "row id" pseudocolumn is called ROWID. Oracle ROWIDs are alphanumeric case-sensitive strings. They can be treated as ordinary strings and used to rapidly (re)select rows.

Automatic key or sequence generation

Oracle supports "sequence generators". Any number of named sequence generators can be created in a database using the CREATE SEQUENCE seq_name SQL command. Each has pseudocolumns called NEXTVAL and CURRVAL. The typical usage is:

INSERT INTO table (k, v) VALUES (seq_name.nextval, ?)

To get the value just inserted you can use:

SELECT seq_name.currval FROM DUAL

Oracle does not support automatic key generation such as "auto increment" or "system generated" keys. However they can be emulated using triggers and sequence generators.

For example:

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name FOR EACH ROW
  DECLARE newid integer;
BEGIN
  IF (:NEW.key_field_name IS NULL)
  THEN
    SELECT sequence_name.NextVal INTO newid FROM DUAL;
    :NEW.key_field_name := newid;
  END IF;
END;

Oracle8i (8.1.0 and above) supports Universal Unique ID number generation, per the IETF Internet-Draft, using the new SYS_GUID( ) function. GUIDs are more useful than sequence generators in a distributed database since no two hosts will generate the same GUID.

Automatic row numbering and row count limiting

The ROWNUM pseudocolumn can be used to sequentially number selected rows (starting at 1). Sadly, however, Oracle's ROWNUM has some frustrating limitations. Refer to the Oracle SQL documentation.

Positioned updates and deletes

Oracle does not support positioned updates or deletes.

Parameter Binding

The SQL_CHAR type is bound as a CHAR, thus enabling fixed-width, blank-padded comparison semantics.

The SQL_BINARY and SQL_VARBINARY types are bound as RAW. SQL_LONGVARBINARY is bound as LONG RAW and SQL_LONGVARCHAR as LONG.

Unsupported values of the TYPE attribute generate a warning.

Refer to the DBD::Oracle documentation for details of how to bind LOBs and CURSORs.

Stored Procedures

The DBD::Oracle module can be used to execute a block of PL/SQL code by starting it with a BEGIN and ending it with an END;. PL/SQL blocks are used to call stored procedures. Here's a simple example that calls a stored procedure called "foo" and passes it two parameters:

$sth = $dbh->prepare("BEGIN foo(:1, :2); END;");
$sth->execute("Baz", 24);

Here's a more complex example that shows a stored procedure being called with two parameters and returning the return value of the procedure. The second parameter of the procedure is defined as IN OUT so we bind that using bind_ param_inout() to enable it to update the Perl variable:

$sth = $dbh->prepare("BEGIN :result = func_name(:id, :changeme); END;");
$sth->bind_param(":id", "FooBar");
my ($result, $changeme) = (41, 42);
$sth->bind_param_inout(":result",  \$result,  100);
$sth->bind_param_inout(":changeme", \$changeme, 100);
$sth->execute();
print "func_name returned '$result' and updated changeme to '$changeme'\n";

Table Metadata

The ALL_TABLES view contains detailed information about all tables in the database, one row per table.

The ALL_TAB_COLUMNS view contains detailed information about all columns of all the tables in the database, one row per table.

The ALL_INDEXES view contains detailed information about all indexes in the database, including primary keys, one row per index.

The ALL_IND_COLUMNS view contains information about the columns that make up each index.

(Note that for all these views, fields containing statistics derived from the actual data in the corresponding table are updated only when the SQL ANALYSE command is executed for that table.)

Driver-Specific Attributes and Methods

The following private methods are supported:

General Information

Driver version

Feature summary

Transactions                           Yes
Locking                                Yes, implicit and explicit
Table joins                            Yes, inner only
LONG/LOB datatypes                     Yes, max size depends on filesystem
Statement handle attributes available  After execute(  )
Placeholders                           Yes, "?" and ":1" styles (native)
Stored procedures                      No
Bind output values                     No
Table name letter case                 Insensitive, stored as lowercase
Field name letter case                 Insensitive, stored as lowercase
Quoting of otherwise invalid names     Yes, via double quotes
Case-insensitive "LIKE" operator       No, but has "~*" case-insensitive regex
                                       match
Server table ROW ID pseudocolumn       Yes, "oid"
Positioned update/delete               No
Concurrent use of multiple handles     Unrestricted

Author and contact details

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

Supported database versions and options

The DBD-Pg-0.91 module supports PostgreSQL 6.4. For futher information please refer to:

dbi:Pg:dbname=$dbname
dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty

All parameters, including the userid and password parameter of the connect command, have a hard-coded default that can be overridden by setting appropriate environment variables.

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

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

Datatypes

Numeric data handling

Some platforms also support the int8 type. DBD::Pg always returns all numbers as strings.

String data handling

PostgreSQL supports the following string datatypes:

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

All string datatypes have a limit of 4096 bytes. The CHAR type is fixed length and blank padded.

There is no special handling for data with the eighth bit set. They are stored unchanged in the database. None of the character types can store embedded nulls and Unicode is not formally supported.

Strings can be concatenated using the || operator.

Date data handling

PostgreSQL supports the following date/time datatypes:

PostgreSQL supports a range of date formats:

The default output format does not depend on the client/server locale. It depends on, in increasing priority: the PGDATESTYLE environment variable at the server, the PGDATESTYLE environment variable at the client, and the SET DATESTYLE SQL command.

All of the formats described above can be used for input. A great many others can also be used. There is no specific default input format. If the format of a date input is ambiguous then the current DATESTYLE is used to help disambiguate.

If you specify a date/time value without a time component, the default time is 00:00:00 (midnight). To specify a date/time value without a date is not allowed. If a date with a two-digit year is input, then if the year was less than 70, add 2000; otherwise, add 1900.

The current date/time is returned by the keyword `now' or `current' , which has to be cast to a valid datatype. For example:

SELECT 'now'::datetime

PostgreSQL supports a range of date/time functions for converting between types, extracting parts of a date/time value, truncating to a given unit, etc. The usual arithmetic can be performed on date and interval values, e.g., date-date=interval, etc.

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(unixtime_field)

and to do the reverse:

DATE_PART('epoch', datetime_field)

The server stores all dates internally in GMT. Times are converted to local time on the database server before being sent to the client frontend, hence by default are in the server time zone.

The TZ environment variable is used by the server as default time zone. The PGTZ environment variable on the client side is used to send the time zone information to the backend upon connection. The SQL SET TIME ZONE command can set the time zone for the current session.

LONG/BLOB data handling

PostgreSQL handles BLOBs using a so-called "large objects" type. The handling of this type differs from all other datatypes. The data are broken into chunks, which are stored in tuples in the database. Access to large objects is given by an interface which is modelled closely after the Unix file system. The maximum size is limited by the file size of the operating system.

If you just select the field, you get a "large object identifier" and not the data itself. The LongReadLen and LongTruncOk attributes are not implemented because they don't make sense in this case. The only method implemented by the driver is the undocumented DBI method blob_read( ).

Other data handling issues

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

Transactions, Isolation, and Locking

With AutoCommit on, a query never places a lock on a table. Readers never block writers, and writers never block readers. This behavior changes whenever a transaction is started (with AutoCommit off). Then a query induces a shared lock on a table and blocks anyone else until the transaction has been finished.

The LOCK TABLE table_name statement can be used to apply an explicit lock on a table. This works only inside a transaction (with AutoCommit off).

To ensure that a table being selected does not change before you make an update later in the transaction, you must explicitly lock it with a LOCK TABLE statement before executing the select.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

Outer joins are not supported. Inner joins use the traditional syntax.

Table and column names

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

If an identifier is enclosed by double quotes (" ), it can contain any combination of characters except double quotes.

PostgreSQL converts all identifiers to lowercase unless enclosed in double quotes. National character set characters can be used, if enclosed in quotation marks.

Row ID

The PostgreSQL "row id" pseudocolumn is called oid, object identifier. It can be treated as a string and used to rapidly (re)select rows.

Automatic key or sequence generation

PostgreSQL does not support automatic key generation such as "auto increment" or "system generated" keys.

However, PostgreSQL does support "sequence generators." Any number of named sequence generators can be created in a database. Sequences are used via functions called NEXTVAL and CURRVAL. The typical usage is:

INSERT INTO table (k, v) VALUES (NEXTVAL('seq_name'), ?);

To get the value just inserted, you can use the corresponding currval( ) SQL function in the same session, or:

SELECT last_value FROM seq_name

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

PostgreSQL does not support positioned updates or deletes.

Parameter Binding

The TYPE attribute of the bind_ param() method may be used to influence how parameters are treated. These SQL types are bound as VARCHAR: SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, and SQL_VARCHAR.

The SQL_CHAR type is bound as a CHAR, thus enabling fixed-width, blank-padded comparison semantics.

Unsupported values of the TYPE attribute generate a warning.

Stored Procedures

DBD::Pg does not support stored procedures.

Table Metadata

DBD::Pg supports the table_info() method.

The pg_attribute table contains detailed information about all columns of all the tables in the database, one row per table.

The pg_index table contains detailed information about all indexes in the database, including primary keys, one row per index.

Driver-Specific Attributes and Methods

There are no significant DBD::Pg driver-specific database handle attributes.

pg_size

Returns a reference to an array of integer values for each column. The integer shows the storage (not display) size of the column in bytes. Variable length columns are indicated by -1.

pg_type

Returns a reference to an array of strings for each column. The string shows the name of the datatype.

pg_oid_status

Returns the OID of the last INSERT command.

pg_cmd_status

Returns the name of the last command type. Possible types are: INSERT, DELETE, UPDATE, and SELECT.

DBD::Pg has no private methods.

Other Significant Database or Driver Features

PostgreSQL offers substantial additional power by incorporating the following four additional basic concepts in such a way that users can easily extend the system: classes, inheritance, types, and functions.

Other features provide additional power and flexibility: constraints, triggers, rules, transaction integrity, procedural languages, and large objects.

It's also free Open Source Software with an active community of developers.

General Information

Driver version

This driver was previously known as DBD::Fulcrum.

Feature summary

Transactions                           No
Locking                                Yes, implicit and explicit
Table joins                            No, but see description below
LONG/LOB datatypes                     Yes, up to 2 GB
Statement handle attributes available  After execute(  )
Placeholders                           Yes, "?" and ":1" styles (emulated)
Stored procedures                      No
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       Yes, "LIKE"
Server table ROW ID pseudocolumn       Yes, "FT_CID"
Positioned update/delete               Yes
Concurrent use of multiple handles     Unrestricted

Author and contact details

The driver author is Davide Migliavacca. He can be contacted via the dbi-users mailing list. Davide Migliavacca has no relationship with PCDOCS/Fulcrum, the maker of SearchServer, and particularly no contact with product support for PCDOCS/Fulcrum customers.

Supported database versions and options

The DBD::SearchServer module supports PCDOCS/Fulcrum SearchServer, versions 2.x thru 3.5.

For further information about SearchServer, refer to:

dbi:SearchServer:

Under WIN32, you may use the fully qualified DSN syntax using the ODBC data source name as the third component of the connect string:

dbi:SearchServer:DSN

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

DBD::SearchServer supports an unlimited number of concurrent database connections to the same server.

Datatypes

Numeric data handling

String data handling

SearchServer supports the following string datatypes:

CHAR(size)
VARCHAR(size)
APVARCHAR(size)

A CHAR column is of fixed size, whereas a VARCHAR column can be of varying length up to the specified maximum size. If the size is not specified, it defaults to 1. The maximum size for a CHAR or VARCHAR column is 32,767.

APVARCHAR is a special datatype. You can have at most one APVARCHAR column per table; it is designed to contain the full text of the document to be indexed and it is used in queries to retrieve the text. It is eventually modified to identify spots where the query matched. The maximum length of the APVARCHAR column is 2,147,483,647.

The CHAR type is fixed-length and blank-padded to the right.

SearchServer has its own conversion functionality for national language character sets. Basically, it treats all text as being specified in one of three internal character sets (FTICS). It is up to the application to use character sets consistently. The document readers (software that is used by SearchServer to actually access documents when indexing) are responsible for translating from other characters sets to FTICS. A number of "translation" filters are distributed with the product.

ISO Latin 1 (8859-1) is supported. See the "Character Sets" section of the SearchSQL Reference Manual for more details on character set issues.

Date data handling

SearchServer supports only a DATE datatype. A DATE can have any value from January 1, 100 AD to December 31, 2047 AD with one-day resolution. Rows in tables have an automatic read-only FT_TIMESTAMP column with a better resolution, but it is not of a DATE type (it is an INTEGER). Also, only date literals can be used with DATE columns.

The date format is YYYY-MM-DD (ISO standard). There are provisions for other formats, but their use is discouraged.

Only the ISO date format is recognized for input.

If a two-digit year value is entered, then 1900 is added to the value. However, this isn't supported functionality, for good reason.

No date/time arithmetic or functions are provided, and there is no support for time zones.

LONG/BLOB data handling

The APVARCHAR type can hold up to 2 GB.

LongReadLen and LongTruncOk are ignored due to very different semantics of the APVARCHAR type.

You need to use the undocumented blob_read( ) method to fetch data from an APVARCHAR column. Inserting an APVARCHAR column happens indirectly by specifying an external document in the FT_SFNAME reserved column. Document data is not really inserted into the tables, it is indexed. Later, however, you can fetch the document selecting the APVARCHAR column.

Other data handling issues

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

Transactions, Isolation, and Locking

Locking is performed based on the characteristics of the table, set at creation time or modified later with an external utility, ftlock.

By default, ROWLOCKING is applied, which applies "transient" locks during normal operations including select, searched update, and delete. These locks should not prevent reading the affected rows, but will block additional concurrent modifications, and prevent reindexing of the locked rows.

If set to NOLOCKING, no locking will be performed on that table by the engine, meaning that data integrity is left for the application to manage. Please read the documentation carefully before playing with these parameters; there is additional feedback with the PERIODIC or IMMEDIATE indexing mode.

Rows returned by a SELECT statement can be locked to prevent them from being changed by another transaction, by appending FOR UPDATE to the select statement.

There is no explicit table lock facility. You can prevent a table schema being modified, dropped, or even reindexed using PROTECT TABLE, but this does not include row-level modifications, which are still allowed. UNPROTECT TABLE restores normal behavior.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

SearchServer does not really support joins, however it does support a kind of view mechanism.

With views, tables must be located on the same node and have the same schema. Only read-only access is granted with views, and they have to be described using a special syntax file. Please refer to the "Data Administration and Preparation" manual for more information on views.

Table and column names

Letters, numbers, and underscores ( _) are valid characters in identifiers. The maximum size of table and column names is not known at this time.

SearchServer converts all identifiers to uppercase. Table and column names are not case-sensitive. National characters can be used in identifier names.

Row ID

The SearchServer "row id" pseudocolumn is called FT_CID and is of the INTEGER datatype. FT_CID can be used in a WHERE clause, but only with the = operator.

Automatic key or sequence generation

SearchServer does not support automatic key generation such as "auto increment" or "system generated" keys. However, the integer FT_CID pseudocolumn is not reissued when rows are deleted.

There is no support for 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 supported using the WHERE CURRENT OF syntax. For example:

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

Parameter Binding

The TYPE attribute to bind_ param() is ignored, so no warning is generated for unsupported values.

Stored Procedures

There are no stored procedures or functions in SearchServer.

Table Metadata

DBD::SearchServer supports the table_info() method.

The COLUMNS system table contains detailed information about all columns of all the tables in the database, one row per column. The COLUMNS system table uses the INDEX_MODE column to identify indexed columns and which indexing mode is used for them.

Driver-Specific Attributes and Methods

DBD::SearchServer has no driver-specific database handle attributes. It does have one driver-specific statement handle attribute:

ss_last_row_id

This attribute is read-only and is valid after an INSERT, DELETE, or UPDATE statement. It will report the FT_CID (row ID) of the last affected row in the statement. You'll have to prepare/execute the statement (as opposed to simply do()-ing it) in order to fetch the attribute.

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 execute(  )
Placeholders                           Yes, "?" style (native), see text below
Stored procedures                      Yes
Bind output values                     No, all values returned via fetch methods
Table name letter case                 Sensitive, stored as defined, configurable
Field name letter case                 Sensitive, stored as defined, configurable
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     Statement handles restricted, see below

Author and contact details

The driver author is Michael Peppler. He can be contacted via the dbi-users mailing list, or at [email protected].

Supported database versions and options

The DBD::Sybase module supports Sybase 10.x and 11.x, and offers limited support for accessing Sybase 4.x and Microsoft MS-SQL servers, assuming availability of Sybase OpenClient, or the FreeTDS libraries.

The standard release of MS-SQL 7 can not be accessed using the Sybase libraries, but can be used using the FreeTDS libraries. There is a patch for MS-SQL 7 to allow Sybase clients to connect:

dbi:Sybase:attrs

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

server

Specifies the Sybase server to connect to.

database

Specifies the database within the server that should be made the default database for this session (via USE database).

charset

Specifies 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.

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

It is not normally possible for Sybase clients to prepare/execute a new statement handle while still fetching data from another statement handle that is associated with the same database handle. However, DBD::Sybase emulates this process by opening a new connection that will automatically be closed when the new statement handle is destroyed. You should be aware that there are some subtle but significant transaction issues with this approach.

Datatypes

Numeric data handling

INTEGER is always a 32-bit int, SMALLINT is 16-bit, and TINYINT is 8-bit. All others except the NUMERIC/DECIMAL datatypes are hardware specific. Precision for NUMERIC/DECIMAL is from 1 to 38, and scale is from to 38.

NUMERIC/DECIMAL values are returned as Perl strings by default, even if the scale is and the precision is small enough to fit in an integer value. All other numbers are returned in native format.

String data handling

DBD::Sybase supports CHAR, VARCHAR, BINARY, and VARBINARY, all limited to 255 characters in length. The CHAR type is fixed-length and blank-padded.

Sybase automatically converts CHAR and VARCHAR data between the character set of the server (see the syscharset system table) and the character set of the client, defined by the locale setting of the client. The BINARY and VARBINARY types are not converted. UTF-8 is supported.

See the "OpenClient International Developer's Guide" in the Sybase OpenClient manuals for more on character set issues.

Strings can be concatenated using the SQL + operator.

Date data handling

Sybase supports the DATETIME and SMALLDATETIME values. A DATETIME can have a value from Jan 1 1753 to Dec 31, 9999 with a 300th of a second resolution. A SMALLDATETIME has a range of Jan 1 1900 to Jun 6 2079 with a one-minute resolution.

The current date on the server is obtained with the GETDATE( ) SQL function.

The Sybase date format depends on the locale settings for the client. The default date format is based on the "C" locale:

Feb 16 1999 12:07PM

In this same locale, Sybase understands several input formats in addition to the one above:

2/16/1998 12:07PM
1998/02/16 12:07
1998-02-16 12:07
19980216 12:07

If the time portion is omitted, it is set to 00:00. If the date portion is omitted, it is set to Jan 1 1900. If the century is omitted, it is assumed to be 2000 if year < 50, and 1900 if year >= 50.

You can use the special _date_fmt( ) private method (accessed via $dbh->func( )) to change the date input and output format. The formats are based on Sybase's standard conversion routines. The following subset of available formats has been implemented:

LONG        - Nov 15 1998 11:30:11:496AM
SHORT       - Nov 15 1998 11:30AM
DMY4_YYYY   - 15 Nov 1998
MDY1_YYYY   - 11/15/1998
DMY1_YYYY   - 15/11/1998
HMS         - 11:30:11

Use the CONVERT( ) SQL function to convert date and time values from other formats. For example:

UPDATE a_table 
    SET date_field = CONVERT(datetime_field, '1999-02-21', 105)

CONVERT( ) is a generic conversion function that can convert to and from most datatypes. See the CONVERT( ) function in Chapter 2 of the Sybase Reference Manual.

Arithmetic on date/time types is done on dates via the DATEADD( ), DATEPART( ), and DATEDIFF( ) Transact SQL functions. For example:

SELECT DATEDIFF(ss, date1, date2)

returns the difference in seconds between date1 and date2.

Sybase does not understand time zones at all, except that the GETDATE( ) SQL function returns the date in the time zone that the server is running in (via localtime).

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

DATEADD(ss, unixtime_field, 'Jan 1 1970')

Note however that the server does not understand time zones, and will therefore give the local Unix time on the server, and not the correct value for the GMT time zone.

If you know that the server runs in the same time zone as the client, you can use:

use Time::Local;
$time_to_database = timegm(localtime($unixtime));

to convert the Unix time value before sending it to Sybase.

To do the reverse, converting from a database date/time value to Unix time, you can use:

DATEDIFF(ss, 'Jan 1 1970', datetime_field)

The same GMT versus localtime caveat applies in this case. If you know that the server runs in the same time zone as the client, you can convert the returned value to the correct GMT-based value with this Perl expression:

use Time::Local;
$time = timelocal(gmtime($time_from_database));

LONG/BLOB data handling

Sybase supports an IMAGE and a TEXT type for LONG/BLOB data. Each type can hold up to 2 GB of binary data, including nul characters. The main difference between an IMAGE and a TEXT column lies in how the client libraries treat the data on input and output. TEXT data is entered and returned "as is." IMAGE data is returned as a long hex string, and should be entered in the same way.

LongReadLen and LongTrunkOk attributes have no effect. The default limit for TEXT/IMAGE data is 32 KB, but this can be changed by the SET TEXTSIZE Transact-SQL command.

Bind parameters can not be used to insert TEXT or IMAGE data to Sybase.

Other data handling issues

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

Sybase does not automatically convert numbers to strings or strings to numbers. You need to explicitly call the CONVERT SQL function. However, placeholders don't need special handling because DBD::Sybase knows what type each placeholder needs to be.

Transactions, Isolation, and Locking

Sybase supports READ COMMITED, READ UNCOMMITED, and SERIALIZABLE isolation levels. The level be changed per-connection or per-statement by executing SET TRANSACTION_ISOLATION LEVEL x, where x is for READ UNCOMMITED, 1 for READ COMMITED, and 3 for SERIALIZABLE.

By default, a READ query will acquire a shared lock on each page that it reads. This will allow any other process to read from the table, but will block any process trying to obtain an exclusive lock (for update). The shared lock is only maintained for the time the server needs to actually read the page, not for the entire length of the SELECT operation. (11.9.2 and later servers have various additional locking mechanisms.)

There is no explicit LOCK TABLE statement. Appending WITH HOLDLOCK to a SELECT statement can be used to force an exclusive lock to be acquired on a table, but is rarely needed.

The correct way to do a multi-table update with Sybase is to wrap the entire operation in a transaction. This will ensure that locks will be acquired in the correct order, and that no intervening action from another process will modify any rows that your operation is currently modifying.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

Outer joins are supported using the =* (right outer join) and *= (left outer join) operators:

SELECT customers.customer_name, orders.order_date 
FROM customers, orders 
WHERE customers.cust_id =* orders.cust_id

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

Table and column names

The names of identifiers, such as tables and columns, cannot exceed thirty characters in length.

The first character must be an alphabetic character (as defined by the current server character set) or an underscore ( _ ). Subsequent characters can be alphabetic, and may include currency symbols, @, #, and _. Identifiers can't include embedded spaces or the %, !, ^, *, or . symbols. In addition, identifiers must not be on the "reserved word" list (see the Sybase documentation for a complete list).

Table names or column names may be quoted if the set quoted_identifier option is turned on. This allows the user to get around the reserved word limitation. When this option is set, character strings enclosed in double quotes are treated as identifiers, and strings enclosed in single quotes are treated as literal strings.

By default identifiers are case-sensitive. This can be turned off by changing the default sort order for the server.

National characters can be used in identifier names without quoting.

Row ID

Sybase does not support a pseudo "row ID" column.

Automatic key or sequence generation

Sybase supports an IDENTITY feature for automatic key generation. Declaring a table with an IDENTITY column will generate a new value for each insert. The values assigned always increase but are not guaranteed to be sequential.

To fetch the value generated and used by the last insert, you can:

SELECT @@IDENTITY

Firstly, DBD::Sybase creates an internal stored procedure for each prepare() call that includes ? style parameters. These stored procedures live in the tempdb database, and are only destroyed when the connection is closed. It is quite possible to run out of tempdb space if a lot of prepare() calls with placeholders are being made in a script.

Secondly, because all the temporary stored procedures are created in tempdb, this causes a potential hot spot due to the locking of system tables in tempdb. This performance problem may be removed in an upcoming release of Sybase (possibly 11.9.4 or 12.0).

The :1 placeholder style is not supported and the TYPE attribute to bind_ param() is currently ignored, so unsupported values don't generate a warning. Finally, trying to bind a TEXT or IMAGE datatype will fail.

Stored Procedures

Stored procedures are called exactly the same way as regular SQL, and can return the same types of results (i.e., a SELECT in the stored procedure can be retrieved with $sth->fetch()).

If the stored procedure returns data via OUTPUT parameters, then these must be declared first:

$sth = $dbh->prepare(qq[
   declare \@name varchar(50)
   exec getName 1234, \@name output
]);

Stored procedures can't be called with bind (?) parameters. So the following code would be illegal:

$sth = $dbh->prepare("exec my_proc ?");  # illegal
$sth->execute($foo);

Use this code instead:

$sth = $dbh->prepare("exec my_proc '$foo'");
$sth->execute();

Because Sybase stored procedures almost always return more than one result set, you should always make sure to use a loop until syb_more_results is 0:

do {
  while($data = $sth->fetch) {
     ...
  }
} while($sth->{syb_more_results});

Table Metadata

DBD::Sybase supports the table_info() method.

The syscolumns table has one row per column per table. See the definitions of the Sybase system tables for details. However, the easiest method to obtain table metadata is to use the sp_help stored procedure.

The easiest way to get detailed information about the indexes of a table is to use the sp_helpindex (or sp_helpkey) stored procedure.

Driver-Specific Attributes and Methods

syb_show_sql

If set, then the current statement is included in the string returned by $dbh->errstr.

syb_show_eed

If set, then extended error information is included in the string returned by $dbh->errstr. Extended error information includes the index causing a duplicate insert to fail, for example.

DBD::Sybase has the following driver-specific statement handle attributes:

syb_more_results

Described elsewhere in this document.

syb_result_type

Returns the numeric result type of the current result set. Useful when executing stored procedures to determine what type of information is currently fetchable (normal select rows, output parameters, status results, etc.).

One private method is provided:

_date_fmt

Sets the default date conversion and display formats. See the description elsewhere in this document.

Other Significant Database or Driver Features

Sybase and DBD::Sybase allow multiple statements to be prepared with one call and then executed with one call. The results are fed back to the client as a stream of tabular data. Stored procedures can also return a stream of multiple data sets. Each distinct set of results is treated as a normal single result set, so fetch() returns undef at the end of each set. To see if there are more data sets to follow, the syb_more_results attribute can be checked. Here is a typical loop making use of this Sybase-specific feature:

do {
  while($d = $sth->fetch) {
    ... do something with the data
  }
} while($sth->{syb_more_results});

General Information

Driver version

Feature summary

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

Author and contact details

The driver author is Jan Pazdziora. He can be contacted at [email protected] or via the dbi-users mailing list.

Supported database versions and options

The DBD::XBase module supports dBaseIII and IV and Fox* flavors of dbf files, including their dbt and fpt memo files.

Very comprehensive information about the XBase format, along with many references, can be found at:

dbi:XBase:/path/to/directory

It defaults to the current directory.

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

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

Datatypes

Numeric data handling

Existing dbf files come with the field types defined in the dbf file header. Numeric types can be either stored as ASCII string or in some binary format. DBD::XBase (via XBase.pm) parses this information and reads and writes the fields in that format.

When you create a new dbf file via CREATE TABLE, the numeric fields are always created in the traditional XBase way, as an ASCII string. (The XBase.pm module offers more control over this process.)

Numeric fields are always returned as Perl numeric values, not strings. Consequently, numbers outside of Perl's valid range are not possible. This restriction might be withdrawn in the future.

String data handling

DBD::XBase has CHAR(length) and VARCHAR(length) datatypes.

Both CHAR and VARCHAR are blank-padded, so ChopBlanks applies to both.

Data with the eighth bit set are handled transparently. No national language character set conversions are done. Since the string types can store binary data, Unicode strings can be stored.

Date data handling

DBD::XBase supports these date and time types:

DATE
DATETIME
TIME

The DATE type holds an eight-character string in the format YYYYMMDD. Only that format can be used for input and output. DBD::XBase doesn't check for validity of the values.

The DATETIME and TIME types internally store a four-byte integer day value and a four-byte integer seconds value (counting 1/1000's of a second). DBD::XBase inputs and outputs these types using a floating-point Unix-style "seconds-since-epoch" value (possibly with decimal part). This might change in the future.

There is no way to get the current date/time, and no SQL date/time functions are supported. There is also no concept of time zones.

LONG/BLOB data handling

DBD::XBase supports a MEMO datatype. BLOB can be used as an alias for MEMO. Strings up to 2 GB can be stored in MEMO fields (for all types of XBase memo files).

With dBaseIII dbt files, the memo field cannot contain a 0x1A byte. With dBaseIV and Fox* dbt/fpts, any character values can be stored.

No special handling is required for fetching or inserting MEMO fields. The LongReadLen and LongTruncOk attributes are currently ignored.

Other data handling issues

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

DBD::XBase supports automatic conversions between datatypes wherever it's reasonable.

Transactions, Isolation, and Locking

DBD::XBase does not support transactions and does not lock the tables it is working on.

SQL Dialect

Case sensitivity of LIKE operator

Table join syntax

DBD::XBase does not support table joins.

Table and column names

The XBase format stores each table as a distinct file. Memo fields are stored in an additional file. The table names are limited by the filesystem's maximum filename length. They are stored and treated as entered. The case-sensitivity depends on the filesystem that the file is stored on.

Column names are limited to eleven characters. They are stored as uppercase, but are not case-sensitive.

Table and field names have to start with letter. Any combination of letters, digits, and underscores may follow. National character sets can be used.

DBD::XBase does not support putting quotes around table or column names.

Row ID

DBD::XBase does not support a "row ID" pseudocolumn.

Automatic key or sequence generation

DBD::XBase does not support automatic key generation or sequence generators owing to the limitations of the XBase format.

Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

Positioned updates and deletes

DBD::XBase does not support positioned updates or deletes.

Parameter Binding

The TYPE attribute to bind_ param() is ignored. Consequently, unsupported values of the TYPE attribute do not currently generate a warning.

Stored Procedures

Stored procedures are not applicable in the XBase format.

Table Metadata

There is no way to get detailed information about the columns of a table (at the moment) other than by doing a SELECT * FROM table and using the NAME and TYPE attributes of the statement handle.

Keys and indexes are not currently supported.

Driver-Specific Attributes and Methods

DBD::XBase has just one driver-specific attribute and that is valid for both database and statement handles:

xbase_ignorememo

Ignore memo files and thus don't fail to read a table where the memo file is missing or corrupt.