NAME
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]
SYNOPSIS
Public functions
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
Public variables
$Dsqlstatus - SQL status (error) code
$Dcmdstatus - Command status (error) code
$Derror_message - Actual error message
$VERSION - Module wersion
$Dstr_command - last used SQL command
Private functions
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
Private variables
DESCRIPTION
new
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
close
Closes DB connect
Returns: -nothing-
check
Checks DB connect (via ping()).
Syntax:
check()
Args:
-none-
Returns:
-1 - error
0 - ok/connected
ping
Interface to DBH->ping().
Syntax:
ping()
Args:
-none-
Returns:
value returned by DBH->ping().
transaction_begin
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").
transaction_end
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
Note: It erases all cursors if close_curs enabled (see "new").
select
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)
open_cursor
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.
fetch_cursor
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
close_cursor
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
exists_cursor
Check existence of cursor of given name.
Syntax:
exists_cursor($cursor_name)
Args:
$cursor_name [required] - cursor name
Returns:
0 - not exists
1 - exists
open_statement
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.
perform_statement
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
close_statement
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
exists_statement
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
insert
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
delete
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
update
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
command
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
func
Interface to DBH->func().
Syntax:
func(@func_params)
Args:
@func_params - parameters for func()
Returns:
value(s) returned by DBH->func()
const
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
nextval
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
quote
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
date2db
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".
db2date
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]
*) You can use datestyle parameter of "new".
trace_on
Interface to DBI->trace().
Syntax:
trace_on($level, $file)
Args:
$level - trace level
$file - filename to store log
Returns:
-nothing-
Note: See DBI manpage.
trace_off
Stops tracing started by trace_on().
Syntax:
trace_off()
Args:
-none-
Returns:
-nothing-
_set_app
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('').
set_stat
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-
reset_stat
Resets statistic counters and arrays.
Syntax:
reset_stat()
Args:
-none-
Returns:
-nothing-
get_stat
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
reset_stat
Resets local statistics (global leaves untouched).
Syntax:
reset_stat()
Args:
-none-
Returns:
-nothing-
test_err
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.
AUTHOR
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.