Programming | Advanced 8.3.3 using connection Status
Saving the connection string to an application variable is a common technique that works as well as using a containing file. For example, you can add the following code to the Global.asa file:
Sub Application_OnStart ()
strconn = "PROVIDER=SQLOLEDB; Data Source=watcher; "& _
"Initial catalog=pubs; User Id=davids; Password=whisky "
Set application ("ConnectionString") = strconn
End Sub
In the ASP page, you can use the following code:
Set conpubs = Server.CreateObject ("ADODB. Connection ")
Conpubs.application ("ConnectionString")
Personally, I prefer to use a method of including files because I write many different examples of connecting to various servers and databases. Using an application method means that you must restart the application each time you have to close the browser. Readers can use any of the methods they like, and there is no difference in speed.
For the example in this section of this book, a connection.asp file containing a connection string is used as a containing file.
8.3.4 Connection Syntax
What do you do when you really want to connect to the data store, as described in the relevant theory? If you use an explicitly defined Connection object, you can use the Open method, which has the following syntax:
Connection. Open [ConnectionString], [UserID], [Password], [Options]
The parameters are shown in table 8-1:
Table 8-1 the parameters and description of the Open method
Parameters
Description
ConnectionString
A string that contains the details of the connection. Can be the name of the ODBC DSN, the name of the data link file, or the actual connection details
Userid
The name used by the user during the connection. Overwrite any user name provided in the connection string
Password
The password for the user. Overwrite any password provided in the connection string
Options
Can be adasyncconnect, which specifies that the connection be established asynchronously. Ignoring this parameter, a synchronous connection is established
Asynchronous connections are not used in the ASP environment because the scripting language cannot receive events from ADO.
Examples of 8.3.5 connections
Here are a few examples, where it is assumed that strconn contains a valid connection string.
To open a connection, use the Connection object's Open method. For example:
Set conpubs = server.connection ("ADODB. Connection ")
Conpubs.open strconn
' Some processing
Conpubs.close
You can also use the ConnectionString property:
Set conpubs = Server.CreateObject ("ADODB. Connection ")
conpubs.connectionstring = strconn
Conpubs.open
' Some processing
Conpubs.close
There is no difference between the two implementations, and if the previous method is used to implement the connection, the ConnectionString property is also assigned.
It is worth noting that, once a connection is made to the data store, ADO may change the value of the ConnectionString property. Don't worry, ADO just fills in some extra property values.
8.3.6 Connection Buffer Pool
The connection buffer pool (connection pool) is always confusing to many people, but the principle is very simple. When a connection is turned off, the connection is closed, as far as the user (and ADO) is concerned. But in fact OLE DB does not close the connection, just puts it in an inactive connection buffer pool. Any time a user (or someone else) opens a connection, OLE DB first detects whether a connection in the connection buffer pool has the same connection details. If so, the connection is made directly from the buffer pool. If not, a new connection is created for the user. To avoid wasting resources, clear the connection from the buffer pool after a default time period.
So, what are the advantages of it? Opening a connection may be one of the slowest operations in the operation, and connecting the buffer pool allows users to reconnect to the data store without recreating the connection. This is especially important for web sites that continuously open and close a large number of connections.
For ODBC connections, the connection buffer pool is controlled by the ODBC Data Source Administrator. For OLE DB, you cannot change the connection buffer pool (or the session buffer pool).
It is important to note that the connection buffer pool is not a connection share. A connection can be used again only after it has been closed by the customer.
House handling
In order for the connection buffer pool to take effect, you must ensure that the housekeeping (housekepping) is in an orderly state. This includes closing the connection objects in a timely manner so they can return to the buffer pool for reuse. You might think that constantly opening and closing connections is expensive for your system, but you have to weigh scalability-your application may be used by many people, and OLE db is very good at managing connection resources.
The general principle is to establish the connection as late as possible, and to close the connection as early as possible, so that the connection is open for the shortest period of time.
8.4 Recordsets
As mentioned earlier, it is not surprising that the recordset is the most commonly used object in ADO. After all, they contain the data. However, there is more to the recordset than you can imagine, and it is important to know how the data is saved and processed, because it provides more references for choosing which recordset to use.
There are different types of recordsets, and in some small places there are differences that can easily lead to errors. The first thing to be seriously talking about is the concept of the cursor.
8.4.1 cursor
The cursor (cursor) is a concept that puzzles many people, but it's actually very simple.
The cursor is used to manage the current position of the recordset and Recordset, which is handled by the current record pointer.
Is this not what the Recordset object does? Yes, but a recordset also relies on its cursor. This still doesn't answer the question of what the cursor is, so first look at a recordset, as shown in table 8-2:
au_id
au_lname
au_fname
PHONE
172-32-1176
White
Bob
408 496-7223
219-46-8915
Green
Marjorie
415 986-7020
238-95-7766
Carson
Cheryl
415 548-7723
267-41-2394
O ' Leary
Michael
408 286-2428
274-80-9391
Straight
Dean
415 834-2919
341-22-1782
Smith
Meander
913 843-0462
Here are six rows and four columns. Open a Recordset, and the current record is the first record, which is the record for Bob White. What do you use to identify the current record? With the current record pointer. So how do you handle this pointer? When you need to move to the next record or another record, it is done by the cursor. When you access the field of the current row, the cursor knows which row it is in, so you can return the correct value. If you try to move out of the last row of the recordset, the cursor will also handle it.
A good way to understand the cursor is to think of the cursor as a window that can be moved within the recordset. This window is as tall and as long as a single row of a recordset, so you can see only one row of data values at a time. When you move to another record, the window moves along as well.
You might think it's quite simple, but it's really important because what you can do with the cursor is determined by the type of cursor.
1. Cursor type
The type of cursor identifies the functionality that the cursor can provide. Here are four kinds of cursors:
· Static (adOpenStatic). The static cursor contains a static copy of the record. This means that after the recordset is set up, the contents of the Recordset are solid
[1] [2] Next page
