Closed chakthy closed 2 years ago
Try closing the cursor before running a second query.
On 8/5/19 3:54 PM, chakthy wrote:
I used the following code. The first query works and at the end of query2 I get the printed message. It looks like the program fails trying to fetch the columns. If I remove the column code it goes through all the code but displays the error that columns should be fetch first.
if (conn.connected()) { // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, INSERT_SQL,card1,card2,card3,card4); Serial.println(query); cur_mem->execute(query); Serial.println("Record created"); delay(1000);
|//This is to retrieve if the card is active or not sprintf(query2, INSERT_SQL2,card1,card2,card3,card4); Serial.println(query2); cur_mem->execute(query2); Serial.println("second query"); // Fetch the columns (required) but we don't use them. column_names *cols = cur_mem->get_columns(); Serial.println("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 row (we are only expecting the one) do { row = cur_mem->get_next_row(); if (row != NULL) { // for (int f = 0; f < cols->num_fields; f++) { // Serial.print(row->values[f]); head_count = atol(row->values[0]); Serial.print(" "); // } Serial.println(); Serial.println("got though row code"); } } while (row != NULL); // Deleting the cursor also frees up memory used Serial.println("got here"); |
// Show the result Serial.print(" Status = "); Serial.println(head_count);
— 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/108?email_source=notifications&email_token=AB6SHYEDXYCRYBX5LVCJAPTQDCAOVA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HDO4YGQ, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYDSQ32CXGRJ3FUIUFDQDCAOVANCNFSM4IJOLXZA.
If I use delete cur_mem after the first query it closes the WiFi connection. Not sure why?
No, cur_mem->close();
On 8/5/19 4:05 PM, chakthy wrote:
If I use delete cur_mem after the first query it closes the WiFi connection. Not sure why?
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/108?email_source=notifications&email_token=AB6SHYFOHN52H73RAB6EIC3QDCB2LA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3S5NQQ#issuecomment-518379202, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYDTANLOKXZUBB3B753QDCB2LANCNFSM4IJOLXZA.
OK. So I set curmem>close() below the Serial.println("Record created"). It is doing the same thing. It gets to the message Serial.println("second query") and hangs there.
Try replacing your handling of the results with show_results(). If that doesn't work, you'll need to send me your entire sketch so I can debug it.
On 8/5/19 4:17 PM, chakthy wrote:
OK. So I set curmem>close() below the Serial.println("Record created"). It is doing the same thing. It gets to the message Serial.println("second query") and hangs there.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/108?email_source=notifications&email_token=AB6SHYCS6OYRTXCZJSSIGA3QDCDHDA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3S6LPY#issuecomment-518383039, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYFWN4UI47LMV6IATBLQDCDHDANCNFSM4IJOLXZA.
I also commented the first query and only processed query2 and the result is the same
`
// If using the breakout with SPI, define the pins for SPI communication.
Adafruit_PN532 nfc(PN532_IRQ, PN532_RESET);
// for Zero, output on USB Serial console, remove line below if using programming port to program the Zero! // also change #define in Adafruit_PN532.cpp library file // #define Serial SerialUSB
char ssid[] = “network”; // your network SSID (name) char pass[] = “pass”; // your network password (use for WPA, or use as key for WEP) int status = WL_IDLE_STATUS; // the Wifi radio's status IPAddress server_addr(1x,x,x,x); // IP of the MySQL server here char user[] = “user”; // MySQL user char password[] = “pass2”; // MySQL password // Sample query
char INSERT_SQL[] = "INSERT INTO db.parents_log (v1, v2, v3, v4, date) VALUES ('%d','%d','%d','%d',NOW())"; char query[130]; char INSERT_SQL2[] = "SELECT status FROM db.table where v1='%d' and v2='%d' and v3='%d' and v4='%d'"; char query2[130]; int estado; WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn((Client )&client); MySQL_Cursor cursor;
void setup() { pinMode(3, OUTPUT); pinMode(4,OUTPUT); pinMode(5,OUTPUT); //signal to gate relay
while (!Serial); // for Leonardo/Micro/Zero
//Initialize serial and wait for port to open: Serial.begin(115200); while (!Serial) { ; // wait for serial port to connect. Needed for native USB port only }
// check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true); }
String fv = WiFi.firmwareVersion(); if (fv < "1.0.0") { Serial.println("Please upgrade the firmware"); }
// attempt to connect to Wifi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to WPA SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network: status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection:
delay(3000);
printWifiData(); } // RFID reader code nfc.begin();
uint32_t versiondata = nfc.getFirmwareVersion(); Serial.print(versiondata); if (! versiondata) { Serial.print("Didn't find PN53x board"); while (1); // halt } // Got ok data, print it out! Serial.print("Found chip PN5"); Serial.println((versiondata>>24) & 0xFF, HEX); Serial.print("Firmware ver. "); Serial.print((versiondata>>16) & 0xFF, DEC); Serial.print('.'); Serial.println((versiondata>>8) & 0xFF, DEC);
// configure board to read RFID tags nfc.SAMConfig(); FREERAM_PRINT; Serial.println("Waiting for an ISO14443A Card ..."); ///RfiD code ends
}
void loop() { row_values *row = NULL; long head_count = 0; // check the network connection once every 10 seconds: delay(1000); printCurrentNet(); digitalWrite(3,HIGH); //yellow digitalWrite(4,LOW); //green digitalWrite(5,LOW); //close
uint8_t success; uint8_t uid[] = { 0, 0, 0, 0, 0, 0, 0 }; // Buffer to store the returned UID uint8_t uidLength; // Length of the UID (4 or 7 bytes depending on ISO14443A card type) uint8_t idn; // This will be used to store the card number through the code uint8_t (_uid[7]); // This extracts the card number uint8_t card1 = 0; uint8_t card2 = 0; uint8_t card3 = 0; uint8_t card4 = 0;
// Wait for an ISO14443A type cards (Mifare, etc.). When one is found // 'uid' will be populated with the UID, and uidLength will indicate // if the uid is 4 bytes (Mifare Classic) or 7 bytes (Mifare Ultralight) success = nfc.readPassiveTargetID(PN532_MIFARE_ISO14443A, uid, &uidLength); card1 = _uid[7]; card2 = _uid[8]; card3 = _uid[9]; card4 = _uid[10];
if (success) { // Display some basic information about the card digitalWrite(3,HIGH); //yellow digitalWrite(4,HIGH); //green Serial.println("Found an ISO14443A card"); Serial.print(" UID Length: ");Serial.print(uidLength, DEC);Serial.println(" bytes"); Serial.print(" UID Value: "); nfc.PrintHex(uid, uidLength); Serial.println("");
FREERAM_PRINT;
if (uidLength == 4)
{
// We probably have a Mifare Classic card ...
Serial.println("Seems to be a Mifare Classic card (4 byte UID)");
// Now we need to try to authenticate it for read/write access
// Try with the factory default KeyA: 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF
Serial.println("Trying to authenticate block 4 with default KEYA value");
uint8_t keya[6] = { 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF };
// Start with block 4 (the first block of sector 1) since sector 0
// contains the manufacturer data and it's probably better just
// to leave it alone unless you know what you're doing
success = nfc.mifareclassic_AuthenticateBlock(uid, uidLength, 4, 0, keya);
if (success)
{
Serial.println("Sector 1 (Blocks 4..7) has been authenticated");
uint8_t data[16];
// If you want to write something to block 4 to test with, uncomment
// the following line and this text should be read back in a minute
//memcpy(data, (const uint8_t[]){ 'a', 'd', 'a', 'f', 'r', 'u', 'i', 't', '.', 'c', 'o', 'm', 0, 0, 0, 0 }, sizeof data);
// success = nfc.mifareclassic_WriteDataBlock (4, data);
// Try to read the contents of block 4
success = nfc.mifareclassic_ReadDataBlock(4, data);
if (success)
{
// Data seems to have been read ... spit it out
Serial.println("Reading Block 4:");
nfc.PrintHexChar(data, 16);
Serial.println("");
// Wait a bit before reading the card again
delay(1000);
}
else
{
Serial.println("Ooops ... unable to read the requested block. Try another key?");
}
}
else
{
Serial.println("Ooops ... authentication failed: Try another key?");
}
}
if (uidLength == 7)
{
// We probably have a Mifare Ultralight card ...
Serial.println("Seems to be a Mifare Ultralight tag (7 byte UID)");
// Try to read the first general-purpose user page (#4)
Serial.println("Reading page 4");
uint8_t data[32];
success = nfc.mifareultralight_ReadPage (4, data);
if (success)
{
// Data seems to have been read ... spit it out
nfc.PrintHexChar(data, 4);
Serial.println("");
// Wait a bit before reading the card again
delay(1000);
}
else
{
Serial.println("Ooops ... unable to read the requested page!?");
}
}
Serial.println("");
delay(1000);
Serial.println("Connecting to SQL...");
Serial.println(server_addr);
client.flush();
if (! conn.connected()) {
conn.connect(server_addr, 3306, user, password);
delay(5000);
Serial.println("Connected.");
digitalWrite(3,LOW); //yellow
digitalWrite(4,HIGH); //green
}
if (conn.connected()) {
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
sprintf(query, INSERT_SQL,v1,v2,v3,v4);
// Serial.println(query); cur_mem->execute(query); Serial.println("Record created"); delay(1000);
cur_mem->close();
//This is to retrieve if the record is active or not
sprintf(query2, INSERT_SQL2,v1,v2,v3,v4);
Serial.println(query2);
cur_mem->execute(query2);
Serial.println("second query");
// Fetch the columns (required) but we don't use them.
column_names *cols = cur_mem->get_columns();
Serial.println("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 row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
head_count = atol(row->values[0]);
Serial.print(" ");
}
Serial.println();
Serial.println("row");
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
Serial.println("second");
// Show the result Serial.print(" Status = "); Serial.println(head_count);
delay(500);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
Serial.println("Completed");
digitalWrite(3,HIGH); //yellow
digitalWrite(4,HIGH); //green
digitalWrite(5,HIGH); //open
delay(1000);
digitalWrite(5,LOW); //close
client.flush();
} else{
Serial.println("Connection not established");
conn.close();
}
} }
void printWifiData() { // print your board's IP address: IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print your MAC address: byte mac[6]; WiFi.macAddress(mac); Serial.print("MAC address: "); printMacAddress(mac); }
void printCurrentNet() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print the MAC address of the router you're attached to: byte bssid[6]; WiFi.BSSID(bssid); Serial.print("BSSID: "); printMacAddress(bssid);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.println(rssi);
// print the encryption type: byte encryption = WiFi.encryptionType(); Serial.print("Encryption Type:"); Serial.println(encryption, HEX); Serial.println(); }
void printMacAddress(byte mac[]) { for (int i = 5; i >= 0; i--) { if (mac[i] < 16) { Serial.print("0"); } Serial.print(mac[i], HEX); if (i > 0) { Serial.print(":"); } } Serial.println(); }`
I tried the show_results() but the behavior was the same.
This should be on the code instead of card ` uint8_t v1 = 0; uint8_t v2 = 0; uint8_t v3 = 0; uint8_t v4 = 0;
// Wait for an ISO14443A type cards (Mifare, etc.). When one is found // 'uid' will be populated with the UID, and uidLength will indicate // if the uid is 4 bytes (Mifare Classic) or 7 bytes (Mifare Ultralight) success = nfc.readPassiveTargetID(PN532_MIFARE_ISO14443A, uid, &uidLength); v1 = _uid[7]; v2 = _uid[8]; v3 = _uid[9]; v4 = _uid[10]; `
I don't see anything specific, but I do have a few observations. I hope these help.
The query buffer you're using is only 130 bytes (chars). The string by itself is about 75. Are you running over the buffer? That'll cause strange behavior. Note: you only need one buffer. No need to have the two.
The sketch is quite long and complex. I would suggest creating a new sketch with the absolute minimal code (wifi + mysql) mock queries and test the MySQL parts separately. It is far too likely that some memory issue or other problem is manifesting as another error. Always best to code and test in small pieces before trying the whole. For example, if you write a sketch to test the MySQL code parts and it works without changes, there's something else wrong.
I would set row = null before the loop to read the rows to prevent issues running the query a second time.
What is the client.flush() for? Remove it.
You have several libraries in the code. Are you running low on memory? If you have less than 500-700 bytes for data after compile, the sketch is going to be unstable and will fail.
I am seeing many compilation errors. For example, the line: MySQL_Cursor cursor; ...is not valid. Are you using the Arduino IDE or something else to compile?
Try adding cur_mem->clear_ok_packet() before the first close(). This clears the buffer.
Try turning on debug (place #define DEBUG 1 in mysql_packet.h).
Finally, please show me exactly what you are seeing in the serial monitor.
This is what I get after compiling and uploading the code.
Sketch uses 24698 bytes (50%) of program storage space. Maximum is 48640 bytes. Global variables use 1555 bytes (25%) of dynamic memory, leaving 4589 bytes for local variables. Maximum is 6144 bytes.
It seems like memory might not be the issue. I will try it without the reader and see what happens. Thanks
I'm using the Arduino IDE and I do not get any errors when compiling the code.
I must be doing something wrong with the DEBUG 1, because I don't any messages. I removed all the code except the wifi and the connector. The insert code works fine, but the Select does not.
Try the select by itself.
On Wed, Aug 14, 2019 at 15:59 chakthy notifications@github.com wrote:
I must be doing something wrong with the DEBUG 1, because I don't any messages. I removed all the code except the wifi and the connector. The insert code works fine, but the Select does not.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/108?email_source=notifications&email_token=AB6SHYDG6XETNM2DWEATXO3QERP2NA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4J55DA#issuecomment-521395852, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYFTKJVNZ4ZRYSOXCKLQERP2NANCNFSM4IJOLXZA .
That is what I did. I only left the SELECT with fixed values and no other query on the code. But it gets stuck on the same code for the columns.
What version of MySQL are you using?
Ok, here's a possible fix.
int MySQL_Packet::wait_for_bytes(int bytes_need) { const long wait_till = millis() + MYSQL_DATA_TIMEOUT; int num = 0; long now = 0;
do { now = millis(); num += client->available();
Change that last line in the code above (MySQL_Packet.cpp) - add + before the =. Let me know if it works.
This is the version I have installed 5.7.25-0ubuntu0.18.04.2 libmysql - mysqlnd 5.0.12-dev - 20150407
Ok, that may not be an Oracle distribution. What does this give you?
Select @@version;
On Tue, Aug 20, 2019 at 15:25 chakthy notifications@github.com wrote:
This is the version I have installed 5.7.25-0ubuntu0.18.04.2 libmysql - mysqlnd 5.0.12-dev - 20150407
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/108?email_source=notifications&email_token=AB6SHYC52RSGTQCZ4FI7IF3QFRAMJA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4XMTKI#issuecomment-523159977, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYGORXCMYIU4SCCLO7DQFRAMJANCNFSM4IJOLXZA .
Ver 14.14 Distrib 5.7.25
Ok. I am not certain that is an Oracle MySQL release. Simply, the connector is not guaranteed to work with non-Oracle distributions.
It may be possible to get it to work by manipulating how it communicates, but it will require some work.
I recommend downloading and installing the Oracle MySQL distribution from dev.mysql.com.
On Fri, Aug 23, 2019 at 11:59 chakthy notifications@github.com wrote:
Ver 14.14 Distrib 5.7.25
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/108?email_source=notifications&email_token=AB6SHYBBJVF6UL2UBBBLLFTQGACONA5CNFSM4IJOLXZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5AT4XA#issuecomment-524369500, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYHD37C36NDNGPM7PKTQGACONANCNFSM4IJOLXZA .
Regardless, did you try the suggestion I posted?
Hello Chuck
The code you provided is to get added on the MySQL_Packet.cpp?
I used the following code. The first query works and at the end of query2 I get the printed message. It looks like the program fails trying to fetch the columns. If I remove the column code it goes through all the code but displays the error that columns should be fetch first.
if (conn.connected()) { // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, INSERT_SQL,card1,card2,card3,card4); Serial.println(query); cur_mem->execute(query);
Serial.println("Record created"); delay(1000);
// Show the result Serial.print(" Status = "); Serial.println(head_count);