Skip site navigation (1) Skip section navigation (2)

Re: Inserting a image into a database

From: richard terry <rterry(at)gnumed(dot)net>
To: sydpug(at)postgresql(dot)org
Cc: Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz>
Subject: Re: Inserting a image into a database
Date: 2008-08-31 01:08:12
Message-ID: 200808311108.12405.rterry@gnumed.net (view raw or flat)
Thread:
Lists: sydpug
On Fri, 29 Aug 2008 08:49:50 am Andrew Boag wrote:

Thanks andrew, that's helped but I've got some sort of encoding problem, and 
as I'm using gambas basic I didn't know how to interpret these lines:

> use DBI ;
> use DBD::Pg qw(:pg_types); #Required for us to use the *bytea* column


My gambas code goes something like this and I've selected the png file to load 
which has been passed to this routine as ImagePath.

Public sub Save_Image(ImagePath as string)

 Dim img As Image
 Dim pictureData As String
 Dim sql As String
 
  img = Image.Load(ImagePath)  ' Save temp image as png file
  tempFile = Temp() & ".png"
  img.Save(tempFile)


  pictureData = File.Load(tempFile) 'reload as a string


At this point the pictureData string seems  to be ok.
I Then tried writing to the database:

  sql = "insert into temp_image(piccie)"
          "values($$"
    sql = sql & pictureData & "$$)"

and got back this message:

Query failed:ERROR: invalid byte sequence for encoding "UTF8":0x89 HINT: This 
error can also happen if the byte sequence does not match the encoding 
expected by the server, which is controlled  by "client_encoding"

Any idea's?

thanks

Richard



> reading in file from system (we need binmode here)
>
> #Now we'll read in the contents of the raw file
> my $contents = "";
>
> open ( F,"< $passed_file");
> binmode (F); #Our friend binmode
>
> while ( read F, my $buf, 16384) {$contents .= $buf ;}
> close ( F );
>
>
> sanity check writing file back to system (to make sure you get the same
> one)
>
> my $new_file = $passed_file.".FILECOPY";
> open ( F,"> $new_file");
> binmode(F);
> print F $contents ;
> close (F);
>
>
>
>
> now the insert (you'll have to update the $id value as this had meaning)
>
> #INSERT THE Binary DATA into the table.
> $sth = $dbh->prepare( " INSERT INTO binary_image ( image_id , contents )
> VALUES ( ? , ? ) " ) or die "PREPARE FAILED";
>
> #bind_param the index starts from 1
> $sth->bind_param(2, undef, { pg_type => DBD::Pg::PG_*BYTEA* });
> $sth->execute($id , $contents) or die "EXECUTE FAILED";
>
>
>
> now the select
>
> #Now let's get the blob and write it to a file ...
> $sth = $dbh->prepare( " SELECT contents from binary_image WHERE image_id =
> ? " );
>
> my $content = $sth->fetchrow_hashref->{'contents'} ;
>
> #Now lets write the
> $new_file = $passed_file.".DBCOPY";
> open ( F,"> $new_file");
> binmode(F);
> print F $content ;
> close (F);
>
> richard terry wrote:
> > Hi
> >
> > I wondered if anyone could give me a few lines of sample code on basic,
> > showing how to insert a file you have on your hard drive, into  a data
> > field in postgres. I'm totally stumped (seems that using the bytea field
> > is the way to go from reading the doc's.
> >
> > Thanks in advance.
> >
> > Richard



In response to

Responses

sydpug by date

Next:From: Andrew BoagDate: 2008-08-31 23:21:59
Subject: Re: Inserting a image into a database
Previous:From: Jacinta RichardsonDate: 2008-08-29 11:07:33
Subject: Re: Inserting a image into a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group