OLEDB Parameterised Query (beginner OLEDB) 
Author Message
 OLEDB Parameterised Query (beginner OLEDB)

 Hi All,

I hope some-one can help. I have figured out, fianlly, how to get OLEDB
to return result sets. Now I am trying to figure out how to get it to
run a parameterized query.

I do the following:

1) Create a CDataSource object,
2) Creat a CSession object;
3) Create a CCommand object using CManualAccessor and CRowset,
4) I then created a DBPARAMINFO strucutre,
5)I then try to use SetParamInfo to bind the paramter info to the
CCommnad object but this fails.

My questions are as follows:
How do I create and run a paramterized query?
How do I create a query that uses paramters and also returns a normal
rowset (i.e. requires a normal accessor as well as a paramter
accessor)? Do I first creat a dbparaminfo strucutre, then create a
parameteraccessor, then bond the parameters and then set the parmater
info?

My code is attached below -  I know its incredibly messy. I have been
trying different things to get it to work but no luck. I keep on
getting assert errors on the SetParamterInfo method call.

Regards
Mark

void CADOTESTView::OnButton1()
{
USES_CONVERSION;

HRESULT hr;
CDataSource db;
CSession session;
CCommand  <CManualAccessor, CRowset>rs;
char *str;
ULONG Count=-1;
CColumns col;
BSTR bstr;
CDBPropSet dbinit(DBPROPSET_DBINIT);
CDBPropSet propset(DBPROPSET_ROWSET);

dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, "SSPI");
dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
dbinit.AddProperty(DBPROP_INIT_CATALOG,  "pubs");
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, "xxxxx");
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);

hr = db.OpenWithServiceComponents("SQLOLEDB.1", &dbinit);   if (FAILED
(hr))
  return;

  hr = session.Open(db);
  if (FAILED(hr))
  return ;

  propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
  propset.AddProperty(DBPROP_IRowsetScroll, true);
  propset.AddProperty(DBPROP_IRowsetChange, true);
  propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE );

if (col.Open (session,NULL,NULL,"Stores")==S_OK)
  {
  while (col.MoveNext()==S_OK)
  {
    Count++;
    TRACE ("COL Name:%s\n\r",col.m_szColumnName);
  }
  }

char params[]="Portland";
DBPARAMBINDINFO param;
param = new DBPARAMINFO;

param.pwszDataSourceType= T2OLE(_T("DBTYPE_STR"));
param.pwszName= OLESTR("City");
param.dwFlags = DBPARAMFLAGS_ISINPUT;
param.ulParamSize = 10;
param.bPrecision =0;
param.bScale =0;
ULONG Ordinal=1;
OLECHAR *Store=NULL;
DBPARAMINFO *pParamInfo=NULL;
ULONG *p=NULL;

rs.CreateParameterAccessor (1,&params,sizeof (char)*8);
rs.AddParameterEntry (1,DBTYPE_STR,10,&param);
rs.SetParameterInfo (1,&Ordinal,&param);

char (*buf)[40];
buf = new char [Count][40];
rs.CreateAccessor (Count,&buf,sizeof (char)*Count*40);
for (ULONG i=0;i<Count;i++)
  {
  rs.AddBindEntry (i+1,DBTYPE_STR,40,&buf[i],NULL,NULL);
  }

rs.Open (session,"Select * from Stores where  city like ?");

memset (&buf,0,sizeof (char)*40*Count);
rs.MoveNext ();
for (ULONG j=0;j<Count;j++)
{
  TRACE ("Col%d:%s\n\r",j,buf[j]);

Quote:
}

delete [Count] buf;

Quote:
}

--
========================================================================

May the force be with you - always.

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Tue, 17 Dec 2002 03:00:00 GMT  
 OLEDB Parameterised Query (beginner OLEDB)
Hi,
   See example MultiRecords at
http://www.geocities.com/OleDBPro/index.htm.
   You can also find an example at microsoft support web. This is
example demonstrate how to use different accessors to handle stored
procedures. But I don't rember the name of the example.


Tue, 17 Dec 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. OLEDB interfaces vs OLEDB templates

2. ADO vs OLEDB COM vs OLEDB ATL templates

3. Re-Query (Sorting) without accessing the DataBase in OLEDB

4. OLEDB: Microsoft Access 2000 Query Useage

5. how to manage OLEDB output Param Query in vc++

6. OLEDB: Change Query on the fly

7. C# oledb/SQL beginners question

8. Opening a Parameterised Access Query as CRecordSet

9. Writing DAO Parameterised query

10. Opening a Parameterised Access Query as CRecordSet

11. Help !!! OleDb and Transactions

12. oledb insert command

 

 
Powered by phpBB® Forum Software