· 4 years ago · Jun 14, 2021, 06:04 PM
1// example of putting a dataset into SQL Lite this might have been parsed from a string
2// to the server and contains the products costs and quantities
3//
4// for SQL Lite you will need :-
5// sudo apt update
6// sudo apt-cache search sqlite
7// sudo apt install sqlite3
8//
9// To Compile as well as the header file here
10// https://github.com/dmitigr/sqlixx
11// you also need
12// sudo apt-get install libsqlite3-dev
13//
14// to compile
15// g++-7 -std=c++17 batch_request.cpp -o batch_request -lsqlite3
16//
17#include <cstdlib>
18#include <iostream>
19#include <string>
20#include <optional>
21#include "sqlixx.hpp"
22
23#include <fstream>
24#include <algorithm>
25#include <map>
26
27using namespace std;
28
29int g_index {0};
30
31struct product_word_t {
32 unsigned char ourText[100];
33 unsigned char ourNums[20];
34 unsigned char ourMoneys[20];
35};
36
37// parses string input to an int or null
38std::optional<double> ParseStringToDouble(string& arg)
39{
40 try
41 {
42 return { std::stod(arg,0) }; // converts first number
43 }
44 catch (...)
45 {
46 std::cout << "cannot convert \'" << arg << "\' to int!\n";
47 }
48
49 return { };
50}
51
52int main()
53{
54 namespace sqlixx = dmitigr::sqlixx;
55 sqlixx::Connection c{"", SQLITE_OPEN_READWRITE | SQLITE_OPEN_MEMORY};
56 product_word_t inputCatalog;
57 std::string printitdesc;
58 std::string printitmoneys;
59 std::string printitqty;
60 std::string costConvertTofloat;
61 std::string qtyConvertTofloat;
62
63 // ============= Create the table for products costs and quantity =======================
64 c.execute(
65 R"(
66 create table if not exists prod_cost_tab(
67 id integer primary key AUTOINCREMENT,
68 cost real,
69 qty real,
70 ct text,
71 printmoney text,
72 printqty text,
73 cb blob)
74 )"
75 );
76
77 // =============== Truncate the product cost table. ======================================
78 c.execute("delete from prod_cost_tab");
79
80 // =============== set up the message object with dummy data ============================
81 strcpy((char*)&inputCatalog.ourText[0],"This is an example product");
82 strcpy((char*)&inputCatalog.ourMoneys[0],"$10.75");
83 strcpy((char*)&inputCatalog.ourNums[0]," 5.6Kg");
84
85 // =============== get the data from the message object =================================
86 char * pout = (char*) &inputCatalog.ourText[0];
87 printitdesc.append(pout);
88 pout = (char*) &inputCatalog.ourMoneys[0];
89 printitmoneys.append(pout);
90 costConvertTofloat.append(pout+1); // append the number without the leading currency identifier
91 auto cost_float = ParseStringToDouble(costConvertTofloat);
92
93 pout = (char*) &inputCatalog.ourNums[0];
94 printitqty.append(pout);
95 qtyConvertTofloat.append(pout); // append the number can have e.g kg after but no leading char
96 auto qty_float = ParseStringToDouble(qtyConvertTofloat);
97
98 // =========== Populate the table with the information passed from the structure ==========
99 if ((qty_float) && (cost_float)) // qty and cost are valid
100 {
101 auto s = c.prepare("insert into prod_cost_tab(id, cost, qty, ct, printmoney, printqty, cb) values(?, ?, ?, ?, ?, ?, ?)");
102 c.execute("begin");
103 s.execute(g_index, *cost_float, *qty_float, printitdesc, printitmoneys, printitqty, sqlixx::Blob{"four", 4});
104 c.execute("end");
105 g_index++;
106 }
107
108 // ============== Query the product cost table. ===========================================
109 c.execute([](const sqlixx::Statement& s)
110 {
111 const auto b = s.result<sqlixx::Blob>("cb");
112 const std::string_view cb{static_cast<const char*>(b.data()), b.size()};
113 const auto t1 = s.result<sqlixx::Text_utf8>("ct");
114 const auto t2 = s.result<std::string>("ct");
115 const auto t3 = s.result<std::string_view>("ct");
116 assert(!std::strcmp(t1.data(), t2.data()) && (t2 == t3));
117 std::cout << "id: " << s.result<int>("id") << "\n"
118 << "number cost: " << s.result<double>("cost") << "\n"
119 << "quantity: " << s.result<double>("qty") << "\n"
120 << "description: " << t3 << "\n"
121 << "blob: " << cb << "\n";
122 const auto t4 = s.result<sqlixx::Text_utf8>("printmoney");
123 const auto t5 = s.result<std::string>("printmoney");
124 const auto t6 = s.result<std::string_view>("printmoney");
125 assert(!std::strcmp(t4.data(), t5.data()) && (t5 == t6));
126 const auto t7 = s.result<sqlixx::Text_utf8>("printqty");
127 const auto t8 = s.result<std::string>("printqty");
128 const auto t9 = s.result<std::string_view>("printqty");
129 assert(!std::strcmp(t7.data(), t8.data()) && (t8 == t9));
130 //std::cout << t1 << " " << t4 << " " << t7 << std::endl;
131 },
132 "select * from prod_cost_tab where id >= ? and id < ?", 0, g_index);
133
134}