Bug 364261

Comment 1 Joseph Vincens 2011-11-21 11:33:43 EST

When ds = new SQLDataSource(string); is used we don't know what database you are using so we can't create a URL for you. You have to set the URL properly for the application you are developing.

Comment 2 Xiao Bin Chen 2011-11-21 21:00:14 EST

(In reply to comment #1)
> When ds = new SQLDataSource(string); is used we don't know what database you
> are using so we can't create a URL for you. You have to set the URL properly
> for the application you are developing.

Hi Joe, 
I think the most important is the tools we used in "Resource Binding" tab of **.egldd. 

when we add a "sql database binding", If you choose a mysql database, we may could know it's a mysql database. So the generated "Connection URL" might could be append "relaxAutoCommit=true"  for mysql. 

Because when I used the 
ads SQLDataSource?{@Resource {bindingkey = "mvclib", propertyFileName = "aproject"}};	
The error will show, Which will confused customer.(Customer used the URL our tools provided,It does not works!!!)


Second, for ds = new SQLDataSource(string).
Could we analysis the string, I don't know if the string has contain some meta data of which database was used?

like"jdbc:mysql://rbdtest1.raleigh.ibm.com:3306" contains "mysql".

Comment 3 Tony Chen 2011-11-23 21:30:36 EST

bottom line is the url given by egldd (if user has not manually modified it) should work by default. It would an issue to let user identify problems like what's described here. 

The runtime/gen and the tool needs to be aligned on what auto commit status is expected. 

Since this may involve some design decision. I'm setting it the future. I also raise the severity to major because it is easily to hit, and difficult to get out. 
The component is set to IDE, but JSGen might be involved as well.

Comment 4 Zhi Zhu 2011-12-12 04:09:29 EST

In our current implementation, Connection object is get from SQLDataSource:
   public Connection getConnection() throws SQLException {
		if (conn == null) {
			try {
				conn = DriverManager.getConnection(connectionUrl, properties);
			} catch (java.sql.SQLException e) {
				throw JavartUtil.makeEglException(e);
			}
		}
		return conn;
	}


   In the above method, the returned Connection object is auto-commited based on JDK document, when running SQL-related part, SQLDataSource.commit() method will be called to commit SQL statements, so it is possible to commit SQL statements more than once

  I suggested to set autoCommit property of returned Connection object to false due to below two points:
  1. SQLDataSource.commit() will be called which will commit SQL statements.
  2. The SQL part probably contains more than one statements that should be executed in one transaction; if autoCommit property of returned Connection is true, they will be executed in different transactions producing unexpected results.
Add:
   conn.setAutoCommit(false);
to SQLDataSource.commit() method 

  public Connection getConnection() throws SQLException {
		if (conn == null) {
			try {
				conn = DriverManager.getConnection(connectionUrl, properties);
				conn.setAutoCommit(false);
			} catch (java.sql.SQLException e) {
				throw JavartUtil.makeEglException(e);
			}
		}
		return conn;
	}

Comment 5 Brian Svihovec 2011-12-16 10:50:10 EST

Changing this to .8 so that we can have a discussion about how this should be handled.

Comment 6 Zhi Zhu 2012-01-05 02:08:29 EST

update for testing search

Comment 7 Joseph Vincens 2012-01-05 16:48:43 EST

Brian and I had a discussion on this.

In comment 4 Zhi suggests that we set AutoCommit to false. Our functionality parallels Java and the java.sql.Connection defaults to autoCommit = true, so we will continue to use the Java default. Auto commit is an application specific issue, some applications rely on auto committing while other group sql statements by turning off auto commit. 

The SQLDataSource object has setAutoCommit so based on their application needs the developer can change the state of autoCommit. 

As far as having the tooling add relaxAutoCommit=true when we create the egldd entry. Take the existing eclipse/java defaults, no relaxAutoCommit and auto commit = true. If the user codes a commit then they expect an exception. If we add the relax they won't get an exception.

So keeping to the thought of paralleling Java functionality. The real problem is when the rununit ends EGL issues a connection.commit which causes the exception. The Java doc for Connection.commit says "This method should be used only when auto-commit mode has been disabled." So I have change the SQLDataSource to only issue a commit/rollback if autoCommit == false.

Comment 8 Yu Hao 2012-01-12 01:12:29 EST

Verified in 201201112157