Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 1999-06-28
15
Medium Priority
?
332 Views
Last Modified: 2008-03-04
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
Comment
Question by:ericvb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 5

Expert Comment

by:mayhew
ID: 1095981
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
 

Author Comment

by:ericvb
ID: 1095982
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
 
LVL 5

Expert Comment

by:mayhew
ID: 1095983
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:ericvb
ID: 1095984
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
 
LVL 5

Expert Comment

by:mayhew
ID: 1095985
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
 

Author Comment

by:ericvb
ID: 1095986
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
 
LVL 1

Expert Comment

by:mikkon
ID: 1095987
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
 
LVL 1

Expert Comment

by:mikkon
ID: 1095988
Oops, sorry, that was not meant as an answer. Feel free to reject.
0
 

Author Comment

by:ericvb
ID: 1095989
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
 

Expert Comment

by:Quoth
ID: 2016986
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
 

Accepted Solution

by:
Quoth earned 100 total points
ID: 2016987
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
 

Author Comment

by:ericvb
ID: 2028382
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
 
LVL 1

Expert Comment

by:Vnagaraj
ID: 2051818
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
 
LVL 1

Expert Comment

by:Vnagaraj
ID: 2051820
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
 

Expert Comment

by:Quoth
ID: 2054621
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question