ERROR : FOR UPDATE cannot be specified on a READ ONLY ...

Hi All,
I am using SQL Server 2000 + JDeveloper 10g with ADF.
When i try to make changes to data and do COMMIT, I receive ERROR, I
have tried both JDBC Driver from Microsoft and jTDS, both FAILS.
Here is the error :
With Microsoft JDBC Driver :
(oracle.jbo.DMLException) JBO-26080: Error while selecting entity for
Departments
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer] FOR UPDATE cannot be specified on a READ ONLY cursor.
With jTDS JDBC Driver :
(oracle.jbo.DMLException) JBO-26080: Error while selecting entity for
Departments
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) Line 1: FOR UPDATE clause allowed only for
DECLARE CURSOR.
Pls any body help...
Thank you,
Krist
Quite simple, really. SQL Server does not support FOR UPDATE; at least
not in the way that Oracle does. It's not standard SQL.
Alin,
The jTDS Project.
|||Hi ,
Thank you for your reply, it works now if I use JDBC driver from
Microsoft.
But if I use jTDS JDBC Driver, I get this error :
(oracle.jbo.DMLException) JBO-26041: Failed to post data to database
during "Update": SQL Statement
"UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?".
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) Invalid parameter index 1.
Is there any options /setting on jDTS diver that I miss ?
Thank you for your help,
Krist
|||xtanto@.hotmail.com wrote:
> Hi ,
> Thank you for your reply, it works now if I use JDBC driver from
> Microsoft.
> But if I use jTDS JDBC Driver, I get this error :
> (oracle.jbo.DMLException) JBO-26041: Failed to post data to database
> during "Update": SQL Statement
> "UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?".
> -- LEVEL 1: DETAIL 0 --
> (java.sql.SQLException) Invalid parameter index 1.
> Is there any options /setting on jDTS diver that I miss ?
What does this have to do with FOR UPDATE?
Alin.
|||Hi Alin,
I am sorry, yes FOR UPDATE problem already solved after I upgrade to
JDev 10.1.2 and Oracle fix the problem by generating the correct sql
syntax for sqlserver.
However I still have problem with jTDS (described above).
I also post this in jTDS forum.
Thank you,
Krist
|||Could you enable DriverManager logging just before executing the code
that generates the exception and post the resulting log dump?
Alin.

Error - Trancount Reset!

Hi,
I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
setting the autoCommit feature of JDBC.
We often have objects that contain transactional updates that may be called
by another object that has already initiated a transaction or that may be
used alone, thus we need a form of transaction nesting which the autoCommit
feature does not provide.
However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
statement object is created on the same connection - for example, we would
expect:
FIRST OBJECT
BEGIN TRANSACTION
... SOME WORK (TRANCOUNT = 1)
SECOND OBJECT
BEGIN TRANSACTION (TRANCOUNT = 2)
.. SOME WORK
COMMIT TRANSACTION (TRANCOUNT = 1)
FIRST OBJECT
COMMIT TRANSACTION (TRANCOUNT = 0)
However, we have noticed that as soon as a Statement object is created in
the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
COMMIT TRANSACTION statement is issued, an error is raised because there is
no matching BEGIN TRANSACTION.
Has anyone ever encountered this error? Is it a bug?
Thanks,
Mike
MikeF wrote:

> Hi,
> I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
> and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
> setting the autoCommit feature of JDBC.
Very dangerous.

> We often have objects that contain transactional updates that may be called
> by another object that has already initiated a transaction or that may be
> used alone, thus we need a form of transaction nesting which the autoCommit
> feature does not provide.
That doesn't explain why the autoCommit() path won't work. You set autoCommit(false)
and call what you want. anything below needn't know whether it's in a tx or not.
Then commit or roll back.

> However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
> statement object is created on the same connection - for example, we would
> expect:
> FIRST OBJECT
> BEGIN TRANSACTION
> ... SOME WORK (TRANCOUNT = 1)
> SECOND OBJECT
> BEGIN TRANSACTION (TRANCOUNT = 2)
> .. SOME WORK
> COMMIT TRANSACTION (TRANCOUNT = 1)
> FIRST OBJECT
> COMMIT TRANSACTION (TRANCOUNT = 0)
> However, we have noticed that as soon as a Statement object is created in
> the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
> COMMIT TRANSACTION statement is issued, an error is raised because there is
> no matching BEGIN TRANSACTION.
> Has anyone ever encountered this error? Is it a bug?
You need to add a connection property selectMethod=cursor. Otherwise
the driver will make *new connections* under the covers to implement
concurrent statements, and each statement will be independent of
the connection/statement on which you think you started a transaction.
This would have been prevented/revealed immediately if you had stuck to
JDBC control calls for defining your transactions.
Joe Weinstein at BEA
> Thanks,
> Mike