From: | "Fred Cunningham" <cunningham_fred(at)hotmail(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Error saving image to PostgresSQL 8.x database |
Date: | 2005-06-29 23:13:09 |
Message-ID: | BAY103-DAV12330001F48228CBE892E597E00@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Vsevolod (Simon) Ilyushchenko | 2005-06-30 00:25:55 | Foreign key pg_dump issue and serial column type |
Previous Message | Tom Lane | 2005-06-29 22:55:59 | Re: ENUM like data type |