[ruby-oci8-devel] Proposal for bind_param_array function prototype

[Liming Lian]

Hi,

I have a workaround for the problem of execution count. Following is my 
proposal.

We can add a function OCI8::Cursor#set_batch_size to set the iteration 
count for statement execution.  This function needs a parameter "size" 
like following: set_batch_size(size). When users want to bind array, 
they *must* call OCI8::Cursor#set_batch_size first. And they must also 
make sure the size of the binding array is equal to the size declared at 
set_batch_size. Otherwise, an error will be raised to prompt users 
incoming array with incorrect size. Besides set_batch_size, like the 
implementation of array DML in Perl DBD::ORACLE, we should also add 
another function OCI8::Cursor#exec_array. Bind_param_array and 
bind_param cannot be mixed in the same statement execution, and 
bind_param_array *must* be used with OCI8::Cursor#exec_array; using 
bind_param_array will have no effect for OCI8::Cursor#exec.

Users can call set_batch_size many times during Cursor process. But 
every time at calling set_batch_size, if the new size isn't equal to the 
old one, all the binds are clear from the Cursor. In that situation, 
they have to re-bind all the parameter array.

Look at following usage scenarios for demonstration.

1) Correct usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, ["test1", "test2", "test3"])
Cursor.bind_param_array(2, [20, 21, 22])
Cursor.exec_array

2) Correct usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, nil, String)
Cursor.bind_param_array(2, nil, Fixnum)
Cursor[1] = ["test1", "test2", "test3"]
Cursor[2] = [20, 21, 22]
Cursor.exec_array

3) Correct usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, ["test1", "test2", "test3"])
Cursor.bind_param_array(2, [20, 21, 22])
Cursor.set_batch_size(2)
Cursor.bind_param_array(1, ["test4", "test5"])
Cursor.bind_param_array(2, [23, 24])
Cursor.exec_array

4) Incorrect usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.bind_param_array(1, ["test1", "test2"])
Cursor.bind_param_array(2, [20, 21])
Cursor.exec_array

 == Error raised: should call set_batch_size first

5) Incorrect usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, ["test1", "test2"])
Cursor.bind_param_array(2, [20, 21])
Cursor.exec_array

 == Error raised: incorrect incoming bound array size

6) Incorrect usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, ["test1", "test2"])
Cursor.bind_param_array(2, [20, 21])
Cursor.exec

 == Error raised: should call exec_array to process array DML

7) Incorrect usage

Cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
Cursor.set_batch_size(3)
Cursor.bind_param_array(1, ["test1", "test2"])
Cursor.bind_param_array(2, [20, 21])
Cursor.set_batch_size(2)
Cursor.exec_array

 == Error raised: OCIError: ORA-01008: not all variables bound
 == Reason: didn't re-bind the array after calling set_batch_size with a 
new batch size
 

With the new function set_batch_size, the function bind_param_array 
doesn't need a parameter "max_array_size" any more. The parameter size 
passed to set_batch_size will be used as max_array_size internally. So 
the prototype of bind_param_array has been updated to:

bind_param_array(key, var_array, type = nil, max_item_length = nil)


Liming
> I haven't  fully tested the above modifications but I can insert String 
> arrays without any problem.  The "Cursor[Key] = val" doesn't cause 
> problem with bind_param, because the iteration count for a normal insert 
> is always 1. In case of array insert, the iteration count changes with 
> calling of "Cursor[Key] = val". Obviously it is unrealistic to make 
> assumption as what I mentioned above. So I am thinking other ways to get 
> the exact size of the array user has bound at the calling of cursor.exec.
>
> Hope I am clear. Any ideas?
>
> _______________________________________________
> ruby-oci8-devel mailing list
> ruby-oci8-devel at rubyforge.org
> http://rubyforge.org/mailman/listinfo/ruby-oci8-devel
>