-
Notifications
You must be signed in to change notification settings - Fork 133
API Documentation
This section is for those who want to know a bit more about how the connector works and what functions are available. If you plan to modify the connector for your own use or to improve some part under the license, this section should give you a place to get started. This section examines the library and its supporting methods in more detail.
The MySQL Connector Arduino folder contains a number of files and a directory. The following list describes each of the files:
-
examples: A directory containing example code for using the library. -
extras: A directory containing additional usage files. -
src: A directory containing the source code for the library. -
keywords.txt: The list of keywords reserved for the library. -
library.properties: A file containing the Arduino properties for the library. -
README.md: Introductory documentation.
The source code is spread across several source files each with its own purpose. The following lists the source code module (header .h and source .cpp file) and its purpose.
-
MySQL_Connection: Handles initial handshake and general client/server connection. -
MySQL_Cursor: Handles execution of queries and their result sets. -
MySQL_Encrypt_Sha1: Implements the SHA1 encryption for the connection handshake. -
MySQL_Packet: Handles low-level packet format, transmit, and receive for the client/server connection. If you need to change the connector, you can concentrate your changes in the appropriate module. For example, if you need to adjust the way the connector makes the connection to the server, look inMySQL_Connection.h/.cpp.
The library uses a number of structures when communicating with the server. There is one structure that you use frequently when returning result sets. It is called field_struct and is shown below. You can find this in MySQL_Cursor.h. // Structure for retrieving a field (minimal implementation). typedef struct { char *db; char *table; char *name; } field_struct;
The field structure is used to retrieve the metadata for a field. Notice that you get the database, table, and field name. This permits you to determine which table a field is derived from in the case of queries involving joins. The method used to populate this field structure, get_field(), creates the strings in memory. It is your responsibility to free this memory—the strings—when you are finished reading or operating on the data.
There are also two structures for working with result sets: column_names and row_values. Use column_names for getting column information and row_values for getting row values in a result set. You can also find these in MySQL_Cursor.h.
// Structure for storing result set metadata.
typedef struct {
int num_fields; // actual number of fields
field_struct *fields[MAX_FIELDS];
} column_names;
// Structure for storing row data.
typedef struct {
char *values[MAX_FIELDS];
} row_values;
Now that you understand the structures involved with working with the library methods, lets examine the methods available to you for communicating with a MySQL server.
Libraries—or, more specifically, classes—typically have one or more public methods that can be used by any caller (program) via an instantiation of the class. Classes also have some parts that are private, which are typically helper methods to do something internal for the class. The methods can abstract portions of the class or simply hide data and operations that do not need to be accessed by the caller (think abstract data types). The public methods and attributes are therefore the things the designer permits the caller to access.
The Connector/Arduino library has a number of public methods that define the library’s capabilities. There are methods for connecting, executing queries, and returning results (rows) from the database. Each of which is declared in the appropriate code module.
We will look at the public methods for the three most commonly used modules. The SHA1 module has no methods applicable to MySQL-enabled sketches.
The following shows the method declarations for the public methods for the MySQL_Connection class in the MySQL_Connection.h file.
boolean connect(IPAddress server, int port, char *user, char *password,
char *db=NULL);
int connected() { return client->connected(); }
const char *version() { return MYSQL_VERSION_STR; }
void close();
The connect() method, as you have seen, is the method you must call to connect to a MySQL database server. This method must be called after the initialization of the Ethernet class and before any other method from the library. It requires the IP address of the server, the port for the server, and the username and password to use to connect. You can also specify a default database so that you do not have to specify the database in your SQL commands.
It returns a Boolean, where true indicates success and false means there was some error in connecting to the server. If you encounter problems connecting to the server, you should attempt to connect from another machine on your network using the mysql client and the IP, port, user, and password defined in your sketch, to ensure connectivity and that there are no user or password issues.
The connected() method returns true if the Arduino is connected to the server or false if not. You can use this method to test connectivity if or when there are long periods of inactivity or errors.
The version() method returns the server version to which you connected. It is only valid once the connection is successful.
The close() method disconnects from the server and closes the connection. Always call this method if you connect and disconnect periodically.
The following shows the method declarations for the public methods for the MySQL_Cursor class in the MySQL_Cursor.h file. boolean execute(const char *query, boolean progmem=false); void show_results(); void close(); column_names *get_columns(); row_values *get_next_row(); int get_rows_affected() { return rows_affected; } int get_last_insert_id() { return last_insert_id; }
The execute() method is the method you can use to execute a query (SQL statement). The method takes a constant string reference that contains the query you wish to execute. You can also pass the string passed if it is defined using program space. In this case, you set the progmem parameter to true. For more information about using program space (called PROGMEM), see the Arduino online reference (www.arduino.cc/en/Reference/PROGMEM). Basically, if you need more space for data but can afford to use program space for data, you should use this method to execute strings from program space.
The show_results() method is both an example of how to retrieve data from the database for SELECT queries and a method you can use as is to execute after issuing the execute() call. The method reads one row at a time and sends it to the serial monitor. It can be handy for testing queries and for experimenting with new sketches.
On the other hand, if you want to read rows from a database and process the data, you can write your own method to do this. You must first execute the query with execute(); then, if there is a result set, read the column headers (the server always sends the column headers first) using get_columns() and read the rows with the iterator get_next_row().
If you want to retrieve the number of rows affected for SQL commands that return such, you can use the get_rows_affected() method after executing the query to get that value. Similarly, you can get the last inserted auto increment value with get_last_insert_id(), but that is only valid when using auto increment.
This module isn’t used that much for most sketches, but there is one method that bears mentioning. The print_packet() method can be used in the cursor or connector classes to write the packet data to the serial monitor. If you are experimenting with modifying the connector for use with a different board or client/server protocol, you can place this method in key locations to display the data in the packet. Be sure to turn on debug mode before using the method.
Besides connecting to a database server, the two uses of the library are issuing queries that do not return results (like INSERT) and returning rows from queries that return result sets (like SELECT or SHOW VARIABLES). The following sections demonstrate each of these options.
You have seen how to issue queries without result sets in the “Hello, MySQL!” example. Recall that this is simply a call to execute() with the query passed as a string. The following shows an example of a query that returns no results:
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
int res = cur_mem->execute(INSERT_SQL);
if (!res) {
Serial.println("Query failed.");
} else {
Serial.println("Ok.");
}
delete cur_mem;
Returning results (rows) from the server is a bit more complicated but not overly so. To read a result set from the server, you must first read the result set header and the field packets, and then the data rows. Specifically, you must anticipate, read, and parse the following packets:
- Result-set header packet: Number of columns
- Field packets: Column descriptors
- EOF packet: Marker: end-of-field packets
- Row data packets: Row contents
- EOF packet: marker: End-of-data packets
This means the MySQL server first sends the number of fields and a list of the fields (columns) that you must read, and then the row data appears in one or more packets until there are no more rows. The algorithm for reading a result set is as follows:
- Read result set header for number of columns.
- Read fields until EOF.
- Read rows until EOF.
Let’s take a look at the contents of the show_results() method. void MySQL_Cursor::show_results() { column_names *cols; int rows = 0;
// Get the columns
cols = get_columns();
if (cols == NULL) {
return;
}
for (int f = 0; f < columns.num_fields; f++) {
Serial.print(columns.fields[f]->name);
if (f < columns.num_fields-1)
Serial.print(',');
}
Serial.println();
// Read the rows
while (get_next_row()) {
rows++;
for (int f = 0; f < columns.num_fields; f++) {
Serial.print(row.values[f]);
if (f < columns.num_fields-1)
Serial.print(',');
}
free_row_buffer();
Serial.println();
}
// Report how many rows were read
Serial.print(rows);
conn->show_error(ROWS, true);
free_columns_buffer();
// Free any post-query messages in queue for stored procedures
clear_ok_packet();
}
So what’s going on here? Notice how the code is structured to execute the query; if there are results (execute() does not return NULL), you read the column headers. The return from the get_columns() method is a structure that contains an array of field structures. The structure is shown next:
// Structure for retrieving a field (minimal implementation).
Typedef struct {
char *db;
char *table;
char *name;
} field_struct;
// Structure for storing result set metadata.
Typedef struct {
int num_fields; // actual number of fields
field_struct *fields[MAX_FIELDS];
} column_names;
Notice that the column_names structure has a fields array. Use that array to get information about each field in the form of the field_struct (shown earlier). In that structure, you can get the database name, table name, and column name. In the code you simply print out the column names and a comma after each.
Next, you read the rows using a special iterator named get_next_row(), which returns a pointer to a row structure that contains an array of the field values:
// Structure for storing row data.
typedef struct {
char *values[MAX_FIELDS];
} row_values;
In this case, while get_next_row() returns a valid pointer (not NULL), you read each field and print out the values.
You may be wondering what MAX_FIELDS is. Well, it is an easy way to make sure you limit your array of columns (fields). This is defined in MySQL_Cursor.h and is set to 32 (0x20). If you want to save a few bytes, you can change that value to something lower, but beware: if you exceed that value, your code will wander off into wonkyville (unreferenced pointer). So tread lightly.
Notice also the calls to free_row_buffer() and free_columns_buffer(). These are memory cleanup methods needed to free any memory allocated when reading columns and row values (hey you have to put it somewhere!). You call the free_row_buffer() after you are finished processing the row and the free_columns_buffer() at the end of the method. If you fail to add these to your own query handler method, you will run out of memory quickly.
Why is it manual? Well, like the MAX_FIELDS setting, it was added to keep it simple and therefore save as much space as possible. Automatic garbage collection would have added a significant amount of code.
You can use this method as a template to build your own custom query handler. For example, instead of printing the data to the serial monitor, you could display it in an LCD or perhaps use the information in another part of your sketch.
The library contains a delay in the wait_for_client() method (in MySQL_Packet.cpp) that can be adjusted to improve the speed of query results returned. It is currently set at a modest delay. Depending on your network latency and proximity to the database server (as in, no network hops), you can reduce this value considerably. It was originally added to help prevent issues with slower wireless networks.