1 2 Previous Next 17 Replies Latest reply: Aug 24, 2008 2:01 AM by 843785 RSS

    Don't know why I get this SQLException.

    843785
      Hi all.

      I am working on a small project involving database connectivity. The code below is supposed to create a connection to a "Finances" database:
      /* THE DatabaseConnection CLASS 
      * BY MiddleChild 
      * 2008/08/22 */*
      
      import java.io.*;
      import java.sql.*;
      import javax.swing.*;
      
      public class DatabaseConnection
      {
      private String path = "jdbc:odbc:DRIVER={Microsoft Access Driver(*.mdb)};DBQ ="
      + System.getProperty ("user.dir") + "\\";
      
      Connection conn;
      
      public static void main (String[]args)
      {
      new DatabaseConnection();
      }
      
      DatabaseConnection()
      {
      
      
      // LOAD THE DRIVER
      
      try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}
      
      catch (ClassNotFoundException c)
      {
      JOptionPane.showMessageDialog(null,"Unable to load database driver!","ERROR",JOptionPane.ERROR_MESSAGE);
      }
      
      // ESTABLISH CONNECTION
      
      try{conn = DriverManager.getConnection(path + "Finances");}
      
      catch(SQLException s)
      {
      JOptionPane.showMessageDialog(null,"Unable to establish connection!","ERROR",JOptionPane.ERROR_MESSAGE);
      System.out.print(s);
      }
      }
      
      
      ResultSet queryTbl (String givenStmt) throws SQLException
      {
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(givenStmt);
      return rs;
      }
      
      
      }
      I get the following SQLException:

      java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
      Process completed.


      What is wrong with this code?
        • 1. Re: Don't know why I get this SQLException.
          796254
          print out the value for "user.dir". i'll bet you're surprised when you see it, because it's not what you assumed it should be.

          bad code. nice try, but don't do it this way.

          mingling UI code like JOptionPane with other things like database code is a very bad idea. this connection class could be useful without Swing.

          I wouldn't write it this way, anyway. your exceptions are useless. print the stack trace. don't hard wire connection parameters. returning a result set like that is terrible. load it into an object or data structure and close it. you don't close any resources properly. you'll regret it.

          I think a class like this will be a lot more useful:
          package database.persistence;
          
          import java.sql.Connection;
          import java.sql.DriverManager;
          import java.sql.ResultSet;
          import java.sql.SQLException;
          import java.sql.Statement;
          
          /**
           * DatabaseUtils
           * User: Michael
           * Date: Apr 13, 2008
           * Time: 7:31:22 PM
           */
          public class DatabaseUtils
          {
             public static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
             {
                Class.forName(driver);
          
                return DriverManager.getConnection(url, username, password);
             }
          
             public static void close(Connection connection)
             {
                try
                {
                   if (connection != null)
                   {
                      connection.close();
                   }
                }
                catch (SQLException e)
                {
                   e.printStackTrace();
                }
             }
          
             public static void close(Statement statement)
             {
                try
                {
                   if (statement != null)
                   {
                      statement.close();
                   }
                }
                catch (SQLException e)
                {
                   e.printStackTrace();
                }
             }
          
             public static void close(ResultSet resultSet)
             {
                try
                {
                   if (resultSet != null)
                   {
                      resultSet.close();
                   }
                }
                catch (SQLException e)
                {
                   e.printStackTrace();
                }
             }
          
             public static void rollback(Connection connection)
             {
                try
                {
                   if (connection != null)
                   {
                      connection.rollback();
                   }
                }
                catch (SQLException e)
                {
                   e.printStackTrace();
                }
             }
          }
          %
          • 2. Re: Don't know why I get this SQLException.
            843785
            You couldn't have made it easier to understand could you?

            What I mean to say is that the code I posted kinda came straight from my school textbook, and now it turns out that it is all rubbish. I will now attempt to make sense of your code and help. For the next hour. But thank you.
            • 3. Re: Don't know why I get this SQLException.
              843785
              OK, I did run:

              System.out.println(System.getProperty("user.dir"));

              and the result was

              C:\Documents and Settings\de Villiers\Desktop\IT Taak\Tests


              This is where both my Finances.mdb and DatabaseConnection.java files are situated.
              • 4. Re: Don't know why I get this SQLException.
                843785
                MiddleChild wrote:
                You couldn't have made it easier to understand could you?
                The URL (path) is wrong. And it may be wrong in multiple places as duffymo pointed out.

                The advice to fix the code is good in that it's better to learn best practices right away, rather than trying to break bad habits later. But to fix your current code. Print out the URL (path) exactly of what you have. You may well find you are pointing to the wrong directory. Also the path should be ending with .mdb so "Finances" by itself is also wrong. It should at least be "Finances.mdb"

                Anyway take a look at your path and see what your problem is. Assuming for example that Finances.mdb is in your working directory then something like this would work.
                "jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb);DBQ=Finances.mdb"
                • 5. Re: Don't know why I get this SQLException.
                  843785
                  I used your advice cotton.m.

                  Now I get this:

                  #
                  # An unexpected error has been detected by Java Runtime Environment:
                  #
                  # EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x0063006e, pid=3960, tid=232
                  #
                  # Java VM: Java HotSpot(TM) Client VM (1.6.0-b105 mixed mode, sharing)
                  # Problematic frame:
                  # C 0x0063006e
                  #
                  # An error report file with more information is saved as hs_err_pid3960.log
                  #
                  # If you would like to submit a bug report, please visit:
                  # http://java.sun.com/webapps/bugreport/crash.jsp
                  #

                  Process completed.


                  I guess this isn't what is supposed to happen?
                  • 6. Re: Don't know why I get this SQLException.
                    843785
                    MiddleChild wrote:
                    I used your advice cotton.m.

                    Now I get this:

                    #
                    # An unexpected error has been detected by Java Runtime Environment:
                    #
                    # EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x0063006e, pid=3960, tid=232
                    #
                    # Java VM: Java HotSpot(TM) Client VM (1.6.0-b105 mixed mode, sharing)
                    # Problematic frame:
                    # C 0x0063006e
                    #
                    # An error report file with more information is saved as hs_err_pid3960.log
                    #
                    # If you would like to submit a bug report, please visit:
                    # http://java.sun.com/webapps/bugreport/crash.jsp
                    #

                    Process completed.


                    I guess this isn't what is supposed to happen?
                    Not really no. That's a native bug of some sort. Do you know what line it happened on in your code?
                    • 7. Re: Don't know why I get this SQLException.
                      843785
                      That's the output I got when I used the path you suggested.

                      How do I find out what line it happened on?
                      • 8. Re: Don't know why I get this SQLException.
                        843785
                        MiddleChild wrote:
                        That's the output I got when I used the path you suggested.

                        How do I find out what line it happened on?
                        Add some System.out.println statements and trace where you are in your code.

                        To be honest this will help in determining a bit if you are dead in the water or simply going to have some inconveinences but this is a bug you aren't going to be able to "fix". Perhaps workaround, but not fix. And it's not the path that's the issue here. Some bug in the ODBC driver or the bridge driver is to blame.

                        In this light you might want to give consideration to installing a real database (aka not Access) for which you can get a type 4 (pure Java) driver for. Something free like MySQL or postgres. Then you won't have bugs in native code because there is no native code.
                        • 9. Re: Don't know why I get this SQLException.
                          843785
                          This is the line that caused all the trouble.

                          conn = DriverManager.getConnection(path + "Finances");

                          Is there really no way to fix or work around it without drastic measures.
                          Note: I am posting in New To Java forum.
                          • 10. Re: Don't know why I get this SQLException.
                            843785
                            MiddleChild wrote:
                            This is the line that caused all the trouble.

                            conn = DriverManager.getConnection(path + "Finances");

                            Is there really no way to fix or work around it without drastic measures.
                            Nope.

                            If connecting is causing the runtime to abort you're dead in the water.
                            Note: I am posting in New To Java forum.
                            Yes I see that. I don't know what you want me to take from that.

                            I wouldn't recommend JDBC to anyone as a good way to learn Java.
                            • 11. Re: Don't know why I get this SQLException.
                              843785
                              Damn, I have a confession to make. This code might be for only a small personal project, but I was going to use it to figure out bigger things for a way bigger project:

                              my annual school IT project. Due Tuesday. S.O.S.

                              All my other java files that make use of DBC are having the same problem. They sure as hell didn't do this before.

                              Could it be a curse? I did remove norton from my pc and did a decompilation. Could it be taking revenge?
                              • 12. Re: Don't know why I get this SQLException.
                                843785
                                They censor the word dam*n. Wow.
                                • 13. Re: Don't know why I get this SQLException.
                                  843785
                                  MiddleChild wrote:
                                  Damn, I have a confession to make. This code might be for only a small personal project, but I was going to use it to figure out bigger things for a way bigger project:

                                  my annual school IT project. Due Tuesday. S.O.S.

                                  All my other java files that make use of DBC are having the same problem. They sure as hell didn't do this before.

                                  Could it be a curse? I did remove norton from my pc and did a decompilation. Could it be taking revenge?
                                  You can try reinstalling your Java runtime. That's about all I can suggest. Possibly the bug is in the ODBC bridge driver and a re-install might fix it.

                                  Sorry but there isn't much else you can do. I sometimes (rarely) use the bridge driver to connect to access and I have had issues like yours sometimes. Usually when closing the connection. I don't know why. But there's nothing you can do to fix the driver or ODBC.

                                  If you installed another database and changed drivers though, this would take some work but your code should be almost entirely unaffected. That's what JDBC does for you. After getting the connection the rest of the code remains the same.
                                  • 14. Re: Don't know why I get this SQLException.
                                    843785
                                    I just downloaded OpenOffice.org today. Would it make any difference if I used OpenOffice.org Base instead of Access? If yes, how do you establish the connection(or how does it differ in code). Links will be appreciated.
                                    1 2 Previous Next