Inserting File into Postgres database

Question

I am performing Data migration from oracle database to postgres 9.0 database.All fields are successfully transferred except when i am trying to move a column of file(Blob object in Oracle) into the postgres(bytea in postgres).This is the exception what i am getting

org.postgresql.util.PSQLException: ERROR: syntax error at or near "",#\\034\\034(7),01444\\037\'9=82<.342\\377\\333\\000C\\001\\011\\011\\011\\014\\013\\014\\030\\015\\015\\0302!\\034!22222222222222222222222222222222222222222222222222\\377\\300\\000\\021\\010\\000D\\0004\\003\\001""

Below is the piece of Code i am using for storing the file in the database :

Class.forName("org.postgresql.Driver");
        destDatabaseconnection = DriverManager.getConnection(
                rb.getString("DESTINATION_DATABASE_CONNECTION_URL"),
                rb.getString("DESTINATION_DATABASE_CONNECTION_USERNAME"),
                rb.getString("DESTINATION_DATABASE_CONNECTION_PASSWORD"));

        File file = new File("d://img//10090.gif");
        System.out.println(file.isFile());
        FileInputStream fis = new FileInputStream(file);
        prepstmt = destDatabaseconnection
                .prepareStatement("insert into entps.emp_photos(emp_number,emp_photo) values (?,?)");
        prepstmt.setInt(1, 1);

        prepstmt.setBinaryStream(2, fis, (int) file.length());
        int check = prepstmt.executeUpdate();
        System.out.println(check);

Please let me know if you have ever stored a file in the postgres 9.0

+1 for migration from Oracle to PostgreSQL

Oleg Mikheev
– Oleg Mikheev

2013-12-24 08:50:13 +00:00
Commented Dec 24, 2013 at 8:50 — Oleg Mikheev
– Oleg Mikheev, Commented Dec 24, 2013 at 8:50

Oleg Mikheev · Accepted Answer · 2013-12-24 18:34:13Z

2

Just for the fun of it I've just created a table (in Postgres 9.2)

CREATE TABLE test
(
  id integer NOT NULL,
  file bytea,
  CONSTRAINT id PRIMARY KEY (id)
)

and have successfully uploaded a file to it (using driver 8.3 and 9.3):

public static void main(String[] args) throws Exception {
    Class.forName("org.postgresql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:postgresql:test", "postgres", "");
    File file = new File("/tmp/q");
    FileInputStream fis = new FileInputStream(file);
    PreparedStatement pstmt = conn
            .prepareStatement("insert into test(id,file) values (?,?)");
    pstmt.setInt(1, 1);
    pstmt.setBinaryStream(2, fis, (int) file.length());
    int check = pstmt.executeUpdate();
    System.out.println(check);
}

Please check you driver.

edited Dec 24, 2013 at 18:34

answered Dec 24, 2013 at 9:14

Oleg Mikheev

17.5k16 gold badges77 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tusharagrawa Over a year ago

I have tried this in Postgres 8.3 where it is working...Can you please Check the Postgres Version You Are using.!!

Oleg Mikheev Over a year ago

I used 9.2, with driver versions 8.3 and 9.3. Please specify your versions in the question

tusharagrawa Over a year ago

Yeah It Worked..Actually The Postgres driver i was using dosen't specified it's version so i thought it's of only available driver but on searching i found out there are many updated drivers availabe so i used the postgresql-9.3-1100.jdbc3 driver and it worked.Thanks Oleg Mikhev

Collectives™ on Stack Overflow

Inserting File into Postgres database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related