DBD-Informix
view release on metacpan or search on metacpan
2013-02-06: Release to a few parties; there's another fix pending for GA.
2013-02-06: Recognize ESQL/C 4.10 from 2013 as valid. Do not try this with
ESQL/C 4.10 from 1989; the code will not compile using the
archaic ESQL/C. I had no say in the reuse of version numbers.
2013.0118 Release
-----------------
2013-01-18: Upload to PAUSE and CPAN.
2013-01-18: Workaround for CQ idsdb00247065, the error -1820 problem. You
cannot reuse an SQL descriptor for LVARCHAR variables upon
reopening the cursor unless the size for the LVARCHAR variables
is set to the original size. So, store the original size for
each LVARCHAR and reinstate that size in the SQL descriptor
before the first FETCH (actually, after the OPEN). Reported
this time by Robert Carew <Robert.Carew@mpi.govt.nz>, but also
reported by a number of other people previously.
2012-05-19: Use $Config{so} as well as $Config{dlext} searching for ESQL/C
libraries. Ticket 77286 at rt.cpan.org, submitted by n4zmz.
2011-09-25: Despite some misgivings, add a $dbh->ping method (using SELECT
1 FROM "informix".systables WHERE TabID = 1). The misgivings
are for MODE ANSI databases; a transaction may be started.
2004-12-01: Revert to SQL DESCRIPTORS. Cleanup version detection.
2004-11-24: Conversion to MSD/NMD distribution mechanism.
2004-10-21: Pre-emptive fix for version detection problems in ClientSDK
2.90 (which will have ESQL/C 2.90, instead of 9.x).
2004-09-13: Fix problem in dbdimp.ec v100.36.1.x related to formatting
SQLFLOAT and SQLSMFLOAT; bug reported by Will Rutherford
<will.rutherford@sciatl.com>
2004-08-04: Perl 5.5.x requires prototypes - Makefile.PL should not fuss
about setting __STDC__ etc on any platforms. Fixes problem
reported by Václav Ovsík <vaclav.ovsik@i.cz>.
2004-04-01: Enable hold cursors test (t/t33holdcurs.t). Update documentation.
2004-01-16: Import updated sqltoken.c which handles C-style comments, just
like the servers do.
2003-10-17: Fix version detection problem with ESQL/C on RedHat 9, reported
by Jay Hannah <jhannah@omnihotels.com>. Fix problems with
dates in t/t09exec.t - I don't know whom to credit for this
(let me know if it was you).
2003-09-30: Add t/t98pod.t, but do not distribute as it requires Test::Pod,
which is far from being universally installed.
2003-09-09: Detect and object when a $dbh is used by a child process.
(Response to an old bug report, but name/email missing.)
Pre-requisite version of DBI is 1.33 (not yet released).
2002-12-11: Migrate code for data_sources into dbdimp.ec, and other
clean-ups to bring DBD::Informix into line with documented
practices in DBI::DBD, which is being heavily revised at the
same time.
2002-12-09: Replace PERL_DBI_DEBUG with DBI_TRACE.
2002-12-06: Fix problem with stray trailing nulls on LVARCHAR data
(reported by Mike Langen <mike.langen@tamedia.ch>)
2002-12-05: Change version numbering (drop .PC1 in favour of .00) and
document intended next version number (2003.00.00) in Announce.
2002-12-02: Fix overlong table name in t/t35cursor.t; fix test_for_ius() in
TestHarness.pm to use primary_connection() (reported by Andrew
Hamm <AHamm@civica.com.au>). Fix pre-requisite version of DBI
to 1.30 for DBIc_DBISTATE support (reported by Christopher
Cosby <christopher.cosby@sciatl.com> but misdiagnosed, then
reported by Mike Langen <mike.langen@tamedia.ch> and diagnosed
correctly). If upgrading DBI is unacceptable, change the
prerequisite DBI version number in Informix.pm and Makefile.PL
(several places) and change uses of DBIc_DBISTATE(xxh) to DBIS
in Informix.xs and dbdimp.ec.
Informix.Licence into (new) Notes sub-directory.
1999-09-17: The DBI headers might be in $INSTALLARCHLIB rather than
$INSTALLSITEARCH. This is the case under Debian. Problem
reported by Roderick Schertler <roderick@argon.org>. Also, AIX
4.2 requires the use of ld to create shared libraries, so do
not complain on AIX about the use of ld.
1999-08-30: Add notes on SERIAL and $sth->{ix_salerrd}[1] after extensive
discussions with Kevin Brannen <kbrannen@physiciansdata.com> on
this and related matters. Chase down references to fugue.com
and replace with the updated isc.org equivalents. Add basic
support for HOLD cursors (when available, which means with
AutoCommmit Off -- AutoCommit On always uses HOLD CURSORS;
think about it). Bill Rothanburg <brothanb@fll-ro.dhl.com>
supplied the patch, to whom many thanks. There are hooks for
supporting scroll cursors too, but the actual FETCH code
doesn't know how to handle these. DBI 1.13 doesn't support any
attribute on any of the fetch methods, which makes it difficult
to do much until DBI moves on. Fix t/t30update.t to work in
non-US locales - problem reported by Arnar M Hrafnkelsson
<amh@mbl.is>.
1999-08-23: Add (remarkably simple) fix to error -451 problem, and test
t/t76blob.t to verify the fix. Fix supplied by Richard Jones
<rich@annexia.org>. Does not appear to fix the t75blob.t core
dump problem.
1999-07-26: Add Informix.faq file with two questions. Both questions have
directory from which distribution is made -- to ensure that
correct versions of all library files which are not mastered
under the DBD/Informix directory are fully up to date. Drop
sqlcmd.sh as it is (a) unused and (b) superseded by DBI::Shell.
Add two new attributes, $sth->{ix_StatementText} and
$dbh->{ix_DatabaseName}. Note that $dbh->{Name} returns the name
of the database when DBI->connect() is called, but the name of
he current database can be changed by things such as CLOSE
DATABASE and DATABASE or CREATE DATABASE statements;
$dbh->{ix_DatabaseName} reports the current database name. When
last row is fetched, implicitly close (finish) the cursor, as
requested by Tim Bunce. Explicit finish is only required if the
loop is terminated early.
1998-06-25: Include notes from Yang-Chao lin <ylin@sr.hp.com> about compiling
on HP-UX with ESQL/C 5.x or 6.x. Also upgrade the comments about
IUS types for those who do not know anything other than IUS, such
as Stephen Pillinger <S.M.Pillinger@cs.bham.ac.uk> who first
alerted me to this problem.
1998-06-16: Include notes from Alex Shah <ashah@atg.eb.com> on __eprintf().
1998-06-15: Incorporate __eprintf() fix from Bernd Gill <bernd@dsi.co.nz>.
Include the HP-UX 10.20 build notes from Reed White
insists that $INFORMIXDIR is set, and that $INFORMIXDIR/bin is in
$PATH. Update tests to use the non-deprecated attributes. Add the
test program esqltest.ec to check that the ESQL/C environment is OK
before creating the Makefile.
1997-03-10: No changes over the weekend - the skiing at Squaw was fabulous!
Update the POD to encourage the use of DBI->connect() in preference
to $drh = DBI->install_driver(). Add two new, but fore-shadowed,
driver attributes, ix_CurrentConnection and ix_ActiveConnections,
though they're currently only set when the driver is loaded.
1997-03-17: Update Informix.Licence to contain more comprehensive information
on licencing and Netscape's LiveWire Pro. Update t/updcursor.t so
it passes on a logged (non-MODE ANSI) database. Fix the problem
with nulls found by Oyvind Gjerstad <ogj@tglobe1.tollpost.no> and
now immortalized in the test case t/nulls02.t. Change version to
0.53 and release.
0.52 Release
------------
1997-03-05: Add ESQL/C => DBD::Informix statement mapping to POD in Informix.PM,
after suggestion by Bill Mitlyng <Bill.Mitlyng@abnamro.com>.
1997-03-03: Use $DBI::VERSION to determine where DBIXS.h should be found.
attributes. Add 'deprecated' warning to the system, and a way of
turning the deprecated warnings off. Use this method in
InformixTest.pm. Add the attribute handling of ix_XyZ as well as
deprecating the corresponding XyZ attribute. Fix the handling of
SQLCA so that the inserted serial number is available, etc. Version
bumped to 0.52.
0.51 Release
------------
1997-02-26: Add $sth->{CursorName} and $dbh->{ConnectionName}. Add test
updcursor.t to check that you can do 'UPDATE ... WHERE CURRENT OF'.
Add ".DEFAULT." as an explicit way of making the default connection.
Release version 0.51, and a patch, version 0.51p1.
1997-02-25: Version bumped to 0.51. Nulls on SELECT handled correctly. Test
nulls01.t added. Notes on access to the SQLCA record added to the
POD in Informix.PM, after this omission was noted by Torsten Stein
<stein@cls.de>.
1997-01-23: Fix InformixTest.pm so it works with perldoc. It seems that perldoc
does not like lines which start with a single quote. Thanks to
Jason Bodnar <jcbodnar@mail.utexas.edu> for pointing this problem
out. Add and use stmt_err to report errors or warnings, and document
changes for dbd_ix_database() and dbd_ix_connect(). Add testing.
Thanks to Serge Davidov <serge@fcmc.com> for finding the problem.
You can also use DBI->connect('@server','','','Informix'), which
actually worked in v0.25. DBD::Informix is still confused, in
general, by statements such as CLOSE DATABASE.
1997-01-12: Version bumped to 0.50p1, for consistency with documented intentions
when 0.25 release was made.
0.26 Release
------------
1997-01-12: Use cursors WITH HOLD when a MODE ANSI database is working with
AutoCommit set On. Make transaction tests more sensitive.
This release never made public.
1997-01-09: Updated copyright messages. Improved documentation. Fixed tests.
Add $dbh->{InTransaction} query. Clean up dbdimp.ec so that
AutoCommit works better. Etc. Version 0.26a2.
1996-12-20: Get transaction stuff to work, more or less, with AutoCommit On or
Off, in MODE ANSI or Logged databases. Tests t/transact0[123].t
added, but these still need to be more formally verified. They
pass with OnLine and any type of database. Clean up various tests
so that they pass on MODE ANSI databases. Add select_some_data
and select_zero_data functions to InformixTest.pm. Etc. Version
0.26a1. Use bug-fixed version of decsci.c (ouch!).
1996-12-19: Add install instructions to README file after Wes Gamble
<wgamble@informix.com> pointed out this omission. Add extensive
notes to Informix.PM on transaction management. Implement most
of what those notes say (WITH HOLD cursors omitted at the
moment).
1996-12-18: Fix bug in patched Makefile.PL (need space before __H_LOCALEDEF).
Updated dbd_ix_setconnection() to take connection information, and
updated code in dbdimp.ec to ensure that the correct connection is
set before starting work. Simple testing more or less works. Some
more major revisions, such as not passing SV* to most of the code in
dbdimp.ec (let the Informix.c code convert as much as possible to
plain C). Rework the 'do' function to completely override
DBI::_::db::do, avoiding problems with the rows() function.
1996-09-26: Patches from Terry arrived. This'll be the 0.25 release. Seems to
work.
0.23 Release
------------
1996-09-18: Wrapped in a patch to Makefile.PL from Christophe Martin. Added
some other Makefile.PL patches of my own. It should now support
cleaner builds on HP-UX (spit!) and correctly detect when dbdimp.ec
has changed.
1996-09-18: Wrapped in patches by Terry Nightingale based on Bill Hailes'
isqlperl for supporting multi-cursors. Cleaned the type handling
code again.
1996-09-17: Added patch for bad column names as show in George Vicherek's
splendid test case. (extratests/primitives.pl exercises this)
0.22 Release
------------
1996-09-10: Fixed all numeric types to return correctly. CHAR fields don't
quite behave as planned, or perhaps they do. Added 'multicursor'
test which proves that simultaneous multiple cursors fail miserably.
1996-09-09: Added @ary = $drh->func('_ListDBs'); method for getting a list of
extant databases on the local Informix server. (From original code
and suggestion by George.Vicherek@Sciatl.COM)
0.21 Release
------------
1995-09-01: Fixed truncated INTEGER bug. Other numerical types still screwed.
Patch on the way.
Pre-Release
DBD-Informix.msd view on Meta::CPAN
t/RCS/t22mconn.t,v t/RCS/t22mconn.t,v
t/RCS/t23mconn.t,v t/RCS/t23mconn.t,v
t/RCS/t24mcurs.t,v t/RCS/t24mcurs.t,v
t/RCS/t25dratt.t,v t/RCS/t25dratt.t,v
t/RCS/t28dtlit.t,v t/RCS/t28dtlit.t,v
t/RCS/t29update.t,v t/RCS/t29update.t,v
t/RCS/t30update.t,v t/RCS/t30update.t,v
t/RCS/t31nulls.t,v t/RCS/t31nulls.t,v
t/RCS/t32nulls.t,v t/RCS/t32nulls.t,v
t/RCS/t33holdcurs.t,v t/RCS/t33holdcurs.t,v
t/RCS/t35cursor.t,v t/RCS/t35cursor.t,v
t/RCS/t40rows.t,v t/RCS/t40rows.t,v
t/RCS/t41txacoff.t,v t/RCS/t41txacoff.t,v
t/RCS/t42txacon.t,v t/RCS/t42txacon.t,v
t/RCS/t43trans.t,v t/RCS/t43trans.t,v
t/RCS/t44txansi.t,v t/RCS/t44txansi.t,v
t/RCS/t46chpblk.t,v t/RCS/t46chpblk.t,v
t/RCS/t50update.t,v t/RCS/t50update.t,v
t/RCS/t51getinfo.t,v t/RCS/t51getinfo.t,v
t/RCS/t53types.t,v t/RCS/t53types.t,v
t/RCS/t54native.t,v t/RCS/t54native.t,v
DBD-Informix.msd view on Meta::CPAN
examples/RCS/x05fetchall_arrayref.pl,v examples/RCS/x05fetchall_arrayref.pl,v
examples/RCS/x06chopblanks.pl,v examples/RCS/x06chopblanks.pl,v
examples/RCS/x07fetchrow_array.pl,v examples/RCS/x07fetchrow_array.pl,v
examples/RCS/x10cgi_nodbi.pl,v examples/RCS/x10cgi_nodbi.pl,v
examples/RCS/x11cgi_nodbi.pl,v examples/RCS/x11cgi_nodbi.pl,v
examples/RCS/x12cgi_noform.pl,v examples/RCS/x12cgi_noform.pl,v
examples/RCS/x13cgi_noform.pl,v examples/RCS/x13cgi_noform.pl,v
examples/RCS/x14cgi_form.pl,v examples/RCS/x14cgi_form.pl,v
examples/RCS/x15cgi_form.pl,v examples/RCS/x15cgi_form.pl,v
# Experiment - simulate scroll cursors.
examples/RCS/fetchscroll.pl,v examples/RCS/fetchscroll.pl,v
# Custom Distribution Tools
# Release mechanism stuff - not needed except by maintainers, mostly.
RCS/Release.Checklist,v RCS/Release.Checklist,v
RCS/DBD-Informix.jdc,v RCS/DBD-Informix.jdc,v
RCS/DBD-Informix.msd,v RCS/DBD-Informix.msd,v
RCS/DBD-Informix.nmd,v RCS/DBD-Informix.nmd,v
# Recommended mechanism for using ExtUtils::AutoInstall
DBD-Informix.nmd view on Meta::CPAN
t/t22mconn.t t/RCS/t22mconn.t,v 2014.1
t/t23mconn.t t/RCS/t23mconn.t,v 2014.1
t/t24mcurs.t t/RCS/t24mcurs.t,v 2014.1
t/t25dratt.t t/RCS/t25dratt.t,v 2014.1
t/t28dtlit.t t/RCS/t28dtlit.t,v 2014.1
t/t29update.t t/RCS/t29update.t,v 2014.1
t/t30update.t t/RCS/t30update.t,v 2014.1
t/t31nulls.t t/RCS/t31nulls.t,v 2014.1
t/t32nulls.t t/RCS/t32nulls.t,v 2014.1
t/t33holdcurs.t t/RCS/t33holdcurs.t,v 2014.1
t/t35cursor.t t/RCS/t35cursor.t,v 2014.1
t/t40rows.t t/RCS/t40rows.t,v 2014.1
t/t41txacoff.t t/RCS/t41txacoff.t,v 2014.1
t/t42txacon.t t/RCS/t42txacon.t,v 2014.1
t/t43trans.t t/RCS/t43trans.t,v 2014.1
t/t44txansi.t t/RCS/t44txansi.t,v 2014.1
t/t46chpblk.t t/RCS/t46chpblk.t,v 2014.1
t/t50update.t t/RCS/t50update.t,v 2014.1
t/t51getinfo.t t/RCS/t51getinfo.t,v 2014.1
t/t53types.t t/RCS/t53types.t,v 2014.1
t/t54native.t t/RCS/t54native.t,v 2014.1
DBD-Informix.nmd view on Meta::CPAN
examples/x05fetchall_arrayref.pl examples/RCS/x05fetchall_arrayref.pl,v 100.1
examples/x06chopblanks.pl examples/RCS/x06chopblanks.pl,v 100.1
examples/x07fetchrow_array.pl examples/RCS/x07fetchrow_array.pl,v 100.1
examples/x10cgi_nodbi.pl examples/RCS/x10cgi_nodbi.pl,v 100.1
examples/x11cgi_nodbi.pl examples/RCS/x11cgi_nodbi.pl,v 100.1
examples/x12cgi_noform.pl examples/RCS/x12cgi_noform.pl,v 100.1
examples/x13cgi_noform.pl examples/RCS/x13cgi_noform.pl,v 100.1
examples/x14cgi_form.pl examples/RCS/x14cgi_form.pl,v 100.1
examples/x15cgi_form.pl examples/RCS/x15cgi_form.pl,v 100.1
# Experiment - simulate scroll cursors.
examples/fetchscroll.pl examples/RCS/fetchscroll.pl,v 1.4
inc/ExtUtils/AutoInstall.pm inc/ExtUtils/AutoInstall.pm -
DBD-Informix.jdc RCS/DBD-Informix.jdc,v 2018.5
DBD-Informix.msd RCS/DBD-Informix.msd,v 2018.1
DBD-Informix.nmd RCS/DBD-Informix.nmd,v $Revision: 2018.6 $
mknmd.sh RCS/mknmd.sh,v 2012.1
prodverstamp.sh RCS/prodverstamp.sh,v 2015.1
Informix.pm view on Meta::CPAN
The key factor is the version of ESQL/C used when building
DBD::Informix.
Basically, there are two groups of versions to worry about, the 5.x
family of versions (5.00.UC1 through 5.20.UCx at the moment), and the 6.x
and later family of versions (6.00 through 9.53, and then 2.90 through 3.70
at the moment; yes, some clown in marketing decreased the version number).
All version families acquire extra versions on occasion.
Note that DBD::Informix does not work with Informix ESQL/C Version
4.1x or earlier versions because it uses both SQL descriptors and
strings for cursor names and statement names, and these features were
not available before Version 5.00.
For information about Informix software, you should also read the
Notes/FAQ file that is distributed with Informix Database Driver for Perl DBI.
=head2 TECHNICAL SUPPORT
For information on technical support for Informix Database Driver for Perl DBI, please run:
perldoc DBD::Informix::TechSupport
Informix.pm view on Meta::CPAN
Note that you must use a placeholder if the string could be longer
than 255 characters, or if the underlying column is a blob (BYTE,
TEXT, BLOB or CLOB) type.
Otherwise, the string probably represents a table name or a column
name and you must use $dbh->quote.
=head2 CREATING STATEMENTS
You can also prepare a statement for multiple uses, and you can do
this for SELECT and EXECUTE PROCEDURE statements that return data
(cursory statements) as well as noncursory statements that return no data.
You create a statement handle (another reference) using:
$sth = $dbh->prepare($stmt);
If the statement is a SELECT that returns data (not SELECT...INTO TEMP) or
an EXECUTE PROCEDURE for a procedure that returns values, a cursor is
declared for the prepared statement.
The prepare call accepts an optional attributes parameter that is a
reference to a hash.
Starting with version 1.03.PC1, the following attributes are recognized:
{ix_InsertCursor => 1, ix_ScrollCursor => 1, ix_CursorWithHold => 1}
The ix_ScrollCursor is a placeholder that may become unnecessary with a
future revision of DBI.
The ix_CursorWithHold attribute is only of relevance if AutoCommit is
disabled.
When AutoCommit is enabled, all cursors have to be WITH HOLD (just one
more reason to hate AutoCommit).
$sth = $dbh->prepare("SELECT id, name FROM tablename", {'ix_CursorWithHold' => 1});
After the cursor is opened ($sth->execute), it is not closed by
$dbh->commit().
Either fetch all the rows or use $sth->finish() to close it.
The ix_InsertCursor attribute can be applied to an INSERT statement (but
generates an error -481 for other types of statement).
Subsequent uses of $sth->execute() will use the ESQL/C PUT statement to
insert the data, and $sth->finish() will close the INSERT cursor.
There is at present no mechanism to invoke the FLUSH statement.
It would be reasonable to add {ix_BlobLocation => 'InFile'} to support
per-statement blob location.
You need to check for errors unless you are using {RaiseError => 1}.
# Emphasizing the error handling.
die "Failed to prepare '$stmt'\n"
unless ($sth = $dbh->prepare($stmt));
# Emphasizing the SQL action.
$sth = $dbh->prepare($stmt) or die "Failed to prepare '$stmt'\n"
You can tell whether the statement is just executable or whether it is
a cursory (fetchable) statement by testing the
Informix-specific attribute ix_Fetchable.
The approved, canonical DBI method of doing this check is
"$sth->{NUM_OF_FIELDS} > 0".
Once the statement is prepared, you can execute it:
$sth->execute;
For a noncursory statement, this simply executes the statement.
If the statement is executed successfully, the number of rows
affected will be returned.
If an error occurs, the returned value will be undef.
If the statement does not affect any rows, the string returned is
"0E0", which evaluates to true but also to zero.
For a cursory statement, $sth->execute opens the cursor.
If the cursor is opened successfully, it returns the value "0E0",
which evaluates to true but also to zero.
If an error occurs, the returned value will be undef.
You can also specify the input parameters for a statement that contains
question-marks as place-holders using:
$sth->execute(@parameters);
The first parameter will be supplied as the value for the first
place-holder question mark in the statement, the second parameter for
Informix.pm view on Meta::CPAN
Note that you cannot use $sth->execute($blob_val, $pkey) because there
is no way to convey the type information to the code.
Also note that Informix servers do provide information about blob
values in both the select-list of a SELECT statement and the VALUES
clause of the INSERT statement.
The INSERT statement is a special case, and it provides support for
code that implements the non-SQL statement 'LOAD FROM "file" INSERT
INTO SomeTable'.
For cursory statements, you can discover the returned column
names, types, nullability, and so on.
You do this with:
@name = @{$sth->{NAME}}; # Column names
@null = @{$sth->{NULLABLE}}; # True => accepts nulls
@type = @{$sth->{TYPE}}; # ODBC Data Type numbers
@prec = @{$sth->{PRECISION}}; # ODBC PRECISION numbers (or undef)
@scal = @{$sth->{SCALE}}; # ODBC SCALE numbers (or undef)
# Native (Informix) type equivalents
Informix.pm view on Meta::CPAN
Note: Informix uses '(expression)' in the array $sth->{NAME} for any
nonaliased computed value in a SELECT list, and to describe the return
values from stored procedures, and so on.
This could be usefully improved.
There is also no guarantee that the names returned are unique.
For example, in "SELECT A.Column, B.Column FROM Table1 A, Table1 B
WHERE ...", both the return columns are described as 'column'.
=back
If the statement is a cursory statement, you can retrieve the
values in any of a number of ways, as described in the DBI
specification.
$ref = $sth->fetchrow_arrayref;
$ref = $sth->fetch; # Alternative spelling...
@row = @{$ref};
@row = @{$sth->fetchrow_arrayref}; # Shorthand for above...
@row = $sth->fetchrow_array;
Informix.pm view on Meta::CPAN
You can also set the ix_BlobLocation attribute on the database,
overriding it at the statement level.
=over 4
BUG: ix_BlobLocation is not handled properly.
=back
When you have fetched as many rows as required, you close the cursor using:
$sth->finish;
You do not have to finish a cursor explicitly if you executed a fetch
that failed to retrieve any data.
Using $sth->finish simply closes the cursor but does not free the cursor
or the statement.
That is done when you destroy (undef) the statement handle:
undef $sth;
You can also implicitly rebind a statement handle to a new statement
by simply using the same variable again.
This does not cause any memory leaks.
You can use the (DBI standard) Statement attribute to discover (or
rediscover) the text of a statement:
$txt = $sth->{Statement};
=head2 CURSORS FOR UPDATE
You can use the (DBI standard) attribute $sth->{CursorName} to retrieve the name of a
cursor.
If the statement for $sth is actually a SELECT and the cursor is in a
MODE ANSI database or is declared with the 'FOR UPDATE [OF col,...'
tag, you can use the cursor name in a 'DELETE...WHERE CURRENT OF'
or 'UPDATE...WHERE CURRENT OF' statement.
$st1 = $dbh->prepare("SELECT * FROM SomeTable FOR UPDATE");
$wc = "WHERE CURRENT OF $st1->{CursorName}";
$st2 = $dbh->prepare("UPDATE SomeTable SET SomeColumn = ? $wc");
$st3 = $dbh->prepare("DELETE FROM SomeTable $wc");
$st1->execute;
$row = $st1->fetch;
$st2->execute("New Value");
$row = $st1->fetch;
Informix.pm view on Meta::CPAN
WORK operation as being in the No-TX state.
Any statement other than a disconnection statement (Group 2) or a
commit or rollback (Group 3A or 3C) takes the databases into the
In-TX state.
In a MODE ANSI database, you can execute BEGIN WORK successfully.
However, if AutoCommit is On, the transaction is immediately
committed, so it does you no good.
If the user elects to switch to AutoCommit On, things get trickier.
All cursors need to be declared WITH HOLD so that Group 4B statements
being committed do not close the active cursors.
Whenever a Group 4B statement is executed, the statement needs to be
committed.
With OnLine (and theoretically with SE), if the statement fails there
is no need to do a rollback -- the statement failing did the rollback
anyway.
As before, the code does ROLLBACK WORK before disconnecting, though it
should not actually be necessary.
=head2 LOGGED DATABASES
Informix.pm view on Meta::CPAN
$dbh->commit => COMMIT WORK (+BEGIN WORK)
$dbh->rollback => ROLLBACK WORK (+BEGIN WORK)
$dbh->do => EXECUTE IMMEDIATE
$dbh->prepare => PREPARE, DESCRIBE (DECLARE)
$sth->execute => EXECUTE or OPEN
$sth->fetch => FETCH
$sth->fetchrow => FETCH
$sth->finish => CLOSE
undef $sth => FREE cursor, FREE statement, etc
=head1 KNOWN RESTRICTIONS
=over 2
=item *
Blobs (meaning BYTE and TEXT blobs) can only be located in memory.
The provision for locating them in files is not functional.
t/t22mconn.t
t/t23mconn.t
t/t24mcurs.t
t/t25dratt.t
t/t28dtlit.t
t/t29update.t
t/t30update.t
t/t31nulls.t
t/t32nulls.t
t/t33holdcurs.t
t/t35cursor.t
t/t40rows.t
t/t41txacoff.t
t/t42txacon.t
t/t43trans.t
t/t44txansi.t
t/t46chpblk.t
t/t50update.t
t/t51getinfo.t
t/t53types.t
t/t54native.t
Notes/Working.Versions view on Meta::CPAN
<PERL> 5.005_03 </PERL>
<SYSTEM> Linux 2.2.13 </SYSTEM>
<SYS_COMPILER> Unknown </SYS_COMPILER>
<SYS_LOADER> Unknown </SYS_LOADER>
<WHEN> 1999-11-17 </WHEN>
<WHO> Peter Eckhardt <Peter.Eckhardt@transcom.de> </WHO>
<Z_NOTES> Optional Notes</Z_NOTES>
</WORKING_VERSION>
<WORKING_VERSION VERSION="1.00">
<DBD_INFORMIX> 0.61_02 with patches for hold cursors </DBD_INFORMIX>
<DBI> 1.13 </DBI>
<INFORMIX_ESQLC> 9.20UC3 (SDK 2.20UC3) </INFORMIX_ESQLC>
<INFORMIX_SERVER> Unknown </INFORMIX_SERVER>
<PERL> 5.005_02 </PERL>
<SYSTEM> HP-UX 10.20 </SYSTEM>
<SYS_COMPILER> Unknown </SYS_COMPILER>
<SYS_LOADER> Unknown </SYS_LOADER>
<WHEN> 1999-11-18 </WHEN>
<WHO> Bill Rothanburg <brothanb@fll-ro.dhl.com> </WHO>
<Z_NOTES> Optional Notes</Z_NOTES>
bug-lvcnn.ec view on Meta::CPAN
}
static void check_data(void)
{
$ lvarchar *lv1 = 0;
$ lvarchar *lv2 = 0;
$ int row;
$ short ind;
$ prepare p from "select row_number, lvc_with_null, lvc_wout_null from lvarchar_test order by row_number";
$ declare c cursor for p;
$ allocate descriptor "d" with max 3;
$ describe p using sql descriptor "d";
/*
** The following two lines do not work around the problem.
** $ set descriptor "d" value 2 NULLABLE = 1;
** $ set descriptor "d" value 3 NULLABLE = 1;
*/
/* Print allocator description */
/* RT#54426: Fix for NUM_OF_FIELDS > 0 test */
/* Avoid returning non-zero value on INSERT */
if (imp_sth->st_type == SQ_SELECT ||
(imp_sth->st_type == SQ_EXECPROC && imp_sth->n_ocols > 0))
retsv = newSViv((IV)imp_sth->n_ocols);
else
retsv = newSViv(0);
}
else if (KEY_MATCH(kl, key, "CursorName"))
{
retsv = newSVpv(imp_sth->nm_cursor, 0);
}
else if (KEY_MATCH(kl, key, "ParamValues"))
{
retsv = dbd_ix_st_bound_parameters(imp_sth);
}
/* Informix specific attributes */
else if (KEY_MATCH(kl, key, ix_typenam))
{
char buffer[SQLTYPENAME_BUFSIZ];
retsv = newRV_inc((SV *)av);
for (i = 1; i <= imp_sth->n_ocols; i++)
{
EXEC SQL GET DESCRIPTOR :nm_obind VALUE :i
:collength = LENGTH;
av_store(av, i - 1, newSViv((IV)collength));
}
}
else if (KEY_MATCH(kl, key, ix_csrhold))
{
retsv = newSViv((IV)imp_sth->is_holdcursor);
}
else if (KEY_MATCH(kl, key, ix_scrlcsr))
{
retsv = newSViv((IV)imp_sth->is_scrollcursor);
}
else if (KEY_MATCH(kl, key, ix_instcsr))
{
retsv = newSViv((IV)imp_sth->is_insertcursor);
}
else
{
dbd_ix_exit(function);
return Nullsv;
}
dbd_ix_exit(function);
if (av != 0)
/* One day, these will go! Maybe... */
static void del_statement(imp_sth_t *imp_sth);
static int dbd_ix_begin(imp_dbh_t *dbh);
/*
** Discussion of imp_sth->st_state (JL 2002-02-12).
** The State enumeration can take the values: Unused, Prepared,
** Allocated, Described, Declared, Opened, NoMoreData.
** -- Unused state means that there is no prepared statement, nor (by
** definition) a declared cursor, nor any allocated descriptors.
** -- Prepared state means that there is a prepared statement but no
** declared cursor nor any allocated descriptors. -JL-VERIFY
** -- Allocated state means that there is a prepared statement and a
** descriptor for the input parameters (nm_idesc), but no declared
** cursor nor any output descriptor (nm_odesc). -JL-VERIFY
** -- Described state means that there is a prepared statement and
** descriptors for both input and output parameters. -JL-VERIFY
** -- Declared state means that there is both a prepared statement and a
** declared cursor (which is currently closed) and descriptors for
** both input and output parameters.
** -- Opened state means that the cursor is also open.
** -- NoMoreData state means that the cursor is closed, but that any
** further fetches on the statement should always indicate NoMoreData
** (SQLNOTFOUND). This is a consequence of the DBI requirement that
** the $sth->finish function should only be necessary for an early
** exit from a fetch loop. If you use $sth->finish on a NoMoreData
** cursor, the state is changed to Declared. If you use $sth->finish
** on an open cursor, the cursor is closed and the state is changed
** to Declared. If you attempt $sth->finish on a cursor in any other
** state, you will get an error.
*/
/* ================================================================= */
/* ==================== Driver Level Operations ==================== */
/* ================================================================= */
/* Official name for DBD::Informix module */
const char *
dbd_ix_module(void)
}
/* ================================================================== */
/* =================== Statement Level Operations =================== */
/* ================================================================== */
/* Initialize a statement structure, allocating names */
static void
new_statement(imp_dbh_t *imp_dbh, imp_sth_t *imp_sth)
{
static long cursor_num = 0;
sprintf(imp_sth->nm_stmnt, "p_%09ld", cursor_num);
sprintf(imp_sth->nm_cursor, "c_%09ld", cursor_num);
sprintf(imp_sth->nm_obind, "d_%09ld", cursor_num);
sprintf(imp_sth->nm_ibind, "b_%09ld", cursor_num);
imp_sth->dbh = imp_dbh;
imp_sth->st_state = Unused;
imp_sth->st_type = 0;
imp_sth->st_text = 0;
imp_sth->n_iblobs = 0;
imp_sth->n_oblobs = 0;
imp_sth->n_icols = 0;
imp_sth->n_rows = 0;
imp_sth->n_ocols = 0;
imp_sth->n_iudts = 0;
imp_sth->n_oudts = 0;
imp_sth->a_iudts = 0;
imp_sth->a_oudts = 0;
imp_sth->n_lvcsz = 0;
imp_sth->a_lvcsz = 0;
imp_sth->is_holdcursor = False;
imp_sth->is_scrollcursor = False;
dbd_ix_link_add(&imp_dbh->head, &imp_sth->chain);
imp_sth->chain.data = (void *)imp_sth;
cursor_num++;
/* Cleanup required for statement chain in imp_dbh */
DBIc_on(imp_sth, DBIcf_IMPSET);
}
/* Close cursor */
static int
dbd_ix_close(imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_close";
EXEC SQL BEGIN DECLARE SECTION;
char *nm_cursor = imp_sth->nm_cursor;
EXEC SQL END DECLARE SECTION;
dbd_ix_enter(function);
assert(imp_sth->st_state == Opened);
if (imp_sth->st_state == Opened)
{
EXEC SQL CLOSE :nm_cursor;
dbd_ix_sqlcode(imp_sth->dbh);
imp_sth->st_state = Declared;
if (sqlca.sqlcode < 0)
{
dbd_ix_exit(function);
return 0;
}
}
dbd_ix_exit(function);
return 1;
}
switch (imp_sth->st_state)
{
case NoMoreData:
dbd_ix_debug(5, "\t---- %s() state %s\n", function, "NoMoreData");
/* FALLTHROUGH */
case Opened:
dbd_ix_debug(5, "\t---- %s() state %s\n", function, "Opened");
name = imp_sth->nm_cursor;
EXEC SQL CLOSE :name;
dbd_ix_debug(3, "\t---- %s() CLOSE cursor %s\n", function, name);
/* FALLTHROUGH */
case Declared:
dbd_ix_debug(5, "\t---- %s() state %s\n", function, "Declared");
name = imp_sth->nm_cursor;
EXEC SQL FREE :name;
dbd_ix_debug(3, "\t---- %s() FREE cursor %s\n", function, name);
/* FALLTHROUGH */
case Described:
dbd_ix_debug(5, "\t---- %s() state %s\n", function, "Described");
/* FALLTHROUGH */
case Allocated:
dbd_ix_debug(5, "\t---- %s() state %s\n", function, "Allocated");
dbd_ix_st_deallocate(imp_sth->nm_obind, imp_sth->n_oblobs, imp_sth->n_ocols);
/* FALLTHROUGH */
/* Tell ESQL/C how to handle this blob */
EXEC SQL SET DESCRIPTOR :nm_obind VALUE :colno DATA = :blob;
dbd_ix_sqlcode(imp_sth->dbh);
}
}
dbd_ix_exit(function);
}
/*
** Workaround for CQ idsdb00247065: ESQL/C reporting error -1820 when
** reusing SQL DESCRIPTOR after reopening cursor
*/
static int
count_lvc(char *descname, int ncols)
{
/*static const char function[] = "count_lvc";*/
EXEC SQL BEGIN DECLARE SECTION;
char *nm_obind = descname;
int colno;
int coltype;
EXEC SQL END DECLARE SECTION;
assert(lvcp != 0);
imp_sth->a_oudts[i++] = lvcp;
}
}
assert(i == nudts);
}
dbd_ix_exit(function);
return(nudts);
}
/* Declare cursor for SELECT, EXECUTE PROCEDURE, or INSERT */
static int
dbd_ix_declare(imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_declare";
EXEC SQL BEGIN DECLARE SECTION;
char *nm_stmnt = imp_sth->nm_stmnt;
char *nm_cursor = imp_sth->nm_cursor;
EXEC SQL END DECLARE SECTION;
dbd_ix_enter(function);
#ifdef SQ_EXECPROC
assert(imp_sth->st_type == SQ_SELECT || imp_sth->st_type == SQ_INSERT ||
imp_sth->st_type == SQ_EXECPROC);
#else
assert(imp_sth->st_type == SQ_SELECT || imp_sth->st_type == SQ_INSERT);
#endif /* SQ_EXECPROC */
assert(imp_sth->st_state == Described);
dbd_ix_blobs(imp_sth);
dbd_ix_lvarchar(imp_sth); /* CQ idsdb00247065 */
dbd_ix_udts(imp_sth);
/* BR 1999-08-30: Hold Cursor -- Not necessarily correct... */
if (imp_sth->dbh->is_modeansi == True &&
DBI_AutoCommit(imp_sth->dbh) == True)
{
/* XPS 8.11 does not support hold cursors (Robert Wyrick <rob@wyrick.org>) */
/* Note that the ESQL/C does support hold cursors. */
/* The issue is whether the server does. */
/* Assume 8.00 through 8.29 does not do so either. 8.30 may support them. */
if (imp_sth->dbh->srvr_vrsn >= 800 && imp_sth->dbh->srvr_vrsn < 830)
imp_sth->is_holdcursor = False;
else
imp_sth->is_holdcursor = True;
}
#define print_tf(a) (a == True ? "True" : "False")
dbd_ix_debug(3, "\t---- is_holdcursor = %s", print_tf(imp_sth->is_holdcursor));
dbd_ix_debug(3, "\t---- is_scrollcursor = %s", print_tf(imp_sth->is_scrollcursor));
dbd_ix_debug(3, "\t---- is_insertcursor = %s", print_tf(imp_sth->is_insertcursor));
#undef print_tf
if (imp_sth->is_scrollcursor == True)
{
if (imp_sth->is_holdcursor == True)
{
EXEC SQL DECLARE :nm_cursor SCROLL CURSOR WITH HOLD FOR :nm_stmnt;
}
else
{
EXEC SQL DECLARE :nm_cursor SCROLL CURSOR FOR :nm_stmnt;
}
}
else
{
if (imp_sth->is_insertcursor && imp_sth->dbh->is_loggeddb &&
DBI_AutoCommit(imp_sth->dbh) == True)
{
warn("insert cursor ineffective with AutoCommit enabled");
}
if (imp_sth->is_holdcursor == True)
{
EXEC SQL DECLARE :nm_cursor CURSOR WITH HOLD FOR :nm_stmnt;
}
else
{
EXEC SQL DECLARE :nm_cursor CURSOR FOR :nm_stmnt;
}
}
dbd_ix_sqlcode(imp_sth->dbh);
if (sqlca.sqlcode < 0)
{
dbd_ix_exit(function);
return 0;
}
imp_sth->st_state = Declared;
dbd_ix_exit(function);
new_statement(imp_dbh, imp_sth);
nm_stmnt = imp_sth->nm_stmnt;
nm_obind = imp_sth->nm_obind;
imp_sth->st_text = newSVpv(stmt, 0);
/* Bill R. Code to allow the setting of Hold and Scroll Cursor Attribs */
if (attribs == NULL)
dbd_ix_debug(4, "\t---- %s - no attribs set", function);
else
{
imp_sth->is_holdcursor = dbd_ix_st_attrib(attribs, ix_hc);
imp_sth->is_scrollcursor = dbd_ix_st_attrib(attribs, ix_sc);
imp_sth->is_insertcursor = dbd_ix_st_attrib(attribs, ix_ic);
}
dbd_ix_debug(4, "\t---- %s <<%s>>\n", function, statement);
EXEC SQL PREPARE :nm_stmnt FROM :statement;
dbd_ix_savesqlca(imp_dbh);
dbd_ix_sqlcode(imp_dbh);
if (sqlca.sqlcode < 0)
{
del_statement(imp_sth);
dbd_ix_exit(function);
EXEC SQL GET DESCRIPTOR :nm_obind :desc_count = COUNT;
dbd_ix_sqlcode(imp_dbh);
if (sqlca.sqlcode < 0)
{
del_statement(imp_sth);
dbd_ix_exit(function);
return 0;
}
/* Record the number of fields in the cursor for DBI and DBD::Informix */
DBIc_NUM_FIELDS(imp_sth) = imp_sth->n_ocols = desc_count;
/* Cannot create an INSERT cursor except on an insert statement */
if (imp_sth->is_insertcursor == True && imp_sth->st_type != SQ_INSERT)
{
/* -481: Invalid statement name or statement was not prepared */
/* Generated by 9.21.UC1 in response to declare cursor on update stmt */
sqlca.sqlcode = -481;
dbd_ix_sqlcode(imp_dbh);
del_statement(imp_sth);
dbd_ix_exit(function);
return(0);
}
/**
** Only non-cursory statements need an output descriptor.
** Only cursory statements need a cursor declared for them.
** INSERT may yield an input descriptor (which will appear to be the
** output descriptor, such being the wonders of Informix).
** UPDATE and DELETE (and, indeed, INSERT, SELECT and EXECUTE
** PROCEDURE) statements would benefit from having a description of
** the input parameters, but this is not available. SQL-92 defines
** DESCRIBE INPUT and DESCRIBE OUTPUT, but (as of 2000-08-01)
** Informix does not implement DESCRIBE INPUT.
*/
if (imp_sth->st_type == SQ_SELECT)
rc = dbd_ix_declare(imp_sth);
imp_sth->n_iudts = imp_sth->n_oudts;
imp_sth->n_oudts = t1;
t2 = imp_sth->a_iudts;
imp_sth->a_iudts = imp_sth->a_oudts;
imp_sth->a_oudts = t2;
dbd_ix_debug(3, "%s() switch descriptor names: new ibind %s\n", function, imp_sth->nm_ibind);
dbd_ix_debug(3, "%s() switch descriptor names: new obind %s\n", function, imp_sth->nm_obind);
imp_sth->n_icols = desc_count;
}
rc = 1;
if (imp_sth->is_insertcursor == True)
rc = dbd_ix_declare(imp_sth);
}
else
{
/**
** JL 2000-08-09:
** The IDS 7.30 and later servers nearly support describe for
** UPDATE. However, it requires a special server configuration.
** Worse, the information returned by DESCRIBE is not usable.
** Bug B111987: DESCRIBE ON UPDATE STATEMENT GIVES INADEQUATE
imp_sth->st_state = Prepared;
rc = 1;
}
dbd_ix_debug(2, "\t---- %s imp_sth->n_ocols: %d\n", function, imp_sth->n_ocols);
dbd_ix_exit(function);
return rc;
}
/* CLOSE cursor */
int
dbd_ix_st_finish(SV *sth, imp_sth_t *imp_sth, int gd_flag)
{
static const char function[] = "dbd_ix_st_finish";
dTHR;
int rc;
dbd_ix_enter(function);
if ((rc = dbd_db_setconnection(imp_sth->dbh)) == 0)
imp_sth->st_state = Declared;
else
rc = 0;
DBIc_ACTIVE_off(imp_sth);
}
dbd_ix_exit(function);
return rc;
}
/* Free up resources used by the cursor or statement */
void
dbd_ix_st_destroy(SV *sth, imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_st_destroy";
dbd_ix_enter(function);
del_statement(imp_sth);
dbd_ix_exit(function);
}
/* Convert DECIMAL to convenient string */
** the host variable, not the length of the database column, whereas 'vc'
** is blank-padded to the length of the database column for a CHAR column,
** and to the length of the inserted data in a VARCHAR column.
*/
AV *
dbd_ix_st_fetch(SV *sth, imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_st_fetch";
AV *av;
EXEC SQL BEGIN DECLARE SECTION;
char *nm_cursor = imp_sth->nm_cursor;
char *nm_obind = imp_sth->nm_obind;
varchar coldata[256];
long coltype;
long collength;
long colind;
char colname[SQL_COLNAMELEN];
int index;
char *result;
long length;
loc_t blob;
if (dbd_db_setconnection(imp_sth->dbh) == 0)
{
dbd_ix_savesqlca(imp_sth->dbh);
dbd_ix_exit(function);
return Nullav;
}
if (imp_sth->st_state == NoMoreData)
{
/* Simulate SQLNOTFOUND on a closed cursor */
dbd_ix_debug(1, "%s: Simulate SQLNOTFOUND\n", function);
sqlca.sqlcode = SQLNOTFOUND;
dbd_ix_savesqlca(imp_sth->dbh);
dbd_ix_sqlcode(imp_sth->dbh);
dbd_ix_exit(function);
return Nullav;
}
/* JL 2007-08-24: verified necessary - core dumps otherwise */
dbd_ix_blobs(imp_sth); /* Fix -451 errors; Rich Jones <rich@annexia.org> */
dbd_ix_debug(1, "\t---- %s: FETCH %s into %s\n", function, nm_cursor, nm_obind);
EXEC SQL FETCH :nm_cursor USING SQL DESCRIPTOR :nm_obind;
dbd_ix_savesqlca(imp_sth->dbh);
dbd_ix_sqlcode(imp_sth->dbh);
if (sqlca.sqlcode != 0)
{
if (sqlca.sqlcode != SQLNOTFOUND)
{
dbd_ix_debug(1, "\t---- %s -- FETCH failed\n", function);
}
else
{
/* Implicitly CLOSE cursor when no more data available */
dbd_ix_close(imp_sth);
imp_sth->st_state = NoMoreData;
dbd_ix_debug(1, "\t---- %s -- SQLNOTFOUND\n", function);
}
dbd_ix_exit(function);
return Nullav;
}
imp_sth->n_rows++;
free(result);
break;
}
}
}
}
dbd_ix_exit(function);
return(av);
}
/* Open a cursor */
static int
dbd_ix_open(imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_open";
EXEC SQL BEGIN DECLARE SECTION;
char *nm_cursor = imp_sth->nm_cursor;
char *nm_ibind = imp_sth->nm_ibind;
EXEC SQL END DECLARE SECTION;
dbd_ix_enter(function);
assert(imp_sth->st_state == Declared || imp_sth->st_state == Opened ||
imp_sth->st_state == NoMoreData);
/* Close currently open cursors - MODE ANSI databases give error otherwise */
if (imp_sth->st_state == Opened)
{
dbd_ix_close(imp_sth);
if (sqlca.sqlcode < 0)
{
dbd_ix_exit(function);
return 0;
}
}
assert(imp_sth->st_state == Declared || imp_sth->st_state == NoMoreData);
if ((imp_sth->st_type != SQ_INSERT) && (imp_sth->n_icols > 0) )
EXEC SQL OPEN :nm_cursor USING SQL DESCRIPTOR :nm_ibind;
else
EXEC SQL OPEN :nm_cursor;
dbd_ix_sqlcode(imp_sth->dbh);
dbd_ix_savesqlca(imp_sth->dbh);
if (sqlca.sqlcode < 0)
{
dbd_ix_exit(function);
return 0;
}
dbd_ix_reset_lvarchar_sizes(imp_sth);
imp_sth->st_state = Opened;
if (imp_sth->dbh->is_modeansi == True)
if (DBIc_DBISTATE(sth)->debug >= 4)
warn("new database name <<%s>>\n", SvPV(sth->dbh->database, PL_na));
dbd_ix_exit(function);
}
static int
dbd_ix_exec(imp_sth_t *imp_sth)
{
static const char function[] = "dbd_ix_exec";
EXEC SQL BEGIN DECLARE SECTION;
char *nm_cursor = imp_sth->nm_cursor;
char *nm_stmnt = imp_sth->nm_stmnt;
char *nm_ibind = imp_sth->nm_ibind;
EXEC SQL END DECLARE SECTION;
imp_dbh_t *dbh = imp_sth->dbh;
int rc = 1;
Boolean exec_stmt = True;
dbd_ix_enter(function);
if (imp_sth->st_type == SQ_BEGWORK)
}
}
if (exec_stmt == True)
{
if (imp_sth->n_icols <= 0)
{
dbd_ix_debug(2, "\t---- EXECUTE %s - no parameters\n", nm_stmnt);
EXEC SQL EXECUTE :nm_stmnt;
}
else if (imp_sth->st_type == SQ_INSERT && imp_sth->is_insertcursor == True)
{
dbd_ix_debug(2, "\t---- PUT %s USING %s\n", nm_cursor, nm_ibind);
EXEC SQL PUT :nm_cursor USING SQL DESCRIPTOR :nm_ibind;
}
else
{
dbd_ix_debug(2, "\t---- EXECUTE %s USING %s\n", nm_stmnt, nm_ibind);
EXEC SQL EXECUTE :nm_stmnt USING SQL DESCRIPTOR :nm_ibind;
}
}
dbd_ix_sqlcode(dbh);
dbd_ix_savesqlca(dbh);
break;
}
DBIc_on(imp_sth, DBIcf_IMPSET); /* Qu'est que c'est? */
dbd_ix_exit(function);
return rc;
}
/*
** Execute the statement.
** - OPEN the cursor for a SELECT or cursory EXECUTE PROCEDURE.
** - EXECUTE the statement for anything else.
** Remember that dbd_st_execute() must return:
** -2 or smaller => error
** -1 => unknown number of rows affected
** 0 or greater => known number of rows affected
** DBD::Informix will not return -1, though there's at least half an
** argument for returning -1 after dbd_ix_open() is called.
*/
int
dbd_ix_st_execute(SV *sth, imp_sth_t *imp_sth)
if (imp_sth->st_type == SQ_SELECT)
rc = dbd_ix_open(imp_sth);
#ifdef SQ_EXECPROC
else if (imp_sth->st_type == SQ_EXECPROC && imp_sth->n_ocols > 0)
rc = dbd_ix_open(imp_sth);
#endif /* SQ_EXECPROC */
else
{
rc = 1;
/* only open cursor if it is not currently open, otherwise it flushes */
if ((imp_sth->st_type == SQ_INSERT) &&
(imp_sth->is_insertcursor == True) &&
(imp_sth->st_state != Opened))
rc = dbd_ix_open(imp_sth);
if (rc)
rc = dbd_ix_exec(imp_sth);
}
/* Map returned values from dbd_ix_exec and dbd_ix_open */
if (rc == 0)
{
/* Statement failed -- return the error code */
/* Different states for a statement */
enum State
{
Unused, Prepared, Allocated, Described, Declared, Opened, NoMoreData
};
typedef enum State State; /* Cursor/Statement states */
typedef long ErrNum; /* Informix Error Number */
typedef char Name[NAMESIZE]; /* ESQL Object Names */
/* ESQL Object Names covers connection, descriptor, cursor, statement names */
/* Define drh implementor data structure */
struct imp_drh_st
{
dbih_drc_t com; /* MUST be first element in structure */
Boolean multipleconnections;/* Supports multiple connections */
int n_connections; /* Number of active connections */
const char *current_connection; /* Name of current connection */
Link head; /* Head of list of connections */
};
long dbh_pid; /* PID of Perl process creating handle */
Boolean enable_utf8; /* Option ix_EnableUTF8 passed */
};
/* Define sth implementor data structure */
struct imp_sth_st
{
dbih_stc_t com; /* MUST be first element in structure */
Name nm_stmnt; /* Name of prepared statement */
Name nm_obind; /* Name of output descriptor */
Name nm_cursor; /* Name of declared cursor */
Name nm_ibind; /* Name of input descriptor */
State st_state; /* State of statement */
int st_type; /* Type of statement */
SV *st_text; /* Text of statement */
BlobLocn blob_bind; /* Blob Binding */
int n_iblobs; /* Number of blobs in input descriptor */
int n_oblobs; /* Number of blobs in output descriptor */
int n_ocols; /* Number of output fields */
int n_icols; /* Number of input fields */
int n_rows; /* Number of rows processed */
int n_iudts; /* Number of UDTs in input descriptor */
int n_oudts; /* Number of UDTs in output descriptor */
void **a_iudts; /* Array of lvarchar pointers for input UDTs */
void **a_oudts; /* Array of lvarchar pointers for output UDTs */
int n_lvcsz; /* Number of LVARCHAR (not UDT) columns */
int *a_lvcsz; /* Array of integers holding LVARCHAR sizes */
Boolean is_holdcursor; /* Using a hold Cursor */
Boolean is_scrollcursor; /* Using a scroll Cursor */
Boolean is_insertcursor; /* Using a insert Cursor */
imp_dbh_t *dbh; /* Database handle for statement */
Link chain; /* Link in list of statements */
};
#define DBI_AutoCommit(dbh) (DBIc_is(dbh, DBIcf_AutoCommit) ? True : False)
#ifndef DBD_IX_MODULE
#define DBD_IX_MODULE "DBD::Informix"
#endif /* DBD_IX_MODULE */
@(#)Copyright: (C) JLSS 1992-93,1995-97,2000,2002-06,2008
@(#)Product: Informix Database Driver for Perl DBI Version 2018.1031 (2018-10-31)
*/
/*
** @(#)Informix ESQL/C Version 5.0x ANSI C Function Prototypes
*/
/*
** Beware:
** ESQL/C version 5.00 has a 4-argument version of _iqlocate_cursor(), but
** ESQL/C versions 5.02 and upwards (to 5.07 at least) have a 3-argument
** version of _iqlocate_cursor(). Opinion is divided on whether version
** 5.01 used 3 or 4 arguments. On SunOS 4.1.3, 5.01.UC1 used 4 arguments.
** You must set ESQLC_VERSION accurately.
*/
#ifndef ESQL5_00_H
#define ESQL5_00_H
/* There is an unprototyped declaration of _iqlocate_cursor() in <sqlhdr.h> */
#undef _iqlocate_cursor
#define _iqlocate_cursor _iq_non_existent
#include <sqlhdr.h>
#undef _iqlocate_cursor
#include <sqlda.h>
#ifndef MAXADDR
#include <value.h>
#endif /* MAXADDR */
#ifdef MAIN_PROGRAM
#ifndef lint
/* Prevent over-aggressive optimizers from eliminating ID string */
const char jlss_id_esql5_00_h[] = "@(#)$Id: esql5_00.h,v 2008.2 2008/03/10 00:45:19 jleffler Exp $";
#else
/* The code generator emits a dubious declaration for the cmdtxt parameter. */
/* The CCPCCPC (const char pointer to const char pointer to const) typedef */
/* isn't entirely accurate (because the called code isn't prototyped, much */
/* less declared with const attributes, but the code in the called routines */
/* honours the restrictions (it doesn't modify anything), so it does OK. */
typedef const char *const *const CCPCCPC;
#endif /* ESQLC_SLOPPY_CONST */
#if ESQLC_VERSION == 500 || ESQLC_VERSION == 501
extern _SQCURSOR *_iqlocate_cursor(const char *name, int type, int cs, int xx);
#else
extern _SQCURSOR *_iqlocate_cursor(const char *name, int type, int cs);
#endif /* ESQLC_VERSION in {500, 501} */
extern int _iqalloc(const char *descname, int occurrence);
extern int _iqbeginwork(void);
extern int _iqcdcl(_SQCURSOR *cursor,
const char *curname,
CCPCCPC cmdtxt,
struct sqlda *idesc,
struct sqlda *odesc,
int flags);
extern int _iqcddcl(_SQCURSOR *cursor,
const char *curname,
_SQCURSOR *stmt,
int flags);
extern int _iqcftch(_SQCURSOR *cursor,
struct sqlda *idesc,
struct sqlda *odesc,
const char *odesc_name,
_FetchSpec *fetchspec);
extern int _iqclose(_SQCURSOR *cursor);
extern int _iqcommit(void);
extern int _iqcopen(_SQCURSOR *cursor,
int icnt,
struct sqlvar_struct *ibind,
struct sqlda *idesc,
struct value *ivalues,
int useflag);
extern int _iqcput(_SQCURSOR *cursor,
struct sqlda *idesc,
const char *desc_name);
extern int _iqcrproc(const char *fname);
extern int _iqdbase(const char *db_name, int exclusive);
extern int _iqdbclose(void);
extern int _iqdcopen(_SQCURSOR *cursor,
struct sqlda *idesc,
const char *desc_name,
char *ivalues,
int useflag);
extern int _iqdealloc(const char *desc_name);
extern int _iqdescribe(_SQCURSOR *cursor,
struct sqlda **descp,
const char *desc_name);
extern int _iqexecute(_SQCURSOR *cursor,
struct sqlda *idesc,
const char *desc_name,
struct value *ivalues);
extern int _iqeximm(const char *stmt);
extern int _iqexproc(_SQCURSOR *cursor,
CCPCCPC cmdtxt,
int icnt,
struct sqlvar_struct *ibind,
int ocnt,
struct sqlvar_struct *obind,
int chkind);
extern int _iqflush(_SQCURSOR *cursor);
extern int _iqfree(_SQCURSOR *cursor);
extern int _iqgetdesc(const char *desc_name,
int sqlvar_num,
struct hostvar_struct *hosttab,
int xopen_flg);
extern int _iqprepare(_SQCURSOR *cursor, const char *stmt);
extern int _iqrollback(void);
extern int _iqsetdesc(const char *desc_name,
int sqlvar_num,
struct hostvar_struct *hosttab,
int xopen_flg);
extern int _iqslct(_SQCURSOR *cursor,
CCPCCPC cmdtxt,
int icnt,
struct sqlvar_struct *ibind,
int ocnt,
struct sqlvar_struct *obind,
int chkind);
extern int _iqstmnt(_SQSTMT *scb,
CCPCCPC cmdtxt,
int icnt,
struct sqlvar_struct *ibind,
extern int sqlbreak(void);
extern char *ifx_getcur_conn_name(void);
extern int sqldetach(void);
extern int sqlexit(void);
extern int sqlstart(void);
extern void sqlsignal(int sigvalue, void (*ldv)(void), int mode);
extern int sqlbreakcallback(long timeout, void(*)(int));
extern int _iqalloc(char *descname, int occurrence);
extern int _iqbeginwork(void);
extern int _iqcdcl(struct _sqcursor *cursor,char *curname, char **cmdtxt, struct sqlda *idesc, struct sqlda *odesc, int flags);
extern int _iqcddcl(struct _sqcursor *cursor, char *curname, struct _sqcursor *stmt, int flags);
extern int _iqcftch(struct _sqcursor *cursor, struct sqlda *idesc, struct sqlda *odesc, char *odesc_name, _FetchSpec *fetchspec);
extern int _iqchkbuff(struct _sqcursor *cursor, int *direction, long *valptr);
extern int _iqsetautofree(struct _sqcursor *cursor, int status);
extern int _iqsetdefprep(int status);
extern int _iqclose(struct _sqcursor *cursor);
extern int _iqcommit(void);
extern int _iqcopen(struct _sqcursor *cursor, int icnt, struct sqlvar_struct *ibind, struct sqlda *idesc, struct value *ivalues, int useflag);
extern int _iqcput(struct _sqcursor *cursor, struct sqlda *idesc, char *desc_name);
extern int _iqcrproc(char *fname);
extern int _iqdatabase(char *db_name, int exclusive, int icnt, struct sqlvar_struct *ibind);
extern int _iqdbase(char *db_name, int exclusive);
extern int _iqdbclose(void);
extern int _iqdclcur(struct _sqcursor *cursor, char *curname, char **cmdtxt, int icnt, struct sqlvar_struct *ibind, int ocnt, struct sqlvar_struct *obind, int flags);
#ifndef XA_5_0
extern int _iqdcopen(struct _sqcursor *cursor, struct sqlda *idesc, char *desc_name, struct value *ivalues, int useflag, int reoptflag);
#else /* !XA_5_0 */
extern int _iqdcopen(struct _sqcursor *cursor, struct sqlda *idesc, char *desc_name, struct value *ivalues, int useflag);
#endif /* !XA_5_0 */
extern int _iqddclcur(struct _sqcursor *cursor, char *curname, int flags);
extern int _iqdealloc(char *desc_name);
extern int _iqdescribe(struct _sqcursor *cursor, struct sqlda **descp, char *desc_name);
extern int _iqdscribe(struct _sqcursor *cursor, struct sqlda **descp);
extern int _iqexecute(struct _sqcursor *cursor, struct sqlda *idesc, char *idesc_name, struct value *ivalues, struct sqlda *odesc, char *odesc_name, struct value *ovalues, int chkind);
extern int _iqeximm(char *stmt);
extern int _iqexproc(struct _sqcursor *cursor, char **cmdtxt, int icnt, struct sqlvar_struct *ibind, int ocnt, struct sqlvar_struct *obind, int chkind, int freecursor);
extern int _iqflush(struct _sqcursor *cursor);
extern int _iqfree(struct _sqcursor *cursor);
extern int _iqftch(struct _sqcursor *cursor, struct sqlda *odesc, int sysdesc, int chkind, char *odesc_name);
extern int _iqgetdesc(char *desc_name, int sqlvar_num, struct hostvar_struct *hosttab, int xopen_flg);
extern int _iqgetdiag(struct hostvar_struct *hosttab, int exception_num);
extern int _iqinsput( struct _sqcursor *cursor, int icnt, struct sqlvar_struct *ibind, struct sqlda *idesc, struct value *ivalues);
extern struct _sqcursor *_iqlocate_cursor(char *name, int type);
extern int _iqnftch(struct _sqcursor *cursor, int ocnt, struct sqlvar_struct *obind, struct sqlda *odesc, int fetch_type, long val, int icnt, struct sqlvar_struct *ibind, struct sqlda *idesc, int chkind );
extern struct _sqcursor *_iqnprep(char *name, char *stmt);
extern int _iqrollback(void);
extern int _iqsetdesc(char *desc_name, int sqlvar_num, struct hostvar_struct *hosttab, int xopen_flg);
extern void _iqseterr(int sys_errno);
extern int _iqsftch(struct _sqcursor *cursor, struct sqlda *idesc, struct sqlda *odesc, int sysdesc, _FetchSpec *fetchspec, char *odesc_name);
extern int _iqslct(struct _sqcursor *cursor, char **cmdtxt, int icnt, struct sqlvar_struct *ibind, int ocnt, struct sqlvar_struct *obind, int chkind);
extern int _iqstmnt(_SQSTMT *scb, char **cmdtxt, int icnt, struct sqlvar_struct *ibind, struct value *ivalues);
extern void _iqstop(void);
extern int _iqxecute(struct _sqcursor *cursor, int icnt, struct sqlvar_struct *ibind, struct sqlda *idesc, struct value *ivalues);
extern void _iqconnect(int conn_kw, char *dbenv, char *conn_name, char *username, char *passwd, int concur_tx);
extern void _iqdisconnect(int conn_kw, char *conn_name, int flag, int from_reassoc);
extern void _iqsetconnect(int conn_kw, char *conn_name, int dormant);
#ifdef _REENTRANT
extern long * ifx_sqlcode(void);
extern char * ifx_sqlstate(void);
extern struct sqlca_s * ifx_sqlca(void);
#endif /* _REENTRANT */
#if ESQLC_EFFVERSION >= 900
/* Type for casting dynamic SQL types to LVARCHAR */
typedef void *Lvarchar;
#endif
/* ESQL/C Features */
/* The ESQL/C compiler versions are defined in esqlinfo.h by autoconf */
/* Variable cursors and stored procedures were introduced in 5.00 */
/* They are essentially always available in ESQL/C. */
/* Some (old) versions of XPS did not support stored procedures. */
/* Some (old) versions of XPS did not support BYTE and TEXT blobs. */
#define ESQLC_STORED_PROCEDURES 1
#define ESQLC_VARIABLE_CURSORS 1
#if ESQLC_EFFVERSION >= 600
#define ESQLC_CONNECT 1
#define ESQLC_SQLSTATE 1
#define ESQLC_RGETLMSG 1
examples/fetchscroll.pl view on Meta::CPAN
#!/usr/bin/perl -w
#
# @(#)$Id: fetchscroll.pl,v 1.4 2003/01/13 23:58:53 jleffler Exp $
#
# Simulate proposed scroll cursor support for Perl DBI
use strict;
use DBI;
use Carp;
my $debug = 0;
sub fetchrow_scroll_arrayref($$$$)
{
my($sth, $ctl, $key, $val) = @_;
examples/fetchscroll.pl view on Meta::CPAN
print " Fetch Tests $ttest";
print " (with $tfail failures)" if $tfail;
print "\n";
print (($tfail) ? "== FAILED ==\n" : "== PASSED ==\n");
$dbh->do(qq{drop table $tab});
$dbh->disconnect;
__END__
=head2 "Scroll cursors"
$sth->setattr(SQL_STMT_CURSOR_ATTR, SQL_CURSOR_SCROLL);
$sth->execute([@vals]);
The $sth->execute method is the familiar function, but the $sth->setattr
function (which is modelled after the ODBC function SQLStmtSetAttr()) is
new. If the DBMS being accessed by the driver does not support scroll
cursors (determined via $dbh->getinfo(...)) or the driver has not
implemented the interface to the feature, then you will get a default
implementation (emulation) of scroll cursors shown above. When
$sth->setattr() is called, it ensures that $sth->execute creates a
scroll cursor for the statement, which must be one that returns values
($sth->{NUM_OF_FIELDS} > 0).
$row = $sth->fetchrow_scroll_arrayref($move, $offset);
@row = $sth->fetchrow_scroll_array($move, $offset);
$ref = $sth->fetchrow_scroll_hashref($move, $offset);
These methods can be used to fetch rows of data via a statement that was
executed with $sth->execute_scroll. (Optionally: if the $move is
'next', then the fetchrow_scroll_* methods map to the corresponding
regular fetchrow_* method;any $move other than 'next' is rejected.) The
examples/x12cgi_noform.pl view on Meta::CPAN
# and activating an automatic exit and notification on errors
my $dbh=DBI->connect('dbi:Informix:stores', '', '',
{ 'PrintError'=>1, 'RaiseError'=>1 }
) or die "Could not connect, stopped\n";
# prepare the select statement
my $st_text = 'SELECT * FROM customer';
my $sth = $dbh->prepare( $st_text ) or
die "Could not prepare $st_text; stopped";
# open the cursor
$sth->execute() or die "Failed to open cursor for SELECT statment\n";
# bind columns to variables
my (
$customer_num, $fname, $lname, $company,
$address1, $address2, $city, $state, $zipcode, $phone
);
my @cols = ( \$customer_num, \$fname, \$lname, \$company,
\$address1, \$address2, \$city, \$state, \$zipcode, \$phone );
$sth->bind_columns(undef, @cols);
examples/x12cgi_noform.pl view on Meta::CPAN
$zipcode,
$phone
] )
);
}
# close the table
print "\n</TABLE>\n";
# This free statement is not strictly necessary in DBD::Informix
# 0.60 as cursors are auto-finished when the last row is fetched.
# It is needed in earlier versions; it does no damage in later ones.
$sth->finish();
# disconnect from the server
$dbh->disconnect();
# finish the html page
print $query->end_html();
}
examples/x13cgi_noform.pl view on Meta::CPAN
# and activating an automatic exit and notification on errors
my $dbh=DBI->connect('dbi:Informix:stores', '', '',
{ 'PrintError'=>1, 'RaiseError'=>1 }
) or die "Could not connect, stopped\n";
# prepare the select statement
my $st_text = 'SELECT * FROM Customer ORDER BY Lname, Fname, Company, Customer_num';
my $sth = $dbh->prepare( $st_text ) or
die "Could not prepare $st_text; stopped";
# open the cursor
$sth->execute() or die "Failed to open cursor for SELECT statment\n";
# bind columns to variables
my (
$customer_num, $fname, $lname, $company,
$address1, $address2, $city, $state, $zipcode, $phone
);
my @cols = ( \$customer_num, \$fname, \$lname, \$company,
\$address1, \$address2, \$city, \$state, \$zipcode, \$phone );
$sth->bind_columns(undef, @cols);
examples/x13cgi_noform.pl view on Meta::CPAN
$addr,
$phone
] )
);
}
# close the table
print "\n</TABLE>\n";
# This free statement is not strictly necessary in DBD::Informix
# 0.60 as cursors are auto-finished when the last row is fetched.
# It is needed in earlier versions; it does no damage in later ones.
$sth->finish();
# disconnect from the server
$dbh->disconnect();
print "\n<HR>\n";
sub ordinal
{
examples/x14cgi_form.pl view on Meta::CPAN
# connect to the database, instantiating a database handler
# and activating an automatic exit and notification on errors
my $dbh = DBI->connect('dbi:Informix:stores', '', '',
{ 'PrintError'=>1, 'RaiseError'=>1 })
or die "Could not connect, stopped\n";
# prepare the select statement
my $sth = $dbh->prepare( $query ) or
die "Could not prepare $query; stopped";
# open the cursor
$sth->execute() or die "Failed to open cursor for SELECT statment\n";
# bind columns to variables
my ($customer_num, $fname, $lname, $company);
my ($address1, $address2, $city, $state, $zipcode, $phone);
my @cols = ( \$customer_num, \$fname, \$lname, \$company,
\$address1, \$address2, \$city, \$state, \$zipcode, \$phone );
$sth->bind_columns(undef, @cols);
# chop blanks from char columns
$sth->{ ChopBlanks } = 1;
examples/x14cgi_form.pl view on Meta::CPAN
$addr,
$phone
] )
);
}
# close the table
print "\n</TABLE>\n";
# This free statement is not strictly necessary in DBD::Informix
# 0.60 as cursors are auto-finished when the last row is fetched.
# It is needed in earlier versions; it does no damage in later ones.
$sth->finish();
# disconnect from the server
$dbh->disconnect();
print $q->hr;
sub ordinal
{
examples/x15cgi_form.pl view on Meta::CPAN
$pad = " AND";
}
$query .= ' ORDER BY Lname, Fname, Company, Customer_num';
print $query, $q->hr;
# prepare the select statement
my $sth = $dbh->prepare( $query ) or
die "Could not prepare $query; stopped";
# open the cursor
$sth->execute() or die "Failed to open cursor for SELECT statment\n";
# bind columns to variables
my ($customer_num, $fname, $lname, $company);
my ($address1, $address2, $city, $state, $zipcode, $phone);
my @cols = ( \$customer_num, \$fname, \$lname, \$company,
\$address1, \$address2, \$city, \$state, \$zipcode, \$phone );
$sth->bind_columns(undef, @cols);
# chop blanks from char columns
$sth->{ ChopBlanks } = 1;
examples/x15cgi_form.pl view on Meta::CPAN
}
# close the table
print "\n</TABLE>\n" if $count > 0;
print "\n",
$q->center($q->font({-color=>"#C04040"}, "No customers selected")),
"\n"
if $count == 0;
# This free statement is not strictly necessary in DBD::Informix
# 0.60 as cursors are auto-finished when the last row is fetched.
# It is needed in earlier versions; it does no damage in later ones.
$sth->finish();
# disconnect from the server
$dbh->disconnect();
print $q->hr;
sub ordinal
{
lib/Bundle/DBD/Informix.pm view on Meta::CPAN
If you've not previously used the CPAN module to install any
bundles, you will be interrogated during its setup phase.
But when you've done it once, it remembers what you told it.
You could start by running:
C<perl -MCPAN -e 'install Bundle::CPAN'>
C<perl -MCPAN -e 'install Bundle::libnet'>
C<perl -MCPAN -e 'install Bundle::LWP'>
DBD::Informix uses the Time::HiRes module for timing insert cursors.
=head1 SEE ALSO
Bundle::DBI
=head1 AUTHOR
Jonathan Leffler E<lt>F<jleffler@google.com>E<gt>
=head1 THANKS
lib/DBD/Informix/Summary.pm view on Meta::CPAN
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.
lib/DBD/Informix/TestHarness.pm view on Meta::CPAN
It takes a database handle, and uses the environment variables
DBD_INFORMIX_NO_SBSPACE and DBD_INFORMIX_SBSPACE to determine whether
smart blobs should be tested.
The return value is either an empty string (do not test smart blobs) or
the name of a valid smart blob space.
=head2 Using validate_unordered_unique_data
The C<validate_unordered_unique_data> function is used to ensure that
exactly the correct data is returned from a cursor-like statement handle
which has already had the $sth->execute method executed on it.
The data in $val is a hash indexed by the key value containing the
expected values for each column corresponding to the key value:-
&validate_unordered_unique_data($sth, $keycol, \%expected);
&validate_unordered_unique_data($sth, 'c1',
{
'c1-value1' => { 'c1' => 'c1-value1', 'c2' => 'c2-value1', 'c3' => 'c3-value1' },
t/t00basic.t view on Meta::CPAN
stmt_retest($dbh, $stmt2, 0);
my $stmt3 = "INSERT INTO $testtable VALUES(1, 'Alligator Descartes')";
stmt_test($dbh, $stmt3, 0);
my $stmt4 = "DELETE FROM $testtable WHERE id = 1";
stmt_test($dbh, $stmt4, 0);
# Test SELECT of empty data set
my $stmt5 = "SELECT * FROM $testtable WHERE id = 1";
stmt_note("# Testing: \$cursor = \$dbh->prepare('$stmt5')\n");
my $cursor;
stmt_fail() unless ($cursor = $dbh->prepare($stmt5));
stmt_ok();
# Print statement...
stmt_note("# Statement: $cursor->{Statement}\n");
stmt_note("# Testing: \$cursor->execute\n");
stmt_fail() unless ($cursor->execute);
stmt_ok();
stmt_note("# Statement: $cursor->{Statement}\n");
stmt_note("# Testing: \$cursor->fetch\n");
my $i = 0;
my @row;
while ((@row = $cursor->fetch) and $#row > 0)
{
$i++;
stmt_note("# Row $i: $row[0] => $row[1]\n");
stmt_note("# FETCH succeeded but should have failed!\n");
stmt_fail();
}
stmt_fail() unless ($#row == 0);
stmt_note("# OK (nothing found)\n");
stmt_ok(0);
print_sqlca($cursor);
stmt_note("# Testing: \$cursor->finish\n");
stmt_fail() unless ($cursor->finish);
stmt_ok(0);
# FREE the cursor and asociated data
undef $cursor;
# Insert some data
stmt_retest($dbh, $stmt3, 0);
# Verify that inserted data can be returned
stmt_note("# Re-testing: \$cursor = \$dbh->prepare('$stmt5')\n");
stmt_fail() unless ($cursor = $dbh->prepare($stmt5));
stmt_ok(0);
stmt_note("# Re-testing: \$cursor->execute\n");
stmt_fail() unless ($cursor->execute);
stmt_ok(0);
stmt_note("# Re-testing: \$cursor->fetch\n");
# Fetch returns a reference to an array!
my $j = 0;
my $ref;
while ($ref = $cursor->fetch)
{
$j++;
@row = @{$ref};
# Verify returned data!
my @exp = (1, "Alligator Descartes");
stmt_note("# Values returned: ", $#row + 1, "\n");
for ($i = 0; $i <= $#row; $i++)
{
stmt_note("# Row value $i: $row[$i]\n");
stmt_fail("Incorrect value returned: got $row[$i]; wanted $exp[$i]\n")
unless ($exp[$i] eq $row[$i]);
}
}
stmt_fail("FAIL: $j rows selected when 1 expected\n") unless ($j == 1);
# Verify data attributes!
my @type = @{$cursor->{TYPE}};
for ($i = 0; $i <= $#type; $i++) { print ("# Type $i: $type[$i]\n"); }
my @name = @{$cursor->{NAME}};
for ($i = 0; $i <= $#name; $i++) { print ("# Name $i: <<$name[$i]>>\n"); }
my @null = @{$cursor->{NULLABLE}};
for ($i = 0; $i <= $#null; $i++) { print ("# Nullable $i: $null[$i]\n"); }
my @prec = @{$cursor->{PRECISION}};
for ($i = 0; $i <= $#prec; $i++) { print ("# Precision $i: $prec[$i]\n"); }
my @scal = @{$cursor->{SCALE}};
for ($i = 0; $i <= $#scal; $i++) { print ("# Scale $i: $scal[$i]\n"); }
my $nfld = $cursor->{NUM_OF_FIELDS};
my $nbnd = $cursor->{NUM_OF_PARAMS};
print("# Number of Columns: $nfld; Number of Parameters: $nbnd\n");
stmt_note("# Re-testing: \$cursor->finish\n");
stmt_fail() unless ($cursor->finish);
stmt_ok(0);
# FREE the cursor and asociated data
undef $cursor;
my $stmt6 = "UPDATE $testtable SET id = 2 WHERE name = 'Alligator Descartes'";
stmt_retest($dbh, $stmt6, 0);
my $stmt7 = "INSERT INTO $testtable VALUES(1, 'Jonathan Leffler')";
stmt_test($dbh, $stmt7, 0);
sub select_all
{
my ($dbh, $exp1) = @_;
my (%exp2) = %{$exp1}; # Associative array of numbers (keys) and names
my (@row, $i); # Local variables
stmt_note("# Checking Updated Data\n");
my $stmt8 = "SELECT * FROM $testtable ORDER BY id";
stmt_note("# Re-testing: \$cursor = \$dbh->prepare('$stmt8')\n");
stmt_fail() unless ($cursor = $dbh->prepare($stmt8));
stmt_ok(0);
stmt_note("# Re-testing: \$cursor->execute\n");
stmt_fail() unless ($cursor->execute);
stmt_ok(0);
stmt_note("# Testing: \$cursor->fetchrow iteratively\n");
$i = 1;
while (@row = $cursor->fetchrow)
{
stmt_note("# Row $i: $row[0] => $row[1]\n");
if ($row[1] eq $exp2{$row[0]})
{
stmt_ok(0);
}
else
{
stmt_note("# Wrong value:\n");
stmt_note("# -- Got <<$row[1]>>\n");
stmt_note("# -- Wanted <<$exp2{$row[0]}>>\n");
stmt_fail();
}
$i++;
}
stmt_note("# Re-testing: \$cursor->finish\n");
stmt_fail() unless ($cursor->finish);
stmt_ok(0);
# Free cursor referencing the table...
undef $cursor;
}
select_all($dbh, {
1 => 'Jonathan Leffler',
2 => 'Alligator Descartes',
});
# Now the table is dropped.
stmt_retest($dbh, $stmt1, 0);
t/t01stproc.t view on Meta::CPAN
-- Sometimes known as ndelta_eq()
RETURNING INTEGER;
IF (val1 = val2) THEN RETURN 1; END IF;
IF NOT (val1 = val2) THEN RETURN 0; END IF;
RETURN NULL;
END PROCEDURE;
};
stmt_test($dbh, $stmt11, 0);
my $stmt12 = "EXECUTE PROCEDURE $procname(23.00, 23)";
stmt_note("# Testing: \$cursor = \$dbh->prepare('$stmt12')\n");
my $cursor;
stmt_fail() unless ($cursor = $dbh->prepare($stmt12));
stmt_ok(0);
stmt_note("# Re-testing: \$cursor->execute\n");
stmt_fail() unless ($cursor->execute);
stmt_ok(0);
stmt_note("# Re-testing: \$cursor->fetchrow\n");
my @row;
stmt_fail() unless (@row = $cursor->fetchrow);
stmt_ok(0);
stmt_note("# Values returned/expected: ", $#row + 1, "/1\n");
my $i;
for ($i = 0; $i <= $#row; $i++)
{
stmt_note("# Row value $i: $row[$i]\n");
die "Unexpected value returned\n" unless $row[$i] == 1;
}
stmt_note("# Re-testing: \$cursor->finish\n");
stmt_fail() unless ($cursor->finish);
stmt_ok(0);
# FREE the cursor and asociated data
undef $cursor;
# Remove stored procedure
stmt_retest($dbh, $stmt10, 0);
}
stmt_note("# Testing: \$dbh->disconnect()\n");
stmt_fail() unless ($dbh->disconnect);
stmt_ok(0);
all_ok;
t/t24mcurs.t view on Meta::CPAN
#!/usr/bin/perl
#
# @(#)$Id: t24mcurs.t,v 2014.1 2014/04/21 06:38:37 jleffler Exp $
#
# Tests multiple simultaneous cursors being open
#
# Copyright 1996 Hermetica. Written by Alligator Descartes <descarte@hermetica.com>
# Copyright 1996-99 Jonathan Leffler
# Copyright 2000 Informix Software Inc
# Copyright 2002-03 IBM
# Copyright 2013-14 Jonathan Leffler
use DBD::Informix::TestHarness;
use strict;
use warnings;
t/t24mcurs.t view on Meta::CPAN
my $sth1 = $dbh->prepare("SELECT id1, id2, id3, id4, name FROM $tablename1");
stmt_fail() if (!defined $sth1);
stmt_ok(0);
# Prepare the second SELECT statement
stmt_note("# 2nd SELECT\n");
my $sth2 = $dbh->prepare("SELECT id, name FROM $tablename2");
stmt_fail() if (!defined $sth2);
stmt_ok(0);
# Open the first cursor
stmt_note("# Open 1st cursor\n");
stmt_fail() unless $sth1->execute;
stmt_ok(0);
# Open the second cursor
stmt_note("# Open 2nd cursor\n");
stmt_fail() unless $sth2->execute;
stmt_ok(0);
my @row1;
my @row2;
while (@row1 = $sth1->fetchrow)
{
print "# Row1: @row1\n";
stmt_ok(0);
@row2 = $sth2->fetchrow;
if (@row2)
{
print "# Row2: @row2\n";
stmt_ok(0);
}
}
# Close the cursors
stmt_note("# Close 1st cursor\n");
stmt_fail() unless $sth1->finish;
stmt_ok(0);
undef $sth1;
stmt_note("# Close 2nd cursor\n");
stmt_fail() unless $sth2->finish;
stmt_ok(0);
undef $sth2;
$dbh->disconnect;
all_ok();
t/t30update.t view on Meta::CPAN
# ----------------------------------------------------------------------
sub select_all
{
my ($exp1) = @_; # Reference to associative array
my (%exp2) = %{$exp1}; # Associative array of numbers (keys) and names
my (@data); # Array dereferenced from %exp{1} etc.
my (@row, $i); # Local variables
my ($cursor);
stmt_note("# Checking Updated Data\n");
my($stmt8) = "SELECT * FROM $testtable ORDER BY id";
stmt_note("# Testing: \$cursor = \$dbh->prepare('$stmt8')\n");
stmt_fail() unless ($cursor = $dbh->prepare($stmt8));
stmt_ok(0);
stmt_note("# Testing: \$cursor->execute\n");
stmt_fail() unless ($cursor->execute);
stmt_ok(0);
stmt_note("# Testing: \$cursor->fetchrow iteratively\n");
$i = 1;
while (@row = $cursor->fetchrow)
{
stmt_note("# Row $i: $row[0] => '$row[1]', '$row[2]', $row[3]\n");
my($ref_arr) = $exp2{$row[0]};
@data = @{$ref_arr};
stmt_note("# Want $i: '$data[0]', '$data[1]', |$data[2]|\n");
if ($row[1] eq $data[0] && $row[2] eq $data[1] && $row[3] == $data[2])
{
stmt_ok(0);
}
else
t/t30update.t view on Meta::CPAN
if ($row[3] != $data[2])
{
stmt_note("# -- Got <<$row[3]>>\n");
stmt_note("# -- Wanted <<$data[2]>>\n");
}
stmt_fail();
}
$i++;
}
stmt_note("# Re-testing: \$cursor->finish\n");
stmt_fail() unless ($cursor->finish);
stmt_ok(0);
# Free cursor referencing the table...
undef $cursor;
}
t/t33holdcurs.t view on Meta::CPAN
#!/usr/bin/perl
#
# @(#)$Id: t33holdcurs.t,v 2014.1 2014/04/21 06:38:37 jleffler Exp $
#
# Copyright 1996-99,2004 Jonathan Leffler
# Copyright 1999 Bill Rothanburg
# Copyright 2002-03 IBM
# Copyright 2004-14 Jonathan Leffler
#
# Tests hold cursors in transactions
use DBD::Informix::TestHarness;
use strict;
use warnings;
my $dbh = connect_to_test_database();
if ($dbh->{ix_LoggedDatabase} == 0)
{
stmt_note("1..1\n");
t/t33holdcurs.t view on Meta::CPAN
my $sth = $dbh->prepare("SELECT id, name FROM $tablename1");
stmt_fail() if (!defined $sth);
stmt_ok(0);
stmt_note("# Fetch Hold Attrib\n");
my $hold = $sth->{ix_CursorWithHold};
stmt_fail() unless defined $hold;
print "# ix_CursorWithHold = $hold\n";
stmt_ok(0);
# Open the first cursor
stmt_note("# Open cursor\n");
stmt_fail() unless $sth->execute;
stmt_ok(0);
my @row1 = $sth->fetchrow;
if (@row1)
{
print "# Row1: @row1\n";
stmt_ok(0);
}
else
t/t33holdcurs.t view on Meta::CPAN
print "# $DBI::errstr\n";
stmt_ok(0);
}
else
{
print "# Row2: defined (incorrect behaviour - should have failed)\n";
print "# Row2: @row2\n";
stmt_fail();
}
# Close the cursors
stmt_note("# Close cursor\n");
stmt_fail() unless $sth->finish;
stmt_ok(0);
undef $sth;
# With CURSOR WITH HOLD - Should pass at second fetch
#
# Prepare the SELECT statement
stmt_note("# SELECT: ix_CursorWithHold = True\n");
$sth = $dbh->prepare("SELECT id, name FROM $tablename1", {'ix_CursorWithHold' => 1});
stmt_fail() if (!defined $sth);
stmt_ok(0);
stmt_note("# Fetch Hold Attrib\n");
$hold = $sth->{ix_CursorWithHold};
stmt_fail() unless defined $hold;
print "# ix_CursorWithHold = $hold\n";
stmt_ok(0);
# Open the first cursor
stmt_note("# Open cursor\n");
stmt_fail() unless $sth->execute;
stmt_ok(0);
@row1 = $sth->fetchrow;
if (@row1)
{
print "# Row1: @row1\n";
stmt_ok(0);
}
else
t/t33holdcurs.t view on Meta::CPAN
{
print "# Row1: @row2\n";
stmt_ok(0);
}
else
{
print "# Row2: undefined (unexpected behaviour)\n";
stmt_fail();
}
# Close the cursors
stmt_note("# Close cursor\n");
stmt_fail() unless $sth->finish;
stmt_ok(0);
undef $sth;
$dbh->disconnect;
all_ok();
t/t35cursor.t view on Meta::CPAN
#!/usr/bin/perl
#
# @(#)$Id: t35cursor.t,v 2014.1 2014/04/21 06:38:37 jleffler Exp $
#
# Test handling of cursors and cursor states
#
# Copyright 2002-03 IBM
# Copyright 2013-14 Jonathan Leffler
use strict;
use warnings;
use DBD::Informix::TestHarness;
# Test install...
my ($dbh) = connect_to_test_database;
stmt_note "1..18\n";
stmt_ok;
my ($table) = "dbd_ix_cursorstate";
# Create table for testing
stmt_test $dbh, qq{
CREATE TEMP TABLE $table
(
Col01 SERIAL(1000) NOT NULL,
Col02 CHAR(20) NOT NULL,
Col03 INTEGER NOT NULL,
Col04 DATETIME YEAR TO FRACTION(5) NOT NULL,
Col05 DECIMAL(10,9) NOT NULL
t/t35cursor.t view on Meta::CPAN
}
{
my ($sth) = $dbh->prepare($select) or stmt_fail;
$sth->execute or stmt_fail;
stmt_ok;
my ($row) = $sth->fetchrow_arrayref or stmt_fail;
stmt_ok;
# Implicitly undefine open cursor - no error
}
{
my ($sth) = $dbh->prepare($insert);
stmt_fail unless $sth;
stmt_ok;
# Finish a non-cursory statement - no error
$sth->finish or stmt_fail;
stmt_ok;
# Implicitly undefine prepard statement - no error
}
all_ok();
t/t44txansi.t view on Meta::CPAN
# @(#)$Id: t44txansi.t,v 2014.1 2014/04/21 06:38:37 jleffler Exp $
#
# Test AutoCommit On for DBD::Informix
#
# Copyright 1996-99 Jonathan Leffler
# Copyright 2000 Informix Software Inc
# Copyright 2002-03 IBM
# Copyright 2013-14 Jonathan Leffler
#
# AutoCommit On => Each statement is a self-contained transaction
# Ensure MODE ANSI databases use cursors WITH HOLD
use DBD::Informix::TestHarness;
use strict;
use warnings;
# Test install...
my $dbh = connect_to_test_database();
if (!$dbh->{ix_ModeAnsiDatabase})
{
t/t44txansi.t view on Meta::CPAN
my($pad, $i) = ("#-# ", 0);
for ($i = 0; $i < @row; $i++)
{
stmt_note("$pad$row[$i]");
$pad = " :: ";
}
stmt_note("\n");
}
my $fetch1;
# Prepare, open and fetch one row from a cursor
my $sth;
stmt_fail unless ($sth = $dbh->prepare($select));
stmt_fail unless ($sth->execute);
stmt_fail unless ($fetch1 = $sth->fetch);
print_row $fetch1;
stmt_ok;
# Insert another two rows of data (committing those rows)
stmt_test $dbh, $insert01;
$insert01 =~ s/$tag2/$tag1/;
stmt_test $dbh, $insert01;
my $fetch2;
# Check that the cursor still works!
while ($fetch2 = $sth->fetch)
{
print_row $fetch2;
}
stmt_fail if ($sth->{ix_sqlcode} < 0);
stmt_ok;
# Check that there is some data
$sel->execute ? validate_unordered_unique_data($sel, 'col01', $res3) : stmt_nok;
t/t65updcur.t view on Meta::CPAN
#!/usr/bin/perl
#
# @(#)$Id: t65updcur.t,v 2014.1 2014/04/21 06:38:37 jleffler Exp $
#
# Test $sth->{CursorName} and cursors FOR UPDATE for DBD::Informix
#
# Copyright 1997-99 Jonathan Leffler
# Copyright 2000 Informix Software Inc
# Copyright 2002-03 IBM
# Copyright 2013-14 Jonathan Leffler
use DBD::Informix::TestHarness;
use strict;
use warnings;
t/t66insert.t view on Meta::CPAN
)
};
# Create table for testing
stmt_test $dbh, $create;
my($rows) = 1000;
# Insert a row of values.
{
stmt_note("# Inserting $rows rows without INSERT cursor\n");
my($sth) = $dbh->prepare("INSERT INTO $table VALUES(0, ?, CURRENT, ?)" );
stmt_fail() unless $sth;
stmt_ok;
my($t0) = [gettimeofday];
for my $i (0 .. $rows)
{
stmt_fail() unless ($sth->execute('FOOBARBAZ', $i + 2.8128))
}
my($elapsed) = tv_interval ( $t0, [gettimeofday]);
my($note) = sprintf("without INSERT cursor: $rows in %.3f seconds (%.5f seconds/row)\n",
$elapsed, $elapsed/$rows);
stmt_note($note);
stmt_ok;
#print_sqlca $sth;
my($rows) = $sth->rows;
stmt_comment("ROWS = $rows\n");
$sth->finish;
$dbh->commit if $dbh->{ix_LogggedDatabase};
}
$dbh->do("DROP TABLE $table") or stmt_fail;
$dbh->do($create) or stmt_fail;
{
stmt_note("# Inserting $rows rows with INSERT cursor\n");
my($sth) = $dbh->prepare("INSERT INTO $table VALUES(0, ?, CURRENT, ?)", { ix_InsertCursor => 1 } );
stmt_fail() unless $sth;
stmt_ok;
my($t0) = [gettimeofday];
for my $i (0 .. $rows)
{
stmt_fail() unless ($sth->execute('FOOBARBAZ', $i + 2.8128))
}
my($elapsed) = tv_interval ( $t0, [gettimeofday]);
my($note) = sprintf("# with INSERT cursor: $rows in %.3f seconds (%.5f seconds/row)\n",
$elapsed, $elapsed/$rows);
stmt_note($note);
stmt_ok;
#print_sqlca $sth;
my($rows) = $sth->rows;
stmt_comment("ROWS = $rows");
$sth->finish;
$dbh->commit if $dbh->{ix_LogggedDatabase};
}
$dbh->do("DROP TABLE $table") or stmt_fail;
$dbh->do($create) or stmt_fail;
{
$rows /= 10;
stmt_note("# Inserting $rows rows without INSERT cursor\n");
my($sth) = $dbh->prepare("INSERT INTO $table VALUES(0, ?, CURRENT, ?)", { ix_InsertCursor => 0 } );
stmt_fail() unless $sth;
stmt_ok;
my($t0) = [gettimeofday];
for my $i (0 .. $rows)
{
stmt_fail() unless ($sth->execute('FOOBARBAZ', $i + 2.8128));
}
my($elapsed) = tv_interval ( $t0, [gettimeofday]);
my($note) = sprintf("# without INSERT cursor: $rows in %.3f seconds (%.5f seconds/row)\n",
$elapsed, $elapsed/$rows);
stmt_note($note);
stmt_ok;
my($rows) = $sth->rows;
stmt_comment("ROWS = $rows");
$sth->finish;
$dbh->commit if $dbh->{ix_LogggedDatabase};
}
t/t66insert.t view on Meta::CPAN
{
if ($dbh->{ix_LoggedDatabase})
{
stmt_note("# Checking for warning if AutoCommit On in Logged DB\n");
$dbh->{AutoCommit} = 1;
my($warning);
local $SIG{__WARN__} = sub {$warning = $_[0]};
my($sth) = $dbh->prepare("INSERT INTO $table VALUES(0, ?, CURRENT, ?)", { ix_InsertCursor => 1 } );
stmt_fail() unless $sth;
stmt_note("# $warning");
stmt_fail() unless $warning =~ /insert cursor ineffective with AutoCommit enabled/;
}
stmt_ok;
}
$dbh->disconnect or stmt_fail;
all_ok();
( run in 0.498 second using v1.01-cache-2.11-cpan-4d50c553e7e )