grininmonkey
Posts: 124
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: 34536
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

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.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

grininmonkey
Posts: 124
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: 34536
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

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.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

grininmonkey
Posts: 124
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: 34536
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

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.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

grininmonkey
Posts: 124
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: 34536
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

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).
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

Return to “C/C++”