Thread: Prepared Statement executing but not working. HEADACHE

Hey guys,

I cant program my project because this stupid issue has risen out of no where. For some reason the preparedstatement executes but no changes are made in the db. I am using this as a part of a web app i am developing in eclipse but i decided to put this into main and debug this as it wasnt working but its still causing a headache after countless hours on end. Theres no problem with the db connection as theres a similar query that working fine. I have just about tried everything, any help would be appreciated.

 
public class Main {
		 static PreparedStatement preparedStatementinsert ;
 
 
	public static void main(String[] args) {
 
		try {
		    Class.forName(DBConstants.DB_DRIVER);
		} catch (ClassNotFoundException cfe) {		
			System.out.println("The JDBC drive loading failed.");
			cfe.printStackTrace();
		}
		try {	
			dbCon = DriverManager.getConnection(DBConstants.DB_NAME);
		} catch (SQLException se) {
			SQLExcept.handleException(se);	
		}
 
		dbCon=ConnectionManager.getConnection();		
	String MyQuery= "INSERT INTO LoggedInUsers(Email,SessionID) VALUES(?,?)";
	String email = "sdas";
	String Session = "adsadsads";
 
	try{
 
       preparedStatementinsert= dbCon.prepareStatement(MyQuery);
 
		preparedStatementinsert.setString(1, "asdddddddddddddddddddddddddemail");
		preparedStatementinsert.setString(2, "Sessioasdddddddddddddddddddddn");
 
		int click=preparedStatementinsert.executeUpdate();
		System.out.println(click);
 
		}catch(SQLException e){
			//this.success=false;
			SQLExcept.handleException(e);
			e.printStackTrace();
		}	
 
	}
	}

Variable click keeps on returning 1 every time even if theres a tuple in the relation. Also there has been a few instances(3 out of about a 1000) that the tuple has miraculously appeared in the relation. Doesn't make sense. Also , there are no problems with committing as i have never changed it and its always been at default which is true.

Originally Posted by copeg

First, your code does not compile - dbCon is not defined: posting an SSCCE always helps break the problem down. Second, dbCon is assigned twice - any reason to assign it twice, and are you sure both assignments point to the same database instance you want it to point to?

The code does compile, db is is defined in a seperate class. The class is call connection manager and heres the code for that.

 
public class ConnectionManager {
	static Connection dbCon;
 
	public static Connection getConnection()  {
 
		try {
		    Class.forName(DBConstants.DB_DRIVER);
		} catch (ClassNotFoundException cfe) {		
			System.out.println("The JDBC drive loading failed.");
			cfe.printStackTrace();
		}
		try {	
			dbCon = DriverManager.getConnection(DBConstants.DB_NAME);
	//		System.out.print("asasdads");
		} catch (SQLException se) {
			SQLExcept.handleException(se);	
		}
 
		return dbCon;
 
	}
}

The db constants class just contain the driver name and database file location . The code compiles and runs as i said before otherwise i would be getting a database error. I have simplified the code and it still runs and still does nothing . It is as follows :

Originally Posted by copeg

Not for us it doesn't...hence the request for an SSCCE (as well as possibly including the type of database you are working with), with which we can at least see the full code and try and reproduce the behavior. Otherwise, we are left looking at code that looks like it technically should do what you wish (I would recommend closing the PreparedStatement and the Connection - unless they are pooled), but evidently does not.

GUYSSSSS I FIXEDDDDD ITTTTTTT. Apparently if you dont close the dbCon it can cause all sorts of random problems. I have now started closing my db by doing

 
if (this.dbCon != null) {
	        try {
	            this.dbCon.close();
	        } catch (SQLException e) {}
	    }

and its working every time. Any insight to why this might be?

Unless you close the resources data won't be flushed and hence you were not seeing the changes. Its always best to use finally block if you are prior to JDK 7 or use try with resources statement to cleanup resources instead of a separate try like you have done.

Hey guys,

I need some help on fixing my db filepath so i dont have to change it everytime i go onto a new computer. But i am having trouble doing so in eclipse.

My file tree is like this

 
Workspace->
            .metadata
             RemoteSystemTempFiles
             Servers
             Myproject->
                                .settings
                                 build
                                 src ->
                                         package1
                                         package2->
                                                         All my main .java files
                                 WebContent ->
                                         Databases ->
                                                                         MyDB.mdb
                                         Design
                                         Meta-INF
                                         WEB-INF
                                         All my various .jsps
                                 .classpath
                                 .project
 
I want to know how i can link my MyDB.mdb file in my DB_Name constant, ""jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\Users\\sup123\\Workspace\\Myproject\\WebContent\\Databases\\MyDB.mdb" ;" such that i dont have to change it everytime i switch computers. I am using an absolute path atm (C:\\workspace etc)
*

My project starts from login.jsp which is in the webcontent folder so someone told me that all the files you are referencing to have to be relative to that. Meaning if im specifying my DB file in my DBConstant.java file , it still has to be relative where my login.jsp is in.

Yes thats right you need to give a relative path.

ya that was my question but you didnt answer it. How do i give it a relative path?

Relative path would be Databases/MyDB.mdb.

doesnt work i tried the following three,

	public static final String DB_NAME = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=\\Databases\\MyDB.mdb" ;
	public static final String DB_NAME = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=.\\Databases\\MyDB.mdb" ;
	public static final String DB_NAME = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=Databases\\MyDB.mdb" ;

Firstly print the current directory to better understand and give relative path in your app.

Heres a picture of my directory

http://imgur.com/33i50on

Any Ideas for the relative path