Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.help > #2612 > unrolled thread

java reading csv file and inserting into database gives date value error.

Back to article view | Back to comp.lang.java.help

Contents

  java reading csv file and inserting into database gives date value error. twonjee2002@gmail.com - 2013-03-21 01:26 -0700
    Re: java reading csv file and inserting into database gives date value error. Roedy Green <see_website@mindprod.com.invalid> - 2013-03-21 14:10 -0700
    Re: java reading csv file and inserting into database gives date value error. Lew <lewbloch@gmail.com> - 2013-03-21 14:17 -0700

#2612 — java reading csv file and inserting into database gives date value error.

I am using a java program to read lines from csv and insert them into mysql database. Whenever i run my program i discovered i could not insert values into a date field insert mysql 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]+"')";
      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).


    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)

[toc] | [next] | [standalone]

#2613

On Thu, 21 Mar 2013 01:26:47 -0700 (PDT), twonjee2002@gmail.com wrote,
quoted or indirectly quoted someone who said :

>I am using a java program to read lines from csv and insert them into mysql=
> database.

there are so many different formats for dates.

I like to standardize on ISO format yyyy-mm-dd

Make sure you are feeding your SQL /JDBC dates in the form it wants
and that you have configured your SQL dates to match.

One time a long time ago I got so pissed with incompatible date
formats I stored them in integer days since 1970, which were simple
ints that could be confused by different SQL engines.

-- 
Roedy Green Canadian Mind Products http://mindprod.com
Every method you use to prevent or find bugs leaves a residue of subtler 
bugs against which those methods are ineffectual. 
 ~ Bruce Beizer  Pesticide Paradox

[toc] | [prev] | [next] | [standalone]

#2614

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.help

csiph-web