Insert different datatypes into postgreSQL in a query error

Question

I want to insert a record into PostgreSQL database. Most of it's fields are String, but there is also java.sql.date, and BIGINT. How can i do that? My code doesn't seem to work.

    public Record(RecordFields data)
    {
        CreateNewRecord(data);
    }

        private void CreateNewRecord(RecordFields recordFields)
        {
            try
            {    

                    String addRecord = "INSERT INTO " + GlobalFields.TABLE + " VALUES (? , ? , ? , ? , ? , ? , ? , ? , ?);";

                    Connection conn = DataBase.Connect();

                    PreparedStatement query = conn.prepareStatement(addRecord);
                    query.setLong(1, recordFieldsID);
                    query.setString(2, recordFields.surname);
                    query.setString(3, recordFields.name);
                    query.setString(4, recordFields.sex);

                    DateTime date = recordFields.bornDate.toDateTime();
                    query.setTimestamp(5, new Timestamp(date.getMillis()));

                    query.setString(6, recordFields.adress);
                    query.setString(7, recordFields.contact);
                    query.setString(8, recordFields.insurance);
                    query.setString(9, recordFields.commentary);

                    query.executeUpdate(addRecord);
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }


    }

table generator:

public static void CreateTable()
    {
        try
        {
            Connection conn = DataBase.Connect();

            try
            {
                Statement stat = conn.createStatement();

                String createTable = "CREATE TABLE " + GlobalFields.TABLE 
                    + "(id BIGINT PRIMARY KEY,"
                    + "surname TEXT,"
                    + "name TEXT,"
                    + "sex CHAR(1),"                 
                    + "bornDate DATE,"
                    + "adress TEXT,"
                    + "contact TEXT,"
                    + "insurance TEXT,"
                    + "commentary TEXT);";

                stat.executeUpdate(createTable);
            }
            finally
            {
                conn.close();
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        } 
    }

public class RecordFields { public RecordFields() { }

public long id;
public String surname;
public String name;
public String sex;
public MutableDateTime bornDate;
public String adress;
public String contact;
public String insurance;
public String commentary;

}

Perhaps you could describe how it doesn't work. What is the output? Is an exception being thrown? What are the particulars? — btiernay, Oct 20 '12 at 15:01
org.postgresql.util.PSQLException: Can't use query methods that take a query string on a PreparedStatement. at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:294) at EngineClasses.Record.CreateNewRecord(Pacjent.java:62) at EngineClasses.Record.(Record.java:37) at mainPackage.Test.main(Test.java:38) — Ariel Grabijas, Oct 20 '12 at 15:03
I've found this http://archives.postgresql.org/pgsql-jdbc/2004-11/msg00201.php but now i have no idea how to insert all of my data into database. — Ariel Grabijas, Oct 20 '12 at 15:04
@AmitD but that link's diagnose (not the solution) is spot on nevertheless — fvu, Oct 20 '12 at 15:32

score 1 · Accepted Answer · edited May 23 '17 at 10:24

stat.executeUpdate(createTable);

is the culprit. On PreparedStatements executeUpdate takes no parameters.

So, modify that line to - just as expained by Tom Anderson

stat.executeUpdate();

and you're done

Also,

INSERT table VALUES (values)

makes your code unnecessarily brittle. Use

INSERT INTO table (fieldlist) VALUES (valuelist)

instead.

And also 2:

DateTime date = recordFields.bornDate.toDateTime();
query.setTimestamp(5, new Timestamp(data.getMillis()));

is a rather convoluted version of saying

query.setTimestamp(5,new Timestamp(recordFields.bornDate.getMillis()));

EDIT: regarding Date in postgressql

What Tom says is that to fill a PostgreSQL DATE type column you can either use a java.sql.date type variable (as opposed to the "common" java.util.Date), or a javax.sql.TimeStamp. But that's totally unrelated to the present problem ( which is caused by the parameter in the call to executeUpdate ) and what you do here (set it using a Timestamp type variable) is OK.

What method should i use, to send that kind of query with parameters? — Ariel Grabijas, Oct 20 '12 at 15:19
Also please take a look on this question: http://stackoverflow.com/questions/12182751/date-in-postgressql and explaint to me Tom Anderson's answer, because looks like i dont understand something. — Ariel Grabijas, Oct 20 '12 at 15:21

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

Have a look at this question.

Essentially, your code should have the following form:

String query = "..."; 
PreparedStatement stat = conn.prepareStatement(query); 
ResultSet rs = stat.executeQuery(); 
while (rs.next()) { 
  // TODO 
}

Because you haven't included all the source code of the problematic method, it's difficult to diagnose completely.

edited May 23 '17 at 10:24

Community

1
1

answered Oct 20 '12 at 15:06

btiernay

7,873
5
42
48

Insert different datatypes into postgreSQL in a query error

2 Answers2