input a date

Konnor · #1 (**permalink**) 03-09-06, 19:17

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,

sathyaram_s · #2 (**permalink**) 03-09-06, 20:51

use date(flight date)

And what is the format that users are allowed to enter date

Konnor · #3 (**permalink**) 03-10-06, 03:07

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.

sathyaram_s · #4 (**permalink**) 03-10-06, 03:23

Can you post the generated INSERT Statement ?

Konnor · #5 (**permalink**) 03-10-06, 12:47

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.

n_i · #6 (**permalink**) 03-10-06, 13:13

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"?

craigmc · #7 (**permalink**) 03-10-06, 15:21

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.

craigmc · #8 (**permalink**) 03-10-06, 15:30

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.

Konnor · #9 (**permalink**) 03-10-06, 16:15

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);

craigmc · #10 (**permalink**) 03-10-06, 17:18

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.

craigmc · #11 (**permalink**) 03-10-06, 17:23

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.

sathyaram_s · #12 (**permalink**) 03-10-06, 17:34

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

craigmc · #13 (**permalink**) 03-10-06, 19:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech