Lists: | Postg토토 핫SQL : Postg토토 핫SQL 메일 링리스트 : 2008-08-31 이후 SYDPUG 23:21 |
---|
From: | richard terry <rterry(at)gnumed(dot)net> |
---|---|
To: | sydpug(at)postgresql(dot)org |
Subject: | Inserting a image into a database |
Date: | 2008-08-28 21:45:00 |
Message-ID: | 200808290745.00414.rterry@gnumed.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | sydpug |
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
From: | Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz> |
---|---|
To: | rterry(at)internode(dot)on(dot)net |
Cc: | sydpug(at)postgresql(dot)org |
Subject: | Re: Inserting a image into a database |
Date: | 2008-08-28 22:49:50 |
Message-ID: | 48B72B8E.70204@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | sydpug |
Hear your pain, this was all done on debian linux. binmode was a gotcha
I've done it with a bytea column like this
use DBI ;
use DBD::Pg qw(:pg_types); #Required for us to use the *bytea* column
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
>
>
From: | Jacinta Richardson <jarich(at)perltraining(dot)com(dot)au> |
---|---|
To: | Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz> |
Cc: | rterry(at)internode(dot)on(dot)net, sydpug(at)postgresql(dot)org |
Subject: | Re: Inserting a image into a database |
Date: | 2008-08-29 11:07:33 |
Message-ID: | 48B7D875.90601@perltraining.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | sydpug |
G'day folk,
Andrew's given you some great starting points, but I'd like to just make a few
stylistic corrections to the Perl code. So building on what Andrew wrote:
#!/usr/bin/perl -w
use strict;
use DBI ;
use DBD::Pg qw(:pg_types); # Required for us to use the *bytea* column
use Fatal qw(open close); # Throw exception if we fail to open or close
# You'll need to change these
my $dbname = "name of your database";
my $username = "your database username, or empty";
my $password = "your database password, or empty";
my $id = "id of image for insert";
my $passed_file = $ARGV[0]; # Assuming it was passed in on the command line
# Read in the contents of the raw file
my $contents = "";
open (my $in_fh,"<",$passed_file); # 3 arg open and scalar filehandles
# Tell Perl not to translate newline characters
binmode($in_fh);
while ( read $in_fh, my $buf, 16384) {$contents .= $buf ;}
close $in_fh ;
# sanity check writing file back to system (to make sure you get the same
# one) Not essential, but a good test.
my $file_copy = $passed_file.".FILECOPY";
open (my $out_fh,">", $file_copy);
binmode($out_fh);
print {$out_fh} $contents; # curlies not needed, but visually helpful
close $out_fh;
# Connect to the database
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, $password,
{
AutoCommit => 0,
RaiseError => 1
}
);
# now the insert (you'll have to update the $id value above)
# INSERT THE Binary DATA into the table.
my $insert = $dbh->prepare("
INSERT INTO binary_image ( image_id , contents )
VALUES ( ? , ? )
"); # Raise Error will check for failures
# bind_param the index starts from 1 (PG_BYTEA as per the documentation)
$insert->bind_param(2, undef, { pg_type => PG_BYTEA });
$insert->execute($id , $contents);
# And it's inserted!
# now the select
# Now let's get the blob and write it to a file ...
my $select = $dbh->prepare("
SELECT contents from binary_image WHERE image_id > = ?
");
my $content = $select->fetchrow_hashref->{'contents'} ;
# Now lets write it
my $new_file = $passed_file.".DBCOPY";
open (my $new_fh,">", $new_file);
binmode($new_fh);
print {$new_fh} $content; # curlies not needed, but visually helpful
close $new_fh;
--
("`-''-/").___..--''"`-._ | Jacinta Richardson |
`6_ 6 ) `-. ( ).`-.__.`) | Perl Training Australia |
(_Y_.)' ._ ) `._ `. ``-..-' | +61 3 9354 6001 |
_..`--'_..-_/ /--'_.' ,' | contact(at)perltraining(dot)com(dot)au |
(il),-'' (li),' ((!.-' | www.perltraining.com.au |
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From: | Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz> |
---|---|
To: | rterry(at)internode(dot)on(dot)net |
Cc: | sydpug(at)postgresql(dot)org |
Subject: | Re: Inserting a image into a database |
Date: | 2008-08-31 23:21:59 |
Message-ID: | 48BB2797.8080205@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg토토 핫SQL : Postg토토 핫SQL 메일 링리스트 : 2008-08-31 이후 SYDPUG 23:21 |
sorry mate, try using perl!!!
richard terry wrote:
> 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
>>>
>
>
>
>