|
 |
activex_data_objects thread: Disconnected Recordsets (ADO 2.0)
Message #1 by "Parimel" <selvarap@a...> on Wed, 23 May 2001 20:17:20
|
|
Hi
I have a very basic question on disconnected recordset.
What does it really mean?
Does it mean it is disconnected from the connection object, so that
the "connection object" is available for other processes
OR
Does it mean it has no connection whatsoever to the database and
the "connection" is available for other processes
(Note diff. in the quoted words)
Take the sample code of disconnected recordset
Set mobjConn = New ADODB.Connection
mobjConn.Open "DSN=mydsn"
Set robjRS = New ADODB.Recordset
robjRS.ActiveConnection = mobjConn
robjRS.CursorLocation = adUseClient
robjRS.CursorType = adOpenStatic
robjRS.Open "Select * from something" , , , adLockReadOnly
***Set robjRS.ActiveConnection = Nothing**
''''mobjConn.Close
Now, If I close the connection after getting my recordset(uncomment the
last line), then I lose all the records in the recordset, which means the
recordset still has the knowledge of the database information and holds
the connection in some form.
I use ADO 2.0 and I'm fairly confident that this holds good for higher
versions as well.
Please correct me if I am going wrong on the understanding of disconnected
recordset.
-Parimel
Message #2 by "Pete Davis" <pdavis@q...> on Wed, 23 May 2001 20:03:37 -0400
|
|
Basically, it means this: If you have a disconnected recordset, and you
close the connection, you still have the records in the recordset. They are
no longer "connected" to the underlying database. You can do whatever you
want with them. There are then issues regarding "reconnecting" the data to
the database if you want to then update it.
We use disconnected recordsets a great deal. Here's an example: We have a
COM+ system. All access to our database is done through COM+ components on
the server. The client application, itself, never deals directly with the
database. If the client wants to retrieve data from the database, it calls a
component, which retrieves the recordset, disconnected, and returns that
recordset to the caller.
The connection is freed by the COM+ component, and generally, the COM+
component is freed by the client. The COM+ component is, therefore,
stateless. This means, not only is the connection now available for another
user, but so is the COM+ component. If later, the user wants to make an
update, they would then re-instantiate the COM+ component, pass the updated
recordset, and that component would then re-connect it to the database and
perform the update.
Pain in the butt, but very efficient and scalable.
Pete
----- Original Message -----
From: "Parimel" <selvarap@a...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Wednesday, May 23, 2001 8:17 PM
Subject: [activex_data_objects] Disconnected Recordsets (ADO 2.0)
> Hi
> I have a very basic question on disconnected recordset.
> What does it really mean?
> Does it mean it is disconnected from the connection object, so that
> the "connection object" is available for other processes
> OR
> Does it mean it has no connection whatsoever to the database and
> the "connection" is available for other processes
> (Note diff. in the quoted words)
> Take the sample code of disconnected recordset
>
> Set mobjConn = New ADODB.Connection
> mobjConn.Open "DSN=mydsn"
> Set robjRS = New ADODB.Recordset
> robjRS.ActiveConnection = mobjConn
> robjRS.CursorLocation = adUseClient
> robjRS.CursorType = adOpenStatic
> robjRS.Open "Select * from something" , , , adLockReadOnly
> ***Set robjRS.ActiveConnection = Nothing**
> ''''mobjConn.Close
> Now, If I close the connection after getting my recordset(uncomment the
> last line), then I lose all the records in the recordset, which means the
> recordset still has the knowledge of the database information and holds
> the connection in some form.
>
> I use ADO 2.0 and I'm fairly confident that this holds good for higher
> versions as well.
>
> Please correct me if I am going wrong on the understanding of disconnected
> recordset.
>
> -Parimel
Message #3 by "Selvaraju, Parimel" <selvarap@a...> on Thu, 24 May 2001 08:42:11 -0500
|
|
Well, the situation I'm trying to get disconnected recordset is using the
command object. I run a stored procedure which returns a recordset and I
disconnect from the database. This is where I lose my recordset.
lobjComm.CommandType = adCmdStoredProc
lobjComm.CommandText = vstrSPName
Set lobjComm.ActiveConnection = mobjConn
Set lobjRS = New ADODB.Recordset
lobjRS.CursorLocation = adUseClient
lobjRS.CursorType = adOpenStatic
lobjRS.Open lobjComm
Does this mean I can't have a disconnected recordset if I use the command
object? Is this realted to the OLE-DB provider?
Thanks,
-Parimel
-----Original Message-----
From: Pete Davis [mailto:pdavis@q...]
Sent: Wednesday, May 23, 2001 7:04 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] Re: Disconnected Recordsets (ADO 2.0)
Basically, it means this: If you have a disconnected recordset, and you
close the connection, you still have the records in the recordset. They are
no longer "connected" to the underlying database. You can do whatever you
want with them. There are then issues regarding "reconnecting" the data to
the database if you want to then update it.
We use disconnected recordsets a great deal. Here's an example: We have a
COM+ system. All access to our database is done through COM+ components on
the server. The client application, itself, never deals directly with the
database. If the client wants to retrieve data from the database, it calls a
component, which retrieves the recordset, disconnected, and returns that
recordset to the caller.
The connection is freed by the COM+ component, and generally, the COM+
component is freed by the client. The COM+ component is, therefore,
stateless. This means, not only is the connection now available for another
user, but so is the COM+ component. If later, the user wants to make an
update, they would then re-instantiate the COM+ component, pass the updated
recordset, and that component would then re-connect it to the database and
perform the update.
Pain in the butt, but very efficient and scalable.
Pete
----- Original Message -----
From: "Parimel" <selvarap@a...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Wednesday, May 23, 2001 8:17 PM
Subject: [activex_data_objects] Disconnected Recordsets (ADO 2.0)
> Hi
> I have a very basic question on disconnected recordset.
> What does it really mean?
> Does it mean it is disconnected from the connection object, so that
> the "connection object" is available for other processes
> OR
> Does it mean it has no connection whatsoever to the database and
> the "connection" is available for other processes
> (Note diff. in the quoted words)
> Take the sample code of disconnected recordset
>
> Set mobjConn = New ADODB.Connection
> mobjConn.Open "DSN=mydsn"
> Set robjRS = New ADODB.Recordset
> robjRS.ActiveConnection = mobjConn
> robjRS.CursorLocation = adUseClient
> robjRS.CursorType = adOpenStatic
> robjRS.Open "Select * from something" , , , adLockReadOnly
> ***Set robjRS.ActiveConnection = Nothing**
> ''''mobjConn.Close
> Now, If I close the connection after getting my recordset(uncomment the
> last line), then I lose all the records in the recordset, which means the
> recordset still has the knowledge of the database information and holds
> the connection in some form.
>
> I use ADO 2.0 and I'm fairly confident that this holds good for higher
> versions as well.
>
> Please correct me if I am going wrong on the understanding of disconnected
> recordset.
>
> -Parimel
$subst('Email.Unsub')
Message #4 by "Polsky, Eva" <EPolsky@s...> on Thu, 24 May 2001 10:05:03 -0400
|
|
I've just encountered the same problem and fortunately found a very neat
solution on the Web.
'***************************************************************************
********************
'Disconnected ADO recordset.
'A disconnected recordset is created by
' - specify the CursorLocation property as adUseClient.
' - then create the recordset as usual
' - set the ActiveConnection to Nothing, which disconnects it from the
server.
' about maintaining the connection.
'***************************************************************************
********************
This is function that I created and it works.
Add some errors checking
Public Function ExecDisconnectedRS(a_SQL As String) As ADODB.Recordset
'***************************************************************************
********************
'Disconnected ADO recordset.
'A disconnected recordset is created by
' - specify the CursorLocation property as adUseClient.
' - then create the recordset as usual
' - set the ActiveConnection to Nothing, which disconnects it from the
server.
' You can then pass the whole recordset to the destination application
without having to worry
' about maintaining the connection.
'***************************************************************************
********************
Dim lRs As New ADODB.Recordset
With lRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.ActiveConnection = myConnectStr
.Source = a_SQL
.Open
.ActiveConnection = Nothing
End With
Set ExecDisconnectedRS = lRs
Set lRs = Nothing
End Function
create a string to execute your store procedure like:
lStrSql = "exec dbo.vstrSPName " & a_Parameter
set yourRS = ExecDisconnectedRS(lStrSql)
-----Original Message-----
From: Selvaraju, Parimel [mailto:selvarap@a...]
Sent: Thursday, May 24, 2001 9:42 AM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] Re: Disconnected Recordsets (ADO 2.0)
Well, the situation I'm trying to get disconnected recordset is using the
command object. I run a stored procedure which returns a recordset and I
disconnect from the database. This is where I lose my recordset.
lobjComm.CommandType = adCmdStoredProc
lobjComm.CommandText = vstrSPName
Set lobjComm.ActiveConnection = mobjConn
Set lobjRS = New ADODB.Recordset
lobjRS.CursorLocation = adUseClient
lobjRS.CursorType = adOpenStatic
lobjRS.Open lobjComm
Does this mean I can't have a disconnected recordset if I use the command
object? Is this realted to the OLE-DB provider?
Thanks,
-Parimel
-----Original Message-----
From: Pete Davis [mailto:pdavis@q...]
Sent: Wednesday, May 23, 2001 7:04 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] Re: Disconnected Recordsets (ADO 2.0)
Basically, it means this: If you have a disconnected recordset, and you
close the connection, you still have the records in the recordset. They are
no longer "connected" to the underlying database. You can do whatever you
want with them. There are then issues regarding "reconnecting" the data to
the database if you want to then update it.
We use disconnected recordsets a great deal. Here's an example: We have a
COM+ system. All access to our database is done through COM+ components on
the server. The client application, itself, never deals directly with the
database. If the client wants to retrieve data from the database, it calls a
component, which retrieves the recordset, disconnected, and returns that
recordset to the caller.
The connection is freed by the COM+ component, and generally, the COM+
component is freed by the client. The COM+ component is, therefore,
stateless. This means, not only is the connection now available for another
user, but so is the COM+ component. If later, the user wants to make an
update, they would then re-instantiate the COM+ component, pass the updated
recordset, and that component would then re-connect it to the database and
perform the update.
Pain in the butt, but very efficient and scalable.
Pete
----- Original Message -----
From: "Parimel" <selvarap@a...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Wednesday, May 23, 2001 8:17 PM
Subject: [activex_data_objects] Disconnected Recordsets (ADO 2.0)
> Hi
> I have a very basic question on disconnected recordset.
> What does it really mean?
> Does it mean it is disconnected from the connection object, so that
> the "connection object" is available for other processes
> OR
> Does it mean it has no connection whatsoever to the database and
> the "connection" is available for other processes
> (Note diff. in the quoted words)
> Take the sample code of disconnected recordset
>
> Set mobjConn = New ADODB.Connection
> mobjConn.Open "DSN=mydsn"
> Set robjRS = New ADODB.Recordset
> robjRS.ActiveConnection = mobjConn
> robjRS.CursorLocation = adUseClient
> robjRS.CursorType = adOpenStatic
> robjRS.Open "Select * from something" , , , adLockReadOnly
> ***Set robjRS.ActiveConnection = Nothing**
> ''''mobjConn.Close
> Now, If I close the connection after getting my recordset(uncomment the
> last line), then I lose all the records in the recordset, which means the
> recordset still has the knowledge of the database information and holds
> the connection in some form.
>
> I use ADO 2.0 and I'm fairly confident that this holds good for higher
> versions as well.
>
> Please correct me if I am going wrong on the understanding of disconnected
> recordset.
>
> -Parimel
|
|
|
View Cart
