1//
2// RowFormatter.cpp
3//
4// This sample demonstrates the Data library recordset row formatting
5// and streaming capabilities.
6//
7// Copyright (c) 2008, Applied Informatics Software Engineering GmbH.
8// and Contributors.
9//
10// SPDX-License-Identifier: BSL-1.0
11
12
13#include "Poco/SharedPtr.h"
14#include "Poco/DateTime.h"
15#include "Poco/SQL/SessionFactory.h"
16#include "Poco/SQL/Session.h"
17#include "Poco/SQL/Statement.h"
18#include "Poco/SQL/RecordSet.h"
19#include "Poco/SQL/RowFormatter.h"
20#include "Poco/SQL/SQLite/Connector.h"
21#include <iostream>
22
23
24using namespace Poco::SQL::Keywords;
25using Poco::DateTime;
26using Poco::SQL::Session;
27using Poco::SQL::Statement;
28using Poco::SQL::RecordSet;
29using Poco::SQL::RowFormatter;
30
31
32class HTMLTableFormatter : public RowFormatter
33{
34public:
35 HTMLTableFormatter()
36 {
37 std::ostringstream os;
38 os << "<TABLE border=\"1\" cellspacing=\"0\">" << std::endl;
39 setPrefix(os.str());
40
41 os.str("");
42 os << "</TABLE>" << std::endl;
43 setPostfix(os.str());
44 }
45
46 std::string& formatNames(const NameVecPtr pNames, std::string& formattedNames)
47 {
48 std::ostringstream str;
49
50 str << "\t<TR>" << std::endl;
51 NameVec::const_iterator it = pNames->begin();
52 NameVec::const_iterator end = pNames->end();
53 for (; it != end; ++it) str << "\t\t<TH align=\"center\">" << *it << "</TH>" << std::endl;
54 str << "\t</TR>" << std::endl;
55
56 return formattedNames = str.str();
57 }
58
59 std::string& formatValues(const ValueVec& vals, std::string& formattedValues)
60 {
61 std::ostringstream str;
62
63 str << "\t<TR>" << std::endl;
64 ValueVec::const_iterator it = vals.begin();
65 ValueVec::const_iterator end = vals.end();
66 for (; it != end; ++it)
67 {
68 if (it->isNumeric())
69 str << "\t\t<TD align=\"right\">";
70 else
71 str << "\t\t<TD align=\"left\">";
72
73 str << it->convert<std::string>() << "</TD>" << std::endl;
74 }
75 str << "\t</TR>" << std::endl;
76
77 return formattedValues = str.str();
78 }
79};
80
81
82int main(int argc, char** argv)
83{
84 // register SQLite connector
85 Poco::SQL::SQLite::Connector::registerConnector();
86
87 // create a session
88 Session session("SQLite", "sample.db");
89
90 // drop sample table, if it exists
91 session << "DROP TABLE IF EXISTS Simpsons", now;
92
93 // (re)create table
94 session << "CREATE TABLE Simpsons (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)", now;
95
96 // insert some rows
97 DateTime hd(1956, 3, 1);
98 session << "INSERT INTO Simpsons VALUES('Homer Simpson', 'Springfield', 42, ?)", use(hd), now;
99 hd.assign(1954, 10, 1);
100 session << "INSERT INTO Simpsons VALUES('Marge Simpson', 'Springfield', 38, ?)", use(hd), now;
101 hd.assign(1980, 4, 1);
102 session << "INSERT INTO Simpsons VALUES('Bart Simpson', 'Springfield', 12, ?)", use(hd), now;
103 hd.assign(1982, 5, 9);
104 session << "INSERT INTO Simpsons VALUES('Lisa Simpson', 'Springfield', 10, ?)", use(hd), now;
105
106 // create a statement and print the column names and data as HTML table
107 HTMLTableFormatter tf;
108 Statement stmt = (session << "SELECT * FROM Simpsons", format(tf), now);
109 RecordSet rs(stmt);
110 std::cout << rs << std::endl;
111
112 // Note: The code above is divided into individual steps for clarity purpose.
113 // The four lines can be reduced to the following single line:
114 std::cout << RecordSet(session, "SELECT * FROM Simpsons", HTMLTableFormatter());
115
116 // simple formatting example (uses the default SimpleRowFormatter provided by framework)
117 std::cout << std::endl << "Simple formatting:" << std::endl << std::endl;
118 std::cout << RecordSet(session, "SELECT * FROM Simpsons");
119
120 return 0;
121}
122