1//
2// Binding.cpp
3//
4// This sample demonstrates the Data library.
5//
6// Copyright (c) 2008, Applied Informatics Software Engineering GmbH.
7// and Contributors.
8//
9// SPDX-License-Identifier: BSL-1.0
10
11
12#include "Poco/SharedPtr.h"
13#include "Poco/DateTimeFormatter.h"
14#include "Poco/SQL/SessionFactory.h"
15#include "Poco/SQL/Session.h"
16#include "Poco/SQL/TypeHandler.h"
17#include "Poco/SQL/SQLite/Connector.h"
18#include <vector>
19#include <iostream>
20
21
22using namespace Poco::SQL::Keywords;
23using Poco::DateTime;
24using Poco::DateTimeFormatter;
25using Poco::SQL::Session;
26using Poco::SQL::Statement;
27
28
29struct Person
30{
31 std::string name;
32 std::string address;
33 int age;
34 DateTime birthday;
35};
36
37
38namespace Poco {
39namespace SQL {
40
41
42template <>
43class TypeHandler<Person>
44 /// Defining a specialization of TypeHandler for Person allows us
45 /// to use the Person struct in use and into clauses.
46{
47public:
48 static std::size_t size()
49 {
50 return 4;
51 }
52
53 static void bind(std::size_t pos, const Person& person, AbstractBinder::Ptr pBinder, AbstractBinder::Direction dir)
54 {
55 TypeHandler<std::string>::bind(pos++, person.name, pBinder, dir);
56 TypeHandler<std::string>::bind(pos++, person.address, pBinder, dir);
57 TypeHandler<int>::bind(pos++, person.age, pBinder, dir);
58 TypeHandler<DateTime>::bind(pos++, person.birthday, pBinder, dir);
59 }
60
61 static void extract(std::size_t pos, Person& person, const Person& deflt, AbstractExtractor::Ptr pExtr)
62 {
63 TypeHandler<std::string>::extract(pos++, person.name, deflt.name, pExtr);
64 TypeHandler<std::string>::extract(pos++, person.address, deflt.address, pExtr);
65 TypeHandler<int>::extract(pos++, person.age, deflt.age, pExtr);
66 TypeHandler<DateTime>::extract(pos++, person.birthday, deflt.birthday, pExtr);
67 }
68
69 static void prepare(std::size_t pos, const Person& person, AbstractPreparator::Ptr pPrep)
70 {
71 TypeHandler<std::string>::prepare(pos++, person.name, pPrep);
72 TypeHandler<std::string>::prepare(pos++, person.address, pPrep);
73 TypeHandler<int>::prepare(pos++, person.age, pPrep);
74 TypeHandler<DateTime>::prepare(pos++, person.birthday, pPrep);
75 }
76};
77
78
79} } // namespace Poco::SQL
80
81
82int main(int argc, char** argv)
83{
84 // create a session
85 Session session("SQLite", "sample.db");
86
87 // drop sample table, if it exists
88 session << "DROP TABLE IF EXISTS Person", now;
89
90 // (re)create table
91 session << "CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)", now;
92
93 // insert some rows
94 Person person =
95 {
96 "Bart Simpson",
97 "Springfield",
98 10,
99 DateTime(1980, 4, 1)
100 };
101
102 Statement insert(session);
103 insert << "INSERT INTO Person VALUES(?, ?, ?, ?)",
104 use(person);
105
106 insert.execute();
107
108 person.name = "Lisa Simpson";
109 person.address = "Springfield";
110 person.age = 8;
111 person.birthday = DateTime(1982, 5, 9);
112
113 insert.execute();
114
115 // a simple query
116 Statement select(session);
117 select << "SELECT Name, Address, Age, Birthday FROM Person",
118 into(person),
119 range(0, 1); // iterate over result set one row at a time
120
121 while (!select.done())
122 {
123 select.execute();
124 std::cout << person.name << "\t"
125 << person.address << "\t"
126 << person.age << "\t"
127 << DateTimeFormatter::format(person.birthday, "%b %d %Y")
128 << std::endl;
129 }
130
131 // another query - store the result in a container
132 std::vector<Person> persons;
133 session << "SELECT Name, Address, Age, Birthday FROM Person",
134 into(persons),
135 now;
136
137 for (std::vector<Person>::const_iterator it = persons.begin(); it != persons.end(); ++it)
138 {
139 std::cout << it->name << "\t"
140 << it->address << "\t"
141 << it->age << "\t"
142 << DateTimeFormatter::format(it->birthday, "%b %d %Y")
143 << std::endl;
144 }
145
146 return 0;
147}
148