Re: 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 <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 <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.