1

I've written a code that will be querying data from Excel using odbc bridge. Now since in java8 there is no more support to odbc, I'm searching an alternative of the same. I've nearly 32 different programs of suce. i.e. querying from Excel Database. Below is a sample program.

private void getTheDetailedDataForRca(String userName, XSSFWorkbook workbook, XSSFSheet sheet) {
        // System.out.println("ph2");
        try {
            DecimalFormat df2 = new DecimalFormat("#.##");
            HashMap<String, HashMap<String, Double>> myArray;
            myArray = new HashMap<String, HashMap<String, Double>>();
            String user = "%" + userName + "%";

            String dburl = path.getDBUrl();

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            myConn = DriverManager.getConnection(dburl);
            String queryString = "select Error As Err, Sum(TotalErrors) as Errors from [Quality Sheet$] where Associate like ? group By Error";
            PreparedStatement ps = myConn.prepareStatement(queryString);
            ps.setString(1, user);
            ResultSet rs = ps.executeQuery();
            myArray.put(userName, new HashMap<String, Double>());

            while (rs.next()) {
                double inputValue = rs.getDouble("Errors");
                String fin = df2.format(inputValue);
                myArray.get(userName).put(rs.getString("Err"), Double.parseDouble(fin));
            }
            ps.close();
            myConn.close();
            printMapOne(myArray, userName, workbook, sheet);
        } catch (Exception e) {
            System.out.println(e + "\t" + "in finals sheet B2");
        }
    }

And my dburl is

public String getDBUrl() {
    return "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\Users\\" + getSystemId
            + "\\Desktop\\Quality Sheets\\quality_template.xlsx;";
}

I checked it online and to my surprise I found nothing apart from suggesting to use POI. I tried with UCanAccess, but this supports only Access database. Can someone please let me know any alternate for odbc, so that I need not code the entire thing.

I've tried the solution given at Removal of JDBC ODBC bridge in java 8

I got the message as below in cmd.

C:\Users\u0138039\Desktop\sun>jar -cvf jdbc.jar sun
sun : no such file or directory
added manifest

But a jar is created in folder.

I pasted the same jdbc driver in C:\Program Files (x86)\Java\jdk1.8.0_101\jre\lib and copied jdbcodbc.dll to C:\Program Files (x86)\Java\jre1.8.0_101\bin, restarted and to my surprise I get the same java.sql.SQLException: No suitable driver found for jdbc:odbc Exception.

Thanks

Community
  • 1
  • 1
user2423959
  • 836
  • 1
  • 13
  • 27
  • See answer explaining [How to enable JDBC-ODBC bridge for JDK 8](http://stackoverflow.com/a/36875001/5221149) – Andreas Jul 27 '16 at 15:21
  • Hi @Andreas, I tried this but unfortunately this happens not to work. Can you please take off the duplicate flag, I've searched this and then I've posted the question as I didn't find any other option :-(. – user2423959 Jul 27 '16 at 15:36
  • @Andreas, Also when I ran the command I got this message `C:\Users\u0138039\Desktop\sun>jar -cvf jdbc.jar sun sun : no such file or directory added manifest`. Can you please clarify if this is causing the error? – user2423959 Jul 27 '16 at 15:37
  • Do you have a `sun` folder inside the `sun` folder? If not, then maybe you need to be in the parent folder when running that command, in order to find the `sun` folder in the current folder. – Andreas Jul 27 '16 at 17:47

0 Answers0