Results 1 to 13 of 13

Thread: input a date

  1. #1
    Join Date
    Mar 2006
    Posts
    8

    Unanswered: input a date

    I have a table with a DATE column definition and am trying to insert some data via JDBC to a DB2 database

    Code:
    CREATE TABLE Flight
    (
    	FlightID	INTEGER NOT NULL,
    	FlightDate	DATE NOT NULL,
    )
    I am using a Keyboard read class in my java program to get the date from the user.

    Code:
    String flightDate = Keyboard.readString();
    and then using the following SQL to try and INSERT the date and other fields.

    Code:
    INSERT INTO Flight VALUES ("+flightID+", '"+flightDate+"')
    which gives me the error:

    'The syntax of the string representation of a datetime value is incorrect'

    I have tried different types input such as 20010209, 2001-02-09 etc with no luck.

    ta,

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    use date(flight date)

    And what is the format that users are allowed to enter date
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2006
    Posts
    8
    not quite sure what you mean by use 'date(flight date)'

    Is this inside the SQL INSERT?

    something like DATE('"+flightDate+"') unfortunately churns up the same error.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you post the generated INSERT Statement ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2006
    Posts
    8
    The INSERT statement I have tried to use is in the first post.

    I have tried almost every combination of date input, each returning the same thing.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Konnor
    The INSERT statement I have tried to use is in the first post.
    No, it's not. It's a java expression.

    You would want to see the actual statement that you're trying to execute. Print it before executing and you'll most likely see what the problem is.

    By the way, have you heard about "SQL injection"?

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    This format should work:
    {d '1952-01-18'}

    For a Timestamp use:
    {ts '1952-01-18 00:00:00'}

    I would also try to print the insert statement and manually run it, as n_i suggested.

  8. #8
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Another option is to use the JDBC java.sql.Date class. Create a java.sql.Date and do toString on it to get the string value. It will probably come out in the correct format.

  9. #9
    Join Date
    Mar 2006
    Posts
    8
    I'm still stumped unfortunately.

    Sorry I'm a little new to this so understanding your comments is a touch tricky

    This link shows the program running with the inputs, errors and SQL

    http://img232.imageshack.us/img232/4...uggered7ye.jpg

    After the 5 inputs, the INSERT SQL statement is printed on the screen, then (attempted to be) executed.

    All the code is here...

    Code:
    //Get inputs
    System.out.println("Please input FlightID");
    int flightID = Keyboard.readInt();
    System.out.println("Please input Origin");
    String origin = Keyboard.readString();
    System.out.println("Please input Destination");
    String destination = Keyboard.readString();
    System.out.println("Please input Flight Date");
    String flightDate = Keyboard.readString();
    System.out.println("Please input Capacity of aircraft");
    int capacity = Keyboard.readInt();
    			    	
    //print out insert
    System.out.println("INSERT INTO Flight VALUES ("+flightID+", '"+origin+"', '"+destination+"','"+flightDate+"', "+capacity+")");
    			    	
    //execute query
    String SQLStatement = "INSERT INTO Flight VALUES ("+flightID+", '"+origin+"', '"+destination+"','"+flightDate+"', "+capacity+")";
    st.executeUpdate(SQLStatement);

  10. #10
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    It looks to me like your date syntax is correct. Maybe try specifying the columns instead of relying on the default order. You might have the columns out of order in your statement from the table definition.
    It is always the best practice to do that. ex)
    INSERT INTO FLIGHT (FlightID, FlightDate) VALUES (1, '2006-01-01')

    Try running the statement manually against the database instead of through your java program. That format should work. I ran your create table statement here and tried it.

  11. #11
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Your debug statements there are iffy as well. You want to print the actual statement you are running, so instead of:

    System.out.println("INSERT INTO Flight VALUES ("+flightID+", '"+origin+"', '"+destination+"','"+flightDate+"', "+capacity+")");
    //execute query
    String SQLStatement = "INSERT INTO Flight VALUES ("+flightID+", '"+origin+"', '"+destination+"','"+flightDate+"', "+capacity+")";
    st.executeUpdate(SQLStatement);


    do

    String SQLStatement = "INSERT INTO Flight VALUES ("+flightID+", '"+origin+"', '"+destination+"','"+flightDate+"', "+capacity+")";
    System.out.println(SQLStatement);
    //execute query
    st.executeUpdate(SQLStatement);

    That way you are sure there is not a typo in the statement you are running compared to the one you are printing out. You could have a comma on the wrong side of a quote or something. And if you change one statement, you might forget to change the other, so you won't see the error.

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    INSERT INTO FLIGHT (FlightID, FlightDate) VALUES (1, '2006-01-01')

    shoud be

    INSERT INTO FLIGHT (FlightID, FlightDate) VALUES (1, date('2006-01-01'))

    And, if this is your real world java app, consider using Parameter Markers ... That's much more efficient ..

    And you can avoid SQL Injection (n_i's comment above)

    Cheers
    Sathyaran
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    But date('2006-01-01') is not standard SQL, it's Db2 specific.
    The beauty of java and JDBC is that it should work for any database vendor. The JDBC driver should take care of converting formats to whatever the specific database wants.

    Most drivers I believe should process the XOPEN escape sequences.
    so it would be
    ...VALUES( 1, {d '2006-01-31'} )
    most also probably would just take the string '2006-01-01' as well without the escape sequence.

    We use that same format against SQL Server, MS Access and DB2 databases.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •