=head1 DBD::Empress and DBD::EmpressNet =begin docbook =end docbook =head1 =head2 Version Version 0.51. These drivers use the same Perl interface but use a different underlying database interface. C is for direct access of databases, whilst C is for distibuted database connected I the Empress Connectivity Server ( referred to in Empress v8.10 and earlier as the Empress ODBC server ). =head2 Author and Contact Details The driver was written by Steve Williams. He can be contacted at I. =head2 Supported Database Versions and Options C supports Empress V6.10 and later. =head2 Connect Syntax The Cconnect()> Data Source Name, or I 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 Cconnect()> method. =head2 Numeric Data Handling Empress RDBMS supports the following numeric data types: 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 the DBD driver supports Empress Generic datatypes only. This means that all data for a specific group will be retrieved as the same data type. For example, SHORTINTEGER, INTEGER, and LONGINTEGER will all be retrieved as LONGINTEGER. C always returns all numbers as strings. =head2 String Data Handling Empress RDBMS supports the following string data types: 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 (2GB). 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 C SQL function. =head2 Date Data Handling Empress RDBMS supports the following date time data types: 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 9 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 For input, the DBD drivers recognize all formats. 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.I and above the default for this is 1950. Earlier versions of Empress defaulted to 1900. Empress accepts any of the 9 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, C uses just C and C uses just C. 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 C and C, 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. =head2 LONG/BLOB Data Handling Empress RDBMS supports the following LONG data types: 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 (2GB). None of the types are passed to and from the database as pairs of hex digits. I works as defined for C but is ignored for C. The maximum I is limited to 2GB typically. I is not implemented. No special handling is required for binding LONG/BLOB data types. The TYPE attribute is currently not used when binding parameters. The maximum length of C parameters is limited by the capabilities of the OS or the size of the C C, whichever comes first. =head2 Other Data Handling issues The C method is not supported. Empress automatically converts strings to numbers and dates, and numbers and dates to strings, as needed. =head2 Transactions, Isolation and Locking C support 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 C 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. Locks are only valid for the duration of a transaction. =head2 No-Table Expression Select Syntax To select a constant expression--that is, one that doesn't involve data from a database table or view--you need to select from a real table. Use the DISTINCT keyword in the query to prevent multiple values being returned; or, better yet, write the query to only match one row in a table you know exists, such as a system catalog. =head2 Table Join Syntax For outer joins, the Empress keyword OUTER should be placed before the table(s) that should drive the outer join. For example: SELECT customer_name, order_date FROM OUTER customers, orders WHERE customers.cust_id = orders.cust_id; This returns all the rows in the customers table that have no matching rows in the orders table. Empress returns c for any select list expressions containing columns from the orders table. =head2 Table and Column Names The names of Empress identifiers, such as tables and columns, cannot exceed 32 characters in length. The first character must be a letter, but the rest can be any combination of letters, numerals, and underscores (_). Empress table/column names are stored as defined. They are case sensitive. Empress tables and fields can contain most ASCII characters (except C<$> and C) if they are quoted. For example: SELECT field_name "a simple *&" FROM table_name "what a dumb ^" However, this practice is not recommended. Any ISO-Latin characters can be used in the base product. Specific products for other languages, such as Japanese, can handle those character sets. =head2 Case Sensitivity of LIKE Operator The LIKE operator is case sensitive. The MATCH operator is case insensitive. =head2 Row ID A table row identifier can be referenced as MS_RECORD_NUMBER. It can be treated as a string during fetch. But it must be treated as an integer when used in a WHERE clause. It is only useful for explicit fetch; inequalities are not allowed. SELECT * FROM table_name WHERE MS_RECORD_NUMBER = ? =head2 Automatic Key or Sequence Generation Empress has no "auto increment" or "system generated" key mechanism, and does not support sequence generators. =head2 Automatic Row Numbering and Row Count Limiting There is no pseudocolumn that sequentially numbers the rows fetched by a select statement. =head2 Parameter Binding Parameter binding is directly suported by Empress. Only the default C style of placeholders is supported. C recognizes the C TYPE attribute SQL_BINARY. All other types are automatically bound correctly without TYPE being used. Unsupported types are ignored without warning. =head2 Stored Procedures C does not explicitly support stored procedures. Implicit support is available for stored procedures in SQL statements, e.g.: $sth->prepare("SELECT func(attribute) FROM table_name"); =head2 Table Metadata C does not support the C 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 C. It is possible, though difficult, to interpret the contents of the system tables to obtain this information. =head2 Driver-specific Attributes and Methods C has no significant driver-specific handle attributes or private methods. =head2 Positioned updates and deletes C does not currently support positioned updates and deletes. =head2 Differences from the DBI Specification C was written against DBI 0.89 and has not been updated yet. The DBD was conformant to the behavior at that time. Newer features of DBI that require driver changes may not behave as specified. Note the transaction limitation of C (not C) outlined in the section on L below. =head2 URLs to More Database/Driver Specific Information http://www.empress.com =head2 Concurrent use of Multiple Handles C supports an unlimited number of concurrent database connections to one or more databases. C also supports multiple concurrent database connections to one or mode 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. C supports the preparation and execution of a new statement handle while still fetching data from another statment handle associated with the same database handle. =cut # This driver summary for DBD::Empress is Copyright (c) 1999 Tim Bunce # and Steve Williams # $Id: dbd-empress.pod,v 2.3 1999/05/16 13:09:17 timbo Exp timbo $