Random wanderings through Microsoft Azure esp. the IoT bits, AI on Micro controllers, .NET nanoFramework, .NET Core on *nix, and GHI Electronics TinyCLR
I then ran the device in the debugger to see how the AT+SLEEP was handled.
There was limited AT+SLEEP -Sleep mode documentation but it looks like the RAK3172 module sleeps, “wakes up” and then replies with “OK”.
Initially the Sleep method didn’t appear to work, the power consumption didn’t change….
private static void SendMessageTimerCallback(object state)
{
Rak3172LoRaWanDevice device = (Rak3172LoRaWanDevice)state;
#if PAYLOAD_HEX
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} port:{MessagePort} payload HEX:{PayloadHex}");
Result result = device.Send(MessagePort, PayloadHex, SendTimeout);
#endif
#if PAYLOAD_BYTES
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} port:{MessagePort} payload bytes:{Rak3172LoRaWanDevice.BytesToHex(PayloadBytes)}");
Result result = device.Send(MessagePort, PayloadBytes, SendTimeout);
#endif
if (result != Result.Success)
{
Debug.WriteLine($"Send failed {result}");
}
#if SLEEP
Thread.Sleep(7500); //10000 Works 5000 to short
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Sleep period:{SleepPeriod:hh:mm:ss}");
result = device.Sleep(SleepPeriod);
if (result != Result.Success)
{
Debug.WriteLine($"Sleep failed {result}");
return;
}
#endif
}
After some debugging and reading this helpful RAK Wireless forum post I added a short delay before sleeping the RAK3172 module and power consumption reduced.
Initially the Sleep method timed out every time it was called. After some more debugging I figured out that I needed a slightly longer delay for the AutoResetEvent.Waitone as it was timing out just before the “OK” was processed.
public Result Sleep(TimeSpan period)
{
return Sleep(period, SleepExtensionDefault);
}
public Result Sleep(TimeSpan period, TimeSpan extension)
{
#if DIAGNOSTICS
Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} AT+SLEEP {period.TotalMilliseconds:f0} mSec");
#endif
Result result = SendCommand("OK", $"AT+SLEEP={period.TotalMilliseconds:f0}", period.Add(extension));
if (result != Result.Success)
{
#if DIAGNOSTICS
Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} AT+SLEEP failed {result}");
#endif
return result;
}
return Result.Success;
}
public static void Main()
{
Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172 BreakoutSerial starting");
try
{
// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif
Debug.Write("Ports:");
foreach (string port in SerialPort.GetPortNames())
{
Debug.Write($" {port}");
}
Debug.WriteLine("");
using (_SerialPort = new SerialPort(SerialPortId))
{
// set parameters
_SerialPort.BaudRate = 115200;
_SerialPort.Parity = Parity.None;
_SerialPort.DataBits = 8;
_SerialPort.StopBits = StopBits.One;
_SerialPort.Handshake = Handshake.None;
_SerialPort.NewLine = "\r\n";
_SerialPort.ReadTimeout = 1000;
//_SerialPort.WatchChar = '\n'; // May 2022 WatchChar event didn't fire github issue https://github.com/nanoframework/Home/issues/1035
#if SERIAL_ASYNC_READ
_SerialPort.DataReceived += SerialDevice_DataReceived;
#endif
_SerialPort.Open();
_SerialPort.WatchChar = '\n';
_SerialPort.ReadExisting(); // Running at 115K2 this was necessary
...
for (int i = 0; i < 5; i++)
{
string atCommand;
atCommand = "AT+VER=?";
//atCommand = "AT+SN=?"; // Empty response?
//atCommand = "AT+HWMODEL=?";
//atCommand = "AT+HWID=?";
//atCommand = "AT+DEVEUI=?";
//atCommand = "AT+APPEUI=?";
//atCommand = "AT+APPKEY=?";
//atCommand = "ATR";
//atCommand = "AT+SLEEP=4000";
Debug.WriteLine("");
Debug.WriteLine($"{i} TX:{atCommand} bytes:{atCommand.Length}--------------------------------");
_SerialPort.WriteLine(atCommand);
Thread.Sleep(5000);
}
}
...
Debug.WriteLine("Done");
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
After resetting the device I modified the code to display some of the configuration.
To reconfigure the device I ran the RAK3172LoRaWANDeviceClient application with DEVICE_DEVEUI_SET, OTAA, UNCONFIRMED, REGION_SET and ADR_SET defined. The testrig could then successfully connect to The Things Network and when the device was power cycled the configuration was retained.
public Result FactoryReset()
{
#if DIAGNOSTICS
Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} ATR");
#endif
Result result = SendCommand("OK", "ATR", CommandTimeoutDefault);
if (result != Result.Success)
{
#if DIAGNOSTICS
Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} ATR failed {result}");
#endif
return result;
}
return Result.Success;
}
Read-only replicas of an Azure SQL Database database with Active geo-replication are easy to setup but there are some disadvantages. e.g. bi-directional synchronisation is not supported, not all tables or selected columns of some tables might not be needed\should not be accessible for reporting, the overhead of replicating tables used for transaction processing might impact on the performance of the solution etc. Azure SQL Data Sync is a service built on Azure SQL Database that can synchronise selected data bi-directionally across multiple databases, both on-premises and in the cloud.
For my “read-only replicas” scenario if there are any update conflicts the the source database “wins”.
The Azure SQL Database Data Sync was pretty easy to setup (configuration in the hub database tripped me up initially). For a production scenario where only a portion of the database (e.g. shaped by Customer, Geography, security considerations, or a bi-directional requirement) it would be an effective solution, though for some applications the delay between synchronisations might be an issue.
namespace devMobile.WebAPIDapper.Lists.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class StockItemsRetryADONetController : ControllerBase
{
private readonly string connectionString;
private readonly ILogger<StockItemsRetryADONetController> logger;
// This is a bit nasty but sufficient for PoC
private readonly int NumberOfRetries = 3;
private readonly TimeSpan TimeBeforeNextExecution = TimeSpan.Parse("00:00:01");
private readonly TimeSpan MaximumInterval = TimeSpan.Parse("00:00:30");
private readonly List<int> TransientErrors = new List<int>()
{
49920, // Cannot process rquest. Too many operations in progress for subscription
49919, // Cannot process create or update request.Too many create or update operations in progress for subscription
49918, // Cannot process request. Not enough resources to process request.
41839, // Transaction exceeded the maximum number of commit dependencies.
41325, // The current transaction failed to commit due to a serializable validation failure.
41305, // The current transaction failed to commit due to a repeatable read validation failure.
41302, // The current transaction attempted to update a record that has been updated since the transaction started.
41301, // Dependency failure: a dependency was taken on another transaction that later failed to commit.
40613, // Database XXXX on server YYYY is not currently available. Please retry the connection later.
40501, // The service is currently busy. Retry the request after 10 seconds
40197, // The service has encountered an error processing your request. Please try again
20041, // Transaction rolled back. Could not execute trigger. Retry your transaction.
17197, // Login failed due to timeout; the connection has been closed. This error may indicate heavy server load.
14355, // The MSSQLServerADHelper service is busy. Retry this operation later.
11001, // Connection attempt failed
10936, // The request limit for the elastic pool has been reached.
10929, // The server is currently too busy to support requests.
10928, // The limit for the database is has been reached
10922, // Operation failed. Rerun the statement.
10060, // A network-related or instance-specific error occurred while establishing a connection to SQL Server.
10054, // A transport-level error has occurred when sending the request to the server.
10053, // A transport-level error has occurred when receiving results from the server.
9515, // An XML schema has been altered or dropped, and the query plan is no longer valid. Please rerun the query batch.
8651, // Could not perform the operation because the requested memory grant was not available in resource pool
8645, // A timeout occurred while waiting for memory resources to execute the query in resource pool, Rerun the query
8628, // A timeout occurred while waiting to optimize the query. Rerun the query.
4221, // Login to read-secondary failed due to long wait on 'HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING'. The replica is not available for login because row versions are missing for transactions that were in-flight when the replica was recycled
4060, // Cannot open database requested by the login. The login failed.
3966, // Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. Retry the transaction.
3960, // Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table directly or indirectly in database
3935, // A FILESTREAM transaction context could not be initialized. This might be caused by a resource shortage. Retry the operation.
1807, // Could not obtain exclusive lock on database 'model'. Retry the operation later.
1221, // The Database Engine is attempting to release a group of locks that are not currently held by the transaction. Retry the transaction.
1205, // Deadlock
1204, // The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement.
1203, // A process attempted to unlock a resource it does not own. Retry the transaction.
997, // A connection was successfully established with the server, but then an error occurred during the login process.
921, // Database has not been recovered yet. Wait and try again.
669, // The row object is inconsistent. Please rerun the query.
617, // Descriptor for object in database not found in the hash table during attempt to un-hash it. Rerun the query. If a cursor is involved, close and reopen the cursor.
601, // Could not continue scan with NOLOCK due to data movement.
233, // The client was unable to establish a connection because of an error during connection initialization process before login.
121, // The semaphore timeout period has expired.
64, // A connection was successfully established with the server, but then an error occurred during the login process.
20, // The instance of SQL Server you attempted to connect to does not support encryption.
};
...
}
After some experimentation the most reliable way I could reproduce a transient failure (usually SQL Error 11001-“An error has occurred while establishing a connection to the server”) was by modifying the database connection string or unplugging the network cable after a connection had been explicitly opened or command executed.
namespace devMobile.WebAPIDapper.Lists.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class StockItemsRetryADONetController : ControllerBase
{
...
[HttpGet("Dapper")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetDapper()
{
IEnumerable<Model.StockItemListDtoV1> response = null;
SqlRetryLogicOption sqlRetryLogicOption = new SqlRetryLogicOption()
{
NumberOfTries = NumberOfRetries,
DeltaTime = TimeBeforeNextExecution,
MaxTimeInterval = MaximumInterval,
TransientErrors = TransientErrors,
//AuthorizedSqlCondition = x => string.IsNullOrEmpty(x) || Regex.IsMatch(x, @"^SELECT", RegexOptions.IgnoreCase),
};
SqlRetryLogicBaseProvider sqlRetryLogicProvider = SqlConfigurableRetryFactory.CreateFixedRetryProvider(sqlRetryLogicOption);
using (SqlConnection db = new SqlConnection(this.connectionString))
{
db.RetryLogicProvider = sqlRetryLogicProvider;
db.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnDapperRetrying);
await db.OpenAsync(); // Did explicitly so I could yank out the LAN cable.
response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
}
return this.Ok(response);
}
protected void OnDapperRetrying(object sender, SqlRetryingEventArgs args)
{
logger.LogInformation("Dapper retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
}
...
}
}
I then added an OpenAsync just before the Dapper query so I could open the database connection, pause the program with a breakpoint, unplug the LAN cable and then continue execution. The QueryAsync failed without any retries and modifying the AuthorizedSqlCondition didn’t seem change the way different SQL statement failures were handled.
There was limited documentation about how to use ADO.Net retry functionality so I hacked up another method to try and figure out what I had done wrong. The method uses the same SqlRetryLogicOption configuration for retrying connection and command failures.
namespace devMobile.WebAPIDapper.Lists.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class StockItemsRetryADONetController : ControllerBase
{
...
[HttpGet("AdoNet")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetAdoNet()
{
List<Model.StockItemListDtoV1> response = new List<Model.StockItemListDtoV1>();
// Both connection and command share same logic not really an issue for nasty demo
SqlRetryLogicOption sqlRetryLogicOption = new SqlRetryLogicOption()
{
NumberOfTries = NumberOfRetries,
DeltaTime = TimeBeforeNextExecution,
MaxTimeInterval = MaximumInterval,
TransientErrors = TransientErrors,
//AuthorizedSqlCondition = x => string.IsNullOrEmpty(x) || Regex.IsMatch(x, @"^SELECT", RegexOptions.IgnoreCase),
};
SqlRetryLogicBaseProvider sqlRetryLogicProvider = SqlConfigurableRetryFactory.CreateFixedRetryProvider(sqlRetryLogicOption);
// This ADO.Net is a bit overkill but just wanted to highlight ADO.Net vs. Dapper
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
sqlConnection.RetryLogicProvider = sqlRetryLogicProvider;
sqlConnection.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnConnectionRetrying);
await sqlConnection.OpenAsync(); // Did explicitly so I could yank out the LAN cable.
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]";
sqlCommand.CommandType = CommandType.Text;
sqlCommand.RetryLogicProvider = sqlRetryLogicProvider;
sqlCommand.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnCommandRetrying);
// Over kill but makes really obvious
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection))
{
while (await sqlDataReader.ReadAsync())
{
response.Add(new Model.StockItemListDtoV1()
{
Id = sqlDataReader.GetInt32("Id"),
Name = sqlDataReader.GetString("Name"),
RecommendedRetailPrice = sqlDataReader.GetDecimal("RecommendedRetailPrice"),
TaxRate = sqlDataReader.GetDecimal("TaxRate"),
});
}
}
};
}
return this.Ok(response);
}
protected void OnConnectionRetrying(object sender, SqlRetryingEventArgs args)
{
logger.LogInformation("Connection retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
}
protected void OnCommandRetrying(object sender, SqlRetryingEventArgs args)
{
logger.LogInformation("Command retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
}
}
}
I modified the NetworkJoinOTAA sample(based on the asynchronous version of BreakOutSerial) to send the required sequence of AT commands and displays the responses.
namespace devMobile.IoT.LoRaWAN.nanoFramework.RAK3172
{
using System;
using System.Diagnostics;
using System.IO.Ports;
using System.Threading;
#if ESP32_WROOM
using global::nanoFramework.Hardware.Esp32; //need NuGet nanoFramework.Hardware.Esp32
#endif
public class Program
{
#if ESP32_WROOM
private const string SerialPortId = "COM2";
#endif
#if ST_STM32F769I_DISCOVERY
private const string SerialPortId = "COM6";
#endif
private const string DevEui = "...";
private const string DevAddress = "...";
private const string NwksKey = "...";
private const string AppsKey = "...";
private const byte MessagePort = 1;
private const string Payload = "A0EEE456D02AFF4AB8BAFD58101D2A2A"; // Hello LoRaWAN
public static void Main()
{
Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172.NetworkJoinABP starting");
try
{
// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif
Debug.Write("Ports:");
foreach (string port in SerialPort.GetPortNames())
{
Debug.Write($" {port}");
}
Debug.WriteLine("");
using (SerialPort serialPort = new SerialPort(SerialPortId))
{
// set parameters
serialPort.BaudRate = 115200;
serialPort.Parity = Parity.None;
serialPort.DataBits = 8;
serialPort.StopBits = StopBits.One;
serialPort.Handshake = Handshake.None;
serialPort.NewLine = "\r\n";
serialPort.ReadTimeout = 1000;
serialPort.DataReceived += SerialDevice_DataReceived;
serialPort.Open();
serialPort.WatchChar = '\n';
serialPort.ReadExisting(); // Running at 115K2 this was necessary
// Set the Device EUI
Console.WriteLine("Set Device EUI");
serialPort.WriteLine($"AT+DEVEUI={DevEui}");
// Set the Working mode to LoRaWAN
Console.WriteLine("Set Work mode");
serialPort.WriteLine("AT+NWM=1");
// Set the Region to AS923
Console.WriteLine("Set Region");
serialPort.WriteLine("AT+BAND=8");
// Set the JoinMode
Console.WriteLine("Set Join mode");
serialPort.WriteLine("AT+NJM=0");
// Set the DevAddress
Console.WriteLine("Set Device Address");
serialPort.WriteLine($"AT+DEVADDR={DevAddress}");
// Set the Network Session Key
Console.WriteLine("Set NwksKey");
serialPort.WriteLine($"AT+NWKSKEY={NwksKey}");
// Set the Application Session Key
Console.WriteLine("Set AppsKey");
serialPort.WriteLine($"AT+APPSKEY={AppsKey}");
// Set the Confirm flag
Console.WriteLine("Set Confirm off");
serialPort.WriteLine("AT+CFM=0");
// Join the network
Console.WriteLine("Start Join");
serialPort.WriteLine("AT+JOIN=1:0:10:2");
// Wait for the +EVT:JOINED
while (true)
{
Console.WriteLine("Sending");
serialPort.WriteLine($"AT+SEND={MessagePort}:{Payload}");
Thread.Sleep(300000);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
private static void SerialDevice_DataReceived(object sender, SerialDataReceivedEventArgs e)
{
SerialPort serialPort = (SerialPort)sender;
switch (e.EventType)
{
case SerialData.Chars:
break;
case SerialData.WatchChar:
string response = serialPort.ReadExisting();
Debug.Write(response);
break;
default:
Debug.Assert(false, $"e.EventType {e.EventType} unknown");
break;
}
}
}
}
The NetworkJoinABP application assumes that all of the AT commands succeed.
#define ST_STM32F769I_DISCOVERY // nanoff --target ST_STM32F769I_DISCOVERY --update
//#define ESP32_WROOM //nanoff --target ESP32_REV0 --serialport COM17 --update
namespace devMobile.IoT.LoRaWAN.nanoFramework.RAK3172
{
using System;
using System.Diagnostics;
using System.IO.Ports;
using System.Threading;
#if ESP32_WROOM
using global::nanoFramework.Hardware.Esp32; //need NuGet nanoFramework.Hardware.Esp32
#endif
public class Program
{
#if ESP32_WROOM
private const string SerialPortId = "COM2";
#endif
#if ST_STM32F769I_DISCOVERY
private const string SerialPortId = "COM6";
#endif
private const string DevEui = "...";
private const string AppEui = "...";
private const string AppKey = "...";
private const byte MessagePort = 1;
private const string Payload = "A0EEE456D02AFF4AB8BAFD58101D2A2A"; // Hello LoRaWAN
public static void Main()
{
Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172 NetworkJoinOTAA starting");
try
{
// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif
Debug.Write("Ports:");
foreach (string port in SerialPort.GetPortNames())
{
Debug.Write($" {port}");
}
Debug.WriteLine("");
using (SerialPort serialPort = new SerialPort(SerialPortId))
{
// set parameters
serialPort.BaudRate = 115200;
serialPort.Parity = Parity.None;
serialPort.DataBits = 8;
serialPort.StopBits = StopBits.One;
serialPort.Handshake = Handshake.None;
serialPort.NewLine = "\r\n";
serialPort.ReadTimeout = 1000;
serialPort.DataReceived += SerialDevice_DataReceived;
serialPort.Open();
serialPort.WatchChar = '\n';
serialPort.ReadExisting(); // Running at 115K2 this was necessary
// Set the devEUI
Console.WriteLine("Set Dev Eui");
serialPort.WriteLine($"AT+DEVEUI={DevEui}");
// Set the Working mode to LoRaWAN
Console.WriteLine("Set Work mode");
serialPort.WriteLine("AT+NWM=1");
// Set the Region to AS923
Console.WriteLine("Set Region");
serialPort.WriteLine("AT+BAND=8");
// Set the JoinMode
Console.WriteLine("Set Join mode");
serialPort.WriteLine("AT+NJM=1");
// Set the appEUI
Console.WriteLine("Set App Eui");
serialPort.WriteLine($"AT+APPEUI={AppEui}");
// Set the appKey
Console.WriteLine("Set App Key");
serialPort.WriteLine($"AT+APPKEY={AppKey}");
// Set the Confirm flag
Console.WriteLine("Set Confirm off");
serialPort.WriteLine("AT+CFM=0");
// Join the network
Console.WriteLine("Start Join");
serialPort.WriteLine("AT+JOIN=1:0:10:2");
// Wait for the +EVT:JOINED
while (true)
{
Console.WriteLine("Sending");
serialPort.WriteLine($"AT+SEND={MessagePort}:{Payload}");
Thread.Sleep(300000);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
private static void SerialDevice_DataReceived(object sender, SerialDataReceivedEventArgs e)
{
SerialPort serialPort = (SerialPort)sender;
switch (e.EventType)
{
case SerialData.Chars:
break;
case SerialData.WatchChar:
string response = serialPort.ReadExisting();
Debug.Write(response);
break;
default:
Debug.Assert(false, $"e.EventType {e.EventType} unknown");
break;
}
}
}
}
The NetworkJoinOTAA application assumes that all of the AT commands succeed. I usually step though the application in the debugger so I can monitor progress.