• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Inserts and using cursors in multi-user environment (T-SQL)

I declared a cursor to move around in my table (next, prior, ...). To see modifications and deletions, I declared the cursor 'dynamic'. So if someone (another application) is changing a row, I will see it when I fetch the row.

What if another application inserts a row into the base table? I won't see it until I close and reopen the cursor.

How to manage that?
Can application 1 receive a message from application 2 to close his cursor and reopen it because application 2 inserted a row?
Or can the server send a message to all the applications connected to this database?

I can't close and reopen the cursor every time that I fetch a record :
1. I lose the current record, so I must provide a mechanism to avoid this
2. because the select statement in the cursor is complicated, opening the cursor will take some time
0
ericvb
Asked:
ericvb
  • 5
  • 3
  • 3
  • +2
1 Solution
 
mayhewCommented:
The first thing that occurs to me is to update a value in a dummy table when you add a new record.

Then you could check the value in that table from within your cursor to see if it has become true (or whatever).

Just a thought...
0
 
ericvbAuthor Commented:
Yes, I've had the same idee. The problem that can occur with that is when there are three applications (or more)

Application 1 : declared a cursor and user is moving around
Application 2 : declared the same sort of cursor and the user is doing nothing for the moment.
Application 3 : the user added a record to the table and also to the dummy table

--> Application 1 : the program sees a change in the dummy table and will update his cursor.
2 possibilities :
a. he clears the dummy table
b. he adds to the dummy table that he had seen the change

--> Application 2 will see nothing because the user is doing nothing (if I use a thread, I can avoid that, but still that will give problems)

So if I clear the dummy table, the others will not see it, even with threads.

The solution b gives me a overhead to manage. It is possible, but I hoped that there was a easier solution.
(slowness in the application : I must not only search the following record but also check continuously the dummy table)



In my opinion, a SQL database is a database to store things, but not a database to manage things in real-time.
Or I'm missing something......
0
 
mayhewCommented:
No you're absolutely right.

The problem is that in a two-tier, multi-user environment, how else do you communicate between different clients?

You either have to track that info "real-time" in the database or add a middle tier (not necessarily a minor task).

The way I would do yours is to have a table that contains a username field and boolean to show if a change has been made to the cursor.

When a new user opens the cursor, their name is inserted into the table with a false (for example) value in the CursorChanged field.

When somebody makes a change to the cursor, update every row in the dummy table.  Change CursorChanged to true for everybody.

Then when another user triggers the check, just change the boolean to false for that one person.

If you have more than one cursor, then you would have to add a column to the dummy table to track which cursor you're using.

It's not ideal, but that's probably how I would proceed.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ericvbAuthor Commented:
I hoped that you would give me another solution.  :-((

However, typing this, I've got an idee. If I store the number of records when the cursor is opened and check every time this number ....
If the number has changed, someone added or deleted something, so I know that I must close and reopen my cursor.

I will test if this is an usable solution and give you the results.

Thanks
0
 
mayhewCommented:
That might not be a bad solution.

However, it won't catch updates - only adds and deletes.  If that works for you then great!  :)

Sorry I couldn't give you better.
0
 
ericvbAuthor Commented:
Thanks anyway for your help and comments.

I've tried it out and it works fine, even for updates (the cursor sees the changes, even if another user did the change).


0
 
mikkonCommented:
What if application 2 does an INSERT and application 3 a DELETE. Application 1 won't notice anything because the count stays the same...
0
 
mikkonCommented:
Oops, sorry, that was not meant as an answer. Feel free to reject.
0
 
ericvbAuthor Commented:
Sorry mikkon,

I didn't read your first comment. Logically you are right...

Do you know another solution ?
I can't believe that others didn't had the same problem....

Thanks
0
 
QuothCommented:
Why are you using a cursor for anyway?  For most applications the use of a cursor is not the most efficient way to get the desired output.  Most apps I've seen that use cursors do so because the developer is used to dealing with recordsets or data controls and is working under a deadline.  Is there something that your app does that _requires_ the rowset operations of a cursor?
0
 
QuothCommented:
I'm sorry that this is an answer.  For some reason the web page won't let me submit a comment.  Please reject.

Why are you using a cursor for anyway?  For most applications the use of a cursor is not the most efficient way to get the desired output.  Most apps I've seen that use cursors do so because the developer is used to dealing with recordsets or data controls and is working under a deadline.  Is there something that your app does that _requires_ the rowset operations of a cursor?
0
 
ericvbAuthor Commented:
I'm using cursors because in the application the user can navigate through the data (next, previous, last and first).

I can do the same with a select/where statement, but why do it manually if a cursor is giving me the possibilities right away.
0
 
VnagarajCommented:
Use a dummy table with a counter.
Increment this when ever an insert , update or delete on the table is done.

In u'r application initially read the value of this dummy table before u build u'r cursor.

When ever this value has changed in further reads u will have to re-build u'r cursor.
note u also need to move this new value in to the variable used to hold initial value from dummy table. This way u will be able to handle multiple user actions affecting the table.

Looking into future u can also have an extra column in this dummy table to have table name. write proc which returns the count when the input is the table name. so u can use this functionality for all other tables with one dummy table.
0
 
VnagarajCommented:
Use a dummy table with a counter.
Increment this when ever an insert , update or delete on the table is done.

In u'r application initially read the value of this dummy table before u build u'r cursor.

When ever this value has changed in further reads u will have to re-build u'r cursor.
note u also need to move this new value in to the variable used to hold initial value from dummy table. This way u will be able to handle multiple user actions affecting the table.

Looking into future u can also have an extra column in this dummy table to have table name. write proc which returns the count when the input is the table name. so u can use this functionality for all other tables with one dummy table.
0
 
QuothCommented:
Ericvb:  Please reject my error or do what you need to do to give these other fine folks a chance.  My answer was designed to be a comment in the first place, and I'm going to be out of the office for three weeks and won't be able to keep up on the thread.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now