"no value found for parameter 1" error for query with no parameters
От | Phil Endecott |
---|---|
Тема | "no value found for parameter 1" error for query with no parameters |
Дата | |
Msg-id | 1171229902168@dmwebmail.belize.chezphil.org обсуждение исходный текст |
Ответы |
Re: "no value found for parameter 1" error for query with no parameters
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-general |
Dear All, I have recently been converting some old code to use $n parameter placeholders, rather than inlining the values in the query string. It has mostly gone well, but I have encountered some odd behaviour with a temporary view. I'm using libpq to talk to an 8.1.0 server. This is the old code which works: create or replace temporary view u_messages as select * from messsages where owner='phil'; select msg_id from u_messages where msgdate>'2007-02-11 21:36:43.886004+00'::timestamp with time zone - '1 day'::interval order by msg_id; I converted the first query like this: create or replace temporary view u_messages as select * from messsages where owner=$1; I run this with "phil" as the value for $1. This completes without error. Then I run the second, unchanged, query. It does not have any $n placeholders, but I get an error: ERROR: no value found for parameter 1 So I think it is complaining that I have not supplied enough parameters even though none are needed. I have used strace to look at the communication with the backend: send(4, "P\0\0\0\223\0select msg_id from u_messages where msgdate > \'2007-02-11 21:36:43.886004+00\'::timestamp with time zone - \'1 day\'::interval order by msg_id\0\0\0B\0\0\0\16\0\0\0\0\0\0\0\1\0\1D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4", 185, 0) = 185 Which I have broken down as follows: P \0\0\0\223 \0 select msg_id from u_messages where msgdate > \'2007-02-11 21:36:43.886004+00\'::timestamp with time zone - \'1 day\'::interval order by msg_id\0 \0\0 B \0\0\0\16 \0 \0 \0\0 \0\0 \0\1 \0\1 D \0\0\0\6 P \0 E \0\0\0\t \0 \0\0\0\0 S \0\0\0\4 That looks OK. recv(4, "1\0\0\0\0042\0\0\0\4T\0\0\0\37\0\1msg_id\0\0\2\20\224\0\1\0\0\0\27\0\4 \377\377\377\377\0\1E\0\0\0OSERROR\0C42704\0Mno value found for parameter 1\0 Fparams.c\0L120\0RlookupParam\0\0", 16384, 0) = 122 1 \0\0\0\004 (Parse complete) 2 \0\0\0\4 (Bind complete) T \0\0\0\37 \0\1 msg_id\0 \0\2\20\224 \0\1 \0\0\0\27 \0\4 \377\377\377\377 \0\1 E \0\0\0O S ERROR\0 C 42704\0 M no value found for parameter 1\0 F params.c\0 L 120\0 R lookupParam\0 \0 Also OK I think, apart from the unexpected error. Is it normal to get the 'T' line before the error? For completeness here is the earlier communication to create the temporary view: P \0\0\0` \0 create or replace temporary view u_messages as select * from messages where owner=$1\0 \0\1 \0\0\0\31 B \0\0\0\30 \0 \0 \0\1 \0\0 \0\1 \0\0\0\4 phil \0\1 \0\1 D \0\0\0\6 P \0 E \0\0\0\t \0 \0\0\0\0 S \0\0\0\4 and the response: 1 \0\0\0\004 2 \0\0\0\4 n \0\0\0\4 C \0\0\0\20 CREATE VIEW\0 Z \0\0\0\5 I It's as if the server has been confused by the presence of the $1 in the view query and has tried to substitute a value for it from those supplied when the view is used. I know next to nothing about the internals but I could imagine something like this happening since it must expand the query to include the view at some point. Maybe I'm not allowed to use $n in "create view" queries at all? Unfortunately there must be a bit more to it than this as the simple test case that I have prepared does not fail. I will investigate further. But I thought I'd post now in case anyone is aware of any known issues in this area, or has any suggestions. Unrelated to this, it used to be true that recreating temporary tables (and presumably views) would confuse stored procedures (and presumably prepared statements) that had cached some sort of pointer to the old instance of the table (or view). See for example this thread: http://archives.postgresql.org/pgsql-general/2004-09/msg00316.php Is this still true? (This isn't the cause of the problems above as it doesn't get anywhere near running any queries for a second time, but I will need to re-visit this issue.) Cheers, Phil. (You are welcome to CC: me in any replies, so I'll see them more quickly.)
В списке pgsql-general по дате отправления:
Следующее
От: "Anton Melser"Дата:
Сообщение: getting postgres to emulate mysql/sqlserver bit datatype