[ruby-oci8-devel] Issue of binding nil element for Array DML

KUBO Takehiro kubo at jiubao.org
Sat Dec 29 07:51:27 EST 2007


Hi,

Liming Lian <liming.lian at oracle.com> writes:

> Hi,
>
> Open this thread to discuss issue of binding nil element for  Array DML.
>
> An example of normal insert:
>
> cursor = conn.parse("INSERT INTO test_table VALUE (:str)")
> cursor.bind_param(1, nil , String)
> cursor.exec
>
> The result of above example is inserting a row of "nil" to test_table. 
> Let's turn to array insert example:
>
> cursor = conn.parse("INSERT INTO test_table VALUE (:str)")
> cursor.max_array_size = 3
> cursor.bind_param(1, nil , String)

Isn't this a typo of bind_param_array?
Do you use bind_param explicitly?

> cursor.exec_array
>
> For this binding "nil" elements for array insert, I have several 
> optional results:
>
> 1) insert an array of "nil"
>
> 2) insert single row of "nil"
>
> 3) raise an error
>
> Personally, I prefer option 1). Any idea?

If it is not a typo of bind_param_array, I also 1).
In addition, if users set a value, all array elements are the value.

  cursor = conn.parse("INSERT INTO test_table VALUE (:str)")
  cursor.max_array_size = 3
  cursor.bind_param(1, 'abc', String)
  cursor.exec_array

 Inserted rows are:
  row1: 'abc'
  row2: 'abc'
  row3: 'abc'

We can implement it easily by just changing code as following.

stmt.c line 225-227
from:
    if (NIL_P(obind->tdo) && obind->maxar_sz > 0) {
        oci_lc(OCIBindArrayOfStruct(obind->base.hp.bnd, oci8_errhp, obind->alloc_sz, sizeof(sb2), 0, 0));
    }
to:
    if (NIL_P(obind->tdo) && obind->maxar_sz > 0) {
        oci_lc(OCIBindArrayOfStruct(obind->base.hp.bnd, oci8_errhp, obind->alloc_sz, sizeof(sb2), 0, 0));
    } else {
        /* All iterations refer to same position. */
        oci_lc(OCIBindArrayOfStruct(obind->base.hp.bnd, oci8_errhp, 0, 0, 0, 0));
    }

This will be useful when users want to set same values to a parameter.

  cursor = conn.parse("INSERT INTO test_table VALUE (:name, :sex)")
  cursor.max_array_size = 3
  cursor.bind_param_array(1, ['John', 'James', 'Charlee'], String)
  cursor.bind_param(2, 'male')
  cursor.exec_array

 Inserted rows are:
  row1: 'John', 'male'
  row2: 'James', 'male'
  row3: 'Charlee', 'male'

If it is a typo of bind_param_array, '3) raise an error.'

-- 
KUBO Takehiro <kubo at jiubao.org>


More information about the ruby-oci8-devel mailing list