Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to write to new table?

 
Elahe Shafie
Ranch Hand
Posts: 291
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jamie and the other friends,
I'm posting my code and shows I'm reading submittedDate from table bug and I have to write it to new table exactly like bug table just submittedDate it's column type is Date(the old one submittedDate was varchar(20))
Could you please help me how can I implement this part (I mean write in new table?)
Time is really tied up and appreciate for your help like always...
Many thanks,
Elahe
[code]
....
rs =stmt.executeQuery("select bugNumber,submittedDate from bug ");

String strWork;
Date dateWork;
String strPutBack;

//* Format the Date
SimpleDateFormat formatter = new SimpleDateFormat("M/d/yy");

while (rs.next()) {
strWork = rs.getString(2);
dateWork = null;

/*parse to Date*/
try {
long l = Date.parse( strWork );
dateWork = new Date(l);
}
catch( Exception x ) {
strPutBack = "-";
}

try {
strPutBack = formatter.format(dateWork);
System.out.println( "was string "+ strWork + " new date "+ strPutBack );

}
catch( Exception x ) {
}

}

}
catch( SQLException x ) {
}
catch( Exception x ) {
}
...
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elahe
To create a new table just create a sql statement like any other table to create it then write to it with an INSERT query.
Some databases have a way to create temporary tables that only last for a certain amount of time or until the current session is done. To see how to do that you'll need to consult the specs for the database your using, I dont think there is a standard way to it.
 
Elahe Shafie
Ranch Hand
Posts: 291
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
where should I put my insert statement in my code I don't know where is supposed to go...
Thanks,
Elahe
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elahe
It looks like you are getting iterating over the resultset from your query and changing the format of the date to how you want it. If nothing else is being changed then as soon as you ahve the date formatted correctly just insert the data from the current record of the resultset your iterating over into a new INSERT statemnt and put it into the new table. It would probably be one of the last lines in your while loop. Keep in mind you'll have to use a different statement to do the inserting ito the new table or you'll lose the resultset your iterating over.
Also, you should enclose code that you post in ubb code tags to make it more readable.
hope that helps
 
Elahe Shafie
Ranch Hand
Posts: 291
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dave for your help here is my code and I want to know am I doing right?my code is fine?
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elahe
Without actually running your ode it looks ok. Why are you creating the new Date object with a 1 in the constructor? I though you were setting it to the same date that you got from the database but in a different format - I may have misunderstood you.
You should create your prepared statemnt outside of the loop. If you leave it where it is you will get a new one very time and lose all the benefits of using one. Dont forget also with a prepared statemnt you should clear the parameters before you start adding more to it.
The general idea behind your code is fine and the logic should work fine. Are you having specific problems? Or errors?
 
Elahe Shafie
Ranch Hand
Posts: 291
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave,
As you said:
1. I take out the preparestmt from loop please see if I am correct now.
2.Yes I want to read Date and reformat it and put is in the new table same column name but different coulumn type(this time the column type is Date not String)
3.The error that I am getting is this:
No method found matching setDate(int,String)
pstmt.setDate(3,strPutBack);
^
No method found matching setDdate(int,String)
pstmt.setDdate(4,fixedDate);
^
2 errors
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try this:

I made some assumptions on converting fixedDate to a date. So check it over
Jamie
 
Elahe Shafie
Ranch Hand
Posts: 291
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you so much Jamie and also Dave...
Have a great weekend,
Elahe
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic