Skip to content

Commit 911d0c5

Browse files
aleks-faleks-f
committed
Stored procedure support
1 parent 2bb4746 commit 911d0c5

8 files changed

Lines changed: 209 additions & 8 deletions

File tree

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

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -165,9 +165,17 @@ class ODBC_API Binder: public Poco::Data::AbstractBinder
165165
catch (StatementException&) { }
166166
}
167167

168+
bool in = isInBound();
169+
bool out = isOutBound();
170+
SQLSMALLINT ioType = SQL_PARAM_TYPE_UNKNOWN;
171+
if (in && out) ioType = SQL_PARAM_INPUT_OUTPUT;
172+
else if(in) ioType = SQL_PARAM_INPUT;
173+
else if(out) ioType = SQL_PARAM_OUTPUT;
174+
else throw Poco::IllegalStateException("Binder not bound (must be [in] OR [out]).");
175+
168176
if (Utility::isError(SQLBindParameter(_rStmt,
169177
(SQLUSMALLINT) pos + 1,
170-
SQL_PARAM_INPUT,
178+
ioType,
171179
cDataType,
172180
sqlDataType,
173181
columnSize,

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

Lines changed: 95 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@
3434
#include "CppUnit/TestCaller.h"
3535
#include "CppUnit/TestSuite.h"
3636
#include "Poco/String.h"
37+
#include "Poco/Tuple.h"
3738
#include "Poco/Format.h"
3839
#include "Poco/Exception.h"
3940
#include "Poco/Data/Common.h"
@@ -54,6 +55,7 @@ using Poco::Data::ODBC::ConnectionException;
5455
using Poco::Data::ODBC::StatementException;
5556
using Poco::Data::ODBC::StatementDiagnostics;
5657
using Poco::format;
58+
using Poco::Tuple;
5759
using Poco::NotFoundException;
5860

5961

@@ -827,7 +829,98 @@ void ODBCOracleTest::testInternalExtraction()
827829

828830
void ODBCOracleTest::testStoredProcedure()
829831
{
830-
//TODO
832+
*_pSession << "CREATE OR REPLACE "
833+
"PROCEDURE storedProcedure(outParam OUT NUMBER) IS "
834+
" BEGIN outParam := -1; "
835+
"END storedProcedure;" , now;
836+
837+
int i = 0;
838+
*_pSession << "{call storedProcedure(?)}", out(i), now;
839+
assert(-1 == i);
840+
*_pSession << "DROP PROCEDURE storedProcedure;", now;
841+
842+
*_pSession << "CREATE OR REPLACE "
843+
"PROCEDURE storedProcedure(inParam IN NUMBER, outParam OUT NUMBER) IS "
844+
" BEGIN outParam := inParam*inParam; "
845+
"END storedProcedure;" , now;
846+
847+
i = 2;
848+
int j = 0;
849+
*_pSession << "{call storedProcedure(?, ?)}", in(i), out(j), now;
850+
assert(4 == j);
851+
*_pSession << "DROP PROCEDURE storedProcedure;", now;
852+
853+
*_pSession << "CREATE OR REPLACE "
854+
"PROCEDURE storedProcedure(ioParam IN OUT NUMBER) IS "
855+
" BEGIN ioParam := ioParam*ioParam; "
856+
" END storedProcedure;" , now;
857+
858+
i = 2;
859+
*_pSession << "{call storedProcedure(?)}", io(i), now;
860+
assert(4 == i);
861+
*_pSession << "DROP PROCEDURE storedProcedure;", now;
862+
}
863+
864+
865+
void ODBCOracleTest::testStoredFunction()
866+
{
867+
*_pSession << "CREATE OR REPLACE "
868+
"FUNCTION storedFunction RETURN NUMBER IS "
869+
" BEGIN return(-1); "
870+
" END storedFunction;" , now;
871+
872+
int i = 0;
873+
*_pSession << "{? = call storedFunction()}", out(i), now;
874+
assert(-1 == i);
875+
*_pSession << "DROP FUNCTION storedFunction;", now;
876+
877+
*_pSession << "CREATE OR REPLACE "
878+
"FUNCTION storedFunction(inParam IN NUMBER) RETURN NUMBER IS "
879+
" BEGIN RETURN(inParam*inParam); "
880+
" END storedFunction;" , now;
881+
882+
i = 2;
883+
int result = 0;
884+
*_pSession << "{? = call storedFunction(?)}", out(result), in(i), now;
885+
assert(4 == result);
886+
*_pSession << "DROP FUNCTION storedFunction;", now;
887+
888+
*_pSession << "CREATE OR REPLACE "
889+
"FUNCTION storedFunction(inParam IN NUMBER, outParam OUT NUMBER) RETURN NUMBER IS "
890+
" BEGIN outParam := inParam*inParam; RETURN(outParam); "
891+
" END storedFunction;" , now;
892+
893+
i = 2;
894+
int j = 0;
895+
result = 0;
896+
*_pSession << "{? = call storedFunction(?, ?)}", out(result), in(i), out(j), now;
897+
assert(4 == j);
898+
assert(j == result);
899+
*_pSession << "DROP FUNCTION storedFunction;", now;
900+
901+
*_pSession << "CREATE OR REPLACE "
902+
"FUNCTION storedFunction(param1 IN OUT NUMBER, param2 IN OUT NUMBER) RETURN NUMBER IS "
903+
" temp NUMBER := param1; "
904+
" BEGIN param1 := param2; param2 := temp; RETURN(param1+param2); "
905+
" END storedFunction;" , now;
906+
907+
i = 1;
908+
j = 2;
909+
result = 0;
910+
*_pSession << "{? = call storedFunction(?, ?)}", out(result), io(i), io(j), now;
911+
assert(1 == j);
912+
assert(2 == i);
913+
assert(3 == result);
914+
915+
Tuple<int, int> params(1, 2);
916+
assert(1 == params.get<0>());
917+
assert(2 == params.get<1>());
918+
result = 0;
919+
*_pSession << "{? = call storedFunction(?, ?)}", out(result), io(params), now;
920+
assert(1 == params.get<1>());
921+
assert(2 == params.get<0>());
922+
assert(3 == result);
923+
*_pSession << "DROP FUNCTION storedFunction;", now;
831924
}
832925

833926

@@ -1081,6 +1174,7 @@ CppUnit::Test* ODBCOracleTest::suite()
10811174
CppUnit_addTest(pSuite, ODBCOracleTest, testTuple);
10821175
CppUnit_addTest(pSuite, ODBCOracleTest, testTupleVector);
10831176
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredProcedure);
1177+
CppUnit_addTest(pSuite, ODBCOracleTest, testStoredFunction);
10841178
CppUnit_addTest(pSuite, ODBCOracleTest, testInternalExtraction);
10851179

10861180
return pSuite;

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -117,6 +117,7 @@ class ODBCOracleTest: public CppUnit::TestCase
117117

118118
void testInternalExtraction();
119119
void testStoredProcedure();
120+
void testStoredFunction();
120121

121122
void setUp();
122123
void tearDown();

‎Data/include/Poco/Data/AbstractBinder.h‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,9 +106,52 @@ class Data_API AbstractBinder
106106

107107
virtual void bind(std::size_t pos, const BLOB& val) = 0;
108108
/// Binds a BLOB.
109+
110+
bool isInBound() const;
111+
/// Returns true if binder is in-bound.
112+
113+
bool isOutBound() const;
114+
/// Returns true if binder is out-bound.
115+
116+
void setInBound(bool inBound = true);
117+
/// If inBound is true, binder is set to be in-bound.
118+
119+
void setOutBound(bool outBound = true);
120+
/// If outBound is true, binder is set to be out-bound.
121+
122+
protected:
123+
bool _inBound;
124+
bool _outBound;
109125
};
110126

111127

128+
///
129+
/// inlines
130+
///
131+
inline bool AbstractBinder::isInBound() const
132+
{
133+
return _inBound;
134+
}
135+
136+
137+
inline bool AbstractBinder::isOutBound() const
138+
{
139+
return _outBound;
140+
}
141+
142+
143+
inline void AbstractBinder::setInBound(bool inBound)
144+
{
145+
_inBound = inBound;
146+
}
147+
148+
149+
inline void AbstractBinder::setOutBound(bool outBound)
150+
{
151+
_outBound = outBound;
152+
}
153+
154+
112155
} } // namespace Poco::Data
113156

114157

‎Data/include/Poco/Data/AbstractBinding.h‎

Lines changed: 25 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@
4141

4242

4343
#include "Poco/Data/Data.h"
44+
#include "Poco/Data/AbstractBinder.h"
4445
#include "Poco/Any.h"
4546
#include "Poco/RefCountedObject.h"
4647
#include "Poco/AutoPtr.h"
@@ -52,9 +53,6 @@ namespace Poco {
5253
namespace Data {
5354

5455

55-
class AbstractBinder;
56-
57-
5856
class Data_API AbstractBinding: public Poco::RefCountedObject
5957
/// AbstractBinding connects a value with a placeholder via an AbstractBinder interface.
6058
{
@@ -92,8 +90,16 @@ class Data_API AbstractBinding: public Poco::RefCountedObject
9290
virtual void reset() = 0;
9391
/// Allows a binding to be reused.
9492

93+
void setInBound(bool inBound = true);
94+
/// Sets the binder to be in-bound.
95+
96+
void setOutBound(bool outBound = true);
97+
/// Sets the binder to be out-bound.
98+
9599
private:
96100
AbstractBinder* _pBinder;
101+
bool _inBound;
102+
bool _outBound;
97103
};
98104

99105

@@ -106,7 +112,11 @@ typedef std::vector<AbstractBindingPtr> AbstractBindingVec;
106112
//
107113
inline void AbstractBinding::setBinder(AbstractBinder* pBinder)
108114
{
115+
poco_check_ptr (pBinder);
109116
_pBinder = pBinder;
117+
_pBinder->setInBound(_inBound);
118+
_pBinder->setOutBound(_outBound);
119+
poco_assert_dbg (_inBound || _outBound);
110120
}
111121

112122

@@ -116,6 +126,18 @@ inline AbstractBinder* AbstractBinding::getBinder() const
116126
}
117127

118128

129+
inline void AbstractBinding::setInBound(bool inBound)
130+
{
131+
_inBound = inBound;
132+
}
133+
134+
135+
inline void AbstractBinding::setOutBound(bool outBound)
136+
{
137+
_outBound = outBound;
138+
}
139+
140+
119141
} } // namespace Poco::Data
120142

121143

‎Data/include/Poco/Data/Binding.h‎

Lines changed: 30 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -478,7 +478,36 @@ class Binding<std::multimap<K, V> >: public AbstractBinding
478478
template <typename T> Binding<T>* use(const T& t)
479479
/// Convenience function for a more compact Binding creation.
480480
{
481-
return new Binding<T>(t);
481+
Binding<T>* pB = new Binding<T>(t);
482+
poco_check_ptr (pB);
483+
return pB;
484+
}
485+
486+
487+
template <typename T> Binding<T>* in(const T& t)
488+
/// Convenience function for a more compact Binding creation.
489+
{
490+
return use(t);
491+
}
492+
493+
494+
template <typename T> Binding<T>* out(const T& t)
495+
/// Convenience function for a more compact Binding creation.
496+
{
497+
Binding<T>* pB = use(t);
498+
pB->setInBound(false);
499+
pB->setOutBound(true);
500+
return pB;
501+
}
502+
503+
504+
template <typename T> Binding<T>* io(const T& t)
505+
/// Convenience function for a more compact Binding creation.
506+
{
507+
Binding<T>* pB = use(t);
508+
pB->setInBound(true);
509+
pB->setOutBound(true);
510+
return pB;
482511
}
483512

484513

‎Data/src/AbstractBinder.cpp‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,9 @@ namespace Poco {
4141
namespace Data {
4242

4343

44-
AbstractBinder::AbstractBinder()
44+
AbstractBinder::AbstractBinder():
45+
_inBound(true),
46+
_outBound(false)
4547
{
4648
}
4749

‎Data/src/AbstractBinding.cpp‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,9 @@ namespace Data {
4242

4343

4444
AbstractBinding::AbstractBinding():
45-
_pBinder(0)
45+
_pBinder(0),
46+
_inBound(true),
47+
_outBound(false)
4648
{
4749
}
4850

0 commit comments

Comments
 (0)