Re: OLE DB Templates rules

I'm unclear about what you mean by "no matter how i wrote the sp". What different ways could you write a stored procedure that would effect your code? In general, the code would also be sensitive (even in the SQLOLEDB provider) to what behavioral properties you specific to the behavioral properties you specify in DBPROPSET_ROWSET before calling ICommand::Execute. For example, setting DBPROP_COMMITPRESERVE has an effect on whether a Rowset is valid after commiting a transaction.
 
Take a look at the description of these properties in the SQLOLEDB-specific docs (and confirm the behaviors through testing ;-) and you'll be able to write your policy.
 
Another thing to remember about SQLOLEDB is that the number of results (Rowsets or just counts of rows affected) varies depending on whether you have 'SET NOCOUNT ON' in your sproc. For example:
 
INSERT into foo ...
SELECT * from foo
INSERT into foo ...
 
returns 3 sets of results (rows affected plus possible IRowset pointer) if you have NOCOUNT off. If SET NOCOUNT ON is added it only returns the results of the SELECT.
 
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
 

-----Original Message-----
From: Frederic Gos [mailto:[email protected]]
Sent: Friday, August 02, 2002 9:34 AM
To: [email protected]
Subject: Re: [OLEDB_DEV] OLE DB Templates rules


Maybe I should concretize a bit. For now, I don't care about anything other than SQL server.
 
My problem or wish if you like is I want to be able to write a set of functions that will work 
everytime no matter how i wrote the sp. Now, i do cut and paste of these 10-15 lines of
code everytime i need to call a new SP. This is very error prone. 
 
I have a dream of a policy based template lib for OLEDB... 
 
Something like 
 
    CStoredProc< CAccessor<MyStruct>, CRowset, OutputParamsPolicy, ReturnsRowsetPolicy,  ReturnValuePolicy, RaiseExceptionPolicy > 
 
Would that be feasible?
 
Thanks for your coments. As allways...
 
cheers
Frederic
 
 
-----Original Message-----
From: Beauchemin, Bob [mailto:[email protected]]
Sent: Friday, August 02, 2002 5:59 PM
To: [email protected]
Subject: Re: [OLEDB_DEV] OLE DB Templates rules



OLE DB is a specification for abstracting data access. It does not attempt to abstract away the difference between data stores. Therefore, in some cases, there are no rules but a set of possible behaviors and a way for the provider to indicate the behavior(s) it supports.
 
WRT the rules you are wanting, these differ among providers. When using SQL Server, output parameters can only be returned when rowsets have been released. In Oracle (using Oracle's provider, but not using MSDAORA provider from Microsoft), this is not the case; you can get output parameters before rowset pointers (actually pointers to refcursors) have been released. The property that tell you the behavior of the provider is in DBPROPSET_DATASOURCEINFO (IIRC) and is named DBPROP_OUTPUTPARAMETERAVAILABILITY. It is a set of bitswitches that you can query to find out when output parameters are available.
 
WRT providers returning the number of rows affected from a SELECT statement, this varies not only among providers, but within the same provider. Although Access will return a real value (last I looked), SQL Server only returns the number of rows for KEYSET and STATIC server cursors (the cursor you get is defined by DBPROPSET_ROWSET properties). When you use cursorless mode (the default) then rows affected return -1 on a SELECT. This is the provider's way of telling you that information is unavailable. 
 
The way to do the right thing every time is to query the appropriate properties or information from the provider (this info is sometimes in the provider-specific docs as well) then use the appropriate behaviors. A la QueryInterface in COM. If you QI for an interface and get back NULL, that indicates that the provider does not support that interface (OLE DB consists of required and optional interfaces) which means that the data source (database) does not support that behavior (or you have forgotten to specify the needed properties to request the behavior).
 
The point is to allow providers to expose as much rich functionality as their database permits, rather than a lowest common denominator approach.
 
Hope this helps clear things up,
Bob Beauchemin
http://staff.develop.com/bobb
 

-----Original Message-----
From: Frederic Gos [mailto:[email protected]]
Sent: Friday, August 02, 2002 7:13 AM
To: [email protected]
Subject: [OLEDB_DEV] OLE DB Templates rules



Hi, 

I'n trying to get the rules straight when using teh OLE DB templates of VC7. 

Depending on how you wrote the sp's, the order og the tempalte functions 
you must call change. As an example, if you have an SP with output params 
that also retuns a rowset, you have to call close before getting access to the 
params. 

The question is: Is there a summary of these rules? Are they consistent? 

What I'm trying to do is to write code that will do the right thing everytime, no matter 
how the sp was written. The only assumption would be that all metadata is known at compile 
time. No Dynamic or Manual accessors.  

Is that naive? 
Do I even make sense? :-) 

cheers 
Frederic 

You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV, or subscribe to other DevelopMentor lists at http://discuss.develop.com. 

You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV, or subscribe to other DevelopMentor lists at http://discuss.develop.com. 

You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV, or subscribe to other DevelopMentor lists at http://discuss.develop.com. 


You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV,
or subscribe to other DevelopMentor lists at http://discuss.develop.com.