• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1957
  • Last Modified:

reading excel cell values of each row and check againest oracle database

I have following program to read the excel file(which has 2 columns and say 10 rows) and generate query with two cel values of each row of excel. The query should hit the database and execute select on those excel cell values and find out if the values are already present in database table or not. I kind of wrote program like this but I am getting erros

like

java.sql.SQLException: ORA-01000: maximum open cursors exceeded


and also the new query is not formed properly for each and every  excel row cell values. Error coming in recognizing 'FIELD2'. Please advise on how can I modify toi make this program work.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance



import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;


import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class CopyOfReadExcelEnhanceDBConnPropFileFinal {  

      
      private String inputFile;
      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException, BiffException  {
            
            List list = new ArrayList();
            String query = "select * from DEF.TestTable where col1='{FIELD1}' and col2='{FIELD2}'";
            

            Connection conn2 =  ConnectionDataSource.getConnection(XyzConstants.ABC_INDEXES);
      
            
            
            
            
            

            File inputWorkbook = new File(inputFile);
            Workbook w;
            w = Workbook.getWorkbook(inputWorkbook);
            // Get the first sheet
            Sheet sheet = w.getSheet(0);
            // Loop over first 10 column and lines
            System.out.println("rows"+sheet.getRows());
            System.out.println("columns"+sheet.getColumns());
            for (int j = 0; j < sheet.getColumns(); j++) {
            //      String newQuery = new String(query);
                  for (int i = 1; i < sheet.getRows(); i++) {
                        Cell cell = sheet.getCell(j, i);
                        String x = cell.getContents();
                        newQuery = newQuery.replaceAll("{FIELD"+(i+1)+"}", x);
                  
                        
                        
                        Cell leadLoanCellName = sheet.getCell(0,i);
                        Cell imageNamecellName = sheet.getCell(1,i);
                        String leadLoan = leadLoanCellName.getContents();
                        String imageName = imageNamecellName.getContents();
                        
                        //String query = "select * from DEF.TestTable where col1='{FIELD1}' and col2='{FIELD2}'";
                        
                        
                        
                        
                        
                        
                      

                        PreparedStatement pstmt = null;
                  
                        try{
                              //String sql = "SELECT * FROM CDMS.optimg01";
                              String query = "select * from CDMS.optimg01 where col1='ghi' and col2='klm'";
                              pstmt = connCdms.prepareStatement(query);
                              ResultSet rs1 = pstmt.executeQuery();


                              
                              // System.out.println("List of values");
                                while (rs1.next()){
                                String rec1 = rs1.getString(1);
                        
                                System.out.println("rec1 is"+rec1);
                                }

                        }catch(Exception e){
                  
                              e.printStackTrace();
                        }finally{
                  
                        }
                        
                        
                        
            
                  }
                  //list.add(newQuery);
                  
            }
            
            
      //      return list;
      }
            
      
      
      public static void main(String[] args) throws IOException, BiffException {
            CopyOfReadExcelEnhanceDBConnPropFileFinal test = new CopyOfReadExcelEnhanceDBConnPropFileFinal();
            test.setInputFile("c:/gp/Report.xls");
            test.read();


      }
      
}
Report.xls
0
gudii9
Asked:
gudii9
  • 5
  • 3
2 Solutions
 
gudii9Author Commented:
I am also seeing error popup like

Unhandled event loop exception
Item not added


giving swt error popup and abruptly closing the eclipse as well. Please advise
0
 
gudii9Author Commented:
Following program hitting againest same database and printing the values to console without any issue. I am having hard time incorporating the excel within this example and make connection properly to the database without making it many times

public class RiPropDS {
    public static void main(String[] args) throws SQLException {    
     
          RiPropDS test=new RiPropDS();
          test.checkRecords();
          
    }
   
   
   
    private void checkRecords() throws SQLException {       
          
   
          System.out.println("inside check record method");
            Connection conn2 =  ConnectionDataSource.getConnection(xyzConstants.ABC_INDEXES);
            System.out.println("after conenction"+conn2.toString());
            PreparedStatement pstmt = null;
            ResultSet resultDB2 = null;
            //log.debug("populateDocTypes");
            try{
                  String sql = "SELECT * FROM DEF.TestTable";
                  pstmt = conn2.prepareStatement(sql);
                  ResultSet rs1 = pstmt.executeQuery();


                  
                   System.out.println("List of values");
                    while (rs1.next()){
                    String rec1 = rs1.getString(1);
            
                    System.out.println("rec1 is"+rec1);
                    }

            }catch(Exception e){
      
                  e.printStackTrace();
            }finally{
                  try {
                        if(conn2 != null){
                              conn2.close();

                        }
                        if(pstmt != null){
                              pstmt.close();
                        }
                        if(resultDBCdms != null){
                              resultDB2.close();
                        }
                  } catch (SQLException e) {
            
                        e.printStackTrace();
                  }
            }
      
            
            
      }
   
   
}

Please advise on how to modify my program
0
 
gatorvipCommented:
Try to close each PreparedStatement and ResultSet explicitly in the same loop where you're creating them.

If you still get an error after that, for readability purposes, please post any additional error codes you get (along with line numbers, if any). Also, post the full code - for example, you mention an error in FIELD2, but the code you provided, as posted, doesn't actually use that. You're passing a harcoded query to the connection instead.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
gudii9Author Commented:
I added following lines


 pstmt.close();
  rs1.close();

Here is the updated program

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;


import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class CopyOfReadExcelEnhanceDBConnPropFileFinal {  

     
      private String inputFile;
      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException, BiffException  {
           
            List list = new ArrayList();
            String query = "select * from DEF.TestTable where col1='{FIELD1}' and col2='{FIELD2}'";
           

            Connection conn2 =  ConnectionDataSource.getConnection(XyzConstants.ABC_INDEXES);
     
           
           
           
           
           

            File inputWorkbook = new File(inputFile);
            Workbook w;
            w = Workbook.getWorkbook(inputWorkbook);
            // Get the first sheet
            Sheet sheet = w.getSheet(0);
            // Loop over first 10 column and lines
            System.out.println("rows"+sheet.getRows());
            System.out.println("columns"+sheet.getColumns());
            for (int j = 0; j < sheet.getColumns(); j++) {
            //      String newQuery = new String(query);
                  for (int i = 1; i < sheet.getRows(); i++) {
                        Cell cell = sheet.getCell(j, i);
                        String x = cell.getContents();
                        newQuery = newQuery.replaceAll("{FIELD"+(i+1)+"}", x);
                 
                       
                       
                        Cell leadLoanCellName = sheet.getCell(0,i);
                        Cell imageNamecellName = sheet.getCell(1,i);
                        String leadLoan = leadLoanCellName.getContents();
                        String imageName = imageNamecellName.getContents();
                       
                        //String query = "select * from DEF.TestTable where col1='{FIELD1}' and col2='{FIELD2}'";
                       
                       
                       
                       
                       
                       
                     

                        PreparedStatement pstmt = null;
                 
                        try{
                              //String sql = "SELECT * FROM CDMS.optimg01";
                              String query = "select * from CDMS.optimg01 where col1='ghi' and col2='klm'";
                              pstmt = connCdms.prepareStatement(query);
                              ResultSet rs1 = pstmt.executeQuery();


                             
                              // System.out.println("List of values");
                                while (rs1.next()){
                                String rec1 = rs1.getString(1);
                       
                                System.out.println("rec1 is"+rec1);
                                           pstmt.close();
                                rs1.close();
                                }

                        }catch(Exception e){
                 
                              e.printStackTrace();
                        }finally{
                 
                        }
                       
                       
                       
           
                  }
                  //list.add(newQuery);
                 
            }
           
           
      //      return list;
      }
           
       
     
      public static void main(String[] args) throws IOException, BiffException {
            CopyOfReadExcelEnhanceDBConnPropFileFinal test = new CopyOfReadExcelEnhanceDBConnPropFileFinal();
            test.setInputFile("c:/gp/Report.xls");
            test.read();


      }
     
}

                              

when I run the program getting error like


rows4
columns2
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)
rec32 isnull
java.sql.SQLException: Closed Resultset: next
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:99)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:174)

I am getting error as follows. Please advise
0
 
gudii9Author Commented:
I have about 50 columns in the table and lot of rows


   while (rs1.next()){
                                String rec1 = rs1.getString(1);
                       
                                System.out.println("rec1 is"+rec1);
                                           pstmt.close();
                                rs1.close();
                                }




If i have to write  like above

I have  to create 50 statements like

String rec1 = rs1.getString(1);
System.out.println("rec1 is"+rec1);
                                         
then again

String rec2 = rs1.getString(2);                        
System.out.println("rec1 is"+rec2);
                                        --------



i am repeating 50 times which seems inefficient. Please advise how can I loop and read all the values in one stroke.
0
 
gatorvipCommented:
You closed the objects in the wrong loop. Try this:

try
{
	//String sql = "SELECT * FROM CDMS.optimg01";
	String query = "select * from CDMS.optimg01 where col1='ghi' and col2='klm'";
	pstmt = connCdms.prepareStatement(query);
	ResultSet rs1 = pstmt.executeQuery();

	// System.out.println("List of values");
	while (rs1.next())
	{
		String rec1 = rs1.getString(1);

		System.out.println("rec1 is"+rec1);
	}
	
	pstmt.close();
	rs1.close();
	

}
catch(Exception e)
{
	e.printStackTrace();
}
finally
{

}

Open in new window

0
 
Sanjeev LabhCommented:
Can you tell me what is the value of the maximum_open_cursor in init parameter of your oracle database? Ideally number of concurrent cursors cannot exceed this otherwise you will get the error of maximum open cursors. Check your loop if this is opening more cursors than the value set then you might need to change either the init param or reduce the loop.
0
 
gudii9Author Commented:
>>>Check your loop if this is opening more cursors

where can I see and find this information in my program. Please advise
0
 
gatorvipCommented:
Before you start attempting to change database parameters, make sure your code is correct. Even moreso when  a different project is using the same database, the difference being that the code doesn't use a loop.

Assuming the OP posted his full code, there are no mysterious cursors being opened. You just need to close your objects in a timely fashion, i.e., open resultset, do some work with it, close it. When you do it in a straight shot, the finally() part takes care of closing everything. Inside the current loop you have no such thing.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now