JDBIx - Object-oriented higher-level DBI interface for select into arrays, placeholders, sequences, etc.
This module is Copyright (C) 2000-2016 by Jim Turner Email: turnerjw784@yahoo.com All rights reserved.
You may distribute this module under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
#!/usr/bin/perl use strict; use JDBIx; &jdbix_setlog('/tmp/dbixlogt.txt'); #OPTIONALLY, SET UP A LOG FILE. #CONNECT TO A DATABASE: my $dbh = new JDBIx('dbtype:dbname,dbuser,dbpswd',{}) or die ("-no login: err=" . &jdbix_err() . ':' . &jdbix_errstr . "=\n"); $dbh->package(__PACKAGE__); #UNLESS PACKAGE IS "main::". #DO A SIMPLE SELECT, STORING THE RESULTS INTO @f1 AND @f2. our (@f1, @f2); #MUST BE PACKAGE, NOT "MY" VARIABLES: my $res = $dbh->select('select field1, field2 into :f1, :f2 from test') or warn ('Could not do select (' . $dbh->err() . '.' . $dbh->errstr() . ')!'); for (my $i=0;$i<=$res;$i++) { print "-For record# $i: field1=$f1[$i], field2=$f2[$i]\n"; } #ANOTHER SIMPLE SELECT, RETURNING THE RESULTS INTO @res: my @res = $dbh->select('select field1, field2 from test'); for (my $i=0;$i<=$#res;$i++) { print "\n-For record# $i: "; for (my $j=0;$j<=$#{$res[$i]};$j++) { print "value =$res[$i]->[$j],\t"; } } #DO A VECTORIZED SELECT, SELECTING A RECORD FOR EACH KEY VALUE IN @f1: our (@f3, @f4); #MUST BE PACKAGE, NOT "MY" VARIABLES: my $sqlstr = 'select field3, field4 into :f3, :f4 from test2 where field1 = :f1'; $res = $dbh->do($sqlstr, 1) or die $dbh->errstr(); #INSERT A NEW RECORD: $res = $dbh->do1('insert into test values (?, ?, ?, ?)', 'value1', 'value2', 'value3', 'value4') or die ('Could not insert record (' . $dbh->err() . '.' . $dbh->errstr() . ')!'); #COMMIT THE TRANSACTION: $dbh->commit(); #FETCH THE AUTONUMBER / SEQUENCE KEY VALUE OF THE LAST INSERT: my $newestkey = $dbh->fetchseq('keyfieldname'); print "..Just inserted new record with key: $newestkey.\n"; #CURSORS: $sqlstr = 'update test set field1 = ? where field2 = ?'; my $csr = $dbh->opencsr($sqlstr) or die ("Could not open ($sqlstr) (".$dbh->errstr().")!"); #AN OPTIONAL 2ND ARGUMENT TO opencsr 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 select (see ":f1" and ":f2" in prev. examples). for (my $i=0;$i<=$#f1;$i++) { $csr->bind($i, $f1[$i]); } $csr->closecsr(); #AN EXAMPLE OF SELECT USING CURSORS: $sqlstr = 'select field3, field4 from test2 where field1 = ?'; $csr = $dbh->opencsr($sqlstr) or die "Could not open ($sqlstr) (".$dbh->errstr().")!"; for (my $i=0;$i<=$#f1;$i++) { $csr->bind($f1[$i]); while (my ($f3, $f4) = $csr->fetch()) { print "-For record# $i (key $f1[$i]): field3=$f3, field4=$f4\n"; } } $csr->closecsr(); #ANOTHER EXAMPLE OF VECTOR SELECT USING CURSORS (WITH INTO CLAUSE): #NOTE: $csr->fetch() and $csr->fetchall() WILL NOT RETURN RESULTS HERE BECAUSE #THE DATA HAS ALREADY BEEN FETCHED BY THE $csr->bind() CALL INTO THE VECTORS: @f1 and @f3! $sqlstr = 'select field3, field4 into :f3, :f4 from test2 where field1 = ?'; $csr = $dbh->opencsr($sqlstr) or die "Could not open ($sqlstr) (".$dbh->errstr().")!"; for (my $i=0;$i<=$#f1;$i++) { $res = $csr->bind($f1[$i]); for (my $j=0;$j<$res;$j++) { print "-For record# $i (key $f1[$i]): field3=$f3[$j], field4=$f4[$j]\n"; } } $csr->closecsr(); #DISCONNECT FROM THE DATABASE: $dbh->disconnect(); exit(0);
JDBIx provides a higher-level interface to DBI by combining prepare() and execute() methods, but more than that, it also provides placeholders for databases that do not support them, provides instant loading of select() results into arrays by column via a single command. Another feature is abstraction of sequence / autonumbering fields in a database-independent manner.
Creates and returns a new database connection object. connect-string normally contains the DBD::modulename, the database-name, the user-name, and the password in the format: 'modulename:database-name,user-name,password'. The user-name, and password are sometimes optional depending on the database and connection requirements. attrs is an optional hash-reference and DBD::modulename -dependent. Returns undef on failure.
connect-string: Examples:
1) Simple 1: 'mysql:mydatabase,userid,password'
2) Simple 2: 'SQLite:mydatabase'
3) Proxy 1: 'dbi:host:port:mysql:mydatabase,userid,password'
3) Proxy 2: 'mysql:mydatabase,userid,password', { -proxy => 'host:port' }
4) Literal1: '=dbi:Proxy:hostname=host;port=9090;dsn=DBI:dbi:host:port:mysql:userid,password'
5) Literal2: 'connect=dbi:Proxy:hostname=host;port=9090;dsn=DBI:dbi:host:port:mysql:userid,password'
6) Hash1: (-type => 'mysql', -name = >'mydatabase', -user=> 'userid' -pswd => 'password', -proxy => 'host:port')
7) Hash2: (-connect => 'mysql:mydatabase,userid,password', -proxy => 'host:port')
8) Hash3: (-connect => '=dbi:Proxy:hostname=host;port=9090;dsn=DBI:dbi:host:port:mysql:userid,password', -proxy => 'IGNORESME!:port')
NOTE: If using the Hash format, any additional arguments ("attrs") should be included in the hash, rather than as a separate hash-reference.
NOTE2: If a literal DBI connect string ("=dbi...") is used, any conflicting attributes, namely "-name", "-user", "-pswd", and "-proxy" are IGNORED and the literal string is passed directly to DBI->connect()!
attrs - include any additional attributes in hash-reference format. This includes any parameters to be passed to DBI, any database-specific parameters, any jdbix parameters (prefix with "-jdbix_parameter"), and any environment variables that need to be set (prefix with "-jdbix_env_variable-name").
If an argument is passed, then sets the default autocommit status for any future database objects created within the program to on or off based on that argument, if no argument is passed, it returns the current default autocommit status, which is zero (0) if this function has not been previously called with an argument. NOTE: It does not alter the autocommit flag for any currently opened database objects, see $dbh->autocommit() for changing the autocommit flag for a currently opened database.
Returns the last error code number encountered. If $database_object is specified, then it returns the last error code recorded for that database object, otherwise $DBI::err is returned. NOTE: this method can be used after an attempted creation of a new database object to get the error code when the object fails to be created (and there's thus no database object). After a database object is successfully created, it's preferred to use $dbh->err().
Returns the last error message text for the last error encountered. If $database_object is specified, then it returns the last error message recorded for that database object, otherwise $DBI::errstr is returned. NOTE: this method can be used after an attempted creation of a new database object to get the error code when the object fails to be created (and there's thus no database object). After a database object is successfully created, it's preferred to use $dbh->errstr().
Sets up a log file to log queries to. The first sets up a log file for a specific connection, the latter, a general log file for all connections. The log-file should be a string containing the full path of the log file. Both functions may be called and set up two separate log files. Return 1 if successfully opened the log file for writing, 0 if not.
Set or retrieve the current DEFAULT package name used for placeholder variables in "INTO" clauses in queries. NOTE: This function does NOT change the current package name used by currently opened database objects, but only the default package name used when subsequent database objects are created! See $dbh->package() for changing the current package name used by an open database object. IF package_name is specified, the default package name is set in the program in subsequently opened database objects (until &jdbix_package() is called with a different package name). If not specified, it returns the current package name. The default package name is "main". NOTE: The current package_name can also be set for a database when opened by passing the 'jdbix_package' => 'package_name' attribute when creating the database object.
If an argument is passed, then sets the database's autocommit status to on or off based on that argument, if no argument is passed, it returns the current state of the database's autocommit status.
Causes any pending queries that changed the database to be committed (unless autocommit is on for the database connection). If autocommit is on, it returns 1 (true). Otherwise, it returns the result of the DBI->commit() call.
Disconnect from the database;
Provides vectorized selects and inserts, updates, etc. query-string can be any valid SQL query string. Returns the number of records affected / selected. attrs is an optional hash-reference that can contain one or more of the following options:
If 0, then the query is just executed as-is and the number of rows affected / selected is returned. For selects based on key vectors (arrays), the results are like as if 2 was specified.
If 1, and the query is a select, then it is assumed that a 1:1 relationship exists between the vector array-references given in the WHERE clause and the results returned - the query is done once in a loop for each element in the set of "keys" specified by these array-references and only the first matching record for each keyset will be returned. All the arrays given by the array-references in both the WHERE clause AND the INTO clause will be sorted WITHIN the sort order of the elements in the keys arrays.
If 2 or 3, a 1:MANY relationship is assumed between the key arrays and the results returned in the INTO arrays, and all records that match the combination of the queries performed on each element in the set of "keys" specified by the array-references in the WHERE clause will be returned sorted, but the "key" arrays referenced will not be sorted, but remain in the order given. The difference between 2 and 3 is that with 2, at least one record (with empty fields, if none match) is guarenteed to be returned for EACH element in the longest key array (all key arrays should be the same length). With 3, if no records match a given element in the key arrays, then no records will be returned for that set of keys.
Performs a single query as-is.
Similar to $dbh->do1(), but simpler and a bind-parameter-list is allowed. This method is the preferred method of doing non-select queries, but can handle selects too. The optional returned results are the same as $dbh->select() for select queries, and $dbh->do() for other queries. The primary difference versus $dbh->do() is that the query is not looped against vectors given in a WHERE clause, the WHERE clause must be a simple scalar set of key values, which can be bound with placeholders by the bind-parameter-list. Results are not committed unless autocommit is turned on for the database connection. Think of it as "do ONE query".
Returns the last error code number encountered.
Returns the last error message text for the last error encountered.
This can be called anytime and returns the next value of sequence sequencename. For Sprite and Oracle, this is accomplished by doing a "SELECT <sequencename>.NEXTVAL from DUAL". For mysql, it returns the mysql_insert_id(). Currently only Oracle, Sprite, mysql, and SQLite 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, 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". For mysql and SQLite, the sequencename can be omitted as mysql always returns mysql_insert_id() which is the value of the most recent autonumber field updated. SQLite always returns sqlite_last_insert_rowid().
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. sequencename defaults to the last sequence used in Oracle databases, but can be overridden here. 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".
The keyfieldname is the sequence or autonumber field name. The optional sequencename is useful only for Oracle and Sprite databases and specifies the actual "sequence" name to fetch. For others, the default is used, which is the last sequence or autonumber field used in the most recent insert or update query that involved a sequence or autonumber field. The tablename field is useful for other "non-supported" databases (those other than Oracle, Sprite, or mysql) to tell fetchseq() which table to grab the most recent autonumber / sequence field specified by keyfieldname from. It is ignored for supported databases and defaults to the last table to which an insert or update involving a sequence or autonumber field was done.
Set or retrieve the current package name used for placeholder variables in "INTO" clauses in queries. IF package_name is specified, the default package for all such variables from that point on in the program (until &jdbix_package() is called with a different package name) are assumed to be of that package. If not specified, it returns the current package name. The default package name is "main". NOTE: The current package_name can also be set for a database when opened by passing the '-jdbix_package' => 'package_name' attribute when creating the database object.
Causes any pending queries that changed the database to be rolled back (unless autocommit is on for the database connection). If autocommit is on, it returns 1 (true). Otherwise, it returns the result of the DBI->rollback() call.
Perform a selection query on the database. The query-string can be a standard SQL query string, but may contain traditional SQL placeholders ("?") or special JDBIx placeholders in an "INTO" clause representing corresponding Perl variables to receive the respective field data. For example:
[ $scalar | @array = ] $dbh->select('select field1, field2, field3 into :f1, :f2, :f3 where keyfield = ?', 'foo');
This would do a prepare(), an execute(), and fetch() functions, and create / reset three scalars ($f1, $f2, and $f3) set to the first values returned for the respective fields, and three arrays (@f1, @f2, and @f3), each containing a list of all the values returned for their respective columns. NOTE: These are package variables, NOT "my" variables. The package is the current package (or main) or the one specified by the dbix_package attribute when the database object was created.
If <@array> is given, an array of references to arrays of field data is returned. This is not necessary (and memory can be saved) if using the "INTO" clause and package variable placeholders as described above. Each element of the array represents a single "row" or "record" of data as a reference to an array containing the field values returned for that record. If no records are returned, then it is (). If $scalar is given, then it is the number of records (rows) fetched.
Opens a "cursor" (does a prepare()) on the query specified by query-string and returns a "cursor" object which can be used ...
If the optional execute_flag is set to true, then an execute() is also performed immediately after the prepare(). One can then call opencsr() in array context, if so, the first element is the cursor object returned and the second element is the result returned by the execute() call.
Returns undef if anything fails.
Causes the values in the bind-parameter-list to be bound to the placeholders and the query to be executed. This is needed unless the execute_flag is set to true when the cursor was opened. Returns the number of rows affected or fetched, undef on failure. "0E0", the Perl "true" value for zero is returned if successful, but no rows are affected. NOTE: If a SELECT query with an INTO clause is used, the data is fetched into the vector arrays by bind() and the number of rows fetched will be returned. In this case, calling fetch() or fetchall() will return empty since the data has already been fetched here. If a SELECT query with no INTO clause is performed, then bind() returns "0E0" (success, but no rows affected or fetched) and then the data can then be fetched with either fetch() or fetchall().
Fetches and returns the next row of data by a call to fetchrow_array(). If no records remained to be fetched, it returns an empty array () (false).
Fetches and returns any and all remaining records by repeated calls to fetchrow_array(). In scalar context, returns only the number of records fetched. In array context, returns an array of array references, one for each record returned. These each reference an array of field values returned. If using array variable placeholders (an INTO clause), then one need not call fetchall() in array context to get back the data.
Closes the cursor.
-none (yet)-
DBI, perl(1)
To install JDBIx, copy and paste the appropriate command in to your terminal.
cpanm
cpanm JDBIx
CPAN shell
perl -MCPAN -e shell install JDBIx
For more information on module installation, please visit the detailed CPAN module installation guide.