Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1189

Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX

Cross-posted to 2 groups.

Followups directed to: comp.databases.ms-sqlserver

Show all headers | View raw

Gene Wirchenko (genew@ocis.net) writes:
>   1) I expect to have to create and insert to #Error quite frequently.
> I would like a stored procedure for each of these.  Unfortunately, due
> to the lifetime rules for temporary tables, if I create #Error in its
> own SP, it will be deleted as soon as the creation SP terminates.  The
> above is more verbose than I like.  I would prefer to be able to code
> something like the following in-line in my SPs:
>           execute CreErrTbl
>           execute InsErrRow 0,'Key not found.'
> Is there a cleaner way to do this?

Why wouid you creaet the temp table at all? Why not just return a result set

   SELECT 0 AS OrderBy, 'Key not found.' AS ErrorReponse

Also, most clients gets confused if a stored procedure returns two results 
sets with different shape. Of course, it's perfectly manageable if you 
program the client correctly, but it's a little dubious.
 
>   2) I query Primus twice, once for the row count and once for real. I
> do wish to catch no rows as an error condition.  I wish to avoid race
> conditions.  (I think I have a race condition here in the case of the
> row being deleted just after the first time, but before the second.
> Am I correct in thinking this?  If so, how do I correct it?)

So the purpose with the above procedure is to check for existence? Why in 
that case not use 

   SELECT @output = CASE WHEN EXISTS (select * 
                                      from Primus where PKPri=@theKey)
                         THEN 1
                         ELSE 0
                   END

And this scraps the result set above entirely. The stored procedure have no 
reason to ponder whether existence is an error not. The job of the procedure 
is return whether there is a row or not. It's up to the caller to consider 
that to be an error or not.

As for your actual question, the answer is that if you are in a transaction
and you add the table hint (SERIALISABLE) you will prevent a row to be 
inserted until you commit. However, see the next point:

 
>   3) The front-end will be a Web page.  I want to retrieve a row, edit
> it, and write it back.  I would like that any changes in the meantime
> are not overwritten.  As far as I can see, this is not going to work
> to protect meantime changes, and I need to do something more.
> 
>      Can I lock the read row?  If so:
>   a) Can I release the lock after updating the row?  This would be in
> a separate stored procedure and a different XMLHttpRequest.
>   b) Can I have the row lock expire after a period of time (say, 15
> minutes)?  Someone could close his browser after reading a row, and I
> do not want the row lock kept indefinitely.

Yes, you can lock a row if you read it in a transction and the isolation 
level is at least PEPEATABLE READ. The row will be released when you commit.

BUT DON'T DO THIS! Having a transaction in progress when you are waiting for 
user input is an absolute no-no.

The most commonly used solution is optimisitic concurrency. You add a 
timestamp (a.k.a rowversion) column to the table. This column is auto-
matically updated with a database-unique monotonically growing value when a 
row is updated. Thus, you read the timestamp value and when you write back 
you compare, and if they are different someone else has come in between.

If you want pessimistic concunrrency, you will have to roll your own.

>   4) I would like to avoid using ActiveX to communicate between the
> Web page and SQL Server and preferably without having to add more
> software to the server.  Otherwise, my app is going to be limited to
> IE.  I have been unable to find any details on how to do this.
> Pointers would be appreciated.

Sounds like a question for a different newsgroup.
 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:37 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:47 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 09:08 +0000
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 09:26 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:12 -0400
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:04 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 21:45 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 20:59 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-08-01 13:30 -0700
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 23:27 +0200
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:25 -0400
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 00:26 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 17:43 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-01 06:29 -0400
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:40 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:02 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-08-01 16:02 -0400
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:29 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 09:14 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-04 18:14 +1000
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-04 21:15 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-05 18:10 +1000
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 10:50 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 00:03 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 20:21 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 19:44 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-06 20:54 +0200

csiph-web