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
_______________________________________________
General mailing list
[email protected]
http://xqzone.com/mailman/listinfo/general
