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

Discussion in 'ASP General' started by Steve, May 24, 2006.

  1. Steve

    Steve Guest

    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!!
     
    Steve, May 24, 2006
    #1
    1. Advertising

  2. Steve

    Bob Lehmann Guest

    Where are you getting this syntax from - 'update of', 'select for update'?

    Bob Lehmann

    "Steve" <> wrote in message
    news:...
    > 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!!
    >
     
    Bob Lehmann, May 24, 2006
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], May 24, 2006
    #3
  4. Steve

    Steve Guest

    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"
     
    Steve, May 24, 2006
    #4
  5. 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.
     
    Bob Barrows [MVP], May 24, 2006
    #5
  6. Steve

    Steve Guest

    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
     
    Steve, May 24, 2006
    #6
  7. Steve

    Steve Guest

    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!!
     
    Steve, May 24, 2006
    #7
  8. Steve

    Steve Guest

    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!!!
     
    Steve, May 25, 2006
    #8
  9. 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"
     
    Bob Barrows [MVP], May 25, 2006
    #9
  10. 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"
     
    Bob Barrows [MVP], May 25, 2006
    #10
  11. "Steve" <> wrote in message
    news:...
    > 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.
     
    Anthony Jones, May 26, 2006
    #11
  12. Anthony Jones wrote:
    > "Steve" <> wrote in message
    > news:...
    >> 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"
     
    Bob Barrows [MVP], May 26, 2006
    #12
  13. Steve

    Steve Guest

    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" <> wrote in message
    > news:...
    > > 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.
     
    Steve, May 29, 2006
    #13
  14. Steve

    Steven Burn Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > > news:...
    > > > 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.

    >
     
    Steven Burn, May 29, 2006
    #14
  15. Steve

    Bob Lehmann Guest

    'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
    being updated.

    Bob Lehmann

    "Steven Burn" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > > news:...
    > > > > 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.

    > >

    >
    >
     
    Bob Lehmann, May 29, 2006
    #15
  16. "Steve" <> wrote in message
    news:...
    > 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" <> wrote in message
    > > news:...
    > > > 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.

    >
     
    Anthony Jones, May 30, 2006
    #16
  17. Steve

    Steven Burn Guest

    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" <> wrote in message
    news:%...
    > 'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
    > being updated.
    >
    > Bob Lehmann
    >
    > "Steven Burn" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > > > 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" <> wrote in message
    > > > > news:...
    > > > > > 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.
    > > >

    > >
    > >

    >
    >
     
    Steven Burn, May 30, 2006
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alex Kizub
    Replies:
    10
    Views:
    1,775
    Alex Kizub
    Feb 11, 2010
  2. Sue Adams

    asp: type mismatch and update loop

    Sue Adams, Sep 18, 2003, in forum: ASP General
    Replies:
    0
    Views:
    164
    Sue Adams
    Sep 18, 2003
  3. select query data type mismatch

    , Jul 1, 2008, in forum: ASP General
    Replies:
    3
    Views:
    329
    Daniel Crichton
    Jul 2, 2008
  4. .Net Sports

    Type mismatch problems with select box list

    .Net Sports, Dec 21, 2009, in forum: ASP General
    Replies:
    10
    Views:
    1,125
    .Net Sports
    Dec 22, 2009
  5. programmingzeal
    Replies:
    0
    Views:
    1,181
    programmingzeal
    May 6, 2012
Loading...

Share This Page