ChuckBell
commented
1 year ago Hi,
I would separate the two queries. Don’t run them interlaced. This may require “Saving” the rows/data you need to update, but interlacing is likely to cause problems with the limited capabilities of the connector.
Dr. Bell
On Jul 12, 2022, at 10:13 AM, PeterUMJ @.***> wrote:
Hi
I have a problem with update inside a cursor loop. If I run without update, the result show all rows, but with update it skips every other row.
SetStartUpTurnout() is run in setup.
The code.
//uppdterar status för växel -------------------------------------------------------------------------------------------------------- void UpdateTurnout(int inTurnoutid, int inLastdir, int inLocked) { Serial.print("UpdateTurnout = "); Serial.println(inTurnoutid);
const char UPDATE_CUR[] = "UPDATE UMJ.turnout SET lastdir = %i, locked = 1 WHERE turnoutid = %i"; char update[128];
MySQL_Cursor *upd_mem = new MySQL_Cursor(&conn); sprintf(update, UPDATE_CUR, inLastdir, inTurnoutid); upd_mem->execute(update); delete upd_mem; Serial.println("UpdateTurnout = klar");
}
// init turnout when startup---------------------------------------------------------------------------------------------- void SetStartUpTurnout() {
int SPinSta; int DPinSta; int loLastDir; long loStartdir; long loTurnoutid; long loPairTurnout; long loMcp; long loDpin; long loSpin;
const char QUERY_CUR[] = "SELECT turnoutid, startdir, pairturnout, mcp, dpin, spin FROM UMJ.turnout WHERE panel = %i"; char query[128]; MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, QUERY_CUR, 1);
cur_mem->execute(query); //cur_mem->show_results(); column_names cols = cur_mem->get_columns(); row_values row = NULL;
do {row = cur_mem->get_next_row();
if (row != NULL) { loTurnoutid = atol(row->values[0]);
loStartdir = atol(row->values[1]); loPairTurnout = atol(row->values[2]); loMcp = atol(row->values[3]); loDpin = atol(row->values[4]); loSpin = atol(row->values[5]);Serial.print(loTurnoutid); Serial.print(','); Serial.print(loStartdir); Serial.print(','); Serial.print(loPairTurnout); Serial.print(','); Serial.print(loMcp); Serial.print(','); Serial.print(loDpin); Serial.print(','); Serial.println(loSpin); if (loStartdir == 1) { sendOPC_SW_REQ(loTurnoutid, TURNOUT_DIVERGING, 1); loLastDir = 1; SPinSta = 0; DPinSta = 1; } else { sendOPC_SW_REQ(loTurnoutid, TURNOUT_NORMAL, 1); loLastDir = 0; SPinSta = 1; DPinSta = 0; } if (loPairTurnout != 0) { if (loLastDir == 0) { sendOPC_SW_REQ(loPairTurnout, TURNOUT_NORMAL, 1); } else { sendOPC_SW_REQ(loPairTurnout, TURNOUT_DIVERGING, 1); } } UpdateTurnout(loTurnoutid, loLastDir, 1); // SetLed(loMcp, loSpin, SPinSta, loDpin, DPinSta); }} while (row != NULL); delete cur_mem; }
from serial monitor, run without update _pinCS = 0W5100 init, using SS_PIN_DEFAULT = 10, new ss_pin = 10, W5100Class::ss_pin = 10W5100:: init: W5500, SSIZE =4096 Ansluter till databasen...... trying... Connected to server version 5.5.5-10.3.32-MariaDBAnsluten till UMJ db! 64,1,0,0,3,2 65,0,0,0,9,8 66,0,0,0,7,6 67,0,0,0,5,4 73,0,0,0,13,12 76,0,0,0,11,10 77,0,0,1,1,0 78,0,0,0,15,14 94,0,93,1,3,2 961,0,0,0,1,0 Setup klar
from serial monitor, run with update _pinCS = 0W5100 init, using SS_PIN_DEFAULT = 10, new ss_pin = 10, W5100Class::ss_pin = 10W5100:: init: W5500, SSIZE =4096 Ansluter till databasen...... trying... Connected to server version 5.5.5-10.3.32-MariaDBAnsluten till UMJ db! 64,1,0,0,3,2 UpdateTurnout = 64 UpdateTurnout = klar 66,0,0,0,7,6 UpdateTurnout = 66 UpdateTurnout = klar 73,0,0,0,13,12 UpdateTurnout = 73
Any idea what the problem might be?
Peter
— Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/191, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYGXCMVMIRANCNPJYEDVTV4RNANCNFSM53LGBK3A. You are receiving this because you are subscribed to this thread.
PeterUMJ
Hi
I have a problem with update inside a cursor loop. If I run without update, the result show all rows, but with update it skips every other row.
SetStartUpTurnout() is run in setup.
The code.
//uppdterar status för växel -------------------------------------------------------------------------------------------------------- void UpdateTurnout(int inTurnoutid, int inLastdir, int inLocked) { Serial.print("UpdateTurnout = "); Serial.println(inTurnoutid);
const char UPDATE_CUR[] = "UPDATE UMJ.turnout SET lastdir = %i, locked = 1 WHERE turnoutid = %i"; char update[128];
MySQL_Cursor *upd_mem = new MySQL_Cursor(&conn); sprintf(update, UPDATE_CUR, inLastdir, inTurnoutid); upd_mem->execute(update); delete upd_mem; Serial.println("UpdateTurnout = klar");
}
// init turnout when startup---------------------------------------------------------------------------------------------- void SetStartUpTurnout() {
int SPinSta; int DPinSta; int loLastDir; long loStartdir; long loTurnoutid; long loPairTurnout; long loMcp; long loDpin; long loSpin;
const char QUERY_CUR[] = "SELECT turnoutid, startdir, pairturnout, mcp, dpin, spin FROM UMJ.turnout WHERE panel = %i"; char query[128]; MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, QUERY_CUR, 1);
cur_mem->execute(query); //cur_mem->show_results(); column_names cols = cur_mem->get_columns(); row_values row = NULL;
do {row = cur_mem->get_next_row();
}
from serial monitor, run without update
64,1,0,0,3,2 65,0,0,0,9,8 66,0,0,0,7,6 67,0,0,0,5,4 73,0,0,0,13,12 76,0,0,0,11,10 77,0,0,1,1,0 78,0,0,0,15,14 94,0,93,1,3,2 961,0,0,0,1,0 Setup klar
64,1,0,0,3,2 UpdateTurnout = 64 UpdateTurnout = klar 66,0,0,0,7,6 UpdateTurnout = 66 UpdateTurnout = klar 73,0,0,0,13,12 UpdateTurnout = 73
Any idea what the problem might be?
Peter