X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1012C52A06 for ; Wed, 29 Jun 2005 20:14:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96516-03 for ; Wed, 29 Jun 2005 23:14:01 +0000 (GMT) Received: from hotmail.com (bay103-dav12.bay103.hotmail.com [65.54.174.84]) by svr1.postgresql.org (Postfix) with ESMTP id BA26A52A34 for ; Wed, 29 Jun 2005 20:13:58 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 29 Jun 2005 16:14:01 -0700 Message-ID: Received: from 65.54.174.204 by BAY103-DAV12.phx.gbl with DAV; Wed, 29 Jun 2005 23:14:01 +0000 X-Originating-IP: [65.54.174.204] X-Originating-Email: [cunningham_fred@hotmail.com] X-Sender: cunningham_fred@hotmail.com From: "Fred Cunningham" To: Subject: Error saving image to PostgresSQL 8.x database Date: Wed, 29 Jun 2005 18:13:09 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01EA_01C57CD6.34404540" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-OriginalArrivalTime: 29 Jun 2005 23:14:01.0771 (UTC) FILETIME=[3C7527B0:01C57D00] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.896 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_MUA_OUTLOOK, HTML_60_70, HTML_MESSAGE, MSGID_FROM_MTA_HEADER X-Spam-Level: **** X-Archive-Number: 200506/363 X-Sequence-Number: 21964 This is a multi-part message in MIME format. ------=_NextPart_000_01EA_01C57CD6.34404540 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In PostgreSQL 7.1 Documentation Chapter 8. JDBC Interface the example = below is used to store a image file into the database.=20 I am using J2SE 5.02 and Pervasive Postgres 8 Release 2 with = postgresql-8.0-310.jdbc3 driver. Example 8-2. Using the JDBC Large Object Interface For example, suppose you have a table containing the file name of an = image and you have a large object containing that image:=20 CREATE TABLE images (imgname text, imgoid oid);To insert an image, you = would use:=20 File file =3D new File("myimage.gif"); FileInputStream fis =3D new FileInputStream(file); PreparedStatement ps =3D conn.prepareStatement("INSERT INTO images = VALUES (?, ?)"); (1) ps.setString(1, file.getName()); ps.setBinaryStream(2, fis, file.length()); ps.executeUpdate(); ps.close(); fis.close();I have used this example code to create my own method to = save an image file. The code is listed below:** * This method saves = the current record if it has been modified. * * @return = Integer. */ private void saveImageRec() { java.sql.Date = sqlDate; java.sql.Time sqlTime; = java.sql.Timestamp sqlTimestamp; java.util.Date now; = long nowLong; int = returnVal; PreparedStatement ps =3D null; = File file =3D null; FileInputStream = fis =3D null; int vehinfoid =3D 0; = //Get the cuurent date, time and timestamp = now =3D new java.util.Date(); nowLong =3D now.getTime(); = sqlDate =3D new java.sql.Date(nowLong); sqlTime =3D new = java.sql.Time(nowLong); sqlTimestamp =3D new = java.sql.Timestamp(nowLong); try { = file =3D new File(photoFilePath); = fis =3D new FileInputStream(file); } catch = (NullPointerException e) { = System.out.println(e.getMessage()); } catch = (FileNotFoundException e) { = System.out.println(e.getMessage()); } try { = pgconn.connection.setAutoCommit(false); vehinfoid = =3D (Integer)vehicleinfoTable.getValue(0, "vehinfoid"); = if (newrec) { = ps =3D pgconn.connection.prepareStatement("INSERT INTO = tbl_images VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); = ps.setInt(1, vehinfoid); //vehinfoid ps.setString(2, = file.getName()); //Image file name = ps.setBinaryStream(3, fis, (int) file.length()); //image file = ps.setDate(4, sqlDate); //changeddate ps.setTime(5, = sqlTime); //changedtime ps.setString(6, "Fred = Cunningham"); //changedby ps.setTimestamp(7, = sqlTimestamp); //creationdatetime ps.setString(8, "Fred = Cunningham"); //createdby } else { ps =3D = pgconn.connection.prepareStatement("UPDATE tbl_images SET imagename = =3D ?, imageoid =3D ?, changeddate =3D ?, changedtime =3D ?, changedby = =3D ? WHERE vehinfoid =3D ?"); = ps.setString(1, file.getName()); //Image file name = ps.setBinaryStream(2, fis, (int) file.length()); //image file = ps.setDate(3, sqlDate); //changeddate ps.setTime(4, = sqlTime); //changedtime ps.setString(5, "Fred = Cunningham"); //changedby ps.setInt(6, vehinfoid); = //dispatchid } returnVal =3D = ps.executeUpdate(); ps.close(); fis.close(); = } catch (SQLException e) { = System.out.println(e.getMessage()); } catch = (IOException e) { System.out.println(e.getMessage()); = } }------------------------------------- = table -----------------------------------------------------------CREATE = TABLE tbl_images( imageid int4 NOT NULL DEFAULT = nextval('public."tbl_images_imagid_seq"'::text), vehinfoid int4 NOT = NULL, imagename text, imageoid oid, changeddate date NOT NULL, = changedtime time NOT NULL, changedby varchar(25) NOT NULL, = creationdatetime timestamp NOT NULL, createdby varchar(25) NOT NULL, = CONSTRAINT tbl_images_pk PRIMARY KEY (imageid)) WITHOUT OIDS;ALTER TABLE = tbl_images OWNER TO postgres;GRANT ALL ON TABLE tbl_images TO = postgres;GRANT ALL ON TABLE tbl_images TO GROUP = tsam;--------------------------------------------------------------------= ------------------------------------when I execute the code I get the = error listed below: ERROR: column "imageoid" is of type oid but = expression is of type byteaCan anyone tell what I doing wrong.Thanks = Fred Cunningham ------=_NextPart_000_01EA_01C57CD6.34404540 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
In PostgreSQL 7.1=20 Documentation Chapter 8.=20 JDBC Interface the example = below is=20 used to store a  image file into the database.
I am using J2SE=20 5.02 and Pervasive Postgres  8 Release 2  = with=20 postgresql-8.0-310.jdbc3 driver.

Example 8-2. Using the JDBC Large = Object=20 Interface

For example, suppose you have a table containing the file name of an = image=20 and you have a large object containing that image: 

CREATE TABLE images (imgname text, imgoid =
oid);

To insert an image, you would use: 

File file =3D new File("myimage.gif");
 FileInputStream fis =3D new FileInputStream(file);
 PreparedStatement ps =3D conn.prepareStatement("INSERT INTO images =
VALUES (?, ?)"); (1)
 ps.setString(1, file.getName());
 ps.setBinaryStream(2, fis, file.length());
 ps.executeUpdate();
 ps.close();
 fis.close();
 
I have =
used this example code to create my own method to save an image file. =
The code is listed below:
**
     * This =
method saves the current record if it has been =
modified.     
     =
*
     * @return =
Integer.
     */
    private =
void saveImageRec() {
        =
java.sql.Date          =
sqlDate;
        =
java.sql.Time          =
sqlTime;
        =
java.sql.Timestamp     =
sqlTimestamp;
        =
java.util.Date         =
now;
        =
long           &nb=
sp;       =
nowLong;
        =
int           &nbs=
p;        =
returnVal;
        =
        =

        =
PreparedStatement      ps =3D =
null;
        =
File           &nb=
sp;       file =3D =
null;        =

        =
FileInputStream        fis =3D =
null;
        =
int           &nbs=
p;       vehinfoid =3D =
0;
           &=
nbsp;        =

        //Get the cuurent date, =
time and timestamp
        now =3D =
new java.util.Date();
        =
nowLong =3D now.getTime();
        =
sqlDate =3D new =
java.sql.Date(nowLong);
        =
sqlTime =3D new =
java.sql.Time(nowLong);
        =
sqlTimestamp =3D new =
java.sql.Timestamp(nowLong);
       =
;         =

        try =
{            =
            &=
nbsp;           &n=
bsp;  =

            =
file =3D new =
File(photoFilePath);        =

            =
fis =3D new FileInputStream(file);     =

        } catch =
(NullPointerException  e) =
{
            =
System.out.println(e.getMessage());
     &nbs=
p;  } catch (FileNotFoundException e) =
{
            =
System.out.println(e.getMessage());
     &nbs=
p;  }    =

        =

        try =
{
           &n=
bsp; =
pgconn.connection.setAutoCommit(false);
     =
        vehinfoid =3D  =
(Integer)vehicleinfoTable.getValue(0,  =
"vehinfoid");
         &n=
bsp;   =

           &nb=
sp; if (newrec) =
{            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;   =

           &nb=
sp;   ps =3D  pgconn.connection.prepareStatement("INSERT =
INTO tbl_images VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); =

        =

           &nb=
sp;   ps.setInt(1, vehinfoid); //vehinfoid =

           &nb=
sp;   ps.setString(2, file.getName()); //Image file =
name        =

           &nb=
sp;   ps.setBinaryStream(3, fis, (int) file.length()); //image =
file
           =
;    ps.setDate(4, sqlDate); //changeddate =

           &nb=
sp;   ps.setTime(5, sqlTime); //changedtime =

           &nb=
sp;   ps.setString(6, "Fred Cunningham"); //changedby =

           &nb=
sp;   ps.setTimestamp(7, sqlTimestamp); //creationdatetime =

           &nb=
sp;   ps.setString(8, "Fred Cunningham"); //createdby =

           &nb=
sp; } else =
{
           &n=
bsp;   ps =3D  pgconn.connection.prepareStatement("UPDATE =
tbl_images SET   imagename =3D ?, imageoid =3D ?, changeddate =
=3D ?, changedtime =3D ?, changedby =3D ? WHERE vehinfoid =3D ?"); =

           &nb=
sp;   =

           &nb=
sp;   ps.setString(1, file.getName()); //Image file =
name        =

           &nb=
sp;   ps.setBinaryStream(2, fis, (int) file.length()); //image =
file
           =
;    ps.setDate(3, sqlDate); //changeddate =

           &nb=
sp;   ps.setTime(4, sqlTime); //changedtime =

           &nb=
sp;   ps.setString(5, "Fred Cunningham"); //changedby =

           &nb=
sp;   ps.setInt(6, vehinfoid); //dispatchid =

           &nb=
sp; }   =

           &nb=
sp; =

            =
returnVal =3D =
ps.executeUpdate();
        &n=
bsp;   =
ps.close();
         &nbs=
p;  fis.close(); 
        } =
catch (SQLException e) =
{            =
            &=
nbsp;           &n=
bsp;          =

           =
System.out.println(e.getMessage()); =

        } catch (IOException e) =
{    =

           =
System.out.println(e.getMessage());     =

        =
}            =
       
    =
}
 
-------------------------------------  =
table =
-----------------------------------------------------------
<= PRE class=3DPROGRAMLISTING>CREATE TABLE = tbl_images
(
  imageid int4 NOT NULL DEFAULT = nextval('public."tbl_images_imagid_seq"'::text),
  vehinfoid = int4 NOT NULL,
  imagename text,
  imageoid = oid,
  changeddate date NOT NULL,
  changedtime time NOT = NULL,
  changedby varchar(25) NOT NULL,
  = creationdatetime timestamp NOT NULL,
  createdby varchar(25) NOT = NULL,
  CONSTRAINT tbl_images_pk PRIMARY KEY (imageid)
) =
WITHOUT OIDS;
ALTER TABLE tbl_images OWNER TO postgres;
GRANT = ALL ON TABLE tbl_images TO postgres;
GRANT ALL ON TABLE tbl_images TO = GROUP tsam;
----------------------------------------------------------------=
----------------------------------------
when I =
execute the code I get the error listed below:
   ERROR: =
column "imageoid" is of type oid but expression is of type =
bytea
 
Can anyone tell what I doing =
wrong.
 
Thanks 
 
Fred Cunningham
 
 
 ------=_NextPart_000_01EA_01C57CD6.34404540--