Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
I am trying to insert a large object in a PostgreSQL database, but am having difficulties finding a graceful way to do this. Note that the included code has been simplified to avoid distractions--I am well aware that I should be addressing possible errors.

System information:

  • perl, version 5.005_03
  • Linux 2.2.14 i686
  • Postgres: 6.5.3
  • DBI: 1.1.3
  • DBD Pg: 0.93

I have created a table with one field, data, which is of type OID. Next I attempt to insert by more or less copying the code on page 153 of 'Programming the Perl DBI':

my $sth = $dbh->prepare("insert into foo(data) values (?);"); $sth->bind_param(1, $value, SQL_LONGVARBINARY); $sth->execute();
I receive a warning that binding the value isn't permitted:
SQL type 1075548612 for 'rc' is not fully supported, bound as VARCHAR +instead at ./1.0 line 122.
Whereupon it fails with a pg_atoi error.

I can insert by writing $value to a file and using the (presumably Postgres-specific) function lo_import:

$dbh->prepare("insert into foo(data) values (lo_import('/tmp/lame'));")
I'm not pleased with this solution because it requires the creation of a temporary file and it seems to undermine the use of DBI to use a database-specific insert.

I imagine some of the problem has to do with the unusual way Postgres deals with large objects. I expected that the Pg DBD would compensate for that, perhaps using lo_import, but accepting the format shown earlier.

The fact that I can't seem to find any documentation mentioning this problem (which I imagine must have occurred before) makes me feel as if I've completely failed to grasp something simple (or that Pg's documentation-dearth is contagious, affecting anything that touches Postgres). Is there a better way to insert a large object in Postgres using DBI? Thanks in advance.

In reply to Difficulty inserting a large object using DBD::Pg by kudra

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, 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, 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.
  • Log In?

    What's my password?
    Create A New User
    [GotToBTru]: you are correct, choroba, but of course there is no point in doing one without the other
    [GotToBTru]: un less you're working on write-only memory
    [1nickt]: my $x = $aohoaoh->[0]->{' foo'}->[0]->{'bar' }; should work
    [thepkd]: not indexing
    [thepkd]: getting data out of the ds
    [choroba]: it depends. Give more details, please
    [thepkd]: i used a series of {}'s but it dont work
    [choroba]: You need square brackets for arrays
    [GotToBTru]: you can certainly construct a single expression to access any part of the data structure, without using temp variables. but you might make it easier on yourself and any other poor soul who has to understand your code if you do
    [thepkd]: to dereference i mean

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (14)
    As of 2016-12-06 13:21 GMT
    Find Nodes?
      Voting Booth?
      On a regular basis, I'm most likely to spy upon:

      Results (104 votes). Check out past polls.