MySQL_MariaDB_Generic Library supports Teensy, nRF52, SAMD, etc. & W5x00/ENC/LAN8742A

khoih-prog

Well-known member
MySQL_MariaDB_Generic Library supports Teensy, nRF52, SAMD, etc. & W5x00/ENC/LAN8742A

MySQL_MariaDB_Generic

How To Install Using Arduino Library Manager


Why do we need this MySQL_MariaDB_Generic

Have you ever wanted to connect your Arduino project to a database to either store the data you've collected or retrieve data saved to trigger events in your sketch?

Well, now you can connect your Arduino project directly to a MySQL / MariaDB server without using an intermediate computer or a web-based or cloud-based service. Having direct access to a MySQL / MariaDB database server means you can store data acquired from your project as well as check values stored in tables on the server.

This also means you can setup your own, local MySQL server to store your data further removing the need for Internet connectivity. If that is not an issue, you can still connect to and store data on a MySQL server via your network, Internet, or even in the cloud!

This MySQL_MariaDB_Generic is based on and modified from Dr. Charles Bell's MySQL_Connector_Arduino Library to provide support to many more boards and shields, such as Arduino SAMD21, Adafruit SAMD21/SAMD51, Seeeduino SAMD21/SAMD51, nRF52, STM32F/L/H/G/WB/MP1, Teensy, SAM DUE, AVR Mega, etc. Those supported boards can be used with ESP8266/ESP32’s WiFi, WiFiNINA, W5x00/ENC28J60/LAN8742A Ethernet, ESP8266/ESP32-AT modules/shields.


New in v1.0.2

1. Fix crashing bug when Client timeout.
2. Make code more error-proof.
3. Drop support to ESP8266_AT_Webserver.
4. Enhance examples

New in v1.0.1

1. Add support to ENC28J60 Ethernet module/shield using UIPEthernet library.
2. Fix bugs
3. Optimize code
4. Enhance examples
5. Add UIPEthernet Library Patches for SAMD21/SAMD51

New in v1.0.0


1. Add support to nRF52 boards, such as AdaFruit Feather nRF52832, nRF52840 Express, BlueFruit Sense, Itsy-Bitsy nRF52840 Express, Metro nRF52840 Express, NINA_B302_ublox, NINA_B112_ublox, etc.
2. Add support to Adafruit SAMD51 (Itsy-Bitsy M4, Metro M4, Grand Central M4, Feather M4 Express, etc.)
3. Add support to Adafruit SAMD21 Itsy-Bitsy M0, Feathr M0, Metro M0, AdaFruit CIRCUITPLAYGROUND_EXPRESS, etc.)
4. Add support to Arduino SAMD21 (ZERO, MKR, NANO_33_IOT, etc.)
5. Add support to all STM32F/L/H/G/WB/MP1 (Nucleo-144 NUCLEO_F767ZI, Nucleo-64 NUCLEO_L053R8, etc.)
6. Add support to Seeeduino SAMD21/SAMD51 boards (SEEED_WIO_TERMINAL, SEEED_FEMTO_M0, SEEED_XIAO_M0, Wio_Lite_MG126, WIO_GPS_BOARD, SEEEDUINO_ZERO, SEEEDUINO_LORAWAN, SEEED_GROVE_UI_WIRELESS, etc.)
7. Add support to SAM DUE and AVR Mega boards.
8. Add support to ESP32/ESP8266 boards.
9. Add connectNonBlocking() function to use in loop() to avoid being blocked running other tasks.
10. Add support to WiFiNINA using WiFiNINA_Generic library or WiFiNINA library.
11. Add support to W5x00 Ethernet module/shield using Ethernet, EthernetLarge, Ethernet2 or Ethernet3 library.
12. Add support to LAN8742A Ethernet module/shield using STM32Ethernet library.
13. Add support to ESP8266/ESP32-AT-command module/shield using WiFiEspAT library or ESP8266_AT_WebServer library.
14. Add Packages' Patches.
15. Add sample Packages_Patches for STM32 stm32 (Nucleo-144 NUCLEO_F767ZI, Nucleo-64 NUCLEO_L053R8)
16. Add Ethernet Library Patches
17. Add many examples

1. This is terminal debug output when running Query_Progmem on Adafruit NRF52840_FEATHER board with Ethernet W5500 shield using Ethernet3 library, connecting to MariaDB Server.

Code:
Starting Query_Progmem on NRF52840_FEATHER using W5x00/Ethernet3 Library

Ethernet3 W5500 init, using SPI_CS = 10, number of sockets = 4
Using mac index = 10
Connected! IP address: 192.168.2.145
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1

Running SELECT from PROGMEM and printing results

SELECT * FROM world.city LIMIT 12
ID,Name,CountryCode,District,Population
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200
6,Rotterdam,NLD,Zuid-Holland,593321
7,Haag,NLD,Zuid-Holland,440900
8,Utrecht,NLD,Utrecht,234323
9,Eindhoven,NLD,Noord-Brabant,201843
10,Tilburg,NLD,Noord-Brabant,193238
11,Groningen,NLD,Groningen,172701
12,Breda,NLD,Noord-Brabant,160398
12 rows in result.


2. This is terminal debug output when running Complex_Select on STM32F7 Nucleo-144 NUCLEO_F767ZI using LAN8742A built-in Ethernet and STM32Ethernet library, connecting to MariaDB Server.

Code:
Starting Complex_Select on NUCLEO_F767ZI using LAN8742A/STM32Ethernet Library
Using mac index = 0
Connected! IP address: 192.168.2.165
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest, DB = world
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 857880 ORDER BY population DESC LIMIT 12;
name,population
Naucalpan de Ju⸮rez,857511
Pikine,855287
Lubumbashi,851381
Monrovia,850000
Freetown,850000
Zaporizzja,848000
Handan,840000
S⸮o Lu⸮s,837588
Wuxi,830000
Ouagadougou,824000
Ciudad de Guatemala,823301
Coimbatore,816321
[SQL] Disconnected

Sleeping...
================================================
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 972170 ORDER BY population DESC LIMIT 12;
name,population
Mekka,965700
K⸮ln,962507
Managua,959000
Detroit,951270
Shenzhen,950500
Haora (Howrah),950435
Campinas,950043
Brazzaville,950000
Khartum,947483
Karaj,940968
Taichung,940589
Santa Cruz de la Sierra,935361
[SQL] Disconnected

Sleeping...
================================================

3. This is terminal debug output when running Query_Results_WiFi on Adafruit SAMD51 ITSYBITSY_M4 using ESP8266-AT WiFi shield and WiFiEspAT library, connecting to MariaDB Server.

Code:
Starting Query_Results_WiFi on ITSYBITSY_M4
Using WiFiEspAT Library
WiFi shield init done
Connecting to HueNet1
Connected to network. My IP address is: 192.168.2.32
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1

Running SELECT and printing results

SELECT * FROM world.city LIMIT 6
ID, Name, CountryCode, District, Population
1, Kabul, AFG, Kabol, 1780000
2, Qandahar, AFG, Qandahar, 237500
3, Herat, AFG, Herat, 186800
4, Mazar-e-Sharif, AFG, Balkh, 127800
5, Amsterdam, NLD, Noord-Holland, 731200
6, Rotterdam, NLD, Zuid-Holland, 593321
[SQL] Disconnected

Sleeping...
================================================


4. This is terminal debug output when running Basic_Select_WiFi on Seeeduino SAMD21 SEEED_XIAO_M0 using ESP8266-AT WiFi shield and WiFiEspAT library, connecting to MariaDB Server.

Code:
Starting Basic_Select_WiFi on SEEED_XIAO_M0
Using WiFiEspAT Library
WiFi shield init done
Connecting to HueNet1
Connected to network. My IP address is: 192.168.2.32
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest, DB = world
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
1) Demonstrating using a dynamically allocated query.
SELECT population FROM world.city WHERE name = 'Toronto'
  Toronto pop = 688275
2) Demonstrating using a local, global query.
SELECT population FROM world.city WHERE name = 'Toronto'
  Toronto pop = 688275
  Toronto pop increased by 11725 = 700000
[SQL] Disconnected

Sleeping...
================================================

5. This is terminal debug output when running Query_Results_WiFiNINA on Arduino SAMD21 SAMD_NANO_33_IOT using built-in WiFiNINA and WiFiNINA_Generic library, connecting to MariaDB Server.

Code:
Starting Query_Results_WiFiNINA on SAMD_NANO_33_IOT
Attempting to connect to SSID: HueNet1
SSID: HueNet1
IP Address: 192.168.2.118
signal strength (RSSI):-40 dBm
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1

Running SELECT and printing results

SELECT * FROM world.city LIMIT 12
ID, Name, CountryCode, District, Population
1, Kabul, AFG, Kabol, 1780000
2, Qandahar, AFG, Qandahar, 237500
3, Herat, AFG, Herat, 186800
4, Mazar-e-Sharif, AFG, Balkh, 127800
5, Amsterdam, NLD, Noord-Holland, 731200
6, Rotterdam, NLD, Zuid-Holland, 593321
7, Haag, NLD, Zuid-Holland, 440900
8, Utrecht, NLD, Utrecht, 234323
9, Eindhoven, NLD, Noord-Brabant, 201843
10, Tilburg, NLD, Noord-Brabant, 193238
11, Groningen, NLD, Groningen, 172701
12, Breda, NLD, Noord-Brabant, 160398
[SQL] Disconnected

Sleeping...
================================================

6. This is terminal debug output when running Connect_By_Hostname on Arduino SAM DUE using W5100 Ethernet shield and EthernetLarge library, connecting to MariaDB Server.


Code:
Starting Connect_By_Hostname on SAM DUE using W5x00/EthernetLarge Library
_pinCS = 0
W5100 init, using SS_PIN_DEFAULT = 10, new ss_pin = 10, W5100Class::ss_pin = 10
W5100::init: W5100, SSIZE =4096
Using mac index = 2
Connected! IP address: 192.168.2.177
DNS Lookup: Hostname : account.ddns.net => IP = 216.154.***.***
Connecting to SQL Server @ 216.154.***.***, Port = 5698
User = invited-guest, PW = the-invited-guest
[SQL] Connecting to Server: 216.154.***.*** , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
[SQL] Disconnected

---

7. This is terminal debug output when running Complex_Select on Adafruit NRF52840_FEATHER using ENC28J60 Ethernet shield and UIPEthernet library, connecting to MariaDB Server.

Code:
Starting Complex_Select on NRF52840_FEATHER using ENC28J60/UIPEthernet Library
ENC28J60_CONTROL_CS =10
SS =5
SPI_MOSI =25
SPI_MISO =24
SPI_SCK =26
Using mac index = 5
Connected! IP address: 192.168.2.161
Connecting to SQL Server @ 192.168.2.112, Port = 5698
User = invited-guest, PW = the-invited-guest, DB = world
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 885240 ORDER BY population DESC LIMIT 12;
name,population
Port-au-Prince,884472
Mosul,879000
Barquisimeto,877239
Krasnojarsk,875500
Saratov,874000
Shubra al-Khayma,870716
S⸮o Gon⸮alo,869254
Songnam,869094
Chiba,863930
Nova Igua⸮u,862225
Naucalpan de Ju⸮rez,857511
Pikine,855287
[SQL] Disconnected

Sleeping...
================================================
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 1015280 ORDER BY population DESC LIMIT 12;
name,population
Kalyan,1014557
Birmingham,1013000
Rostov-na-Donu,1012700
Odesa,1011000
Perm,1009700
Napoli,1002619
Zapopan,1002239
Amman,1000000
Mogadishu,997000
Volgograd,993400
Sendai,989975
Peshawar,988005
[SQL] Disconnected

Sleeping...
================================================
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Connected. Server Version = 5.5.5-10.3.23-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 1193330 ORDER BY population DESC LIMIT 12;
name,population
Tabriz,1191043
Dallas,1188580
Bel⸮m,1186926
Multan,1182441
Praha,1181126
Kalookan,1177604
Nanning,1161800
C⸮rdoba,1157507
Samara,1156100
Hyderabad,1151274
Omsk,1148900
Davao,1147116
[SQL] Disconnected

Sleeping...
================================================
 
That's pretty neat. I would probably use sqlite3 for most things. However, being able to talk to MySQL makes for much better integration with stuff running on other machines, and decouples data access from the device itself.
 
MySQL_MariaDB_Generic supports Teensy 4.1 NativeEthernet now

The library now supports Teensy 4.1 using NativeEthernet

Code:
Starting Complex_Select on Teensy 4.1 , with NativeEthernet
MySQL_MariaDB_Generic v1.3.0
[SQL] =========================================
[SQL] Default SPI pinout:
[SQL] MOSI: 11
[SQL] MISO: 12
[SQL] SCK: 13
[SQL] SS: 10
[SQL] =========================================
[SQL] Board : Teensy 4.1 , setCsPin: 10
[SQL] =========================================
[SQL] Currently Used SPI pinout:
[SQL] MOSI: 11
[SQL] MISO: 12
[SQL] SCK: 13
[SQL] SS: 10
[SQL] =========================================
Using mac index = 2
Connected! IP address: 192.168.2.86
Connecting to SQL Server @ 192.168.2.112 , Port = 5698
User = invited-guest , PW = the-invited-guest , DB = world
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Writing this_buffer, size_send = 72
[SQL] Connected. Server Version = 5.5.5-10.3.29-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 940450 ORDER BY population DESC LIMIT 12;
name,population
Santa Cruz de la Sierra,935361
Varanasi (Benares),929270
Patna,917243
Hohhot,916700
Rosario,907718
Voronez,907700
Soweto,904165
Torino,903705
San Jose,894943
Srinagar,892506
Agra,891790
Kampala,890800
[SQL] Disconnected

Sleeping...
================================================
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Writing this_buffer, size_send = 72
[SQL] Connected. Server Version = 5.5.5-10.3.29-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 1054490 ORDER BY population DESC LIMIT 12;
name,population
Shiraz,1053025
Donetsk,1050000
Ludhiana,1042740
Jilin,1040000
Tangshan,1040000
Vadodara (Baroda),1031346
Maputo,1018938
Montr�al,1016376
Kitakyushu,1016264
Kalyan,1014557
Birmingham,1013000
Rostov-na-Donu,1012700
[SQL] Disconnected

Sleeping...
 
The library now supports Teensy 4.1 using NativeEthernet

Code:
Starting Complex_Select on Teensy 4.1 , with NativeEthernet
MySQL_MariaDB_Generic v1.3.0
[SQL] =========================================
[SQL] Default SPI pinout:
[SQL] MOSI: 11
[SQL] MISO: 12
[SQL] SCK: 13
[SQL] SS: 10
[SQL] =========================================
[SQL] Board : Teensy 4.1 , setCsPin: 10
[SQL] =========================================
[SQL] Currently Used SPI pinout:
[SQL] MOSI: 11
[SQL] MISO: 12
[SQL] SCK: 13
[SQL] SS: 10
[SQL] =========================================
Using mac index = 2
Connected! IP address: 192.168.2.86
Connecting to SQL Server @ 192.168.2.112 , Port = 5698
User = invited-guest , PW = the-invited-guest , DB = world
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Writing this_buffer, size_send = 72
[SQL] Connected. Server Version = 5.5.5-10.3.29-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 940450 ORDER BY population DESC LIMIT 12;
name,population
Santa Cruz de la Sierra,935361
Varanasi (Benares),929270
Patna,917243
Hohhot,916700
Rosario,907718
Voronez,907700
Soweto,904165
Torino,903705
San Jose,894943
Srinagar,892506
Agra,891790
Kampala,890800
[SQL] Disconnected

Sleeping...
================================================
Connecting...
[SQL] Connecting to Server: 192.168.2.112 , Port =  5698
[SQL] Connect OK. Try reading packets
[SQL] Try parsing packets
[SQL] Try send_authentication packets
[SQL] Writing this_buffer, size_send = 72
[SQL] Connected. Server Version = 5.5.5-10.3.29-MariaDB-0+deb10u1
====================================================
> Running SELECT with dynamically supplied parameter
SELECT name, population FROM world.city WHERE population < 1054490 ORDER BY population DESC LIMIT 12;
name,population
Shiraz,1053025
Donetsk,1050000
Ludhiana,1042740
Jilin,1040000
Tangshan,1040000
Vadodara (Baroda),1031346
Maputo,1018938
Montr�al,1016376
Kitakyushu,1016264
Kalyan,1014557
Birmingham,1013000
Rostov-na-Donu,1012700
[SQL] Disconnected

Sleeping...

Why is this output spewing out SPI info when the Teensy 4.1 does not use SPI?
It has its own internal QSPI so even including SPI for this board is just a waste of code.

Regards,
Ed
 
Thanks for pointing out my mistakes, in forgetting to remove the SPI-bus displaying code, useful only for non-native Ethernet. Will fix.
 
Back
Top