dcsimg
Results 1 to 13 of 13

Thread: [RESOLVED] SQL General Error

  1. #1

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Resolved [RESOLVED] SQL General Error

    Hi guys,

    I've written a class that creates a connection to an MS Access database and I've included several methods that will carry out SQL update, insert functions etc.

    The problem is that whenever I call one of these methods from an external class, a General Error is thrown. The class structure is basically as follows -

    A constructor method that establishes a connection to the Access database. This method is functioning normally. I've tested the connection by carrying out update/select statements in this method and they've been implemented without any problems. I've created a global Connection variable that is initialised in this method so that the other methods can use the it (as opposed to having each method set up a connection and carry out its tasks).

    An update method that takes a String variable as a parameter and uses this as part of a SQL Update command

    An insert method that takes a String variable as a parameter and uses this as part of a SQL INSERT command

    As I said, the problem seems to be that any statements/commands called on the Connection outside of the constructor method (where the connection is established) are resulting in a General Error.

    I'd appreciate any help that anyone here could provide. If you need me to post any code, no problem.

    Thanks
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

  2. #2
    Kitten CornedBee's Avatar
    Join Date
    Aug 2001
    Location
    In a microchip!
    Posts
    11,594

    Re: SQL General Error

    What's a "general error"? Is it a Java exception or something that Access generates?

    Does the DB provide any further information?

    Are you absolutely sure you assign the connection to the correct variable? Do you accidently close the connection somewhere?

    Can you provide the minimal amount of code that still shows the problem? (Perhaps the class with only the constructor and one method that doesn't work.)
    All the buzzt
    CornedBee

    "Writing specifications is like writing a novel. Writing code is like writing poetry."
    - Anonymous, published by Raymond Chen

    Don't PM me with your problems, I scan most of the forums daily. If you do PM me, I will not answer your question.

  3. #3

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Re: SQL General Error

    Quote Originally Posted by CornedBee
    What's a "general error"? Is it a Java exception or something that Access generates?
    I'm not sure - I've never encountered it before. It seems to be a Java SQL problem and not something that Access generates, but I can't be sure.

    Quote Originally Posted by CornedBee
    Does the DB provide any further information?

    Are you absolutely sure you assign the connection to the correct variable? Do you accidently close the connection somewhere?
    No, it's not telling me much more than that error at the moment, sorry.

    I've checked and yes I'm assigning the connection to the correct variable.

    The code that's causing the problems -

    Code:
    import java.sql.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class DatabaseConnection {
        
        public String databaselocation = "";
        public String database = "";
        public Connection con = null;
        
        /** The JDBC driver. */
        private static final String DB_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    
        
        /** Creates a new instance of DatabaseConnection */
        public DatabaseConnection(String location) {
            
            location = location.trim();
            databaselocation = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + location + ";DriverID=22;READONLY=false}";
            
        }
        
        public void createConnection() {
    
            /*This is the first method that's called in the contstructor method of the other class*/
    
            try {
            
                Class.forName(DB_DRIVER);
                con = DriverManager.getConnection(databaselocation, "", "");
    
                    //SQL commands (ie: statements) will work here
    
            } catch (Exception e) {
                
                System.out.println("" + e.getMessage());
                e.printStackTrace();
                
            }
                
        }
        
        public void writeToTable(String company, String fund) {
            
            try {
    
                //The fund number will need to be converted to Integer format
                int fundno = Integer.valueOf(fund).intValue();
       
                    /*The sqlstr String below was where the problems started.  Initially, I thought it was something to do with the 'company' and 'fundno' variables being passed into the String....*/
                    //sqlstr = "INSERT INTO [" + company + "] VALUES (" + fundno + ")";
    
                    /*... So I hardcoded this String for test purposes.  The table 555 exists and has only one field (Number format)*/
                    sqlstr = "INSERT INTO 555 VALUES (2)";
                    s = con.createStatement();
                    //s.execute("INSERT INTO 555 VALUES (2)");
    
                    /*According to NetBeans, the General Error is occurring on this line*/
                    s.execute(sqlstr);
                
        
                //s.close();
         
             } catch (Exception e) {
                
                System.out.println("" + e.getMessage());
                e.printStackTrace();
                
            }
            
        }
    
    }
    EDIT -

    Acutally, the compiler is giving me the following stack trace of the problem -

    Code:
    java.sql.SQLException: General error
            at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
            at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
            at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
            at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
            at TrailerAudit.DatabaseConnection.writeToTable(DatabaseConnection.java:84)
    Last edited by MethadoneBoy; Jan 24th, 2007 at 05:04 AM.
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

  4. #4
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    You're not opening the SQL connection anywhere?

  5. #5
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    Also, inferring your tables etc... you have a table per company?
    Would it not make more sense - and a lot easier to do analysis on the data - to have a single table, but have a field with the company name in it?

  6. #6

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Re: SQL General Error

    Quote Originally Posted by plenderj
    You're not opening the SQL connection anywhere?
    My mistake, that writeToTable method should look like -

    Code:
    public void writeToTable(String company, String fund) {
            
            try {
    
                //The fund number will need to be converted to Integer format
                int fundno = Integer.valueOf(fund).intValue();
                
                Class.forName(DB_DRIVER);
                con = DriverManager.getConnection(databaselocation, "", "");
    
                String sqlstr = "SELECT * FROM [Table Names] WHERE [Tab_Name] = '" + company + "';";
                Statement s = con.createStatement();
                s.execute(sqlstr);
                
                //s = con.createStatement();
                
                ResultSet rs = s.getResultSet();
                
                if ( rs != null ) { // If table exitsts
                    
                    sqlstr = "INSERT INTO [" + company + "] VALUES (" + fundno + ")";
                    //sqlstr = "INSERT INTO 555 VALUES (2)";
                    //pl (sqlstr);
                    
                    s = con.createStatement();
                    s.executeUpdate(sqlstr);
                
                } else {
                    
                    company = "Test1";
                    
                    sqlstr = "INSERT INTO [" + company + "] VALUES (" + fundno + ")";
                    //sqlstr = "INSERT INTO 555 VALUES (2)";
                    //pl (sqlstr);
                    
                    s = con.createStatement();
                    s.executeUpdate(sqlstr);
                    
                }
                
                s.close();
                con.close();
                
             } catch (Exception e) {
                
                System.out.println("" + e.getMessage());
                e.printStackTrace();
                
            }
            
        }
    Quote Originally Posted by plenderj
    Also, inferring your tables etc... you have a table per company?
    Would it not make more sense - and a lot easier to do analysis on the data - to have a single table, but have a field with the company name in it?
    It would but it's an old database and I've not got around to restructuring it yet. Frankly, I'm more concerned with the fact that a rudimentary Java operation isn't working and I can't explain why. Any ideas?
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

  7. #7
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    Do you need to call the close method on your returned Statement object after using it or something?

  8. #8

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Re: SQL General Error

    I've just included it for cleanliness' sake. I've tried the code without it and no luck.
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

  9. #9
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    Okay, what if you don't execute the first query at all? I'm thinking that perhaps - and bear in mind my knowledge of JDBC is zilch - some object is staying open or in a read state or something

  10. #10

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Re: SQL General Error

    Tried it - same deal
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

  11. #11
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    What about calling just execute() instead of executeUpdate() ?

  12. #12
    Banned plenderj's Avatar
    Join Date
    Jan 2001
    Location
    Dublin, Ireland
    Posts
    10,359

    Re: SQL General Error

    Doesn't directly answer the question, but I thought I'd type up my own solution from scratch

    Code:
    import java.sql.*;
    
    class Test {
    	public static void main(String[] args) {
    
    		CDetails myCompany = new CDetails( "plenderj.com", 555 );
    		DBAccess myDBAccess = new DBAccess();
    		
    		int intRowsInserted = -1;
    		intRowsInserted = myDBAccess.InsertRow( myCompany );
    		System.out.println( intRowsInserted );		
    	}
    	
    }
    
    class DBAccess {
    
    	private Connection _conConnection;
    	private ResultSet _rsResultSet;
    	private final String _strConnString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=Database1.mdb;DriverID=22;READONLY=false}";
    	
    	void DBAccess() {
    		
    	}
    
    	public int InsertRow( CDetails CompanyDetails ) {
    		int intRetVal = -1;
    		try {
    			_conConnection = DriverManager.getConnection(_strConnString, "", "");
    			String strCommand = "";
    			strCommand += "INSERT INTO " + CompanyDetails.ID;
    			strCommand += " (Field1, Field2)";
    			strCommand += " VALUES ";
    			strCommand += " ('" + CompanyDetails.Name + "', ";
    			strCommand += " '" + CompanyDetails.Name + "')";
    			
    			intRetVal = _ExecuteStatement( strCommand );
    			
    		} catch (java.sql.SQLException ex) {
    			_PresentError(ex);
    		}
    
    		return intRetVal;
    	}
    
    	private int _ExecuteStatement( String strStatement ) {
    		int intRetVal = -1;
    		try {
    			Statement _myStatement = _conConnection.createStatement();
    			intRetVal = _myStatement.executeUpdate( strStatement );
    		} catch (java.sql.SQLException ex) {
    			_PresentError(ex);
    		}
    		return intRetVal;
    	}
    
    	private void _PresentError( Exception ex ) {
    		_PresentError( ex, "" );
    	}
    	private void _PresentError( Exception ex , String strWhere ) {		
    		String strOutput = "";
    		strOutput = " ** Something went wrong : ";
    		strOutput += strWhere;
    		strOutput += ex.toString();
    		System.out.println( strOutput );
    	}
    }
    
    
    class CDetails {
    	public String Name;
    	public int ID;
    
    	CDetails() { 
    		
    	}
    	CDetails( String strName, int intID ) {
    		Name = strName;
    		ID = intID;
    	}
    }

  13. #13

    Thread Starter
    Addicted Member MethadoneBoy's Avatar
    Join Date
    Oct 2001
    Location
    Preferably somewhere between Keira Knightley and Diane Kruger but I'm not fussy
    Posts
    180

    Re: SQL General Error

    Thanks ;-)

    It's been resolved, to a degree. Basically I modified the database (as you suggested) so that all the information was read into one table. This is working perfectly but I can't account for the error at all.
    "'Oh, hello Mr. Crick! What do you think of Jeffrey Archer?' Clip-clip-clip! Oh, come on! Who are you kidding? You wait til I'm mayor, you'll see how tough I am! Christ almighty...."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width