Modified:
trunk/examples/CPP/7/ADO/main.cpp
Log:
C++ ADO example now works on MS SQL Server and MySQL
Modified: trunk/examples/CPP/7/ADO/main.cpp
===================================================================
--- trunk/examples/CPP/7/ADO/main.cpp 2006-07-20 16:25:25 UTC (rev 454)
+++ trunk/examples/CPP/7/ADO/main.cpp 2006-07-20 23:59:17 UTC (rev 455)
@@ -1,3 +1,28 @@
+/*!
+ \file main.cpp
+ \author Peter Harvey <pharvey@stripped>
+ Copyright MySQL AB 2004-2006 Released under GPL.
+ \version Connector/ODBC v5
+ \date 2006
+ \brief Demonstrates ADO in use via C++.
+
+ It is probably safe to say that ADO is seldom used via C++ while ADO is
often used by
+ Visual Basic. The result is that it can sometimes be very difficult to
find information
+ on how to do some things with ADO in C++. For example; sending binary
'down the pipe'
+ via CreateParameter.
+
+ This file has been created in the hope that it may answer questions by
those wishing
+ to 'go down this path'.
+
+ \note Before using this code you will want to;
+ - create the tbAdo table (see code comments later in this file)
+ - create the insAdo stored procedure (see code comments later in this
file)
+ - create a DSN (to tbAdo and insAdo)
+ - alter the connection information in the code below
+
+ \warning Do not use this in a production environment or in any environment where
you may suffer
+ grief if things go horribly wrong. Use at your own risk.
+*/
#include <oledb.h>
#include <msdadc.h>
#include <msdaguid.h>
@@ -22,54 +47,60 @@
no_namespace \
rename( "EOF", "EndOfFile")
+// handle a HRESULT return type
inline void TESTHR( HRESULT x ) { if FAILED(x) _com_issue_error(x); };
-IDataConvert *pDataConvert = NULL;
+
+// global connect
_ConnectionPtr pConnection = NULL;
+char *pszConnectionString = "ado2";
+char *pszUserID = "root";
+char *pszPassword = "pharvey";
-#define DB_MSSQL
+// sample data
+UCHAR pData[] = "12345678901234567890123456789012345678901234567890";
+int nDataLen = 10; // data truncation errors if this is larger than strlen pData or
size of column
-#ifdef DB_MYSQL
-char *pszConnectionString = "NorthwindMySQL";
-#elif defined( DB_ACCESS )
-char *pszConnectionString = "NorthwindAccess";
-#elif defined( DB_MSSQL )
-char *pszConnectionString = "Northwind";
-#endif
-char *pszUserID = "root";
-char *pszPassword = "mysql";
-
+/*!
+ \brief Print the providers errors.
+*/
void doPrintProviderError( _ConnectionPtr pConnection )
{
- // Print Provider Errors from Connection object.
- // pErr is a record object in the Connection's Error collection.
ErrorPtr pErr = NULL;
- if( (pConnection->Errors->Count) > 0)
+ // do we have errors?
+ if ( pConnection->Errors->Count > 0 )
{
long nCount = pConnection->Errors->Count;
- // Collection ranges from 0 to nCount -1.
- for(long i = 0; i < nCount; i++)
+ // for each error - print error
+ for ( long n = 0; n < nCount; n++ )
{
- pErr = pConnection->Errors->GetItem(i);
- printf("Error number: %x\t%s\n", pErr->Number,
(LPCSTR)pErr->Description);
+ pErr = pConnection->Errors->GetItem( n );
+ printf( "Error #: %x\t%s\n", pErr->Number, (LPCSTR)pErr->Description );
}
}
}
+/*!
+ \brief Print COM error.
+*/
void doPrintComError( _com_error &e )
{
- _bstr_t bstrSource(e.Source());
- _bstr_t bstrDescription(e.Description());
+ _bstr_t bstrSource( e.Source() );
+ _bstr_t bstrDescription( e.Description() );
- // Print Com errors.
- printf("Error\n");
- printf("\tCode = %08lx\n", e.Error());
- wprintf( L"\tCode meaning = %s\n", e.ErrorMessage());
- printf("\tSource = %s\n", (LPCSTR) bstrSource);
- printf("\tDescription = %s\n", (LPCSTR) bstrDescription);
+ printf( "Error\n" );
+ printf( "\tCode = %08lx\n", e.Error() );
+ wprintf( L"\tCode meaning = %s\n", e.ErrorMessage() );
+ printf( "\tSource = %s\n", (LPCSTR)bstrSource );
+ printf( "\tDescription = %s\n", (LPCSTR)bstrDescription );
}
+/*!
+ \brief Print record-set.
+
+ This prints all data in the record-set in a generic fashion.
+*/
bool doPrintRecordset( _RecordsetPtr pRecordset )
{
/* field names */
@@ -91,6 +122,11 @@
return true;
}
+/*!
+ \brief Connect.
+
+ This will connect to the data source.
+*/
bool doConnect()
{
try
@@ -109,26 +145,22 @@
return true;
}
-bool doSelectBoundParameterChar()
+/*!
+ \brief Print all data in tbAdo.
+*/
+bool doSelect()
{
_CommandPtr pCommand = NULL;
_ParameterPtr pParameter = NULL;
_RecordsetPtr pRecordset = NULL;
- VARIANT variant;
try
{
TESTHR( pCommand.CreateInstance( __uuidof( Command ) ) );
pCommand->ActiveConnection = pConnection;
- pCommand->CommandText = "SELECT * FROM orders WHERE CustomerID = ? ORDER BY
OrderID";
+ pCommand->CommandText = "SELECT * FROM tbAdo";
pCommand->CommandType = adCmdText;
- variant.vt = VT_BSTR;
- variant.bstrVal = _bstr_t( "WELLI" );
-
- pParameter = pCommand->CreateParameter( _bstr_t( "CustomerID" ), adChar,
adParamInput, 6, variant );
- pCommand->Parameters->Append( pParameter );
-
pRecordset = pCommand->Execute( NULL, NULL, adCmdText );
doPrintRecordset( pRecordset );
@@ -140,124 +172,93 @@
return false;
}
- return true;
-}
-bool doSelectBoundParameterInteger()
-{
- _CommandPtr pCommand = NULL;
- _ParameterPtr pParameter = NULL;
- _RecordsetPtr pRecordset = NULL;
- VARIANT variant;
-
- try
- {
- TESTHR( pCommand.CreateInstance( __uuidof( Command ) ) );
- pCommand->ActiveConnection = pConnection;
- pCommand->CommandText = "SELECT * FROM orders WHERE EmployeeID = ? ORDER BY
OrderID";
- pCommand->CommandType = adCmdText;
-
- variant.vt = VT_I2;
- variant.iVal = 4;
-
- pParameter = pCommand->CreateParameter( "EmployeeID", adInteger, adParamInput,
sizeof(int), variant );
- pCommand->Parameters->Append( pParameter );
-
- pRecordset = pCommand->Execute( NULL, NULL, adCmdText );
-
- doPrintRecordset( pRecordset );
- }
- catch ( _com_error &e )
- {
- doPrintProviderError( pConnection );
- doPrintComError( e );
- return false;
- }
-
-
return true;
}
-bool doSelect()
-{
- _CommandPtr pCommand = NULL;
- _ParameterPtr pParameter = NULL;
- _RecordsetPtr pRecordset = NULL;
+/*!
+ \brief Use CreateParameter on an INSERT statement.
- try
- {
- TESTHR( pCommand.CreateInstance( __uuidof( Command ) ) );
- pCommand->ActiveConnection = pConnection;
- pCommand->CommandText = "SELECT * FROM categories";
- pCommand->CommandType = adCmdText;
+ This will show CreateParameter in use on an INSERT statement
+ with a variety of column data types.
- pRecordset = pCommand->Execute( NULL, NULL, adCmdText );
+ \note Needs the following table to work...
- doPrintRecordset( pRecordset );
- }
- catch ( _com_error &e )
- {
- doPrintProviderError( pConnection );
- doPrintComError( e );
- return false;
- }
+ MSSQL
+ -----
+ CREATE TABLE `tbAdo`
+ (
+ `bin` BINARY(50),
+ `txt` TEXT,
+ `n` INTEGER,
+ `vc` VARCHAR(50)
+ )
-
- return true;
-}
-
-bool doInsertBoundParameterBinary()
+ MySQL
+ -----
+ CREATE TABLE `tbAdo`
+ (
+ `bin` BINARY(50),
+ `txt` TEXT,
+ `n` INTEGER UNSIGNED,
+ `vc` VARCHAR(50)
+ )
+ ENGINE = InnoDB;
+*/
+bool doCreateParameterInsert()
{
_CommandPtr pCommand = NULL;
_ParameterPtr pParameter = NULL;
-#ifndef DB_MSSQL
VARIANT variant1;
-#endif
- VARIANT variant2;
VARIANT variant3;
VARIANT variant4;
- const char * pBinary = "binary data here";
+ SAFEARRAY FAR * pArray;
+ SAFEARRAYBOUND arrayBound[1];
+
+ arrayBound[0].lLbound = 0;
+ arrayBound[0].cElements = nDataLen;
+
try
{
+ // our INSERT statement
TESTHR( pCommand.CreateInstance( __uuidof( Command ) ) );
pCommand->ActiveConnection = pConnection;
-#ifdef DB_MSSQL
- pCommand->CommandText = "INSERT INTO categories VALUES( ?, ?, ? )";
-#else
- pCommand->CommandText = "INSERT INTO categories VALUES( ?, ?, ?, ? )";
-#endif
+ pCommand->CommandText = "INSERT INTO tbAdo VALUES( ?, ?, ?, ? )";
pCommand->CommandType = adCmdText;
-#ifndef DB_MSSQL
- variant1.vt = VT_I2;
- variant1.iVal = 1867;
- pParameter = pCommand->CreateParameter( "CategoryID", adInteger, adParamInput,
sizeof(int), variant1 );
+ // BINARY
+ pArray = SafeArrayCreate( VT_UI1, 1, arrayBound );
+ for ( long n = 0; n < nDataLen; n++ )
+ {
+ SafeArrayPutElement( pArray, &n, &(pData[n]) );
+ }
+
+ variant1.vt = VT_ARRAY|VT_UI1;
+ variant1.parray = pArray;
+ pParameter = pCommand->CreateParameter( "bin", adBinary, adParamInput,
nDataLen, variant1 );
pCommand->Parameters->Append( pParameter );
-#endif
- variant2.vt = VT_BSTR;
- variant2.bstrVal = _bstr_t( "Eighteen sixty seven" );
- pParameter = pCommand->CreateParameter( "CategoryName", adChar, adParamInput,
20, variant2 );
+ // TEXT (we use same data as BINARY - because we can)
+ pParameter = pCommand->CreateParameter( "txt", adChar, adParamInput,
nDataLen, variant1 );
pCommand->Parameters->Append( pParameter );
- variant3.vt = VT_BSTR;
- variant3.bstrVal = _bstr_t( "a fine year" );
- pParameter = pCommand->CreateParameter( "Description", adChar, adParamInput,
11, variant3 );
+ // INTEGER
+ variant3.vt = VT_I2;
+ variant3.iVal = 1867;
+ pParameter = pCommand->CreateParameter( "n", adInteger, adParamInput,
sizeof(int), variant3 );
pCommand->Parameters->Append( pParameter );
- variant4.vt = VT_BLOB;
- variant4.pbVal = (BYTE*)pBinary;
-// variant4.vt = VT_BSTR;
-// variant4.bstrVal = _bstr_t( pBinary );
- pParameter = pCommand->CreateParameter( "Picture", adLongVarBinary,
adParamInput, 17 + 1, variant4 );
+ // VARCHAR
+ variant4.vt = VT_BSTR;
+ variant4.bstrVal = _bstr_t( "test" );
+ pParameter = pCommand->CreateParameter( "vc", adChar, adParamInput, 5,
variant4 );
pCommand->Parameters->Append( pParameter );
-// pParameter->AppendChunk( variant4 );
-
+ // execute statement
pCommand->Execute( NULL, NULL, adCmdText );
-
+// SafeArrayDestroy( variant4 );
}
catch ( _com_error &e )
@@ -271,44 +272,78 @@
return true;
}
-bool doInsertBoundParameterBinary2()
+/*!
+ \brief Use CreateParameter on a stored procedure.
+
+ This shows CreateParameter in use on a stored procedure with a variety of
+ column data types.
+
+ \note This needs the following stored procedure to work...
+
+ MSSQL
+ ------
+ CREATE PROCEDURE insAdo @bin BINARY(50), @txt TEXT, @n INT, @vc VARCHAR(50)
AS
+ INSERT INTO tbAdo VALUES( @bin, @txt, @n, @vc )
+
+ MySQL
+ -----
+ CREATE PROCEDURE `northwind`.`insAdo` ( IN bin BINARY(50), IN txt TEXT, IN n
INT, IN vc VARCHAR(50) )
+ BEGIN
+ INSERT INTO tbAdo VALUES( @bin, @txt, @n, @vc );
+ END
+
+*/
+bool doCreateParameterProcedure()
{
_CommandPtr pCommand = NULL;
_ParameterPtr pParameter = NULL;
- VARIANT variant2;
+ VARIANT variant1;
+ VARIANT variant3;
VARIANT variant4;
SAFEARRAY FAR * pArray;
- SAFEARRAYBOUND arrayBound[1];
+ SAFEARRAYBOUND arrayBound[1];
+
arrayBound[0].lLbound = 0;
- arrayBound[0].cElements = 7;
+ arrayBound[0].cElements = nDataLen;
- UCHAR pBinary[] = "binary";
-
try
{
+ // our stored procedure statement
TESTHR( pCommand.CreateInstance( __uuidof( Command ) ) );
pCommand->ActiveConnection = pConnection;
- pCommand->CommandText = "insCategories";
+ pCommand->CommandText = "insAdo";
pCommand->CommandType = adCmdStoredProc;
- variant2.vt = VT_BSTR;
- variant2.bstrVal = _bstr_t( "Eighteen sixty seven" );
- pParameter = pCommand->CreateParameter( "@vcName", adChar, adParamInput, 20,
variant2 );
- pCommand->Parameters->Append( pParameter );
-
+ // BINARY
pArray = SafeArrayCreate( VT_UI1, 1, arrayBound );
- for ( long n = 0; n < variant4.parray->rgsabound->cElements; n++ )
+ for ( long n = 0; n < nDataLen; n++ )
{
- SafeArrayPutElement( pArray, &n, &(pBinary[n]) );
+ SafeArrayPutElement( pArray, &n, &(pData[n]) );
}
- variant4.vt = VT_ARRAY|VT_UI1;
- variant4.parray = pArray;
+ variant1.vt = VT_ARRAY|VT_UI1;
+ variant1.parray = pArray;
+ pParameter = pCommand->CreateParameter( "bin", adBinary, adParamInput,
nDataLen, variant1 );
+ pCommand->Parameters->Append( pParameter );
- pParameter = pCommand->CreateParameter( "@binPicture", adBinary, adParamInput,
variant4.parray->rgsabound->cElements, variant4 );
+ // TEXT (we use same data as BINARY - because we can)
+ pParameter = pCommand->CreateParameter( "txt", adChar, adParamInput,
nDataLen, variant1 );
pCommand->Parameters->Append( pParameter );
+ // INTEGER
+ variant3.vt = VT_I2;
+ variant3.iVal = 1867;
+ pParameter = pCommand->CreateParameter( "n", adInteger, adParamInput,
sizeof(int), variant3 );
+ pCommand->Parameters->Append( pParameter );
+
+ // VARCHAR
+ variant4.vt = VT_BSTR;
+ variant4.bstrVal = _bstr_t( "test" );
+ pParameter = pCommand->CreateParameter( "vc", adChar, adParamInput, 5,
variant4 );
+ pCommand->Parameters->Append( pParameter );
+
+ // execute statement
pCommand->Execute( NULL, NULL, adCmdStoredProc );
// SafeArrayDestroy( variant4 );
@@ -324,6 +359,12 @@
return true;
}
+
+/*!
+ \brief Disconnect.
+
+ This will disconnect from the data source.
+*/
bool doDisconnect()
{
try
@@ -345,6 +386,8 @@
\brief main
This is the 'main' entry point to this program - it all starts here.
+
+ \todo We need to do a better job of cleaning up after our processing.
*/
void main()
{
@@ -357,11 +400,10 @@
/* do it */
if ( doConnect() )
{
-// doSelectBoundParameterChar();
-// doSelectBoundParameterInteger();
-// doSelect();
- doInsertBoundParameterBinary2();
-// doSelect();
+ doSelect();
+ doCreateParameterInsert();
+ doCreateParameterProcedure();
+ doSelect();
doDisconnect();
}
| Thread |
|---|
| • Connector/ODBC 5 commit: r455 - trunk/examples/CPP/7/ADO | pharvey | 21 Jul |
