🚀 go-pugleaf

Hi,
    You can use a parameterized statement or stored procedure to add bulk
records with a single call (net trip). For details, see the examples
MultiProcs and OleDBExcel without involvement of a rowset at the site
http://www.geocities.com/oledbpro. you can't see the method from ADO or
ADOExpress. See the following code.

#define DBINITCONSTANTS
#include "OleDB25.h"
#include <iostream.h>

const LPOLESTR strMakeDept=OLESTR("Create Table Dept(DeptNo short, Name
char(50))");
const LPOLESTR strMakeEmpInfo=OLESTR("CREATE TABLE EmpInfo (EmpID Integer,
LastName CHAR(25), HireDate DATETIME)");
const LPOLESTR strInsert=L"Insert into Dept values(?, ?)";

void main()
{
 CoInitialize(NULL);
 {
  USES_CONVERSION;
  CODataSource  ODataSource;
  COSession   OSession;
  CBatchParam<CParamBase>   BatchParam;
  CPInfo    pPInfo[2];
  CPData    pPData[6];
  short    pnDeptNo[3]={1,2,3};

  long    *pnDept=NULL;
  char    *strDeptName=NULL;
  long    nIndex=0;

  DeleteFile(_T("xlsTest.xls"));
  ODataSource.SetProviderString(L"Excel 8.0;");
  ODataSource.SetServer(L"xlsTest");

  //create an excel file
  if(!ODataSource.Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"))
  {
   cout<<"Error information: "<<W2T(ODataSource.m_strError)<<endl;
   return;
  }

  if(!OSession.Open(ODataSource.m_pIDBInitialize))
  {
   cout<<"Error information: "<<W2T(OSession.m_strError)<<endl;
   return;
  }

  //Begin two-phase transaction
  if(!OSession.BeginTrans())
  {
   cout<<"Error Information: "<<W2T(OSession.m_strError)<<"  ";
   return;
  }

  //The following build two tables and define fields
  if(!OSession.ExecuteSQL(strMakeDept))
   goto ROLLBACK;
  if(!OSession.ExecuteSQL(strMakeEmpInfo))
   goto ROLLBACK;

  //if successful, commit the above transaction
  OSession.Commit();

  //it is a must to set a data type for each of parameters
  pPInfo[0].m_nDBType=DBTYPE_I2;
  pPInfo[1].m_nDBType=DBTYPE_STR;

  if(!BatchParam.Open(OSession.m_pIOpenRowset, strInsert, pPInfo))
  {
   cout<<"Error Information: "<<W2T(BatchParam.m_strError)<<"  ";
   return;
  }

  //Prepare an array of parameter data
  pPData[0].m_pData=&(pnDeptNo[0]);
  pPData[2].m_pData=&(pnDeptNo[1]);
  pPData[4].m_pData=&(pnDeptNo[2]);

  pPData[1].m_pData="Sales Dept";
  pPData[3].m_pData="Software Development";
  pPData[5].m_pData="Admin Dept";

  //set 3 sets of parameter data into a data source by a single call at the
fastest speed! Reduce the number of trips
  if(!BatchParam.DoBatch(6, pPData))
  {
   cout<<"Error Information: "<<W2T(BatchParam.m_strError)<<"  ";
   return;
  }
    return;

ROLLBACK:
  OSession.Rollback();
}
 CoUninitialize();
}

    The code can't be simpler, faster and more elegant!

    Cheers!!!

--
Yuancai (Charlie) Ye
See 28 OLEDB examples at http://www.geocities.com/oledbpro


"Changsong Bu" <cbu@idax.com> wrote in message news:3b39dc7d$1_1@dnews...
> I wonder whether you can execute a stored procedure multiple times by
using
> arrays of parameters? The procedure will be very simple, just an insert
and
> error checking. However it needs to be called very frequently, like once
> every second. I hope to be able to call it once every 15 minutes,
supplying
> all the data in an array. In SQL Server books on line they talk about
> column-wise parameter binding or row-wise parameter bing, which you can
use
> to execute a statement multiple times, in ODBC. But the syntax doesn't
work
> in C++ builder. Anyone has any idea about it? Thanks very much in advance.
> Changsong
>
>