MXChip and Azure SQL
I had the pleasure of receiving a couple of MXChip devices last week, and of course couldn't wait to start playing with this new technology. This blog post walks you through some of the key steps I went through to make the MXChip board save records to an Azure SQL Database using Enzo Online (running as an HTTPS proxy).
Enzo Online is currently in preview: https://portal.enzounified.com/
About the Chip
The MXChip AZ3166 is an Arduino board that was designed specifically to communicate easily and directly with various Azure IoT services out of the box. In fact you can see that the board contains LEDs that specifically tell you if an Azure connection has been established. The are many fun projects you can roll out with Microsoft Azure without any advanced programming; just by following the configuration steps provided. To obtain a thorough overview and get started, visit https://microsoft.github.io/azure-iot-developer-kit/.
The installation went smoothly and I was able to get the chip up and running, connecting to the cloud, within about an hour. No bad... However, learning the VSCode environment with Arduino extensions, and getting my own sample code running was a different story. Here is what I did.
Using the SensorStatus Project
The SensorStatus project (installed on your computer through the standard installation process) runs by default on MXChip; it is a comprehensive project with support for configuring your WIFI connection and reading from various sensors on the board. So I copied/pasted the entire project and added my own methods.
About the Project
The objective of this small project is to call the Enzo Online service using HTTPS, and save Temperature data directly into an Azure SQL database. The idea is to redirect the calls to another, non-Azure service, so I can extend the MXChip with additional capabilities, such as sending SMS messages, sending emails and so forth. In summary, the project calls the Enzo Online Service from the MXChip, which in turns saves data in an Azure SQL database.
The overall objective of this architecture is to offload complex processing, including retry logic, to an external service so that the logic doesn't have to be coded in C++. Enzo Online provides easy access to the Azure Service Bus and the Azure Key Vault as well, through simplified HTTPS calls, so it would be trivial to implement cross-MXChip messaging communication and accessing secrets in Azure.
VSCode Project Layout
VSCode works by opening a "folder", which is the root folder of your project. Under this root folder, you need to have a specific sub-folder structure where specific files are needed. To simplify my environment, I created the following structure:
The config.json simply points to the EnzoMXChip.ino program file (the source code), which initially was the SensorStatus.ino file simply renamed. The c_cpp_properties.json file is critical to the proper compilation of your project since it contains external library references.
About Missing Libraries
Since we are dealing with C++ code, you must include a large number of referenced libraries that are needed to compile the code. Each necessary library must be referenced individually (there is no sub-directory discovery capability). However, VSCode comes with a nifty feature, allowing to discover the location of a missing library and add it automatically to the project references, one at a time. For this to work, you must add a path to the intellisense section with the root folder of your libraries. In my case, all my libraries are located under the MXChipDemo\include folder, so my browse section looks like this:
"browse": {
"path": [
"C:/Program Files (x86)/Microsoft Visual Studio 14.0/VC/include/*",
"C:/Program Files (x86)/Windows Kits/10/Include/10.0.10586.0/um",
"C:/Program Files (x86)/Windows Kits/10/Include/10.0.10586.0/ucrt",
"C:/Program Files (x86)/Windows Kits/10/Include/10.0.10586.0/shared",
"C:/Program Files (x86)/Windows Kits/10/Include/10.0.10586.0/winrt",
"C:/Dev/BSC/dev2-1.7.0/MXChipDemo/include",
"${workspaceRoot}"
],
Once the path the include directory has been added, Intellisense will work, allowing you to quickly add missing references.
About SSL
What is a bit strange with the libraries provided is that you cannot establish an SSL communication without also providing the public certificate chain of the service you are calling; while this might make sense from a security standpoint, it is certainly an overkill for development scenarios that require SSL.
Since I am modifying the sample project to add a REST call to an Enzo Online service running in Azure, I needed to add the certificate chain (with public keys only) to the source code. The certificate chain needs to be exported as a Base-64 encoded X.509 (.CER). Keep in mind that in the VSCode environment, a string cannot be too long on a single line; you must break the string into multiple lines if you have a certificate chain, otherwise you will experience a compilation error.
Additional C++ Code
As mentioned previously, I used the SensorStatus project as my base code. So I only needed to add a few lines of code in the renamed EnzoMXChip.ino file to call my Enzo Online service.
The code below represents two additional methods to the base source code:
getCurrentTempF() Returns the current temperature in Fahrenheits
saveTempToSQLDB() Calls the HTTPS Enzo Online service's DB proxy that executes the SQL statement provided as-is
Note that I removed the Authentication token of Enzo, and removed the really long string that represents my certificate chain.
const char enzoAuthId[] = "ENZO_SECRET_KEY";
const char enzoDBURI[] = "https://enzoclientdemo.enzounified.com:49551/bsc/db/executesql";
const char enzoCERT[] =
"-----BEGIN CERTIFICATE-----\r\n"
"this is a really long string of my certificate chain removed for convenience\r\n"
"-----END CERTIFICATE-----\r\n";
float getCurrentTempF()
{
ht_sensor->reset();
float temperature = 0;
ht_sensor->getTemperature(&temperature);
temperature = temperature*1.8 + 32; //convert from C to F
return temperature;
}
bool saveTempToSQLDB()
{
// CREATE TABLE temperature (ID INT IDENTITY(1,1), DeviceName NVARCHAR(255), MessageType NVARCHAR(50), Value float, recordedOn datetime)
char sql[256];
float temperature = getCurrentTempF();
snprintf(sql, 256, "INSERT INTO temperature (DeviceName, MessageType, recordedOn, value) VALUES ('%s', 'TEMP', GETUTCDATE(), %s)"
,"MXChip"
,f2s(temperature, 1)
);
HTTPClient client = HTTPClient(enzoCERT, HTTP_POST, enzoDBURI);
client.set_header("authToken", enzoAuthId);
client.set_header("definitionName", "azuredb");
client.set_header("sql", sql);
const Http_Response *response = client.send(NULL, 0);
char buff[128];
if (response != NULL)
{
snprintf(buff, 128, "Enzo Call:\r\nTemp: %s \r\nSUCCESS",f2s(temperature, 1));
}
else
{
snprintf(buff, 128, "Enzo Call:\r\nTemp: %s \r\nFAILED",f2s(temperature, 1));
}
Screen.print(buff);
}
Last but not least, the main loop() method calls the saveTempToSQLDB procedure regularly (about every 2 seconds) to store data in the Azure SQL DB database that Enzo Online is configured to point to; calling Enzo only takes place when displaying the current temperature on the MXChip.
if(showSensor)
{
switch(status)
{
case 0:
saveTempToSQLDB();
delay(2000);
showHumidTempSensor();
break;
case 1:
showPressureSensor();
break;
case 2:
showMagneticSensor();
break;
case 3:
showMotionGyroSensor();
break;
}
}
Conclusion
Using the MXChip and the VSCode environment wan't too hard to learn and configure, although the learning curve can be a bit steep for those unfamiliar with C++ programming. Within a few hours however, I was able to configure the MXChip to call an HTTPS service and save database records easily through the Enzo Online service.