Thank you.
I will test the code change and let you know.
When are you planning to release next MLSQL?
From: [email protected]
Subject: Re: [MarkLogic Dev General] MLSQL update/delete operation
Date: Wed, 24 Feb 2010 10:59:08 -0800
To: [email protected]
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
_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
http://clk.atdmt.com/GBL/go/201469226/direct/01/_______________________________________________
General mailing list
[email protected]
http://xqzone.com/mailman/listinfo/general
