Re: OLE DB Templates rules

The way to write generalized code is to always check the properties, check the return codes, check the status fields in certain calls, check the HRESULTs, QueryInterface for everything, and call IMultipleResults::GetResult until it returns DB_S_NORESULT, checking for the for IRowset = NULL and the row count for each result.
 
I hear what you are saying and (mostly) agree. I'd rather have the ability to expose richer functionality than a lowest common denominator API. Although that sometimes means writing more code (or a template policy). ;-)
 
Writing external code that accesses database using internal code (stored procs, UDFs, etc) depends on cooperation (and standardization) between the database coder and data access coder. Or extra code to ensure properly handling any case. As you correctly point out.
 
Cheers,
Bob Beauchemin
http://staff.develop.com/bobb
 

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


The reason I say "no matter how i wrote the sp" is best descibed by example:
 
lets say i have a proc,
 
    set nocount on
 
    declare @id int
    select @id=null
 
    select @id=id from SomeTable where somthingistrue
    
    if @id is null 
    begin
        return 1
    end
 
    select name, address from someothertable where @id=id
    return 0
 
Now, if the return value is 1 I know there won't be a rowset. But if its 0 there is. 
 
The problem here is that i have to check the return value before trying to bind the 
returned rowset, but I can't because its not available before I close the rowset. 
 
I circumvent that by setting the return valuse to -1 in the c++ code, but it's not 
very elegant. 
 
The set nocount you mention is also part of what i mean. I allmost religiously use 
it and code accordingly in the c++ code. But one day I'm gonna forget it and my code
will crash... 
 
Maybe im just a bit confused right now and there actually is a set of consistent rules
for coding against SQLOLEDB. 
 
I think I will try to write that template library after all....
 
Thank for your help 
Frederic
 
 

 -----Original Message-----
From: Beauchemin, Bob [mailto:[email protected]]
Sent: Friday, August 02, 2002 6:59 PM
To: [email protected]
Subject: Re: [OLEDB_DEV] 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. 

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.