If you can confirm it works for you, I'll put it in the next MLSQL release. I think the fix should be general purpose and backward compatible.
-jh- On Feb 24, 2010, at 10:20 AM, Chetan Patel wrote: > Jason, > > The code change that you are suggesting is for our version of MLSQL or for > community version of MLSQL? > > Thanks > -Chetan > From: [email protected] > Subject: Re: [MarkLogic Dev General] MLSQL update/delete operation > Date: Tue, 23 Feb 2010 14:09:22 -0800 > To: [email protected] > > I've been looking at the MLSQL code to see how to fix this for the general > case. Unfortunately MLSQL doesn't require the user of MLSQL specify whether > the SQL update call is an INSERT or an UPDATE, so I can't easily have the > library follow different code paths for the two. > > Then I wondered why others haven't been hitting the issue. If you look at > the JDBC docs it does say this on the getGeneratedKeys() call: > > Retrieves any auto-generated keys created as a result of executing this > Statement object. If this Statement object did not generate any keys, an > empty ResultSet object is returned. > > So I think a well behaved database should be able to return an empty > ResultSet instead of a SQLException in the event there are no keys. I expect > MySQL doesn't have this problem as MLSQL has been used widely against MySQL. > > Perhaps this code change will work for you: > > else if (type.equalsIgnoreCase("update")) { > try { > int count = stmt.executeUpdate(); > addWarnings(meta, stmt.getWarnings()); > addUpdateCount(meta, count); > try { > addGeneratedKeys(meta, stmt.getGeneratedKeys()); > } > catch (SQLException e) { > // Generated keys are available on INSERT calls but not UPDATE > calls > // So catch and eat the exception that Oracle (and maybe others) > will throw > } > } > catch (SQLException e) { > addExceptions(meta, e); > } > } > > -jh- > > On Feb 23, 2010, at 7:34 AM, Chetan Patel wrote: > > You are right Jason. > > Looks like the only option is to comment following two lines of the code. In > JDBC3.0, the retrieval of auto-generated key feature is supported ONLY when > INSERT statements are processed. In MLSQL, INSERT statement is processed by > default else block (stmt.execute()) of the code which already retrieves the > auto-generated keys. > > stmt.getGeneratedKeys(); > addGeneratedKeys(meta, stmt.getGeneratedKeys()); > > Thanks for your help. > > -Chetan > > From: [email protected] > Subject: Re: [MarkLogic Dev General] MLSQL update/delete operation > Date: Mon, 22 Feb 2010 14:08:59 -0800 > To: [email protected] > > OK, then I'm going to guess the issue is with catching the generated keys. > Here's what MLSQL is doing on the servlet side: > > else if (type.equalsIgnoreCase("update")) { > try { > int count = stmt.executeUpdate(); > addWarnings(meta, stmt.getWarnings()); > addUpdateCount(meta, count); > stmt.getGeneratedKeys(); > addGeneratedKeys(meta, stmt.getGeneratedKeys()); > } > catch (SQLException e) { > addExceptions(meta, e); > } > } > > Earlier the Java does this: > > // Note this call depends on JDBC 3.0 (accompanying Java 1.4). > // The call without the 2nd argument would work on earlier JVMs, > // you just won't catch any generated keys. > stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); > > I bet it will work if you remove the fetch for the generated keys from the > MLSQL code. It'll probably also fail if you try getting the generated keys > in your standalone code. Ideally you can figure out why Oracle isn't > returning the generated keys (or if you have to just remove the feature from > the library to work around the issue). Curious what you come up with... > > -jh- > > On Feb 22, 2010, at 1:24 PM, Chetan Patel wrote: > > Hi Jason, > > It does the update/delete operation but it also throws an exception (17090 > operation not allowed). > > I tried with standalone program and it is not throwing any exception (with > same jar). > > Thanks > -Chetan > > From: [email protected] > Subject: Re: [MarkLogic Dev General] MLSQL update/delete operation > Date: Mon, 22 Feb 2010 09:13:08 -0800 > To: [email protected] > > Maybe this explains it? > > http://forum.springsource.org/showthread.php?p=283913 > > -jh- > > On Feb 22, 2010, at 8:55 AM, Chetan Patel wrote: > > Hello, > > I am getting following error when I try update or delete operation (oracle 10 > database) using MLSQL (insert and select works fine). Does anyone know how to > solve this problem? > > import module namespace sql = "http://xqdev.com/sql"; at "sql.xqy" > sql:executeUpdate("update test set title = > 'xxxxx'","http://localhost:8080/mlsql/mlsql";, ()) > > <sql:result xmlns:sql="http://xqdev.com/sql";> > <sql:meta> > <sql:rows-affected>1</sql:rows-affected> > <sql:exceptions> > <sql:exception type="java.sql.SQLException"> > <sql:reason>operation not allowed</sql:reason> > <sql:sql-state /> > <sql:vendor-code>17090</sql:vendor-code> > </sql:exception> > </sql:exceptions> > </sql:meta> > </sql:result> > > Thanks > > -Chetan Patel > Elsevier > > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up > now._______________________________________________ > General mailing list > [email protected] > http://xqzone.com/mailman/listinfo/general > > > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up > now._______________________________________________ > General mailing list > [email protected] > http://xqzone.com/mailman/listinfo/general > > > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up > now._______________________________________________ > General mailing list > [email protected] > http://xqzone.com/mailman/listinfo/general > > > Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up > now._______________________________________________ > General mailing list > [email protected] > http://xqzone.com/mailman/listinfo/general
_______________________________________________ General mailing list [email protected] http://xqzone.com/mailman/listinfo/general
