ChuckBell
commented
6 years ago Hello,
The first bit of advice is KISS - keep it simple. You should not jump immediately to reading sensors and running queries if you cannot connect. That is, first build a sketch that connects to the server and once that is working, add the other bits. If you adopt this attitude, you will get much further in your own diagnosis.
Now, as to why you cannot connect, there are many things and several areas that you need to check as listed in the reference manual.
Namely...
- Begin by making a copy of your sketch and strip out everything except the MySQL parts and try that. If that works, you have a conflict between either the libraries you're using or the code you've written to read the sensors. But don't go to the next step until this is working.
As to what the problem could be, here are some observations (guesses):
-
I see the use of software serial in your sketch. That can be a problem if you choose the wrong pins.
-
I notice in your sketch, the IP address for the server is on the private 104.20.67 subnet. Are you certain that is correct? If so, are you certain your WiFi can reach the subnet? Try the laptop connect (see below) to be certain.
-
You have defined static networking properties. Why? This is almost always a bad idea with WiFi networks that you don't own. Use DHCP. I've found nearly 100% of people who ask for help and use static IP fail to get a connection because the networking properties don't match. Don't guess, use DHCP.
-
You should initialize the WiFi before anything else. Put that at the top of your setup().
-
You need a guard around the query. If connected... See the example sketches.
-
Check the MySQL version number to ensure you're using a true, Oracle produced MySQL installation. The connector may not work with forks of MySQL.
-
Ensure the credentials you're using actually work on the server. Remember, MySQL user accounts are formed by user+hostname. Your permissions to access the server can be denied if you failed to post the correct hostname when you created the account. Most people for projects such as these use anonymous hostnames (%).
-
Ensure the network pathways from your board to the server work. This can only be accomplished by using another machine (e.g. laptop) connected, in this case, to the same WiFi as your board. Run the MySQL client (mysql) on the laptop with the credentials from your sketch. If it connects, move on. If not, fix that problem first (most likely cause is (2) above).
-
Ensure your board is working. Some have reported all manner of issues and bugs only to discover their wee little board is broken. This is less likely, but it happens.
-
Finally, and this is a long shot, depending on which ESP board you are using, you may not be able to use the connector. I see you're using the ESP8266WiFi, which is the correct library, however, I have found some ESP's don't work with that library and, hence, the connector.
If all of this doesn't work, send me the model of your board and the version of the connector and MySQL that you are using and I will consider possibility of conflicts.
Dr. Bell
On 3/16/18 12:54 AM, nithish21896 wrote:
i am trying to create a project wherein i read the epc number from rfid tags and compare them with the epc already stored in database and read out the result in the serial monitor of arduino. but i am unsuccessful everytime i try to connect. this is my code
include
include
include
include
include
include
include
//pin connecction configuration
define rxPin 2 // Serial input (connects to Emic 2 SOUT)
define txPin 3 // Serial output (connects to Emic 2 SIN)
define SS_PIN 10
define RST_PIN 9
MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance. SoftwareSerial emicSerial = SoftwareSerial(rxPin, txPin);
char ssid[] = "Airtel-WD670-71CE";// Network Name char pass[] = "D8F171CE"; // Network Password byte mac[6];
WiFiServer server(80); IPAddress ip(192, 168, 1, 100);//192.168.1.100 IPAddress gateway(192, 168, 1, 1); IPAddress subnet(255, 255, 255, 0);
WiFiClient client; MySQL_Connection conn((Client *)&client);
IPAddress server_addr(104,20 ,67,46); // MySQL server IP char user[] = "xxx"; // MySQL user char password[] = "xxx"; // MySQL password
//query instruction const char QUERY_POP[] = "SELECT name,cost,description FROM id4857053_database.details WHERE id='content';"; char query[128];
void setup() {
//setup of mfrc522 Serial.begin(9600); // Initiate a serial communication SPI.begin(); // Initiate SPI bus mfrc522.PCD_Init(); // Initiate MFRC522 Serial.println("Approximate your card to the reader..."); Serial.println();
//emic2 pinout details pinMode(rxPin, INPUT); pinMode(txPin, OUTPUT);
// set the data rate for the SoftwareSerial port emicSerial.begin(9600); emicSerial.print('\n'); // Send a CR in case the system is already up while (emicSerial.read() != ':'); // When the Emic 2 has initialized and is ready, it will send a single ':' character, so wait here until we receive it delay(10); // Short delay emicSerial.flush(); // Flush the receive buffer
//setting up esp8266 connection Serial.println("Initialising connection"); Serial.print(F("Setting static ip to : ")); Serial.println(ip);
Serial.println(""); Serial.println(""); Serial.print("Connecting to "); Serial.println(ssid); WiFi.config(ip, gateway, subnet); WiFi.begin(ssid, pass);
while (WiFi.status() != WL_CONNECTED) { delay(200); Serial.print("."); }
Serial.println(""); Serial.println("WiFi Connected");
WiFi.macAddress(mac); Serial.print("MAC: "); Serial.print(mac[5],HEX); Serial.print(":"); Serial.print(mac[4],HEX); Serial.print(":"); Serial.print(mac[3],HEX); Serial.print(":"); Serial.print(mac[2],HEX); Serial.print(":"); Serial.print(mac[1],HEX); Serial.print(":"); Serial.println(mac[0],HEX); Serial.println(""); Serial.print("Assigned IP: "); Serial.print(WiFi.localIP()); Serial.println("");
Serial.println("Connecting to database");
while (conn.connect(server_addr, 3306, user, password) != true) { delay(200); Serial.print ( "." ); }
Serial.println(""); Serial.println("Connected to SQL Server!");
}
void loop() {
// Look for new cards if ( ! mfrc522.PICC_IsNewCardPresent()) { return; } // Select one of the cards if ( ! mfrc522.PICC_ReadCardSerial()) { return; } //Show UID on serial monitor Serial.print("UID tag :"); String content= ""; byte letter; for (byte i = 0; i < mfrc522.uid.size; i++) { Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " "); Serial.print(mfrc522.uid.uidByte[i], HEX); content.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ")); content.concat(String(mfrc522.uid.uidByte[i], HEX)); } Serial.println(); Serial.print("Message : "); content.toUpperCase();
delay(10000); //10 sec
Serial.println("> Running SELECT with dynamically supplied parameter");
// Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Supply the parameter for the query // Here we use the QUERY_POP as the format string and query as the // destination. This uses twice the memory so another option would be // to allocate one buffer for all formatted queries or allocate the // memory as needed (just make sure you allocate enough memory and // free it when you're done!). sprintf(query, QUERY_POP, 9000000); // Execute the query cur_mem->execute(query); // Fetch the columns and print them column_names cols = cur_mem->get_columns(); for (int f = 0; f < cols->num_fields; f++) { Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(','); } } Serial.println(); // Read the rows and print them row_values *row = NULL; do { row = cur_mem->get_next_row(); if (row != NULL) { for (int f = 0; f < cols->num_fields; f++) { Serial.print(row->values[f]); if (f < cols->num_fields-1) { Serial.print(','); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem;
}
any solution to it will be appreciated. thank you in advance
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/35, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4GhXamLFl49u_8xHJ0MgRCYYN5o-ks5te0V_gaJpZM4StPxt.
nithish21896
i am trying to create a project wherein i read the epc number from rfid tags and compare them with the epc already stored in database and read out the result in the serial monitor of arduino. but i am unsuccessful everytime i try to connect. this is my code
include
include
include
include
include
include
include
//pin connecction configuration
define rxPin 2 // Serial input (connects to Emic 2 SOUT)
define txPin 3 // Serial output (connects to Emic 2 SIN)
define SS_PIN 10
define RST_PIN 9
MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance. SoftwareSerial emicSerial = SoftwareSerial(rxPin, txPin);
char ssid[] = "Airtel-WD670-71CE";// Network Name char pass[] = "D8F171CE"; // Network Password byte mac[6];
WiFiServer server(80); IPAddress ip(192, 168, 1, 100);//192.168.1.100 IPAddress gateway(192, 168, 1, 1); IPAddress subnet(255, 255, 255, 0);
WiFiClient client; MySQL_Connection conn((Client *)&client);
IPAddress server_addr(104,20 ,67,46); // MySQL server IP char user[] = "xxx"; // MySQL user char password[] = "xxx"; // MySQL password
//query instruction const char QUERY_POP[] = "SELECT name,cost,description FROM id4857053_database.details WHERE id='content';"; char query[128];
void setup() {
//setup of mfrc522 Serial.begin(9600); // Initiate a serial communication SPI.begin(); // Initiate SPI bus mfrc522.PCD_Init(); // Initiate MFRC522 Serial.println("Approximate your card to the reader..."); Serial.println();
//emic2 pinout details pinMode(rxPin, INPUT); pinMode(txPin, OUTPUT);
// set the data rate for the SoftwareSerial port emicSerial.begin(9600); emicSerial.print('\n'); // Send a CR in case the system is already up while (emicSerial.read() != ':'); // When the Emic 2 has initialized and is ready, it will send a single ':' character, so wait here until we receive it delay(10); // Short delay emicSerial.flush(); // Flush the receive buffer
//setting up esp8266 connection Serial.println("Initialising connection"); Serial.print(F("Setting static ip to : ")); Serial.println(ip);
Serial.println(""); Serial.println(""); Serial.print("Connecting to "); Serial.println(ssid); WiFi.config(ip, gateway, subnet); WiFi.begin(ssid, pass);
while (WiFi.status() != WL_CONNECTED) { delay(200); Serial.print("."); }
Serial.println(""); Serial.println("WiFi Connected");
WiFi.macAddress(mac); Serial.print("MAC: "); Serial.print(mac[5],HEX); Serial.print(":"); Serial.print(mac[4],HEX); Serial.print(":"); Serial.print(mac[3],HEX); Serial.print(":"); Serial.print(mac[2],HEX); Serial.print(":"); Serial.print(mac[1],HEX); Serial.print(":"); Serial.println(mac[0],HEX); Serial.println(""); Serial.print("Assigned IP: "); Serial.print(WiFi.localIP()); Serial.println("");
Serial.println("Connecting to database");
while (conn.connect(server_addr, 3306, user, password) != true) { delay(200); Serial.print ( "." ); }
Serial.println(""); Serial.println("Connected to SQL Server!");
}
void loop() {
// Look for new cards if ( ! mfrc522.PICC_IsNewCardPresent()) { return; } // Select one of the cards if ( ! mfrc522.PICC_ReadCardSerial()) { return; } //Show UID on serial monitor Serial.print("UID tag :"); String content= ""; byte letter; for (byte i = 0; i < mfrc522.uid.size; i++) { Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " "); Serial.print(mfrc522.uid.uidByte[i], HEX); content.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ")); content.concat(String(mfrc522.uid.uidByte[i], HEX)); } Serial.println(); Serial.print("Message : "); content.toUpperCase();
delay(10000); //10 sec
Serial.println("> Running SELECT with dynamically supplied parameter");
// Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Supply the parameter for the query // Here we use the QUERY_POP as the format string and query as the // destination. This uses twice the memory so another option would be // to allocate one buffer for all formatted queries or allocate the // memory as needed (just make sure you allocate enough memory and // free it when you're done!). sprintf(query, QUERY_POP, 9000000); // Execute the query cur_mem->execute(query); // Fetch the columns and print them column_names cols = cur_mem->get_columns(); for (int f = 0; f < cols->num_fields; f++) { Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(','); } } Serial.println(); // Read the rows and print them row_values *row = NULL; do { row = cur_mem->get_next_row(); if (row != NULL) { for (int f = 0; f < cols->num_fields; f++) { Serial.print(row->values[f]); if (f < cols->num_fields-1) { Serial.print(','); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem;
}
any solution to it will be appreciated. thank you in advance