November 22, 2007 at 5:26 pm
GOOD DAY!
Could anyone show me how to handle record viewing in SQL? Could it be done in SQL Statement?
Heres the detail sepecification of my application...
Im developing a client application that handles record viewing and processing. Currently there are 3 client application running. Particularly this application viewed unprocess record. Now after it has been process the flgProcess is set to true. Meaning only all unprocess record is viewed in my application.
Now, what I want is... the moment a certain client application viewed a specific record, those record present on that client application may not be viewed on the other two applications. Its a matter of handling record viewing, i want to restrict viewing once a client viewed a specific record. Let me illustrate...
---RECORD TO BE PROCESS-----
Record1
Record2
Record3
Record4
Record5
Record6
Now 3 client application is running at the same time....
PROCESSING NO. 1
Record1
PROCESSING NO. 2
Record2
PROCESSING NO. 3
Record3
I want to devide the process on different client application by handling viewing restriction.
This is the flow of my client application which is suggested in the other forum.
The flow for the clients should be...
0. connect, begin transaction
1. Lock the records that are to be retrieved (update)
2. Retrieve the records
3. commit, disconnect
However, I've seen it with my two eyes. Same record was fetch by the client. Do we have an issue of simultaneous pick and update?
Heres my code...
BEGIN TRANSACTION
update fetching set fetching.CapturedDateTime = getdate()
,fetching.CapturedBy = @UserName
from tblFetch as fetching
where fetching.tID in (select top 1 tID
from tblFetch
where CapturedBy = '0'
or datediff(minute,CapturedDateTime,getdate()) >= 15
order by tID asc
)
-- Rollback transaction if error occurs.
if @@error <> 0
begin
ROLLBACK TRANSACTION
end
-- Show the record for picking.
SELECT top 1
s.SubscriberName as SubscriberName
,t.TransAmount
,t.tID
,s1.SubscriberName as upline
FROM tblFetch as fetching
INNER JOIN tblTransactions as t on t.tID = loading.tID
INNER JOIN tblArrivals as sa on sa.aid = t.aid
LEFT JOIN tblSubscribers as s on s.CellphoneNo = t.CellphoneNo
LEFT JOIN tblSubscribers as s1 on s.refCellphone = s1.CellphoneNo
where fetching.CapturedBy = @UserName
order by t.sysDateTime
-- Rollback transaction if error occurs.
if @@error <> 0
begin
ROLLBACK TRANSACTION
end
COMMIT TRANSACTION
Could anyone show me an idea how to do this?
Thanks in advance.
November 23, 2007 at 1:20 pm
You may want to check into using SQl triggers - -
I believe they give you the ability to determine how to resolve
the situation of 2 users both having the same record open & changing it.
November 23, 2007 at 10:43 pm
i see... I got your point...
I will create a trigger for update. This trigger will determined if the record was picked by a certain client, then the updation will be rollback else committed.
Thanks for the suggestion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply