Skip to content

Commit 95c5230

Browse files
aleks-faleks-f
committed
batch statements (multiple results) support
1 parent ae34e0f commit 95c5230

18 files changed

Lines changed: 283 additions & 194 deletions

‎Data/ODBC/include/Poco/Data/ODBC/ODBCStatementImpl.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,9 @@ class ODBC_API ODBCStatementImpl: public Poco::Data::StatementImpl
126126
void makeInternalExtractors();
127127
/// Creates internal extractors if none were supplied from the user.
128128

129+
bool isStoredProcedure() const;
130+
/// Returns true if SQL is a stored procedure call.
131+
129132
void doPrepare();
130133
/// Prepares placeholders for data returned by statement.
131134
/// It is called during statement compilation for SQL statements

‎Data/ODBC/include/Poco/Data/ODBC/Preparation.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,9 @@ class ODBC_API Preparation : public AbstractPreparation
199199
void prepareImpl(std::size_t pos);
200200
/// Utility function to prepare Any and DynamicAny
201201

202+
void resize() const;
203+
/// Resize the values and lengths vectors.
204+
202205
template <typename T>
203206
void preparePOD(std::size_t pos, SQLSMALLINT valueType)
204207
{

‎Data/ODBC/src/ODBCStatementImpl.cpp‎

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -284,25 +284,17 @@ bool ODBCStatementImpl::hasNext()
284284

285285
if (!nextRowReady())
286286
{
287-
try
288-
{
289-
activateNextDataSet();
290-
} catch (InvalidAccessException&)
291-
{
292-
return false;
293-
}
287+
try { activateNextDataSet(); }
288+
catch (InvalidAccessException&)
289+
{ return false; }
290+
291+
try { checkError(SQLMoreResults(_stmt)); }
292+
catch (NoDataException&)
293+
{ return false; }
294294

295295
addPreparation();
296296
doPrepare();
297297
fixupExtraction();
298-
299-
try
300-
{
301-
checkError(SQLMoreResults(_stmt));
302-
} catch (NoDataException&)
303-
{
304-
return false;
305-
}
306298
makeStep();
307299
}
308300
else if (Utility::isError(_nextResponse))
@@ -408,4 +400,13 @@ void ODBCStatementImpl::fillColumns()
408400
}
409401

410402

403+
bool ODBCStatementImpl::isStoredProcedure() const
404+
{
405+
std::string str = toString();
406+
if (trimInPlace(str).size() < 2) return false;
407+
408+
return ('{' == str[0] && '}' == str[str.size()-1]);
409+
}
410+
411+
411412
} } } // namespace Poco::Data::ODBC

‎Data/ODBC/src/Preparation.cpp‎

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@ Preparation::Preparation(const Preparation& other):
6262
_maxFieldSize(other._maxFieldSize),
6363
_dataExtraction(other._dataExtraction)
6464
{
65+
resize();
6566
}
6667

6768

@@ -79,18 +80,20 @@ Preparation::~Preparation()
7980

8081
std::size_t Preparation::columns() const
8182
{
82-
if (_pValues.empty())
83+
if (_pValues.empty()) resize();
84+
return _pValues.size();
85+
}
86+
87+
88+
void Preparation::resize() const
89+
{
90+
SQLSMALLINT nCol = 0;
91+
if (!Utility::isError(SQLNumResultCols(_rStmt, &nCol)) &&
92+
0 != nCol)
8393
{
84-
SQLSMALLINT nCol = 0;
85-
if (!Utility::isError(SQLNumResultCols(_rStmt, &nCol)) &&
86-
0 != nCol)
87-
{
88-
_pValues.resize(nCol, 0);
89-
_pLengths.resize(nCol, 0);
90-
}
94+
_pValues.resize(nCol, 0);
95+
_pLengths.resize(nCol, 0);
9196
}
92-
93-
return _pValues.size();
9497
}
9598

9699

‎Data/ODBC/testsuite/src/ODBCDB2Test.cpp‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1201,6 +1201,22 @@ void ODBCDB2Test::testDynamicAny()
12011201
}
12021202

12031203

1204+
void ODBCDB2Test::testMultipleResults()
1205+
{
1206+
if (!_pSession) fail ("Test not available.");
1207+
1208+
for (int i = 0; i < 8;)
1209+
{
1210+
recreatePersonTable();
1211+
_pSession->setFeature("autoBind", bindValues[i]);
1212+
_pSession->setFeature("autoExtract", bindValues[i+1]);
1213+
_pExecutor->multipleResults();
1214+
1215+
i += 2;
1216+
}
1217+
}
1218+
1219+
12041220
void ODBCDB2Test::dropObject(const std::string& type, const std::string& name)
12051221
{
12061222
try
@@ -1350,7 +1366,11 @@ bool ODBCDB2Test::canConnect(const std::string& driver, const std::string& dsn)
13501366
{
13511367
std::cout << "DSN found: " << itDSN->first
13521368
<< " (" << itDSN->second << ')' << std::endl;
1353-
format(_dbConnString, "DSN=%s", dsn);
1369+
format(_dbConnString,
1370+
"DSN=%s;"
1371+
"Uid=db2admin;"
1372+
"Pwd=db2admin;",
1373+
dsn);
13541374
return true;
13551375
}
13561376
}
@@ -1470,6 +1490,7 @@ CppUnit::Test* ODBCDB2Test::suite()
14701490
CppUnit_addTest(pSuite, ODBCDB2Test, testAsync);
14711491
CppUnit_addTest(pSuite, ODBCDB2Test, testAny);
14721492
CppUnit_addTest(pSuite, ODBCDB2Test, testDynamicAny);
1493+
CppUnit_addTest(pSuite, ODBCDB2Test, testMultipleResults);
14731494

14741495
return pSuite;
14751496
}

‎Data/ODBC/testsuite/src/ODBCDB2Test.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,8 @@ class ODBCDB2Test: public CppUnit::TestCase
132132
void testAny();
133133
void testDynamicAny();
134134

135+
void testMultipleResults();
136+
135137
void setUp();
136138
void tearDown();
137139

‎Data/ODBC/testsuite/src/ODBCMySQLTest.cpp‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,9 @@ void ODBCMySQLTest::testBareboneODBC()
108108
_pExecutor->bareboneODBCTest(_dbConnString, tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_MANUAL, false);
109109
_pExecutor->bareboneODBCTest(_dbConnString, tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_BOUND, false);
110110

111+
/*
112+
MySQL supports batch statements as of 3.51.18
113+
http://bugs.mysql.com/bug.php?id=7445
111114
112115
tableCreateString = "CREATE TABLE Test "
113116
"(First VARCHAR(30),"
@@ -118,6 +121,7 @@ void ODBCMySQLTest::testBareboneODBC()
118121
_pExecutor->bareboneODBCMultiResultTest(_dbConnString, tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_BOUND);
119122
_pExecutor->bareboneODBCMultiResultTest(_dbConnString, tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_MANUAL);
120123
_pExecutor->bareboneODBCMultiResultTest(_dbConnString, tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_BOUND);
124+
*/
121125
}
122126

123127

@@ -989,6 +993,27 @@ void ODBCMySQLTest::testDynamicAny()
989993
}
990994

991995

996+
void ODBCMySQLTest::testMultipleResults()
997+
{
998+
/*
999+
MySQL supports batch statements as of 3.51.18
1000+
http://bugs.mysql.com/bug.php?id=7445
1001+
1002+
if (!_pSession) fail ("Test not available.");
1003+
1004+
for (int i = 0; i < 8;)
1005+
{
1006+
recreatePersonTable();
1007+
_pSession->setFeature("autoBind", bindValues[i]);
1008+
_pSession->setFeature("autoExtract", bindValues[i+1]);
1009+
_pExecutor->multipleResults();
1010+
1011+
i += 2;
1012+
}
1013+
*/
1014+
}
1015+
1016+
9921017
void ODBCMySQLTest::dropObject(const std::string& type, const std::string& name)
9931018
{
9941019
*_pSession << format("DROP %s IF EXISTS %s", type, name), now;
@@ -1239,6 +1264,7 @@ CppUnit::Test* ODBCMySQLTest::suite()
12391264
CppUnit_addTest(pSuite, ODBCMySQLTest, testAsync);
12401265
CppUnit_addTest(pSuite, ODBCMySQLTest, testAny);
12411266
CppUnit_addTest(pSuite, ODBCMySQLTest, testDynamicAny);
1267+
CppUnit_addTest(pSuite, ODBCMySQLTest, testMultipleResults);
12421268

12431269
return pSuite;
12441270
}

‎Data/ODBC/testsuite/src/ODBCMySQLTest.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,8 @@ class ODBCMySQLTest: public CppUnit::TestCase
132132
void testAny();
133133
void testDynamicAny();
134134

135+
void testMultipleResults();
136+
135137
void setUp();
136138
void tearDown();
137139

‎Data/ODBC/testsuite/src/ODBCOracleTest.cpp‎

Lines changed: 51 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -131,17 +131,17 @@ void ODBCOracleTest::testBarebone()
131131
"Third NUMBER)";
132132

133133
*_pSession << "CREATE OR REPLACE "
134-
"PROCEDURE multiResultsProcedure(tmp1 OUT SYS_REFCURSOR, "
135-
"tmp2 OUT SYS_REFCURSOR,"
136-
"tmp3 OUT SYS_REFCURSOR,"
137-
"tmp4 OUT SYS_REFCURSOR,"
138-
"tmp5 OUT SYS_REFCURSOR) IS "
134+
"PROCEDURE multiResultsProcedure(ret1 OUT SYS_REFCURSOR, "
135+
"ret2 OUT SYS_REFCURSOR,"
136+
"ret3 OUT SYS_REFCURSOR,"
137+
"ret4 OUT SYS_REFCURSOR,"
138+
"ret5 OUT SYS_REFCURSOR) IS "
139139
"BEGIN "
140-
"OPEN tmp1 FOR SELECT * FROM Test WHERE First = '1';"
141-
"OPEN tmp2 FOR SELECT * FROM Test WHERE First = '2';"
142-
"OPEN tmp3 FOR SELECT * FROM Test WHERE First = '3';"
143-
"OPEN tmp4 FOR SELECT * FROM Test WHERE First = '4';"
144-
"OPEN tmp5 FOR SELECT * FROM Test WHERE First = '5';"
140+
"OPEN ret1 FOR SELECT * FROM Test WHERE First = '1';"
141+
"OPEN ret2 FOR SELECT * FROM Test WHERE First = '2';"
142+
"OPEN ret3 FOR SELECT * FROM Test WHERE First = '3';"
143+
"OPEN ret4 FOR SELECT * FROM Test WHERE First = '4';"
144+
"OPEN ret5 FOR SELECT * FROM Test WHERE First = '5';"
145145
"END multiResultsProcedure;" , now;
146146

147147
_pExecutor->bareboneODBCMultiResultTest(_dbConnString,
@@ -1109,7 +1109,7 @@ void ODBCOracleTest::testStoredProcedureDynamicAny()
11091109
}
11101110

11111111

1112-
void ODBCOracleTest::testStoredCursorProcedure()
1112+
void ODBCOracleTest::testCursorStoredProcedure()
11131113
{
11141114
if (!_pSession) fail ("Test not available.");
11151115

@@ -1251,7 +1251,7 @@ void ODBCOracleTest::testStoredFunction()
12511251
}
12521252

12531253

1254-
void ODBCOracleTest::testStoredCursorFunction()
1254+
void ODBCOracleTest::testCursorStoredFunction()
12551255
{
12561256
if (!_pSession) fail ("Test not available.");
12571257

@@ -1367,6 +1367,39 @@ void ODBCOracleTest::testDynamicAny()
13671367
}
13681368

13691369

1370+
void ODBCOracleTest::testMultipleResults()
1371+
{
1372+
if (!_pSession) fail ("Test not available.");
1373+
1374+
1375+
std::string sql = "CREATE OR REPLACE "
1376+
"PROCEDURE multiResultsProcedure(paramAge1 IN NUMBER,"
1377+
" paramAge2 IN NUMBER,"
1378+
" ret1 OUT SYS_REFCURSOR, "
1379+
" ret2 OUT SYS_REFCURSOR,"
1380+
" ret3 OUT SYS_REFCURSOR) IS "
1381+
"BEGIN "
1382+
" OPEN ret1 FOR SELECT * FROM Person WHERE Age = paramAge1;"
1383+
" OPEN ret2 FOR SELECT Age FROM Person WHERE FirstName = 'Bart';"
1384+
" OPEN ret3 FOR SELECT * FROM Person WHERE Age = paramAge2;"
1385+
"END multiResultsProcedure;";
1386+
1387+
for (int i = 0; i < 8;)
1388+
{
1389+
recreatePersonTable();
1390+
*_pSession << sql, now;
1391+
_pSession->setFeature("autoBind", bindValues[i]);
1392+
_pSession->setFeature("autoExtract", bindValues[i+1]);
1393+
_pExecutor->multipleResults("{call multiResultsProcedure(?, ?)}");
1394+
1395+
i += 2;
1396+
}
1397+
}
1398+
1399+
1400+
1401+
1402+
13701403
void ODBCOracleTest::dropObject(const std::string& type, const std::string& name)
13711404
{
13721405
try
@@ -1593,14 +1626,16 @@ bool ODBCOracleTest::init(const std::string& driver, const std::string& dsn)
15931626

15941627
_pExecutor = new SQLExecutor(driver + " SQL Executor", _pSession);
15951628

1629+
#ifdef POCO_OS_FAMILY_WINDOWS
15961630
// Workaround:
15971631
//
1598-
// Barebone ODBC test is called initially for Oracle only.
1632+
// Barebone ODBC test is called automatically on startup for Oracle.
15991633
// The test framework does not exit cleanly if
16001634
// Oracle tests are enabled (i.e. Oracle driver is found)
16011635
// but no tests are executed.
16021636
// The exact reason for this behavior is unknown at this time.
16031637
testBarebone();
1638+
#endif
16041639

16051640
return true;
16061641
}
@@ -1661,18 +1696,19 @@ CppUnit::Test* ODBCOracleTest::suite()
16611696
CppUnit_addTest(pSuite, ODBCOracleTest, testTuple);
16621697
CppUnit_addTest(pSuite, ODBCOracleTest, testTupleVector);
16631698
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredProcedure);
1664-
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredCursorProcedure);
1699+
CppUnit_addTest(pSuite, ODBCOracleTest, testCursorStoredProcedure);
16651700
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredProcedureAny);
16661701
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredProcedureDynamicAny);
16671702
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredFunction);
1668-
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredCursorFunction);
1703+
CppUnit_addTest(pSuite, ODBCOracleTest, testCursorStoredFunction);
16691704
CppUnit_addTest(pSuite, ODBCOracleTest, testInternalExtraction);
16701705
CppUnit_addTest(pSuite, ODBCOracleTest, testInternalStorageType);
16711706
CppUnit_addTest(pSuite, ODBCOracleTest, testNull);
16721707
CppUnit_addTest(pSuite, ODBCOracleTest, testRowIterator);
16731708
CppUnit_addTest(pSuite, ODBCOracleTest, testAsync);
16741709
CppUnit_addTest(pSuite, ODBCOracleTest, testAny);
16751710
CppUnit_addTest(pSuite, ODBCOracleTest, testDynamicAny);
1711+
CppUnit_addTest(pSuite, ODBCOracleTest, testMultipleResults);
16761712

16771713
return pSuite;
16781714
}

‎Data/ODBC/testsuite/src/ODBCOracleTest.h‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -120,9 +120,9 @@ class ODBCOracleTest: public CppUnit::TestCase
120120
void testInternalStorageType();
121121

122122
void testStoredProcedure();
123-
void testStoredCursorProcedure();
123+
void testCursorStoredProcedure();
124124
void testStoredFunction();
125-
void testStoredCursorFunction();
125+
void testCursorStoredFunction();
126126
void testStoredProcedureAny();
127127
void testStoredProcedureDynamicAny();
128128

@@ -134,6 +134,8 @@ class ODBCOracleTest: public CppUnit::TestCase
134134
void testAny();
135135
void testDynamicAny();
136136

137+
void testMultipleResults();
138+
137139
void setUp();
138140
void tearDown();
139141

0 commit comments

Comments
 (0)