[RESOLVED] SQL General Error

[MethadoneBoy]

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

[CornedBee]

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.)

[MethadoneBoy]

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.

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 -

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 -

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)

[plenderj]

You're not opening the SQL connection anywhere?

[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?

[MethadoneBoy]

You're not opening the SQL connection anywhere?

My mistake, that writeToTable method should look like -

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();
            
        }
        
    }

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?

[plenderj]

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

[MethadoneBoy]

I've just included it for cleanliness' sake. I've tried the code without it and no luck.

[plenderj]

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

[MethadoneBoy]

Tried it - same deal

[plenderj]

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

[plenderj]

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

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;
	}
}

[MethadoneBoy]

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.