Database query issue

Question

Relatively new to using database and for some reason I can't get this 'execute' to work.

statment2.execute("insert into table Value (" + int + "," + date + "," + int + ",'" + string + "')");

The error I get is "missing a comma". The date is designated as dates only in that particular field.

I set it up as follows

Date date = new Date();
date.setMonth(month);
date.setYear(year);
date.setDate(weekStart); //weekStart is always monday

Do I need to use just plain old date or date.toString? I was going to use Calendar but I don't know how to set a DB date using the Calendar object. I didn't see a "gety/m/d" method.

So, is the problem my query or am I improperly using the Date object to set the date in the database?

Edit:

Tried the response, got incorrect format - Expected Date got number. Tried

sqlDate.valueOf(dateString I created)
sqlDate.toString()
sqlDate

Using a preparedStatement wouldn't fix this would it? I realize it's supposed to be better for security reasons.

Few things - 1) depends on the database, every database will handle this differently. Check out their docs. 2) You should look into JDBC prepared statements instead of creating our own SQL on the fly - you'll end up avoiding some of these types of issues. — Roy Truelove
– Roy Truelove, Commented Mar 12, 2012 at 18:51

CoolBeans · Accepted Answer · 2012-03-12 19:12:33Z

2

First, you should use a PreparedStatement to insert values in your query. This has many advantages including avoiding SQL Injection issues. If you use PreparedStatement, you will be avoid the errors that you are seeing now. Your code using PreparedStatement would something like this:

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "insert into table (column1,column2,column3,column4) values(?, ?, ?,?)";

      pstmt = conn.prepareStatement(query); 
      pstmt.setInt(1, 1); 
      pstmt.setDate(2, sqlDate); 
      pstmt.setInt(3, 3); 
      pstmt.setString(3, "test"); 
      pstmt.executeUpdate();
    } catch (Exception e) {
      //log the error messages log.error(e,e);
      //throw the actual exception upstream
    } finally {
      pstmt.close();
      conn.close();
    }

I am not sure what you meant by "DB" date. If you are after the sql date object you can convert a java.util.Date object to a java.sql.Date object this way:

java.util.Date date = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date(date.getTime());

edited Mar 12, 2012 at 19:12

answered Mar 12, 2012 at 18:49

CoolBeans

20.9k10 gold badges88 silver badges102 bronze badges

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

3 Comments

cphilpot Over a year ago

Am I supposed to copy String query = "insert into table (column1,column2,column3,column4) values(?, ?, ?,?) exactly or replace column1 with it's literal name? Sorry for the confusion. Never used prepared statements (going to start though)

CoolBeans Over a year ago

@cphilpot - replace column1 with real name. It's not part of PreparedStatement. Standard SQL.

cphilpot Over a year ago

Pff, THANKS! Prepared statements are my friend now lol. Will learn how to use them more effectively. :)

Collectives™ on Stack Overflow

Database query issue

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