Image Insert Postgresql DB

Hello I have a doubt regarding Image insert into Postgres DB.
Yesterday i posted a query regarding this. and this one is in
continuation.
I am working on Project with Linux Server and Windows Client
environment. And v r developing windows based application.
V have to scan images from scanner attched to the client machine and
these images r to be inserted into postgres db. Data type for Image
field in the DB is OID.
copy of Image file actually resides in the client. V have to insert
this image into the DB.

Query to insert
----------------
INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));

here the path specified is the image path at client. But its doesn't
work. Since it is not
possible set the querys path to a remote machine.

How i can insert the image into the DB at linux machine where the
image file resides in the client machine.
pls reply

Sreejith

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Image Insert Postgresql DB

Dear Shreejith ,


> Data type for Image
> field in the DB is OID.
> copy of Image file actually resides in the client. V have to insert
> this image into the DB.


Firtsly crosscheck the data type is OID it must be either bytea or blob.

>
> Query to insert
> ----------------
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));

Out of experience I would suggest you to keep images on the file
system as database with images in it would be bulky and would takes
loads of time to restore and backup.
If these do not bother you continue with it.

Thats Pascal ??

Dont know about pascal but you would have to stream the file to the
server. I doubt that PostgreSQL will fetch from the client.
In PHP we would have Posted the file and exected the insert query


Hope this helps you.


--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Image Insert Postgresql DB

On Tuesday 23 November 2004 09:51 am, sreejith s wrote:

> Query to insert
> ----------------
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));


have you succeeded in inserting an image into the database from a file on the
server using the above query? AFAIK you need a bytea datatype, and also the
image data has to be properly escaped (\\) before pg will accept it. I assume
you are using a scripting language with a dbapi. Your steps would be:

1. read the image file into a variable
2. escape the image data
3. insert this into the database in a bytea field

if you follow this procedure it is irrelevant as to where the source file is
- client or server

regards
kg

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Image Insert Postgresql DB

I face this same problem when I try to insert image by lo_import to
database with my client files. I already change the way to bytea data
type but it's not work. The error return like type mismatch when I try
to insert it from Binary variable.

Why I cannot insert it? If anybody found some solution please reply on
this topic for me too.

Thank You.


>>> Kenneth Gonsalves 23/11/2004 12:18:44 pm
>>>



On Tuesday 23 November 2004 09:51 am, sreejith s wrote:

> Query to insert
> ----------------
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));


have you succeeded in inserting an image into the database from a file
on the
server using the above query? AFAIK you need a bytea datatype, and also
the
image data has to be properly escaped (\\) before pg will accept it. I
assume
you are using a scripting language with a dbapi. Your steps would be:

1. read the image file into a variable
2. escape the image data
3. insert this into the database in a bytea field

if you follow this procedure it is irrelevant as to where the source
file is
- client or server

regards
kg

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Image Insert Postgresql DB

On Tuesday 23 November 2004 12:48 pm, Premsun Choltanwanich wrote:
> I face this same problem when I try to insert image by lo_import to
> database with my client files. I already change the way to bytea data
> type but it's not work. The error return like type mismatch when I try
> to insert it from Binary variable.
>
> Why I cannot insert it? If anybody found some solution please reply on
> this topic for me too.

you must escape the octets with \\ that is two backslashes and not one
backslash.

for example if your binary data is like this:

\x05\x00\x02

you must make it like so:

\\x05\\x00\\x02

please see chapter 8.4 of the postgresql manual.

kg

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Image Insert Postgresql DB

I use VB as develop tool and ADO for connect. My code is show below and
for vPicMember is stand ADOStream in Binary.

rs.Open "SELECT * FROM t_mbrpic WHERE mbrsysid = " & vSysId, conn,
adOpenStatic, adLockOptimistic
If rs.EOF Then rs.AddNew
rs!MbrSysId = vSysId
rs!MbrPic = vPicMember.Read
rs.Update
rs.Close

In this case, Is I must still escape the octets with \\ that is two
backslashes? Could anybody has some suggestion?




>>> Kenneth Gonsalves 23/11/2004 2:40:27 pm
>>>
On Tuesday 23 November 2004 12:48 pm, Premsun Choltanwanich wrote:
> I face this same problem when I try to insert image by lo_import to
> database with my client files. I already change the way to bytea
data
> type but it's not work. The error return like type mismatch when I
try
> to insert it from Binary variable.
>
> Why I cannot insert it? If anybody found some solution please reply
on
> this topic for me too.

you must escape the octets with \\ that is two backslashes and not one

backslash.

for example if your binary data is like this:

\x05\x00\x02

you must make it like so:

\\x05\\x00\\x02

please see chapter 8.4 of the postgresql manual.

kg

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Image Insert Postgresql DB

On Tuesday 23 November 2004 01:49 pm, Premsun Choltanwanich wrote:
> I use VB as develop tool and ADO for connect. My code is show below and
> for vPicMember is stand ADOStream in Binary.
>
> rs.Open "SELECT * FROM t_mbrpic WHERE mbrsysid = " & vSysId, conn,
> adOpenStatic, adLockOptimistic
> If rs.EOF Then rs.AddNew
> rs!MbrSysId = vSysId
> rs!MbrPic = vPicMember.Read
> rs.Update
> rs.Close

i use python, and have no idea about ado, this might help:

http://gborg.postgresql.org/project/psqlodbc/genpage.php?how to-vblo

kg


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Image Insert Postgresql DB

I already try it on last week.
Unfortunately that I cannot create the lo type using the appropriate
functions available in contrib/lo in the Postgresql source tree because
library file cannot be loaded. (I use PostgreSQL 8.0 Beta1 as Database
backend.)
However, it's look like a solution for this problem if I can create
this lo type.

>>> Kenneth Gonsalves 23/11/2004 4:29:29 pm
>>>
On Tuesday 23 November 2004 01:49 pm, Premsun Choltanwanich wrote:
> I use VB as develop tool and ADO for connect. My code is show below
and
> for vPicMember is stand ADOStream in Binary.
>
> rs.Open "SELECT * FROM t_mbrpic WHERE mbrsysid = " & vSysId, conn,
> adOpenStatic, adLockOptimistic
> If rs.EOF Then rs.AddNew
> rs!MbrSysId = vSysId
> rs!MbrPic = vPicMember.Read
> rs.Update
> rs.Close

i use python, and have no idea about ado, this might help:

http://gborg.postgresql.org/project/psqlodbc/genpage.php?how to-vblo

kg


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings