forked from tdslite/tdslite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
05-query-with-parameters.ino
274 lines (231 loc) · 9.69 KB
/
05-query-with-parameters.ino
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
/**
* ____________________________________________________
* Example code for illustrating how to bind parameters
* to parameterized SQL query, a.k.a. `how to use
* execute_rpc`
*
* Prerequisites:
* - A board with an Ethernet shield
* - A running SQL server (*)
* - tdslite library installed via Library Manager
*
* (*) The development environment container has an embedded
* SQL server exposed to host at port 14333.
*
* Tested with Arduino Uno w/ Ethernet shield.
*
* @file 05-query-with-parameters.ino
* @author mkg <me@mustafagilor.com>
* @date 22.01.2023
*
* SPDX-License-Identifier: MIT
* ____________________________________________________
*/
#include <Ethernet.h>
#include <tdslite.h>
// Serial output uses ~175 bytes of SRAM space
// and ~840 bytes of program memory.
#define SKETCH_ENABLE_SERIAL_OUTPUT
#if defined SKETCH_ENABLE_SERIAL_OUTPUT
#define SERIAL_PRINTF_PROGMEM(FMTPM, ...) \
char buf [64] = {}; \
snprintf_P(buf, sizeof(buf), FMTPM, ##__VA_ARGS__); \
Serial.print(buf);
#define SERIAL_PRINTF(FMTSTR, ...) \
[&]() { \
/* Save format string into program */ \
/* memory to save flash space */ \
static const char __fmtpm [] PROGMEM = FMTSTR; \
SERIAL_PRINTF_PROGMEM(__fmtpm, ##__VA_ARGS__) \
}()
#define SERIAL_PRINTLNF_PROGMEM(FMTPM, ...) \
SERIAL_PRINTF_PROGMEM(FMTPM, ##__VA_ARGS__) \
Serial.println(""); \
Serial.flush()
#define SERIAL_PRINTLNF(FMTSTR, ...) \
SERIAL_PRINTF(FMTSTR, ##__VA_ARGS__); \
Serial.println(""); \
Serial.flush()
#define SERIAL_PRINT_U16_AS_MB(U16SPAN) \
[](tdsl::u16char_view v) { \
for (const auto ch : v) { \
Serial.print(static_cast<char>(ch)); \
} \
}(U16SPAN)
#else
#define SERIAL_PRINTF_PROGMEM(FMTPM, ...)
#define SERIAL_PRINTF(FMTSTR, ...)
#define SERIAL_PRINTLNF_PROGMEM(FMTPM, ...)
#define SERIAL_PRINTLNF(FMTSTR, ...)
#define SERIAL_PRINT_U16_AS_MB(U16SPAN)
#endif
// --------------------------------------------------------------------------------
/**
* The network buffer.
*
* The library will use this buffer for network I/O.
*
* The buffer must be at least 512 bytes in size.
* In order to have some headroom for fragmentation
* it is recommended to allocate 768 bytes at least.
*
* The actual size need for network buffer is depends
* on your use case.
*
*/
tdsl::uint8_t net_buf [768] = {};
// --------------------------------------------------------------------------------
/**
* The tdslite driver object.
*
* tdsl::arduino_driver class is a templated type
* where the template argument is the TCP client
* implementation compatible with Arduino's
* EthernetClient interface.
*
* The client will be initialized internally.
*/
tdsl::arduino_driver<EthernetClient> driver{net_buf};
// --------------------------------------------------------------------------------
/**
* MAC address for the ethernet interface
*/
byte mac [] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xEB};
// --------------------------------------------------------------------------------
/**
* IP address for the ethernet interface.
* Change it according to your network address space
*/
IPAddress ip(192, 168, 1, 246);
// --------------------------------------------------------------------------------
/**
* The setup function initializes Serial output,
* Ethernet interface, tdslite library and then
* the database tables.
*/
void setup() {
#ifdef SKETCH_ENABLE_SERIAL_OUTPUT
Serial.begin(115200);
while (!Serial)
;
#endif
//////////////////////////
// Initialize ethernet interface
//////////////////////////
// The reason we're not using DHCP here is, this is
// a minimal example with absolute minimum space
// requirements, so the code can work on boards with
// tight memory constraints (i.e. Arduino Uno/Nano)
//
// DHCP requires UDP, UDP requires extra space.
// We're not using DHCP here to save some program
// memory and SRAM space.
SERIAL_PRINTLNF("Initializing ethernet interface");
// Try to configure ethernet interface
// with given MAC and IP
Ethernet.begin(mac, ip);
// Check if *any* ethernet hardware is detected.
if (Ethernet.hardwareStatus() == EthernetNoHardware) {
SERIAL_PRINTLNF("Error: No ethernet hardware detected!");
// Ethernet shield not detected
while (true) {
delay(1000);
}
}
//////////////////////////
// Initialize tdslite
//////////////////////////
// Declare a connection parameters struct. We will fill this struct
// with the details of the SQL server/database we want to connect to.
// We're using progmem_connection_parameters here, because we want to
// store database connection parameters in program memory in order to
// save some precious SRAM space.
decltype(driver)::progmem_connection_parameters params;
// Server's hostname or IP address.
params.server_name = TDSL_PMEMSTR("192.168.1.45"); // WL
// SQL server port number
params.port = 14333; // default port is 1433
// SQL server login user
params.user_name = TDSL_PMEMSTR("sa");
// SQL server login user password
params.password = TDSL_PMEMSTR("2022-tds-lite-test!");
// Client name(optional)
params.client_name = TDSL_PMEMSTR("arduino mega");
// App name(optional)
params.app_name = TDSL_PMEMSTR("sketch");
// Database name(optional)
params.db_name = TDSL_PMEMSTR("master");
// TDS packet size
// Recommendation: Half of the network buffer.
// This is the PDU size that TDS protocol will use.
// Given that the example has 768 bytes of network buffer space,
// we set this to 512 to allow some headroom for fragmentation.
params.packet_size = {512};
// How many times the driver should attempt to connect to the server
params.conn_retry_count = 5;
// Delay between each connection attempt (milliseconds)
params.conn_retry_delay_ms = 2000;
SERIAL_PRINTLNF("Initializing tdslite");
// Try to connect with given parameters. If connection succeeds,
// the `result` will be e_driver_error_code::success. Otherwise,
// the connection attempt has failed.
auto result = driver.connect(params);
if (not(decltype(driver)::e_driver_error_code::success == result)) {
SERIAL_PRINTLNF("Error: Database connection failed!");
// Database connection failed.
while (true) {
delay(1000);
}
}
//////////////////////////
// Initialize the database
//////////////////////////
driver.execute_query(TDSL_PMEMSTR("CREATE TABLE #example_table(a varchar(12), b int);"));
}
// --------------------------------------------------------------------------------
/**
* How many times the loop function has
* been invoked.
*/
static int loop_counter = {0};
// --------------------------------------------------------------------------------
/**
* Handle row data coming from tdsl driver
*
* @param [in] u user pointer (table_context)
* @param [in] colmd Column metadata
* @param [in] row Row information
*/
static void row_callback(void * u, const tdsl::tds_colmetadata_token & colmd,
const tdsl::tdsl_row & row) {
SERIAL_PRINTLNF("row: %.4s %d", row [0].as<tdsl::char_view>().data(),
row [1].as<tdsl::int32_t>());
}
// --------------------------------------------------------------------------------
/**
* The loop function executes INSERT query every
* 1 second, and SELECT query every 10 seconds.
*/
void loop() {
// Your queries goes here.
auto query{TDSL_PMEMSTR("INSERT INTO #example_table VALUES('test', 1)")};
SERIAL_PRINTF("Executing query: ");
SERIAL_PRINTLNF_PROGMEM(query.raw_data());
auto result = driver.execute_query(query);
SERIAL_PRINTLNF("Rows affected: %d", result.affected_rows);
// Execute SELECT query on every tenth loop.
if (0 == (loop_counter % 10)) {
auto query{TDSL_PMEMSTR("SELECT * FROM #example_table WHERE a = @p0 AND b = @p1")};
SERIAL_PRINTF("Executing query: ");
SERIAL_PRINTLNF_PROGMEM(query.raw_data());
// We're using the row
tdsl::sql_parameter_varchar a{"test"};
tdsl::sql_parameter_int b{1};
tdsl::sql_parameter_binding params []{a, b};
driver.execute_rpc(query, params, tdsl::rpc_mode::executesql, row_callback);
// SERIAL_PRINTLNF("Result: %d", ra);
}
delay(1000);
// Increment the loop counter.
loop_counter++;
}