Appendix B. Driver and Database CharacteristicsContents:In this appendix, we hope to give you a flavor of the functionality and quirks of different DBI drivers and their databases. The line between the functionality and quirks of a given driver and the functionality and quirks of its corresponding database is rather blurred. In some cases, the database has functionality that the driver can't or doesn't access; in others, the driver may emulate functionality that the database doesn't support, such as placeholders. So when you see the terms driver or database below, take them with a pinch of salt. Our primary goals are:
We don't attempt to describe the drivers and databases in detail here, and we're not reproducing their documentation. We're only interested in the key features that are most commonly used or relevant to our goals. And for those features, we're just providing an outline guide, sometimes little more than signposts. Consult the database and driver documentation for full details. With the cooperation of the driver authors, we have produced descriptions for the following drivers and databases:
For each of these drivers, we've tried to cover the same range of topics in the same order. The topics include:
Reading straight through is not for the faint-hearted. We recommend dipping in on an as-needed basis. B.1. Acquiring the DBI and DriversBefore you can use a DBI driver module, you obviously need to get it from somewhere and install it on your system. If you're on a Microsoft Windows system and using the ActiveState version of Perl, then the first thing to try is their Perl Package Manager , or PPM for short. The PPM utility is installed with ActiveState Perl and greatly simplifies downloading and installing pre-compiled modules. Installing a DBI driver using PPM also automatically installs the DBI if it's not already installed. For more information refer to: That simple solution won't work for everyone. If you're not using ActiveState Perl on Microsoft Windows, or the driver you want isn't one that they have pre-compiled for downloading via PPM, then you'll have to travel the longer road: download the source code for the driver and build it yourself. It's usually not as hard as it may sound. The source code for DBI drivers can be downloaded from any site that is part of the Comprehensive Perl Archive Network (CPAN). Here are a few handy URLs to get you started:
If you've not already installed the DBI, then you'll need to do that first. Simply substituting DBI for DBD in the URLs above will take you to the source code for the DBI module. Remember that many drivers for database systems require some database-specific client software to be installed on the machine in order to be able to build the driver. The driver documentation should explain what's needed. Driver versionAt the time of this writing, the DBD::ADO driver, and even ADO itself, are relatively new. Things are bound to change, so be sure to read the latest documentation. Feature summaryBecause DBD::ADO acts as an interface to other lower-level database drivers within Windows, 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 execute( ) Placeholders No, not yet Stored procedures Limited support, no parameters 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 No Concurrent use of multiple handles Dependent on connected data source Author and contact detailsThe driver is maintained by Thomas Lowery and Phlip Plumlee. They can be contacted via the dbi-users mailing list. Supported database versions and optionsThe DBD::ADO module requires Microsoft ADO version 2.1 or later to work reliably. Using NT with Service Pack 4 is recommended. The module is pure Perl, making use of the Win32::OLE module to handle the ADO requests. The DBD::ADO module supports the use of SQL statements to query any data source your raw ADO supports. This can include the Jet data drivers for the various Microsoft Office file formats, any number of ODBC data drivers, or experimental data providers that expose file system folder hierarchies or Internet directory services as data sources. Each provider system supports SQL in some format, either in a native format like MS-SQL Server's Transact SQL or as an emulation layer in the data provider, such as a Jet data driver reading an Excel spreadsheet. Information about ADO can be found at http://www.microsoft.com/data/ado/. Differences from the DBI specificationDBD::ADO is a very new and currently incomplete driver. It is evolving rapidly though, and since it's written in pure Perl using Win32::OLE, it's easy for people to enhance. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, has the following format: dbi:ADO:DSN DSN must be an ODBC Data Source Name registered with the Control Panel ODBC Data Sources applet. If your DBI application runs as a service or daemon, such as a CGI script, the DSN must appear on the "System DSN" tab. There are no driver-specific attributes for the DBI->connect() method. DBD::ADO supports an unlimited number of concurrent data source connections to one or more data sources, subject to the limitations of those data sources. DatatypesThe numeric, string, date, and LONG/LOB datatypes depend on the interaction of four forces: what a Perl "scalar" supports, how the Win32::OLE layer translates VARIANTs into scalars, the types that VARIANT itself permits, and the types your target provider emits. A user/programmer must research those forces in his or her relevant documentation. Rest assured that DBD::ADO will then transmit the type correctly. Transactions, Isolation, and LockingDBD::ADO reflects the capabilities of the native connection to the user. Transactions, if a provider supports them, are per connection -- all statements derived from one connection will ``see'' updates to the data that awaits a COMMIT statement. Other connections to that data source will not see these pending updates. SQL DialectBecause DBD::ADO acts as an interface to other database drivers, the following issues are governed by those drivers and the databases they connect to:
For more information, refer to the documentation for the drivers and the database being used. The DBD::ADO driver does not support positioned updates and deletes. Parameter BindingParameter binding is not yet supported by DBD::ADO. Stored ProceduresCalling stored procedures is supported by DBD::ADO using the ODBC style {call procedure_name()} syntax. Table MetadataDBD::ADO does not currently support the table_info() method. It awaits the needed slack time and/or other volunteers. Driver-Specific Attributes and MethodsThe ADO connection object can be accessed from database and statement handles via the ado_conn attribute. The ADO RecordSet object can be accessed from statement handles via the ado_rs attribute.
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Jochen Wiedmann. He can be contacted via the dbi-users mailing list. Supported database versions and optionsThe DBD::CSV driver is built upon the services of several other related modules. The Text::CSV_XS module is used for reading and writing CSV files. The abstract generic DBD::File class provides the driver framework for handling flat files. That, in turn, uses the SQL::Statement module to parse and evaluate simple SQL statements. 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: Differences from the DBI specificationDBD::CSV does not fully parse the statement until it's executed. Thus, attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute( ) has been called. This is valid behavior but is important to note when porting applications originally written for other drivers. The statement handle attributes PRECISION, SCALE, and TYPE are not supported. Also note that many statement attributes cease to be available after fetching all the result rows or calling the finish( ) method. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: 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:
DatatypesNumeric data handlingWithout question, the main disadvantage of the DBD::CSV module is the lack of appropriate type handling. While reading a CSV table, you have no way to reliably determine the correct datatype of the fields. All fields look like strings and are treated as such by default. 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 handlingSimilar 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 handlingNo date or time types are directly supported. LONG/BLOB data handlingBLOBs are equivalent to strings. They are only limited in size by available memory. Other data handling issuesThe type_info( ) method is supported. Transactions, Isolation, and LockingThe 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 DialectCase sensitivity of LIKE operatorTwo different LIKE operators are supported. LIKE is case-sensitive, whereas CLIKE is not. Table join syntaxTable joins are not supported. Table and column namesTable 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 IDRow IDs are not supported. Automatic key or sequence generationNeither automatic keys nor sequences are supported. Automatic row numbering and row count limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesPositioned updates and deletes are not supported. Parameter BindingQuestion marks are supported as placeholders, as in: $dbh->do("INSERT INTO A VALUES (?, ?)", undef, $id, $name); The :1 placeholder style is not supported. Stored ProceduresStored procedures are not supported. Table MetadataBy default, the driver expects the column names to be stored in the table's first row, as in: 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 MethodsBesides the attributes f_dir, csv_eol, csv_sep_char, csv_quote_char, and csv_sep_char that have already been discussed above, the most important database handle attribute is: $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:
There are no driver-specific statement handle attributes and no private methods for either type of handle.
General InformationDriver versionFeature summaryTransactions 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 detailsSupport for the DBD::DB2 driver is provided by IBM through its service agreements for DB2 UDB. Any comments, suggestions, or enhancement requests can be sent via email to db2perl@ca.ibm.com. Please see the web site at: for more information. Supported database versions and optionsThe DBD::DB2 driver supports DB2 UDB V5.2 and later. Here are some URLs to more database/driver-specific information:
Differences from the DBI specificationThe only significant difference in behavior from the current DBI specification is the way in which datatypes are specified in the bind_ param( ) method. Please see the information later in this section of the document about using the bind_ param( ) method with the DBD::DB2 driver. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, is specified as follows: 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. DatatypesNumeric data handlingDB2 UDB supports the following numeric datatypes: 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 handlingDB2 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 handlingDB2 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 handlingDB2 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 issuesThe 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 LockingDB2 UDB supports transactions and four transaction isolation levels: Repeatable Read, Read Stability, Cursor Stability, Uncommited Read. The default transaction isolation level is Cursor Stability. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxYou 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 namesIn 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 IDDB2 UDB does not support a "table row ID" pseudocolumn. Automatic key or sequence generationThe 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 limitingThere 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 deletesDB2 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 BindingParameter binding is directly supported by DB2 UDB. Only the standard ? style of placeholders is supported. 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 ProceduresStored procedures are invoked by using the following SQL syntax: CALL procedure-name(argument, ...) Table MetadataDBD::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 InformationDriver versionFeature summaryTransactions 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 detailsThe driver was written by Steve Williams. He can be contacted at swilliam@empress.com. Supported database versions and optionsDBD::Empress supports Empress V6.10 and later. For further information refer to: These drivers use the same Perl interface but use a different underlying database interface. DBD::Empress is for direct access of databases, while DBD::EmpressNet is for distibuted database connected via the Empress Connectivity Server (referred to in Empress v8.10 and earlier as the Empress ODBC server). Differences from the DBI specificationThere are no significant differences. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: dbi:Empress:physical_database dbi:EmpressNet:logical_database dbi:EmpressNet:SERVER=server_name;DATABASE=physical_database;PORT=port_number There are no driver-specific attributes for the DBI->connect() method. 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. DatatypesNumeric data handlingEmpress RDBMS supports the following numeric datatypes: 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 handlingEmpress 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 handlingEmpress 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 handlingEmpress 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 issuesThe 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 LockingDBD::Empress supports transactions. The default isolation level is Serializable. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. The MATCH operator is case-insensitive. Table join syntaxFor 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 NamesThe 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 IDA 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 generationEmpress has no "auto increment" or "system generated" key mechanism, and does not support sequence generators. Automatic row numbering and row count limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesPositioned updates and deletes are not supported. Parameter BindingParameter binding is directly supported by Empress. Only the standard ? style of placeholders is supported. 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 ProceduresDBD::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 MetadataDBD::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 MethodsDBD::Empress has no significant driver-specific handle attributes or private methods.
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Jonathan Leffler. He can be contacted via the dbi-users mailing list. Supported database versions and optionsThe 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: Differences from the DBI SpecificationIf you change AutoCommit after preparing a statement, you will probably run into problems that you don't expect. So don't do that. See the DBD::Informix documentation for more details on this and other assorted subtle compatibility issues. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, has the following form: 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. DatatypesNumeric data handlingInformix supports these numeric datatypes: 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 handlingInformix 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 handlingThere 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 There is no simple expression for inline use that will do the reverse. Use a stored procedure; see the comp.databases.informix archives at DejaNews, or the Informix International Users Group (IIUG) web site at http://www.iiug.org. Informix does not handle multiple time zones in a simple manner. LONG/BLOB data handlingInformix supports the following large object types: BYTE - binary data max 2 GB TEXT - text data max 2 GB BLOB - binary data max 2 GB (maybe bigger); IDS/UDO only CLOB - character data max 2 GB (maybe bigger); IDS/UDO only DBD::Informix does not currently have support for BLOB and CLOB datatypes, but does support the BYTE and TEXT types. The DBI LongReadLen and LongTruncOk attributes are not implemented. If the data selected is a BYTE or TEXT type, then the data is stored in the relevant Perl variable, unconstrained by anything except memory up to a limit of 2 GB. The maximum length of bind_ param( ) parameter value that can be used to insert BYTE or TEXT data is 2 GB. No specialized treatment is necessary for fetch or insert. UPDATE simply doesn't work. The bind_ param( ) method doesn't pay attention to the TYPE attribute. Instead, the string presented will be converted automatically to the required type. If it isn't a string type, it needs to be convertible by whichever bit of the system ends up doing the conversion. UPDATE can't be used with these types in DBD::Informix; only version 7.30 IDS provides the data necessary to be able to handle blobs. Other data handling issuesThe type_info( ) method is not supported. Non-BLOB types can be automatically converted to and from strings most of the time. Informix also supports automatic conversions between pure numeric datatypes whereever it is reasonable. Converting from DATETIME or INTERVAL to numeric datatypes is not automatic. Transactions, Isolation, and LockingInformix databases can be created with or without transaction support. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxAll 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 namesFor 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 IDMost 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 generationThe 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 limitingInformix 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 deletesPositioned updates and deletes are supported using the WHERE CURRENT OF syntax. For example: $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}"); Parameter BindingParameter 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 ProceduresSome stored procedures can be used as functions in ordinary SQL: 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 MetadataThe DBI table_info() method isn't currently supported. The private _tables() method can be used to get a list of all tables or a subset. 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. Further information about these and other issues can be found via the comp.databases.informix newsgroup, and via the International Informix User Group (IIUG) at http://www.iiug.org. Driver-Specific Attributes and MethodsRefer to the DBD::Informix documentation for details of driver-specific database and statement handle attributes. Private _tables() and _columns() methods give easy access to table and column details. Other Significant Database or Driver FeaturesTemporary tables can be created during a database session that are automatically dropped at the end of that session if they have not already been dropped explicitly. It's a very handy feature. The latest versions of Informix (IDS/UDO, IUS) support user-defined routines and user-defined types, which can be implemented in the server in C or (shortly) Java. The SQL-92 "CASE WHEN" syntax is supported by some versions of the Informix servers. That greatly simplifies some kinds of queries.
General InformationDriver versionDBD::Ingres version 0.16 and, where noted, the 0.20 release Feature summaryTransactions 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 detailsThe driver author is Henrik Tougaard. He can be contacted via the dbi-users mailing list. Supported database versions and optionsThe DBD::Ingres module supports both Ingres 6.4 and OpenIngres (1.x & II). For more information about Ingres, refer to: Differences from the DBI specificationPrepared statements do not work across transactions because commit/rollback and close/invalidate are all prepared statements. Work is underway to fix this deficiency. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: 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. DatatypesNumeric data handlingThe database and driver supports one-byte, two-byte and four-byte INTEGERs, four-byte and eight-byte FLOATS, and a currency type. The database and the driver (from version 0.20) supports the DECIMAL-number type.
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 handlingIngres 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 handlingIngres 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 handlingIngres 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 issuesThe DBD::Ingres driver supports the type_info( ) method. Ingres supports automatic conversions between datatypes wherever it's reasonable. Transactions, Isolation, and LockingIngres supports transactions. The default transaction isolation level is Serializable. OpenIngres II supports Repeatable Read, Read Commited, and Serializable. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxOpenIngres supports outer joins in ANSI SQL-92 syntax. Ingres 6.4 does not support outer joins. Table and column namesThe 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 IDThe 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 generationOpenIngres 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 limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesPositioned 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 BindingParameter binding is directly supported by Ingres. Only the standard ? placeholder style is supported. 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 ProceduresCalling 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 MetadataDBD::Ingres version 0.20 supports the table_info() method. 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 MethodsDBD::Ingres has no driver-specific database handle attributes. However, it does support a number of statement handle attributes. Each returns a reference to an array of values, one for each column of the select results. These attributes are:
DBD::Ingres supports just one private method:
General InformationDriver versionThis version of the DBD::InterBase driver is a pure Perl wrapper module around the IBPerl module. The author is working on a direct XS version, so be sure to read the latest documentation. Feature summaryTransactions 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 detailsThe driver author is Edwin Pratomo. He can be contacted via the dbi-users mailing list, or at ed.pratomo@computer.org. Supported database versions and optionsDBD::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: Differences from the DBI specificationDBD::InterBase does not have access to statement metadata until after the statement has been executed and the first row fetched. Thus, attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute( ) and a fetch method has been called. Hopefully this will be fixed in a later version. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, has the following format: dbi:InterBase:attrs where attrs is a semicolon-separated list of key=value pairs Valid attributes include:
DBD::InterBase supports an unlimited number of concurrent database connections to one or more databases. DatatypesNumeric data handlingInterBase supports INTEGER, SMALLINT, FLOAT, DOUBLE PRECISION, NUMERIC (p,s), and DECIMAL(p,s). 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 handlingInterBase 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 handlingInterBase 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 handlingInterBase 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 issuesInterBase supports automatic conversions between datatypes wherever it is reasonable. Transactions, Isolation, and LockingInterBase supports transactions. Transaction isolation can be altered using the SET TRANSACTION ISOLATION LEVEL x statement. Refer to the InterBase DSQL manual for full details. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxOuter joins and inner joins are supported and are expressed using the ISO standard SQL syntax. Table and column namesThe 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 IDThere is no "Row ID" concept. Automatic key or sequence generationA 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 limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesInterBase does not support positioned updates or deletes. Parameter BindingParameter binding is supported directly by InterBase. DBD::InterBase supports the ? placeholder style. The TYPE attribute of the bind_ param() as well as type_info() method are not yet supported. Stored ProceduresInterBase does support stored procedures, but neither DBD::InterBase nor IBPerl has support for them that yet. Table MetadataDBD::InterBase hasn't yet supported the table_info() method. Driver-Specific Attributes and MethodsThere are no significant DBD::InterBase driver-specific database handle attributes.
General InformationDriver versionDBD::mysql and DBD::mSQL versions 1.20xx and 1.21_xx 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 summaryTransactions 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 detailsThe driver author is Jochen Wiedmann. He can be contacted via the mailing list Msql-Mysql-modules@lists.mysql.com. Supported database versions and optionsMySQL 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: For further information about mSQL: Differences from the DBI specificationBoth DBD::mysql and DBD::mSQL do not fully parse the statement until it's executed. Thus attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute() has been called. This is valid behavior, but is important to note when porting applications written originally for other drivers. Also note that many statement attributes cease to be available after fetching all the result rows or calling the finish( ) method. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: DBI:mysql:attrs DBI:mSQL:attrs where attrs is a semicolon-separated list of key=value pairs. Valid attributes include:
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. DatatypesNumeric data handlingMySQL has five sizes of integer datatype, each of which can be signed (the default) or unsigned (by adding the word UNSIGNED after the type name).
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.
NUMERIC can be used as an alias for DECIMAL. The numeric datatypes supported by mSQL are much more restricted:
The driver returns all datatypes, including numbers, as strings. It thus puts no restriction on size of PRECISION or SCALE. String data handlingThe following string types are supported by MySQL, quoted from mysql.info where M denotes the maximum display size or PRECISION:
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 handlingThe following date and time types are supported by MySQL, and quoted from mysql.info:
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! In MySQL 3.23, this feature will be changed to 2000-2068 and 1969-1999, following the X/Open Unix standard.[73]
The NOW() function, and its alias SYSDATE, allow you to refer to the current date and time in SQL. The DATE_FORMAT(date, format) function can be used to format date and time values using printf-like format strings. MySQL has a rich set of functions operating on dates and times, including DAYOFWEEK(date) (1 = Sunday, ..., 7 = Saturday), WEEKDAY(date) (0 = Monday, ..., 6 = Sunday), DAYOFMONTH(date), DAYOFYEAR(date), MONTH(date), DAYNAME (date), MONTHNAME(date), WEEK(date), YEAR(date), HOUR(time), MINUTE (time), SECOND(time), DATE_ADD(date, interval) (interval being something like "2 HOURS"), and DATE_SUB(date, 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: FROM_UNIXTIME(seconds_since_epoch) and the reverse: UNIX_TIMESTAMP(timestamp) MySQL does no automatic time zone adjustments. The mSQL database supports these date/time types: DATE - corresponds to MySQL's DATE type TIME - corresponds to MySQL's TIME type The only date format supported by mSQL is DD-MMM-YYYY, where MMM is the three-character English abbreviation for the month name. The only time format supported by mSQL is HH:MM:SS. LONG/BLOB data handlingThese are MySQL's BLOB types, quoted from mysql.info : TINYBLOB / TINYTEXT maximum length of 255 (2**8 - 1) BLOB / TEXT maximum length of 65535 (2**16 - 1) MEDIUMBLOB / MEDIUMTEXT maximum length of 16777215 (2**24 - 1) LONGBLOB / LONGTEXT maximum length of 4294967295 (2**32 - 1) Binary characters in all BLOB types are allowed. The LongReadLen and LongTruncOk types are not supported. The maximum length of bind_ param( ) parameter values is only limited by the maximum length of an SQL statement. By default that's 1MB but can be extended to just under 24 MB by changing the mysqld variable max_allowed_packet. No TYPE or other attributes need to be given to bind_ param( ) when binding these types. The only BLOB type supported by mSQL is TEXT. This is a cross between a traditional VARCHAR type and a BLOB. An average width is specified, and data longer than average is automatically stored in an overflow area in the table. Other data handling issuesThe driver versions 1.21_xx and above do support the type_info( ) method. MySQL supports automatic conversions between datatypes wherever it's reasonable. mSQL, on the other hand, supports none. Transactions, Isolation, and LockingBoth mSQL and MySQL do not support transactions. 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 DialectCase sensitivity of LIKE operatorWith 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 syntaxJoins 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 namesMySQL 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 IDMySQL 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 generationAll 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 limitingNeither 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 deletesNeither positioned updates nor deletes are supported by MySQL or mSQL. Parameter BindingNeither engine supports placeholders, but the DBD::mysql and DBD::mSQL drivers provide full emulation. Question marks are used as placeholders, as in: $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 ProceduresNeither mSQL nor MySQL have a concept of stored procedures, although there are plans to add some stored procedure features to MySQL. Table MetadataThe 1.21_xx version of the drivers was the first to support the table_info() method. 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 MethodsThe following driver-specific database handle attributes are supported:
The following driver-specific statement handle attributes are supported:
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'); These correspond to the respective commands of mysqladmin and msqladmin.
General InformationDriver versionFeature summaryBecause 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 detailsThe 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 optionsThe DBD::ODBC module supports ODBC Version 2.x and 3.x on Unix and Win32. For all platforms, both an ODBC driver manager and an ODBC driver are required in addition to the DBD::ODBC module. 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:
There are other vendors; this is not an exhaustive list. Other related ODBC links include: To subscribe to the freeodbc development mailing list, send a message to freeodbc-request@as220.org with just the word subscribe in the body of the message. Differences from the DBI specificationDBD::ODBC does not currently support "out" parameter binding. That should be fixed in a later release. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, has the following forms: 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. DatatypesNumeric data handlingThe numeric data handling for ODBC is dependent upon a variety of factors. One of those critical factors is the end database. For example, Oracle supports different numeric types than Sybase which, in turn, supports different numeric types than a CSV file. You will need to read your database documentation for more information. 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 handlingAs 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 handlingAs 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 handlingSupport 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 issuesThe DBD::ODBC driver supports the type_info( ) method. Transactions, Isolation, and LockingDBD::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 DialectBecause DBD::ODBC acts as an interface to other database drivers, the following issues are governed by those drivers and the databases they connect to:
For more information, refer to the documentation for the drivers and the database being used. Table join syntaxTable 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 deletesThis 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 BindingParameter 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 ProceduresStored 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 MetadataDBD::ODBC supports the table_info() method. 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 MethodsDBD::ODBC has no driver-specific handle attributes. In addition to the private methods described in "Table Metadata" above, the GetInfo() private method can be used to discover many details about the driver and database you are using.
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Tim Bunce. He can be contacted via the dbi-users mailing list. Supported database versions and optionsThe 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: Differences from the DBI specificationDBD::Oracle has no known significant differences in behavior from the current DBI specification. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: 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. DatatypesNumeric data handlingOracle has only one flexible underlying numeric type, NUMBER. But Oracle does support several ANSI standard and IBM datatype names as aliases, including: 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 handlingOracle 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 handlingOracle 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 handlingOracle 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 issuesThe DBD::Oracle driver supports the type_info( ) method. Oracle supports automatic conversions between datatypes wherever it's reasonable. Transactions, Isolation, and LockingDBD::Oracle supports transactions. The default transaction isolation level is READ COMMITED. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxOracle 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 namesThe 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 IDThe 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 generationOracle 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 limitingThe 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 deletesOracle does not support positioned updates or deletes. Parameter BindingParameter binding is directly supported by Oracle. Both the ? and :1 style of placeholders are supported. The :name style is also supported, but is not portable. The bind_ param() method TYPE attribute can be used to indicate the type a parameter should be bound as. These SQL types are bound as VARCHAR2: SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, and SQL_VARCHAR. Oracle will automatically convert from VARCHAR2 to the required type.[74]
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 ProceduresOracle stored procedures are implemented in the Oracle PL/SQL language.[75]
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 MetadataDBD::Oracle supports the table_info() method. 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 MethodsDBD::Oracle has no significant driver-specific database or statement handle attributes. The following private methods are supported:
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Edmund Mergl. He can be contacted via the dbi-users mailing list. Supported database versions and optionsThe DBD-Pg-0.91 module supports PostgreSQL 6.4. For futher information please refer to: Differences from the DBI SpecificationDBD::Pg does not fully parse the statement until it's executed. Thus, attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute( ) has been called. This is valid behavior, but it is important to note when porting applications originally written for other drivers. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, can be one of the following: 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. DatatypesNumeric data handlingPostgreSQL supports the following numeric types:
Some platforms also support the int8 type. DBD::Pg always returns all numbers as strings. String data handlingPostgreSQL 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 handlingPostgreSQL 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 handlingPostgreSQL 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 issuesThe DBD::Pg driver supports the type_info( ) method. PostgreSQL supports automatic conversions between datatypes wherever it's reasonable. Transactions, Isolation, and LockingPostgreSQL supports transactions. The current default isolation transaction level is Serializable and is currently implemented using table-level locks. Both may change. No other isolation levels for transactions are supported. 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 DialectCase sensitivity of LIKE operatorPostgreSQL has the following string matching operators:
Table join syntaxOuter joins are not supported. Inner joins use the traditional syntax. Table and column namesThe 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 IDThe 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 generationPostgreSQL 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 limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesPostgreSQL does not support positioned updates or deletes. Parameter BindingParameter binding is emulated by the driver. Both the ? and :1 style of placeholders are supported. 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 ProceduresDBD::Pg does not support stored procedures. Table MetadataDBD::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 MethodsThere are no significant DBD::Pg driver-specific database handle attributes. DBD::Pg has the following driver-specific statement handle attributes:
DBD::Pg has no private methods. Other Significant Database or Driver FeaturesPostgreSQL 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 InformationDriver versionDBD::SearchServer version 0.20 This driver was previously known as DBD::Fulcrum. Feature summaryTransactions 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 detailsThe 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 optionsThe DBD::SearchServer module supports PCDOCS/Fulcrum SearchServer, versions 2.x thru 3.5. Fulcrum SearchServer is a very powerful text-retrieval system with a SQL interface. You should not expect to find a full-fledged SQL RDBMS here. Refer to the product documentation for details about the query language. For further information about SearchServer, refer to: Differences from the DBI specificationDBD::SearchServer doesn't fully parse the statement until it's executed. Attributes like $sth->{NUM_OF_FIELDS} aren't available until after $sth->execute( ) has been called. This is valid behavior but is important to note when porting applications originally written for other drivers. Connect SyntaxUnder Unix, you may specify where SearchServer will find the database tables by using a set of environment variables: FULSEARCH, FULCREATE, and FULTEMP. So the connect string is always just: 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. DatatypesNumeric data handlingSearchServer has two numeric datatypes: INTEGER and SMALLINT. INTEGER (or INT) is an unsigned 32-bit binary integer with 10 digits of precision. SMALLINT is a signed 16-bit binary integer with 5 digits of precision. String data handlingSearchServer 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 handlingSearchServer 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 handlingThe 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 issuesThe DBD::SearchServer driver does not support the type_info( ) method. Transactions, Isolation, and LockingDBD::SearchServer does not support transactions. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is not case-sensitive. Table join syntaxSearchServer 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 namesLetters, 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 IDThe 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 generationSearchServer 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 limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesPositioned updates and deletes are supported using the WHERE CURRENT OF syntax. For example: $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}"); Parameter BindingBoth the ? and :1 style of placeholders are supported by driver emulation. The TYPE attribute to bind_ param() is ignored, so no warning is generated for unsupported values. Stored ProceduresThere are no stored procedures or functions in SearchServer. Table MetadataDBD::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 MethodsDBD::SearchServer has no driver-specific database handle attributes. It does have one driver-specific statement handle attribute:
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Michael Peppler. He can be contacted via the dbi-users mailing list, or at mpeppler@peppler.org. Supported database versions and optionsThe 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:
The FreeTDS libraries (www.freetds.org) is an Open Source effort to reverse engineer the TDS (Tabular Data Stream) protocol that both Sybase and Microsoft use. FreeTDS is still in alpha, but DBD::Sybase builds cleanly against the latest release and suppports most functions (apart from ?-style placeholders). Here are some URLs to more database/driver specific information: Differences from the DBI specificationThe LongReadLen and LongTruncOk attributes are not supported. Note that DBD::Sybase does not fully parse the statement until it's executed. Thus, attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute( ) has been called. This is valid behavior but is important to note when porting applications originally written for other drivers. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, has the following format: dbi:Sybase:attrs where attrs is a semicolon-separated list of key=value pairs. Valid attributes include:
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. DatatypesNumeric data handlingThe driver supports INTEGER, SMALLINT, TINYINT, MONEY, SMALLMONEY, FLOAT, REAL, DOUBLE, NUMERIC(p,s), and DECIMAL(p,s). 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 handlingDBD::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 handlingSybase 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 handlingSybase 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 issuesThe 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 LockingDBD::Sybase supports transactions. The default transaction isolation level is READ COMMITTED. 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 DialectCase sensitivity of LIKE operatorThe LIKE operator is case-sensitive. Table join syntaxOuter 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 namesThe 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 IDSybase does not support a pseudo "row ID" column. Automatic key or sequence generationSybase 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 Sybase does not support sequence generators, although ad hoc stored procedures to generate sequence numbers are quite easy to write.[76]
Automatic row numbering and row count limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesSybase does not support positioned updates or deletes. Parameter BindingParameter binding is directly suported by Sybase. However, there are two downsides that you should be aware of. 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 ProceduresSybase stored procedures are written in Transact-SQL, which is Sybase's procedural extension to SQL. 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 MetadataDBD::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 MethodsDBD::Sybase has the following driver-specific database handle attributes:
DBD::Sybase has the following driver-specific statement handle attributes:
One private method is provided:
Other Significant Database or Driver FeaturesSybase 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}); Sybase also has rich and powerful stored procedure and trigger functionality and encourages you to use them.
General InformationDriver versionFeature summaryTransactions 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 detailsThe driver author is Jan Pazdziora. He can be contacted at adelton@fi.muni.cz or via the dbi-users mailing list. Supported database versions and optionsThe 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: Differences from the DBI specificationDBD::XBase does not fully parse the statement until it is executed. Thus attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute( ) has been called. This is valid behavior but is important to note when porting applications written originally for other drivers. Connect SyntaxThe DBI->connect() Data Source Name, or DSN, should include the directory where the dbf files are located as the third part: 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. DatatypesNumeric data handlingDBD::XBase supports generic NUMBER(p,s), FLOAT(p,s), and INTEGER(l) types. The maximum scale and precision is limited by Perl's handling of numbers. In the dbf files, the numbers are stored as ASCII strings, binary integers, or floats. 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 handlingDBD::XBase has CHAR(length) and VARCHAR(length) datatypes. The maximum length is 65535 characters for both types.[77]
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 handlingDBD::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 handlingDBD::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 issuesThe DBD::XBase driver supports the type_info( ) method. DBD::XBase supports automatic conversions between datatypes wherever it's reasonable. Transactions, Isolation, and LockingDBD::XBase does not support transactions and does not lock the tables it is working on. SQL DialectCase sensitivity of LIKE operatorThe LIKE operator is not case-sensitive. Table join syntaxDBD::XBase does not support table joins. Table and column namesThe 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 IDDBD::XBase does not support a "row ID" pseudocolumn. Automatic key or sequence generationDBD::XBase does not support automatic key generation or sequence generators owing to the limitations of the XBase format. Automatic row numbering and row count limitingNeither automatic row numbering nor row count limitations are supported. Positioned updates and deletesDBD::XBase does not support positioned updates or deletes. Parameter BindingParameter binding is implemented in the driver and supports the ?, :1, and :name placeholder styles. The TYPE attribute to bind_ param() is ignored. Consequently, unsupported values of the TYPE attribute do not currently generate a warning. Stored ProceduresStored procedures are not applicable in the XBase format. Table MetadataDBD::XBase supports the table_info method. 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 MethodsDBD::XBase has just one driver-specific attribute and that is valid for both database and statement handles:
Copyright © 2001 O'Reilly & Associates. All rights reserved. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|