view release on metacpan or  search on metacpan

lib/DBD/Informix/Summary.pm  view on Meta::CPAN

=head2 LONG/BLOB Data Handling

Informix supports the following large object types:

  BYTE  - binary data     max 2GB
  TEXT  - text data       max 2GB
  BLOB  - binary data     max 2GB (maybe bigger); IDS/UDO only
  CLOB  - character data  max 2GB (maybe bigger); IDS/UDO only

C<DBD::Informix> does not currently have support for BLOB and CLOB data
types, but does support the BYTE and TEXT types.

The DBI I<LongReadLen> and I<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 2GB.

The maximum length of C<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 C<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 cannot be used with these
types in C<DBD::Informix>; only version 7.30 IDS provides the data
necessary to be able to handle blobs.


=head2 Other Data Handling issues

The C<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 data types whereever it is reasonable.  Converting from
DATETIME or INTERVAL to numeric data types is not automatic.


=head1 Transactions, Isolation and Locking

Informix databases can be created with or without transaction support.

Informix supports several transaction isolation levels:  REPEATABLE
READ, CURSOR STABILITY, COMMITTED READ, DIRTY READ. Refer to the
Informix documentation for their exact meaning.  Isolation levels only
apply 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 C<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 C<READ ONLY>.

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

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

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


=head1 SQL Dialect

=head2 Case Sensitivity of LIKE Operator

The LIKE operator is case sensitive.


=head2 Table Join Syntax

All Informix versions support the basic C<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.


=head2 Table and Column Names

For most versions, the maximum size of a table name or column name is
18 characters, as required by SQL-86.  For the latest versions
(Centaur, provisionally 9.2 or 7.4), the answer will be 128, as required
by SQL-92.  Owner (schema) names can be 8 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 (C<_>).

If the C<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 C<"I said, ""Don't!""">. (Normally, Informix is very relaxed about
treating double quotes and single quotes as equivalent, so often you
could write C<'I said, "Don''t"'> as the equivalent of the previous
example.  With C<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).


=head2 Row ID