Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54

    Unanswered: SQL Server does not support FOR UPDATE?

    When I have FOR UPDATE in query string I get SQL error:

    FOR UPDATE cannot be specified on a READ ONLY cursor.

    I wonder if MS SQL Server does this type of positional update differently than DB2 and Oracle.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nope, you are "good to go" on this one. SQL Server supports the SQL-92 syntax for DECLARE CURSOR.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is your query string a cursor? You don't need FOR UPDATE just to update a dataset. You can run an UPDATE query:

    UPDATE tableA
    set columnA = ValueA,
    columnB = ValueB
    from tableA
    inner join Whatever
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Thanks.

    Does this mean we cannot do the following JDBC code?
    What would be the equivalent code for SQL Server?


    ResultSet rs = stmt.executeQuery(SELECT * FROM pubs.dbo.jobs FOR UPDATE WHERE jobno=100);

    if (rs.next()) {
    stmt.excuteUpdate(“UPDATE pubs.dbo.jobs SET position=’none’ WHERE CURSOR=rs.getCursorName()”);
    }

    Thanks.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm afraid that you appear to be thinking in Oracle/DB2 terms again. SQL Server (and therefore it's ADO interface) doesn't implicitly declare a cursor for a SELECT statement. The use of FOR UPDATE is meaningless in this context.

    The best solution is to just derive the PK from the columns returned, and issue the UPDATE based on that PK instead of the cursor position.

    -PatP

  6. #6
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Thanks Pat!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •