Gene Wirchenko
2012-07-31 02:37:02 UTC
Dear SQLers:
Having been off on something else, I come back to my slow
progress with JavaScript and SQL Server.
I have a stored procedure for retrieving a row given the key. The
results of this SP (the last table selected AIUI) will be returned to
a Web page.
***** Start of Included Code *****
-- GetPriRow
-- Return the Primus Row Matching the Key
-- Last Modification: 2012-07-30
drop procedure GetPriRow
go
create procedure GetPriRow
(
@theKey varchar(4)
)
as
begin
select * from Primus where PKPri=@theKey
if (@@rowcount=0)
begin
create table #Error
(
OrderBy int not null,
ErrorResponse varchar(max)
)
insert into #Error
(OrderBy,ErrorResponse)
values
(0,'Key not found.')
select * from #Error
return
end
select * from Primus where PKPri=@theKey
end
go
***** End of Included Code *****
This code has issues.
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?
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?)
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.
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.
For all of the above, what is the stuff called (so I can search
it down (though good links will be appreciated)), and what is the
syntax?
Are there any other details I should give? Are there any other
questions that I should be asking?
Sincerely,
Gene Wirchenko
Having been off on something else, I come back to my slow
progress with JavaScript and SQL Server.
I have a stored procedure for retrieving a row given the key. The
results of this SP (the last table selected AIUI) will be returned to
a Web page.
***** Start of Included Code *****
-- GetPriRow
-- Return the Primus Row Matching the Key
-- Last Modification: 2012-07-30
drop procedure GetPriRow
go
create procedure GetPriRow
(
@theKey varchar(4)
)
as
begin
select * from Primus where PKPri=@theKey
if (@@rowcount=0)
begin
create table #Error
(
OrderBy int not null,
ErrorResponse varchar(max)
)
insert into #Error
(OrderBy,ErrorResponse)
values
(0,'Key not found.')
select * from #Error
return
end
select * from Primus where PKPri=@theKey
end
go
***** End of Included Code *****
This code has issues.
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?
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?)
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.
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.
For all of the above, what is the stuff called (so I can search
it down (though good links will be appreciated)), and what is the
syntax?
Are there any other details I should give? Are there any other
questions that I should be asking?
Sincerely,
Gene Wirchenko