Ruby ODBC Reference

ODBC

The module to encapsulate the Ruby ODBC binding.

module functions:

datasources

Returns an array of ODBC::DSNs, i.e. all known data source names.

drivers

Returns an array of ODBC::Drivers, i.e. all known ODBC drivers.

error

Returns the last error messages (String array) or nil. The layout of the warning/error messages is described here. Retrieving this message as well as subsequent succeeding ODBC method invocations do not clear the message. Use the clear_error method for that purpose.

info

Returns the last driver/driver manager warning messages (String array) or nil. The layout of the warning/error messages is described here. Retrieving this message as well as subsequent succeeding ODBC method invocations do not clear the message. Use the clear_error method for that purpose.

clear_error

Resets the last driver/driver manager error and warning messages to nil.

raise(value)

Raises an ODBC::Error exception with String error message value.

newenv

Returns a new ODBC::Environment.

connection_pooling[=value]

Gets or sets the process-wide connection pooling attribute.

to_time(timestamp)

to_time(date,[time])

to_time(time,[date])

Creates a Time object from the specified arguments, which must be ODBC::Date, ODBC::Time, or ODBC::TimeStamp objects.

to_date(timestamp)

to_date(date)

Creates a Date object from the specified arguments, which must be ODBC::Date, or ODBC::TimeStamp objects.

connect(dsn,[user,passwd]) [{|dbc| block}]

If no block is specified, a connection to the given data source is established and a ODBC::Database object is returned, identifying that connection. Otherwise, the block is executed with the database object. When the block is finished, the connection is automatically released. Options are:

dsn: Data source name (String or ODBC::DSN)

user: Login user name (String)

passwd: Login password (String)

constants:

The boolean constant UTF8 reports the character encoding of the module. If it is true, the UTF-8 variant of the module is in use, and string data is automatically converted to/from Unicode.

Some constants of the ODBC API are defined in order to set connection options, to deal with SQL data types, and to obtain database meta data.

Cursor behaviour:

SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_KEYSET_DRIVEN, SQL_CURSOR_DYNAMIC, SQL_CURSOR_STATIC

Concurrency (transactions):

SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, SQL_CONCUR_VALUES

Fetch direction:

SQL_FETCH_NEXT, SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_PRIOR, SQL_FETCH_ABSOLUTE, SQL_FETCH_RELATIVE

Data types:

SQL_UNKNOWN_TYPE, SQL_CHAR, SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, SQL_VARCHAR, SQL_DATETIME, SQL_DATE, SQL_TYPE_DATE, SQL_TIME, SQL_TYPE_TIME, SQL_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_LONGVARCHAR, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY, SQL_BIGINT, SQL_TINYINT, SQL_BIT, SQL_GUID

Parameter related:

SQL_PARAM_TYPE_UNKNOWN, SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, SQL_PARAM_INPUT_OUTPUT, SQL_DEFAULT_PARAM SQL_RETURN_VALUE

Procedure related:

SQL_RESULT_COL, SQL_PT_UNKNOWN, SQL_PT_PROCEDURE, SQL_PT_FUNCTION

Environment attributes:

SQL_CP_OFF, SQL_CP_ONE_PER_DRIVER, SQL_CP_ONE_PER_HENV, SQL_CP_DEFAULT, SQL_CP_STRICT_MATCH, SQL_CP_RELAXED_MATCH, SQL_CP_MATCH_DEFAULT, SQL_OV_ODBC2, SQL_OV_ODBC3

Info types for ODBC::Database.get_info yielding integer results:

SQL_ACTIVE_ENVIRONMENTS, SQL_ACTIVE_CONNECTIONS, SQL_ACTIVE_STATEMENTS, SQL_ASYNC_MODE, SQL_CATALOG_LOCATION, SQL_CONCAT_NULL_BEHAVIOR, SQL_CORRELATION_NAME, SQL_CURSOR_COMMIT_BEHAVIOR, SQL_CURSOR_ROLLBACK_BEHAVIOR, SQL_CURSOR_SENSITIVITY, SQL_DDL_INDEX, SQL_DEFAULT_TXN_ISOLATION, SQL_DRIVER_HDBC, SQL_DRIVER_HENV, SQL_DRIVER_HDESC, SQL_DRIVER_HLIB, SQL_DRIVER_HSTMT, SQL_FILE_USAGE, SQL_GROUP_BY, SQL_IDENTIFIER_CASE, SQL_MAX_ASYNC_CONCURRENT_STATEMENTS, SQL_MAX_BINARY_LITERAL_LEN, SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_CHAR_LITERAL_LEN, SQL_MAX_COLUMN_NAME_LEN, SQL_MAX_COLUMNS_IN_GROUP_BY, SQL_MAX_COLUMNS_IN_INDEX, SQL_MAX_COLUMNS_IN_ORDER_BY, SQL_MAX_COLUMNS_IN_SELECT, SQL_MAX_COLUMNS_IN_TABLE, SQL_MAX_CONCURRENT_ACTIVITIES, SQL_MAX_CURSOR_NAME_LEN, SQL_MAX_DRIVER_CONNECTIONS, SQL_MAX_IDENTIFIER_LEN, SQL_MAX_INDEX_SIZE, SQL_MAX_OWNER_NAME_LEN, SQL_MAX_PROCEDURE_NAME_LEN, SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_ROW_SIZE, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_STATEMENT_LEN, SQL_MAX_TABLE_NAME_LEN, SQL_MAX_TABLES_IN_SELECT, SQL_MAX_USER_NAME_LEN, SQL_NON_NULLABLE_COLUMNS, SQL_NULL_COLLATION, SQL_ODBC_API_CONFORMANCE, SQL_ODBC_INTERFACE_CONFORMANCE, SQL_ODBC_SAG_CLI_CONFORMANCE, SQL_ODBC_SQL_CONFORMANCE, SQL_PARAM_ARRAY_ROW_COUNTS, SQL_PARAM_ARRAY_SELECTS, SQL_QUALIFIER_LOCATION, SQL_QUOTED_IDENTIFIER_CASE, SQL_SQL_CONFORMANCE, SQL_TXN_CAPABLE

Info types for ODBC::Database.get_info yielding bitmasks (integer results):

SQL_AGGREGATE_FUNCTIONS, SQL_ALTER_DOMAIN, SQL_ALTER_TABLE, SQL_BATCH_ROW_COUNT, SQL_BATCH_SUPPORT, SQL_BOOKMARK_PERSISTENCE, SQL_CATALOG_USAGE, SQL_CONVERT_BINARY, SQL_CONVERT_BIT, SQL_CONVERT_CHAR, SQL_CONVERT_GUID, SQL_CONVERT_DATE, SQL_CONVERT_DECIMAL, SQL_CONVERT_DOUBLE, SQL_CONVERT_FLOAT, SQL_CONVERT_FUNCTIONS, SQL_CONVERT_INTEGER, SQL_CONVERT_INTERVAL_YEAR_MONTH, SQL_CONVERT_INTERVAL_DAY_TIME, SQL_CONVERT_LONGVARBINARY, SQL_CONVERT_LONGVARCHAR, SQL_CONVERT_NUMERIC, SQL_CONVERT_REAL, SQL_CONVERT_SMALLINT, SQL_CONVERT_TIME, SQL_CONVERT_TIMESTAMP, SQL_CONVERT_TINYINT, SQL_CONVERT_VARBINARY, SQL_CONVERT_VARCHAR, SQL_CONVERT_WCHAR, SQL_CONVERT_WLONGVARCHAR, SQL_CONVERT_WVARCHAR, SQL_CREATE_ASSERTION, SQL_CREATE_CHARACTER_SET, SQL_CREATE_COLLATION, SQL_CREATE_DOMAIN, SQL_CREATE_SCHEMA, SQL_CREATE_TABLE, SQL_CREATE_TRANSLATION, SQL_CREATE_VIEW, SQL_DATETIME_LITERALS, SQL_DROP_ASSERTION, SQL_DROP_CHARACTER_SET, SQL_DROP_COLLATION, SQL_DROP_DOMAIN, SQL_DROP_SCHEMA, SQL_DROP_TABLE, SQL_DROP_TRANSLATION, SQL_DROP_VIEW, SQL_DTC_TRANSITION_COST, SQL_DYNAMIC_CURSOR_ATTRIBUTES1, SQL_DYNAMIC_CURSOR_ATTRIBUTES2, SQL_FETCH_DIRECTION, SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1, SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2, SQL_GETDATA_EXTENSIONS, SQL_KEYSET_CURSOR_ATTRIBUTES1, SQL_KEYSET_CURSOR_ATTRIBUTES2, SQL_INDEX_KEYWORDS, SQL_INFO_SCHEMA_VIEWS, SQL_INSERT_STATEMENT, SQL_LOCK_TYPES, SQL_NUMERIC_FUNCTIONS, SQL_OJ_CAPABILITIES, SQL_OWNER_USAGE, SQL_POS_OPERATIONS, SQL_POSITIONED_STATEMENTS, SQL_QUALIFIER_USAGE, SQL_SCHEMA_USAGE, SQL_SCROLL_CONCURRENCY, SQL_SCROLL_OPTIONS, SQL_SQL92_DATETIME_FUNCTIONS, SQL_SQL92_FOREIGN_KEY_DELETE_RULE, SQL_SQL92_FOREIGN_KEY_UPDATE_RULE, SQL_SQL92_GRANT, SQL_SQL92_NUMERIC_VALUE_FUNCTIONS, SQL_SQL92_PREDICATES, SQL_SQL92_RELATIONAL_JOIN_OPERATORS, SQL_SQL92_REVOKE, SQL_SQL92_ROW_VALUE_CONSTRUCTOR, SQL_SQL92_STRING_FUNCTIONS, SQL_SQL92_VALUE_EXPRESSIONS, SQL_STANDARD_CLI_CONFORMANCE, SQL_STATIC_CURSOR_ATTRIBUTES1, SQL_STATIC_CURSOR_ATTRIBUTES2, SQL_STATIC_SENSITIVITY, SQL_STRING_FUNCTIONS, SQL_SUBQUERIES, SQL_SYSTEM_FUNCTIONS, SQL_TIMEDATE_ADD_INTERVALS, SQL_TIMEDATE_DIFF_INTERVALS, SQL_TIMEDATE_FUNCTIONS, SQL_TXN_ISOLATION_OPTION, SQL_UNION

Info types for ODBC::Database.get_info yielding strings:

SQL_ACCESSIBLE_PROCEDURES, SQL_ACCESSIBLE_TABLES, SQL_CATALOG_NAME, SQL_CATALOG_NAME_SEPARATOR, SQL_CATALOG_TERM, SQL_COLLATION_SEQ, SQL_COLUMN_ALIAS, SQL_DATA_SOURCE_NAME, SQL_DATA_SOURCE_READ_ONLY, SQL_DATABASE_NAME, SQL_DBMS_NAME, SQL_DBMS_VER, SQL_DESCRIBE_PARAMETER, SQL_DM_VER, SQL_DRIVER_NAME, SQL_DRIVER_ODBC_VER, SQL_DRIVER_VER, SQL_EXPRESSIONS_IN_ORDERBY, SQL_IDENTIFIER_QUOTE_CHAR, SQL_INTEGRITY, SQL_KEYWORDS, SQL_LIKE_ESCAPE_CLAUSE, SQL_MAX_ROW_SIZE_INCLUDES_LONG, SQL_MULT_RESULT_SETS, SQL_MULTIPLE_ACTIVE_TXN, SQL_NEED_LONG_DATA_LEN, SQL_ODBC_SQL_OPT_IEF, SQL_ODBC_VER, SQL_ORDER_BY_COLUMNS_IN_SELECT, SQL_OUTER_JOINS, SQL_OWNER_TERM, SQL_PROCEDURE_TERM, SQL_PROCEDURES, SQL_QUALIFIER_NAME_SEPARATOR, SQL_QUALIFIER_TERM, SQL_ROW_UPDATES, SQL_SCHEMA_TERM, SQL_SEARCH_PATTERN_ESCAPE, SQL_SERVER_NAME, SQL_SPECIAL_CHARACTERS, SQL_TABLE_TERM, SQL_USER_NAME, SQL_XOPEN_CLI_YEAR

Options for ODBC::Database.get_option, ODBC::Database.set_option, ODBC::Statement.get_option, and ODBC::Statement.set_option yielding integers:

SQL_AUTOCOMMIT, SQL_CONCURRENCY, SQL_QUERY_TIMEOUT, SQL_MAX_ROWS, SQL_MAX_LENGTH, SQL_NOSCAN, SQL_ROWSET_SIZE, SQL_CURSOR_TYPE

ODBC::Object

The class to represent the root of all other ODBC related objects.

super class:

Object

methods:

error

Returns the last error message (String) or nil. For further information see ODBC::error.

info

Returns the last driver/driver manager warning messages (String array) or nil. For further information see ODBC::error.

clear_error

Resets the last driver/driver manager error and warning messages to nil.

raise(value)

Raises an ODBC::Error exception with String error message value.

singleton methods:

error

Returns the last error message (String array) or nil. For further information see ODBC::error.

info

Returns the last driver/driver manager warning messages (String array) or nil. For further information see ODBC::error.

clear_error

Resets the last driver/driver manager error and warning messages to nil.

raise(value)

Raises an ODBC::Error exception with String error message value.

ODBC::Environment

The class to represent the environment for ODBC::Databases.

super class:

ODBC::Object

methods:

connect(dsn,[user,passwd])

Connects to an ODBC data source and returns a ODBC::Database object. Options are:

dsn: Data source name (String or ODBC::DSN)

user: Login user name (String)

passwd: Login password (String)

environment

Returns the ODBC::Environment of the object.

commit

Commits the current transaction.

rollback

Rollbacks the current transaction.

transaction {|env| block}

First commits the current transaction, then executes the given block where the paramater is the object itself (an environment or a database connection). If the block raises an exception, the transaction is rolled back, otherwise committed.

connection_pooling[=value]

Gets or sets the connection pooling attribute of the environment.

cp_match[=value]

Gets or sets the connection pooling match attribute of the environment.

odbc_version[=value]

Gets or sets the ODBC version attribute of the environment.

singleton methods:

new

Returns a new ODBC::Environment object.

ODBC::Database

The class to represent a connection to an ODBC data source.

When underlying ODBC SQL functions report an error, an ODBC::Error exception with a corresponding error message from the ODBC driver manager and/or driver is raised.

super class:

ODBC::Environment

methods:

connected?

Returns true when the object is in connected state, false otherwise.

drvconnect(drv)

Connect to a data source specified by drv (ODBC::Driver).

disconnect(no_drop=false)

Disconnects from the data source, all active statements for the connection are dropped except when the no_drop argument is true. The method returns true when the connection was released, false otherwise.

newstmt

Returns a new ODBC::Statement object without preparing or executing a SQL statement. This allows to use ODBC::Statement.set_option to set special options on the statement before actual execution.

tables([pattern])

Returns an ODBC::Statement with information of all or some (pattern String given) tables of the data source.

columns([table[,column]])

Returns an ODBC::Statement with column information of the given table (String) and optional column (String).

indexes([table[,unique]])

Returns an ODBC::Statement with index information of the given table (String). If unique is given and true, information for unique indexes only is returned.

types([tcode])

Returns an ODBC::Statement with type information of the numeric SQL type tcode or all types if tcode is omitted.

primary_keys([table])

Returns an ODBC::Statement with primary key information of the given table (String).

foreign_keys([table])

Returns an ODBC::Statement with foreign key information of the given table (String).

table_privileges([table])

Returns an ODBC::Statement with owner/right information of the given table (String).

procedures([p])

Returns an ODBC::Statement with information about stored procedures.

procedure_columns([p])

Returns an ODBC::Statement with information about stored procedures.

special_columns([table[,id,scope]])

Returns an ODBC::Statement with column information of the given table (String) indicating the optimal unique or identifying columns. id may be specified as ODBC::SQL_BEST_ROWID or ODBC::SQL_ROW_VER. scope may be specified as ODBC::SQL_SCOPE_CURROW, ODBC::SQL_SCOPE_TRANSACTION, or ODBC::SQL_SCOPE_SESSION. If omitted the defaults are ODBC::SQL_BEST_ROWID and ODBC::SQL_SCOPE_CURROW.

get_info(info_type[,sql_type])

Retrieves database meta data according to info_type and returns numbers or strings. info_type may be specified as integer or string. To force interpretation of the return value of the underlying ODBC function SQLGetInfo() as a specific Ruby type the optional parameter sql_type can be given as e.g. ODBC::SQL_SMALLINT (yielding integer), ODBC::SQL_INTEGER (yielding integer), and ODBC::SQL_CHAR (yielding string).

run(sql[,args...])

Prepares and executes the query specified by sql with parameters bound from args and returns an ODBC::Statement or nil, if the query did not produce a result set, e.g. when executing SQL insert, delete, or update statements.

do(sql[,args...])

Prepares and executes the query as in run, but returns the number of result rows and automatically drops the statement. Useful for SQL insert, delete, or update statements.

prepare(sql)

Prepares the query specified by sql and returns an ODBC::Statement.

proc(sql,[type,size[,n=1]]) {|stmt| block}

Prepares the query specified by sql within a ODBCProc and returns that procedure. When the procedure is called, the statement is executed with the procedure's arguments bound to the statement's parameters and the statement is bound as parameter in the block, e.g.

# add customer given name and id
# and return number of affected rows

addcust = dbc.proc("insert into customer values(?, ?)") { |stmt|
    stmt.nrows
}

addcust.call("J.R. User", 9999)

# alternative to call: addcust["J.R. User", 9999]

The optional arguments type, size, and n, make the n-th statement parameter into an output parameter with that given ODBC data type and size. That statement parameter must be omitted when the ODBCProc is called. This can be useful for wrapping stored procedures, e.g.

# wrap statement with output parameter

aproc = dbc.proc("{ ? = call stored_proc(?) }", ODBC::SQL_INTEGER, 4) {}

# call this statement/procedure, output parameter is result

result = aproc.call(99)

# alternative to call: result = aproc[99]

Finalization of an ODBCProc can be done according to this code snippet

# aproc is wrapped statement, finalize it now

aproc.statement.drop

get_option(option)

Gets a connection level option. option can be a module constant, e.g. SQL_MAX_ROWS, a number, or a string. This method returns the integer value of that option.

set_option(option,intval)

Sets a connection level option. option can be a module constant, e.g. SQL_MAX_ROWS, a number, or a string. The second parameter intval can be used to set driver-specific statement options.

autocommit[=bool]

Sets or queries the autocommit option of the connection.

concurrency[=intval]

Sets or queries the concurrency mode of cursors opened on the connection.

maxrows[=intval]

Sets or queries the maximum rows returned in result sets from the connection.

timeout[=intval]

Sets or queries the number of seconds to wait for queries to execute on the connection before returning.

maxlength[=intval]

Sets or queries the maximum amount of data that will be returned from a character or binary column.

cursortype[=intval]

Sets or queries the cursor type used for fetches.

noscan[=bool]

Sets or queries whether the driver scans SQL strings for ODBC escape clauses.

rowsetsize

Returns the number of rows in a rowset fetched internally by a call o SQLExtendedFetch (hardcoded to 1).

ignorecase[=bool]

Sets or queries the uppercase conversion for column names. If turned on (bool is true), ODBC::Statements created by database methods will report column names in ODBC::Column or in the statement fetch methods as uppercase strings. Otherwise (the default) column names are passed unmodified.

drop_all

Releases the resources of all open ODBC::Statements in this database connection.

use_time[=bool]

Sets or queries the mapping of SQL_DATE, SQL_TIME, and SQL_TIMESTAMP data types to Ruby objects. When true, SQL_DATE is mapped to Ruby Date objects, SQL_TIME and SQL_TIMESTAMP are mapped to Ruby Time objects. Otherwise (default) ODBC::Date, ODBC::Time, and ODBC::TimeStamp are used.

use_utc[=bool]

Sets or queries the timezone applied on SQL_DATE, SQL_TIME, and SQL_TIMESTAMP data types to Ruby objects. When true, Ruby Date and Time objects are represented in UTC, when false (default) in the local timezone.

use_sql_column_name[=bool]

Sets or queries the flag controlling how column names are read from the data source. When false (default), the ODBC API SQLColAttributes(SQL_COLUMN_LABEL) is used, otherwise SQLColAttributes(SQL_COLUMN_NAME) is used.

singleton methods:

new

Creates an unconnected ODBC object, e.g. for a later drvconnect method call.

new(dsn,[user,passwd])

Connect to an ODBC data source. Options are:

dsn: Data source name (String or ODBC::DSN)

user: Login user name (String)

passwd: Login password (String)

Remarks:

The run, prepare, do, and info methods (e.g. tables) can be invoked with a block. In this case the block is executed with the ODBC::Statement as parameter. The run and do methods use the ODBC API function SQLExecDirect() when the SQL statement has no parameters.

ODBC::Statement

The class to represent a query and its query result. The object of this class is created as the result of every query. You may need to invoke the close or drop methods for the finished object for better memory performance.

When underlying ODBC SQL functions report an error, an ODBC::Error exception with a corresponding error message from the ODBC driver manager and/or driver is raised.

ODBC to Ruby type mapping:

ODBC	Ruby
SQL_SMALLINT, SQL_INTEGER, SQL_TINYINT, SQL_BIT	T_FIXNUM, T_BIGNUM
SQL_FLOAT, SQL_DOUBLE, SQL_REAL	T_FLOAT
SQL_DATE, SQL_TYPE_DATE	ODBC::Date or Date, see ODBC::Database.use_time
SQL_TIME, SQL_TYPE_TIME	ODBC::Time or Time, see ODBC::Database.use_time
SQL_TIMESTAMP, SQL_TYPE_TIMESTAMP	ODBC::TimeStamp or Time, see ODBC::Database.use_time
all others	T_STRING

super class:

ODBC::Database

mixins:

Enumerable

methods:

cancel

Cancel and close the ODBC::Statement.

close

Close the ODBC::Statement.

drop

Close and free the ODBC::Statement.

column(n)

Returns information of the n-th column of the query result as ODBC::Column.

columns(as_ary=false)

Returns a hash of column information keyed by column names (Strings) of the query result. If as_ary is true, an array is returned. In both cases the elements are ODBC::Columns. If the hash keys happen to be not unique later columns get their column number appended, e.g. FOOBAR, FOOBAR#1.

columns {|col| block}

Iterates over the columns of the query result with col bound to each ODBC::Column.

ncols

Returns the number of columns of the query result.

nrows

Returns the number of rows of the query result.

cursorname[=name]

Returns or sets the cursor name of the statement.

ignorecase[=bool]

Same as ODBC::Database.ignorecase but affecting this statement only. Inherited by the current state of the ODBC::Database at the time the statement is created.

fetch

Returns the next row of the query result as an array.

fetch_first

Returns the first row of the query result as an array.

fetch_scroll(direction, offset=1)

Returns the row addressed by direction, e.g. SQL_FETCH_LAST as an array. offset is used for SQL_FETCH_RELATIVE and SQL_FETCH_ABSOLUTE.

fetch_many(count)

Returns an array of the next count rows of the query result, where each row is an array.

fetch_all

Same as fetch_many except that all remaining rows are returned.

fetch_hash(with_table_names=false,use_sym=false)

Returns the next row of the query result as a hash keyed by column names. If with_table_names is true, the keys are combined table/column names. For uniqueness of keys the same rules as in the columns method are applied. If use_sym is true, the keys are formed by intern'ing the (table and) column names. Alternatively, one hash argument can be presented to fetch_hash, e.g. { :key => :Symbol, :table_names => false } or { :key => :String, :table_names => true } or { :key => :Fixnum }.

each {|row| block}

Iterates over the query result, performing a fetch for each row.

each_hash(with_table_names=false,use_sym=false) {|row| block}

Iterates over the query result, performing a fetch_hash for each row. The same rules for arguments as in fetch_hash apply.

execute([args...])

Binds args to current query and executes it.

run(sql[,args...])

Prepares and executes the query specified by sql with parameters bound from args.

prepare(sql)

Prepares the query specified by sql.

more_results

Returns true and switches over to the next result set, if the query produced more than one result set. Otherwise returns false.

get_option(option)

Gets a statement level option. option can be a module constant, e.g. SQL_MAX_ROWS, a number, or a string. This method returns the integer value of that option.

set_option(option,intval)

Sets a statement level option. option can be a module constant, e.g. SQL_MAX_ROWS, a number, or a string. The second parameter intval can be used to set driver-specific statement options.

concurrency[=intval]

Sets or queries the concurrency mode of cursors opened on the statement.

maxrows[=intval]

Sets or queries the maximum rows returned by the statement.

timeout[=intval]

Sets or queries the number of seconds to wait for the statement to execute before returning.

maxlength[=intval]

Sets or queries the maximum amount of data that will be returned from a character or binary column.

cursortype[=intval]

Sets or queries the cursor type used for fetches.

noscan[=bool]

Sets or queries whether the driver scans SQL strings for ODBC escape clauses.

rowsetsize

Returns the number of rows in a rowset fetched internally by a call to SQLExtendedFetch (hardcoded to 1).

nparams

Returns the number of parameters of the statement.

parameter(n)

Returns information of the n-th parameter of the query as ODBC::Parameter.

parameters

Returns an array of ODBC::Parameters describing all parameters of the query.

param_type(n[,type,coldef,scale])

Allows to override the type of parameter n and returns the current type. This can be useful when the ODBC driver does not provide proper type information on SQLDescribeParam.

param_iotype(n[,iotype])

Allows to change the input/output type of parameter n and returns the current input/output type. By default, all parameters are ODBC::SQL_PARAM_INPUT. When calling a stored procedure in the statement, the output parameter must be altered using this method, e.g.

# assume 1st parameter is input, 2nd is output
stmt = conn.prepare("call stored_proc( ?, ?)")
# setup 2nd for output of an integer
stmt.param_iotype(2, ODBC::SQL_PARAM_OUTPUT)
stmt.param_output_type(2, ODBC::SQL_INTEGER)
stmt.param_output_size(2, 4)
# execute stmt and retrieve value of output parameter
stmt.execute(42, nil)
stmt.fetch_all
out_value = stmt.param_output_value(2);

param_output_type(n[,type])

Allows to change the SQL type of output parameter n and returns the current type. For further information see the sample code in the param_iotype method.

param_output_size(n[,size])

Allows to change the byte size of the buffer for output parameter n and returns the current size. For further information see the sample code in the param_iotype method.

param_output_value(n)

Returns the value of the output parameter n. For further information see the sample code in the param_iotype method.

make_proc([n])

Wraps the statement into a ODBCProc object and returns that object. The optional n argument is the parameter number of the statement which is used as output parameter in the wrapped ODBCProc. For further information refer to the samples in the description of ODBC::Database.proc.

singleton methods:

make_proc(stmt,[n])

Wraps the statement stmt into a ODBCProc object and returns that object. The optional n argument is the parameter number of the statement which is used as output parameter in the wrapped ODBCProc. For further information refer to the samples in the description of ODBC::Database.proc.

Remarks:

The fetch, fetch_hash, and execute methods can be invoked with a block. In this case the block is executed with one row of the result set (fetch and fetch_hash) or with the ODBC::Statement (execute) as parameter.

If the ignorecase option is turned on, all column names used in the column, columns, and *_hash methods are converted to upper case.

ODBC::Column

The class to represent (read-only) information of a column of a query. Objects of this class are created as result of the column and columns methods of ODBC::Statement.

super class:

ODBC::Object

methods:

name

Returns the column name (String).

table

Returns the table name (String).

length

Returns the length of the column (Integer).

nullable

Returns the nullable state of the column (Boolean).

searchable

Returns the searchable state of the column (Boolean).

unsigned

Returns the unsigned flag of the column (Boolean).

precision

Returns the precision of the column (Integer).

scale

Returns the scale of the column (Integer).

type

Returns the SQL type of the column (Integer).

autoincrement

Returns true if the column automatically increments, false, if not, and nil if that information cannot be determined from the column.

ODBC::Parameter

The class to represent (read-only) information of a parameter of a query. Objects of this class are created as result of the parameter and parameters methods of ODBC::Statement.

super class:

ODBC::Object

methods:

type

Returns the parameter's type, e.g. ODBC::SQL_CHAR.

precision

Returns the parameter's precision (Integer).

length

Returns the parameter's scale (Integer).

nullable

Returns the nullable state of the column (Boolean).

iotype

Returns the parameter's input/output type, e.g. ODBC::SQL_PARAM_INPUT.

output_type

Returns the parameter's output type, only useful when the parameter is an output parameter (ODBC::SQL_PARAM_OUTPUT or ODBC::SQL_PARAM_INPUT_OUTPUT).

output_size

Returns the parameter's output buffer size, only useful when the parameter is an output parameter (ODBC::SQL_PARAM_OUTPUT).

ODBC::Date

The class to represent a SQL_DATE column in a table or a SQL_DATE query parameter.

super class:

ODBC::Object

mixins:

Comparable

methods:

<=>(adate)

Comparison, compares date with adate and returns 0, 1, or -1.

day[=num]

Returns or sets the day component of the date object.

month[=num]

Returns or sets the month component of the date object.

year[=num]

Returns or sets the year component of the date object.

to_s

Returns a string representation of the object with format YYYY-MM-DD.

clone

Returns a fresh copy of the date object.

singleton methods:

new([year, month, day])

new(date)

new(time)

new(string)

Creates a new date object from numeric values or from a Date, Time, or String object. Recognized string formats are e.g.

2001-01-01
2001-01-01 12:00:01
{ts '2001-01-01 12:00:01.1'}
{d '2001-01-01'}

ODBC::Time

The class to represent a SQL_TIME column in a table or a SQL_TIME query parameter.

super class:

ODBC::Object

mixins:

Comparable

methods:

<=>(atime)

Comparison, compares time with atime and returns 0, 1, or -1.

second[=num]

Returns or sets the second component of the time object.

minute[=num]

Returns or sets the minute component of the time object.

hour[=num]

Returns or sets the hour component of the time object.

to_s

Returns a string representation of the object with format hh:mm:ss.

clone

Returns a fresh copy of the time object.

singleton methods:

new([hour, minute, second])

new(time)

new(string)

Creates a new time object from numeric values or from a Time or String object. Recognized string formats are e.g.

12:00:01
2001-01-01 12:00:01
{ts '2001-01-01 12:00:01.1'}
{t '12:00:01'}

ODBC::TimeStamp

The class to represent a SQL_TIMESTAMP column in a table or a SQL_TIMESTAMP query parameter.

super class:

ODBC::Object

mixins:

Comparable

methods:

<=>(atimestamp)

Comparison, compares time stamp with atimestamp and returns 0, 1, or -1.

fraction[=num]

Returns or sets the fraction component of the time stamp object. Note that this is expressed in nanoseconds.

second[=num]

Returns or sets the second component of the time stamp object.

minute[=num]

Returns or sets the minute component of the time stamp object.

hour[=num]

Returns or sets the hour component of the time stamp object.

day[=num]

Returns or sets the day component of the time stamp object.

month[=num]

Returns or sets the month component of the time stamp object.

year[=num]

Returns or sets the year component of the time stamp object.

to_s

Returns a string representation of the object with format YYYY-MM-DD hh:mm:ss fraction.

clone

Returns a fresh copy of the time stamp object.

singleton methods:

new([year, month, day, hour, minute, second, fraction])

new(time)

new(string)

Creates a new time stamp object from numeric values or from a Time or String object. Recognized string formats are e.g.

12:00:01
2001-01-01
2001-01-01 12:00:01
{ts '2001-01-01 12:00:01.1'}
{d '2001-01-01'}
{t '12:00:01'}

ODBC::DSN

The class to represent a data source name. Objects of this class are created as result of a ODBC::datasources module function call.

super class:

ODBC::Object

methods:

name[=name]

Queries or sets the name (String) of the data source.

descr[=descr]

Queries or sets the descr (description, String) of the data source.

singleton methods:

new

Returns an empty ODBC::DSN object.

ODBC::Driver

The class to represent an ODBC driver with name and attributes. Objects of this class are created as result of a ODBC::drivers module function call.

super class:

ODBC::Object

methods:

name[=name]

Queries or sets the name (String) of the ODBC driver.

attrs[[key][=value]]

Queries or sets attributes in the attrs Hash of the ODBC driver object. The keys and values should be Strings.

singleton methods:

new

Returns an empty ODBC::Driver object.

---

ODBCProc

The class to represent a procedure with ODBC database/statement context. Objects of this class are created as result of a ODBC::Database.proc method call.

super class:

Proc

methods:

call([args*])

Executes the SQL statement with parameters set from args and then invokes the procedure's block, setting the block's single parameter to the ODBC::Statement.

[[args*]]

Synonym for call.

---

ODBC::Error

The class to represent ODBC related exceptions. The descriptive string is made up of the first ODBC driver or driver manager message as concatenation of SQL state, native error, driver manager name, database name/vendor, DSN, and error text, e.g.

S1000 (1146) [unixODBC][TCX][MySQL]Table 'test.foo' doesn't exist

For internally generated errors, e.g. method invocation on a broken connection, the descriptive string starts with 'INTERN' and native error 0, e.g.

INTERN (0) [RubyODBC]No connection

For errors programmatically generated by the raise method, the descriptive string starts with 'INTERN' and native error 1, e.g.

INTERN (1) [RubyODBC]Programmer forgot to RTFM

super class:

StandardError

---

Undocumented

Use The Source, Luke!

ODBC::add_dsn(driver,issys=false)
ODBC::add_dsn(name,attrs,issys=false)
ODBC::config_dsn(driver,issys=false)
ODBC::config_dsn(name,attrs,issys=false)
ODBC::del_dsn(driver,issys=false)
ODBC::del_dsn(name,attrs,issys=false)
ODBC::write_file_dsn(filename,appname,key[,value])
ODBC::read_file_dsn(filename,appname,key)
ODBC::trace([mask])

ODBC::Statement.fetch!
ODBC::Statement.fetch_first!
ODBC::Statement.fetch_first_hash(with_table_names=false,use_sym=false)
ODBC::Statement.fetch_scroll!(direction,offset=1)
ODBC::Statement.fetch_hash!(with_table_names=false,use_sym=false)

---

mailto:Christian Werner