The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
deltax-modules-3.05
River stage zero No dependents
/ DeltaX::Database

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 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.