From: genew@ocis.net
On Tue, 31 Jul 2012 09:08:01 +0000 (UTC), Erland Sommarskog
wrote:
>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
I gave a simple example of error return. There may be more rows;
OrderBy is for presenting them in the correct order. The additional
rows may be added one at a time so I went with a table. Is there a
better way?
>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.
(I do not know what to call the level that is between the browser
and SQL Server. It a) receives the HttpXMLRequest and queries SQL and
b) receives the resultset from SQL Server and packages it for the
browser. What is it called? I will now refer to it as Glue.)
IIUC, you are saying that if I try to return more than one table?
Not the case. Either I return the table data requested, or I return
error data. I do not even know how I would return more than one
resultset. Is it even possible? SQL Server seems to return just the
last query results. Maybe, that is a function of my Glue layer.
>> 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.
No. The purpose of the procedure is to return the row or that
there is not one. If the row is there, the procedure should return
it. I need the data to populate the form.
>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.
IIUC, I read the rowversion value along with the rest of the row
and write it back with the rest of the update. If the versions
differ, SQL Server coughs, presumably, throws an error.
That means that the rowversion value makes it to the front-end,
and that would make the system vulnerable. Am I correct? If so, how
likely is it? Am I worrying over something that is highly unlikely?
What is the way around it? Links would help.
I suppose that I could intercept the rowversion with Glue and
have Glue serve a rowversion gatekeeper. I could keep a table of
recent rowversions returned to browsers and if an update is not on the
list and with the right table, throw an error.
>If you want pessimistic concunrrency, you will have to roll your own.
IIUC, if optimistic fails, the whole update operation will have
to be aborted (including at the front-end). While that is not
optimum, it is probably good enough. I want to protect against update
race conditions. The pattern of usage is such that there ought not to
be too many anyway.
>
>> 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.
It is connected to using SQL Server, so I threw it in. The
problem with it is that it is in between and so it seems it gets
ignored. That might account for me not being able to find out
details.
Sincerely,
Gene Wirchenko
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
