pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError saving image to PostgresSQL 8.x database
2+ messages / 2 participants
[nested] [flat]
* Error saving image to PostgresSQL 8.x database
@ 2005-06-29 23:13 Fred Cunningham <cunningham_fred@hotmail.com>
2005-06-30 03:56 ` Re: Error saving image to PostgresSQL 8.x database Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Fred Cunningham @ 2005-06-29 23:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Error saving image to PostgresSQL 8.x database
2005-06-29 23:13 Error saving image to PostgresSQL 8.x database Fred Cunningham <cunningham_fred@hotmail.com>
@ 2005-06-30 03:56 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2005-06-30 03:56 UTC (permalink / raw)
To: Fred Cunningham <cunningham_fred@hotmail.com>; +Cc: pgsql-sql
Fred,
> 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
Why are you reading the 7.1 docs and using 8.0? How about reading the 8.0
docs?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-30 03:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-29 23:13 Error saving image to PostgresSQL 8.x database Fred Cunningham <cunningham_fred@hotmail.com>
2005-06-30 03:56 ` Josh Berkus <josh@agliodbs.com>
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