Database cursors and their advantages

Cursors (cursor)

　　A cursor is a data buffer opened by the system for the user, which holds the results of the SQL statement execution

Each cursor area has a name

The user can use SQL statements to fetch records from the cursor and assign the main variables to the main language for further processing

The main language is record-oriented, and a set of primary variables can hold only one record at a time

Using only the primary variable does not fully satisfy the SQL statement's requirement to output data to the application

Embedded SQL introduces the concept of cursors to coordinate these two different processing methods

During database development, when you retrieve data that is only a single record, the transaction statement code that you write tends to use the Select INSERT statement. But we often encounter situations where one record is read from one result set to another. So how do we solve this problem? Cursors provide us with a very good solution.

1. Advantages of cursors and cursors

In a database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. cursors are always associated with an SQL selection statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record. when you decide to process a result set, you must declare a cursor that points to the result set. If you have ever written a program that handles a file in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent the file as long as the file opens successfully . For cursors, the rationale is the same. Visible cursors are capable of processing the result set from the underlying table in a manner similar to that of a traditional program reading a flat file, rendering the data in the table to the program as a flat file.

We know that relational database management systems are essentially set-oriented, and in MS SQL SERVER there is no representation of a single record in a table, unless a WHERE clause is used to restrict only one record from being selected. So we have to use cursors to handle single-record-oriented data processing. Thus, cursors allow the application to perform the same or different operations on each row of the rows result set returned by the query statement Select, rather than one operation for the entire result set at a time, and it also provides the ability to delete or update the data in the table based on the cursor position; It is the cursor that is a collection-oriented database management system and the line-oriented programming of the two linked together, so that two data processing methods can communicate.

2. Cursor Type

MS SQL Server supports three types of cursors: Transact_sql cursors, API server cursors, and client cursors.

(1) Transact_sql cursors

TRANSACT_SQL cursors are defined by the DECLARE CURSOR syntax and are primarily used in transact_sql scripts, stored procedures, and triggers. Transact_sql cursors are primarily used on the server and are managed by TRANSACT_SQL statements sent from the client to the server or Transact_sql in batches, stored procedures, triggers. Transact_sql cursors do not support extracting data blocks or multiple rows of data.

(2) API cursors

API cursors support the use of cursor functions in OLE DB, ODBC, and Db_library, primarily on servers. Each time the client application invokes an API cursor function, the MS SQL SEVER OLE DB provider, ODBC Drive, or db_library dynamic-link library (DLL) will route these client requests to the server to process the API cursors.

(3) Customer cursors

A client cursor is used primarily when a result set is cached on the client. In a customer cursor, there is a default result set that is used to cache the entire result set on the client. Client cursors only support static cursors rather than dynamic cursors. Because server cursors do not support all Transact-SQL statements or batches, client cursors are often used only as a helper for server cursors. Because in general, server cursors can support the vast majority of cursor operations. Because API cursors and Transact-SQL cursors are used on the server side, they are called server cursors, also known as background cursors, and client cursors are called foreground cursors. In this chapter we mainly describe the server (background) cursors

Detailed Cursor Description:

Rs. OPEN sql,conn,a,b

The parameter A is the type that sets the cursor, and the value is:

0 forward-only cursors, only forward-browsing records, no paging, Recordset, BookMark

1 keyset cursors, changes that other users make to the record will be reflected in the recordset, but other users adding or deleting records will not be reflected in the recordset. Support for paging, Recordset, BookMark

2 dynamic cursors are the strongest, but the most expensive source. Changes that the user makes to the record, add or delete records are reflected in the recordset. Full-featured browsing is supported.

3 static cursors, just a snapshot of the data, the user changes to the record, add or delete records are not reflected in the recordset. Supports moving forward or backward

The parameter B is the lock type of the recordset, and its value is:

1 lock type, default, read-only, no modification

2 The safest way to lock records immediately when editing

3 The Recordset is locked only when the Update method is called, while other operations can still make changes, insertions, and deletions to the current record

4 records are not locked when editing, and changes, insertions, and deletions are done in batch mode

There is more than one way to open a data recordset, but the most we use is

Rs.Open sql,1,1 method, but the number of parameters behind many people do not understand its meaning, let us introduce.

In fact, there are several arguments behind the Open method

CursorType LockType CommandType

Like Rs.Open sql,1,1.

can also be written

Rs.cursortype = 1

Rs. LockType = 1

Rs.Open SQL

Where CursorType represents the records returned from a table or from a SQL query result.

There are four values for this parameter, respectively:

adOpenForwardOnly means that only the records in the recordset are allowed to move forward. This is the default value.

adOpenKeyset reflects changes or deletions made to records by other users, but does not reflect the action of adding new records that are contrived by other users.

adOpenDynamic reflects changes or deletions made to records by other users, including new records added

adOpenStatic does not reflect changes made to records by other users, add, delete actions.

These four values VBScript pre-defined bits

adOpenForwardOnly = 0

adOpenKeyset = 1

adOpenDynamic = 2

adOpenStatic = 3

LockType represents the type that the data provider uses to lock the database when the recordset is opened:

adLockReadOnly data cannot be changed, this is the default value!

adLockPessimistic Data provider Locks records when starting to edit data

adLockOptimistic Data provider Locks records only when the Update method is called

adLockBatchOptimistic for Batch Modification

Their constant value definitions are:

adLockReadOnly = 1

adLockPessimistic = 2

adLockOptimistic = 3

adLockBatchOptimistic = 4

Rs.Open sql,conn,1,1 Read Record Select

Rs.Open sql,conn,1,3 update only record best update

Rs.Open sql,conn,2,3 inserting and deleting the best insert Delete

The following explanation:

1. What is a cursor?

Cursors, also known as cursors. Generally speaking, it is based on records.

In the past, relational databases have not been widely used as they are now. At that time, most people used small database software such as DBase. This type of database should be specifically data file management software. They are record-oriented.

However, this approach may be more in line with people's habits. For example, we look up the number in the phone book, find the file in the student file, and ultimately it comes down to one of the numbers, a file, that's a record. In real life, when we look for an item in a table, we may sweep through one line by hand to help us find the record we need. For the database, this is the cursor model. So, you can imagine that tables are tables in a database, and our hands are like cursors.

So, when you use a similar. MoveNext,. MoveLast Such a statement, feel more natural.

Now, you know what a cursor is. A cursor is the ' positioning system ' of the data.

There are two types of this ' positioning System ': Server cursors and client cursors. Corresponds to the CursorLocation in ADO. For example:

Set rs = Server.CreateObject ("ADODB". RecordSet ")
Rs. CursorLocation = adUseServer ' default, using server cursors
.....
Rs. CursorLocation = adUseClient ' using customer cursors

2. What is a server cursor?

Or called an API server cursor.

Suppose you want to query a book about a programming language and write it as a SQL statement: SELECT book_id,book_title from books WHERE Book_catalog = ' programming ' ORDER by Book_title while you specify to use server cursors 。

This statement is sent to the server, the server retrieves the data, and the collection of records that meet the query criteria is placed in a temporary table (this is true for some cursor types). Whenever you proceed. MoveNext operation, the server sends a record to the client's buffer before you can use it.

3. What is CacheSize?

Continue with the above example, assuming that there are 100 records that match the query criteria, that is, use. MoveNext This method of traversing the result set requires interacting with the server 100 times. We can set the CacheSize to make the client's communication with the server less. The above example is actually the case of cachesize=1, which is the default value.

Suppose Cachesize=4, when the Recordset object is open, the server sends 4 records to the client. The first 4 times. The MoveNext operation is actually getting data in the client buffer when the 5th time. Movenext, the server sends the next 4 records. This reduces the network communication between the customer and the server.

Does that mean the bigger the CacheSize the better? Don't take it for granted. Everything has two sides, CacheSize is the same. The client requests the data, the server sends the data, and the process is a bit like traffic management. CacheSize is too high to block traffic and even cause data loss (such as when CacheSize is larger than the client buffer). For different applications, the values are also different.

It is also pointed out that the use of any form of cursors is not the most efficient way to access data, Cachesize sometimes is not a bottleneck, as far as possible to use the cursor to convert the program to a result-set-oriented program, performance will improve a lot.

3. What is a customer cursor?

Since the cursor is the ' positioning system ' of the data, it can also be done at the client.

Customer cursors have long been created to compensate for server deficiencies (some database engines do not support cursors). With the advent of server cursors, the client cursor seems to be out of date, but still the phrase: Everything has two sides, on the Internet, the connection to the database is not permanent, and using client cursors allows us to get the same functionality as using a server cursor.

When the Cursorloction property is set to adUseClient, Microsoft's Cursor service creates a RecordSet object, retrieving all query results from the server using a forward/read-only cursor and storing it in the client buffer. When an application requests data through ADO, the cursor service checks the data from the client buffer. This is useful when connecting to a remote server, which can improve the performance of your application. If you are accessing a database that is Jet database (access) and is local, then using a customer cursor instead of improving performance will degrade performance. At this time, the data will be cached two times, the database once, the cursor service once.

If you consider the functionality of the app, the customer cursor functionality is perfect, and it can support operations that some databases cannot do (depending on the database situation).

4. What is the disconnected RecordSet?

With the client cursor, we can disconnect from the database and release the Connection object. Such a result set is the disconnected recordset. To illustrate:

Dim C as New ADODB. Connection
Dim R as New ADODB. Recordset
on Error GoTo handler
   
c.connectionstring = connectstr
C.cursorlocation = adUseClient
C.open
Set r.activeconnection = c
R.open sqltext, adOpenKeyset, adLockBatchOptimistic,-1
Set r.activeconnection = nothing   ' This disconnects the recordset.
C.close
Set c = Nothing
...
...    
' Recordset is now in disconnected state; does something with it.
R.close
Set r = Nothing