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

Re: Inserting a image into a database

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 (view raw or flat)
Thread:
Lists: sydpug
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
>>>       
>
>
>
>   


In response to

sydpug by date

Next:From: Charles DuffyDate: 2008-09-03 01:43:52
Subject: Reminder: SydPUG on tonight
Previous:From: richard terryDate: 2008-08-31 01:08:12
Subject: Re: Inserting a image into a database

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