On Thursday, March 21, 2013 1:26:47 AM UTC-7,
twonj...@gmail.com wrote:
> I am using a java [sic] program to read lines from csv and insert them into mysql database.
> Whenever i [sic] run my program i discovered i could not insert values into a date field insert
> mysql [sic] if the value comming from the file is null. However if i change the field to a varchar,
> it works fine. I want to maintain the field as a date field. Below is the snippet of my code.
>
> queryString = "INSERT INTO account (MNE,DATE1,DATE2,LINK)
> values('"+data[0]+"','"+data[1]+"','"+data[2]+"','"+data[3]+"')";
This is a dangerous way to inject SQL values. Any mistake in the 'data[n]' values will
screw up the SQL, or worse, open a security hole.
> stmt=conn.createStatement();
> val = stmt.executeUpdate(queryString);
>
> and below is the line to be written
>
> TEE,29-11-12,,90681
> BBB,29-11-12,,90681
> CCC,29-11-12,,90681
>
> below is the error message (i think it comes because the value for DATE2 is null).
It is not NULL, which is a SQL keyword. Be precise.
Issue the equivalent SQL command through the command line. You will find that it fails
the same way.
A 'PreparedStatement' will serve you better, but meanwhile make sure that you put the
correct syntax into your SQL statement. I'm betting that '' is not a valid DATE value.
> 712 [pool-1-thread-1] ERROR main.ProcessInboxFileTask - Failed to upload: C:\Users\Teejay\Documents\NetBeansProjects\NetBeans 6.9.1\TEEJAYD\all\GTUS.csv
>
> com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '' for column 'DATE2' at row 1
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3591)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
--
Lew