By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

ASP Type mismatch error with SELECT...FOR UPDATE statement

P: n/a
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement

I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without FOR UPDATE, it is fine and
no error.
I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF
EMPNO"), but it still couldn't help.

any ideas? I tried to search in the web but couldn't find similar
problem. Is it because
the setting problems?
Here's the code fragment:
========================
On Error Resume Next
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectionString
Set objRs = Server.CreateObject("ADODB.RecordSet")
objRs.CursorLocation = adUseClient
objRs.open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
//etc...
ErrHndl:
response.write Err.Source & "<br>"
response.write "Error number " & err.number & "<br>"
response.write "Error description " & err.description & "<br>"
response.end
Errors:
=======================
Microsoft VBScript runtime error
Error number 13
Error description Type mismatch
Please advise. thanks a lot!!

May 24 '06 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Where are you getting this syntax from - 'update of', 'select for update'?

Bob Lehmann

"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement

I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without FOR UPDATE, it is fine and
no error.
I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF
EMPNO"), but it still couldn't help.

any ideas? I tried to search in the web but couldn't find similar
problem. Is it because
the setting problems?
Here's the code fragment:
========================
On Error Resume Next
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectionString
Set objRs = Server.CreateObject("ADODB.RecordSet")
objRs.CursorLocation = adUseClient
objRs.open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
//etc...
ErrHndl:
response.write Err.Source & "<br>"
response.write "Error number " & err.number & "<br>"
response.write "Error description " & err.description & "<br>"
response.end
Errors:
=======================
Microsoft VBScript runtime error
Error number 13
Error description Type mismatch
Please advise. thanks a lot!!

May 24 '06 #2

P: n/a
I presume you are using mssql and not msAccess ?

May 24 '06 #3

P: n/a
Steve wrote:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement


Never ask a database-related question without revealing what database type
and version you are using.

I have never seen "FOR UPDATE" used except in SQL Server. In SQL Server, it
can only be used when declaring a T-SQL cursor. So, even in SQL Server, if
you use it anywhere else, you will get a syntax error.

I cannot speak for other databases like Oracle.

Why do you wish to (or think you need to) use this syntax?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
May 24 '06 #4

P: n/a
I am using oracle 9i database. I tried to execute the query in Oracle
and it works fine, then it should work fine if the ASP page executes
that query runs on Oracle?
Bob Barrows [MVP] wrote:
Steve wrote:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement


Never ask a database-related question without revealing what database type
and version you are using.

I have never seen "FOR UPDATE" used except in SQL Server. In SQL Server, it
can only be used when declaring a T-SQL cursor. So, even in SQL Server, if
you use it anywhere else, you will get a syntax error.

I cannot speak for other databases like Oracle.

Why do you wish to (or think you need to) use this syntax?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


May 24 '06 #5

P: n/a
I don't know. I have no experience with Oracle.
What is the purpose of that syntax in Oracle? I.E., what is the goal you
are trying to accomplish that can't be accomplished with standard ANSI
SQL?

Bob Barrows
Steve wrote:
I am using oracle 9i database. I tried to execute the query in Oracle
and it works fine, then it should work fine if the ASP page executes
that query runs on Oracle?
Bob Barrows [MVP] wrote:
Steve wrote:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE
statement


Never ask a database-related question without revealing what
database type and version you are using.

I have never seen "FOR UPDATE" used except in SQL Server. In SQL
Server, it can only be used when declaring a T-SQL cursor. So, even
in SQL Server, if you use it anywhere else, you will get a syntax
error.

I cannot speak for other databases like Oracle.

Why do you wish to (or think you need to) use this syntax?


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
May 24 '06 #6

P: n/a
I am wondering the same thing.....

It sounds to me like you may be wanting to carry out a secure
transaction... in which case, ASP has the tools to do it.

You'll need to use the proper transaction properties of the database
objects....
E.g....
.....

<%
Set con = Server.CreateObject("ADODB.Connection")

con.Open connectionString 'open connection
con.BeginTrans 'Begin the transaction

con.Execute("insert into YourTablename values ('testFieldData')")
'insert a record

if YourTestCondition = True then
con.CommitTrans
else
con.RollBackTrans
end if

Set Con = Nothing
%>

Hope this helps,

Rob
http://www.webforumz.com/asp-forum/ - ASP Forum

May 24 '06 #7

P: n/a
I tried to put con.BeginTrans and it seems working. Is that all I
need??

objConn.BeginTrans
objRS.Open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
con.CommitTrans
SEOSpecialist wrote:
I am wondering the same thing.....

It sounds to me like you may be wanting to carry out a secure
transaction... in which case, ASP has the tools to do it.

You'll need to use the proper transaction properties of the database
objects....
E.g....
....

<%
Set con = Server.CreateObject("ADODB.Connection")

con.Open connectionString 'open connection
con.BeginTrans 'Begin the transaction

con.Execute("insert into YourTablename values ('testFieldData')")
'insert a record

if YourTestCondition = True then
con.CommitTrans
else
con.RollBackTrans
end if

Set Con = Nothing
%>

Hope this helps,

Rob
http://www.webforumz.com/asp-forum/ - ASP Forum


May 24 '06 #8

P: n/a
Yes, you need to tell SQL you are beginning a transaction.

May 24 '06 #9

P: n/a
The interesting thing is that why we need to call objConn.BeginTrans if
we use UPDATE OF clause? For other regular SQL statement, we don't need
to call objConn.BeginTrans at all, and I never use this method before.
Is this ASP specific problem?

please advise. thanks again!!

May 24 '06 #10

P: n/a
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the reason.
Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!

May 25 '06 #11

P: n/a
More details please. It appears that you are planning to write some sort of
procedural code to do something that can be done in a single efficient
set-based operation.

I'm still not clear what the FOR UPDATE clause does for you in Oracle.

Steve wrote:
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the
reason. Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not
be updated when the application calls tb_update() and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
May 25 '06 #12

P: n/a
On reflection, I think you may get quicker, more focussed help if you find
an Oracle newsgroup or forum and post your scenario there. They will
probably be more able to tell you the most efficient way to accomplish your
task than we would.

This really sounds like the type of activity I would be doing via a stored
procedure.

Bob Barrows

Bob Barrows [MVP] wrote:
More details please. It appears that you are planning to write some
sort of procedural code to do something that can be done in a single
efficient set-based operation.

I'm still not clear what the FOR UPDATE clause does for you in Oracle.

Steve wrote:
Basically below is what I am doing, and the interesting observation
is that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the
reason. Any ideas?

Another concern is do you think it is possible another transaction
can happen right after objConn.RollbackTrans (after step 1 & 2),
since the lock has released? If this is the case, then
current_balance may not be updated when the application calls tb_update()
and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
May 25 '06 #13

P: n/a

"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the reason.
Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


FOR UPDATE informs Oracle that the rows in the selected set are about to be
updated. It therefore will place a lock on them. For that lock to have any
meaning it needs to be made in the context of a transaction.

The if you use the RollbackTrans call as it seems you are in the code above
you might as well not bother with the transaction at all and drop the FOR
UPDATE clause.

The sequence you've outlined above should be protected by a transaction. It
would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.

May 26 '06 #14

P: n/a
Anthony Jones wrote:
"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
Basically below is what I am doing, and the interesting observation
is that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the
reason. Any ideas?

Another concern is do you think it is possible another transaction
can happen right after objConn.RollbackTrans (after step 1 & 2),
since the lock has released? If this is the case, then
current_balance may not be updated when the application calls
tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


FOR UPDATE informs Oracle that the rows in the selected set are about
to be updated. It therefore will place a lock on them. For that
lock to have any meaning it needs to be made in the context of a
transaction.

The if you use the RollbackTrans call as it seems you are in the code
above you might as well not bother with the transaction at all and
drop the FOR UPDATE clause.

The sequence you've outlined above should be protected by a
transaction. It would be best handled by an SP in ORACLE that does
all the transaction management locally. If you must do it in ASP
then you need a BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in
SQL Server.


Ah, that turns the light on! Thanks.

Still, it seems to me that a set-based rather than cursor-based solution
should be pursued for this.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
May 26 '06 #15

P: n/a
Anthony:

I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?

In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?

Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Again, pleae advise more and thanks for your suggestions and inputs.

Steve.
Anthony Jones wrote: "Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the reason.
Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


FOR UPDATE informs Oracle that the rows in the selected set are about to be
updated. It therefore will place a lock on them. For that lock to have any
meaning it needs to be made in the context of a transaction.

The if you use the RollbackTrans call as it seems you are in the code above
you might as well not bother with the transaction at all and drop the FOR
UPDATE clause.

The sequence you've outlined above should be protected by a transaction. It
would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.


May 29 '06 #16

P: n/a
Last time I checked, "UPDATE FOR ..." was not a valid statement in an SQL
string .... it should instead be;

sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."

Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....

As an additional FYI ..... you should NEVER EVER EVER EVER use "Select *"

http://aspfaq.com/show.asp?id=2096

But the experts know best so I shall retire back to my lil' corner ........

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Steve" <ja*****@gmail.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com...
Anthony:

I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?

In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?

Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans
Again, pleae advise more and thanks for your suggestions and inputs.

Steve.
Anthony Jones wrote:
"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the reason. Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be updated when the application calls tb_update() and tb_insert() methods.

Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


FOR UPDATE informs Oracle that the rows in the selected set are about to

be updated. It therefore will place a lock on them. For that lock to have any meaning it needs to be made in the context of a transaction.

The if you use the RollbackTrans call as it seems you are in the code above you might as well not bother with the transaction at all and drop the FOR UPDATE clause.

The sequence you've outlined above should be protected by a transaction. It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.

May 29 '06 #17

P: n/a
'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
being updated.

Bob Lehmann

"Steven Burn" <so*******@in-time.invalid> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
Last time I checked, "UPDATE FOR ..." was not a valid statement in an SQL
string .... it should instead be;

sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."

Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....

As an additional FYI ..... you should NEVER EVER EVER EVER use "Select *"

http://aspfaq.com/show.asp?id=2096

But the experts know best so I shall retire back to my lil' corner .........
--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Steve" <ja*****@gmail.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com...
Anthony:

I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?

In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?

Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
> Pseudocode
> ==========
> //1) call get_balance() method to perform select statement to get
> current_balance
> //some transaction happens here ????
> //2) call tb_update() method to perform update statement based on
> current_balance
> //some transaction happens here ????
> //3) call tb_insert() method to perform insert statement based on
> current_balance
>
> code fragment of get_balance() method:
> =====================================
> objConn.BeginTrans
> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> adOpenForwardOnly, adLockBatchOptimistic
> current_balance = objRS(0)
> objConn.RollbackTrans
Again, pleae advise more and thanks for your suggestions and inputs.

Steve.
Anthony Jones wrote:
"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
> Basically below is what I am doing, and the interesting observation is > that Oracle SELECT ... FOR UPDATE clause needs to use with
> objConn.BeginTrans method. I still don't understand what is the reason. > Any ideas?
>
> Another concern is do you think it is possible another transaction can > happen right after objConn.RollbackTrans (after step 1 & 2), since the > lock has released? If this is the case, then current_balance may not be > updated when the application calls tb_update() and tb_insert() methods. >
>
> Pseudocode
> ==========
> //1) call get_balance() method to perform select statement to get
> current_balance
> //some transaction happens here ????
> //2) call tb_update() method to perform update statement based on
> current_balance
> //some transaction happens here ????
> //3) call tb_insert() method to perform insert statement based on
> current_balance
>
> code fragment of get_balance() method:
> =====================================
> objConn.BeginTrans
> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> adOpenForwardOnly, adLockBatchOptimistic
> current_balance = objRS(0)
> objConn.RollbackTrans
>
>
>
> Please advise more. thanks!!!
>

FOR UPDATE informs Oracle that the rows in the selected set are about to be
updated. It therefore will place a lock on them. For that lock to
have
any meaning it needs to be made in the context of a transaction.

The if you use the RollbackTrans call as it seems you are in the code above you might as well not bother with the transaction at all and drop the FOR UPDATE clause.

The sequence you've outlined above should be protected by a
transaction.
It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in

SQL Server.


May 29 '06 #18

P: n/a

"Steve" <ja*****@gmail.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com...
Anthony:

I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?

In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?

Where's the nested transaction? Without a specific BeginTrans command the
only transaction is an implicit one that a DB engine may choose to create
fleetingly during the operation a single command.

In order to bind a series of commands under a transaction such a transaction
needs to be explicitly declared. Are you saying you are already doing that?
If so you should remember that a transaction is created with in the scope of
a connection so the same connection should be used for all commands that are
to operate under that transaction
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
Without the protection of an explicit transaction declared before calling
these methods then yes. The whole point of FOR UPDATE is to lock a set of
rows so that don't change while modifications are being made. However a DB
Lock needs to be created in the context of an explicit transaction.

IMO this design is flawed. You should have a single method to update a
balance or whatever which in turn calls an SP that makes the change for you.
The SP should use ORACLEs native commands to create a transaction perform
the updates (and as Bob pointed out you probably don't even need the SELECT
to do this) and then commit the transaction.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans
Again, pleae advise more and thanks for your suggestions and inputs.

Steve.
Anthony Jones wrote:
"Steve" <ja*****@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans method. I still don't understand what is the reason. Any ideas?

Another concern is do you think it is possible another transaction can
happen right after objConn.RollbackTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be updated when the application calls tb_update() and tb_insert() methods.

Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance

code fragment of get_balance() method:
=====================================
objConn.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans

Please advise more. thanks!!!


FOR UPDATE informs Oracle that the rows in the selected set are about to

be updated. It therefore will place a lock on them. For that lock to have any meaning it needs to be made in the context of a transaction.

The if you use the RollbackTrans call as it seems you are in the code above you might as well not bother with the transaction at all and drop the FOR UPDATE clause.

The sequence you've outlined above should be protected by a transaction. It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.

May 30 '06 #19

P: n/a
hehe prolly why I've never heard of it (don't use Oracle, lol).

Cheers for the correction ;o)

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
being updated.

Bob Lehmann

"Steven Burn" <so*******@in-time.invalid> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
Last time I checked, "UPDATE FOR ..." was not a valid statement in an SQL
string .... it should instead be;

sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."

Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....

As an additional FYI ..... you should NEVER EVER EVER EVER use "Select *"
http://aspfaq.com/show.asp?id=2096

But the experts know best so I shall retire back to my lil' corner ........

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Steve" <ja*****@gmail.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com...
Anthony:

I will use ASP approach without using stored procedure. What bothers me is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?

In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?

Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?

> > Pseudocode
> > ==========
> > //1) call get_balance() method to perform select statement to get
> > current_balance
> > //some transaction happens here ????
> > //2) call tb_update() method to perform update statement based on
> > current_balance
> > //some transaction happens here ????
> > //3) call tb_insert() method to perform insert statement based on
> > current_balance
> >
> > code fragment of get_balance() method:
> > =====================================
> > objConn.BeginTrans
> > objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> > adOpenForwardOnly, adLockBatchOptimistic
> > current_balance = objRS(0)
> > objConn.RollbackTrans

Again, pleae advise more and thanks for your suggestions and inputs.

Steve.
Anthony Jones wrote:
> "Steve" <ja*****@gmail.com> wrote in message
> news:11**********************@j73g2000cwa.googlegr oups.com...
> > Basically below is what I am doing, and the interesting observation is > > that Oracle SELECT ... FOR UPDATE clause needs to use with
> > objConn.BeginTrans method. I still don't understand what is the reason.
> > Any ideas?
> >
> > Another concern is do you think it is possible another transaction can > > happen right after objConn.RollbackTrans (after step 1 & 2), since the > > lock has released? If this is the case, then current_balance may
not
be
> > updated when the application calls tb_update() and tb_insert()

methods.
> >
> >
> > Pseudocode
> > ==========
> > //1) call get_balance() method to perform select statement to get
> > current_balance
> > //some transaction happens here ????
> > //2) call tb_update() method to perform update statement based on
> > current_balance
> > //some transaction happens here ????
> > //3) call tb_insert() method to perform insert statement based on
> > current_balance
> >
> > code fragment of get_balance() method:
> > =====================================
> > objConn.BeginTrans
> > objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> > adOpenForwardOnly, adLockBatchOptimistic
> > current_balance = objRS(0)
> > objConn.RollbackTrans
> >
> >
> >
> > Please advise more. thanks!!!
> >
>
> FOR UPDATE informs Oracle that the rows in the selected set are
about to
be
> updated. It therefore will place a lock on them. For that lock to

have
any
> meaning it needs to be made in the context of a transaction.
>
> The if you use the RollbackTrans call as it seems you are in the

code above
> you might as well not bother with the transaction at all and drop
the FOR
> UPDATE clause.
>
> The sequence you've outlined above should be protected by a

transaction.
It
> would be best handled by an SP in ORACLE that does all the

transaction > management locally. If you must do it in ASP then you need a
> BeginTrans/CommitTrans to span the whole sequence.
>
>
> Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in

SQL > Server.



May 30 '06 #20

This discussion thread is closed

Replies have been disabled for this discussion.