http://qs321.pair.com?node_id=522093

ketema has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks, I have written a script that I intended to use to escape binary data that I plan to insert into a bytea field into a postgresql database. the specs for the escaping are here: http://www.postgresql.org/docs/8.1/static/datatype-binary.html see table 8-7 my script works, but apparently not correctly because the DB won't take the escaped data. I am returned: invalid input syntax for type bytea I would like some feed back on what I might be missing.
#!/usr/bin/perl open(BINFILE, "your favorite binary file here"); binmode(BINFILE); $escaped = ""; while(read(BINFILE, $char, 1)){ $char = escape(ord($char)); $escaped .= $char } print $escaped, "\n"; sub escape { $ordinal = shift(); %escapeList = (0 => "\\\\000", 1 => "\\\\001", 2 => "\\\\002", 3 => "\\\\003", 4 => "\\\\004", 5 => "\\\\005", 6 => "\\\\006", 7 => "\\\\007", 8 => "\\\\008", 9 => "\\\\009", 10 => "\\\\010", 11 => "\\\\011", 12 => "\\\\012", 13 => "\\\\013", 14 => "\\\\014", 15 => "\\\\015", 16 => "\\\\016", 17 => "\\\\017", 18 => "\\\\018", 19 => "\\\\019", 20 => "\\\\020", 21 => "\\\\021", 22 => "\\\\022", 23 => "\\\\023", 24 => "\\\\024", 25 => "\\\\025", 26 => "\\\\026", 27 => "\\\\027", 28 => "\\\\028", 29 => "\\\\029", 30 => "\\\\030", 31 => "\\\\031", 32 => "\\\\032", 39 => "\\\\047", 92 => "\\\\134", ); if(defined($escapeList{$ordinal})){ return $escapeList{$ordinal}; } else{ return chr($ordinal); } }
Thanks. p.s. i used this ascii reference as a guide: http://www.lookuptables.com/
FIXED: Never mind I am stupid. I was reading the wrong column for the conversion. Of course it is rejected because the codes are in HEX, NOT OCT. Sorry for wasting the post space.
Update: Now that I just verified that it works fine with the correct escape codes, would any one mind suggesting a cleaner, more elegant method than what I have?
Thanks, again.

Replies are listed 'Best First'.
Re: postgresql bytea escaping
by ioannis (Abbot) on Jan 10, 2006 at 07:35 UTC
    Sure it is difficult inserting binary data, but you can avoid it! Use the lo* family of commands that will manage (upload/download, read/write/seek, etc.,) binary files to postgresql; alternatively and preferably, use the equivalent \lo-import, \lo_export, and \lo_list commands from psql.

    These tools is more than enough for the job. If you want more tools to manage binary data, use the lo.sql and lo_drop.sql from the contrib section.

    Furthermore, you can always avoid dealing with binary data if you encode them to ascii (say using base64) and don't mind a little extra storage space.

    Oh, and you might also choose to avoid both methods (both the binary or ascii data) by using links or references to files on disk instead of storing the data inside the database.

    Now you better alternatives.

      Don't the lo family functions create OID in your pg database, which are references to the file system? I chose bytea, because I know the binary data will typically be less the 10 mb, and I didn't want to have the extra mangement headache of keeping the external file system synced. If I am wrong in understanding the way the lo functions work, please correct me.
        The created OID is a reference to pg_largeobject.loid, the table where the binary data are stored. The data are already copied into the database.
Re: postgresql bytea escaping
by tantarbobus (Hermit) on Jan 10, 2006 at 16:13 UTC

    How about just using the C API (via DBD::Pg) provided by pg? So:

     $quoted_data = $dbh->quote($data, PG_BYTEA)
      That is a good idea. My original plan was to make this simple function an internal pl/perlu function so i could just code Insert statements like Insert into table (byteacol) values (encode_bytea('/path/to/bin/file')::bytea); But since it is a perlu function anyway I will test using the DBD Module in the function instead.