wood burning stoves 2.0*
The moose likes Java in General and the fly likes Problem in inserting from one table to another Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Problem in inserting from one table to another" Watch "Problem in inserting from one table to another" New topic
Author

Problem in inserting from one table to another

Sim Raina
Ranch Hand

Joined: Nov 20, 2003
Posts: 33
i am in the starting of making this program that inserts records in sql database from another master sql database. i have the following important fields in master database like usrnme t_date and t_time
and in the another table i wanna have usrnme,T_date , starttime and endtime
the problem is that i can't to get endtime to work, the endtime depends on two things: on user, meaning starting and ending of users time and it also depnds on interval provided in the constant field. that means for ex if the interval is for 10 min then user "sim" started time on 19:00:00 and end time would be 19:10:00. If no interval is provided and then just started and end time for user,
i can't get end time to work , it is giving me the wrong end time.
all the help i can get is appreciated
and if u have any ideas of doing it some other way please let me know
and i am giving a copy of my code and the result from sql
and it is a a little messy because i am stilll testing it and i changed it alot and now i can't even figure it out so i don't think it is going to big of help and i am sorry about it
thanks in advance
here is the code
import java.sql.*;
import java.io.*;
import java.text.DateFormat;
import java.util.Date;
import java.util.*;
import javax.mail.Message.*;
import javax.mail.internet.*;
import javax.mail.*;
import java.text.*;
import java.io.File;
import java.text.DateFormat;
public class NewSession
{
private int Session = 0;
public NewSession()
{
CreateNewSession();
}
public void CreateNewSession()
{

try{
Connection con;
Class.forName(Testing.FINAL_CLASSNAME).newInstance();
String p_url = Testing.FINAL_URL;
con = DriverManager.getConnection(p_url,"","");
String Query1 = "Select usrnme, t_date, t_time from weblogs where SessionId = '0' order by usrnme, t_date, t_time";
String Query2 = "Select max(SessionId) as count from NewSession";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(Query1);

String usrnme = "******";
String last_Time = "00:00:00";
String time = " ";

String Last_Min = last_Time.substring(3,5);
System.out.println("LastTime " +Last_Min);
Statement stmt1 = con.createStatement();
ResultSet rs1 = stmt1.executeQuery(Query2);
while (rs1.next())
{
Session = rs1.getInt("count");
System.out.println("SessIONID " + Session);
}

while (rs.next())
{
//String Query3 = "Select max(t_time) as EndTime from weblogs where usrnme = '" + rs.getString("usrnme") + "'";
//Statement stmt2 = con.createStatement();
//ResultSet rs2 = stmt2.executeQuery(Query3);
String t_Time = rs.getString("t_Time");
System.out.println("THE REAL TIME " + t_Time);
String sub_Hours = t_Time.substring(0,2);
String sub_Min = t_Time.substring(3,5);
String sub_Sec = t_Time.substring(6,8);
String sTime;
GregorianCalendar cal = new GregorianCalendar();
SimpleDateFormat fmt = new SimpleDateFormat("mm");
cal.set(Calendar.HOUR,Integer.parseInt(sub_Hours));
cal.set(Calendar.MINUTE,Integer.parseInt(sub_Min));
cal.set(Calendar.SECOND,Integer.parseInt(sub_Sec));
Date tmpDate = cal.getTime();
//cal.add(Calendar.MINUTE, - Testing.timeEx);
//tmpDate = cal.getTime();
//fmt.setCalendar(cal);
sTime = fmt.format( cal.getTime());
System.out.println("getTIme "+sTime);
System.out.println(rs.getString("usrnme"));
System.out.println("username" +usrnme);

if (!(rs.getString("usrnme").equals(usrnme)))
{
Session = Session +1;
PreparedStatement pStmt = con.prepareStatement("insert into NewSession()"+
"values(?,?,?,?,?,?)");
pStmt.setString(1, rs.getString("t_date"));
pStmt.setString(2, rs.getString("usrnme"));
pStmt.setInt(3,Session);
pStmt.setString(4,rs.getString("t_Time"));
//while (rs2.next())
//{
//pStmt.setString(5,rs2.getString("EndTime"));
//}
pStmt.setString(5,"");
pStmt.setString(6,"");
pStmt.execute();
usrnme =rs.getString("usrnme");
//cal.add(Calendar.MINUTE, + Testing.timeEx);
///sim = fmt.format( cal.getTime());
Last_Min = sTime;
System.out.println("LasTMIN " +Last_Min);

}
else
{
PreparedStatement pStmt1 = con.prepareStatement("update NewSession"+
" Set EndTime = '" + rs.getString("t_Time") + "' where SessionId = '" + Session +"'");
pStmt1.executeUpdate();
System.out.println("Session " +Session);
System.out.println(rs.getString("t_Time"));
if(!(time.equals(sTime)))
{
Session = Session +1;
PreparedStatement pStmt = con.prepareStatement("insert into NewSession()"+
"values(?,?,?,?,?,?)");
pStmt.setString(1, rs.getString("t_date"));
pStmt.setString(2, rs.getString("usrnme"));
pStmt.setInt(3,Session);
pStmt.setString(4,rs.getString("t_Time"));
//while (rs2.next())
//{
//pStmt.setString(5,rs2.getString("EndTime"));
//}
pStmt.setString(5,"");
pStmt.setString(6,"");
pStmt.execute();
usrnme =rs.getString("usrnme");
//cal.add(Calendar.MINUTE, + Testing.timeEx);

Last_Min = sTime;
time = Last_Min;
System.out.println("Last MIn " +Last_Min);
System.out.println(time);
}

}

} //End While
con.close();

}//EndTry
catch (Exception e) {
System.out.println(e);

} //End Catch
} //End read

public static void main(String[] args)

{
NewSession dt = new NewSession();
}
}
RIGHT NOW THE INTERVAL IS FOR 10 MIN
and look and the result
t_date | USRNME | sessionId | StartTime | EndTime | TotalTime
-----------+--------+-----------+-----------+----------+-----------
2003-12-05 | henry | 1 | 19:01:01 | 19:01:03 | 0
2003-12-05 | henry | 2 | 19:01:03 | 19:02:00 | 0
2003-12-05 | henry | 3 | 19:02:00 | 19:03:06 | 0
2003-12-05 | henry | 4 | 19:03:06 | 19:04:00 | 0
2003-12-05 | henry | 5 | 19:04:00 | 19:05:07 | 0
2003-12-05 | henry | 6 | 19:05:07 | 19:06:00 | 0
2003-12-05 | henry | 7 | 19:06:00 | 19:07:02 | 0
2003-12-05 | henry | 8 | 19:07:02 | 07:12:00 | 0
2003-12-06 | henry | 9 | 07:12:00 | 07:12:54 | 0


if u can't impress people with ur intelligence , confuse them with ur bullshit
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem in inserting from one table to another
 
Similar Threads
Need a program in timer schedule..
Calendar class Regarding...
java sql help
getting the time in milliseconds
Woa! Why does Date/Calendar do this?