grininmonkey
Posts: 16
Joined: Mon Jul 30, 2018 3:44 pm

C and mysql query in a loop issue

Fri Aug 10, 2018 9:17 pm

Code: Select all

    
while (!stop) {

        delay(500);

        if (mysql_real_query(conn2, "call proc_getTargetModeCwire()",30))
            printf("%s\n",mysql_error(conn2));
        
        result = mysql_use_result(conn2);
        if (result == NULL)
            printf("result was null\n");
        
        while ( (row = mysql_fetch_row(result)) != NULL ) {
        /*
            XPIN = atoi(row[1]);
            XSTATE = atoi(row[2]);
            if (digitalRead(XPIN) != XSTATE) {
                setPinMode(XPIN);
                digitalWrite(atoi(row[1]),(XSTATE==0) ? LOW : HIGH);
            }
            strcpy(v1,row[0]);
            v2 = digitalRead(XPIN);
            mysql_stmt_execute(statement);
        */
        //    printf("%s\n", row[0]);
        }

        mysql_free_result(result);

    }
Just now trying to do some stuff in C... but for the life of me I have not found any good results searching for my issue... but I get " Commands out of sync; you can't run this command now " with the above... which nearly all searches on this say to make sure you are freeing the result resource which I believe I am doing ?

I can comment out the loop and single pass works just fine.

User avatar
DougieLawson
Posts: 33603
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: C and mysql query in a loop issue

Sat Aug 11, 2018 7:27 pm

Sprinkle around some commit() calls. Add an explicit close() for your cursors.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

grininmonkey
Posts: 16
Joined: Mon Jul 30, 2018 3:44 pm

Re: C and mysql query in a loop issue

Sat Aug 11, 2018 9:20 pm

DougieLawson wrote:
Sat Aug 11, 2018 7:27 pm
Sprinkle around some commit() calls. Add an explicit close() for your cursors.
I would if I could find what those are in the MySQL C API https://dev.mysql.com/doc/refman/8.0/en ... rview.html .... Every example of a basic query and do something with the results... that I looked at, has no closing of cursor statements.... only thing close is the free results statement which I have tried.

I guess I should say or ask it this way ... without having pieces of code not related to the question I am trying to figure out within my examples...

Ok.... So the following code fails... the intended operation is to query something... wait a short bit and re-execute the same query... so on and so on until interrupted.

Code: Select all

#include <stdio.h>
#include <unistd.h>
#include <mysql.h>

#define DATABASE_HOST       		"#######"
#define DATABASE_NAME			"#######"
#define DATABASE_USERNAME	"#######"
#define DATABASE_PASSWORD	"#######"

int main (int argc, char * argv[])
{

    MYSQL *mysql = mysql_init(NULL);
    MYSQL *conn = mysql_real_connect(mysql, DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0);

    MYSQL_RES *result;
    MYSQL_ROW row;

    for( ; ; ) {

        if (mysql_real_query(conn, "call proc_getTargetModeCwire()",30))
            printf("%s\n",mysql_error(conn));
        
        result = mysql_use_result(conn);
        if (result == NULL)
            printf("result was null\n");
        
        while ( (row = mysql_fetch_row(result)) != NULL ) {
            printf("%s\n",row[0]);
        }

        mysql_free_result(result);
        sleep(1);

    }
    
    mysql_close(conn);

    return 0 ;

}
This results in the first pass success... and the second with failure

Code: Select all

[email protected]:~/Projects/Testing $ ./testing
G
O
Y
Commands out of sync; you can't run this command now
result was null
Segmentation fault
So the only solution I have "currently" figured out to do is to move the connection into the loop and close the connection within the loop... which to be honest... I don't even really know if that's a bad thing or not... its just not the approach I am use to when using something other than C

Code: Select all

#include <stdio.h>
#include <unistd.h>
#include <mysql.h>

#define DATABASE_HOST       		"#######"
#define DATABASE_NAME			"#######"
#define DATABASE_USERNAME	"#######"
#define DATABASE_PASSWORD	"#######"

int main (int argc, char * argv[])
{

    MYSQL_RES *result;
    MYSQL_ROW row;

    for( ; ; ) {

        MYSQL *mysql = mysql_init(NULL);
        MYSQL *conn = mysql_real_connect(mysql, DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0);

        if (mysql_real_query(conn, "call proc_getTargetModeCwire()",30))
            printf("%s\n",mysql_error(conn));
        
        result = mysql_use_result(conn);
        if (result == NULL)
            printf("result was null\n");
        
        while ( (row = mysql_fetch_row(result)) != NULL ) {
            printf("%s\n",row[0]);
        }

        mysql_free_result(result);
        mysql_close(conn);

        sleep(1);

    }

    return 0 ;

}

Code: Select all

[email protected]:~/Projects/Testing $ ./testing
G
O
Y
G
O
Y
G
O
Y
G
O
Y
G
O
Y
^C
[email protected]:~/Projects/Testing $ 

User avatar
DougieLawson
Posts: 33603
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: C and mysql query in a loop issue

Tue Aug 14, 2018 7:53 pm

Closing the connection in the loop is very bad. It costs lots of MySQL server CPU & I/O to establish a new connection each time.

I'd also like to see how proc_getTargetModeCwire() is defined.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

grininmonkey
Posts: 16
Joined: Mon Jul 30, 2018 3:44 pm

Re: C and mysql query in a loop issue

Tue Aug 14, 2018 9:35 pm

DougieLawson wrote:
Tue Aug 14, 2018 7:53 pm
Closing the connection in the loop is very bad. It costs lots of MySQL server CPU & I/O to establish a new connection each time.

I'd also like to see how proc_getTargetModeCwire() is defined.
I have been dabbling and searching some more with no luck.... I just simply can not figure out how to keep one connection outside the loop and within the loop keep requesting and fetching.... for now I have it re-creating the connection and closing within the loop until I can figure it out.

Within node, python, etc.... its a non issue... but I am learning C is particular.. and not sure if my issue is related to some "Strict" effect.

The Proc.. well...

Its just a simple proc for now that returns what I need from my thermostat database... based on current control mode which is data driven from user input and logic within triggers from a temperature table.. so the Proc being called returns a mapping of which relays on what pins need to be open or closed based on what the database says the current mode should be... cooling, heating, off, etc.

Code: Select all

CREATE DEFINER=`#######`@`%` PROCEDURE `proc_getTargetModeCwire`()
BEGIN

	select
		cw.wire,
		cw.gpio,
		ccw.targetstate
	from settings s
		join controlmode_cwire_assoc ccw on ccw.controlmode_fk = s.controlmode_fk
		join cwire cw on cw.wire = ccw.cwire_fk
	where cw.gpio > 0;

END

User avatar
DougieLawson
Posts: 33603
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: C and mysql query in a loop issue

Tue Aug 14, 2018 11:12 pm

How about this version:

Code: Select all

#include <stdio.h>
#include <unistd.h>
#include <string.h>
#include <my_global.h>
#include <mysql.h>

#define DATABASE_HOST           "192.168.3.14"
#define DATABASE_NAME           "dbname"
#define DATABASE_USERNAME       "dbuser"
#define DATABASE_PASSWORD       "dbpasswd"

int main (int argc, char * argv[])
{

    MYSQL *mysql = mysql_init(NULL);
    MYSQL *conn = mysql_real_connect(mysql, DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, CLIENT_MULTI_STATEMENTS);
    int start, end, nr, rq;
    char *sqlstmt = malloc(40);
    start = -5;
    end = -4;
    nr = 0;
    rq = 0;

    MYSQL_RES *result;
    MYSQL_ROW row;
    for( ; ; )
    {
        sprintf(sqlstmt, "CALL test.proc(%d, %d)\0",start, end);
        printf("Start: %d, end: %d\n", start, end);
        rq = mysql_real_query(conn, sqlstmt ,strlen(sqlstmt));
        printf("RQ: %d\n", rq);
        if (rq) printf("%s\n",mysql_error(conn));
        do
        {
            result = mysql_store_result(conn);
            if (result == NULL)
                printf("result was null\n");
            else
            {
                while ((row = mysql_fetch_row(result)) != NULL)
                {
                    printf("%s %s %s %s\n",row[0], row[1], row[2], row[3]);
                }

                mysql_free_result(result);
            }

        nr = mysql_next_result(conn);
        printf("NR: %d\n", nr);
        } while (nr == 0);
        start++;
        end++;
        sleep(1);
    }
    mysql_close(conn);
    return 0 ;
}
My proc takes a couple of parms and does a

Code: Select all

select col1, col2, col3, col4 from test_table where col1 between parm1 and parm2;
That doesn't get the command out of sequence error.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

grininmonkey
Posts: 16
Joined: Mon Jul 30, 2018 3:44 pm

Re: C and mysql query in a loop issue

Tue Aug 14, 2018 11:51 pm

YES!!

Thank you, for your example. Its the do while get next result wrapping the fetch row that fixes it. Not sure that I understand completly why.... but I now know a structure to doing this that works. :D

Is there additional meta data results aside from the actual results or something that you have to account for in C?

Edit:

Ahhhhh, I just slapped myself in the face.... my original was attempting fetch results on null result because I was not conditionally ignoring null result. I just finished updating my live file and still got the error even with the change of wrapping with the do and fetch next... then I finally noticed it.

User avatar
DougieLawson
Posts: 33603
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: C and mysql query in a loop issue

Wed Aug 15, 2018 9:44 pm

I'm glad you got that working.

It took me way longer that it should have done because the documentation for the MySQL C/C++ API is a pile of stinking stuff. They don't even give a simple pseudo code example to show what order you need to run the calls in (and Google seemed to want to give me PHP examples rather than C).
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

Return to “C/C++”

Who is online

Users browsing this forum: Heater and 8 guests