Stored Procedures and Parameter binding

[Bart Pietercil]

On 20-sep-07, at 16:08, Ivan Smahin wrote:

> 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()' );
>
> -- 

Wouldn't this also work ?

create or replace procedure DoInsert(param1,param2)
Begin
	INSERT INTO tbl1 ( fldLong, fldString ) VALUES ( :1, :2 ) binded  
WITH { param1, param2 };
End


Would this work? And if this works could we do without escaping  
strings ?

TIA

Bart