Firebird - Create Store Procedure

Criar Store Procedure no firebird.

cSql  = [create procedure teste(buscar varchar(40)) ]
cSql += [ returns (registro varchar(40)) ]
cSql += [ AS ]
cSql += [BEGIN ]
cSql += [ select nome from tabela where :buscar into :registro]
database.executeSQL(cSql)

If shooting the symbol: before the parameters shows the error of unknown 
column, if I leave the symbol: the parameters before presenting the 
message of unknown parameter.

This was just a simple example to demonstrate the problem that is happening.
I need within my application create Store Procedure updates to the 
database of my customers and this error that preventing this process.


Lysander escreveu:
> Wagner Rocha schrieb:
>> Criar Store Procedure no firebird.
>>
>> cSql  = [create procedure teste(buscar varchar(40)) ]
>> cSql += [ returns (registro varchar(40)) ]
>> cSql += [ AS ]
>> cSql += [BEGIN ]
>> cSql += [ select nome from tabela where :buscar into :registro]
>> database.executeSQL(cSql)
>>
>> If shooting the symbol: before the parameters shows the error of 
>> unknown column, if I leave the symbol: the parameters before 
>> presenting the message of unknown parameter.
>> How to solve?
> 
> I could be wrong, but I think this cannot be done this way at all.
> To paraphrase a VARIABLE into a complete command, you would need the 
> "EXECUTE STATEMENT" function (in the text of the StoredProc).
> 
> Like this:
> 
> Declare Variable V_Buscar as VarChar(40);
> .
> .
> .
> 
> V_Buscar = 'Select nome from tabela where ' ||:Buscar;
> V_Buscar = :V_Buscar||' into '||:Registro;
> 
> Execute Statement :V_Buscar;
> suspend;
> 
> 
> This _Should_ already work with FB 1.5.2; I did not test it.
> Later Editions (1.5.3, and 1.5.4 and 2.x) added even more support for 
> the Execute Statement function.

Wagner Rocha schrieb:
> Criar Store Procedure no firebird.
> 
> cSql  = [create procedure teste(buscar varchar(40)) ]
> cSql += [ returns (registro varchar(40)) ]
> cSql += [ AS ]
> cSql += [BEGIN ]
> cSql += [ select nome from tabela where :buscar into :registro]
> database.executeSQL(cSql)
> 
> If shooting the symbol: before the parameters shows the error of unknown 
> column, if I leave the symbol: the parameters before presenting the 
> message of unknown parameter.
> How to solve?

I could be wrong, but I think this cannot be done this way at all.
To paraphrase a VARIABLE into a complete command, you would need the 
"EXECUTE STATEMENT" function (in the text of the StoredProc).

Like this:

Declare Variable V_Buscar as VarChar(40);
.
.
.

V_Buscar = 'Select nome from tabela where ' ||:Buscar;
V_Buscar = :V_Buscar||' into '||:Registro;

Execute Statement :V_Buscar;
suspend;


This _Should_ already work with FB 1.5.2; I did not test it.
Later Editions (1.5.3, and 1.5.4 and 2.x) added even more support for 
the Execute Statement function.

In my understanding the command EXECUTESQL simplismente must send the 
command to the database and does not hold any kind of validation. Here 
is the syntax correct.
Thank your suggestion, but I have the need to pass parameters to the 
Store Procedure realilzar some treatments in a group of records and 
return these values, to get an idea of what precise below my SP.
The worst I have this running in several bank clients and have to 
perform the update via my application, will have problems.

Thank you.


My Store Procedure

CREATE PROCEDURE SALDO_INSUMO_LOTE (
     I_CODIGOINSUMO CHAR(7))
RETURNS (
     O_CODIGOINSUMO CHAR(7),
     O_DESCRICAO CHAR(30),
     O_LOTE CHAR(20),
     O_SALDO DOUBLE PRECISION)
AS
BEGIN
  FOR SELECT DISTINCT MOV_INSUMO.CODIGOINSUMO,
  MOV_INSUMO.DESCRICAO,
  MOV_INSUMO.LOTE
  FROM MOV_INSUMO
  WHERE MOV_INSUMO.CODIGOINSUMO = :I_CODIGOINSUMO
  INTO :O_CODIGOINSUMO,:O_DESCRICAO,:O_LOTE
  DO
  BEGIN
  SELECT SUM(MOV_INSUMO.qtdeinsunifarma) FROM MOV_INSUMO
  WHERE MOV_INSUMO.CODIGOINSUMO=:I_CODIGOINSUMO AND MOV_INSUMO.LOTE=:O_LOTE
  INTO :O_SALDO;
  SUSPEND;
  END
  END


Lysander escreveu:
> Wagner Rocha schrieb:
>>
>> This was just a simple example to demonstrate the problem that is 
>> happening.
>> I need within my application create Store Procedure updates to the 
>> database of my customers and this error that preventing this process.
> 
> 
> Okay... I tested it right now on Firebird 1.52 and I am VERY surprised.
> Until now I don't know what's going on....
> 
> 1.)
> You are right, the following does NOT work from dBase
> 
> csql = "create procedure test_SP (vbuscar varchar(40)) returns
(registro 
> varchar(40)) as begin registro = :vbuscar; suspend; end  "
> db.executesql(csql)
> 
> The error message is: "parameter not defined: vbuscar
> 
> That is very strange, because I don't have an explanation _WHY_ it does 
> complain about the parameter. The syntax is correct and it works for 
> other languages (delphi, C#). I _THINK_ this is an error message coming 
> from the BDE, but I don't understand why the BDE at all intervenes with 
> that string...
> 
> 
> 2.)
> The following is even more impressing to me, because it DOES work:
> 
> csql = "create procedure test_SP (vbuscar varchar(40)) returns
(registro 
> varchar(40)) as begin registro = vbuscar; suspend; end  "
> db.executesql(csql)
> 
> Note that the difference is just in replacing ":vbuscar" by
"vbuscar"
> 
> The stored procedure is created and can be run on the server now.
> But I don't understand WHY it works.
> I THOUGHT that the Firebird engine would not allow this syntax, because 
> otherwise it would be too easy to mix up field names with variables 
> names. The components I used in Delphi do _NOT_ allow this syntax; they 
> already block this syntax client-sided.
> The same is with IBExpert editor (probably because it is using the same 
> delphi components. IBExpert is written in Delphi)
> 
> Anyway, now you know how it works.
> 
> 
> The reason why your first example did not work is explained by my first 

Wagner Rocha schrieb:
> 
> This was just a simple example to demonstrate the problem that is 
> happening.
> I need within my application create Store Procedure updates to the 
> database of my customers and this error that preventing this process.


Okay... I tested it right now on Firebird 1.52 and I am VERY surprised.
Until now I don't know what's going on....

1.)
You are right, the following does NOT work from dBase

csql = "create procedure test_SP (vbuscar varchar(40)) returns (registro 
varchar(40)) as begin registro = :vbuscar; suspend; end  "
db.executesql(csql)

The error message is: "parameter not defined: vbuscar

That is very strange, because I don't have an explanation _WHY_ it does 
complain about the parameter. The syntax is correct and it works for 
other languages (delphi, C#). I _THINK_ this is an error message coming 
from the BDE, but I don't understand why the BDE at all intervenes with 
that string...


2.)
The following is even more impressing to me, because it DOES work:

csql = "create procedure test_SP (vbuscar varchar(40)) returns (registro 
varchar(40)) as begin registro = vbuscar; suspend; end  "
db.executesql(csql)

Note that the difference is just in replacing ":vbuscar" by
"vbuscar"

The stored procedure is created and can be run on the server now.
But I don't understand WHY it works.
I THOUGHT that the Firebird engine would not allow this syntax, because 
otherwise it would be too easy to mix up field names with variables 
names. The components I used in Delphi do _NOT_ allow this syntax; they 
already block this syntax client-sided.
The same is with IBExpert editor (probably because it is using the same 
delphi components. IBExpert is written in Delphi)

Anyway, now you know how it works.


The reason why your first example did not work is explained by my first