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);   
    }
}

[download]
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.

In reply to postgresql bytea escaping by ketema

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.