Postgres JPG Store Problem

[gwinn7]

After several weeks, I am not back on this project. The problem I am experiencing is when I try to run the script through my browser I get the error message...

"Warning: PostgreSQL query failed: ERROR: Unterminated quoted string in /home/httpd/htdocs/loadandstoreindb.php on line 28."

The script that process this is as follows...

<?
$filename = &quot;Sample.jpg&quot;;
$mysize = filesize($fielname);
$fp = fopen($filename, &quot;r&quot;

while(!feof($fp))
{
$data = $data . fread($fp,1);
}

fclose($fp);

$database = pg_connect(myconnectstring);
$MyInsert = &quot;INSERT INTO Test2 VALUES (4545, 2, '&quot; . $data . &quot;')&quot;;
pg_exec($database, $MyInsert); //ERROR HERE!
?>

I tried appending the string with a &quot;/n&quot;, but that didn't help. Could someone clue me in here? Perhaps, since this is a string containing a JPG image, we could convert this to binary somehow? If so, which function does this?

BTW, the field that I am trying to write the image to is of type &quot;bytea&quot;.

Any ideas?

Thanks,
Gary
gwinn7

 

[gwinn7]

Oops! First sentence typing error!

I am NOW back on this project! lol.

Gary
gwinn7

 

[rycamor]

Did you append &quot;/n&quot;, or &quot;\n&quot;? (hint: \n is a true line terminator ;-) -- hopefully just another typing error) -------------------------------------------

&quot;Now, this might cause some discomfort...&quot;
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[rycamor]

Oh, and by the way, it is more common to store images in PostgreSQL using large objects, instead of bytea. The keyword for creating a large object column in a table is 'oid'. This can then be used with the pg_lo_import() function in PHP, which provides a nice foolproof way of escaping binary data for insertion. See

http://php.net/pg_lo_import

(if you haven't already).

HTH -------------------------------------------

&quot;Now, this might cause some discomfort...&quot;
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[gwinn7]

Hi Rycamor,

RE: Appending &quot;/n&quot;

Yes, I tried this before your message and it didn't help.

RE: pg_lo_import

Yes, I have experimented with this somewhat, but no real success with it. The documentation says to use the system table &quot;pg_largeobject&quot; for storing large objects and reference them with the oid. However, I had trouble with using this too.

One promising thing I found was the &quot;addslashes&quot; function. This seemed to help me store the picture, but I am now having trouble pulling the picture for display. I suspect that the function removed everything but the JPG header, but I am not quite sure until I play with it a bit more.

More posts to follow as I make progress.

Gary
gwinn7

 

[rycamor]

>RE: Appending &quot;/n&quot;

>Yes, I tried this before your message and it didn't help.

No, I didn't mean &quot;did you try it?&quot;. I meant: did you do it correctly, using a &quot;\n&quot; (correct), or did you mistakenly put a &quot;/n&quot; (wrong), as your previous post suggests?

And remember, the line terminator has to be inside double quotes to be treated as a line terminator (not single quotes). -------------------------------------------

&quot;Now, this might cause some discomfort...&quot;
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[gwinn7]

Ok, just confirmed that &quot;\n&quot; does not seem to work for me. Here is the updated sample code:


$newstring = $data.&quot;\n&quot;;

MyInsert = &quot;INSERT INTO Test2 VALUES (4545, 2, '&quot;.$newstring.&quot;')&quot;;

The rest of the code remains the same. When i used the &quot;addslashes&quot; function, the INSERT statement would only store the first few bytes of the JPG header.

Gary
gwinn7

 

[gwinn7]

Progress...

I went back to attempts at storing and retrieving the image from the pg_largeobject table. I seemed to be successful in storing an image, but not retrieving it. Here is the script I am using to retrieve it...

<?
$Connection = pg_connect(My connection string);
$File = pg_loopen($Connection, 24898, &quot;r&quot; //ERROR HERE!!!
$data = pg_loread($File, 10000);

$image = imagecreatefromstring($data);
imagejpeg($image);

?>

The error returned is:

&quot;Unable to open PostgreSQL large object in...&quot;

The &quot;24898&quot; is a specific loid value in the pg_largeobject table. The function should see it and retrieve the picture. When I perform a query on the table, I can see the loid just fine. So I know its there.

I will continue to troubleshoot this problem. Any ideas?

Thanks,
Gary
gwinn7

 

[gwinn7]

Ok, I am feeling a little stupid right now! OK, I figured it out.

I forgot that, even to display pictures, I needed to enclose the statements in a transaction. Once I added the transaction code, it worked.

On to the next thing!

Gary
gwinn7