Re: PQexec(), what should I do for the "NULL in command" problem?

Hello:

As you know the interface of PQexec():
PGresult *PQexec(PGconn *conn, const char *command);

"command" contains some SQL statements.

But there exist "\0" in some data filed, and I found PQexec() failed
on such situation.
Something like this:

PQexec(conn, "INSERT INTO tttt('ttttt', 'abc\0abc');

Does anyone know something about this?
How can I insert a record which has some NULLs in data filed?

Best Regards,
Jason

On Thu, Nov 08, 2007 at 06:39:23PM -0800, jason wrote:
> As you know the interface of PQexec():
> PGresult *PQexec(PGconn *conn, const char *command);
>
> "command" contains some SQL statements.
>
> But there exist "\0" in some data filed, and I found PQexec() failed
> on such situation.

Two ways:
- out of line parameters
- escape the nulls, like \0

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

On Nov 12, 2007 11:13 PM, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Thu, Nov 08, 2007 at 06:39:23PM -0800, jason wrote:
> > As you know the interface of PQexec():
> > PGresult *PQexec(PGconn *conn, const char *command);
> >
> > "command" contains some SQL statements.
> >
> > But there exist "\0" in some data filed, and I found PQexec() failed
> > on such situation.

Thanks for reply :-)
But would you please give me more details or some references?

> Two ways:
> - out of line parameters
How to? Is there any function for this? I mean the C interface.

> - escape the nulls, like \0
Do you mean this function?
#unsigned char *PQescapeBytea(const unsigned char *from,
# size_t from_length,
# size_t *to_length);

But after the "escape", the data is changed, and it cannot
automatically change back when insert them into database.
I have to "unescape" it when query. Is this necessary?

Best Regards,
Jason

On Tue, Nov 13, 2007 at 10:52:09AM +0800, yang zhenyu wrote:
> > > But there exist "\0" in some data filed, and I found PQexec() failed
> > > on such situation.
>
> Thanks for reply :-)
> But would you please give me more details or some references?
>
> > Two ways:
> > - out of line parameters
> How to? Is there any function for this? I mean the C interface.

PQexecParams.

> > - escape the nulls, like \0
> Do you mean this function?
> #unsigned char *PQescapeBytea(const unsigned char *from,
> # size_t from_length,
> # size_t *to_length);
>
> But after the "escape", the data is changed, and it cannot
> automatically change back when insert them into database.
> I have to "unescape" it when query. Is this necessary?

True. You should realise that the text data type does not handle
embedded nulls, that's why the bytea datatype exists. If you don't want
the full conversion, you'll need to handle your own escaping.
PostgreSQL is not going return you strings with embedded NULLs...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

> > > - out of line parameters
> > How to? Is there any function for this? I mean the C interface.
>
> PQexecParams.
>
> > > - escape the nulls, like \0
> > Do you mean this function?
> > #unsigned char *PQescapeBytea(const unsigned char *from,
> > # size_t from_length,
> > # size_t *to_length);
> >
> > But after the "escape", the data is changed, and it cannot
> > automatically change back when insert them into database.
> > I have to "unescape" it when query. Is this necessary?
>
> True. You should realise that the text data type does not handle
> embedded nulls, that's why the bytea datatype exists. If you don't want
> the full conversion, you'll need to handle your own escaping.
> PostgreSQL is not going return you strings with embedded NULLs...

Thank you very much, I got that :)

Jason