Class: OCI8::Cursor
- Defined in:
- lib/oci8/oci8.rb,
lib/oci8/compat.rb,
ext/oci8/stmt.c
Overview
The instance of this class corresponds to cursor in the term of Oracle, which corresponds to java.sql.Statement of JDBC and statement handle $sth of Perl/DBI.
Don’t create the instance by calling ‘new’ method. Please create it by calling OCI8#exec or OCI8#parse.
Class Method Summary collapse
Instance Method Summary collapse
-
#[](key) ⇒ Object
Gets the value of the bind variable.
-
#[]=(key, val) ⇒ Object
Sets the value to the bind variable.
-
#bind_param(key, param, type = nil, length = nil) ⇒ Object
Binds variables explicitly.
-
#bind_param_array(key, var_array, type = nil, max_item_length = nil) ⇒ Object
Bind array explicitly.
-
#close ⇒ Object
close the cursor.
-
#column_metadata ⇒ Object
call-seq: column_metadata -> column information.
-
#define(pos, type, length = nil) ⇒ Object
explicitly indicate the date type of fetched value.
-
#exec(*bindvars) ⇒ Object
Executes the SQL statement assigned the cursor.
-
#exec_array ⇒ Object
Executes the SQL statement assigned the cursor with array binding.
-
#fetch ⇒ Object
Gets fetched data as array.
-
#fetch_hash ⇒ Object
call-seq: fetch_hash.
-
#get_col_names ⇒ Object
(also: #getColNames)
Gets the names of select-list as array.
-
#keys ⇒ an Array
Returns the keys of bind variables as array.
-
#max_array_size=(size) ⇒ Object
Set the maximum array size for bind_param_array.
-
#prefetch_rows=(aFixnum) ⇒ Object
Set number of rows to be prefetched.
-
#row_count ⇒ Object
Returns the number of processed rows.
-
#rowid ⇒ Object
Get the rowid of the last inserted/updated/deleted row.
-
#type ⇒ Object
gets the type of SQL statement as follows.
Methods inherited from OCIHandle
Class Method Details
.select_number_as ⇒ Object
94 95 96 97 98 99 100 101 102 103 |
# File 'lib/oci8/compat.rb', line 94 def self.select_number_as case @@bind_unknown_number when OCI8::BindType::Fixnum return Fixnum when OCI8::BindType::Integer return Integer when OCI8::BindType::Float return Float end end |
.select_number_as=(val) ⇒ Object
82 83 84 85 86 87 88 89 90 91 92 |
# File 'lib/oci8/compat.rb', line 82 def self.select_number_as=(val) if val == Fixnum @@bind_unknown_number = OCI8::BindType::Fixnum elsif val == Integer @@bind_unknown_number = OCI8::BindType::Integer elsif val == Float @@bind_unknown_number = OCI8::BindType::Float else raise ArgumentError, "must be Fixnum, Integer or Float" end end |
Instance Method Details
#[](key) ⇒ Object
Gets the value of the bind variable.
In case of binding explicitly, use same key with that of OCI8::Cursor#bind_param. A placeholder can be bound by name or position. If you bind by name, use that name. If you bind by position, use the position.
example:
cursor = conn.parse("BEGIN :out := 'BAR'; END;")
cursor.bind_param(':out', 'FOO') # bind by name
p cursor[':out'] # => 'FOO'
p cursor[1] # => nil
cursor.exec()
p cursor[':out'] # => 'BAR'
p cursor[1] # => nil
example:
cursor = conn.parse("BEGIN :out := 'BAR'; END;")
cursor.bind_param(1, 'FOO') # bind by position
p cursor[':out'] # => nil
p cursor[1] # => 'FOO'
cursor.exec()
p cursor[':out'] # => nil
p cursor[1] # => 'BAR'
In case of binding by OCI8#exec or OCI8::Cursor#exec, get the value by position, which starts from 1.
example:
cursor = conn.exec("BEGIN :out := 'BAR'; END;", 'FOO')
# 1st bind variable is bound as String with width 3. Its initial value is 'FOO'
# After execute, the value become 'BAR'.
p cursor[1] # => 'BAR'
594 595 596 597 598 599 600 601 602 |
# File 'ext/oci8/stmt.c', line 594
static VALUE oci8_stmt_aref(VALUE self, VALUE key)
{
oci8_stmt_t *stmt = DATA_PTR(self);
VALUE obj = rb_hash_aref(stmt->binds, key);
if (NIL_P(obj)) {
return Qnil;
}
return oci8_bind_get_data(obj);
}
|
#[]=(key, val) ⇒ Object
Sets the value to the bind variable. The way to specify the key
is same with OCI8::Cursor#[]. This is available to replace the value and execute many times.
example1:
cursor = conn.parse("INSERT INTO test(col1) VALUES(:1)")
cursor.bind_params(1, nil, String, 3)
['FOO', 'BAR', 'BAZ'].each do |key|
cursor[1] = key
cursor.exec
end
cursor.close()
example2:
['FOO', 'BAR', 'BAZ'].each do |key|
conn.exec("INSERT INTO test(col1) VALUES(:1)", key)
end
Both example’s results are same. But the former will use less resources.
628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 |
# File 'ext/oci8/stmt.c', line 628
static VALUE oci8_stmt_aset(VALUE self, VALUE key, VALUE val)
{
long max_array_size;
long actual_array_size;
long bind_array_size;
oci8_stmt_t *stmt = DATA_PTR(self);
VALUE obj = rb_hash_aref(stmt->binds, key);
if (NIL_P(obj)) {
return Qnil; /* ?? MUST BE ERROR? */
}
if(TYPE(val) == T_ARRAY) {
max_array_size = NUM2INT(rb_ivar_get(self, id_at_max_array_size));
actual_array_size = NUM2INT(rb_ivar_get(self, id_at_actual_array_size));
bind_array_size = RARRAY_LEN(val);
if(actual_array_size > 0 && bind_array_size != actual_array_size) {
rb_raise(rb_eRuntimeError, "all binding arrays hould be the same size");
}
if(bind_array_size <= max_array_size && actual_array_size == 0) {
rb_ivar_set(self, id_at_actual_array_size, INT2NUM(bind_array_size));
}
}
oci8_bind_set_data(obj, val);
return val;
}
|
#bind_param(key, param, type = nil, length = nil) ⇒ Object
Binds variables explicitly.
When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.
example:
cursor = conn.parse("SELECT * FROM emp WHERE ename = :ename")
cursor.bind_param(1, 'SMITH') # bind by position
...or...
cursor.bind_param(':ename', 'SMITH') # bind by name
To bind as number, Fixnum and Float are available, but Bignum is not supported. If its initial value is NULL, please set nil to type
and Fixnum or Float to val
.
example:
cursor.bind_param(1, 1234) # bind as Fixnum, Initial value is 1234.
cursor.bind_param(1, 1234.0) # bind as Float, Initial value is 1234.0.
cursor.bind_param(1, nil, Fixnum) # bind as Fixnum, Initial value is NULL.
cursor.bind_param(1, nil, Float) # bind as Float, Initial value is NULL.
In case of binding a string, set the string itself to val
. When the bind variable is used as output, set the string whose length is enough to store or set the length.
example:
cursor = conn.parse("BEGIN :out := :in || '_OUT'; END;")
cursor.bind_param(':in', 'DATA') # bind as String with width 4.
cursor.bind_param(':out', nil, String, 7) # bind as String with width 7.
cursor.exec()
p cursor[':out'] # => 'DATA_OU'
# Though the length of :out is 8 bytes in PL/SQL block, it is
# bound as 7 bytes. So result is cut off at 7 byte.
In case of binding a string as RAW, set OCI::RAW to type
.
example:
cursor = conn.parse("INSERT INTO raw_table(raw_column) VALUE (:1)")
cursor.bind_param(1, 'RAW_STRING', OCI8::RAW)
cursor.exec()
cursor.close()
247 248 249 250 251 252 253 254 255 256 257 |
# File 'lib/oci8/oci8.rb', line 247 def bind_param(key, param, type = nil, length = nil) case param when Hash when Class param = {:value => nil, :type => param, :length => length} else param = {:value => param, :type => type, :length => length} end __bind(key, make_bind_object(param)) self end |
#bind_param_array(key, var_array, type = nil, max_item_length = nil) ⇒ Object
Bind array explicitly
When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.
The max_array_size should be set before calling bind_param_array
example:
cursor = conn.parse("INSERT INTO test_table VALUES (:str)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ['happy', 'new', 'year'], String, 30)
cursor.exec_array
309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 |
# File 'lib/oci8/oci8.rb', line 309 def bind_param_array(key, var_array, type = nil, max_item_length = nil) raise "please call max_array_size= first." if @max_array_size.nil? raise "expect array as input param for bind_param_array." if !var_array.nil? && !(var_array.is_a? Array) raise "the size of var_array should not be greater than max_array_size." if !var_array.nil? && var_array.size > @max_array_size if var_array.nil? raise "all binding arrays should be the same size." unless @actual_array_size.nil? || @actual_array_size == 0 @actual_array_size = 0 else raise "all binding arrays should be the same size." unless @actual_array_size.nil? || var_array.size == @actual_array_size @actual_array_size = var_array.size if @actual_array_size.nil? end param = {:value => var_array, :type => type, :length => max_item_length, :max_array_size => @max_array_size} first_non_nil_elem = var_array.nil? ? nil : var_array.find{|x| x!= nil} if type.nil? if first_non_nil_elem.nil? raise "bind type is not given." else type = first_non_nil_elem.class end end bindclass = OCI8::BindType::Mapping[type] raise "unsupported dataType: #{type}" if bindclass.nil? bindobj = bindclass.create(@con, var_array, param, @max_array_size) __bind(key, bindobj) self end |
#close ⇒ Object
close the cursor.
400 401 402 403 404 |
# File 'lib/oci8/oci8.rb', line 400 def close free() @names = nil @column_metadata = nil end |
#column_metadata ⇒ Object
call-seq:
column_metadata -> column information
(new in 1.0.0 and 2.0)
Gets an array of OCI8::Metadata::Column of a select statement.
example:
cursor = conn.exec('select * from tab')
puts ' Name Type'
puts ' ----------------------------------------- ----------------------------'
cursor..each do |colinfo|
puts format(' %-41s %s',
colinfo.name,
colinfo.type_string)
end
380 381 382 |
# File 'lib/oci8/oci8.rb', line 380 def @column_metadata end |
#define(pos, type, length = nil) ⇒ Object
explicitly indicate the date type of fetched value. run this method within parse and exec. pos starts from 1. lentgh is used when type is String.
example:
cursor = conn.parse("SELECT ename, hiredate FROM emp")
cursor.define(1, String, 20) # fetch the first column as String.
cursor.define(2, Time) # fetch the second column as Time.
cursor.exec()
201 202 203 204 |
# File 'lib/oci8/oci8.rb', line 201 def define(pos, type, length = nil) __define(pos, make_bind_object(:type => type, :length => length)) self end |
#exec(*bindvars) ⇒ Object
Executes the SQL statement assigned the cursor. The type of return value depends on the type of sql statement: select; insert, update and delete; create, alter, drop and PL/SQL.
In case of select statement, it returns the number of the select-list.
In case of insert, update or delete statement, it returns the number of processed rows.
In case of create, alter, drop and PL/SQL statement, it returns true. In contrast with OCI8#exec, it returns true even though PL/SQL. Use OCI8::Cursor#[] explicitly to get bind variables.
273 274 275 276 277 278 279 280 281 282 |
# File 'lib/oci8/oci8.rb', line 273 def exec(*bindvars) bind_params(*bindvars) __execute(nil) # Pass a nil to specify the statement isn't an Array DML case type when :select_stmt define_columns() else row_count end end |
#exec_array ⇒ Object
Executes the SQL statement assigned the cursor with array binding
341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
# File 'lib/oci8/oci8.rb', line 341 def exec_array raise "please call max_array_size= first." if @max_array_size.nil? if !@actual_array_size.nil? && @actual_array_size > 0 __execute(@actual_array_size) else raise "please set non-nil values to array binding parameters" end case type when :update_stmt, :delete_stmt, :insert_stmt row_count else true end end |
#fetch ⇒ Object
448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 |
# File 'ext/oci8/stmt.c', line 448
static VALUE oci8_stmt_fetch(VALUE self)
{
oci8_stmt_t *stmt = DATA_PTR(self);
oci8_svcctx_t *svcctx = oci8_get_svcctx(stmt->svc);
if (rb_block_given_p()) {
for (;;) {
VALUE rs = oci8_stmt_do_fetch(stmt, svcctx);
if (NIL_P(rs))
return self; /* NEED TO CHECK 0.1 behavior. */
rb_yield(rs);
}
} else {
return oci8_stmt_do_fetch(stmt, svcctx);
}
}
|
#fetch_hash ⇒ Object
call-seq:
fetch_hash
get fetched data as a Hash. The hash keys are column names. If a block is given, acts as an iterator.
389 390 391 392 393 394 395 396 397 |
# File 'lib/oci8/oci8.rb', line 389 def fetch_hash if iterator? while ret = fetch_a_hash_row() yield(ret) end else fetch_a_hash_row end end |
#get_col_names ⇒ Object Also known as: getColNames
Gets the names of select-list as array. Please use this method after exec.
360 361 362 |
# File 'lib/oci8/oci8.rb', line 360 def get_col_names @names ||= @column_metadata.collect { |md| md.name } end |
#keys ⇒ an Array
Returns the keys of bind variables as array.
662 663 664 665 666 |
# File 'ext/oci8/stmt.c', line 662
static VALUE oci8_stmt_keys(VALUE self)
{
oci8_stmt_t *stmt = DATA_PTR(self);
return rb_funcall(stmt->binds, oci8_id_keys, 0);
}
|
#max_array_size=(size) ⇒ Object
Set the maximum array size for bind_param_array
All the binds will be clean from cursor if instance variable max_array_size is set before
Instance variable actual_array_size holds the size of the arrays users actually binds through bind_param_array
all the binding arrays are required to be the same size
290 291 292 293 294 295 |
# File 'lib/oci8/oci8.rb', line 290 def max_array_size=(size) raise "expect positive number for max_array_size." if size.nil? && size <=0 __clearBinds if !@max_array_size.nil? @max_array_size = size @actual_array_size = nil end |
#prefetch_rows=(aFixnum) ⇒ Object
Set number of rows to be prefetched. This can reduce the number of network round trips when fetching many rows. The default value is one.
FYI: Rails oracle adaptor uses 100 by default.
692 693 694 695 696 697 698 699 |
# File 'ext/oci8/stmt.c', line 692
static VALUE oci8_stmt_set_prefetch_rows(VALUE self, VALUE rows)
{
oci8_stmt_t *stmt = DATA_PTR(self);
ub4 num = NUM2UINT(rows);
oci_lc(OCIAttrSet(stmt->base.hp.ptr, OCI_HTYPE_STMT, &num, 0, OCI_ATTR_PREFETCH_ROWS, oci8_errhp));
return Qfalse;
}
|
#row_count ⇒ Object
Returns the number of processed rows.
527 528 529 530 |
# File 'ext/oci8/stmt.c', line 527
static VALUE oci8_stmt_get_row_count(VALUE self)
{
return oci8_get_ub4_attr(DATA_PTR(self), OCI_ATTR_ROW_COUNT);
}
|
#rowid ⇒ Object
Get the rowid of the last inserted/updated/deleted row. This cannot be used for select statements.
example:
cursor = conn.parse('INSERT INTO foo_table values(:1, :2)', 1, 2)
cursor.exec
cursor.rowid # => the inserted row's rowid
Changes between ruby-oci8 1.0.3 and 1.0.4.
- ruby-oci8 1.0.4 or upper
-
The return value is a String.
- ruby-oci8 1.0.3 or lower
-
It returns an OCIRowid object.
546 547 548 549 |
# File 'ext/oci8/stmt.c', line 546
static VALUE oci8_stmt_get_rowid(VALUE self)
{
return oci8_get_rowid_attr(DATA_PTR(self), OCI_ATTR_ROWID);
}
|
#type ⇒ Object
gets the type of SQL statement as follows.
-
OCI8::STMT_SELECT
-
OCI8::STMT_UPDATE
-
OCI8::STMT_DELETE
-
OCI8::STMT_INSERT
-
OCI8::STMT_CREATE
-
OCI8::STMT_DROP
-
OCI8::STMT_ALTER
-
OCI8::STMT_BEGIN (PL/SQL block which starts with a BEGIN keyword)
-
OCI8::STMT_DECLARE (PL/SQL block which starts with a DECLARE keyword)
-
Other Fixnum value undocumented in Oracle manuals.
Changes between ruby-oci8 1.0 and 2.0.
- ruby-oci8 2.0
-
OCI8::STMT_* are Symbols. (:select_stmt, :update_stmt, etc.)
- ruby-oci8 1.0
-
OCI8::STMT_* are Fixnums. (1, 2, 3, etc.)
497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 |
# File 'ext/oci8/stmt.c', line 497
static VALUE oci8_stmt_get_stmt_type(VALUE self)
{
VALUE stmt_type = oci8_get_ub2_attr(DATA_PTR(self), OCI_ATTR_STMT_TYPE);
switch (FIX2INT(stmt_type)) {
case OCI_STMT_SELECT:
return oci8_sym_select_stmt;
case OCI_STMT_UPDATE:
return oci8_sym_update_stmt;
case OCI_STMT_DELETE:
return oci8_sym_delete_stmt;
case OCI_STMT_INSERT:
return oci8_sym_insert_stmt;
case OCI_STMT_CREATE:
return oci8_sym_create_stmt;
case OCI_STMT_DROP:
return oci8_sym_drop_stmt;
case OCI_STMT_ALTER:
return oci8_sym_alter_stmt;
case OCI_STMT_BEGIN:
return oci8_sym_begin_stmt;
case OCI_STMT_DECLARE:
return oci8_sym_declare_stmt;
default:
return stmt_type;
}
}
|