CODEWITHSUNDEEP
sqlfirebird
Vladimir Savić
Vladimir Savić

Reputation: 57

Using parameter in PSQL

I have a problem with using parameter in psql. How to properly use the parameter in where clause. There are no errors compiling the procedure which I listed below. But no results when I pass 'bla bla bla' string.

CREATE PROCEDURE SELECTCATALOGUE (
 TXT  VARCHAR(30))
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
BEGIN     
for execute statement
'select code,name
            from catalogue
            where name='||:TXT
into :CODE,:NAME
do 
   suspend;
END

But, when I execute this procedure by replacing the parameter for example:

CREATE PROCEDURE SELECTCATALOGUE (
 TXT  VARCHAR(30))
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
BEGIN     
for execute statement
'select code,name
            from catalogue
            where name=''bla bla bla'''
into :CODE,:NAME
do 
   suspend;
END

I'm getting results properly. Am I missing something, how to make it work?

EDIT: Ok, here is one more thing. This procedure:

CREATE PROCEDURE SELECTCATALOGUE
RETURNS (
 CODE VARCHAR(9),
 NAME VARCHAR(50))
AS 
declare variable stmt varchar(1000);
declare variable paramTXT varchar(100);
declare variable paramTXT1 varchar(100);
declare variable T varchar(1000);
BEGIN     
paramTXT='PLO'||'%';
paramTXT1='REM'||'%';
T='paramTXT,paramTXT1';
stmt='select code,name
            from catalogue
            where name like ? or name like ? order by name';
for execute statement (
  stmt
)
            (paramTXT,paramTXT1)
into :CODE,:NAME
do 
   suspend;
END

Gives me back proper results. Is there a way to use "T" string instead "(paramTXT,paramTXT1)" and that can give me results?

Upvotes: 1

Views: 114

Answers (1)

ain
ain

Reputation: 22749

In your first version, you concatenate two strings which result in following string:

select code,name from catalogue where name=bla bla bla

You see that there is no quotes around the string bla bla bla, so the engine should treat it as a column name and usually this should result in a error like "column bla bla bla not found" or some such. If it happens that you send in an value which matches some column name then you would get empty resultset unless there is row(s) where the two columns have the same value.

To fix it, use parameterized statement:

for execute statement (
   'select code,name
            from catalogue
            where name = :parName')
   (parName := TXT)
   into :CODE,:NAME
do

See the documentation for the full syntax of the execute statement.

Upvotes: 3

Related Questions