int main(void)
{
try
{
Environment::Initialize();
Connection con("db", "usr", "pwd");
Statement st(con);
st.Execute("select * from all_users");
Resultset rs = st.GetResultset();
while (rs++)
{
std::cout << "UserName:"******"username") << " Created:" << rs.Get<ostring>("created") << std::endl;
}
std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;
}
catch (std::exception &ex)
{
std::cout << ex.what() << std::endl;
}
Environment::Cleanup();
return EXIT_SUCCESS;
}
Product CreateProductFromQuery(const Resultset &rs)
{
Product p;
rs.Get(1, p.code);
rs.Get(2, p.action);
rs.Get(3, p.price);
rs.Get(4, p.date);
return p;
}
int main(void)
{
try
{
Environment::Initialize();
Connection con("db", "usr", "pwd");
Statement st(con);
st.SetFetchMode(Statement::FetchScrollable);
st.Execute("select rownum, 'Row ' || rownum from (select 1 from dual connect by level <= 65)");
Resultset rs = st.GetResultset();
rs.Last();
std::cout << "Total rows : " << rs.GetCount() << std::endl;
rs.First();
print_row(rs);
while (rs.Next())
{
print_row(rs);
}
while (rs.Prev())
{
print_row(rs);
}
rs.Seek(Resultset::SeekAbsolute, 30);
print_row(rs);
while (rs.GetCurrentRow() < 60 && rs.Next())
{
print_row(rs);
}
rs.Seek(Resultset::SeekRelative, -15);
print_row(rs);
}
catch (std::exception &ex)
{
std::cout << ex.what() << std::endl;
}
Environment::Cleanup();
return EXIT_SUCCESS;
}
void test_ref_cursor(void)
{
ocout << otext("\n>>>>> TEST REF CURSOR \n\n");
Statement stBind(con);
Statement st(con);
st.Prepare(otext("begin open :c for select * from test_fetch; end;"));
st.Bind(otext(":c"), stBind, BindInfo::Out);
st.ExecutePrepared();
Resultset rs = stBind.GetResultset();
while (rs++)
{
PrintProductFromQuery(rs);
}
ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}
int main3(void) {
ofstream of;
of.open("out.tsv");
try {
Environment::Initialize();
Connection con("localhost", "scott", "tiger");
Statement st(con);
st.Execute("select * from APEX_040000.WWV_FLOW_STEP_ITEMS");
Resultset rs = st.GetResultset();
auto colCount = rs.GetColumnCount();
cout << colCount << '\n';
for (int i=1; i <=colCount; i++){
cout << rs.GetColumn(i).GetName() << "|" << rs.GetColumn(i).GetFullSQLType() << '\n';
}
while (rs++) {
for (int i = 1; i <= colCount; i++){
of << rs.Get<ostring>(i);
if (i < colCount){
of << "\t";
}
}
of << "\n";
}
std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;
}
catch (std::exception &ex) {
std::cout << ex.what() << std::endl;
}
Environment::Cleanup();
of.close();
return EXIT_SUCCESS;
}
void test_fetch(void)
{
ocout << otext("\n>>>>> SIMPLE TEST FETCH WITH META DATA\n\n");
Statement st(con);
st.Execute(otext("select * from test_fetch"));
Resultset rs = st.GetResultset();
for (int i = 1, n = rs.GetColumnCount(); i <= n; i++)
{
ocout << otext("> Field : #") << i << otext(" - Name : ") << rs.GetColumn(i).GetName() << oendl;
}
ocout << oendl;
while (rs++)
{
PrintProductFromQuery(rs);
}
ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}
void test_scrollable_cursor(void)
{
if (Environment::GetRuntimeVersion() > Oracle9iR1)
{
ocout << otext("\n>>>>> TEST SCROLLABLE CURSORS \n\n");
Statement st(con);
st.SetFetchMode(Statement::FetchScrollable);
st.Execute(otext("select table_name from user_tables where ")
otext("table_name like 'TEST_%' order by table_name"));
Resultset rs = st.GetResultset();
rs.Last();
ocout << otext("Total rows : ") << rs.GetCount() << oendl;
ocout << otext("... Go to row 1\n");
rs.First();
ocout << otext("table ") << rs.Get<ostring>(1) << oendl;
ocout << otext("... Enumerate from row 2 to row ") << rs.GetCount() << otext(" ") << oendl;
while (rs++)
{
ocout << otext("table ") << rs.Get<ostring>(1) << oendl;
}
ocout << otext("... Enumerate from row ") << rs.GetCount() - 1 << otext(" back to row 1") << oendl;
while (rs.Prev())
{
ocout << otext("table ") << rs.Get<ostring>(1) << oendl;
}
ocout << otext("... Go to the 3th row") << oendl;
rs.Seek(Resultset::SeekAbsolute, 3);
ocout << otext("table ") << rs.Get<ostring>(1) << oendl;
ocout << otext("... Fetch the next 2 rows") << oendl;
while (rs.GetCurrentRow() < 5 && rs++)
{
ocout << otext("table ") << rs.Get<ostring>(1) << oendl;
}
}
}
void test_lob(void)
{
ocout << otext("\n>>>>> TEST LOB MANIPULATION\n\n");
Statement st(con);
st.Execute(otext("select code, content from test_lob where code=1 for update"));
Resultset rs = st.GetResultset();
while (rs++)
{
Clob clob = rs.Get<Clob>(2);
clob.Write(otext("today, "));
clob.Append(otext("i'm going to the cinema ! "));
clob.Seek(SeekSet, 0);
ocout << otext("> code : ") << rs.Get<int>(1) << otext(", content : ") << clob.Read(SizeString) << oendl;
}
con.Commit();
ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}
void test_fetch_translate(void)
{
ocout << otext("\n>>>>> SIMPLE TEST FETCH WITH ROW TRANSLATION TO USER TYPE \n\n");
Statement st(con);
st.Execute(otext("select * from test_fetch"));
Resultset rs = st.GetResultset();
ocout << oendl;
while (rs++)
{
Product p;
if (rs.Get(p, FillProductFromQuery))
{
PrintProductFromObject(p);
}
}
ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}
void test_returning(void)
{
ocout << otext("\n>>>>> TEST RETURNING CLAUSE \n\n");
Statement st(con);
st.Prepare(otext("update test_lob set code = code + 1 returning code, content into :i, :l"));
st.Register<int>(otext(":i"));
st.Register<Clob>(otext(":l"));
st.ExecutePrepared();
Resultset rs = st.GetResultset();
while (rs++)
{
Clob clob = rs.Get<Clob>(2);
clob.Append(otext("(modified)"));
clob.Seek(SeekSet, 0);
ocout << otext("> code : ") << rs.Get<int>(1) << otext(" - ") << clob.Read(static_cast<unsigned int>(clob.GetLength())) << oendl;
}
con.Commit();
ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}
"(f_int int, f_varchar varchar(255)) engine=myisam";
ensure("Create table test.sql_helpers_test", c->query(create_table_q) == 0);
static const char *insert_test_values_q=
"insert into test.sql_helpers_test values "
"(1, 'string1'), (2, 'string2'), (3, 'string3'), (4, 'string4')";
ensure("Insert test values into test.sql_helpers_test",
c->query(insert_test_values_q) == 0);
static const char *select_q=
"select * from test.sql_helpers_test";
Resultset rs1;
ensure("Select test values from test.sql_helpers_test",
rs1.query(c, select_q));
Resultset rs2;
ensure("Select test values from test.sql_helpers_test",
rs2.query(c, select_q));
ensure("Test operator ==", rs1 == rs2);
static const char *dump_file= "sql_helpers_test_dump";
rs1.save(dump_file);
Resultset rs3;
void test_returning_array(void)
{
ocout << otext("\n>>>>> TEST ARRAY BINDING WITH RETURNING CLAUSE \n\n");
std::vector<int> tab_int;
std::vector<double> tab_dbl;
std::vector<float> tab_flt;
std::vector<ostring> tab_str;
std::vector<Date> tab_date;
std::vector<Clob> tab_lob;
std::vector<File> tab_file;
for (int i = 0; i < ElemCount; i++)
{
tab_int.push_back(i + 1);
tab_dbl.push_back(3.14*static_cast<double>(i + 1));
tab_flt.push_back(3.14f*static_cast<float>(i + 1));
ostring str;
str += otext("Name");
str += ((i + 1) + '0');
tab_str.push_back(str);
tab_date.push_back(Date::SysDate());
Clob clob(con);
clob.Write(otext("Lob value ") + str);
tab_lob.push_back(clob);
ostring fileName;
fileName += otext("File");
fileName += ((i + 1) + '0');
File file(con, otext("Mydir"), fileName);
tab_file.push_back(file);
}
Statement st(con);
st.Prepare(otext("insert into test_array ")
otext("( ")
otext(" val_int, val_dbl, val_flt, val_str, val_date, ")
otext(" val_lob, val_file ")
otext(") ")
otext("values ")
otext("( ")
otext(" :val_int, :val_dbl, :val_flt, :val_str, :val_date, ")
otext(" :val_lob, :val_file ")
otext(") ")
otext("returning")
otext(" val_int, val_dbl, val_flt, val_str, val_date, ")
otext(" val_lob, val_file ")
otext("into ")
otext(" :out_int, :out_dbl, :out_flt, :out_str, :out_date, ")
otext(" :out_lob, :out_file "));
st.SetBindArraySize(ElemCount);
/* bind vectors */
st.Bind(otext(":val_int"), tab_int, BindInfo::In);
st.Bind(otext(":val_dbl"), tab_dbl, BindInfo::In);
st.Bind(otext(":val_flt"), tab_flt, BindInfo::In);
st.Bind(otext(":val_date"), tab_date, BindInfo::In);
st.Bind(otext(":val_lob"), tab_lob, BindInfo::In);
st.Bind(otext(":val_file"), tab_file, BindInfo::In);
st.Bind(otext(":val_str"), tab_str, 30, BindInfo::In);
/* register output */
st.Register<int >(otext(":out_int"));
st.Register<double >(otext(":out_dbl"));
st.Register<float >(otext(":out_flt"));
st.Register<Date >(otext(":out_date"));
st.Register<Clob >(otext(":out_lob"));
st.Register<File >(otext(":out_file"));
st.Register<ostring>(otext(":out_str"), 30);
st.ExecutePrepared();
ocout << oendl << st.GetAffectedRows() << otext(" row(s) inserted") << oendl;
int rowIndex = 0;
Resultset rs = st.GetResultset();
while (!rs.IsNull())
{
while (rs++)
{
ocout << otext("Row #") << ++rowIndex << otext("---------------") << oendl;
ocout << otext(".... val_int : ") << rs.Get<int>(otext(":OUT_INT")) << oendl;
ocout << otext(".... val_dbl : ") << rs.Get<double>(otext(":OUT_DBL")) << oendl;
ocout << otext(".... val_flt : ") << rs.Get<float>(otext(":OUT_FLT")) << oendl;
ocout << otext(".... val_str : ") << rs.Get<ostring>(otext(":OUT_STR")) << oendl;
ocout << otext(".... val_date : ") << rs.Get<Date>(otext(":OUT_DATE")) << oendl;
Clob clob = rs.Get<Clob>(otext(":OUT_LOB"));
ocout << otext(".... val_lob : ") << clob.Read(SizeBuffer) << oendl;
File file = rs.Get<File>(otext(":OUT_FILE"));
ocout << otext(".... val_file : ") << file.GetDirectory() << otext("/") << file.GetName() << oendl;
}
rs = st.GetNextResultset();
}
}
