Class: OCIStmt

Inherits:
OCIHandle show all
Defined in:
ext/oci8/oci8.c

Instance Method Summary collapse

Methods inherited from OCIHandle

#attrGet, #attrSet, #free, new

Instance Method Details

#bindByName(*args) ⇒ Object

begin

— OCIStmt#bindByName(name, type [, length [, mode]])

    define the datatype of the bind variable by name.

    :name
       the name of the bind variable including colon.
    :type
       the type of the bind variable.
       ((|String|)), ((|Fixnum|)), ((|Integer|)), ((|Float|)), ((|Time|)),
       ((<OraDate>)), ((<OraNumber>)), or ((|OCI_TYPECODE_RAW|))
    :length
       When the 2nd argument is
((|String|)) or ((|OCI_TYPECODE_RAW|)),
         the max length of fetched data.
otherwise,
         its value is ignored.
    :mode
       ((|OCI_DEFAULT|)), or ((|OCI_DATA_AT_EXEC|)). But now available value is
       ((|OCI_DEFAULT|)) only. Default value is ((|OCI_DEFAULT|))
    :return value
       newly created ((<bind handle|OCIBind>))

    for example
      stmt = env.alloc(OCIStmt)
      stmt.prepare("SELECT * FROM EMP
                     WHERE ename = :ENAME
                       AND sal > :SAL
                       AND hiredate >= :HIREDATE")
      b_ename = stmt.bindByName(":ENAME", String, 10)
      b_sal = stmt.bindByName(":SAL", Fixnum)
      b_hiredate = stmt.bindByName(":HIREDATE", OraDate)

    correspond native OCI function: ((|OCIBindByName|))

end



407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
 
# File 'ext/oci8/stmt.c', line 407

static VALUE oci8_bind_by_name(int argc, VALUE *argv, VALUE self)
{
  VALUE vplaceholder;
  VALUE vtype;
  VALUE vlength;
  VALUE vmode;
  oci8_handle_t *h;
  oci8_string_t placeholder;
  oci8_bind_handle_t *bh;
  ub2 dty;
  ub4 mode;
  dvoid *indp;
  ub2 *rlenp;
  dvoid *valuep;
  OCIBind *bindhp = NULL;
  sword status;
  VALUE hash;
  VALUE obj;

  rb_scan_args(argc, argv, "22", &vplaceholder, &vtype, &vlength, &vmode);
  Get_Handle(self, h); /* 0 */
  Get_String(vplaceholder, placeholder); /* 1 */
  check_bind_type(OCI_HTYPE_BIND, h, vtype, vlength, &bh, &dty); /* 2, 3 */
  Get_Int_With_Default(argc, 4, vmode, mode, OCI_DEFAULT); /* 4 */

  if (mode & OCI_DATA_AT_EXEC) {
    indp = NULL;
    rlenp = NULL;
  } else {
    indp = &(bh->ind);
    rlenp = (bh->bind_type == BIND_STRING) ? NULL : &bh->rlen;
  }
  valuep = &bh->value;
  status = OCIBindByName(h->hp, &bindhp, h->errhp, placeholder.ptr, placeholder.len, valuep, bh->value_sz, dty, indp, rlenp, 0, 0, 0, mode);
  if (status != OCI_SUCCESS) {
    oci8_unlink((oci8_handle_t *)bh);
    bh->type = 0;
    oci8_raise(h->errhp, status, h->hp);
  }
  bh->type = OCI_HTYPE_BIND;
  bh->hp = bindhp;
  bh->errhp = h->errhp;
  obj = bh->self;
  hash = rb_ivar_get(self, oci8_id_bind_hash);
  if (hash == Qnil) {
    hash = rb_hash_new();
    rb_ivar_set(self, oci8_id_bind_hash, hash);
  }
  rb_hash_aset(hash, vplaceholder, obj);
  return obj;
}

#bindByPos(*args) ⇒ Object

begin

— OCIStmt#bindByPos(position, type [, length [, mode]])

    define the datatype of the bind variable by posision.

    :position
       the position of the bind variable.
    :type
       the type of the bind variable.
       ((|String|)), ((|Fixnum|)), ((|Integer|)), ((|Float|)), ((|Time|)),
       ((<OraDate>)), ((<OraNumber>)), or ((|OCI_TYPECODE_RAW|))
    :length
       When the 2nd argument is
((|String|)) or ((|OCI_TYPECODE_RAW|)),
         the max length of fetched data.
otherwise,
         its value is ignored.
    :mode
       ((|OCI_DEFAULT|)), or ((|OCI_DATA_AT_EXEC|)). But now available value is
       ((|OCI_DEFAULT|)) only. Default value is ((|OCI_DEFAULT|))
    :return value
       newly created ((<bind handle|OCIBind>))

    correspond native OCI function: ((|OCIBindByPos|))

end



319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'ext/oci8/stmt.c', line 319

static VALUE oci8_bind_by_pos(int argc, VALUE *argv, VALUE self)
{
  VALUE vposition;
  VALUE vtype;
  VALUE vlength;
  VALUE vmode;
  oci8_handle_t *h;
  ub4 position;
  oci8_bind_handle_t *bh;
  ub2 dty;
  ub4 mode;
  dvoid *indp;
  ub2 *rlenp;
  dvoid *valuep;
  OCIBind *bindhp = NULL;
  sword status;
  VALUE hash;
  VALUE obj;

  rb_scan_args(argc, argv, "22", &vposition, &vtype, &vlength, &vmode);
  Get_Handle(self, h); /* 0 */
  position = NUM2INT(vposition); /* 1 */
  check_bind_type(OCI_HTYPE_BIND, h, vtype, vlength, &bh, &dty); /* 2, 3 */
  Get_Int_With_Default(argc, 4, vmode, mode, OCI_DEFAULT); /* 4 */

  if (mode & OCI_DATA_AT_EXEC) {
    indp = NULL;
    rlenp = NULL;
  } else {
    indp = &(bh->ind);
    rlenp = (bh->bind_type == BIND_STRING) ? NULL : &bh->rlen;
  }
  valuep = &bh->value;
  status = OCIBindByPos(h->hp, &bindhp, h->errhp, position, valuep, bh->value_sz, dty, indp, rlenp, 0, 0, 0, mode);
  if (status != OCI_SUCCESS) {
    oci8_unlink((oci8_handle_t *)bh);
    bh->type = 0;
    oci8_raise(h->errhp, status, h->hp);
  }
  bh->type = OCI_HTYPE_BIND;
  bh->hp = bindhp;
  bh->errhp = h->errhp;
  obj = bh->self;
  hash = rb_ivar_get(self, oci8_id_bind_hash);
  if (hash == Qnil) {
    hash = rb_hash_new();
    rb_ivar_set(self, oci8_id_bind_hash, hash);
  }
  rb_hash_aset(hash, vposition, obj);
  return obj;
}

#defineByPos(*args) ⇒ Object

begin

— OCIStmt#defineByPos(position, type [, length [, mode]])

    define the datatype of fetched column.
    You must define all column's datatype, before you fetch data.

    :position
       the position of the column. It starts from 1.
    :type
       the type of column.
       ((|String|)), ((|Fixnum|)), ((|Integer|)), ((|Float|)), ((|Time|)),
       ((<OraDate>)), ((<OraNumber>)), or ((|OCI_TYPECODE_RAW|))
    :length
       When the 2nd argument is
((|String|)) or ((|OCI_TYPECODE_RAW|)),
         the max length of fetched data.
otherwise,
         its value is ignored.
    :mode
       ((|OCI_DEFAULT|)), or ((|OCI_DYNAMIC_FETCH|)). But now available value is
       ((|OCI_DEFAULT|)) only. Default value is ((|OCI_DEFAULT|))
    :return value
       newly created ((<define handle|OCIDefine>))

    correspond native OCI function: ((|OCIDefineByPos|))

end



241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
 
# File 'ext/oci8/stmt.c', line 241

static VALUE oci8_define_by_pos(int argc, VALUE *argv, VALUE self)
{
  VALUE vposition;
  VALUE vtype;
  VALUE vlength;
  VALUE vmode;
  oci8_handle_t *h;
  ub4 position;
  oci8_bind_handle_t *bh;
  ub2 dty;
  ub4 mode;
  dvoid *indp;
  ub2 *rlenp;
  dvoid *valuep;
  OCIDefine *dfnhp = NULL;
  sword status;
  VALUE ary;
  VALUE obj;

  rb_scan_args(argc, argv, "22", &vposition, &vtype, &vlength, &vmode);
  Get_Handle(self, h); /* 0 */
  position = NUM2INT(vposition); /* 1 */
  check_bind_type(OCI_HTYPE_DEFINE, h, vtype, vlength, &bh, &dty); /* 2, 3 */
  Get_Int_With_Default(argc, 4, vmode, mode, OCI_DEFAULT); /* 4 */

  if (mode & OCI_DYNAMIC_FETCH) {
    indp = NULL;
    rlenp = NULL;
  } else {
    indp = &(bh->ind);
    rlenp = (bh->bind_type == BIND_STRING) ? NULL : &bh->rlen;
  }
  valuep = &bh->value;
  status = OCIDefineByPos(h->hp, &dfnhp, h->errhp, position, valuep, bh->value_sz, dty, indp, rlenp, 0, mode);
  if (status != OCI_SUCCESS) {
    oci8_unlink((oci8_handle_t *)bh);
    bh->type = 0;
    oci8_raise(h->errhp, status, h->hp);
  }
  bh->type = OCI_HTYPE_DEFINE;
  bh->hp = dfnhp;
  bh->errhp = h->errhp;
  obj = bh->self;
  ary = rb_ivar_get(self, oci8_id_define_array);
  if (ary == Qnil) {
    ary = rb_ary_new();
    rb_ivar_set(self, oci8_id_define_array, ary);
  }
  rb_ary_store(ary, position - 1, obj);
  return obj;
}

#execute(*args) ⇒ Object

begin

— OCIStmt#execute(svc [, iters [, mode]])

execute statement at the ((<service context handle|OCISvcCtx>)).

:svc
   ((<service context handle|OCISvcCtx>))
:iters
   the number of iterations to execute.

   For select statement, if there are columns which is not defined
   by ((<OCIStmt#defineByPos>)) and this value is positive, it
   raises exception. If zero, no exception. In any case you must define
   all columns before you call ((<OCIStmt#fetch>)).

   For non-select statement, use positive value.

   Default value is 0 for select statement, 1 for non-select statement.

   note: Current implemantation doesn't support array fetch and batch mode, so
   valid value is 0 or 1.
:mode
   ((|OCI_DEFAULT|)), ((|OCI_BATCH_ERRORS|)), ((|OCI_COMMIT_ON_SUCCESS|)),
   ((|OCI_DESCRIBE_ONLY|)), ((|OCI_EXACT_FETCH|)), ((|OCI_PARSE_ONLY|)),
   any combinations of previous values, or ((|OCI_STMT_SCROLLABLE_READONLY|)).
   Default value is ((|OCI_DEFAULT|)).

   ((|OCI_BATCH_ERRORS|)) and ((|OCI_STMT_SCROLLABLE_READONLY|)) are not
   supported by current implementation.

correspond native OCI function: ((|OCIStmtExecute|))

end



492
493
494
495
496
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
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
 
# File 'ext/oci8/stmt.c', line 492

static VALUE oci8_stmt_execute(int argc, VALUE *argv, VALUE self)
{
  VALUE vsvc;
  VALUE viters;
  VALUE vmode;
  oci8_handle_t *h;
  oci8_handle_t *svch;
  ub4 mode;
  ub4 iters;
  ub2 stmt_type;
  sword rv;

  rb_scan_args(argc, argv, "12", &vsvc, &viters, &vmode);
  Get_Handle(self, h); /* 0 */
  Check_Handle(vsvc, OCISvcCtx, svch); /* 1 */
  if (argc >= 2) {
    iters = NUM2UINT(viters); /* 2 */
  } else {
    rv = OCIAttrGet(h->hp, OCI_HTYPE_STMT, &stmt_type, 0, OCI_ATTR_STMT_TYPE, h->errhp);
    if (rv != OCI_SUCCESS) {
      oci8_raise(h->errhp, rv, h->hp);
    }
    if (stmt_type == OCI_STMT_SELECT) {
      /* for select statement, default value 0. */
      iters = 0;
    } else {
      /* for non-select statement, default value 0. */
      iters = 1;
    }
  }
  Get_Int_With_Default(argc, 3, vmode, mode, OCI_DEFAULT); /* 3 */

  if (iters > 1) {
    rb_raise(rb_eArgError, "current implementation doesn't support array fatch or batch mode");
  }

  rv = OCIStmtExecute(svch->hp, h->hp, h->errhp, iters, 0, NULL, NULL, mode);
  if (rv == OCI_ERROR) {
    sb4 errcode;
    OCIErrorGet(h->errhp, 1, NULL, &errcode, NULL, 0, OCI_HTYPE_ERROR);
    if (errcode == 1000) {
      /* run GC to close unreferred cursors when ORA-01000 (maximum open cursors exceeded). */
      rb_gc();
      rv = OCIStmtExecute(svch->hp, h->hp, h->errhp, iters, 0, NULL, NULL, mode);
    }
  }
  if (IS_OCI_ERROR(rv)) {
    oci8_raise(h->errhp, rv, h->hp);
  }
  return self;
}

#fetch(*args) ⇒ Object

begin

— OCIStmt#fetch([nrows [, orientation [, mode]]])

fetch data from select statement.
fetched data are stored to previously defined ((<define handle|OCIDefine>)).

:nrows
   number of rows to fetch. If zero, cancel the cursor.
   The default value is 1.

   Because array fetch is not supported, valid value is 0 or 1.

:orientation
   orientation to fetch. ((|OCI_FETCH_NEXT|)) only valid.
   The default value is ((|OCI_FETCH_NEXT|)).

:mode
   ((|OCI_DEFULT|)) only valid.
   The default value is ((|OCI_DEFAULT|)).

:return value
   array of define handles, which are defined previously,
   or nil when end of data.

correspond native OCI function: ((|OCIStmtFetch|))

end



571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
 
# File 'ext/oci8/stmt.c', line 571

static VALUE oci8_stmt_fetch(int argc, VALUE *argv, VALUE self)
{
  VALUE vnrows;
  VALUE vorientation;
  VALUE vmode;
  oci8_handle_t *h;
  ub4 nrows;
  ub2 orientation;
  ub4 mode;
  sword rv;

  rb_scan_args(argc, argv, "03", &vnrows, &vorientation, &vmode);
  Get_Handle(self, h); /* 0 */
  Get_Int_With_Default(argc, 1, vnrows, nrows, 1); /* 1 */
  Get_Int_With_Default(argc, 2, vorientation, orientation, OCI_FETCH_NEXT); /* 2 */
  Get_Int_With_Default(argc, 3, vmode, mode, OCI_DEFAULT); /* 3 */

  rv = OCIStmtFetch(h->hp, h->errhp, nrows, orientation, mode);
  if (rv == OCI_NO_DATA) {
    return Qnil;
  }
  if (IS_OCI_ERROR(rv)) {
    oci8_raise(h->errhp, rv, h->hp);
  }
  return rb_ivar_get(self, oci8_id_define_array);
}

#paramGet ⇒ Object

#prepare(*args) ⇒ Object

begin

— OCIStmt#prepare(stmt [, language [, mode]])

set and prepare SQL statement.

:stmt
   SQL or PL/SQL statement
:language
   ((|OCI_NTV_SYNTAX|)), ((|OCI_V7_SYNTAX|)), or ((|OCI_V8_SYNTAX|)).
   Default value is ((|OCI_NTV_SYNTAX|))
:mode
   ((|OCI_DEFAULT|)) or ((|OCI_NO_SHARING|)). Default value is ((|OCI_DEFAULT|)).

   ((|OCI_NO_SHARING|)) disables ((<Shared Data Mode>)) for this statement.

correspond native OCI function: ((|OCIStmtPrepare|))

end



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
 
# File 'ext/oci8/stmt.c', line 169

static VALUE oci8_stmt_prepare(int argc, VALUE *argv, VALUE self)
{
  VALUE vsql, vlanguage, vmode;
  oci8_handle_t *h;
  oci8_string_t s;
  ub4 language;
  ub4 mode;
  sword rv;
  VALUE ary;
  VALUE hash;
  int i;

  rb_scan_args(argc, argv, "12", &vsql, &vlanguage, &vmode);
  Get_Handle(self, h); /* 0 */
  Get_String(vsql, s); /* 1 */
  Get_Int_With_Default(argc, 2, vlanguage, language, OCI_NTV_SYNTAX); /* 2 */
  Get_Int_With_Default(argc, 3, vmode, mode, OCI_DEFAULT); /* 3 */

  /* when a new statement is prepared, OCI implicitly free the previous 
   * statement's define and bind handles. 
   * But ruby's object don't know it. So free these handles in advance.
   */
  /* free define handles */
  ary = rb_ivar_get(self, oci8_id_define_array);
  if (ary != Qnil) {
    for (i = 0;i < RARRAY_LEN(ary);i++) {
      if (RARRAY_PTR(ary)[i] != Qnil)
	oci8_handle_free(RARRAY_PTR(ary)[i]);
    }
    rb_ivar_set(self, oci8_id_define_array, Qnil);
  }
  /* free bind handles */
  hash = rb_ivar_get(self, oci8_id_bind_hash);
  if (hash != Qnil) {
    rb_iterate(oci8_each_value, hash, oci8_handle_free, Qnil);
    rb_ivar_set(self, oci8_id_bind_hash, Qnil);
  }

  rv = OCIStmtPrepare(h->hp, h->errhp, s.ptr, s.len, language, mode);
  if (IS_OCI_ERROR(rv)) {
    oci8_raise(h->errhp, rv, h->hp);
  }
  return self;
}