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

KUBO Takehiro kubo at jiubao.org
Thu Dec 20 11:33:52 EST 2007


Hi Liming,

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

> Hi Kubo,
>> 1. OCI8::Cursor#max_array_size = positive_number
>>
>>   Users can call max_array_size= many times during Cursor process. But
>>   every time at calling max_array_size= all the binds are clear from the
>>   Cursor. In that situation, they have to re-bind all the parameter array.
>
> Do you mean, the binds should be clear even the new value of 
> max_array_size is equal to the old one?

Yes.

> Take a look at following example:
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.max_array_size = 3
> cursor.bind_param_array(1, ["test1", "test2", "test3"])
> cursor.bind_param_array(2, [20, 21, 22])
> cursor.max_array_size = 3
> cursor.exec_array
>
> IMO, this should be a correct usage.  Users don't need to re-bind the 
> parameter since the max_array_size doesn't change.

In that case, users don't need to call max_array_size=.
I can't imagine the merit that users set same size without re-binding.
If it is rare and users have a workaround, I prefer to cut off specs for
special cases.

IMO, we can disallow users to re-set max_array_size. Once the size is
set, setting again raises an error. If users want to change the size,
they need to create a new cursor for a workaround. But I'm not sure
that re-setting of max_array_size is rare. So I don't presist in this
idea.

>> 2. OCI8::Cursor#bind_param_array(key, var_array, type = nil, max_item_length = nil)
>>
>>   If batch_size= is not called, an error is raised.
>
> I guess you mean "max_array_size=" here, right?
>
>>   If the size of var_array is less than max_array_size, the rest values
>>   are NULL.
>>
>
> I am considering if it is proper to insert nil values that users may not 
> want. Example:
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.max_array_size = 3
> cursor.bind_param_array(1, ["test1"])
> cursor.bind_param_array(2, [20])
> cursor.exec_array
>
> The rows inserted to "test_table" are
> row1: test1, 20
> row2: NULL, NULL
> row3: NULL, NULL
>
> I am not sure if users would be happy on those rows full of useless NULL 
> values.
>
>> 4. OCI8::Cursor#exec_array(iteration_count = nil)
>>
>>   If iteration_count is nil, max_array_size is used instead.
>>   If iteration_count > max_array_size, an error is raised.
>>
>>   
> Thanks kubo, here you gave me the answer to my previous worry. If users 
> don't want to insert rows with full NULL values, they can add a 
> parameter iteration_count  to exec_array.

Yes.

Yet another idea is the iteration_count is the size of bind values.

 cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
 cursor.max_array_size = 3
 cursor.bind_param_array(1, ["test1"])
 cursor.bind_param_array(2, [20])
 cursor.exec_array  # iteration_count is 1 because parameter's
                    # array sizes are all 1.
 cursor.bind_param_array(1, ["test2", "test3"])
 cursor.bind_param_array(2, [20, 30])
 cursor.exec_array  # iteration_count is 2 because parameter's
                    # array sizes are all 2.
 cursor.bind_param_array(1, ["test4", "test5"])
 cursor.bind_param_array(2, [40])
 cursor.exec_array  # raise an error because array sizes are not same.

This may be usable than my previous idea.

-- 
KUBO Takehiro <kubo at jiubao.org>


More information about the ruby-oci8-devel mailing list