How to write to new table?

[Elahe Shafie]

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]

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]

where should I put my insert statement in my code I don't know where is supposed to go...
Thanks,
Elahe

[Dave Vick]

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]

Thanks Dave for your help here is my code and I want to know am I doing right?my code is fine?

 stmt = con.createStatement();
 //Execute an SQL query, get a resultSet
   rs =stmt.executeQuery("select ID,bugNumber,bdesc,submittedDate,fixedDate,userID,contact from bug ");
            int ID;
            int bugNumber;
            String bdesc;
            String strWork;
            String fixedDate;
            int userID;
            String contact;
            Date dateWork;
            String strPutBack;
            
//* Format the Date
    SimpleDateFormat formatter = new SimpleDateFormat("M/d/yy");
            
            while (rs.next()) {
                ID = rs.getInt(1);
                bdesc = rs.getString(2);
                strWork = rs.getString(3);
                fixedDate = rs.getString(4);
                userID = rs.getInt(5);
                contact = rs.getString(6);
                bugNumber = rs.getInt(7);
                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 );
PreparedStatement s =con.prepareStatement("insert into bugt values (?,?,?,?,?,?,?)");
                    s.setInt(1,ID); 
                    s.setString(2,bdesc);
                    s.setString(3,strPutBack);
                    s.setString(4,fixedDate); 
                    s.setInt(5,userID); 
                    s.setString(6,contact);
                    s.setInt(7,bugNumber); 
                    
                    s.executeUpdate();
                }
                catch( Exception x ) {

[Dave Vick]

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]

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

//Execute an SQL query, get a resultSet
  rs =stmt.executeQuery("select ID,bdesc,submittedDate,fixedDate,userID,contact,bugNumber from bug ");
            int ID;
            int bugNumber;
            String bdesc;
            String strWork;
            String fixedDate;
            int userID;
            String contact;
            Date dateWork;
            String strPutBack;
            
//* Format the Date
SimpleDateFormat formatter = new SimpleDateFormat("M/d/yy");
            
while (rs.next()) {
                ID = rs.getInt(1);
                bdesc = rs.getString(2);
                strWork = rs.getString(3);
                fixedDate = rs.getString(4);
                userID = rs.getInt(5);
                contact = rs.getString(6);
                bugNumber = rs.getInt(7);
                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 ) {
           }
         }
PreparedStatement pstmt =con.prepareStatement("insert into bugt values (?,?,?,?,?,?,?)");
                    pstmt.setInt(1,ID); 
                    pstmt.setString(2,bdesc);
                    pstmt.setDate(3,strPutBack);
                    pstmt.setDdate(4,fixedDate); 
                    pstmt.setInt(5,userID); 
                    pstmt.setString(6,contact);
                    pstmt.setInt(7,bugNumber); 
                    
              pstmt.executeUpdate();              
        }
        catch( SQLException x ) {
        }

[Jamie Robertson]

try this:

try
{

//Execute an SQL query, get a resultSet
rs =stmt.executeQuery("select ID,bdesc,submittedDate,fixedDate,userID,contact,bugNumber from bug ");
int ID;
int bugNumber;
String bdesc;
String strWork;
String fixedDate;
int userID;
String contact;
java.util.Date dateWork;
java.util.Date convertedFixedDate;
String strPutBack = null;

//*declare prepared statement outside of loop
PreparedStatement pstmt =con.prepareStatement("insert into bugt values (?,?,?,?,?,?,?)");
 
// parse date in with String format of 
// "Fri Jan 04 14:41:20 PST 2002" -->taken from original specs

SimpleDateFormat string_to_date = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");

// format date to string pattern of "1/04/2002" --> from your original specs
SimpleDateFormat date_to_string = new SimpleDateFormat("M/dd/yyyy")
while (rs.next()) 
{
ID = rs.getInt(1);
bdesc = rs.getString(2);
strWork = rs.getString(3);
fixedDate = rs.getString(4);
userID = rs.getInt(5);
contact = rs.getString(6);
bugNumber = rs.getInt(7);
dateWork = null;
convertedFixedDate = null;
                
/*parse to Date*/
     try 
     {
// parse the input String into a date
dateWork = string_to_date.parse( strWork );
//assuming fixedDate is in the same format as strWork
convertedFixedDate = string_to_date( fixedDate );


      }
     catch( Exception x ) 
     {
//handle the date not correctly parsed exception
if ( dateWork == null )
{
dateWork = new java.util.Date();  
}
if ( convertedFixedDate == null )
{
convertedFixedDate = new java.util.Date();
}
     System.out.println(" Date parse error: " + x.getMessage() );
      }
      
      
                
     try 
     {
strPutBack = date_to_string.format( dateWork );
        System.out.println( "was string "+ strWork + "  new date "+ strPutBack );
       }
    catch( Exception x ) 
    {
strPutBack = "-";
      }
      
      pstmt.setInt(1,ID); 
pstmt.setString(2,bdesc);

//still have problems with the next 2 lines.  You are trying to use 
//setDate(int, java.sql.Date) but instead you have (int, String).  
//won't work.  you need to convert both variables to java.sql.Dates
pstmt.setDate(3, new java.sql.Date( dateWork.getTime() ) );

// you never parsed this String to a date value???  
//So I tried to guess in the string conversion
pstmt.setDate( 4, new java.sql.Date( convertedFixedDate.getTime() ) ); 



pstmt.setInt(5,userID); 
pstmt.setString(6,contact);
pstmt.setInt(7,bugNumber); 
                    
       pstmt.executeUpdate();        
      
  
                          
} // end while


}
catch( SQLException x ) 
{
System.out.println( "SQLException: " + e.getMessage() );
}

I made some assumptions on converting fixedDate to a date. So check it over
Jamie

[Elahe Shafie]

Thank you so much Jamie and also Dave...
Have a great weekend,
Elahe