DB problem

[Sherif Shehab]

Hi guys,

i'm facing a weird problem with oracle db ,i'm trying to connect ot it to get some data then put this data in Vector to use it later , the problem is sometime the db staty without retriving any thing so i check the sql statment it;s ok , i check the db connection it's ok , so i debugued my code i found it some times it stops (i mean sitting without any response) in this code :

rs = pSat.executeQuery(); 

PS: no exception given

here is the code for more clarification

   public void prepareTheData()
 
{
String dbUrl = client_input.getChoosenReportDbUrl();
date = client_input.getDate();
String dbDriver =   PropertyLoader.getInstance().getStrProperty("DB_DRIVER");
String dbuser = PropertyLoader.getInstance().getStrProperty("DB_USER");
String dbPass = PropertyLoader.getInstance().getStrProperty("DB_PASSWORD");
Connection con = null;
Statement st;
ResultSet rs = null;
String[] sqlTa = new String[12];// 12
String sqlMw[] = new String[1]; // 2
sqlTa[0] = " select count(distinct cifno) from ivrlog where datelog >= '" + date
+ " 00:00:00.000' and datelog <= '" + date
+ " 23:59:59.999' and menuid =   'Process IVR Tadawul Phone Call '";
sqlTa[1] = " select count (*) from ivrlog where datelog >= '" + date
+ " 00:00:00.000' and datelog <= '" + date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call'";
sqlTa[2] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '8001' and '9590' ";
sqlTa[3] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '3201' and '3290' ";
sqlTa[4] = " select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '101' and '360'";
sqlTa[5] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '7101' and '7160'";
sqlTa[6] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '7501' and '7690'";
sqlTa[7] = " select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '5101' and '5190'";
sqlTa[8] = " select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '5401' and '5490' ";
sqlTa[9] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '7701' and '7960' ";
sqlTa[10] = "select count (*) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and menuid = 'Process IVR Tadawul Phone Call' and extno between '9701' and '9790'";
sqlTa[11] = "select count (distinct callid) from ivrlog where datelog >= '"
+ date
+ " 00:00:00.000' and datelog <= '"
+ date
+ " 23:59:59.999' and ((extno between '8001' and '9590') or (extno between '3201' and '3290') or (extno between '101' and '360') or(extno between '7101' and '7160') or (extno between '7501' and '7690') or (extno between '5101' and '5190') or (extno between '5401' and '5490') or (extno between '7701' and '7960') or (extno between '9701' and '9790'))";
 
// MW sql
 

sqlMw[0] = "select  DISTINCT  dateaud, computeraud, messageaud from ivraudit where dateaud>='"
+ date
+ " 03:00:00:000'and dateaud<='"
+ date
+ " 23:59:59:999' and messageaud like '%TCP%' order by dateaud";
 
PreparedStatement pSat;
 
try {
Class.forName(dbDriver);
con = DriverManager.getConnection(dbUrl, dbuser, dbPass);
st = con.createStatement();
 
}
catch (ClassNotFoundException e) {
System.out.println("Error in \'Class.forName(dbDriver);\' DB Driver");
e.printStackTrace();
}
catch (SQLException e) {
System.out.println("Error in Db connection  or in creating the statement ....");
e.printStackTrace();
}
// tadawul logic
 
if (checkFlag.equalsIgnoreCase("tadawul"))
{
int m = 0;
 
System.out.println("Entered date :" + date);
System.out.println("Tadawul report is going to  be created. Please Wait...");
 
for (int i = 0; i < sqlTa.length; i++) {
try {
 
// st= con.createStatement();
pSat = con.prepareStatement(sqlTa[i]);
 
rs = pSat.executeQuery(); //problem happens here only
 
while (rs.next()) {
// System.out.println(rs.getString(1));
 
vTa.addElement(rs.getInt(1));
 
// System.out.println( "v [" +x+" ]" + v.get(x));
System.out.println("Preparing report records .... " + m++);
}
 
}
catch (SQLException e) {
 
e.printStackTrace();
}
finally {
try {
System.out.println("Closing Tadawul Db connection ...");
rs.close();
con.close();
System.out.println("Tadawul Db connection closed.");
 
}
catch (SQLException e) {
System.err.println("Error in closing the Tadawul Db connection");
e.printStackTrace();
}
}
 
}
 
else {
 
int ss = 0;
System.out.println("Entered date :" + date);
System.out.println("MW report is going to  be created. Please Wait...");
 

 

 

for (int i = 0; i < sqlMw.length; i++) {
try {

pSat = con.prepareStatement(sqlMw[i]);
rs = pSat.executeQuery(); // it goes fine here !! 
 
while (rs.next()) {
// System.out.println(rs.getString(1));
 
vMwDate.addElement(rs.getString(1));
vMwServerName.addElement(rs.getString(2));
vMwErrorDesc.addElement(rs.getString(3));
 
System.out.println("Preparing report records .... " + ss++);
 
}
 
}
catch (SQLException e) {
 
e.printStackTrace();
}
finally {
try {
System.out.println("Closing Mw Db connection ...");
rs.close();
con.close();
System.out.println("Mw Db connection closed.");
}
catch (SQLException e) {
System.err.println("Error in closing the Mw Db connection");
e.printStackTrace();
}
}
formatMwDataTime();
// dte= new DataToExcel();
dte.writeToExcel();
 
}
 
}
 
System.err.println("Done report created.......");
}
 
} 

[Paul Sturrock]

Sounds like it could be a performance problem. Have you tried profiling your SQL statements?

[Rob Spoor]

This would fit better in the JDBC forum. Off we go!

[Sherif Shehab]

Originally posted by Paul Sturrock:
Sounds like it could be a performance problem. Have you tried profiling your SQL statements?

Hi , i tried the the SQL statements outside the code , the weird thing sometimes it getting results , and sometimes it hangs !!! although the db connection is fine !!!

[Paul Sturrock]

I'd profile your queries. You may be missing indices or their statistics may need rebuilt. If you are unsure how to do this, have a read of your Oracle docs for "explain plan" in the performance tunning book.