Module: IBM

Classes and Modules

Class IBM_DB::ClientInfo
Class IBM_DB::Connection
Class IBM_DB::PConnection
Class IBM_DB::RowObject
Class IBM_DB::ServerInfo
Class IBM_DB::Statement

Constants

BINARY	=	INT2NUM(1)	Specifies that binary data shall be converted to a hexadecimal encoding and returned as an ASCII string
CONVERT	=	INT2NUM(2)	Specifies that binary data shall be converted to a hexadecimal encoding and returned as an ASCII string
PASSTHRU	=	INT2NUM(3)	Specifies that binary data shall be converted to a NULL value
PARAM_FILE	=	INT2NUM(11)	Specifies that the column should be bound directly to a file for input
ATTR_CASE	=	INT2NUM(ATTR_CASE)	Specifies the column names case attribute ATTENTION this number is not currently in CLI but used for ibm_db purpose only
CASE_NATURAL	=	INT2NUM(0)	Specifies that column names will be returned in their natural case
CASE_LOWER	=	INT2NUM(1)	Specifies that column names will be returned in lower case
CASE_UPPER	=	INT2NUM(2)	Specifies that column names will be returned in upper case
SQL_ATTR_CURSOR_TYPE	=	INT2NUM(SQL_ATTR_CURSOR_TYPE)	Specifies the cursor type
SQL_CURSOR_DYNAMIC	=	INT2NUM(SQL_CURSOR_DYNAMIC)	Cursor type that detects all changes to the result set ATTENTION Only supported when using DB2 for z/OS Version 8.1 and later.
SQL_CURSOR_FORWARD_ONLY	=	INT2NUM(SQL_CURSOR_FORWARD_ONLY)	Cursor type that only scrolls forward. This is the default
SQL_CURSOR_KEYSET_DRIVEN	=	INT2NUM(SQL_CURSOR_KEYSET_DRIVEN)	Cursor type is a pure keyset cursor
SQL_SCROLL_FORWARD_ONLY	=	INT2NUM(SQL_SCROLL_FORWARD_ONLY)	Cursor type that only scrolls forward
SQL_CURSOR_STATIC	=	INT2NUM(SQL_CURSOR_STATIC)	Cursor type in which the data in the result set is static
SQL_PARAM_INPUT	=	INT2NUM(SQL_PARAM_INPUT)	Parmater binding type of input
SQL_PARAM_OUTPUT	=	INT2NUM(SQL_PARAM_OUTPUT)	Parmater binding type of output
SQL_PARAM_INPUT_OUTPUT	=	INT2NUM(SQL_PARAM_INPUT_OUTPUT)	Parmater binding type of input/output
SQL_BINARY	=	INT2NUM(SQL_BINARY)	Data type used to specify binary data
SQL_BIGINT	=	INT2NUM(SQL_BIGINT)	Data type used to specify bigint data
SQL_LONG	=	INT2NUM(SQL_INTEGER)	Data type used to specify long data
SQL_DOUBLE	=	INT2NUM(SQL_DOUBLE)	Data type used to specify double data
SQL_CHAR	=	INT2NUM(SQL_CHAR)	Data type used to specify char data
SQL_AUTOCOMMIT_OFF	=	INT2NUM(SQL_AUTOCOMMIT_OFF)	Operates in auto-commit mode off. The application must manually commit or rollback transactions
SQL_AUTOCOMMIT_ON	=	INT2NUM(SQL_AUTOCOMMIT_ON)	Operates in auto-commit mode on. This is the default
SQL_ATTR_AUTOCOMMIT	=	INT2NUM(SQL_ATTR_AUTOCOMMIT)	Specifies whether to use auto-commit or manual commit mode
SQL_ATTR_INFO_USERID	=	INT2NUM(SQL_ATTR_INFO_USERID)	String used to identify the client user ID sent to the host database
SQL_ATTR_INFO_WRKSTNNAME	=	INT2NUM(SQL_ATTR_INFO_WRKSTNNAME)	String used to identify the client workstation name sent to the host database
SQL_ATTR_INFO_APPLNAME	=	INT2NUM(SQL_ATTR_INFO_APPLNAME)	String used to identify the client application name sent to the host database
SQL_ATTR_INFO_ACCTSTR	=	INT2NUM(SQL_ATTR_INFO_ACCTSTR)	String used to identify the client accounting string sent to the host database

Public Class methods

active(...)

IBM_DB::active — Checks if the specified connection resource is active

Description

object IBM_DB::active(resource connection)

Returns true if the given connection resource is active

Parameters

connection

    The connection resource to be validated.

Return Values

Returns true if the given connection resource is active, otherwise it will return false

autocommit(...)

IBM_DB::autocommit — Returns or sets the AUTOCOMMIT state for a database connection

Description

mixed IBM_DB::autocommit ( resource connection [, bool value] )

Returns or sets the AUTOCOMMIT behavior of the specified connection resource.

Parameters

connection

  A valid database connection resource variable as returned from connect() or pconnect().

value

  One of the following constants:
  DB2_AUTOCOMMIT_OFF
      Turns AUTOCOMMIT off.
  DB2_AUTOCOMMIT_ON
      Turns AUTOCOMMIT on.

Return Values

When IBM_DB::autocommit() receives only the connection parameter, it returns the current state of AUTOCOMMIT for the requested connection as an integer value. A value of 0 indicates that AUTOCOMMIT is off, while a value of 1 indicates that AUTOCOMMIT is on.

When IBM_DB::autocommit() receives both the connection parameter and autocommit parameter, it attempts to set the AUTOCOMMIT state of the requested connection to the corresponding state.

Returns TRUE on success or FALSE on failure.

bind_param(...)

IBM_DB::bind_param — Binds a Ruby variable to an SQL statement parameter

Description

bool IBM_DB::bind_param ( resource stmt, int parameter-number, string variable-name [, int parameter-type

                                        [, int data-type [, int precision [, int scale [, int size[]]]]]] )

Binds a Ruby variable to an SQL statement parameter in a statement resource returned by IBM_DB::prepare(). This function gives you more control over the parameter type, data type, precision, and scale for the parameter than simply passing the variable as part of the optional input array to IBM_DB::execute().

Parameters

stmt

  A prepared statement returned from IBM_DB::prepare().

parameter-number

  Specifies the 1-indexed position of the parameter in the prepared statement.

variable-name

  A string specifying the name of the Ruby variable to bind to the parameter specified by parameter-number.

parameter-type

  A constant specifying whether the Ruby variable should be bound to the SQL parameter as an input parameter
  (SQL_PARAM_INPUT), an output parameter (SQL_PARAM_OUTPUT), or as a parameter that accepts input and returns output
  (SQL_PARAM_INPUT_OUTPUT). To avoid memory overhead, you can also specify PARAM_FILE to bind the Ruby variable
  to the name of a file that contains large object (BLOB, CLOB, or DBCLOB) data.

data-type

  A constant specifying the SQL data type that the Ruby variable should be bound as: one of SQL_BINARY,
  DB2_CHAR, DB2_DOUBLE, or DB2_LONG .

precision

  Specifies the precision that the variable should be bound to the database.

scale

   Specifies the scale that the variable should be bound to the database.

size

   Specifies the size that should be retreived from an INOUT/OUT parameter.

Return Values

Returns TRUE on success or FALSE on failure.

client_info(...)

IBM_DB::client_info — Returns an object with properties that describe the DB2 database client

Description

object IBM_DB::client_info ( resource connection )

This function returns a read-only object with information about the DB2 database client. The following table lists the DB2 client properties:

Table 1. DB2 client properties

Property name:	Description (Return type)
APPL_CODEPAGE:	The application code page. (int)
CONN_CODEPAGE:	The code page for the current connection. (int)
DATA_SOURCE_NAME:	The data source name (DSN) used to create the current connection to the database. (string)
DRIVER_NAME:	The name of the library that implements the DB2 Call Level Interface (CLI) specification. (string)
DRIVER_ODBC_VER:	The version of ODBC that the DB2 client supports. This returns a string "MM.mm" where MM is the major version and mm is the minor version. The DB2 client always returns "03.51". (string)
DRIVER_VER:	The version of the client, in the form of a string "MM.mm.uuuu" where MM is the major version, mm is the minor version, and uuuu is the update. For example, "08.02.0001" represents major version 8, minor version 2, update 1. (string)
ODBC_SQL_CONFORMANCE:	There are three levels of ODBC SQL grammar supported by the client: MINIMAL (Supports the minimum ODBC SQL grammar), CORE (Supports the core ODBC SQL grammar), EXTENDED (Supports extended ODBC SQL grammar). (string)
ODBC_VER:	The version of ODBC that the ODBC driver manager supports. This returns a string "MM.mm.rrrr" where MM is the major version, mm is the minor version, and rrrr is the release. The DB2 client always returns "03.01.0000". (string)

Parameters

connection

   Specifies an active DB2 client connection.

Return Values

Returns an object on a successful call. Returns FALSE on failure.

close(...)

IBM_DB::close — Closes a database connection

Description

bool IBM_DB::close ( resource connection )

This function closes a DB2 client connection created with IBM_DB::connect() and returns the corresponding resources to the database server.

If you attempt to close a persistent DB2 client connection created with IBM_DB::pconnect(), the close request returns TRUE and the persistent DB2 client connection remains available for the next caller.

Parameters

connection

  Specifies an active DB2 client connection.

Return Values

Returns TRUE on success or FALSE on failure.

column_privileges(...)

IBM_DB::column_privileges — Returns a result set listing the columns and associated privileges for a table

Description

resource IBM_DB::column_privileges ( resource connection [, string qualifier [, string schema

                                              [, string table-name [, string column-name]]]] )

Returns a result set listing the columns and associated privileges for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases,
    pass NULL or an empty string.

schema

    The schema which contains the tables. To match all schemas, pass NULL or an empty string.

table-name

    The name of the table or view. To match all tables in the database, pass NULL or an empty string.

column-name

    The name of the column. To match all columns in the table, pass NULL or an empty string.

Return Values

Returns a statement resource with a result set containing rows describing the column privileges for columns matching the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	Name of the catalog. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema.
TABLE_NAME:	Name of the table or view.
COLUMN_NAME:	Name of the column.
GRANTOR:	Authorization ID of the user who granted the privilege.
GRANTEE:	Authorization ID of the user to whom the privilege was granted.
PRIVILEGE:	The privilege for the column.
IS_GRANTABLE:	Whether the GRANTEE is permitted to grant this privilege to other users.

columnprivileges(...)

IBM_DB::column_privileges — Returns a result set listing the columns and associated privileges for a table

Description

resource IBM_DB::column_privileges ( resource connection [, string qualifier [, string schema

                                              [, string table-name [, string column-name]]]] )

Returns a result set listing the columns and associated privileges for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases,
    pass NULL or an empty string.

schema

    The schema which contains the tables. To match all schemas, pass NULL or an empty string.

table-name

    The name of the table or view. To match all tables in the database, pass NULL or an empty string.

column-name

    The name of the column. To match all columns in the table, pass NULL or an empty string.

Return Values

Returns a statement resource with a result set containing rows describing the column privileges for columns matching the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	Name of the catalog. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema.
TABLE_NAME:	Name of the table or view.
COLUMN_NAME:	Name of the column.
GRANTOR:	Authorization ID of the user who granted the privilege.
GRANTEE:	Authorization ID of the user to whom the privilege was granted.
PRIVILEGE:	The privilege for the column.
IS_GRANTABLE:	Whether the GRANTEE is permitted to grant this privilege to other users.

columns(...)

IBM_DB::columns — Returns a result set listing the columns and associated metadata for a table

Description

resource IBM_DB::columns ( resource connection [, string qualifier [, string schema [, string table-name [, string column-name]]]] )

Returns a result set listing the columns and associated metadata for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables. To match all schemas, pass '%'.

table-name

    The name of the table or view. To match all tables in the database, pass NULL or an empty string.

column-name

    The name of the column. To match all columns in the table, pass NULL or an empty string.

Return Values

Returns a statement resource with a result set containing rows describing the columns matching the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	Name of the catalog. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema.
TABLE_NAME:	Name of the table or view.
COLUMN_NAME:	Name of the column.
DATA_TYPE:	The SQL data type for the column represented as an integer value.
TYPE_NAME:	A string representing the data type for the column.
COLUMN_SIZE:	An integer value representing the size of the column.
BUFFER_LENGTH:	Maximum number of bytes necessary to store data from this column.
DECIMAL_DIGITS:	The scale of the column, or NULL where scale is not applicable.
NUM_PREC_RADIX:	An integer value of either 10 (representing an exact numeric data type), 2 (representing an approximate numeric data type), or NULL (representing a data type for which radix is not applicable).
NULLABLE:	An integer value representing whether the column is nullable or not.
REMARKS:	Description of the column.
COLUMN_DEF:	Default value for the column.
SQL_DATA_TYPE:	An integer value representing the size of the column.
SQL_DATETIME_SUB:	Returns an integer value representing a datetime subtype code, or NULL for SQL data types to which this does not apply.
CHAR_OCTET_LENGTH:	Maximum length in octets for a character data type column, which matches COLUMN_SIZE for single-byte character set data, or NULL for non-character data types.
ORDINAL_POSITION:	The 1-indexed position of the column in the table.
IS_NULLABLE:	A string value where ‘YES’ means that the column is nullable and ‘NO’ means that the column is not nullable.

commit(...)

IBM_DB::commit — Commits a transaction

Description

bool IBM_DB::commit ( resource connection )

Commits an in-progress transaction on the specified connection resource and begins a new transaction. Ruby applications normally default to AUTOCOMMIT mode, so IBM_DB::commit() is not necessary unless AUTOCOMMIT has been turned off for the connection resource.

Note: If the specified connection resource is a persistent connection, all transactions in progress for all applications using that persistent connection will be committed. For this reason, persistent connections are not recommended for use in applications that require transactions.

Parameters

connection

    A valid database connection resource variable as returned from IBM_DB::connect() or IBM_DB::pconnect().

Return Values

Returns TRUE on success or FALSE on failure.

conn_error(...)

IBM_DB::conn_error — Returns a string containing the SQLSTATE returned by the last connection attempt

Description

string IBM_DB::conn_error ( [resource connection] )

IBM_DB::conn_error() returns an SQLSTATE value representing the reason the last attempt to connect to a database failed. As IBM_DB::connect() returns FALSE in the event of a failed connection attempt, you do not pass any parameters to IBM_DB::conn_error() to retrieve the SQLSTATE value.

If, however, the connection was successful but becomes invalid over time, you can pass the connection parameter to retrieve the SQLSTATE value for a specific connection.

To learn what the SQLSTATE value means, you can issue the following command at a DB2 Command Line Processor prompt: db2 ’? sqlstate-value’. You can also call IBM_DB::conn_errormsg() to retrieve an explicit error message and the associated SQLCODE value.

Parameters

connection

    A connection resource associated with a connection that initially succeeded, but which over time

became invalid.

Return Values

Returns the SQLSTATE value resulting from a failed connection attempt. Returns an empty string if there is no error associated with the last connection attempt.

conn_errormsg(...)

IBM_DB::conn_errormsg — Returns the last connection error message and SQLCODE value

Description

string IBM_DB::conn_errormsg ( [resource connection] )

IBM_DB::conn_errormsg() returns an error message and SQLCODE value representing the reason the last database connection attempt failed. As IBM_DB::connect() returns FALSE in the event of a failed connection attempt, do not pass any parameters to IBM_DB::conn_errormsg() to retrieve the associated error message and SQLCODE value.

If, however, the connection was successful but becomes invalid over time, you can pass the connection parameter to retrieve the associated error message and SQLCODE value for a specific connection.

Parameters

connection

    A connection resource associated with a connection that initially succeeded, but which over time

became invalid.

Return Values

Returns a string containing the error message and SQLCODE value resulting from a failed connection attempt. If there is no error associated with the last connection attempt, IBM_DB::conn_errormsg() returns an empty string.

connect(...)

IBM_DB::connect — Returns a connection to a database

Description

resource IBM_DB::connect ( string database, string username, string password [, array options] )

Creates a new connection to an IBM DB2 Universal Database, IBM Cloudscape, or Apache Derby database.

Parameters

database

   For a cataloged connection to a database, database represents the database alias in the DB2 client catalog.
   For an uncataloged connection to a database, database represents a complete connection string in the following format:
   DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;
   where the parameters represent the following values:
      hostname
         The hostname or IP address of the database server.
      port
         The TCP/IP port on which the database is listening for requests.
      username
         The username with which you are connecting to the database.
      password
         The password with which you are connecting to the database.

username

   The username with which you are connecting to the database.
   For uncataloged connections, you must pass a NULL value or empty string.

password

   The password with which you are connecting to the database.
   For uncataloged connections, you must pass a NULL value or empty string.

options

   An associative array of connection options that affect the behavior of the connection,
   where valid array keys include:
      SQL_ATTR_AUTOCOMMIT
         Passing the SQL_AUTOCOMMIT_ON value turns autocommit on for this connection handle.
         Passing the SQL_AUTOCOMMIT_OFF value turns autocommit off for this connection handle.
      ATTR_CASE
         Passing the CASE_NATURAL value specifies that column names are returned in natural case.
         Passing the CASE_LOWER value specifies that column names are returned in lower case.
         Passing the CASE_UPPER value specifies that column names are returned in upper case.
      CURSOR
         Passing the SQL_SCROLL_FORWARD_ONLY value specifies a forward-only cursor for a statement resource.
         This is the default cursor type and is supported on all database servers.
         Passing the SQL_CURSOR_KEYSET_DRIVEN value specifies a scrollable cursor for a statement resource.
         This mode enables random access to rows in a result set, but currently is supported
         only by IBM DB2 Universal Database.

Return Values

Returns a connection handle resource if the connection attempt is successful. If the connection attempt fails, IBM_DB::connect() returns FALSE.

cursor_type(...)

IBM_DB::cursor_type — Returns the cursor type used by a statement resource

Description

int IBM_DB::cursor_type ( resource stmt )

Returns the cursor type used by a statement resource. Use this to determine if you are working with a forward-only cursor or scrollable cursor.

Parameters

stmt

    A valid statement resource.

Return Values

Returns either SQL_SCROLL_FORWARD_ONLY if the statement resource uses a forward-only cursor or SQL_CURSOR_KEYSET_DRIVEN if the statement resource uses a scrollable cursor.

exec(...)

IBM_DB::exec — Executes an SQL statement directly

Description

resource IBM_DB::exec ( resource connection, string statement [, array options] )

Prepares and executes an SQL statement.

If you plan to interpolate Ruby variables into the SQL statement, understand that this is one of the more common security exposures. Consider calling IBM_DB::prepare() to prepare an SQL statement with parameter markers for input values. Then you can call IBM_DB::execute() to pass in the input values and avoid SQL injection attacks.

If you plan to repeatedly issue the same SQL statement with different parameters, consider calling IBM_DB::prepare() and IBM_DB::execute() to enable the database server to reuse its access plan and increase the efficiency of your database access.

Parameters

connection

    A valid database connection resource variable as returned from IBM_DB::connect() or IBM_DB::pconnect().

statement

    An SQL statement. The statement cannot contain any parameter markers.

options

    An associative array containing statement options. You can use this parameter to request
    a scrollable cursor on database servers that support this functionality.

    cursor
        Passing the SQL_SCROLL_FORWARD_ONLY value requests a forward-only cursor for this SQL statement.
        This is the default type of cursor, and it is supported by all database servers.
        It is also much faster than a scrollable cursor.
        Passing the SQL_CURSOR_KEYSET_DRIVEN value requests a scrollable cursor for this SQL statement.
        This type of cursor enables you to fetch rows non-sequentially from the database server.
        However, it is only supported by DB2 servers, and is much slower than forward-only cursors.

Return Values

Returns a statement resource if the SQL statement was issued successfully, or FALSE if the database failed to execute the SQL statement.

execute(...)

IBM_DB::execute — Executes a prepared SQL statement

Description

bool IBM_DB::execute ( resource stmt [, array parameters] )

IBM_DB::execute() executes an SQL statement that was prepared by IBM_DB::prepare().

If the SQL statement returns a result set, for example, a SELECT statement or a CALL to a stored procedure that returns one or more result sets, you can retrieve a row as an array from the stmt resource using IBM_DB::fetch_assoc(), IBM_DB::fetch_both(), or IBM_DB::fetch_array(). Alternatively, you can use IBM_DB::fetch_row() to move the result set pointer to the next row and fetch a column at a time from that row with IBM_DB::result().

Refer to IBM_DB::prepare() for a brief discussion of the advantages of using IBM_DB::prepare() and IBM_DB::execute() rather than IBM_DB::exec().

Parameters

stmt

    A prepared statement returned from IBM_DB::prepare().

parameters

    An array of input parameters matching any parameter markers contained in the prepared statement.

Return Values

Returns TRUE on success or FALSE on failure.

fetch_array(...)

IBM_DB::fetch_array — Returns an array, indexed by column position, representing a row in a result set

Description

array IBM_DB::fetch_array ( resource stmt [, int row_number] )

Returns an array, indexed by column position, representing a row in a result set. The columns are 0-indexed.

Parameters

stmt

    A valid stmt resource containing a result set.

row_number

    Requests a specific 1-indexed row from the result set. Passing this parameter results in a
    Ruby warning if the result set uses a forward-only cursor.

Return Values

Returns a 0-indexed array with column values indexed by the column position representing the next or requested row in the result set. Returns FALSE if there are no rows left in the result set, or if the row requested by row_number does not exist in the result set.

fetch_assoc(...)

IBM_DB::fetch_assoc — Returns an array, indexed by column name, representing a row in a result set

Description

array IBM_DB::fetch_assoc ( resource stmt [, int row_number] )

Returns an array, indexed by column name, representing a row in a result set.

Parameters

stmt

    A valid stmt resource containing a result set.

row_number

    Requests a specific 1-indexed row from the result set. Passing this parameter results in a
    Ruby warning if the result set uses a forward-only cursor.

Return Values

Returns an associative array with column values indexed by the column name representing the next or requested row in the result set. Returns FALSE if there are no rows left in the result set, or if the row requested by row_number does not exist in the result set.

fetch_both(...)

IBM_DB::fetch_both — Returns an array, indexed by both column name and position, representing a row in a result set

Description

array IBM_DB::fetch_both ( resource stmt [, int row_number] )

Returns an array, indexed by both column name and position, representing a row in a result set. Note that the row returned by IBM_DB::fetch_both() requires more memory than the single-indexed arrays returned by IBM_DB::fetch_assoc() or IBM_DB::fetch_array().

Parameters

stmt

    A valid stmt resource containing a result set.

row_number

    Requests a specific 1-indexed row from the result set. Passing this parameter results in a
    Ruby warning if the result set uses a forward-only cursor.

Return Values

Returns an associative array with column values indexed by both the column name and 0-indexed column number. The array represents the next or requested row in the result set. Returns FALSE if there are no rows left in the result set, or if the row requested by row_number does not exist in the result set.

fetch_object(...)

IBM_DB::fetch_object — Returns an object with properties representing columns in the fetched row

Description

object IBM_DB::fetch_object ( resource stmt [, int row_number] )

Returns an object in which each property represents a column returned in the row fetched from a result set.

Parameters

stmt

    A valid stmt resource containing a result set.

row_number

    Requests a specific 1-indexed row from the result set. Passing this parameter results in a
    Ruby warning if the result set uses a forward-only cursor.

Return Values

Returns an object representing a single row in the result set. The properties of the object map to the names of the columns in the result set.

The IBM DB2, Cloudscape, and Apache Derby database servers typically fold column names to upper-case, so the object properties will reflect that case.

If your SELECT statement calls a scalar function to modify the value of a column, the database servers return the column number as the name of the column in the result set. If you prefer a more descriptive column name and object property, you can use the AS clause to assign a name to the column in the result set.

Returns FALSE if no row was retrieved.

fetch_row(...)

IBM_DB::fetch_row — Sets the result set pointer to the next row or requested row

Description

bool IBM_DB::fetch_row ( resource stmt [, int row_number] )

Use IBM_DB::fetch_row() to iterate through a result set, or to point to a specific row in a result set if you requested a scrollable cursor.

To retrieve individual fields from the result set, call the IBM_DB::result() function. Rather than calling IBM_DB::fetch_row() and IBM_DB::result(), most applications will call one of IBM_DB::fetch_assoc(), IBM_DB::fetch_both(), or IBM_DB::fetch_array() to advance the result set pointer and return a complete row as an array.

Parameters

stmt

    A valid stmt resource.

row_number

    With scrollable cursors, you can request a specific row number in the result set. Row numbering
    is 1-indexed.

Return Values

Returns TRUE if the requested row exists in the result set. Returns FALSE if the requested row does not exist in the result set.

field_display_size(...)

IBM_DB::field_display_size — Returns the maximum number of bytes required to display a column

Description

int IBM_DB::field_display_size ( resource stmt, mixed column )

Returns the maximum number of bytes required to display a column in a result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns an integer value with the maximum number of bytes required to display the specified column. If the column does not exist in the result set, IBM_DB::field_display_size() returns FALSE.

field_name(...)

IBM_DB::field_name — Returns the name of the column in the result set

Description

string IBM_DB::field_name ( resource stmt, mixed column )

Returns the name of the specified column in the result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the 0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns a string containing the name of the specified column. If the specified column does not exist in the result set, IBM_DB::field_name() returns FALSE.

field_num(...)

IBM_DB::field_num — Returns the position of the named column in a result set

Description

int IBM_DB::field_num ( resource stmt, mixed column )

Returns the position of the named column in a result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns an integer containing the 0-indexed position of the named column in the result set. If the specified column does not exist in the result set, IBM_DB::field_num() returns FALSE.

field_precision(...)

IBM_DB::field_precision — Returns the precision of the indicated column in a result set

Description

int IBM_DB::field_precision ( resource stmt, mixed column )

Returns the precision of the indicated column in a result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns an integer containing the precision of the specified column. If the specified column does not exist in the result set, IBM_DB::field_precision() returns FALSE.

field_scale(...)

IBM_DB::field_scale — Returns the scale of the indicated column in a result set

Description

int IBM_DB::field_scale ( resource stmt, mixed column )

Returns the scale of the indicated column in a result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns an integer containing the scale of the specified column. If the specified column does not exist in the result set, IBM_DB::field_scale() returns FALSE.

field_type(...)

IBM_DB::field_type — Returns the data type of the indicated column in a result set

Description

string IBM_DB::field_type ( resource stmt, mixed column )

Returns the data type of the indicated column in a result set.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns a string containing the defined data type of the specified column. If the specified column does not exist in the result set, IBM_DB::field_type() returns FALSE.

field_width(...)

IBM_DB::field_width — Returns the width of the current value of the indicated column in a result set

Description

int IBM_DB::field_width ( resource stmt, mixed column )

Returns the width of the current value of the indicated column in a result set. This is the maximum width of the column for a fixed-length data type, or the actual width of the column for a variable-length data type.

Parameters

stmt

    Specifies a statement resource containing a result set.

column

    Specifies the column in the result set. This can either be an integer representing the
    0-indexed position of the column, or a string containing the name of the column.

Return Values

Returns an integer containing the width of the specified character or binary data type column in a result set. If the specified column does not exist in the result set, IBM_DB::field_width() returns FALSE.

foreign_keys(...)

IBM_DB::foreign_keys — Returns a result set listing the foreign keys for a table

Description

resource IBM_DB::foreign_keys ( resource connection, string qualifier, string schema, string table-name )

Returns a result set listing the foreign keys for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL
    or an empty string.

schema

    The schema which contains the tables. If schema is NULL, IBM_DB::foreign_keys() matches the schema
    for the current connection.

table-name

    The name of the table.

Return Values

Returns a statement resource with a result set containing rows describing the foreign keys for the specified table. The result set is composed of the following columns:

Column name:	Description
PKTABLE_CAT:	Name of the catalog for the table containing the primary key. The value is NULL if this table does not have catalogs.
PKTABLE_SCHEM:	Name of the schema for the table containing the primary key.
PKTABLE_NAME:	Name of the table containing the primary key.
PKCOLUMN_NAME:	Name of the column containing the primary key.
FKTABLE_CAT:	Name of the catalog for the table containing the foreign key. The value is NULL if this table does not have catalogs.
FKTABLE_SCHEM:	Name of the schema for the table containing the foreign key.
FKTABLE_NAME:	Name of the table containing the foreign key.
FKCOLUMN_NAME:	Name of the column containing the foreign key.
KEY_SEQ:	1-indexed position of the column in the key.
UPDATE_RULE:	Integer value representing the action applied to the foreign key when the SQL operation is UPDATE.
DELETE_RULE:	Integer value representing the action applied to the foreign key when the SQL operation is DELETE.
FK_NAME:	The name of the foreign key.
PK_NAME:	The name of the primary key.
DEFERRABILITY:	An integer value representing whether the foreign key deferrability is SQL_INITIALLY_DEFERRED, SQL_INITIALLY_IMMEDIATE, or SQL_NOT_DEFERRABLE.

foreignkeys(...)

IBM_DB::foreign_keys — Returns a result set listing the foreign keys for a table

Description

resource IBM_DB::foreign_keys ( resource connection, string qualifier, string schema, string table-name )

Returns a result set listing the foreign keys for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL
    or an empty string.

schema

    The schema which contains the tables. If schema is NULL, IBM_DB::foreign_keys() matches the schema
    for the current connection.

table-name

    The name of the table.

Return Values

Returns a statement resource with a result set containing rows describing the foreign keys for the specified table. The result set is composed of the following columns:

Column name:	Description
PKTABLE_CAT:	Name of the catalog for the table containing the primary key. The value is NULL if this table does not have catalogs.
PKTABLE_SCHEM:	Name of the schema for the table containing the primary key.
PKTABLE_NAME:	Name of the table containing the primary key.
PKCOLUMN_NAME:	Name of the column containing the primary key.
FKTABLE_CAT:	Name of the catalog for the table containing the foreign key. The value is NULL if this table does not have catalogs.
FKTABLE_SCHEM:	Name of the schema for the table containing the foreign key.
FKTABLE_NAME:	Name of the table containing the foreign key.
FKCOLUMN_NAME:	Name of the column containing the foreign key.
KEY_SEQ:	1-indexed position of the column in the key.
UPDATE_RULE:	Integer value representing the action applied to the foreign key when the SQL operation is UPDATE.
DELETE_RULE:	Integer value representing the action applied to the foreign key when the SQL operation is DELETE.
FK_NAME:	The name of the foreign key.
PK_NAME:	The name of the primary key.
DEFERRABILITY:	An integer value representing whether the foreign key deferrability is SQL_INITIALLY_DEFERRED, SQL_INITIALLY_IMMEDIATE, or SQL_NOT_DEFERRABLE.

free_result(...)

IBM_DB::free_result — Frees resources associated with a result set

Description

bool IBM_DB::free_result ( resource stmt )

Frees the system and database resources that are associated with a result set. These resources are freed implicitly when a script finishes, but you can call IBM_DB::free_result() to explicitly free the result set resources before the end of the script.

Parameters

stmt

    A valid statement resource.

Return Values

Returns TRUE on success or FALSE on failure.

free_stmt(...)

IBM_DB::free_stmt — Frees resources associated with the indicated statement resource

Description

bool IBM_DB::free_stmt ( resource stmt )

Frees the system and database resources that are associated with a statement resource. These resources are freed implicitly when a script finishes, but you can call IBM_DB::free_stmt() to explicitly free the statement resources before the end of the script.

Parameters

stmt

    A valid statement resource.

Return Values

Returns TRUE on success or FALSE on failure.

DEPRECATED

get_last_serial_value(...)

IBM_DB::get_last_serial_value — Gets the last inserted serial value from IDS

Description

string IBM_DB::get_last_serial_value ( resource stmt )

Returns a string, that is the last inserted value for a serial column for IDS. The last inserted value could be auto-generated or entered explicitly by the user This function is valid for IDS (Informix Dynamic Server only)

Parameters

stmt

    A valid statement resource.

Return Values

Returns a string representation of last inserted serial value on a successful call. Returns FALSE on failure.

get_option(...)

IBM_DB::get_option — Gets the specified option in the resource.

Description

mixed IBM_DB::get_option ( resource resc, int options, int type )

Returns a value, that is the current setting of a connection or statement attribute.

Parameters

resc

    A valid connection or statement resource containing a result set.

options

    The options to be retrieved

type

    A field that specifies the resource type (1 = Connection, non - 1 = Statement)

Return Values

Returns the current setting of the resource attribute provided.

getoption(...)

IBM_DB::get_option — Gets the specified option in the resource.

Description

mixed IBM_DB::get_option ( resource resc, int options, int type )

Returns a value, that is the current setting of a connection or statement attribute.

Parameters

resc

    A valid connection or statement resource containing a result set.

options

    The options to be retrieved

type

    A field that specifies the resource type (1 = Connection, non - 1 = Statement)

Return Values

Returns the current setting of the resource attribute provided.

next_result(...)

IBM_DB::next_result — Requests the next result set from a stored procedure

Description

resource IBM_DB::next_result ( resource stmt )

A stored procedure can return zero or more result sets. While you handle the first result set in exactly the same way you would handle the results returned by a simple SELECT statement, to fetch the second and subsequent result sets from a stored procedure you must call the IBM_DB::next_result() function and return the result to a uniquely named Ruby variable.

Parameters

stmt

    A prepared statement returned from IBM_DB::exec() or IBM_DB::execute().

Return Values

Returns a new statement resource containing the next result set if the stored procedure returned another result set. Returns FALSE if the stored procedure did not return another result set.

num_fields(...)

IBM_DB::num_fields — Returns the number of fields contained in a result set

Description

int IBM_DB::num_fields ( resource stmt )

Returns the number of fields contained in a result set. This is most useful for handling the result sets returned by dynamically generated queries, or for result sets returned by stored procedures, where your application cannot otherwise know how to retrieve and use the results.

Parameters

stmt

    A valid statement resource containing a result set.

Return Values

Returns an integer value representing the number of fields in the result set associated with the specified statement resource. Returns FALSE if the statement resource is not a valid input value.

num_rows(...)

IBM_DB::num_rows — Returns the number of rows affected by an SQL statement

Description

int IBM_DB::num_rows ( resource stmt )

Returns the number of rows deleted, inserted, or updated by an SQL statement.

To determine the number of rows that will be returned by a SELECT statement, issue SELECT COUNT(*) with the same predicates as your intended SELECT statement and retrieve the value. If your application logic checks the number of rows returned by a SELECT statement and branches if the number of rows is 0, consider modifying your application to attempt to return the first row with one of IBM_DB::fetch_assoc(), IBM_DB::fetch_both(), IBM_DB::fetch_array(), or IBM_DB::fetch_row(), and branch if the fetch function returns FALSE.

Note: If you issue a SELECT statement using a scrollable cursor, IBM_DB::num_rows() returns the number of rows returned by the SELECT statement. However, the overhead associated with scrollable cursors significantly degrades the performance of your application, so if this is the only reason you are considering using scrollable cursors, you should use a forward-only cursor and either call SELECT COUNT(*) or rely on the boolean return value of the fetch functions to achieve the equivalent functionality with much better performance.

Parameters

stmt

    A valid stmt resource containing a result set.

Return Values

Returns the number of rows affected by the last SQL statement issued by the specified statement handle.

pconnect(...)

IBM_DB::pconnect — Returns a persistent connection to a database

Description

resource IBM_DB::pconnect ( string database, string username, string password [, array options] )

Returns a persistent connection to an IBM DB2 Universal Database, IBM Cloudscape, or Apache Derby database.

Calling IBM_DB::close() on a persistent connection always returns TRUE, but the underlying DB2 client connection remains open and waiting to serve the next matching IBM_DB::pconnect() request.

Parameters

database

    The database alias in the DB2 client catalog.

username

    The username with which you are connecting to the database.

password

    The password with which you are connecting to the database.

options

    An associative array of connection options that affect the behavior of the connection,
    where valid array keys include:

    autocommit
        Passing the DB2_AUTOCOMMIT_ON value turns autocommit on for this connection handle.
        Passing the DB2_AUTOCOMMIT_OFF value turns autocommit off for this connection handle.

    DB2_ATTR_CASE
        Passing the DB2_CASE_NATURAL value specifies that column names are returned in natural case.
        Passing the DB2_CASE_LOWER value specifies that column names are returned in lower case.
        Passing the DB2_CASE_UPPER value specifies that column names are returned in upper case.

    CURSOR
        Passing the SQL_SCROLL_FORWARD_ONLY value specifies a forward-only cursor for a statement resource.
        This is the default cursor type and is supported on all database servers.
        Passing the SQL_CURSOR_KEYSET_DRIVEN value specifies a scrollable cursor for a statement resource.
        This mode enables random access to rows in a result set, but currently is supported only
        by IBM DB2 Universal Database.

Return Values

Returns a connection handle resource if the connection attempt is successful. IBM_DB::pconnect() tries to reuse an existing connection resource that exactly matches the database, username, and password parameters. If the connection attempt fails, IBM_DB::pconnect() returns FALSE.

prepare(...)

IBM_DB::prepare — Prepares an SQL statement to be executed

Description

resource IBM_DB::prepare ( resource connection, string statement [, array options] )

IBM_DB::prepare() creates a prepared SQL statement which can include 0 or more parameter markers (? characters) representing parameters for input, output, or input/output. You can pass parameters to the prepared statement using IBM_DB::bind_param(), or for input values only, as an array passed to IBM_DB::execute().

There are three main advantages to using prepared statements in your application:

    * Performance: when you prepare a statement, the database server creates an optimized access plan
      for retrieving data with that statement. Subsequently issuing the prepared statement with
      IBM_DB::execute() enables the statements to reuse that access plan and avoids the overhead of dynamically
      creating a new access plan for every statement you issue.
    * Security: when you prepare a statement, you can include parameter markers for input values.
      When you execute a prepared statement with input values for placeholders, the database server checks
      each input value to ensure that the type matches the column definition or parameter definition.
    * Advanced functionality: Parameter markers not only enable you to pass input values to prepared
      SQL statements, they also enable you to retrieve OUT and INOUT parameters from stored procedures
      using IBM_DB::bind_param().

Parameters

connection

    A valid database connection resource variable as returned from IBM_DB::connect() or IBM_DB::pconnect().

statement

    An SQL statement, optionally containing one or more parameter markers..

options

    An associative array containing statement options. You can use this parameter to request a
    scrollable cursor on database servers that support this functionality.

    cursor
        Passing the SQL_SCROLL_FORWARD_ONLY value requests a forward-only cursor for this SQL statement.
        This is the default type of cursor, and it is supported by all database servers. It is also
        much faster than a scrollable cursor.
        Passing the SQL_CURSOR_KEYSET_DRIVEN value requests a scrollable cursor for this SQL statement.
        This type of cursor enables you to fetch rows non-sequentially from the database server.
        However, it is only supported by DB2 servers, and is much slower than forward-only cursors.

Return Values

Returns a statement resource if the SQL statement was successfully parsed and prepared by the database server. Returns FALSE if the database server returned an error. You can determine which error was returned by calling IBM_DB::stmt_error() or IBM_DB::stmt_errormsg().

primary_keys(...)

IBM_DB::primary_keys — Returns a result set listing primary keys for a table

Description

resource IBM_DB::primary_keys ( resource connection, string qualifier, string schema, string table-name )

Returns a result set listing the primary keys for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables. If schema is NULL, IBM_DB::primary_keys() matches the schema for the current connection.

table-name

    The name of the table.

Return Values

Returns a statement resource with a result set containing rows describing the primary keys for the specified table. The result set is composed of the following columns:

Column name:	Description
TABLE_CAT:	Name of the catalog for the table containing the primary key. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema for the table containing the primary key.
TABLE_NAME:	Name of the table containing the primary key.
COLUMN_NAME:	Name of the column containing the primary key.
KEY_SEQ:	1-indexed position of the column in the key.
PK_NAME:	The name of the primary key.

primarykeys(...)

IBM_DB::primary_keys — Returns a result set listing primary keys for a table

Description

resource IBM_DB::primary_keys ( resource connection, string qualifier, string schema, string table-name )

Returns a result set listing the primary keys for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables. If schema is NULL, IBM_DB::primary_keys() matches the schema for the current connection.

table-name

    The name of the table.

Return Values

Returns a statement resource with a result set containing rows describing the primary keys for the specified table. The result set is composed of the following columns:

Column name:	Description
TABLE_CAT:	Name of the catalog for the table containing the primary key. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema for the table containing the primary key.
TABLE_NAME:	Name of the table containing the primary key.
COLUMN_NAME:	Name of the column containing the primary key.
KEY_SEQ:	1-indexed position of the column in the key.
PK_NAME:	The name of the primary key.

procedure_columns(...)

IBM_DB::procedure_columns — Returns a result set listing stored procedure parameters

Description

resource IBM_DB::procedure_columns ( resource connection, string qualifier, string schema, string procedure, string parameter )

Returns a result set listing the parameters for one or more stored procedures.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the procedures. This parameter accepts a search pattern containing _ and % as wildcards.

procedure

    The name of the procedure. This parameter accepts a search pattern containing _ and % as wildcards.

parameter

    The name of the parameter. This parameter accepts a search pattern containing _ and % as wildcards.
    If this parameter is NULL, all parameters for the specified stored procedures are returned.

Return Values

Returns a statement resource with a result set containing rows describing the parameters for the stored procedures matching the specified parameters. The rows are composed of the following columns:

Column name:

Description

PROCEDURE_CAT:

The catalog that contains the procedure. The value is NULL if this table does not have catalogs.

PROCEDURE_SCHEM:

Name of the schema that contains the stored procedure.

PROCEDURE_NAME:

Name of the procedure.

COLUMN_NAME:

Name of the parameter.

COLUMN_TYPE:

An integer value representing the type of the parameter:

Return value:	Parameter type
1:	(SQL_PARAM_INPUT) Input (IN) parameter.
2:	(SQL_PARAM_INPUT_OUTPUT) Input/output (INOUT) parameter.
3:	(SQL_PARAM_OUTPUT) Output (OUT) parameter.

DATA_TYPE:

The SQL data type for the parameter represented as an integer value.

TYPE_NAME:

A string representing the data type for the parameter.

COLUMN_SIZE:

An integer value representing the size of the parameter.

BUFFER_LENGTH:

Maximum number of bytes necessary to store data for this parameter.

DECIMAL_DIGITS:

The scale of the parameter, or NULL where scale is not applicable.

NUM_PREC_RADIX:

An integer value of either 10 (representing an exact numeric data type), 2 (representing anapproximate numeric data type), or NULL (representing a data type for which radix is not applicable).

NULLABLE:

An integer value representing whether the parameter is nullable or not.

REMARKS:

Description of the parameter.

COLUMN_DEF:

Default value for the parameter.

SQL_DATA_TYPE:

An integer value representing the size of the parameter.

SQL_DATETIME_SUB:

Returns an integer value representing a datetime subtype code, or NULL for SQL data types to which this does not apply.

CHAR_OCTET_LENGTH:

Maximum length in octets for a character data type parameter, which matches COLUMN_SIZE for single-byte character set data, or NULL for non-character data types.

ORDINAL_POSITION:

The 1-indexed position of the parameter in the CALL statement.

IS_NULLABLE:

A string value where ‘YES’ means that the parameter accepts or returns NULL values and ‘NO’ means that the parameter does not accept or return NULL values.

procedurecolumns(...)

IBM_DB::procedure_columns — Returns a result set listing stored procedure parameters

Description

resource IBM_DB::procedure_columns ( resource connection, string qualifier, string schema, string procedure, string parameter )

Returns a result set listing the parameters for one or more stored procedures.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the procedures. This parameter accepts a search pattern containing _ and % as wildcards.

procedure

    The name of the procedure. This parameter accepts a search pattern containing _ and % as wildcards.

parameter

    The name of the parameter. This parameter accepts a search pattern containing _ and % as wildcards.
    If this parameter is NULL, all parameters for the specified stored procedures are returned.

Return Values

Returns a statement resource with a result set containing rows describing the parameters for the stored procedures matching the specified parameters. The rows are composed of the following columns:

Column name:

Description

PROCEDURE_CAT:

The catalog that contains the procedure. The value is NULL if this table does not have catalogs.

PROCEDURE_SCHEM:

Name of the schema that contains the stored procedure.

PROCEDURE_NAME:

Name of the procedure.

COLUMN_NAME:

Name of the parameter.

COLUMN_TYPE:

An integer value representing the type of the parameter:

Return value:	Parameter type
1:	(SQL_PARAM_INPUT) Input (IN) parameter.
2:	(SQL_PARAM_INPUT_OUTPUT) Input/output (INOUT) parameter.
3:	(SQL_PARAM_OUTPUT) Output (OUT) parameter.

DATA_TYPE:

The SQL data type for the parameter represented as an integer value.

TYPE_NAME:

A string representing the data type for the parameter.

COLUMN_SIZE:

An integer value representing the size of the parameter.

BUFFER_LENGTH:

Maximum number of bytes necessary to store data for this parameter.

DECIMAL_DIGITS:

The scale of the parameter, or NULL where scale is not applicable.

NUM_PREC_RADIX:

An integer value of either 10 (representing an exact numeric data type), 2 (representing anapproximate numeric data type), or NULL (representing a data type for which radix is not applicable).

NULLABLE:

An integer value representing whether the parameter is nullable or not.

REMARKS:

Description of the parameter.

COLUMN_DEF:

Default value for the parameter.

SQL_DATA_TYPE:

An integer value representing the size of the parameter.

SQL_DATETIME_SUB:

Returns an integer value representing a datetime subtype code, or NULL for SQL data types to which this does not apply.

CHAR_OCTET_LENGTH:

Maximum length in octets for a character data type parameter, which matches COLUMN_SIZE for single-byte character set data, or NULL for non-character data types.

ORDINAL_POSITION:

The 1-indexed position of the parameter in the CALL statement.

IS_NULLABLE:

A string value where ‘YES’ means that the parameter accepts or returns NULL values and ‘NO’ means that the parameter does not accept or return NULL values.

procedures(...)

IBM_DB::procedures — Returns a result set listing the stored procedures registered in a database

Description

resource IBM_DB::procedures ( resource connection, string qualifier, string schema, string procedure )

Returns a result set listing the stored procedures registered in a database.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the procedures. This parameter accepts a search pattern containing _ and % as wildcards.

procedure

    The name of the procedure. This parameter accepts a search pattern containing _ and % as wildcards.

Return Values

Returns a statement resource with a result set containing rows describing the stored procedures matching the specified parameters. The rows are composed of the following columns:

Column name:	Description
PROCEDURE_CAT:	The catalog that contains the procedure. The value is NULL if this table does not have catalogs.
PROCEDURE_SCHEM:	Name of the schema that contains the stored procedure.
PROCEDURE_NAME:	Name of the procedure.
NUM_INPUT_PARAMS:	Number of input (IN) parameters for the stored procedure.
NUM_OUTPUT_PARAMS:	Number of output (OUT) parameters for the stored procedure.
NUM_RESULT_SETS:	Number of result sets returned by the stored procedure.
REMARKS:	Any comments about the stored procedure.
PROCEDURE_TYPE:	Always returns 1, indicating that the stored procedure does not return a return value.

result(...)

IBM_DB::result — Returns a single column from a row in the result set

Description

mixed IBM_DB::result ( resource stmt, mixed column )

Use IBM_DB::result() to return the value of a specified column in the current row of a result set. You must call IBM_DB::fetch_row() before calling IBM_DB::result() to set the location of the result set pointer.

Parameters

stmt

    A valid stmt resource.

column

    Either an integer mapping to the 0-indexed field in the result set, or a string matching the name of the column.

Return Values

Returns the value of the requested field if the field exists in the result set. Returns NULL if the field does not exist, and issues a warning.

rollback(...)

IBM_DB::rollback — Rolls back a transaction

Description

bool IBM_DB::rollback ( resource connection )

Rolls back an in-progress transaction on the specified connection resource and begins a new transaction. Ruby applications normally default to AUTOCOMMIT mode, so IBM_DB::rollback() normally has no effect unless AUTOCOMMIT has been turned off for the connection resource.

Note: If the specified connection resource is a persistent connection, all transactions in progress for all applications using that persistent connection will be rolled back. For this reason, persistent connections are not recommended for use in applications that require transactions.

Parameters

connection

    A valid database connection resource variable as returned from IBM_DB::connect() or IBM_DB::pconnect().

Return Values

Returns TRUE on success or FALSE on failure.

server_info(...)

IBM_DB::server_info — Returns an object with properties that describe the DB2 database server

Description

object IBM_DB::server_info ( resource connection )

This function returns a read-only object with information about the IBM DB2, Cloudscape, or Apache Derby database server. The following table lists the database server properties:

Table 1. Database server properties

Property name:

Description (Return type)

DBMS_NAME:

The name of the database server to which you are connected. For DB2 servers this is a combination of DB2 followed by the operating system on which the database server is running. (string)

DBMS_VER:

The version of the database server, in the form of a string "MM.mm.uuuu" where MM is the major version, mm is the minor version, and uuuu is the update. For example, "08.02.0001" represents major version 8, minor version 2, update 1. (string)

DB_CODEPAGE:

The code page of the database to which you are connected. (int)

DB_NAME:

The name of the database to which you are connected. (string)

DFT_ISOLATION:

The default transaction isolation level supported by the server: (string)

UR:	Uncommitted read: changes are immediately visible by all concurrent transactions.
CS:	Cursor stability: a row read by one transaction can be altered and committed by a second concurrent transaction.
RS:	Read stability: a transaction can add or remove rows matching a search condition or a pending transaction.
RR:	Repeatable read: data affected by pending transaction is not available to other transactions.
NC:	No commit: any changes are visible at the end of a successful operation. Explicit commits and rollbacks are not allowed.

IDENTIFIER_QUOTE_CHAR:

The character used to delimit an identifier. (string)

INST_NAME:

The instance on the database server that contains the database. (string)

ISOLATION_OPTION:

An array of the isolation options supported by the database server. The isolation options are described in the DFT_ISOLATION property. (array)

KEYWORDS:

An array of the keywords reserved by the database server. (array)

LIKE_ESCAPE_CLAUSE:

TRUE if the database server supports the use of % and _ wildcard characters. FALSE if the database server does not support these wildcard characters. (bool)

MAX_COL_NAME_LEN:

Maximum length of a column name supported by the database server, expressed in bytes. (int)

MAX_IDENTIFIER_LEN:

Maximum length of an SQL identifier supported by the database server, expressed in characters. (int)

MAX_INDEX_SIZE:

Maximum size of columns combined in an index supported by the database server, expressed in bytes. (int)

MAX_PROC_NAME_LEN:

Maximum length of a procedure name supported by the database server, expressed in bytes. (int)

MAX_ROW_SIZE:

Maximum length of a row in a base table supported by the database server, expressed in bytes. (int)

MAX_SCHEMA_NAME_LEN:

Maximum length of a schema name supported by the database server, expressed in bytes. (int)

MAX_STATEMENT_LEN:

Maximum length of an SQL statement supported by the database server, expressed in bytes. (int)

MAX_TABLE_NAME_LEN:

Maximum length of a table name supported by the database server, expressed in bytes. (bool)

NON_NULLABLE_COLUMNS:

TRUE if the database server supports columns that can be defined as NOT NULL, FALSE if the database server does not support columns defined as NOT NULL. (bool)

PROCEDURES:

TRUE if the database server supports the use of the CALL statement to call stored procedures, FALSE if the database server does not support the CALL statement. (bool)

SPECIAL_CHARS:

A string containing all of the characters other than a-Z, 0-9, and underscore that can be used in an identifier name. (string)

SQL_CONFORMANCE:

The level of conformance to the ANSI/ISO SQL-92 specification offered by the database server: (string)

ENTRY:	Entry-level SQL-92 compliance.
FIPS127:	FIPS-127-2 transitional compliance.
FULL:	Full level SQL-92 compliance.
INTERMEDIATE:	Intermediate level SQL-92 compliance.

Parameters

connection

    Specifies an active DB2 client connection.

Return Values

Returns an object on a successful call. Returns FALSE on failure.

set_option(...)

IBM_DB::set_option — Sets the specified option in the resource.

Description

bool IBM_DB::set_option ( resource resc, array options, int type )

Sets options for a connection or statement resource. You cannot set options for result set resources.

Parameters

resc

    A valid connection or statement resource.

options

    The options to be set

type

    A field that specifies the resource type (1 = Connection, NON-1 = Statement)

Return Values

Returns TRUE on success or FALSE on failure

setoption(...)

IBM_DB::set_option — Sets the specified option in the resource.

Description

bool IBM_DB::set_option ( resource resc, array options, int type )

Sets options for a connection or statement resource. You cannot set options for result set resources.

Parameters

resc

    A valid connection or statement resource.

options

    The options to be set

type

    A field that specifies the resource type (1 = Connection, NON-1 = Statement)

Return Values

Returns TRUE on success or FALSE on failure

special_columns(...)

IBM_DB::special_columns — Returns a result set listing the unique row identifier columns for a table

Description

resource IBM_DB::special_columns ( resource connection, string qualifier, string schema, string table_name, int scope )

Returns a result set listing the unique row identifier columns for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables.

table_name

    The name of the table.

scope

    Integer value representing the minimum duration for which the unique row identifier is valid. This can be one of the following values:

    0: Row identifier is valid only while the cursor is positioned on the row. (SQL_SCOPE_CURROW)
    1: Row identifier is valid for the duration of the transaction. (SQL_SCOPE_TRANSACTION)
    2: Row identifier is valid for the duration of the connection. (SQL_SCOPE_SESSION)

Return Values

Returns a statement resource with a result set containing rows with unique row identifier information for a table. The rows are composed of the following columns:

Column name:	Description
SCOPE:	Integer value representing the minimum duration for which the unique row identifier is valid. 0: Row identifier is valid only while the cursor is positioned on the row. (SQL_SCOPE_CURROW) 1: Row identifier is valid for the duration of the transaction. (SQL_SCOPE_TRANSACTION) 2: Row identifier is valid for the duration of the connection. (SQL_SCOPE_SESSION)
COLUMN_NAME:	Name of the unique column.
DATA_TYPE:	SQL data type for the column.
TYPE_NAME:	Character string representation of the SQL data type for the column.
COLUMN_SIZE:	An integer value representing the size of the column.
BUFFER_LENGTH:	Maximum number of bytes necessary to store data from this column.
DECIMAL_DIGITS:	The scale of the column, or NULL where scale is not applicable.
NUM_PREC_RADIX:	An integer value of either 10 (representing an exact numeric data type),2 (representing an approximate numeric data type), or NULL (representing a data type for which radix is not applicable).
PSEUDO_COLUMN:	Always returns 1.

specialcolumns(...)

IBM_DB::special_columns — Returns a result set listing the unique row identifier columns for a table

Description

resource IBM_DB::special_columns ( resource connection, string qualifier, string schema, string table_name, int scope )

Returns a result set listing the unique row identifier columns for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables.

table_name

    The name of the table.

scope

    Integer value representing the minimum duration for which the unique row identifier is valid. This can be one of the following values:

    0: Row identifier is valid only while the cursor is positioned on the row. (SQL_SCOPE_CURROW)
    1: Row identifier is valid for the duration of the transaction. (SQL_SCOPE_TRANSACTION)
    2: Row identifier is valid for the duration of the connection. (SQL_SCOPE_SESSION)

Return Values

Returns a statement resource with a result set containing rows with unique row identifier information for a table. The rows are composed of the following columns:

Column name:	Description
SCOPE:	Integer value representing the minimum duration for which the unique row identifier is valid. 0: Row identifier is valid only while the cursor is positioned on the row. (SQL_SCOPE_CURROW) 1: Row identifier is valid for the duration of the transaction. (SQL_SCOPE_TRANSACTION) 2: Row identifier is valid for the duration of the connection. (SQL_SCOPE_SESSION)
COLUMN_NAME:	Name of the unique column.
DATA_TYPE:	SQL data type for the column.
TYPE_NAME:	Character string representation of the SQL data type for the column.
COLUMN_SIZE:	An integer value representing the size of the column.
BUFFER_LENGTH:	Maximum number of bytes necessary to store data from this column.
DECIMAL_DIGITS:	The scale of the column, or NULL where scale is not applicable.
NUM_PREC_RADIX:	An integer value of either 10 (representing an exact numeric data type),2 (representing an approximate numeric data type), or NULL (representing a data type for which radix is not applicable).
PSEUDO_COLUMN:	Always returns 1.

statistics(...)

IBM_DB::statistics — Returns a result set listing the index and statistics for a table

Description

resource IBM_DB::statistics ( resource connection, string qualifier, string schema, string table-name, bool unique )

Returns a result set listing the index and statistics for a table.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema that contains the targeted table. If this parameter is NULL, the statistics and indexes are
    returned for the schema of the current user.

table_name

    The name of the table.

unique

    An boolean value representing the type of index information to return.

    False    Return only the information for unique indexes on the table.

    True     Return the information for all indexes on the table.

Return Values

Returns a statement resource with a result set containing rows describing the statistics and indexes for the base tables matching the specified parameters. The rows are composed of the following columns:

Column name:

Description

TABLE_CAT:

The catalog that contains the table. The value is NULL if this table does not have catalogs.

TABLE_SCHEM:

Name of the schema that contains the table.

TABLE_NAME:

Name of the table.

NON_UNIQUE:

An integer value representing whether the index prohibits unique values, or whether the row represents statistics on the table itself:

Return value:	Parameter type
0 (SQL_FALSE):	The index allows duplicate values.
1 (SQL_TRUE):	The index values must be unique.
NULL:	This row is statistics information for the table itself.

INDEX_QUALIFIER:

A string value representing the qualifier that would have to be prepended to INDEX_NAME to fully qualify the index.

INDEX_NAME:

A string representing the name of the index.

TYPE:

An integer value representing the type of information contained in this row of the result set:

Return value:	Parameter type
0 (SQL_TABLE_STAT):	The row contains statistics about the table itself.
1 (SQL_INDEX_CLUSTERED):	The row contains information about a clustered index.
2 (SQL_INDEX_HASH):	The row contains information about a hashed index.
3 (SQL_INDEX_OTHER):	The row contains information about a type of index that is neither clustered nor hashed.

ORDINAL_POSITION:

The 1-indexed position of the column in the index. NULL if the row contains statistics information about the table itself.

COLUMN_NAME:

The name of the column in the index. NULL if the row contains statistics information about the table itself.

ASC_OR_DESC:

A if the column is sorted in ascending order, D if the column is sorted in descending order, NULL if the row contains statistics information about the table itself.

CARDINALITY:

If the row contains information about an index, this column contains an integer value representing the number of unique values in the index. If the row contains information about the table itself, this column contains an integer value representing the number of rows in the table.

PAGES:

If the row contains information about an index, this column contains an integer value representing the number of pages used to store the index. If the row contains information about the table itself, this column contains an integer value representing the number of pages used to store the table.

FILTER_CONDITION:

Always returns NULL.

stmt_error(...)

IBM_DB::stmt_error — Returns a string containing the SQLSTATE returned by an SQL statement

Description

string IBM_DB::stmt_error ( [resource stmt] )

Returns a string containing the SQLSTATE value returned by an SQL statement.

If you do not pass a statement resource as an argument to IBM_DB::stmt_error(), the driver returns the SQLSTATE value associated with the last attempt to return a statement resource, for example, from IBM_DB::prepare() or IBM_DB::exec().

To learn what the SQLSTATE value means, you can issue the following command at a DB2 Command Line Processor prompt: db2 ’? sqlstate-value’. You can also call IBM_DB::stmt_errormsg() to retrieve an explicit error message and the associated SQLCODE value.

Parameters

stmt

    A valid statement resource.

Return Values

Returns a string containing an SQLSTATE value.

stmt_errormsg(...)

IBM_DB::stmt_errormsg — Returns a string containing the last SQL statement error message

Description

string IBM_DB::stmt_errormsg ( [resource stmt] )

Returns a string containing the last SQL statement error message.

If you do not pass a statement resource as an argument to IBM_DB::stmt_errormsg(), the driver returns the error message associated with the last attempt to return a statement resource, for example, from IBM_DB::prepare() or IBM_DB::exec().

Parameters

stmt

    A valid statement resource.

Return Values

Returns a string containing the error message and SQLCODE value for the last error that occurred issuing an SQL statement.

table_privileges(...)

IBM_DB::table_privileges — Returns a result set listing the tables and associated privileges in a database

Description

resource IBM_DB::table_privileges ( resource connection [, string qualifier [, string schema [, string table_name]]] )

Returns a result set listing the tables and associated privileges in a database.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases,
    pass NULL or an empty string.

schema

    The schema which contains the tables. This parameter accepts a search pattern containing _
    and % as wildcards.

table_name

    The name of the table. This parameter accepts a search pattern containing _ and % as wildcards.

Return Values

Returns a statement resource with a result set containing rows describing the privileges for the tables that match the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	The catalog that contains the table. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema that contains the table.
TABLE_NAME:	Name of the table.
GRANTOR:	Authorization ID of the user who granted the privilege.
GRANTEE:	Authorization ID of the user to whom the privilege was granted.
PRIVILEGE:	The privilege that has been granted. This can be one of ALTER, CONTROL, DELETE, INDEX, INSERT, REFERENCES, SELECT, or UPDATE.
IS_GRANTABLE:	A string value of "YES" or "NO" indicating whether the grantee can grant the privilege to other users.

tableprivileges(...)

IBM_DB::table_privileges — Returns a result set listing the tables and associated privileges in a database

Description

resource IBM_DB::table_privileges ( resource connection [, string qualifier [, string schema [, string table_name]]] )

Returns a result set listing the tables and associated privileges in a database.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases,
    pass NULL or an empty string.

schema

    The schema which contains the tables. This parameter accepts a search pattern containing _
    and % as wildcards.

table_name

    The name of the table. This parameter accepts a search pattern containing _ and % as wildcards.

Return Values

Returns a statement resource with a result set containing rows describing the privileges for the tables that match the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	The catalog that contains the table. The value is NULL if this table does not have catalogs.
TABLE_SCHEM:	Name of the schema that contains the table.
TABLE_NAME:	Name of the table.
GRANTOR:	Authorization ID of the user who granted the privilege.
GRANTEE:	Authorization ID of the user to whom the privilege was granted.
PRIVILEGE:	The privilege that has been granted. This can be one of ALTER, CONTROL, DELETE, INDEX, INSERT, REFERENCES, SELECT, or UPDATE.
IS_GRANTABLE:	A string value of "YES" or "NO" indicating whether the grantee can grant the privilege to other users.

tables(...)

IBM_DB::tables — Returns a result set listing the tables and associated metadata in a database

Description

resource IBM_DB::tables ( resource connection [, string qualifier [, string schema [, string table-name [, string table-type]]]] )

Returns a result set listing the tables and associated metadata in a database.

Parameters

connection

    A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

qualifier

    A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

schema

    The schema which contains the tables. This parameter accepts a search pattern containing _ and % as wildcards.

table-name

    The name of the table. This parameter accepts a search pattern containing _ and % as wildcards.

table-type

    A list of comma-delimited table type identifiers. To match all table types, pass NULL or an empty string.
    Valid table type identifiers include: ALIAS, HIERARCHY TABLE, INOPERATIVE VIEW, NICKNAME, MATERIALIZED QUERY
    TABLE, SYSTEM TABLE, TABLE, TYPED TABLE, TYPED VIEW, and VIEW.

Return Values

Returns a statement resource with a result set containing rows describing the tables that match the specified parameters. The rows are composed of the following columns:

Column name:	Description
TABLE_CAT:	The catalog that contains the table. The value is NULL if this table does not have catalogs.
TABLE_SCHEMA:	Name of the schema that contains the table.
TABLE_NAME:	Name of the table.
TABLE_TYPE:	Table type identifier for the table.
REMARKS:	Description of the table.

Methods

Classes and Modules

Constants

Public Class methods

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Table 1. DB2 client properties

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

database

username

password

options

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters

Return Values

Description

Parameters