SQL SERVER Cursors

[gsrajput]

I 'm new to Sql Server and would like to know that how you can create a cursor on the server side and then how you can move forward or backward on that cursor by either reading 1 or group of records. I 'm using VFP as my front end. Actually I don't want to use ADO's onto this.

I 'll be grateful if I could get any help onto this.

 

[Mike Lewis]

gsrajput,

Could you explain why you want to do this? Normally, to access records from SQL Server in a VFP application, you would read the records into a client-side cursor. You do not need to create a cursor on the server.

My apologies if I have misunderstood the question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[gsrajput]

Hi Mike,
Thanks for your reply. I think you are right if you 're dealing with small data. but why I 'm thinking other way is that in Client server environment what if my query matches 10,000 records and I don't want to bring all on the client side just read few records by user's input like next 10 records or previous records on the server side.
I 'm just curious that how that could be done on the server side.I know there is a scrollable cursors option in the SQL to do that but not sure how could I use that.

My regards,

Gurvinder.

 

[Mike Lewis]

Gurvinder.

Yes, you are right that this can be done with scrollable cursors. However, the ideal situation is to refine your query so that it does not return such a high number of records. The whole point of using a client/server environment is to limit the size of results set that are sent across the network.

That said, there are obviously going to be times when you can't do that. So, another option is to use synchronous processing on the client side; this will pull records down in batches, so the user can be doing something with them as they arrive.

But even that might not be what you want. I think what you are asking for is a way of delivering small bathes of the records on demand. The user might want to get any of the 10,000 records, but will never want to see more than, say, a hundred or so at any one time.

If that's the case, then you do indeed want to go with scrollable cursors. Essentially, you create a cursor using the syntax: DECLARE cursorname CURSOR FOR select statement. You then OPEN the cursor, then use the FETCH command to retrieve a batch of records.

All this can be done from a VFP front-end. You can find the detailed syntax for these commands in the T-SQL Help.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[gsrajput]

Mike,
Thanks for your help. When I will be doing this on Sql
server how I could tell the stored procedure that results are already there and only thing is get me the next records from the last pointer.

My Regards to you!!

Gurvinder.

 

[Mike Lewis]

Gurvinder,

I'm not sure what you mean. Do you mean you want a way for the stored procedure to know if the cursor is already open? There is a function (or a system variable?) that tells you that. I think it is something like cursor_status. I am away from my system at the moment, so cannot check, but you should be able to find details in the Help.

Basically, your stored procedure will look something like this:

- Declare a cursor to hold a given result set

- If it is not already open, go ahead and open it.

- Fetch the next so-many records.

When you have finished with the cursor, send a CLOSE command.

If you are still stuck after the weekend, I will look up the syntax for you.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Lewis]

Gurvinder,

OK, I'm back at my desk now and have perused the Help file.

What I told you earlier is basically correct. You can write a stored procedure that does the following:

1. If this is the first time here, DECLARE and OPEN your cursor. Make the cursor GLOBAL. For the other options in the DECLARE statement, you can probably rely on the defaults.

2. FETCH the next record. Do not use the INTO clause. This means that records will be available for returning to the caller.

3. If there are no more records to fetch, CLOSE and DEALLOCATE the cursor. Use @@FETCH_STATUS to see if you have retrieved the last record.

However, if you use cursors in this way, the FETCH can only retrieve one record at a time. That means that your procedure can only return one record at a time. This is extremely inefficient -- so much so that it will defeat the object of using cursors.

It is possible to create something called a block cursor. This will return 10, 20, 50 or any other number of records that you specify. Unfortunately, it seems that there is no way of using block cursors within the T-SQL language (someone correct me if I am wrong on that point).

To use a block cursor, you need to make direct calls to the ODBC or OLE DB API (for this reasons, this type of cursor is sometimes called an "API Server" cursor). That seems to me to be a lot of trouble. It would be easier to use ADO to get the equivalent functionality (despite your earlier remark that you want to avoid this).

I still think the best solution is to design the application so that the user never needs to get these 10,000 records. Find some way of filtering the query so that only a small result set is required. That is the whole essence of client-server design.

Hope this is useful.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[gsrajput]

Hi Mike,
Sorry ,I was sick and did n't get to work. Yes, you are a wonderful help and this is very useful too. I think why I did n't want to use ADO were because of the "DLL hell" and I was reading somebody's conversion notes from VFP to Sql server that there are some problems with Dll files.
Anyway thank you very much for your help!!

My best regards to you.

Gurvinder

 

[Mike Lewis]

Gurvinder,

I'm glad it was useful, I know what you mean about DLL Hell, but, even so, ADO will greatly simplify the whole thing. Good luck with the project.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)