$res = The 3rd argument to &dbix_do specifies that a cursor is to be created and that the sql is to be executed for each element of the largest array (the arrays should normally be the same length). If the 3rd argument is "2", then if there is a one-to-many relationship to the key, then all matching records for each fetch will be returned (so that the resulting arrays may be larger than the array(s) of key values). If "1", then exactly 1 record will be returned for each element in the array(s) being used as keys in the "where" clause). NOTE: The third argument can also be given as "{-interpolate => 1}" or "{-interpolate => 2}".
#INSERT A NEW RECORD.
($res = &dbix_do1($dB, 'insert into test values (?, ?, ?, ?)', 'value1', $value2, $value3, 'value4')) || die ('Could not insert record ('.&dbix_err($dB).'.'.&dbix_errstr($dB).')!');
#ARRAY INSERT.
@array1 = (10,20,30,40,50);
@array2 = (qw(cows pigs foxes goats horses));
($res = &dbix_do($dB, 'insert into test values (/:array1, /:array2)', 1))
|| die ('Could not insert arrays ('.&dbix_err($dB).'.'.&dbix_errstr($dB).')!');
NOTE: The third argument can also be given as "{-interpolate => 1}" or "{-interpolate => 2}". You can also specify "{-commit => 1}" to force a commit.
#FETCH BACK THE SEQUENCE / AUTO NUMBER FOR LAST INSERT.
$number = &dbix_fetchseq($dB, "keyfieldname", ["sequencename", ["tablename"]]);
This returns the sequence or auto-number assigned to the last key field inserted into a table in a database-independent way. You usually will call this function immediately after inserting a record with a sequence / autonumber field to get the generated key value, for example to create an index record referencing that key value. This currently supports Oracle, Sprite, and MySQL. There is also a default method of fetching a descending list of values and returning the 1st one. This should work with most other databases, but is less efficient. You can add code for your particular database, if it has a more efficient way of supporting it, grep the comments for "ADD CODE".
#COMMIT THE TRANSACTION!&dbix_commit($dB);
#CURSORS.
$sqlstr = <<END_SQL;
update test set field1 = ?
where field2 = ?
END_SQL
$csr = &dbix_open($dB, $sqlstr);
die "Could not open ($sqlstr)!" unless ($csr);
#AN OPTIONAL 3RD ARGUMENT TO dbix_open CAUSES $dbh->execute() TO BE CALLED IN ADDITION TO $dbh->prepare(). IF THE RETURN VARIABLE IS A LIST, A SECOND VALUE IS RETURNED REPRESENTING THE RESULT RETURNED BY $dbh->execute. ALSO, IF USED FOR A SELECT STATEMENT, VARIABLES CAN BE BOUND JUST AS FOR dbix_select (see ":f1" and ":f2" in prev. examples).
for (my $i=0;$i<=$#f1;$i++)
{
&dbix_bind($dB, $csr, $i, $f1[$i]);
}
&dbix_close($csr);
#AN EXAMPLE OF SELECT USING CURSORS.
$sqlstr = <<END_SQL;
select field3, field4
from test2
where field1 = ?
END_SQL
$csr = &dbix_open($dB, $sqlstr);
die "Could not open ($sqlstr)!" unless ($csr);
for (my $i=0;$i<=$#f1;$i++)
{
&dbix_bind($dB, $csr, $f1[$i]);
while (($f3, $f4) = &dbix_fetch($dB, $csr))
{
print "-For record# $i (key $f1[$i]): field3=$f3, field4=$f4\n";
}
}
&dbix_close($csr);
#DISCONNECT FROM THE DATABASE.
&dbix_disconnect();
#FETCHING SEQUENCES.
DBIx provides a database-independent way to fetch the most recent and next sequence value for sequence / autonumbering.
my $nextSeq = &dbix_fetchnextseq($dB [, "Seq_Name"]);
This can be called anytime and returns the next value of sequence "Seq_Name". For Sprite and Oracle, this is accomplished by doing a "SELECT <Seq_Name>.NEXTVAL from DUAL". Currently only Oracle, Sprite, and MySQL are properly supported, but one can add code for their own database or send me a patch! Or, one can rely on the failsafe option (which works if either your database isn't supported or if "Seq_Name" is not specified, in which case, the first time this function is called, a file named "$ENV{HOME}/.dbixseq" is created with a sequence value of 1, which is returned. Subsequent calls return the next integer and save it to this file, emulating a sequence.
my $lastSeq = &dbix_fetchseq($dB [, "SEQ_FIELD_NAME"[, "Seq_Name"[, "Table_Name"]]]);
This is called after an insert and should return the value of the last sequence specified in that insert (used to get the key of the last record inserted) when using sequences or the autonumbering feature of a database. Only the 1st argument is required. The other arguments are useful if one is not sure their database is supported or to act as a "catch-all" option if a value is not obtainable. This is done by doing a fetch from the table: "Table_Name" for a descending list of values for the field specified by "SEQ_FIELD_NAME" and returns the 1st value returned. "Seq_Name" defaults to the last sequence used in Oracle databases, but can be overridden here.
CHANGES
7/02: Added "dbix_noplaceholders" option to provide seamless support of databases which do not support "?" placeholders, ie. M$ SqlServer via FreeTDS/DBD::Sybase. This allows the use of placeholders and converts them automatically to the appropriate values before passing the query to the database or logging it. ie.: my $dB = &dbix_connect($connectstring, {dbix_noplaceholders => 1});
7/02: Added ability to specify environment variables ie. for Oracle, by prepending them with "dbix_env_" in the attributes list. ie.: my $dB = &dbix_connect($connectstring, {dbix_env_oracle_home => '/var/oracle'});
7/02: Added ability to skip the "dbix_set_databasetype" call by simply specifying the database type in the connect string, ie.: my $dB = &dbix_connect('Oracle:mydatabase,user,password');
7/02: Added ability to pass raw connect string, ie.: my $dB = &dbix_connect('=dbi:mysql:mydatabase,user,password');
9/03: Added dbix_fetchseq() function to fetch last sequence / autonumber key generated from the previous insert in a database-independent manner.
TODO
Make a more object-oriented version.
KNOWN BUGS
-none (yet)-
SEE ALSO
DBI(3), perl(1)