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

[Iustin Pop]

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