From: Fred Cunningham <cunningham_fred@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Error saving image to PostgresSQL 8.x database
Date: Wed, 29 Jun 2005 18:13:09 -0500
Message-ID: <BAY103-DAV12330001F48228CBE892E597E00@phx.gbl> (raw)
In PostgreSQL 7.1 Documentation Chapter 8. JDBC Interface the example below is used to store a image file into the database.
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:
CREATE TABLE images (imgname text, imgoid oid);To insert an image, you would use:
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = 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 = null; File file = null; FileInputStream fis = null; int vehinfoid = 0; //Get the cuurent date, time and timestamp now = new java.util.Date(); nowLong = now.getTime(); sqlDate = new java.sql.Date(nowLong); sqlTime = new java.sql.Time(nowLong); sqlTimestamp = new java.sql.Timestamp(nowLong); try { file = new File(photoFilePath); fis = 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 = (Integer)vehicleinfoTable.getValue(0, "vehinfoid"); if (newrec) { ps = 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 = pgconn.connection.prepareStatement("UPDATE tbl_images SET imagename = ?, imageoid = ?, changeddate = ?, changedtime = ?, changedby = ? WHERE vehinfoid = ?"); 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 = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cunningham_fred@hotmail.com
Subject: Re: Error saving image to PostgresSQL 8.x database
In-Reply-To: <BAY103-DAV12330001F48228CBE892E597E00@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox