I'm updating an OleDb application. I didn't write the original logic however it closely resembles the OleDb samples provided by Microsoft. It uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and ICommandText interfaces to call a stored procedure, returning an IRowset instance. The ICommandWithParams interface is used to set parameter information for the stored procedures and IID_IAccessor to create bindings for the buffer to hold the returned data. ie. fairly standard OleDb code, as noted virtually identical to the samples provided by Microsoft. It works well and has done for a while. Currently the Rowsets are all forward only. All the update requires is that the Rowsets support being able to scroll through the data more than once. According to the documentation this is done by creating an instance of the ICommandProperties interface, filling some DBPROP structures and calling ICommandProperties::SetProperties. However when I do this I get an error on ICommandText::Execute. If I subsequently call ICommandProperties::GetProperties with the guidPropertySet of an DBPROPIDSET struct set to DBPROPSET_PROPERTIESINERROR it tells me that the properties I've set are invalid. This happened regardless of the properties I attempted to set. Out of curiosity I created a test application that ran a select command rather than calling a stored procedure. This ran perfectly. When I switched the logic so that it called a parameter-less stored procedure that returned the exact same data I got the errors again, suggesting that there is something different you need to do for stored procedures. However this is old technology now and I can't find any information (as well as being something of an OleDb noob!)
andrew_k (andre...@discussions.microsoft.com) writes: > I'm updating an OleDb application. I didn't write the original logic > however it closely resembles the OleDb samples provided by Microsoft. It > uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and > ICommandText interfaces to call a stored procedure, returning an IRowset > instance. The ICommandWithParams interface is used to set parameter > information for the stored procedures and IID_IAccessor to create > bindings for the buffer to hold the returned data. ie. fairly standard > OleDb code, as noted virtually identical to the samples provided by > Microsoft. It works well and has done for a while. Currently the Rowsets > are all forward only. All the update requires is that the Rowsets > support being able to scroll through the data more than once. According > to the documentation this is done by creating an instance of the > ICommandProperties interface, filling some DBPROP structures and calling > ICommandProperties::SetProperties. However when I do this I get an error > on ICommandText::Execute. If I subsequently call > ICommandProperties::GetProperties with the guidPropertySet of an > DBPROPIDSET struct set to DBPROPSET_PROPERTIESINERROR it tells me that > the properties I've set are invalid. This happened regardless of the > properties I attempted to set. Out of curiosity I created a test > application that ran a select command rather than calling a stored > procedure. This ran perfectly. When I switched the logic so that it > called a parameter-less stored procedure that returned the exact same > data I got the errors again, suggesting that there is something > different you need to do for stored procedures. However this is old > technology now and I can't find any information (as well as being > something of an OleDb noob!)
Exactly which properties did you try to set?
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
> andrew_k (andre...@discussions.microsoft.com) writes: > > I'm updating an OleDb application. I didn't write the original logic > > however it closely resembles the OleDb samples provided by Microsoft. It > > uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and > > ICommandText interfaces to call a stored procedure, returning an IRowset > > instance. The ICommandWithParams interface is used to set parameter > > information for the stored procedures and IID_IAccessor to create > > bindings for the buffer to hold the returned data. ie. fairly standard > > OleDb code, as noted virtually identical to the samples provided by > > Microsoft. It works well and has done for a while. Currently the Rowsets > > are all forward only. All the update requires is that the Rowsets > > support being able to scroll through the data more than once. According > > to the documentation this is done by creating an instance of the > > ICommandProperties interface, filling some DBPROP structures and calling > > ICommandProperties::SetProperties. However when I do this I get an error > > on ICommandText::Execute. If I subsequently call > > ICommandProperties::GetProperties with the guidPropertySet of an > > DBPROPIDSET struct set to DBPROPSET_PROPERTIESINERROR it tells me that > > the properties I've set are invalid. This happened regardless of the > > properties I attempted to set. Out of curiosity I created a test > > application that ran a select command rather than calling a stored > > procedure. This ran perfectly. When I switched the logic so that it > > called a parameter-less stored procedure that returned the exact same > > data I got the errors again, suggesting that there is something > > different you need to do for stored procedures. However this is old > > technology now and I can't find any information (as well as being > > something of an OleDb noob!)
> Exactly which properties did you try to set?
> -- > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Thanks for your reply. Initially I tried the following:
DBPROP_OWNUPDATEDELETE: VARIANT_FALSE DBPROP_OWNINSERT: VARIANT_FALSE DBPROP_OTHERINSERT: VARIANT_FALSE DBPROP_OTHERUPDATEDELETE: VARIANT_FALSE DBPROP_IRowsetLocate: VARIANT_TRUE - will set bookmarks as well DBPROP_CANSCROLLBACKWARDS: VARIANT_TRUE DBPROP_CANFETCHBACKWARDS: VARIANT_TRUE DBPROP_QUICKRESTART: VARIANT_TRUE
I started cutting out properties until I just had: DBPROP_IRowsetLocate: VARIANT_TRUE DBPROP_QUICKRESTART: VARIANT_TRUE
Then just: DBPROP_IRowsetLocate: VARIANT_TRUE
But ICommandText::Execute always returned the multiple errors HRESULT. As soon as I removed the code to set properties everything ran perfectly once again.
> On Aug 30, 12:36 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> > andrew_k (andre...@discussions.microsoft.com) writes: > > > I'm updating an OleDb application. I didn't write the original logic > > > however it closely resembles the OleDb samples provided by Microsoft. It > > > uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and > > > ICommandText interfaces to call a stored procedure, returning an IRowset > > > instance. The ICommandWithParams interface is used to set parameter > > > information for the stored procedures and IID_IAccessor to create > > > bindings for the buffer to hold the returned data. ie. fairly standard > > > OleDb code, as noted virtually identical to the samples provided by > > > Microsoft. It works well and has done for a while. Currently the Rowsets > > > are all forward only. All the update requires is that the Rowsets > > > support being able to scroll through the data more than once. According > > > to the documentation this is done by creating an instance of the > > > ICommandProperties interface, filling some DBPROP structures and calling > > > ICommandProperties::SetProperties. However when I do this I get an error > > > on ICommandText::Execute. If I subsequently call > > > ICommandProperties::GetProperties with the guidPropertySet of an > > > DBPROPIDSET struct set to DBPROPSET_PROPERTIESINERROR it tells me that > > > the properties I've set are invalid. This happened regardless of the > > > properties I attempted to set. Out of curiosity I created a test > > > application that ran a select command rather than calling a stored > > > procedure. This ran perfectly. When I switched the logic so that it > > > called a parameter-less stored procedure that returned the exact same > > > data I got the errors again, suggesting that there is something > > > different you need to do for stored procedures. However this is old > > > technology now and I can't find any information (as well as being > > > something of an OleDb noob!)
> Thanks for your reply. Initially I tried the following:
> DBPROP_OWNUPDATEDELETE: VARIANT_FALSE > DBPROP_OWNINSERT: VARIANT_FALSE > DBPROP_OTHERINSERT: VARIANT_FALSE > DBPROP_OTHERUPDATEDELETE: VARIANT_FALSE > DBPROP_IRowsetLocate: VARIANT_TRUE - will set bookmarks as well > DBPROP_CANSCROLLBACKWARDS: VARIANT_TRUE > DBPROP_CANFETCHBACKWARDS: VARIANT_TRUE > DBPROP_QUICKRESTART: VARIANT_TRUE
> I started cutting out properties until I just had: > DBPROP_IRowsetLocate: VARIANT_TRUE > DBPROP_QUICKRESTART: VARIANT_TRUE
> Then just: > DBPROP_IRowsetLocate: VARIANT_TRUE
> But ICommandText::Execute always returned the multiple errors HRESULT. > As soon as I removed the code to set properties everything ran > perfectly once again.
Just to add, I started attempting to set DBPROPSET_ROWSET properties because my call to: pIRowset->RestartPosition(DB_NULL_HCHAPTER) returned error 0x80040e18 "Rowset position cannot be restarted". Adding properties results in error 0x80040e21"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." when calling ICommandText::Execute. If I set the DBPROP_IRowsetLocate property via ICommandText::SetProperties a subsqequent call to ICommandText::GetProperties with the guidPropertySet of the DBPROPIDSET struct set to DBPROPSET_PROPERTIESINERROR returns 0x82: the enum value for DBPROP_IRowsetLocate.
andrew_k (andre...@discussions.microsoft.com) writes: > I'm updating an OleDb application. I didn't write the original logic > however it closely resembles the OleDb samples provided by Microsoft. It > uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and > ICommandText interfaces to call a stored procedure, returning an IRowset > instance. The ICommandWithParams interface is used to set parameter > information for the stored procedures and IID_IAccessor to create > bindings for the buffer to hold the returned data. ie. fairly standard > OleDb code, as noted virtually identical to the samples provided by > Microsoft.
Is that the GetFastForwardCursor sample you have in mind?
Anyway, I think can can guess which is the culprit:
> andrew_k (andre...@discussions.microsoft.com) writes: > > I'm updating an OleDb application. I didn't write the original logic > > however it closely resembles the OleDb samples provided by Microsoft. It > > uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and > > ICommandText interfaces to call a stored procedure, returning an IRowset > > instance. The ICommandWithParams interface is used to set parameter > > information for the stored procedures and IID_IAccessor to create > > bindings for the buffer to hold the returned data. ie. fairly standard > > OleDb code, as noted virtually identical to the samples provided by > > Microsoft.
> Is that the GetFastForwardCursor sample you have in mind?
> Anyway, I think can can guess which is the culprit:
Thanks for your reply. I've had to work on other things for the past week hence haven't had a change to get back to this. I wasn't able to access your source code? Has this been removed?
If I add the DBPROP_IRowsetLocate property and no others I get the following error:
HResult: 0x80040e21 Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Source: Microsoft OLE DB Provider for SQL Server
If I run this under SQL Server Profiler I can see that when I add any properties to the command the execute call attempts to create a cursor. If I copy the call from Profiler into Query Analyser and run it I get:
Server: Msg 16937, Level 16, State 1, Procedure Tex_TEST4, Line 1 A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor. Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9
If I add the DBPROP_CLIENTCURSOR property it makes no difference. ie. same result (same if I add DBPROP_SERVERCURSOR). The stored procedure in question performs a simple select on the basis of 2 input parameters. Switching to a stored procedure with no parameters makes no difference.
"Erland Sommarskog" wrote: > andrew_k (andrewklinkha...@gmail.com) writes: > > Thanks for your reply, much appreciated. It's a static client side > > rowset that I need, but one that lets me call > > IRowset::RestartPosition. Basically something akin to an ADO > > adOpenStatic cursor. I have the MS OleDb 2.0 reference but have to > > admit I'm struggling to know exactly what to do. How do I create a > > static client side rowset that lets me call IRowset::RestartPosition > > when calling a stored procedure?
> I took the GetFastForwardCursor sample, and I created a stored procedure:
> create procedure get_vendor_names as > select Name from Purchasing.Vendor
> Then I changed the command text to:
> L"{call get_vendor_names}"
> At the end of ExecuteAndDisplay I added call to IRowset::RestartPosition > and a goto to execute the for loop twice. This code worked and the result > was displayed twice.
> However, the call to RestartPosition did not return S_OK, but > DB_S_COMMANDREEXECUTED, and Profiler also confirmed that this was the > case. Furthermore, this also happened when the command text was the > original statement.
> My conclusion of this is that you probably don't want > IRowset::RestartPosition, but you want to look at the IRowsetLocate > or IRowsetScroll interfaces. To use them, you probably have to set the > corresponding command properties.
> As for how to get a static client-side cursor, I think you should > just rip out the properties you set now. They give you a server-side > cursor, and that is not what you want.
> I attach my slightly modified version of the GetFastForwardCursor > sample, in case you've been looking at another sample, or an older > version of it.
> -- > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
andrew_k (andr...@discussions.microsoft.com) writes: > Thanks for your reply. I've had to work on other things for the past > week hence haven't had a change to get back to this. I wasn't able to > access your source code? Has this been removed?
I seem to recall that you access this newsgroup through Google. Google users seem to have difficulties with attachments. And, well, when I tried to post it from the server I normally read from, I was not permitted to because it was considered binary. I just posted it through Microsoft's newsserver and did not think more of it.
I've pasted the code at the end. Beware that comments etc may be wrapped.
> If I add the DBPROP_IRowsetLocate property and no others I get the > following error:
I read the documentation a little closer, and I get the impression that this is not a property you add as a consumer, but one that the provider sets and you can query whether the interface is available, before you request it.
> If I run this under SQL Server Profiler I can see that when I add any > properties to the command the execute call attempts to create a cursor. > If I copy the call from Profiler into Query Analyser and run it I get:
> Server: Msg 16937, Level 16, State 1, Procedure Tex_TEST4, Line 1 > A server cursor is not allowed on a remote stored procedure or stored > procedure with more than one SELECT statement. Use a default result set or > client cursor. > Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9
Maybe you should post the code of the procedure?
> If I add the DBPROP_CLIENTCURSOR property it makes no difference. ie. > same result (same if I add DBPROP_SERVERCURSOR). The stored procedure in > question performs a simple select on the basis of 2 input parameters. > Switching to a stored procedure with no parameters makes no difference.
When I tried specifying DBPROP_CLIENTCURSOR, I got an error when setting properties. Looking in the OLE DB docs, I see that you are normally supposed to set this one, and it is not listed in Books Online for SQL Server Native Client.
Here is my modified version GetFastForwardCursor that shows that using RestartLocation reruns the query.
//===================================================================== // // File: GetFastForwardCursor.cpp // Summary: The following example shows how to set the rowset properties to // obtain a FAST_FORWARD cursor. After the properties are set, a // SELECT statement is executed to retrieve and display the Name column of the // Purchasing.Vendor table in the AdventureWorks database. // Date: December 7, 2005 // //--------------------------------------------------------------------- // // This file is part of the Microsoft SQL Server Code Samples. // Copyright (C) Microsoft Corporation. All rights reserved. // //This source code is intended only as a supplement to Microsoft //Development Tools and/or on-line documentation. See these other //materials for detailed information regarding Microsoft code samples. // //THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF //ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO //THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A //PARTICULAR PURPOSE. // //===================================================================== #define INITGUID #define DBINITCONSTANTS #define OLEDBVER 0x0250 // to include correct interfaces
// Connect to the server and create a command object. int InitializeAndConnect();
// Set the properties to get a FAST_FORWARD cursor. int SetRowsetProperties();
// This function executes a command and displays the results. int ExecuteAndDisplay();
// Release memory. void Cleanup();
int main() { if (InitializeAndConnect() == -1) { // Handle error. printf("Failed to initialize and connect to the server.\n"); return -1; }
// Set the row properties to FAST_FORWARD cursor. if (SetRowsetProperties() == -1) { // Handle error. printf("Failed to set the rowset properties.\n"); return -1; }
// Execute a command and display the results. if (ExecuteAndDisplay() == -1) { // Handle error. printf("Failed to execute a command and display the results.\n"); return -1; }
// Create an instance of Microsoft SQL Server Native Client OLE DB Provider. if ( FAILED( hr = CoCreateInstance(CLSID_SQLNCLI10, NULL, CLSCTX_INPROC_SERVER, IID_IDBProperties, (void **) &pIDBProperties ))) { // Handle errors here. return -1; }
// Set up the connection properties. dbProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE; dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED; dbProp[0].colid = DB_NULLID; V_VT(&(dbProp[0].vValue)) = VT_BSTR;
CLEANUP: // Release all the objects not needed anymore. pIDBProperties->Release(); if ( pIDBCreateSession ) pIDBCreateSession->Release(); if ( pIDBCreateCommand ) pIDBCreateCommand->Release();
return iRetVal;
}
int SetRowsetProperties() { HRESULT hr = S_OK; ICommandProperties* pICommandProperties = NULL; DBPROPSET dbPropSet; DBPROP dbProp[5]; int iRetVal = 0;
// Get an ICommandProperties object. if ( FAILED( hr = pICommandText->QueryInterface( IID_ICommandProperties, (void **) &pICommandProperties ))) { // Handle errors here. return -1; }
// Set up the properties to get a FAST_FORWARD cursor. dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR; dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED; dbProp[0].colid = DB_NULLID; V_VT(&(dbProp[0].vValue)) = VT_BOOL; V_BOOL(&(dbProp[0].vValue)) = VARIANT_TRUE;
Thanks for your reply. I really appreciate all the trouble you have gone to helping me out here. I see you are using the SQL Native Client for SQL Server 2005. I'm running SQL Server 2000 here. When I run your code it returned an error in the call to pICommandText->Execute. Under Profiler I can see it's trying to open a cursor (exec sp_cursoropen). If I cut and paste the call from Profiler into Query Analyser I get:
Server: Msg 16937, Level 16, State 1, Procedure get_names, Line 1 A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor. Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9 The cursor was not declared.
The stored procedure called is a test one that simply SELECTs the name field from a table. ie.
SELECT name FROM TableName
There are no parameters involved.
Out of curiosity I wrote some ADO code that called the same stored procedure using an adOpenStatic cursor and looped through the resulting recordset a couple of times. The call under Profiler was a simple 'exec', not 'exec sp_cursoropen'. ie. it didn't attempt to create a cursor. The call only occurred once under Profiler despite looping through the recordset multiple times. I need to replicate this behaviour using OleDb. If I remove all properties from the command object though I get a forward only rowset.
"Erland Sommarskog" wrote: > andrew_k (andr...@discussions.microsoft.com) writes: > > Thanks for your reply. I've had to work on other things for the past > > week hence haven't had a change to get back to this. I wasn't able to > > access your source code? Has this been removed?
> I seem to recall that you access this newsgroup through Google. Google > users seem to have difficulties with attachments. And, well, when I > tried to post it from the server I normally read from, I was not > permitted to because it was considered binary. I just posted it through > Microsoft's newsserver and did not think more of it.
> I've pasted the code at the end. Beware that comments etc may be wrapped.
> > If I add the DBPROP_IRowsetLocate property and no others I get the > > following error:
> I read the documentation a little closer, and I get the impression that > this is not a property you add as a consumer, but one that the provider > sets and you can query whether the interface is available, before you > request it.
> > If I run this under SQL Server Profiler I can see that when I add any > > properties to the command the execute call attempts to create a cursor. > > If I copy the call from Profiler into Query Analyser and run it I get:
> > Server: Msg 16937, Level 16, State 1, Procedure Tex_TEST4, Line 1 > > A server cursor is not allowed on a remote stored procedure or stored > > procedure with more than one SELECT statement. Use a default result set or > > client cursor. > > Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9
> Maybe you should post the code of the procedure?
> > If I add the DBPROP_CLIENTCURSOR property it makes no difference. ie. > > same result (same if I add DBPROP_SERVERCURSOR). The stored procedure in > > question performs a simple select on the basis of 2 input parameters. > > Switching to a stored procedure with no parameters makes no difference.
> When I tried specifying DBPROP_CLIENTCURSOR, I got an error when > setting properties. Looking in the OLE DB docs, I see that you are > normally supposed to set this one, and it is not listed in Books > Online for SQL Server Native Client.
> Here is my modified version GetFastForwardCursor that shows that using > RestartLocation reruns the query.
> //===================================================================== > // > // File: GetFastForwardCursor.cpp > // Summary: The following example shows how to set the rowset properties to > // obtain a FAST_FORWARD cursor. After the properties are set, a > // SELECT statement is executed to retrieve and display the Name column of the > // Purchasing.Vendor table in the AdventureWorks database. > // Date: December 7, 2005 > // > //--------------------------------------------------------------------- > // > // This file is part of the Microsoft SQL Server Code Samples. > // Copyright (C) Microsoft Corporation. All rights reserved. > // > //This source code is intended only as a supplement to Microsoft > //Development Tools and/or on-line documentation. See these other > //materials for detailed information regarding Microsoft code samples. > // > //THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF > //ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO > //THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A > //PARTICULAR PURPOSE. > // > //===================================================================== > #define INITGUID > #define DBINITCONSTANTS > #define OLEDBVER 0x0250 // to include correct interfaces
> // Connect to the server and create a command object. > int InitializeAndConnect();
> // Set the properties to get a FAST_FORWARD cursor. > int SetRowsetProperties();
> // This function executes a command and displays the results. > int ExecuteAndDisplay();
> // Release memory. > void Cleanup();
> int main() { > if (InitializeAndConnect() == -1) { > // Handle error. > printf("Failed to initialize and connect to the server.\n"); > return -1; > }
> // Set the row properties to FAST_FORWARD cursor. > if (SetRowsetProperties() == -1) { > // Handle error. > printf("Failed to set the rowset properties.\n"); > return -1; > }
> // Execute a command and display the results. > if (ExecuteAndDisplay() == -1) { > // Handle error. > printf("Failed to execute a command and display the results.\n"); > return -1; > }
> Cleanup(); > }
> int InitializeAndConnect() { > HRESULT hr = S_OK;
andrew_k (andr...@discussions.microsoft.com) writes: > Thanks for your reply. I really appreciate all the trouble you have gone > to helping me out here. I see you are using the SQL Native Client for > SQL Server 2005. I'm running SQL Server 2000 here. When I run your code > it returned an error in the call to pICommandText->Execute. Under > Profiler I can see it's trying to open a cursor (exec sp_cursoropen). If > I cut and paste the call from Profiler into Query Analyser I get:
> Server: Msg 16937, Level 16, State 1, Procedure get_names, Line 1 > A server cursor is not allowed on a remote stored procedure or stored > procedure with more than one SELECT statement. Use a default result set or > client cursor. > Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9 > The cursor was not declared.
> The stored procedure called is a test one that simply SELECTs the name > field from a table. ie.
> SELECT name FROM TableName
And that is really a table? In the local database? By chance, it does not happen to be a view including a linked-server table?
Try this. In tempdb, create this procedure:
CREATE PROCEDURE get_names AS SELECT name FROM sysobjects WHERE type = 'P'
The try the code below. It's the same file, but I've changed it to use SQLOLEDB. Also, I've changed DBPROP_SERVERCURSOR to VARIANT_FALSE. You will find when you run it, that it now runs an EXEC statement. Still twice though.
Also, could you post the output from "SELECT @@version"?
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
//===================================================================== // // File: GetFastForwardCursor.cpp // Summary: The following example shows how to set the rowset properties to // obtain a FAST_FORWARD cursor. After the properties are set, a // SELECT statement is executed to retrieve and display the Name column of the // Purchasing.Vendor table in the AdventureWorks database. // Date: December 7, 2005 // //--------------------------------------------------------------------- // // This file is part of the Microsoft SQL Server Code Samples. // Copyright (C) Microsoft Corporation. All rights reserved. // //This source code is intended only as a supplement to Microsoft //Development Tools and/or on-line documentation. See these other //materials for detailed information regarding Microsoft code samples. // //THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF //ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO //THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A //PARTICULAR PURPOSE. // //===================================================================== #define INITGUID #define DBINITCONSTANTS #define OLEDBVER 0x0250 // to include correct interfaces
// Connect to the server and create a command object. int InitializeAndConnect();
// Set the properties to get a FAST_FORWARD cursor. int SetRowsetProperties();
// This function executes a command and displays the results. int ExecuteAndDisplay();
// Release memory. void Cleanup();
int main() { if (InitializeAndConnect() == -1) { // Handle error. printf("Failed to initialize and connect to the server.\n"); return -1; }
// Set the row properties to FAST_FORWARD cursor. if (SetRowsetProperties() == -1) { // Handle error. printf("Failed to set the rowset properties.\n"); return -1; }
// Execute a command and display the results. if (ExecuteAndDisplay() == -1) { // Handle error. printf("Failed to execute a command and display the results.\n"); return -1; }
CLEANUP: // Release all the objects not needed anymore. pIDBProperties->Release(); if ( pIDBCreateSession ) pIDBCreateSession->Release(); if ( pIDBCreateCommand ) pIDBCreateCommand->Release();
return iRetVal;
}
int SetRowsetProperties() { HRESULT hr = S_OK; ICommandProperties* pICommandProperties = NULL; DBPROPSET dbPropSet; DBPROP dbProp[5]; int iRetVal = 0;
// Get an ICommandProperties object. if ( FAILED( hr = pICommandText->QueryInterface( IID_ICommandProperties, (void **) &pICommandProperties ))) { // Handle errors here. return -1; }
// Set up the properties to get a FAST_FORWARD cursor. dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR; dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED; dbProp[0].colid = DB_NULLID; V_VT(&(dbProp[0].vValue)) = VT_BOOL; V_BOOL(&(dbProp[0].vValue)) = VARIANT_FALSE;
HROW* pRows = new HROW[10]; HACCESSOR hAccessor = 0; DBBINDING Bind[1]; int iRetVal = 0;
if (!pRows) return -1;
// Set the command text. if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"{call get_names}"))) // if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"select Name from Purchasing.Vendor"))) // Handle errors and free the memory here. return -1;
// Execute the command. if ( FAILED( hr = pICommandText->Execute( NULL, IID_IRowset, NULL, NULL, (IUnknown **) &pIRowset ))) // Handle errors and free the memory here. return -1;
// Get an IAccessor interface. if ( FAILED( hr = pIRowset->QueryInterface( IID_IAccessor, (void **) &pIAccessor))) { // Handle errors and free the memory here. iRetVal = -1; goto CLEANUP; }
// Create an accessor. if ( FAILED( hr = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, Bind, 0, &hAccessor, NULL))) { // Handle errors and free the memory here. iRetVal = -1; goto CLEANUP; }
// Allocate memory for the data. pData = new BYTE[102]; if (!(pData /* = new BYTE[102] */ )) { // Handle errors and free the memory here. iRetVal = -1; goto CLEANUP; }
// Loop through all of the rows. int alfine = 0; DaCapo: if (alfine++ == 2) goto Coda; for ( ; ; ) {
|
