Merge lp:~chuck-bell/mysql-arduino/release-1.0.4-ga into lp:~chuck-bell/mysql-arduino/release-1.0.3-rc
- release-1.0.4-ga
- Merge into release-1.0.3-rc
Proposed by
Alexy
Status: | Needs review |
---|---|
Proposed branch: | lp:~chuck-bell/mysql-arduino/release-1.0.4-ga |
Merge into: | lp:~chuck-bell/mysql-arduino/release-1.0.3-rc |
Diff against target: |
662 lines (+273/-303) 6 files modified
CHANGES.txt (+5/-0) Readme.txt (+3/-37) examples/mysql_connector/mysql_connector.ino (+258/-0) mysql.cpp (+4/-6) mysql.h (+3/-2) mysql_connector.ino (+0/-258) |
To merge this branch: | bzr merge lp:~chuck-bell/mysql-arduino/release-1.0.4-ga |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Chuck Bell | Pending | ||
Review via email: mp+275301@code.launchpad.net |
Commit message
Description of the change
please check this https:/
We want port your beautiful library to esp8266 arduino compatible system . Can you help us ?
I not find other method to communicate to you
To post a comment you must log in.
Unmerged revisions
- 6. By Chuck Bell
-
Adding latest updates.
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'CHANGES.txt' | |||
2 | --- CHANGES.txt 2015-03-04 15:33:05 +0000 | |||
3 | +++ CHANGES.txt 2015-10-22 10:38:35 +0000 | |||
4 | @@ -3,6 +3,11 @@ | |||
5 | 3 | This file contains a brief summary of changes made from previous versions of | 3 | This file contains a brief summary of changes made from previous versions of |
6 | 4 | the connector. | 4 | the connector. |
7 | 5 | 5 | ||
8 | 6 | 1.0.4ga - July 2015 | ||
9 | 7 | -------------------- | ||
10 | 8 | * Fixed a defect in the get_next_row() method. | ||
11 | 9 | * Added the reference manual. Yippee! | ||
12 | 10 | |||
13 | 6 | 1.0.3rc - March 2015 | 11 | 1.0.3rc - March 2015 |
14 | 7 | -------------------- | 12 | -------------------- |
15 | 8 | * Code has been changed slightly to help with long latency issues over | 13 | * Code has been changed slightly to help with long latency issues over |
16 | 9 | 14 | ||
17 | === modified file 'Readme.txt' | |||
18 | --- Readme.txt 2013-10-23 19:56:58 +0000 | |||
19 | +++ Readme.txt 2015-10-22 10:38:35 +0000 | |||
20 | @@ -77,40 +77,6 @@ | |||
21 | 77 | 77 | ||
22 | 78 | Documentation | 78 | Documentation |
23 | 79 | ------------- | 79 | ------------- |
61 | 80 | The documentation for the connector library is a work in progress. In the | 80 | The documentation for the connector library is included with the source |
62 | 81 | meantime, I have written a couple of blogs about the connector that explain | 81 | files. I have also written a couple of blogs about the connector that explain |
63 | 82 | how to use it. Please refer to the blogs until such time there is sufficient | 82 | how to use it. |
27 | 83 | documentation available. | ||
28 | 84 | |||
29 | 85 | |||
30 | 86 | Dependency: SHA1 library | ||
31 | 87 | ------------------------ | ||
32 | 88 | The Connector/Arduino requires the SHA1 library from the following link. | ||
33 | 89 | |||
34 | 90 | Note: The single file download already has this library with the changes | ||
35 | 91 | described below already applied so there is nothing for you to do. However, if | ||
36 | 92 | you cloned the connector library instead of using the download, please download | ||
37 | 93 | the SHA1 library, install it, and read on for modifications needed to this | ||
38 | 94 | library. | ||
39 | 95 | |||
40 | 96 | http://code.google.com/p/cryptosuite/downloads/list | ||
41 | 97 | |||
42 | 98 | You must download and install that library first and include it in your | ||
43 | 99 | project. The following shows all of the library header files you need to | ||
44 | 100 | include. | ||
45 | 101 | |||
46 | 102 | #include <SPI.h> | ||
47 | 103 | #include <Ethernet.h> | ||
48 | 104 | #include <sha1.h> | ||
49 | 105 | #include <avr/pgmspace.h> | ||
50 | 106 | #include "mysql.h" | ||
51 | 107 | |||
52 | 108 | NOTICE: The SHA1 library may not compile correctly in your IDE. A change is | ||
53 | 109 | needed to ensure it will compile correctly. See the sha1.diff file for | ||
54 | 110 | details. | ||
55 | 111 | |||
56 | 112 | We also do not need some of the features in the SHA1 library and since it takes | ||
57 | 113 | up space, we can eliminate them. If you would like to remove the unneeded code | ||
58 | 114 | from the SHA1 library, apply the sha1_no256.diff file to remove them and | ||
59 | 115 | delete the sha1256.h and sh1256.cpp files. This will save you about 2k of | ||
60 | 116 | program space. | ||
64 | 117 | 83 | ||
65 | === added directory 'examples/mysql_connector' | |||
66 | === added file 'examples/mysql_connector/mysql_connector.ino' | |||
67 | --- examples/mysql_connector/mysql_connector.ino 1970-01-01 00:00:00 +0000 | |||
68 | +++ examples/mysql_connector/mysql_connector.ino 2015-10-22 10:38:35 +0000 | |||
69 | @@ -0,0 +1,258 @@ | |||
70 | 1 | /* | ||
71 | 2 | Example queries for using the Connector/Arduino library. | ||
72 | 3 | |||
73 | 4 | This file contains a number of examples as discussed in the Readme. It | ||
74 | 5 | also contains an example of how to use the library with the WiFi shield. | ||
75 | 6 | |||
76 | 7 | In order to run these examples, you must have the world sample database | ||
77 | 8 | installed (http://dev.mysql.com/doc/index-other.html) and the following | ||
78 | 9 | databases and tables created: | ||
79 | 10 | |||
80 | 11 | CREATE DATABASE test_arduino; | ||
81 | 12 | CREATE TABLE test_arduino.hello (msg char(50), msg_date timestamp); | ||
82 | 13 | CREATE TABLE temps (temp_c float, temp_date timestamp); | ||
83 | 14 | |||
84 | 15 | Take some time to read through the code before you load and run it. | ||
85 | 16 | |||
86 | 17 | NOTICE: There are a lot of queries in this file. Together they use a lot | ||
87 | 18 | of program space - especially the dtostrf() example. If you attempt | ||
88 | 19 | to run all of these at one time, depending on your board you may | ||
89 | 20 | run out of space or the sketch may run out of memory and hang. | ||
90 | 21 | Thus, if you want to run these tests, I recommend doing them | ||
91 | 22 | *one at a time*. | ||
92 | 23 | |||
93 | 24 | Because of this, all of the examples are commented out. To run one, | ||
94 | 25 | just uncomment it and its corresponding string constant at the | ||
95 | 26 | top of the file and off you go! :) | ||
96 | 27 | */ | ||
97 | 28 | #include <SPI.h> | ||
98 | 29 | #include <Ethernet.h> | ||
99 | 30 | #include <sha1.h> | ||
100 | 31 | //#include <avr/dtostrf.h> // Add this for the Due if you need drostrf | ||
101 | 32 | #include <stdlib.h> | ||
102 | 33 | //#include <WiFi.h> // Use this for WiFi | ||
103 | 34 | #include <mysql.h> | ||
104 | 35 | |||
105 | 36 | byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; | ||
106 | 37 | IPAddress server_addr(10,0,1,13); // Supply the IP of the MySQL *server* here | ||
107 | 38 | |||
108 | 39 | char user[] = "root"; // can be anything but the user must have | ||
109 | 40 | char password[] = "secret"; // access rights to connect (host must permit it) | ||
110 | 41 | |||
111 | 42 | // WiFi card example | ||
112 | 43 | //char ssid[] = "my_lonely_ssid"; | ||
113 | 44 | //char pass[] = "horse_no_name"; | ||
114 | 45 | |||
115 | 46 | Connector my_conn; // The Connector/Arduino reference | ||
116 | 47 | |||
117 | 48 | // String constants for examples. Uncomment those you need. | ||
118 | 49 | |||
119 | 50 | //const char TEST_SELECT_QUERY[] = "SELECT * FROM world.city LIMIT 10"; | ||
120 | 51 | //const char QUERY_POP[] = "SELECT population FROM world.city WHERE name = 'New York'"; | ||
121 | 52 | //const char INSERT_TEXT[] = "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)"; | ||
122 | 53 | //const char INSERT_DATA[] = "INSERT INTO test_arduino.temps VALUES (%s, NULL)"; | ||
123 | 54 | //const char HELLO_SQL[] = "SELECT * from test_arduino.hello"; | ||
124 | 55 | //const char HELLO_DATA[] = "SELECT * from test_arduino.temps"; | ||
125 | 56 | |||
126 | 57 | /** | ||
127 | 58 | * do_query - execute a query and display results | ||
128 | 59 | * | ||
129 | 60 | * This method demonstrates how to execute a query, get the column | ||
130 | 61 | * names and print them, then read rows printing the values. It | ||
131 | 62 | * is a mirror of the show_results() example in the connector class. | ||
132 | 63 | * | ||
133 | 64 | * You can use this method as a template for writing methods that | ||
134 | 65 | * must iterate over rows from a SELECT and operate on the values read. | ||
135 | 66 | * | ||
136 | 67 | */ | ||
137 | 68 | void do_query(const char *q) { | ||
138 | 69 | column_names *c; // pointer to column values | ||
139 | 70 | row_values *r; // pointer to row values | ||
140 | 71 | |||
141 | 72 | // First, execute query. If it returns a value pointer, | ||
142 | 73 | // we have a result set to process. If not, we exit. | ||
143 | 74 | if (!my_conn.cmd_query(q)) { | ||
144 | 75 | return; | ||
145 | 76 | } | ||
146 | 77 | |||
147 | 78 | // Next, we read the column names and display them. | ||
148 | 79 | // | ||
149 | 80 | // NOTICE: You must *always* read the column names even if | ||
150 | 81 | // you do not use them. This is so the connector can | ||
151 | 82 | // read the data out of the buffer. Row data follows the | ||
152 | 83 | // column data and thus must be read first. | ||
153 | 84 | |||
154 | 85 | c = my_conn.get_columns(); | ||
155 | 86 | for (int i = 0; i < c->num_fields; i++) { | ||
156 | 87 | Serial.print(c->fields[i]->name); | ||
157 | 88 | if (i < c->num_fields - 1) { | ||
158 | 89 | Serial.print(","); | ||
159 | 90 | } | ||
160 | 91 | } | ||
161 | 92 | Serial.println(); | ||
162 | 93 | |||
163 | 94 | // Next, we use the get_next_row() iterator and read rows printing | ||
164 | 95 | // the values returned until the get_next_row() returns NULL. | ||
165 | 96 | |||
166 | 97 | int num_cols = c->num_fields; | ||
167 | 98 | int rows = 0; | ||
168 | 99 | do { | ||
169 | 100 | r = my_conn.get_next_row(); | ||
170 | 101 | if (r) { | ||
171 | 102 | rows++; | ||
172 | 103 | for (int i = 0; i < num_cols; i++) { | ||
173 | 104 | Serial.print(r->values[i]); | ||
174 | 105 | if (i < num_cols - 1) { | ||
175 | 106 | Serial.print(", "); | ||
176 | 107 | } | ||
177 | 108 | } | ||
178 | 109 | Serial.println(); | ||
179 | 110 | |||
180 | 111 | // Note: we free the row read to free the memory allocated for it. | ||
181 | 112 | // You should do this after you've processed the row. | ||
182 | 113 | |||
183 | 114 | my_conn.free_row_buffer(); | ||
184 | 115 | } | ||
185 | 116 | } while (r); | ||
186 | 117 | Serial.print(rows); | ||
187 | 118 | Serial.println(" rows in result."); | ||
188 | 119 | |||
189 | 120 | // Finally, we are done so we free the column buffers | ||
190 | 121 | |||
191 | 122 | my_conn.free_columns_buffer(); | ||
192 | 123 | } | ||
193 | 124 | |||
194 | 125 | void setup() { | ||
195 | 126 | Serial.begin(115200); | ||
196 | 127 | while (!Serial); // wait for serial port to connect. Needed for Leonardo only | ||
197 | 128 | |||
198 | 129 | Ethernet.begin(mac_addr); | ||
199 | 130 | |||
200 | 131 | // WiFi section | ||
201 | 132 | // int status = WiFi.begin(ssid, pass); | ||
202 | 133 | // // if you're not connected, stop here: | ||
203 | 134 | // if ( status != WL_CONNECTED) { | ||
204 | 135 | // Serial.println("Couldn't get a wifi connection"); | ||
205 | 136 | // while(true); | ||
206 | 137 | // } | ||
207 | 138 | // // if you are connected, print out info about the connection: | ||
208 | 139 | // else { | ||
209 | 140 | // Serial.println("Connected to network"); | ||
210 | 141 | // IPAddress ip = WiFi.localIP(); | ||
211 | 142 | // Serial.print("My IP address is: "); | ||
212 | 143 | // Serial.println(ip); | ||
213 | 144 | // } | ||
214 | 145 | |||
215 | 146 | delay(1000); | ||
216 | 147 | Serial.println("Connecting..."); | ||
217 | 148 | if (my_conn.mysql_connect(server_addr, 3306, user, password)) { | ||
218 | 149 | delay(1000); | ||
219 | 150 | } | ||
220 | 151 | else | ||
221 | 152 | Serial.println("Connection failed."); | ||
222 | 153 | |||
223 | 154 | // | ||
224 | 155 | // SELECT Examples | ||
225 | 156 | // | ||
226 | 157 | |||
227 | 158 | /* | ||
228 | 159 | // EXAMPLE 1 | ||
229 | 160 | |||
230 | 161 | // SELECT query returning rows (built-in methods) | ||
231 | 162 | // Here we simply read the columns, print the names, then loop through | ||
232 | 163 | // the rows printing the values read. We set a limit to make this something | ||
233 | 164 | // that executes in a reasonable timeframe. | ||
234 | 165 | |||
235 | 166 | my_conn.cmd_query(TEST_SELECT_QUERY); | ||
236 | 167 | my_conn.show_results(); | ||
237 | 168 | */ | ||
238 | 169 | /* | ||
239 | 170 | // EXAMPLE 2 | ||
240 | 171 | |||
241 | 172 | // SELECT query returning rows (custom method) | ||
242 | 173 | // Here we execute the same query as above but use a custom method for reading | ||
243 | 174 | // and displaying the results. See the do_query() method above for more | ||
244 | 175 | // information about how it works. | ||
245 | 176 | |||
246 | 177 | do_query(TEST_SELECT_QUERY); | ||
247 | 178 | */ | ||
248 | 179 | /* | ||
249 | 180 | // EXAMPLE 3 | ||
250 | 181 | |||
251 | 182 | // SELECT query for lookup value (1 row returned) | ||
252 | 183 | // Here we get a value from the database and use it. | ||
253 | 184 | |||
254 | 185 | long head_count = 0; | ||
255 | 186 | my_conn.cmd_query(QUERY_POP); | ||
256 | 187 | |||
257 | 188 | // We ignore the columns but we have to read them to get that data out of the queue | ||
258 | 189 | |||
259 | 190 | my_conn.get_columns(); | ||
260 | 191 | |||
261 | 192 | // Now we read the rows. | ||
262 | 193 | |||
263 | 194 | row_values *row = NULL; | ||
264 | 195 | do { | ||
265 | 196 | row = my_conn.get_next_row(); | ||
266 | 197 | // We use the first value returned in the row - population of NYC! | ||
267 | 198 | if (row != NULL) { | ||
268 | 199 | head_count = atol(row->values[0]); | ||
269 | 200 | } | ||
270 | 201 | } while (row != NULL); | ||
271 | 202 | |||
272 | 203 | // We're done with the buffers so Ok to clear them (and save precious memory). | ||
273 | 204 | |||
274 | 205 | my_conn.free_columns_buffer(); | ||
275 | 206 | my_conn.free_row_buffer(); | ||
276 | 207 | |||
277 | 208 | // Now, let's do something with the data. | ||
278 | 209 | |||
279 | 210 | Serial.print("NYC pop = "); | ||
280 | 211 | Serial.println(head_count); | ||
281 | 212 | */ | ||
282 | 213 | |||
283 | 214 | // | ||
284 | 215 | // INSERT Examples | ||
285 | 216 | // | ||
286 | 217 | |||
287 | 218 | /* | ||
288 | 219 | // EXAMPLE 4 | ||
289 | 220 | |||
290 | 221 | // Inserting static text into a table. | ||
291 | 222 | // Here we simply insert text data into a table. No conversion needed. | ||
292 | 223 | // It also demonstrates the use of NULL to initiate a timestamp value. | ||
293 | 224 | |||
294 | 225 | my_conn.cmd_query(INSERT_TEXT); | ||
295 | 226 | // Now, let's check our results. | ||
296 | 227 | do_query(HELLO_SQL); | ||
297 | 228 | */ | ||
298 | 229 | /* | ||
299 | 230 | // EXAMPLE 5 | ||
300 | 231 | |||
301 | 232 | // Inserting real time data into a table. | ||
302 | 233 | // Here we want to insert a row into a table but in this case we are | ||
303 | 234 | // simulating reading the data from a sensor or some other component. | ||
304 | 235 | // In this case, we 'simulate' reading temperature in celsius. | ||
305 | 236 | |||
306 | 237 | float value_read = 26.9; | ||
307 | 238 | |||
308 | 239 | // To use the value in an INSERT statement, we must construct a string | ||
309 | 240 | // that has the value inserted in it. For example, what we want is: | ||
310 | 241 | // 'INSERT INTO test_arduino.temps VALUES (26.9, NULL)' but the 26.9 is | ||
311 | 242 | // held in the variable 'value_read'. So, we use a character string | ||
312 | 243 | // formatting operation sprintf(). Notice here we must convert the float | ||
313 | 244 | // to a string first and we use the %s specifier in the INSERT_DATA | ||
314 | 245 | // string. | ||
315 | 246 | |||
316 | 247 | char query[64]; | ||
317 | 248 | char temperature[10]; | ||
318 | 249 | dtostrf(value_read, 1, 1, temperature); | ||
319 | 250 | sprintf(query, INSERT_DATA, temperature); | ||
320 | 251 | my_conn.cmd_query(query); | ||
321 | 252 | // Now, let's check our results. | ||
322 | 253 | do_query(HELLO_DATA); | ||
323 | 254 | */ | ||
324 | 255 | } | ||
325 | 256 | |||
326 | 257 | void loop() { | ||
327 | 258 | } | ||
328 | 0 | 259 | ||
329 | === modified file 'mysql.cpp' | |||
330 | --- mysql.cpp 2015-03-04 15:14:29 +0000 | |||
331 | +++ mysql.cpp 2015-10-22 10:38:35 +0000 | |||
332 | @@ -25,6 +25,7 @@ | |||
333 | 25 | Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. | 25 | Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. |
334 | 26 | Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. | 26 | Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. |
335 | 27 | Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. | 27 | Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. |
336 | 28 | Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015. | ||
337 | 28 | */ | 29 | */ |
338 | 29 | #include "Arduino.h" | 30 | #include "Arduino.h" |
339 | 30 | #include "mysql.h" | 31 | #include "mysql.h" |
340 | @@ -1008,7 +1009,7 @@ | |||
341 | 1008 | * in the class. | 1009 | * in the class. |
342 | 1009 | * | 1010 | * |
343 | 1010 | */ | 1011 | */ |
345 | 1011 | boolean Connector::get_row_values() { | 1012 | int Connector::get_row_values() { |
346 | 1012 | int res = 0; | 1013 | int res = 0; |
347 | 1013 | int offset = 0; | 1014 | int offset = 0; |
348 | 1014 | 1015 | ||
349 | @@ -1016,7 +1017,7 @@ | |||
350 | 1016 | // are read. | 1017 | // are read. |
351 | 1017 | if (!columns_read) { | 1018 | if (!columns_read) { |
352 | 1018 | print_message(READ_COLS, true); | 1019 | print_message(READ_COLS, true); |
354 | 1019 | return true; | 1020 | return EOF_PACKET; |
355 | 1020 | } | 1021 | } |
356 | 1021 | // Drop any row data already read | 1022 | // Drop any row data already read |
357 | 1022 | free_row_buffer(); | 1023 | free_row_buffer(); |
358 | @@ -1029,10 +1030,7 @@ | |||
359 | 1029 | row.values[f] = read_string(&offset); | 1030 | row.values[f] = read_string(&offset); |
360 | 1030 | } | 1031 | } |
361 | 1031 | } | 1032 | } |
366 | 1032 | else { | 1033 | return res; |
363 | 1033 | return res; | ||
364 | 1034 | } | ||
365 | 1035 | return true; | ||
367 | 1036 | } | 1034 | } |
368 | 1037 | 1035 | ||
369 | 1038 | #endif | 1036 | #endif |
370 | 1039 | 1037 | ||
371 | === modified file 'mysql.h' | |||
372 | --- mysql.h 2015-03-04 15:14:29 +0000 | |||
373 | +++ mysql.h 2015-10-22 10:38:35 +0000 | |||
374 | @@ -30,6 +30,7 @@ | |||
375 | 30 | Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. | 30 | Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. |
376 | 31 | Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. | 31 | Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. |
377 | 32 | Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. | 32 | Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. |
378 | 33 | Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015. | ||
379 | 33 | */ | 34 | */ |
380 | 34 | #ifndef mysql_h | 35 | #ifndef mysql_h |
381 | 35 | #define mysql_h | 36 | #define mysql_h |
382 | @@ -50,7 +51,7 @@ | |||
383 | 50 | #define EOF_PACKET 0xfe | 51 | #define EOF_PACKET 0xfe |
384 | 51 | #define ERROR_PACKET 0xff | 52 | #define ERROR_PACKET 0xff |
385 | 52 | #define MAX_FIELDS 0x20 // Maximum number of fields. Reduce to save memory. Default=32 | 53 | #define MAX_FIELDS 0x20 // Maximum number of fields. Reduce to save memory. Default=32 |
387 | 53 | #define VERSION_STR "1.0.3rc" | 54 | #define VERSION_STR "1.0.4ga" |
388 | 54 | 55 | ||
389 | 55 | #if defined WITH_SELECT | 56 | #if defined WITH_SELECT |
390 | 56 | 57 | ||
391 | @@ -157,7 +158,7 @@ | |||
392 | 157 | int get_field(field_struct *fs); | 158 | int get_field(field_struct *fs); |
393 | 158 | int get_row(); | 159 | int get_row(); |
394 | 159 | boolean get_fields(); | 160 | boolean get_fields(); |
396 | 160 | boolean get_row_values(); | 161 | int get_row_values(); |
397 | 161 | column_names *query_result(); | 162 | column_names *query_result(); |
398 | 162 | #endif | 163 | #endif |
399 | 163 | 164 | ||
400 | 164 | 165 | ||
401 | === removed file 'mysql_connector.ino' | |||
402 | --- mysql_connector.ino 2013-10-23 19:56:58 +0000 | |||
403 | +++ mysql_connector.ino 1970-01-01 00:00:00 +0000 | |||
404 | @@ -1,258 +0,0 @@ | |||
405 | 1 | /* | ||
406 | 2 | Example queries for using the Connector/Arduino library. | ||
407 | 3 | |||
408 | 4 | This file contains a number of examples as discussed in the Readme. It | ||
409 | 5 | also contains an example of how to use the library with the WiFi shield. | ||
410 | 6 | |||
411 | 7 | In order to run these examples, you must have the world sample database | ||
412 | 8 | installed (http://dev.mysql.com/doc/index-other.html) and the following | ||
413 | 9 | databases and tables created: | ||
414 | 10 | |||
415 | 11 | CREATE DATABASE test_arduino; | ||
416 | 12 | CREATE TABLE test_arduino.hello (msg char(50), msg_date timestamp); | ||
417 | 13 | CREATE TABLE temps (temp_c float, temp_date timestamp); | ||
418 | 14 | |||
419 | 15 | Take some time to read through the code before you load and run it. | ||
420 | 16 | |||
421 | 17 | NOTICE: There are a lot of queries in this file. Together they use a lot | ||
422 | 18 | of program space - especially the dtostrf() example. If you attempt | ||
423 | 19 | to run all of these at one time, depending on your board you may | ||
424 | 20 | run out of space or the sketch may run out of memory and hang. | ||
425 | 21 | Thus, if you want to run these tests, I recommend doing them | ||
426 | 22 | *one at a time*. | ||
427 | 23 | |||
428 | 24 | Because of this, all of the examples are commented out. To run one, | ||
429 | 25 | just uncomment it and its corresponding string constant at the | ||
430 | 26 | top of the file and off you go! :) | ||
431 | 27 | */ | ||
432 | 28 | #include <SPI.h> | ||
433 | 29 | #include <Ethernet.h> | ||
434 | 30 | #include <sha1.h> | ||
435 | 31 | //#include <avr/dtostrf.h> // Add this for the Due if you need drostrf | ||
436 | 32 | #include <stdlib.h> | ||
437 | 33 | //#include <WiFi.h> // Use this for WiFi | ||
438 | 34 | #include <mysql.h> | ||
439 | 35 | |||
440 | 36 | byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; | ||
441 | 37 | IPAddress server_addr(10,0,1,13); // Supply the IP of the MySQL *server* here | ||
442 | 38 | |||
443 | 39 | char user[] = "root"; // can be anything but the user must have | ||
444 | 40 | char password[] = "secret"; // access rights to connect (host must permit it) | ||
445 | 41 | |||
446 | 42 | // WiFi card example | ||
447 | 43 | //char ssid[] = "my_lonely_ssid"; | ||
448 | 44 | //char pass[] = "horse_no_name"; | ||
449 | 45 | |||
450 | 46 | Connector my_conn; // The Connector/Arduino reference | ||
451 | 47 | |||
452 | 48 | // String constants for examples. Uncomment those you need. | ||
453 | 49 | |||
454 | 50 | //const char TEST_SELECT_QUERY[] = "SELECT * FROM world.city LIMIT 10"; | ||
455 | 51 | //const char QUERY_POP[] = "SELECT population FROM world.city WHERE name = 'New York'"; | ||
456 | 52 | //const char INSERT_TEXT[] = "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)"; | ||
457 | 53 | //const char INSERT_DATA[] = "INSERT INTO test_arduino.temps VALUES (%s, NULL)"; | ||
458 | 54 | //const char HELLO_SQL[] = "SELECT * from test_arduino.hello"; | ||
459 | 55 | //const char HELLO_DATA[] = "SELECT * from test_arduino.temps"; | ||
460 | 56 | |||
461 | 57 | /** | ||
462 | 58 | * do_query - execute a query and display results | ||
463 | 59 | * | ||
464 | 60 | * This method demonstrates how to execute a query, get the column | ||
465 | 61 | * names and print them, then read rows printing the values. It | ||
466 | 62 | * is a mirror of the show_results() example in the connector class. | ||
467 | 63 | * | ||
468 | 64 | * You can use this method as a template for writing methods that | ||
469 | 65 | * must iterate over rows from a SELECT and operate on the values read. | ||
470 | 66 | * | ||
471 | 67 | */ | ||
472 | 68 | void do_query(const char *q) { | ||
473 | 69 | column_names *c; // pointer to column values | ||
474 | 70 | row_values *r; // pointer to row values | ||
475 | 71 | |||
476 | 72 | // First, execute query. If it returns a value pointer, | ||
477 | 73 | // we have a result set to process. If not, we exit. | ||
478 | 74 | if (!my_conn.cmd_query(q)) { | ||
479 | 75 | return; | ||
480 | 76 | } | ||
481 | 77 | |||
482 | 78 | // Next, we read the column names and display them. | ||
483 | 79 | // | ||
484 | 80 | // NOTICE: You must *always* read the column names even if | ||
485 | 81 | // you do not use them. This is so the connector can | ||
486 | 82 | // read the data out of the buffer. Row data follows the | ||
487 | 83 | // column data and thus must be read first. | ||
488 | 84 | |||
489 | 85 | c = my_conn.get_columns(); | ||
490 | 86 | for (int i = 0; i < c->num_fields; i++) { | ||
491 | 87 | Serial.print(c->fields[i]->name); | ||
492 | 88 | if (i < c->num_fields - 1) { | ||
493 | 89 | Serial.print(","); | ||
494 | 90 | } | ||
495 | 91 | } | ||
496 | 92 | Serial.println(); | ||
497 | 93 | |||
498 | 94 | // Next, we use the get_next_row() iterator and read rows printing | ||
499 | 95 | // the values returned until the get_next_row() returns NULL. | ||
500 | 96 | |||
501 | 97 | int num_cols = c->num_fields; | ||
502 | 98 | int rows = 0; | ||
503 | 99 | do { | ||
504 | 100 | r = my_conn.get_next_row(); | ||
505 | 101 | if (r) { | ||
506 | 102 | rows++; | ||
507 | 103 | for (int i = 0; i < num_cols; i++) { | ||
508 | 104 | Serial.print(r->values[i]); | ||
509 | 105 | if (i < num_cols - 1) { | ||
510 | 106 | Serial.print(", "); | ||
511 | 107 | } | ||
512 | 108 | } | ||
513 | 109 | Serial.println(); | ||
514 | 110 | |||
515 | 111 | // Note: we free the row read to free the memory allocated for it. | ||
516 | 112 | // You should do this after you've processed the row. | ||
517 | 113 | |||
518 | 114 | my_conn.free_row_buffer(); | ||
519 | 115 | } | ||
520 | 116 | } while (r); | ||
521 | 117 | Serial.print(rows); | ||
522 | 118 | Serial.println(" rows in result."); | ||
523 | 119 | |||
524 | 120 | // Finally, we are done so we free the column buffers | ||
525 | 121 | |||
526 | 122 | my_conn.free_columns_buffer(); | ||
527 | 123 | } | ||
528 | 124 | |||
529 | 125 | void setup() { | ||
530 | 126 | Serial.begin(115200); | ||
531 | 127 | while (!Serial); // wait for serial port to connect. Needed for Leonardo only | ||
532 | 128 | |||
533 | 129 | Ethernet.begin(mac_addr); | ||
534 | 130 | |||
535 | 131 | // WiFi section | ||
536 | 132 | // int status = WiFi.begin(ssid, pass); | ||
537 | 133 | // // if you're not connected, stop here: | ||
538 | 134 | // if ( status != WL_CONNECTED) { | ||
539 | 135 | // Serial.println("Couldn't get a wifi connection"); | ||
540 | 136 | // while(true); | ||
541 | 137 | // } | ||
542 | 138 | // // if you are connected, print out info about the connection: | ||
543 | 139 | // else { | ||
544 | 140 | // Serial.println("Connected to network"); | ||
545 | 141 | // IPAddress ip = WiFi.localIP(); | ||
546 | 142 | // Serial.print("My IP address is: "); | ||
547 | 143 | // Serial.println(ip); | ||
548 | 144 | // } | ||
549 | 145 | |||
550 | 146 | delay(1000); | ||
551 | 147 | Serial.println("Connecting..."); | ||
552 | 148 | if (my_conn.mysql_connect(server_addr, 3306, user, password)) { | ||
553 | 149 | delay(1000); | ||
554 | 150 | } | ||
555 | 151 | else | ||
556 | 152 | Serial.println("Connection failed."); | ||
557 | 153 | |||
558 | 154 | // | ||
559 | 155 | // SELECT Examples | ||
560 | 156 | // | ||
561 | 157 | |||
562 | 158 | /* | ||
563 | 159 | // EXAMPLE 1 | ||
564 | 160 | |||
565 | 161 | // SELECT query returning rows (built-in methods) | ||
566 | 162 | // Here we simply read the columns, print the names, then loop through | ||
567 | 163 | // the rows printing the values read. We set a limit to make this something | ||
568 | 164 | // that executes in a reasonable timeframe. | ||
569 | 165 | |||
570 | 166 | my_conn.cmd_query(TEST_SELECT_QUERY); | ||
571 | 167 | my_conn.show_results(); | ||
572 | 168 | */ | ||
573 | 169 | /* | ||
574 | 170 | // EXAMPLE 2 | ||
575 | 171 | |||
576 | 172 | // SELECT query returning rows (custom method) | ||
577 | 173 | // Here we execute the same query as above but use a custom method for reading | ||
578 | 174 | // and displaying the results. See the do_query() method above for more | ||
579 | 175 | // information about how it works. | ||
580 | 176 | |||
581 | 177 | do_query(TEST_SELECT_QUERY); | ||
582 | 178 | */ | ||
583 | 179 | /* | ||
584 | 180 | // EXAMPLE 3 | ||
585 | 181 | |||
586 | 182 | // SELECT query for lookup value (1 row returned) | ||
587 | 183 | // Here we get a value from the database and use it. | ||
588 | 184 | |||
589 | 185 | long head_count = 0; | ||
590 | 186 | my_conn.cmd_query(QUERY_POP); | ||
591 | 187 | |||
592 | 188 | // We ignore the columns but we have to read them to get that data out of the queue | ||
593 | 189 | |||
594 | 190 | my_conn.get_columns(); | ||
595 | 191 | |||
596 | 192 | // Now we read the rows. | ||
597 | 193 | |||
598 | 194 | row_values *row = NULL; | ||
599 | 195 | do { | ||
600 | 196 | row = my_conn.get_next_row(); | ||
601 | 197 | // We use the first value returned in the row - population of NYC! | ||
602 | 198 | if (row != NULL) { | ||
603 | 199 | head_count = atol(row->values[0]); | ||
604 | 200 | } | ||
605 | 201 | } while (row != NULL); | ||
606 | 202 | |||
607 | 203 | // We're done with the buffers so Ok to clear them (and save precious memory). | ||
608 | 204 | |||
609 | 205 | my_conn.free_columns_buffer(); | ||
610 | 206 | my_conn.free_row_buffer(); | ||
611 | 207 | |||
612 | 208 | // Now, let's do something with the data. | ||
613 | 209 | |||
614 | 210 | Serial.print("NYC pop = "); | ||
615 | 211 | Serial.println(head_count); | ||
616 | 212 | */ | ||
617 | 213 | |||
618 | 214 | // | ||
619 | 215 | // INSERT Examples | ||
620 | 216 | // | ||
621 | 217 | |||
622 | 218 | /* | ||
623 | 219 | // EXAMPLE 4 | ||
624 | 220 | |||
625 | 221 | // Inserting static text into a table. | ||
626 | 222 | // Here we simply insert text data into a table. No conversion needed. | ||
627 | 223 | // It also demonstrates the use of NULL to initiate a timestamp value. | ||
628 | 224 | |||
629 | 225 | my_conn.cmd_query(INSERT_TEXT); | ||
630 | 226 | // Now, let's check our results. | ||
631 | 227 | do_query(HELLO_SQL); | ||
632 | 228 | */ | ||
633 | 229 | /* | ||
634 | 230 | // EXAMPLE 5 | ||
635 | 231 | |||
636 | 232 | // Inserting real time data into a table. | ||
637 | 233 | // Here we want to insert a row into a table but in this case we are | ||
638 | 234 | // simulating reading the data from a sensor or some other component. | ||
639 | 235 | // In this case, we 'simulate' reading temperature in celsius. | ||
640 | 236 | |||
641 | 237 | float value_read = 26.9; | ||
642 | 238 | |||
643 | 239 | // To use the value in an INSERT statement, we must construct a string | ||
644 | 240 | // that has the value inserted in it. For example, what we want is: | ||
645 | 241 | // 'INSERT INTO test_arduino.temps VALUES (26.9, NULL)' but the 26.9 is | ||
646 | 242 | // held in the variable 'value_read'. So, we use a character string | ||
647 | 243 | // formatting operation sprintf(). Notice here we must convert the float | ||
648 | 244 | // to a string first and we use the %s specifier in the INSERT_DATA | ||
649 | 245 | // string. | ||
650 | 246 | |||
651 | 247 | char query[64]; | ||
652 | 248 | char temperature[10]; | ||
653 | 249 | dtostrf(value_read, 1, 1, temperature); | ||
654 | 250 | sprintf(query, INSERT_DATA, temperature); | ||
655 | 251 | my_conn.cmd_query(query); | ||
656 | 252 | // Now, let's check our results. | ||
657 | 253 | do_query(HELLO_DATA); | ||
658 | 254 | */ | ||
659 | 255 | } | ||
660 | 256 | |||
661 | 257 | void loop() { | ||
662 | 258 | } |