DeltaX::Database - Perl module which hiddens DB differences on DBI level
_____ / \ _____ ______ ______ ___________ / \ / \\__ \ / ___// ___// __ \_ __ \ / Y \/ __ \_\___ \ \___ \\ ___/| | \/ \____|__ (____ /____ >____ >\___ >__| \/ \/ \/ \/ \/ project Supported drivers: Oracle [Oracle] PostgreSQL [Pg] MySQL [mysql] Sybase [Sybase] [not tested] MS SQL [mssql] [using Sybase driver] DB2 [DB2] Solid [Solid]
new - New DB connect close - Close DB connect check - DB connect check transaction_begin - Begin transaction transaction_end - End transaction select - Performing SQL select open_cursor - Cursor openning fetch_cursor - Get row by opened cursor close_cursor - Close cursor exists_cursor - Checks existence of cursor insert - Performing SQL insert delete - Performing SQL delete update - Performing SQL update command - Performing any SQL command open_statement - Prepare statement (for bind values) perform_statement - Perform prepared statement close_statement - Close prepared statement exists_statement - Checks existence of statement quote - Quotting string date2db - Converting datetime to db format db2date - Converting db format of date to datetime nextval - Select next value from sequence func - Performs DBD specific function const - Sets DBD specific constant ping - Checks DB connect trace - set trace level trace_on - DBI trace ON trace_off - DBI trace OFF set_stat - set statistics type reset_stat - reset statistics get_stat - get statistics test_err - test sqlerror
$Dsqlstatus - SQL status (error) code $Dcmdstatus - Command status (error) code $Derror_message - Actual error message $VERSION - Module wersion $Dstr_command - last used SQL command
get_driver - Returns DBD driver get_source - Returns DBD specific connect string _trace - Error trace (using DeltaX::Trace) _trace_msg - Error trace (using DeltaX::Trace) _set_app - Sets application prefix (for statements) _replace_values - replaces values for placeholders
Connects to DB and creates new object which handles it. Parameters are given in key => value form.
Possible parameters: driver [required] - DB driver to use (eg. Oracle, Pg, ...) dbname [required] - database name host [def: none] - host on which database resides user [required] - user to connect to DB auth - password to connect to DB autocommit [def: 0] - use autocommit? datestyle [def: none] - DB specific datestyle (eg. PGDATESTYLE for PostgreSQL, NLS_DATE_FORMAT for Oracle, DBDATE for Informix) close_curs [def: 0] - close cursors when ending transaction? cursor_type [def: INTERNAL] - default cursor type <INTERNAL|EXTERNAL> trace [def: 0] - tracing: 0 - none, 1 - errors, 2 - with SQL string app [def: none] - application prefix for Returns: undef in case of error (check $Derror_message for reason) otherwise returns new DeltaX::Database object
Closes DB connect
Returns: -nothing-
Checks DB connect (via ping()).
Syntax: check() Args: -none- Returns: -1 - error 0 - ok/connected
Interface to DBH->ping().
Syntax: ping() Args: -none- Returns: value returned by DBH->ping().
Starts new transaction by performing COMMIT ($type == 1, it's default) or ROLLBACK ($type == 0).
Syntax: transaction_begin([$type]) Args: $type [def: 1] - see above Returns: 1 - ok 0 - SQL command failed (see $Derror_message) -1 - autocommit is enabled -2 - not connected
Note: It erases all cursors if close_curs enabled (see "new").
Ends transaction by performing COMMIT ($type == 1, it's default) or ROLLBACK ($type == 0).
Syntax: transaction_begin([$type]) Args: $type [def: 0] - see above Returns: 1 - ok 0 - SQL command failedc (see $Derror_message) -1 - autocommit is enabled -2 - not connected
Performs SQL command (SELECT assumed) and returns array with first returned row.
Syntax: select($select_str) Args: $select_str - SELECT command string Returns: array, first value: 0 - no records found >0 - record found (on index 1 starts selected row values) -1 - SQL error (see $Derror_message) -2 - bad parameters -3 - not connected
Note: If transaction not started, it performs transaction_end(0)
Opens new cursor $cursor_name. For fetching rows use fetch_cursor().
Syntax: open_cursor($cursor_name, {$select_str | $prepared_name, [$cursor_type,] [@bind_values]}) Args: $cursor_name [required] - cursor name (existing cursor with the same name will be replaced) $select_str - SQL SELECT command - or - $prepared_name - name of prepared statement $cursor_type - INTERNAL [emulated], EXTERNAL [by DBI - DB] @bind_values - values for prepared statement Returns: 0 - no rows found >0 - ok, for INTERNAL returns number of rows, for EXTERNAL DBD specific value -1 - SQL command failed (see $Derror_message) -2 - bad parameters -3 - not connected
Note: Cursor from prepared statement is always INTERNAL.
Note: For MS SQL, cursor is always INTERNAL.
Returns next row from cursor.
Syntax: fetch_cursor($cursor_name, [$num_row]) Args: $cursor_name [required] - cursor name $num_row [def: next] - position of required row (from 0, for INTERNAL cursors only!) Returns: array with result, first value indicates status: 0 - last row, next fetch_cursor() returns first row again >0 - next row, not last -1 - SQL error (see $Derror_message) -2 - bad parameters -3 - cursor doesn't exist -4 - not connected
Closes cursor and releases data from it.
Syntax: close_cursor($cursor_name) Args: $cursor_name [required] - cursor name to close Returns: 0 - cursor closed -1 - bad paramaters -2 - cursor doesn't exist -3 - not connected
Check existence of cursor of given name.
Syntax: exists_cursor($cursor_name) Args: $cursor_name [required] - cursor name Returns: 0 - not exists 1 - exists
Prepares SQL command, which can bind variables and can be repeatly exexuted (using "perform_statement" or "open_cursor").
Syntax: open_statement($stmt_name, $sql_string, $num_binds) Args: $stmt_name [required] - statement name, if exists will be replaced $sql_string [required] - SQL command to prepare $num_binds [optional] - number of binded values (for check only) Returns: >0 - number of binded variables [ok] 0 - no bind values [ok] -1 - SQL command failed [not supported by all drivers] -2 - bad parameters -3 - bad number of binded variables -4 - not connected
Note: Use only question marks, no :a form!
Note: [Oracle only] For BLOBs use exclamation marks or ?B instead of question marks. [Oracle only] For CLOBs use ?C instead of question marks.
Performs prepared statement.
Syntax: perform_statement($stmt_name, [@bind_values]) Args: $stmt_name [required] - statement name (must be prepared using prepare_statement()) @bind_values - values which will be binded to statement, there must be not less values than there is in prepared statement, redundant will be ignored Returns: array, first value indicates status: 0 - no row returned/affected, but success >0 - ok, number of returned/affected rows (for SELECT it returns just one row (see select()), for INSERT/UPDATE/DELETE returns number of affected rows) -1 - SQL error (see $Derror_message) -2 - bad parameters -3 - statement doesn't exist -4 not connected for SELECT other values in array represents returned row
Closes (destroys) prepared statement.
Syntax: close_statement($stmt_name) Args: $stmt_name [required] - statement name to close Returns: 0 - closed -2 - bad parameters -3 - statement doesn't exist -4 - not connected
Checks existence of statement of given name.
Syntax: exists_statement($stmt_name) Args: $stmt_name [required] - statement name to check Returns: 1 - exists 0 - not exists or no statement name given
Performs SQL command (assumes INSERT) and returns number of inserted rows.
Syntax: insert($insert_string) Args: $insert_string [required] - the SQL command (INSERT) Returns: >=0 - number of inserted rows -1 - sql command failed (check Dsqlstatus, Dcmdstatus, Derror_message -2 - bad parameter -3 - not connected
Performs SQL command (assumes DELETE) and returns number of deleted rows.
Syntax: delete($delete_string) Args: $delete_string [required] - the SQL command (DELETE) Returns: >=0 - number of deleted rows -1 - sql command failed (check Dsqlstatus, Dcmdstatus, Derror_message) -2 - bad parameter -3 - not connected
Performs SQL command (assumes UPDATE) and returns number of updated rows.
String: update($update_string) Args: $update_str [required] - the SQL command (UPDATE) Returns: >=0 - number of updated rows -1 - sql command failed (check Dsqlstatus, Dcmdstatus, Derror_message) -2 - bad parameter -3 - not connected
Performs generic command.
String: command($command_string) Args: $command_string [required] - SQL command Returns: >0 - ok -1 - sql command failed (check Dsqlstatus, Dcmdstatus, Derror_message) -2 - bad parameter -3 - not connected
Interface to DBH->func().
Syntax: func(@func_params) Args: @func_params - parameters for func() Returns: value(s) returned by DBH->func()
Interface to DBH->constants.
Syntax: const($const_name[, $value]) Args: $const_name [required] - constant name $value - if defined, set constant to this value Returns: constant $const_name value
Returns next value from sequence.
Syntax: nextval($seq_name) Args: $seq_name [required] - sequence name Returns: >0 - next value from sequence -1 - SQL error (see Derror_message) -2 - bad parameters -3 - not connected
Quotes given string(s).
Note: You should not quote values used in prepared statements.
Syntax: quote(@array) Args: @array - array of strings to quote Returns: array with quoted strings
Formats string (date or datetime) to DB format.
String: date2db([$format_type][, @date_value]) Args: $format_type - DB format type COMMON [default] or PREPARED [for prepared statements] -other parameters are optional, default is now- 1. param - date [dd.mm.yyyy] or datetime [dd.mm.yyyy hh:mm:ss] or seconds or ! now (date) !! now (datetime) 2. param - minutes 3. param - hours 4. param - day in month 5. param - month (0 will be replaced to 1) 6. param - year (if <1000, 1900 will be added) Returns: according to number of arguments without $format_type if given: 0 - current datetime 1 - input is date(time) string, output date(time) 2 - input is month and year, returns date with last day in month 3 - date >3 - datetime undef - bad parameters Returned: see above undef - bad parameters or not connected Note: For driver Must be set To Pg DBDATESTYLE ISO *) Oracle NLS_DATE_FORMAT dd.mm.yyyy hh24:mi:ss *) Informix DBDATE dmy4. *) Sybase [freedts.conf] mssql [freedts.conf]
*) You can use datestyle parameter of "new".
Formats string from DB format.
Syntax: db2date($datetime) Args: $datetime [required] - date(time) from DB Returns: - in the scalar context is returned datetime string - in the array context is returned array ($sec, $min, $hour, $day, $mon, $year) undef or () depend on context bad parameters or not connected Note: For driver Must be set To Pg DBDATESTYLE ISO *) Oracle NLS_DATE_FORMAT dd.mm.yyyy hh24:mi:ss *) Informix DBDATE dmy4. *) Sybase [freedts/locales.conf] mssql [freedts/locales.conf]
Interface to DBI->trace().
Syntax: trace_on($level, $file) Args: $level - trace level $file - filename to store log Returns: -nothing-
Note: See DBI manpage.
Stops tracing started by trace_on().
Syntax: trace_off() Args: -none- Returns: -nothing-
Sets application prefix.
Syntax: _set_app($prefix) Args: $prefix - used for statements and cursors Returns: -nothing-
Note: Default prefix is empty, to set it to this default just call _set_app('').
Sets statistics.
Syntax: set_stat(type[,max_high[,max_all]]) Args: type - type of statistics: none - no statistics sums - only sumaries high - sums & top statements all - high & all statements max_high - max. number of stored top statements (default: 3) max_all - max. number of stored all statements (default: 1000) Returns: -nothing-
Resets statistic counters and arrays.
Syntax: reset_stat() Args: -none- Returns: -nothing-
Gets module statistics.
Syntax: get_stat() Args: -none- Returns: array with statistics: field 0 ... total time for statements (sums, high, all) field 1 ... number of performed statements (sums, high, all) field 2 ... number of errors (sums, high, all) field 3 ... reference to array with top statements (high, all) field 4 ... reference to array with all statements (all) For field 3 and 4: it's an array of references to hashes with these keys: type - action performed (SELECT, INSERT, UPDATE, DELETE, COMMAND, PERFORM, CURSOR_PERFORM, CURSOR_SQL) sql - SQL command name - statement name (if any) par - reference to an array with parameters (if any) time - time needed to perform statement error- error string in case of error
Resets local statistics (global leaves untouched).
Test last sqlerror.
Syntax: test_err(supp_errs) Args: supp_errs (optional) - list of supp_error (below) supp_error (optional) - supposed error. May be: 1 or TABLE_NOEXIST - not existing table (objects) 2 or TABLE_EXIST - table (object) already exists 3 or REC_EXIST - duplicate value in unique key 4 or SCHEMA_NOTEXIST - not existing schema 5 or SCHEMA_EXIST - schema already exists 4 and 5 are not sopported by some drivers (Oracle, Informix, mysql, mssql). Returns: Without args returns error number 1,2,3,4,5 or -1 (unknown). With args return the (args) error number (if equal with any) or 0.
Originally created by Martin Kula <martin.kula@deltaes.com>
Rewritten to object model by Jakub Spicak <jakub.spicak@deltaes.cz> for masser.
Delta E.S., Brno (c) 2000-2002.
To install DeltaX::Page, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DeltaX::Page
CPAN shell
perl -MCPAN -e shell install DeltaX::Page
For more information on module installation, please visit the detailed CPAN module installation guide.