Jakub Špičák > deltax-modules-3.14 > DeltaX::Database

Download:
deltax-modules-3.14.tgz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 3.3   Source  

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.

syntax highlighting: