Thread: EXECUTE problems
I compiled the cvs version of pgsql yesterday afternoon and I'm having some problems with EXECUTE statement. If I do the following the query is executed: PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); EXECUTE TEST_STATEMENT_1(null) But why this doesn't? PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); EXECUTE TEST_STATEMENT_1(DEFAULT) Btw, the field is a varchar. -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
Have u define a value for Default ? / Vc definiu um valor para Default? On 22 Nov 2002 12:25:46 -0200 Felipe Schnack <felipes@ritterdosreis.br> wrote: > I compiled the cvs version of pgsql yesterday afternoon and I'm having > some problems with EXECUTE statement. If I do the following the query is > executed: > > PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); > EXECUTE TEST_STATEMENT_1(null) > > But why this doesn't? > > PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); > EXECUTE TEST_STATEMENT_1(DEFAULT) > > Btw, the field is a varchar. > > -- > > Felipe Schnack > Analista de Sistemas > felipes@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Faculdade Ritter dos Reis > www.ritterdosreis.br > felipes@ritterdosreis.br > Fone/Fax.: (51)32303328 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Renê Salomão Ibiz Tecnologia -- www.ibiz.com.br (011) 5579-3178 - R. 211
Yes, I did... sorry, the error message was "parser: parse error at or near "default" at character 26". Seems to me that execute doesn't "know" DEFAULT keyrword? On Fri, 2002-11-22 at 13:18, Renê Salomão wrote: > Have u define a value for Default ? / Vc definiu um valor para Default? > > On 22 Nov 2002 12:25:46 -0200 > Felipe Schnack <felipes@ritterdosreis.br> wrote: > > > I compiled the cvs version of pgsql yesterday afternoon and I'm having > > some problems with EXECUTE statement. If I do the following the query is > > executed: > > > > PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); > > EXECUTE TEST_STATEMENT_1(null) > > > > But why this doesn't? > > > > PREPARE TEST_STATEMENT_1(text) AS insert into teste values ( $1 ); > > EXECUTE TEST_STATEMENT_1(DEFAULT) > > > > Btw, the field is a varchar. > > > > -- > > > > Felipe Schnack > > Analista de Sistemas > > felipes@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Faculdade Ritter dos Reis > > www.ritterdosreis.br > > felipes@ritterdosreis.br > > Fone/Fax.: (51)32303328 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > -- > Renê Salomão > Ibiz Tecnologia -- www.ibiz.com.br > (011) 5579-3178 - R. 211 -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
Felipe Schnack <felipes@ritterdosreis.br> writes: > Yes, I did... sorry, the error message was "parser: parse error at > or near "default" at character 26". Seems to me that execute doesn't > "know" DEFAULT keyrword? Yeah, the handling of the DEFAULT keyword is currently done within the parsing of the INSERT statement itself. Not sure if that should be changed... You can insert the default value into a column by excluding it from the column list in the INSERT statement -- that should work for you here... Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
No, no... in pgsql 7.3 you can do "insert into test (columnName) values (DEFAULT)" and it works! It just doesn't work for prepared statements. Maybe I should join the pgsql-developers list? On Fri, 2002-11-22 at 14:45, Neil Conway wrote: > Felipe Schnack <felipes@ritterdosreis.br> writes: > > Yes, I did... sorry, the error message was "parser: parse error at > > or near "default" at character 26". Seems to me that execute doesn't > > "know" DEFAULT keyrword? > > Yeah, the handling of the DEFAULT keyword is currently done within the > parsing of the INSERT statement itself. Not sure if that should be > changed... > > You can insert the default value into a column by excluding it from > the column list in the INSERT statement -- that should work for you > here... > > Cheers, > > Neil > > -- > Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC > -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
You're right, I didn't :-) I think it should... after all, why you can use DEFAULT in an normal insert but you can't in an prepared insert statement? I'm asking this because I'm implementing methods to support the new DEFAULT keyword in pgsql 7.3 for the JDBC driver... but if I can't use it in prepared sqls, a great deal of it usefulness will be shortened... Thanks! On Fri, 2002-11-22 at 16:20, Neil Conway wrote: > Felipe Schnack <felipes@ritterdosreis.br> writes: > > No, no... in pgsql 7.3 you can do "insert into test (columnName) > > values (DEFAULT)" and it works! It just doesn't work for prepared > > statements. Maybe I should join the pgsql-developers list? > > Read my reply again, you clearly didn't understand me. > > Cheers, > > Neil > > -- > Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC > -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
Felipe Schnack <felipes@ritterdosreis.br> writes:
> I think it should... after all, why you can use DEFAULT in an normal
> insert but you can't in an prepared insert statement?
PREPARE isn't specific to INSERT statements. Even when you use it for
one, it's not very obvious how to assign a default value to the PREPARE
parameters; in any but the most trivial cases, there's not a one-for-one
correspondence between PREPARE parameters and target columns that might
(or might not) have defaults.
> I'm asking this because I'm implementing methods to support the new
> DEFAULT keyword in pgsql 7.3 for the JDBC driver... but if I can't use
> it in prepared sqls, a great deal of it usefulness will be shortened...
PREPAREing a query as trivial as an INSERT ... VALUES is largely a
waste of time anyway. So I cannot get excited about this.
regards, tom lane
> > I think it should... after all, why you can use DEFAULT in an normal > > insert but you can't in an prepared insert statement? > PREPARE isn't specific to INSERT statements. Even when you use it for > one, it's not very obvious how to assign a default value to the PREPARE > parameters; in any but the most trivial cases, there's not a one-for-one > correspondence between PREPARE parameters and target columns that might > (or might not) have defaults. Well, I don't know a lot about the inner workings of pgsql (I know very little actually), but when you prepare the execution of an insert you probably have "pointers" to database metadata for all mapped columns, don't you? So if the DEFAULT keyword is found, it should fetch the default value, and raise an exception if it doesn't exist... > > I'm asking this because I'm implementing methods to support the new > > DEFAULT keyword in pgsql 7.3 for the JDBC driver... but if I can't use > > it in prepared sqls, a great deal of it usefulness will be shortened... > PREPAREing a query as trivial as an INSERT ... VALUES is largely a > waste of time anyway. So I cannot get excited about this. Well, would be a waste of time when you got this insert into a loop, etc? I don't think an SELECT statement is more adequate for "prepareing" than an insert, so (sorry) I don't get your point > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
How do I prepare an statement in C context, ie which libPQ function does that Felipe Schnack wrote: > You're right, I didn't :-) > I think it should... after all, why you can use DEFAULT in an normal >insert but you can't in an prepared insert statement? > I'm asking this because I'm implementing methods to support the new >DEFAULT keyword in pgsql 7.3 for the JDBC driver... but if I can't use >it in prepared sqls, a great deal of it usefulness will be shortened... > > Thanks! > >On Fri, 2002-11-22 at 16:20, Neil Conway wrote: > > >>Felipe Schnack <felipes@ritterdosreis.br> writes: >> >> >>>No, no... in pgsql 7.3 you can do "insert into test (columnName) >>>values (DEFAULT)" and it works! It just doesn't work for prepared >>>statements. Maybe I should join the pgsql-developers list? >>> >>> >>Read my reply again, you clearly didn't understand me. >> >>Cheers, >> >>Neil >> >>-- >>Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC >> >> >>
Medi Montaseri <medi.montaseri@intransa.com> writes: > How do I prepare an statement in C context, ie which libPQ function > does that Prepared statements are currently implemented as SQL-level constructs, so there's no specific libpq function for it. http://developer.postgresql.org/docs/postgres/sql-prepare.html http://developer.postgresql.org/docs/postgres/sql-execute.html http://developer.postgresql.org/docs/postgres/sql-deallocate.html Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Perfect...thank you.... Before I head out to read those links....do I have to re-prepare if I loose my connection. Or could/would the backend use it regardless of what connection I come in.... Also I need some help using Async query technique....anyone used who I can get some help from....I need to confirm my understanding of how to use the Async Query technique Neil Conway wrote: >Medi Montaseri <medi.montaseri@intransa.com> writes: > > >>How do I prepare an statement in C context, ie which libPQ function >>does that >> >> > >Prepared statements are currently implemented as SQL-level >constructs, so there's no specific libpq function for it. > >http://developer.postgresql.org/docs/postgres/sql-prepare.html >http://developer.postgresql.org/docs/postgres/sql-execute.html >http://developer.postgresql.org/docs/postgres/sql-deallocate.html > >Cheers, > >Neil > > >
Medi Montaseri <medi.montaseri@intransa.com> writes:
> Before I head out to read those links....do I have to re-prepare if
> I loose my connection. Or could/would the backend use it regardless
> of what connection I come in....
No, prepared statements are currently stored locally, per-backend --
that means that a prepared statement can only be EXECUTE'd by the same
client that PREPARE'd it. We looked into storing prepared queries in
shared memory (in fact, the first implementations of the idea did just
that), but it turned out not to be worth the hassle.
> Also I need some help using Async query technique....anyone used who
> I can get some help from....I need to confirm my understanding of
> how to use the Async Query technique
Do you have a specific question that isn't addressed by the
documentation?
http://developer.postgresql.org/docs/postgres/libpq-async.html
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC