Re: parameterized queries: 3 problems

1. You have

ptr->Value = i;

These look wrong. You say that the parametised type is adInteger.
That translates as type "long" not "int" and the type for the variant does
matter.
Generally variants support "long" and "short" but _NOT_ "int".

2. This looks wrong:

_RecordsetPtr rs = cmd->Execute(&vtMissing, &vtMissing, adCmdStoredProc)

In the advice here:
http://msdn2.microsoft.com/en-us/library/ms675103.aspx
it says


"Three methods are exceptions to the typical use of vtMissing. These are the
Execute methods of the Connection and Command objects, and the NextRecordset
method of the Recordset object. The following are their signatures:"


and explains


The parameters, RecordsAffected and Parameters, are pointers to a Variant.
Parameters is an input parameter which specifies the address of a Variant
containing a single parameter, or array of parameters, that will modify the
command being executed. RecordsAffected is an output parameter that
specifies the address of a Variant, where the number of rows affected by the
method is returned.

In the Command object Execute method, indicate that no parameters are
specified by setting Parameters to either &vtMissing (which is recommended)
or to the null pointer (that is, NULL or zero (0)). If Parameters is set to
the null pointer, the method internally substitutes the equivalent of
vtMissing, and then completes the operation.
In all the methods, indicate that the number of records affected should not
be returned by setting RecordsAffected to the null pointer. In this case,
the null pointer is not so much a missing parameter as an indication that
the method should discard the number of records affected.

Thus, for these three methods, it is valid to code something such as:

pConnection->Execute("commandText", NULL, adCmdText);
pCommand->Execute(NULL, NULL, adCmdText);
pRecordset->NextRecordset(NULL);


so you should just use

_RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdStoredProc);

Klaus:

For Visual C++ & ADO, I cull my information from several sources:
You also have to bear in mind that much of the documentation is old and not
updated, so I get my "best " practice from all of them.

(i) You want to digest everything about Visual C++ & ADO from the pages
under
http://msdn2.microsoft.com/en-us/library/ms675043.aspx

(ii) Everything under ADO is mostly under here
http://msdn2.microsoft.com/en-us/library/ms675532.aspx
I sometimes check the properties, methods, events under these because just
occasionally, Microsoft updates the pages with some more detail.

(iii) The Microsoft VC++ ADO examples are poor. They are not exception-safe.
And I find it unbelievable in that they create these smart-pointers for ADO
code (which are good) and then _NOT_ use them in the examples. How
bone-headed in is that? It is intentionally teaching C++ programmers bad
habits.

Also the Microsoft VC++ ADO example on Event handling is over-elaborate.
A colleague of mine simplified the code to illustrate _just_ ADO event
handling.

(iv) This article
"How to Improve the Performance of Your MDAC Application"
http://www.microsoft.com/technet/prodtechnol/sscomm/reskit/mdacapp.mspx
is ages old. But very relevant to ADO. Unfortunately it is in Visual BASIC.
But it is not too bad to translate. 3 things on it
(i) I use the Collect method in Recordset's as that is the 2nd fastest way
of reading/writing to a field. It is semi-documented. But since ADO is a COM
object, there is no way it will cease to exist.
(ii) The advice for Singleton Selects pre-dates the launch of ADO 2.6. I use
the Record object for reading single records.
(iii) Because it is geared to VB, it missed out on IADORecordBinding with
C/C++ struct's.
That is the fastest method of reading/writing to Recordset's as it bypasses
variants and BSTRs.

(v) Articles by Bill Vaughn are very good (but usually in VB).
His original "Best Practices for ADO" was amazing.
See ADO Performance "Best Practices"
http://www.betav.com/Files/Content/Whitepapers/ADO%20Performance.htm

Stephen Howe



.

Relevant Pages

  • Re: Problem with Deletes in code
    ... DoCmd.RunSQL to execute the delete query. ... 'All records in rsSourceProjects will be for the same project, ... problem with ADO interacting with the tables, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Vorgang ist =?ISO-8859-1?Q?f=FCr_geschlossenes_Objekt_?= =?ISO-8859-1?Q?nicht_zu
    ... ich versuche mit ADO auf eine MS-SQL 2005 Instanz zu zugreifen. ... Zeile positioniert (und ein adForwardOnly Resultset wie es Execute ... 1.: SELECT object_id ...
    (microsoft.public.de.sqlserver)
  • Re: foreign key ado syntax
    ... in my previous ado approach I was getting a "Values ..." ... Sub ToDatabase ... oBlockData .ToDatabase oDbController ... execute for each record(instead of doing a batch...which i've never done ...
    (microsoft.public.vb.database.ado)
  • Re: ADO Error - ODBC Microsft Access Driver Toofew parameters. Expecte
    ... .Execute rowsAffected ... I generally use late binding when posting, ... there to be a difference of behaviour between ADO and DAO. ... Doubling up the "offending character" through the replace function, ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I make the ON DELETE CASCADE clause in the ALTER TABLE CON.
    ... I believe that cascading updates/deletes can only be included in your DDL ... Since the interfaces uses the native Access libraray, ... You have to execute the query from code on an ADO connection. ...
    (microsoft.public.access.modulesdaovba)
Loading