Thread: ODBC driver chances function-call in a wrong way
Hello,
I have the following problem:
I have a PG function in my database which looks like this:
CREATE OR REPLACE FUNCTION help_sel0(IN a int,IN b char(2),OUT master_fileset INT, OUT master_type CHAR(2), OUT highest_number INT) RETURNS RECORD AS
$function$
DECLARE
r RECORD;
BEGIN
SELECT INTO r master_fileset,master_type,highest_number
FROM gen_master
WHERE master_fileset= a
AND master_type= b;
master_fileset:=r.master_fileset;
master_type:=r.master_type;
highest_number:=r.highest_number;
END;
$function$
LANGUAGE plpgsql;
When I now want to send with my ODBC-programm the following prepared statement to the server
{SELECT * FROM gen_master_sel0(?,?);} (with the parameters 10 and ‘S2’)
the driver recognizes that I’m calling a “stored procedure”, but he doesn’t notice the given parameter and changes the command to this
SELECT * FROM gen_master_sel0( 10, E'S2');() .
Is this a bug in the recent driver (8.3.0400) which I’m using or has anybody any idea what I might have done wrong? I have to say this is the first time I’m working with Postgres so maybe my function is not ok?!
I’m very thankful for ideas and suggestions J
Thx, Ida
Ida Schonfeld wrote: > Hello, > > > > I have the following problem: > > > > > > I have a PG function in my database which looks like this: > > > > CREATE OR REPLACE FUNCTION help_sel0(IN a int,IN b char(2),OUT > master_fileset INT, OUT master_type CHAR(2), OUT highest_number INT) > RETURNS RECORD AS > > $function$ > > DECLARE > > r RECORD; > > BEGIN > > SELECT INTO r > master_fileset,master_type,highest_number > > FROM gen_master > > WHERE master_fileset= a > > AND master_type= b; > > master_fileset:=r.master_fileset; > > master_type:=r.master_type; > > highest_number:=r.highest_number; > > END; > > $function$ > > LANGUAGE plpgsql; > > > > When I now want to send with my ODBC-programm the following prepared > statement to the server > > {SELECT * FROM gen_master_sel0(?,?);} (with the parameters 10 and ‘S2’) The above call is wrong. You should prepare the ODBC style procedure call {call gen_master_sel0(?,?)} or you can simply prepare the pg-specific procedure call SELECT * FROM gen_master_sel0(?,?) . regards, Hiroshi Inoue