aspose file tools*
The moose likes Java in General and the fly likes problem in formatting date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "problem in formatting date" Watch "problem in formatting date" New topic
Author

problem in formatting date

jen plecerda
Greenhorn

Joined: Mar 02, 2007
Posts: 4
hi!
i have a string in this kind of format and i need to store it in mysql database..IGMTCCBNO12/1/2006

i established a connection to the database and inserted the value but when it comes to date, the value inserted in the mysql was this value: 0000-00-00. i was expecting it should be like this:2006-01-12.. any suggestion in my program is much appreciated!

here is the program that i did:
public void processWord(String word){

if(ctr==0){
setter.setSet1(word);
}
if(ctr==1){
if (word.equalsIgnoreCase("CCB");
setter.setSet2(word);
}
if(ctr==2){
if (word.equalsIgnoreCase("NO")|| word.equalsIgnoreCase("YES")){
setter.setSet3(word);
}
}
if(ctr==3){
DateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
try{
java.util.Date date = (Date)formatter.parse(word);
java.sql.Date sqlDate = new java.sql.Date( date.getTime());
setter.setSet4(sqlDate);
}catch (ParseException pe){
pe.printStackTrace();
setter.setSet4("error date");
}
}

public class Setter {
private String set1;
private String set2;
private String set3;
private String set4;

public String getSet1() {
return set1;
}

public void setSet1(String set1) {
this.set1 = set1;
}

public String getSet2() {
return set2;
}

public void setSet2(String set2) {
this.set2 = set2;
}

public String getSet3() {
return set3;
}

public void setSet3(String set3) {
this.set3 = set3;
}

public String getSet4() {
return set4;
}

public void setSet4(String set4) {
this.set4 = set4;
}
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Jen,

I don't see any code that inserts the data.
Where does "ctr" get it's value?

Herman
jen plecerda
Greenhorn

Joined: Mar 02, 2007
Posts: 4
I don't see any code that inserts the data.
Where does "ctr" get it's value?

hi Herman!

sory i forgot to paste the value of ctr for the processWord().
int ctr=0;
anyway this is the code for inserting my data:

public String LoadIt(String filename, int reportid) {

String outfile = "";
try {
BufferedReader in = new BufferedReader(new FileReader (filename));
String str;
MyDBConnection db = new MyDBConnection();
db.init();
Connection con = db.getMyConnection();
Statement stmt = null;
String sql = "";
while ((str = in.readLine()) != null) {
outfile += str;

this.Process(str);
ctr = 0;

try {
stmt = con.createStatement();
sql = "insert into cspdata (servicename, subtype, hyb, transdate, servicetype) values ('"+ setter.getSet1() +"', '"+setter.getSet2()+"','"+setter.getSet3()+"', '"+setter.getSet4()"'";
System.out.println(sql);
stmt.executeUpdate(sql);

} catch (Exception e) {
e.printStackTrace();
}
}
stmt.close();
con.close();
in.close();
}catch(Exception fnfe){
System.out.println("File does not exist");
}
return outfile;
}

thanks!!
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14428
    
  23

I see you print the SQL statement right before you execute the update:

System.out.println(sql);

What does the SQL statement look like? Is it a valid statement for MySQL? What happens if you execute this statement by hand (in a MySQL console window)?

You have to find out if it is a Java problem or a problem with the SQL.

(Note that it is always better to use PreparedStatement instead of concatenating an SQL statement together yourself.)


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Jen,

I see you have 5 colums in your table, but you only insert 4 values.
Maybe this will give you an error(just look at the stacktrace that you print)

I agree with Jesper that it's better(faster, more secure, easier to read) to use Preparedstatement instead of Statement.

Note that in case of an Exception, your statement and connection will not get closed. They will always get closed, if you just put them in a finally{} block.

Herman
jen plecerda
Greenhorn

Joined: Mar 02, 2007
Posts: 4
hi guys!

after doing the prepared statement that mr. ricky martaputra suggested in my first post: problem with storing date now i realized and agree to mr jesper and herman that using prepared statement is much better instead of using statement. don't wory guys my program is now working!hehehe...

thanks a lot for taking your time looking at my code!

anyway here's the sample code that mr ricky posted in case someone need it:

code:

String query = "INSERT INTO A(CREATED_DATE) VALUES ?";PreparedStatement ps = connection.preparedStatement(query);ps.setDate( 1 , [your date])ps.execute();
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem in formatting date