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.

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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

Public functions

Public variables

Private functions

Private variables

DESCRIPTION

new

close

check

ping

transaction_begin

transaction_end

select

open_cursor

fetch_cursor

close_cursor

exists_cursor

open_statement

perform_statement

close_statement

exists_statement

insert

delete

update

command

func

const

nextval

quote

date2db

db2date

trace_on

trace_off

_set_app

set_stat

reset_stat

get_stat

reset_stat

test_err

AUTHOR

Module Install Instructions