Обсуждение: 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
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
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
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 <lawgon@thenilgiris.com> 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
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
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 <lawgon@thenilgiris.com> 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
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?howto-vblo kg
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 <lawgon@thenilgiris.com> 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?howto-vblo kg