Re: OLE DB Templates rules

Hi,
   I think that you don't have a problem with OLEDBPro
because OleDBPro internally dynamically supports
multiple accessors and all of accessor cooperate each
other. ATL consumer templates will have such a problem
because all of ATL consumer templates accessors except
user accessor just supports one accessor only. With
VC7, this is not changed. This is the bigger
difference between OLEDBPro and ATL consumer
templates. OLEDBPro gurantees handling all of types of
statements without having to create any accessor. No
matter how many new accessor are added with ATL
consumer templates, you may have to create accessors
in some cases especially when a parameterized
statement or stored procedure returns rowsets.

Regards,

Yuancai (Charlie) Ye
Use of free SocketPro package for creating super
client and server application,
See 30 well-tested and real OLEDB examples
at http://www.udaparts.com

--- Frederic Gos <[email protected]> wrote:
> 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
>
=== message truncated ===


__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.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.