Stored Procedures and Parameter binding

[Ivan Smahin]

Hello Bart,

Thursday, September 20, 2007, 4:56:53 PM, you wrote:

> On 20-sep-07, at 15:48, Ivan Smahin wrote:

>> Hello Bart,
>>
>> Thursday, September 20, 2007, 4:33:19 PM, you wrote:
>>
>>> Hi List,
>>> Hi Thorsten
>>
>>> It seems one cannot use parameter binding with inserts inside stored
>>> procedures because there does not seem to be a way to pass an array
>>> as a parameter to the stored procedure ?
>>
>>> This is a pity (if true) . Do I need to file a feature request for
>>> the possibility to pass arrays as parameters for a stored procedure ?
>>
>> We  think  it is redundant. There is a strong enough specification for
>> procedure  -  param  list.  Binding  params seems to be something like
>> hiding  additional specification  which  is  not  declared in any  
>> way and can be
>> wrongfully  omitted by the caller. Mostly, binding allows to apply the
>> same  (parsed  and  preprocessed) plan many times with different data.
>> But  procedure  plan  is  also  preprocessed  once  (thanks  to  param
>> declarations).
>>
>> Why you don't want to pass params for insert as sp arguments?


> I seem to recall that using parameter binding I did not have to worry
> about escaping. With arguments I am not so sure (hence the other mail
> with question about escaping)

I see. In case of binding you pass just array of values rather than sp
call. Here - it is part of the query.

Possible workaround (I have not tried it but you can :) )

db.SQLExecute( 'SET @var1 = :1', singleParamList1 );
db.SQLExecute( 'SET @var2 = :1', singleParamList2 );

db.SQLExecute(
'CREATE OR REPLACE PROCEDURE sp1()
BEGIN
    INSERT INTO t1 VALUES( @var1, @var2 );
END' );

db.SQLExecute( 'call sp1()' );

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com