#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    23
    Rep Power
    0

    Update bytea column in existing row


    I have a table with a bytea column where I want to store an Encapsulated PostScript file so I don't have to place it on every machine that accesses the database. I have not found a useful method for for loading the EPS file into the column. I though it would be as easy as:

    UPDATE table
    SET epsfile = 'filename.eps'
    WHERE tablekey = 1;

    My temporary solution is a column with the file name and the actual file in the directory where the application (a Perl script) lives.

    I understand that there will be a performance hit from copying the file (1 Mb in size) over the network every time the application needs the file but it's easier to do that then make sure the current version of the file is installed on every machine that needs it.

    I looked at using a Perl script to load it but the PostScript::Simple::EPS module doesn't include an option for putting the contents of the file in a scalar.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    23
    Rep Power
    0
    It looks like I can answer my own question. For those who are interested, it's looks like this (in Perl):

    Code:
    use DBI;
    $dbh = DBI->connect("DBI:Pg:dbname=postgres;host=localhost;port=5432", "postgres", "postgres");
    $sth = dbh->prepare("UPDATE table SET column = (?::bytea) WHERE key = ?");
    
    $sth->bind_param(1, $bindata, { pg_type => DBD::Pg::PG_BYTEA});
    $sth->bind_param(2, $key);
    $sth->execute;
    This is basic but it gets the idea across. Remember to check for errors!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    23
    Rep Power
    0
    I had to edit my response because I got the method wrong for binding the parameters. I had "bind_column" but meant "bind_param." I'm sorry if this confused anyone.

IMN logo majestic logo threadwatch logo seochat tools logo