[Haskell-cafe] HDBC, postgresql, bytestrings and embedded NULLs

[Michael Snoyman]

On Fri, Jan 7, 2011 at 11:44 AM, Iustin Pop <iustin at google.com> wrote:
> Hi all,
>
> It seems that (at least) the postgresql bindings do not allow pure
> binary data.
>
> I have a simple table:
>
>  debug=# create table test (name bytea);
>
> byteas seems to be the backing type on the DB side for bytestrings.
>
> and then I run this:
>
>  import Database.HDBC.PostgreSQL
>  import Database.HDBC
>  import Data.ByteString
>
>  main = do
>    db <- connectPostgreSQL "dbname=debug"
>    stmt <- prepare db "INSERT INTO test (name) VALUES($1)"
>    execute stmt [toSql $ pack [0]]
>    execute stmt [toSql $ pack [65, 0, 66]]
>    commit db
>
>
> What happens is that the inserted string is cut-off at the first NULL
> value: the first row is empty, and the second row contains just "A".
>
> http://www.postgresql.org/docs/8.4/static/datatype-binary.html says:
>
> “When entering bytea values, octets of certain values must be escaped
> (but all octet values can be escaped) when used as part of a string
> literal in an SQL statement. In general, to escape an octet, convert it
> into its three-digit octal value and precede it by two backslashes”, and
> continues to list that NULL should be quoted as E'\\000'. However, I
> find no such quoting in the HDCB.Postgresql sources.
>
> Anyone else stumbled on this?
>
> thanks,
> iustin
>
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe at haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>

Yes, I had a bug reported in persistent-postgresql that I traced back
to this bug. I reported the bug, but never heard a response. Frankly,
if I had time, I would write a low-level PostgreSQL binding so I could
skip HDBC entirely.

Michael