| Class | Informix::Database |
| In: |
lib/informix.rb
ext/informixc.c |
| Parent: | Object |
| IfxVersion | = | rb_struct_define("IfxVersion", "server_type", "major", "minor", "level", "os", "full", NULL) |
| close | -> | disconnect |
| version | [R] | Exact version of the database server to which a Database object is connected |
Creates a Database object connected to dbname as user with password. If these are not given, connects to dbname as the current user.
The Database object is passed to the block if it‘s given, and automatically closes the connection when the block terminates, returning the value of the block.
# File lib/informix.rb, line 100
100: def self.open(dbname, user = nil, password = nil)
101: db = new(dbname, user, password)
102: return db unless block_given?
103: begin
104: yield db
105: ensure
106: db.close
107: end
108: end
Disconnects db and returns nil.
/*
* call-seq:
* db.close => db
*
* Disconnects <i>db</i> and returns nil.
*/
static VALUE
rb_database_close(VALUE self)
{
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2056 "informixc.ec"
#line 2057 "informixc.ec"
char *id;
/*
* EXEC SQL end declare section;
*/
#line 2058 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
id = dbt->database_id;
/*
* EXEC SQL disconnect :id;
*/
#line 2062 "informixc.ec"
{
#line 2062 "informixc.ec"
sqli_connect_close(0, id, 0, 0);
#line 2062 "informixc.ec"
}
return Qnil;
}
Returns an array with information for every column of the given table.
/*
* call-seq:
* db.columns(tablename) => array
*
* Returns an array with information for every column of the given table.
*/
static VALUE
rb_database_columns(VALUE self, VALUE tablename)
{
VALUE v, column, result;
char *stype;
static char *stypes[] = {
"CHAR", "SMALLINT", "INTEGER", "FLOAT", "SMALLFLOAT", "DECIMAL",
"SERIAL", "DATE", "MONEY", "NULL", "DATETIME", "BYTE",
"TEXT", "VARCHAR", "INTERVAL", "NCHAR", "NVARCHAR", "INT8",
"SERIAL8", "SET", "MULTISET", "LIST", "UNNAMED ROW", "NAMED ROW",
"VARIABLE-LENGTH OPAQUE TYPE"
};
static char *qualifiers[] = {
"YEAR", "MONTH", "DAY", "HOUR", "MINUTE", "SECOND"
};
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2244 "informixc.ec"
#line 2245 "informixc.ec"
char *did, *cid;
char *tabname;
int tabid, xid;
char colname[129];
short coltype, collength;
char deftype[2];
char defvalue[257];
/*
* EXEC SQL end declare section;
*/
#line 2252 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
did = dbt->database_id;
/*
* EXEC SQL set connection :did;
*/
#line 2256 "informixc.ec"
{
#line 2256 "informixc.ec"
sqli_connect_set(0, did, 0);
#line 2256 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
tabname = StringValueCStr(tablename);
/*
* EXEC SQL select tabid into :tabid from systables where tabname = :tabname;
*/
#line 2263 "informixc.ec"
{
#line 2263 "informixc.ec"
static const char *sqlcmdtxt[] =
#line 2263 "informixc.ec"
{
#line 2263 "informixc.ec"
"select tabid from systables where tabname = ?",
0
};
#line 2263 "informixc.ec"
static ifx_cursor_t _SQ0 = {0};
static ifx_sqlvar_t _sqibind[] =
{
{ 100, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 2263 "informixc.ec"
};
static ifx_sqlvar_t _sqobind[] =
{
{ 102, sizeof(tabid), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 2263 "informixc.ec"
};
#line 2263 "informixc.ec"
_sqibind[0].sqldata = tabname;
#line 2263 "informixc.ec"
_sqobind[0].sqldata = (char *) &tabid;
#line 2263 "informixc.ec"
sqli_slct(ESQLINTVERSION, &_SQ0,sqlcmdtxt,1,_sqibind,1,_sqobind,0,(ifx_literal_t *)0,(ifx_namelist_t *)0,0);
#line 2263 "informixc.ec"
}
if (SQLCODE == SQLNOTFOUND)
rb_raise(rb_eProgrammingError, "Table '%s' doesn't exist", tabname);
result = rb_ary_new();
cid = dbt->cursor_id;
/*
* EXEC SQL open :cid using :tabid;
*/
#line 2270 "informixc.ec"
{
#line 2270 "informixc.ec"
static ifx_sqlvar_t _sqibind[] =
{
{ 102, sizeof(tabid), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 2270 "informixc.ec"
};
static ifx_sqlda_t _SD0 = { 1, _sqibind, {0}, 1, 0 };
#line 2270 "informixc.ec"
_sqibind[0].sqldata = (char *) &tabid;
sqli_curs_open(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, cid, 256), &_SD0, (char *)0, (struct value *)0, 1, 0);
#line 2270 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
for(;;) {
/*
* EXEC SQL fetch :cid into :colname, :coltype, :collength, :xid,
* :deftype, :defvalue;
*/
#line 2276 "informixc.ec"
{
#line 2277 "informixc.ec"
static ifx_sqlvar_t _sqobind[] =
{
{ 114, 129, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
{ 101, sizeof(coltype), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
{ 101, sizeof(collength), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
{ 102, sizeof(xid), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
{ 100, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
{ 114, 257, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 2277 "informixc.ec"
};
static ifx_sqlda_t _SD0 = { 6, _sqobind, {0}, 6, 0 };
static _FetchSpec _FS1 = { 0, 1, 0 };
#line 2277 "informixc.ec"
_sqobind[0].sqldata = colname;
#line 2277 "informixc.ec"
_sqobind[1].sqldata = (char *) &coltype;
#line 2277 "informixc.ec"
_sqobind[2].sqldata = (char *) &collength;
#line 2277 "informixc.ec"
_sqobind[3].sqldata = (char *) &xid;
#line 2277 "informixc.ec"
_sqobind[4].sqldata = deftype;
#line 2277 "informixc.ec"
_sqobind[5].sqldata = defvalue;
sqli_curs_fetch(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, cid, 256), (ifx_sqlda_t *)0, &_SD0, (char *)0, &_FS1);
#line 2277 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
if (SQLCODE == SQLNOTFOUND)
break;
column = rb_hash_new();
rb_hash_aset(column, sym_name, rb_str_new2(colname));
rb_hash_aset(column, sym_type, INT2FIX(coltype));
rb_hash_aset(column, sym_nullable, coltype&0x100? Qfalse: Qtrue);
rb_hash_aset(column, sym_xid, INT2FIX(xid));
if ((coltype&0xFF) < 23) {
stype = coltype == 4118? stypes[23]: stypes[coltype&0xFF];
}
else {
stype = stypes[24];
}
rb_hash_aset(column, sym_stype, rb_str_new2(stype));
rb_hash_aset(column, sym_length, INT2FIX(collength));
switch(coltype&0xFF) {
case SQLVCHAR:
case SQLNVCHAR:
case SQLMONEY:
case SQLDECIMAL:
rb_hash_aset(column, sym_precision, INT2FIX(collength >> 8));
rb_hash_aset(column, sym_scale, INT2FIX(collength&0xFF));
break;
case SQLDATE:
case SQLDTIME:
case SQLINTERVAL:
rb_hash_aset(column, sym_length, INT2FIX(collength >> 8));
rb_hash_aset(column, sym_precision, INT2FIX((collength&0xF0) >> 4));
rb_hash_aset(column, sym_scale, INT2FIX(collength&0xF));
break;
default:
rb_hash_aset(column, sym_precision, INT2FIX(0));
rb_hash_aset(column, sym_scale, INT2FIX(0));
}
if (!deftype[0]) {
v = Qnil;
}
else {
switch(deftype[0]) {
case 'C': {
char current[28];
snprintf(current, sizeof(current), "CURRENT %s TO %s",
qualifiers[(collength&0xF0) >> 5],
qualifiers[(collength&0xF)>>1]);
v = rb_str_new2(current);
break;
}
case 'L':
switch (coltype & 0xFF) {
case SQLCHAR:
case SQLNCHAR:
case SQLVCHAR:
case SQLNVCHAR:
v = rb_str_new2(defvalue);
break;
default: {
char *s = defvalue;
while(*s++ != ' ');
if ((coltype&0xFF) == SQLFLOAT ||
(coltype&0xFF) == SQLSMFLOAT ||
(coltype&0xFF) == SQLMONEY ||
(coltype&0xFF) == SQLDECIMAL)
v = rb_float_new(atof(s));
else
v = LONG2FIX(atol(s));
}
}
break;
case 'N':
v = rb_str_new2("NULL");
break;
case 'T':
v = rb_str_new2("today");
break;
case 'U':
v = rb_str_new2("user");
break;
case 'S':
default: /* XXX */
v = Qnil;
}
}
rb_hash_aset(column, sym_default, v);
rb_ary_push(result, column);
}
/*
* EXEC SQL close :cid;
*/
#line 2371 "informixc.ec"
{
#line 2371 "informixc.ec"
sqli_curs_close(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, cid, 256));
#line 2371 "informixc.ec"
}
return result;
}
Commits a transaction and returns self.
/*
* call-seq:
* db.commit => db
*
* Commits a transaction and returns __self__.
*/
static VALUE
rb_database_commit(VALUE self)
{
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2145 "informixc.ec"
#line 2146 "informixc.ec"
char *did;
/*
* EXEC SQL end declare section;
*/
#line 2147 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
did = dbt->database_id;
/*
* EXEC SQL set connection :did;
*/
#line 2151 "informixc.ec"
{
#line 2151 "informixc.ec"
sqli_connect_set(0, did, 0);
#line 2151 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
/*
* EXEC SQL commit;
*/
#line 2156 "informixc.ec"
{
#line 2156 "informixc.ec"
sqli_trans_commit();
#line 2156 "informixc.ec"
}
return self;
}
Shortcut to create a cursor object based on query using options.
The cursor object is passed to the block if it‘s given, and automatically dropped when the block terminates, returning the value of the block.
query may contain ’?’ placeholders for input parameters.
options can be a Hash object with the following possible keys:
:scroll => true or false :hold => true or false
Examples:
This creates a SequentialCursor
cur = db.cursor('select * from orders where order_date > ?')
This creates a ScrollCursor
cur = db.cursor('select * from customer', :scroll => true)
This creates an InsertCursor
cur = db.cursor('insert into stock values(?, ?, ?, ?, ?, ?)')
# File lib/informix.rb, line 177
177: def cursor(query, options = nil, &block)
178: Cursor.new(self, query, options, &block)
179: end
Shortcut to create, open and iterate a cursor object based on query using options. The records are retrieved as arrays.
The cursor object is passed to the block and automatically dropped when the block terminates. Returns self.
query may contain ’?’ placeholders for input parameters.
options can be a Hash object with the following possible keys:
:scroll => true or false :hold => true or false :params => input parameters as an Array or nil
Examples:
Iterating over a table:
db.each('select * from customer') do |cust|
# do something with cust
puts "#{cust[0] cust[1]}"
end
Same thing, using input parameters:
query = 'select * from orders where order_date = ?'
db.each(query, :params => [Date.today]) do |order|
# do something with order
end
# File lib/informix.rb, line 207
207: def each(query, options = nil, &block)
208: Cursor.open(self, query, options) {|cur| cur.each(&block)}
209: self
210: end
Similar to +Database#each+, except that retrieves records as hashes instead of arrays.
Examples:
Iterating over a table:
db.each_hash('select * from customer') do |cust|
# do something with cust
puts "#{cust['fname'] cust['lname']}"
end
# File lib/informix.rb, line 222
222: def each_hash(query, options = nil, &block)
223: Cursor.open(self, query, options) {|cur| cur.each_hash(&block)}
224: self
225: end
Shortcut to create, execute and drop a Statement object from query.
query may contain ’?’ placeholders for input parameters; it cannot be a query returning more than one row (use Database#cursor instead.)
Returns the record retrieved, in the case of a singleton select, or the number of rows affected, in the case of any other statement.
Examples:
Deleting records:
db.execute('delete from orders where order_date = ?', Date.today)
Updating a record:
db.execute('update items set quantity = ? where item_num = ?', 10, 101)
# File lib/informix.rb, line 152
152: def execute(query, *args)
153: Statement.new(self, query) {|stmt| stmt.execute(*args) }
154: end
Executes query and returns the number of rows affected. query must not return rows. Executes efficiently any DDL (CREATE, DROP, ALTER), DCL (GRANT, REVOKE) and non-parameterized DML (INSERT, UPDATE, DELETE) statements, except SELECT.
Examples:
Granting CONNECT to user:
db.immediate "grant connect to #{user}"
Creating a table:
db.immediate 'create table test(id serial, code char(2), desc varchar(30))'
Deleting records:
db.immediate 'delete from test where id = 7'
/*
* call-seq:
* db.immediate(query) => fixnum
*
* Executes <i>query</i> and returns the number of rows affected.
* <i>query</i> must not return rows. Executes efficiently any
* DDL (CREATE, DROP, ALTER), DCL (GRANT, REVOKE) and non-parameterized
* DML (INSERT, UPDATE, DELETE) statements, except SELECT.
*
* Examples:
*
* Granting CONNECT to user:
* db.immediate "grant connect to #{user}"
* Creating a table:
* db.immediate 'create table test(id serial, code char(2), desc varchar(30))'
* Deleting records:
* db.immediate 'delete from test where id = 7'
*/
static VALUE
rb_database_immediate(VALUE self, VALUE arg)
{
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2090 "informixc.ec"
#line 2091 "informixc.ec"
char *query, *did;
/*
* EXEC SQL end declare section;
*/
#line 2092 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
did = dbt->database_id;
/*
* EXEC SQL set connection :did;
*/
#line 2096 "informixc.ec"
{
#line 2096 "informixc.ec"
sqli_connect_set(0, did, 0);
#line 2096 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
query = StringValueCStr(arg);
/*
* EXEC SQL execute immediate :query;
*/
#line 2102 "informixc.ec"
{
#line 2102 "informixc.ec"
sqli_exec_immed(query);
#line 2102 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
return INT2FIX(sqlca.sqlerrd[2]);
}
Shortcut to create a Statement object from query.
The Statement object is passed to the block if it‘s given, and automatically dropped when the block terminates, returning the value of the block.
query may contain ’?’ placeholders for input parameters; it must NOT be a query returning more than one row (use Database#cursor instead.)
Examples:
Preparing a statement:
st = db.prepare('delete from orders where order_date = ?')
Using a block:
query 'update items set quantity = ? where item_num = ?'
db.prepare(query) do |st|
# do something with st
# the last expression evaluated will be returned
end
# File lib/informix.rb, line 131
131: def prepare(query, &block)
132: Statement.new(self, query, &block)
133: end
Rolls back a transaction and returns self.
/*
* call-seq:
* db.rollback => db
*
* Rolls back a transaction and returns __self__.
*/
static VALUE
rb_database_rollback(VALUE self)
{
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2119 "informixc.ec"
#line 2120 "informixc.ec"
char *did;
/*
* EXEC SQL end declare section;
*/
#line 2121 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
did = dbt->database_id;
/*
* EXEC SQL set connection :did;
*/
#line 2125 "informixc.ec"
{
#line 2125 "informixc.ec"
sqli_connect_set(0, did, 0);
#line 2125 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
/*
* EXEC SQL rollback;
*/
#line 2130 "informixc.ec"
{
#line 2130 "informixc.ec"
sqli_trans_rollback();
#line 2130 "informixc.ec"
}
return self;
}
Shortcut to create a Slob object.
The Slob object is passed to the block if it‘s given, and automatically closes it when the block terminates, returning the value of the block.
type can be Slob::BLOB or Slob::CLOB
options can be a Hash object with the following possible keys:
:sbspace => Sbspace name
:estbytes => Estimated size, in bytes
:extsz => Allocation extent size
:createflags => Create-time flags
:openflags => Access mode
:maxbytes => Maximum size
:col_info => Get the previous values from the column-level storage
characteristics for the specified database column
Examples:
Creating a CLOB:
slob = db.slob
Creating a BLOB without log and passing it to a block:
slob = db.slob(Slob::BLOB, :createflags=>Slob:NOLOG) do |slob|
# do something with slob
end
# File lib/informix.rb, line 255
255: def slob(type = Slob::CLOB, options = nil, &block)
256: Slob.new(self, type, options, &block)
257: end
Opens a transaction and executes block, passing self as parameter. If an exception is raised, the transaction is rolled back. It is commited otherwise.
Returns self.
Examples:
A bulk insert using an insert cursor. Requires a transaction:
db.transaction do |db|
db.cursor('insert into stock values(?, ?, ?, ?, ?, ?)') |cur|
cur.open
# Loading a file separated by '|'
File.open(filename).each do |line|
fields = line.split('|')
cur.put(*fields)
end
end
end
/*
* call-seq:
* db.transaction {|db| block } => db
*
* Opens a transaction and executes <i>block</i>, passing __self__ as parameter.
* If an exception is raised, the transaction is rolled back. It is commited
* otherwise.
*
* Returns __self__.
*
* Examples:
*
* A bulk insert using an insert cursor. Requires a transaction:
* db.transaction do |db|
* db.cursor('insert into stock values(?, ?, ?, ?, ?, ?)') |cur|
* cur.open
* # Loading a file separated by '|'
* File.open(filename).each do |line|
* fields = line.split('|')
* cur.put(*fields)
* end
* end
* end
*/
static VALUE
rb_database_transaction(VALUE self)
{
VALUE ret;
database_t *dbt;
/*
* EXEC SQL begin declare section;
*/
#line 2197 "informixc.ec"
#line 2198 "informixc.ec"
char *did;
/*
* EXEC SQL end declare section;
*/
#line 2199 "informixc.ec"
Data_Get_Struct(self, database_t, dbt);
did = dbt->database_id;
/*
* EXEC SQL set connection :did;
*/
#line 2203 "informixc.ec"
{
#line 2203 "informixc.ec"
sqli_connect_set(0, did, 0);
#line 2203 "informixc.ec"
}
if (SQLCODE < 0)
raise_ifx_extended();
/*
* EXEC SQL commit;
*/
#line 2208 "informixc.ec"
{
#line 2208 "informixc.ec"
sqli_trans_commit();
#line 2208 "informixc.ec"
}
/*
* EXEC SQL begin work;
*/
#line 2209 "informixc.ec"
{
#line 2209 "informixc.ec"
sqli_trans_begin2((mint)1);
#line 2209 "informixc.ec"
}
ret = rb_rescue(rb_yield, self, database_transfail, self);
if (ret == Qundef)
rb_raise(rb_eOperationalError, "Transaction rolled back");
/*
* EXEC SQL commit;
*/
#line 2215 "informixc.ec"
{
#line 2215 "informixc.ec"
sqli_trans_commit();
#line 2215 "informixc.ec"
}
return self;
}