Vladimir Savić Vladimir Savić - 3 months ago 8
SQL Question

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?

ain ain
Answer

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.

Comments