Martwana
Posts: 30
Joined: Sun Dec 16, 2012 1:12 pm

Raspberry Pi MySQL_ROW Question

Sat Jan 26, 2013 2:36 am

Hello, bet your sick of me now :)

So, I'm writing a C program to check the installed MySQL server for gpio statuses for my home automation project. Everythings gone great so far.

I can query the database using the BCM pin numbers or the wiringPi numbers and get simple 0 or 1 for the pin status. Heres the problem. I used mysql_fetch_row to get the row, and when I try to compare it like so:

if (row[0] == 1){
/* TURN ON GPIO PIN */
}

It doesnt work. I have also tried to encase the 1 in doubles ", but it still doesnt work.

I read on the MySQL C API Documentation, that the data type is returned as a string, but I cant seem to figure out how to get this working.

Does anyone have experience with this situation?

I know it isn't really raspi talk, but i cant get an answer elsewhere.

User avatar
rpdom
Posts: 15575
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Raspberry Pi MySQL_ROW Question

Sat Jan 26, 2013 5:02 am

Not a C expert here, but I believe you need to use strcmp().

if ( strcmp( row[0], "1" ) ) {
/* TURN ON GPIO PIN */
}

C doesn't have straight "==" testing for character strings/arrays, just numbers or single characters (which are basically just numbers anyway).

Martwana
Posts: 30
Joined: Sun Dec 16, 2012 1:12 pm

Re: Raspberry Pi MySQL_ROW Question

Sat Jan 26, 2013 1:35 pm

That wouldn't work either. Compiles fine, just doesn't give the result properly.

Here's the whole program, in case someone else can think of it.

Code: Select all

#include <mysql.h>
#include <my_global.h>
#include <wiringPi.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>

// username = gpio .... password = EUAFrrpwBQXcBbPR

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

        MYSQL *dbc;
        MYSQL_RES *rGetStatus;
        MYSQL_ROW row;
        MYSQL_FIELD *field;

        int num_fields;
        int i;

        dbc = mysql_init(NULL);
        mysql_real_connect(dbc, "localhost", "gpio", "EUAFrrpwBQXcBbPR", "gpio", 0, NULL, 0);

        if (wiringPiSetup () == -1){
                exit (1);
        }

        int pinNumbers[14] = {8,9,7,0,2,3,12,13,13,15,16,1,4,5};

        for (i=0;i<sizeof(pinNumbers);i++){
                pinMode(pinNumbers[i], OUTPUT);
        }

        for (i=0;i<sizeof(pinNumbers);i++){
                char fullQuery[100], query[100] = "SELECT * FROM pins WHERE wiringPiPinNumber = %d ORDER BY wiringPiPinNumber ASC";
                int n = sprintf(fullQuery, query, pinNumbers[i]);

                mysql_query(dbc, fullQuery);
                rGetStatus = mysql_store_result(dbc);

                row = mysql_fetch_row(rGetStatus);

                if (strcmp(row[0],"1") == 0){  // AS PER THE REFERENCE EXAMPLE
                        digitalWrite(pinNumbers[i], HIGH);
                        printf("Pin %d is ON\n", pinNumbers[i]);
                }
                else {
                        digitalWrite(pinNumbers[i], LOW);
                        printf("Pin %d is OFF\n", pinNumbers[i]);
                }
        }

        mysql_free_result(rGetStatus);
        mysql_close(dbc);
}


User avatar
Paeryn
Posts: 2741
Joined: Wed Nov 23, 2011 1:10 am
Location: Sheffield, England

Re: Raspberry Pi MySQL_ROW Question

Wed Jan 30, 2013 7:36 pm

How is your database set up? I have just tried your sample program and as long as the pin status field is the first column in the table then your program works. If it isn't the first then you need to change row[0] to row[column_number_of_pin_status].
My sample database (wiringPiPinNumber 5 is ON, all others OFF):

Code: Select all

+--------+-------------------+--------------+
| status | wiringPiPinNumber | BCMPinNumber |
+--------+-------------------+--------------+
|      0 |                 0 |            8 |
|      0 |                 1 |            9 |
|      0 |                 2 |            7 |
|      0 |                 3 |            0 |
|      0 |                 4 |            2 |
|      1 |                 5 |            3 |
|      0 |                 6 |           12 |
|      0 |                 7 |           13 |
|      0 |                 8 |           14 |
|      0 |                 9 |           15 |
|      0 |                10 |           16 |
|      0 |                11 |            1 |
|      0 |                12 |            4 |
|      0 |                13 |            5 |
|      0 |                14 |            6 |
|      0 |                15 |           17 |
|      0 |                16 |           18 |
+--------+-------------------+--------------+
Output from your program:

Code: Select all

Pin 8 is OFF
Pin 9 is OFF
Pin 7 is OFF
Pin 0 is OFF
Pin 2 is OFF
Pin 3 is OFF
Pin 12 is OFF
Pin 13 is OFF
Pin 13 is OFF
Pin 15 is OFF
Pin 16 is OFF
Pin 1 is OFF
Pin 4 is OFF
Pin 5 is ON
She who travels light — forgot something.

Martwana
Posts: 30
Joined: Sun Dec 16, 2012 1:12 pm

Re: Raspberry Pi MySQL_ROW Question

Wed Jan 30, 2013 9:03 pm

Paeryn wrote:How is your database set up? I have just tried your sample program and as long as the pin status field is the first column in the table then your program works. If it isn't the first then you need to change row[0] to row[column_number_of_pin_status].
My sample database (wiringPiPinNumber 5 is ON, all others OFF):

Code: Select all

+--------+-------------------+--------------+
| status | wiringPiPinNumber | BCMPinNumber |
+--------+-------------------+--------------+
|      0 |                 0 |            8 |
|      0 |                 1 |            9 |
|      0 |                 2 |            7 |
|      0 |                 3 |            0 |
|      0 |                 4 |            2 |
|      1 |                 5 |            3 |
|      0 |                 6 |           12 |
|      0 |                 7 |           13 |
|      0 |                 8 |           14 |
|      0 |                 9 |           15 |
|      0 |                10 |           16 |
|      0 |                11 |            1 |
|      0 |                12 |            4 |
|      0 |                13 |            5 |
|      0 |                14 |            6 |
|      0 |                15 |           17 |
|      0 |                16 |           18 |
+--------+-------------------+--------------+
Output from your program:

Code: Select all

Pin 8 is OFF
Pin 9 is OFF
Pin 7 is OFF
Pin 0 is OFF
Pin 2 is OFF
Pin 3 is OFF
Pin 12 is OFF
Pin 13 is OFF
Pin 13 is OFF
Pin 15 is OFF
Pin 16 is OFF
Pin 1 is OFF
Pin 4 is OFF
Pin 5 is ON
Its setup using ints for each field. row[0] was also correct because the query I ran was only returning a single column. I was able to print the row absolutely fine, but I was unable to compare the row to anything. I was unable to understand from the MYSQL C API what datatype 'row[0]' was stored as in the program after being retrieved. I did solve this error by using the code below to convert(if you like) it to a char, which can then be compared with strcmp() easily.

Code: Select all

char r[5];
                        char s[5] = "%s";
                        int p = sprintf(r, s, row[0]);
This works now, and it works great, very responsive and doesn't seem to be inefficient on CPU resources.

Here is the complete program incase anyone wants to use it. It does work fine, but as you can read in the comments at the top, there is a few changes I need to make for the future.

Code: Select all

/*

Name:           Raspberry Pi GPIO SQL Daemon
Revision:       2
Date written:   28/01/2013
Author:         Martin McInnes
Email:          martin.j.mcinnes@gmail.com

Program Description:
        This daemon runs in the background, and queries a MySQL database for pin and in turn toggles them either on or off.

        The database table only needs 2 coloumns:
                wiringPiPinNumber OR bcmPinNumber
                AND
                pinStatus

        Obviously you need not name the columns the exact same, just remember to alter the SQL below :)

        Any questions, email me!

-----------------------------------------------------------------------------------------------------------------------------

Future Rev Notes:
        1)      Have pinNumbers array query database for pins.
        2)      Custom logging for daemon, and output as much as possible in event of error

I am currently working on a version of this daemon that will work with shift registers, to allow
my project "Home Automation" to have more than just the 17 GPIO pins available to control things.

I only started this on 30/01/2013, but if anyone is interested in it, drop me an email and I'll send it
to you once it's ready.

*/

#include <mysql.h>
#include <my_global.h>
#include <wiringPi.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <syslog.h>

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

        pid_t pid, sid;

        /* Clone ourselves to make a child */
        pid = fork();

        /* If the pid is less than zero,
        *    something went wrong when forking */
        if (pid < 0) {
                exit(EXIT_FAILURE);
        }

        /* If the pid we got back was greater
         *    than zero, then the clone was
         *       successful and we are the parent. */
        if (pid > 0) {
                exit(EXIT_SUCCESS);
        }

        /* If execution reaches this point we are the child */
        /* Set the umask to zero */
        umask(0);

        /* Open a connection to the syslog server */
        openlog(argv[0],LOG_NOWAIT|LOG_PID,LOG_USER);

/* Sends a message to the syslog daemon */
        syslog(LOG_NOTICE, "Successfully started the GPIO daemon\n");

        /* Try to create our own process group */
        sid = setsid();
        if (sid < 0) {
                syslog(LOG_ERR, "Could not create GPIO process group\n");
                exit(EXIT_FAILURE);
        }

        /* Change the current working directory */
        if ((chdir("/")) < 0) {
                syslog(LOG_ERR, "Could not change working directory to /\n");
                exit(EXIT_FAILURE);
        }

// Program Start
        if(wiringPiSetup() ==-1){
                syslog(LOG_ERR, "The GPIO daemon could not setup Wiring Pi\n");
                exit (1);
        }

        /* Close the standard file descriptors */
        close(STDIN_FILENO);
        close(STDOUT_FILENO);
        close(STDERR_FILENO);

        int i;

        // Array of pin numbers, remove any if you dont want them to be included in program.
        int pinNumbers[] = {8,9,7,0,2,3,12,13,14,15,16,1,4,5,6,10,11};

        // For each pin, set it to OUTPUT and turn it off
        for (i=0;i<sizeof(pinNumbers);i++){
                pinMode(pinNumbers[i], OUTPUT);
                digitalWrite(pinNumbers[i], LOW);
        }

 // Infinite loop for daemon, never ends unless process is killed. Better solution for exit method?
        for(;;){

                // Set MYSQL API Variables/Pointers
                MYSQL *dbc;
                MYSQL_RES *rGetStatus;
                MYSQL_ROW row;

                // Declare query arrays. fullQuery[] will store query[] with the relevant pin no for the loop below;
                char fullQuery[100], query[100] = "SELECT pinStatus FROM gpioPins WHERE wiringPiPinNumber = %d";

                // For each pin in  pinNumbers array
                for (i=0;i<((sizeof(pinNumbers))/(sizeof(int)));i++){

                        // Generate fullQuery[] from i
                        int n = sprintf(fullQuery, query, pinNumbers[i]);

// Uncomment to debug query problems
//printf("%s\n", fullQuery);

                        // Init connection to MYSQL
                        dbc = mysql_init(NULL);
                        // Connect to database ALTER THESE DETAILS IF YOU COPY THIS CODE
                        mysql_real_connect(dbc, "databasehost", "databaseuser", "password", "databasename", 0, NULL, 0);

                        // Run query
                        mysql_query(dbc, fullQuery);

// Store result of query
                        rGetStatus = mysql_use_result(dbc);

                        // Store the row in array
                        row = mysql_fetch_row(rGetStatus);

                        // Some housework to make the result comparable
                        char r[5];
                        char s[5] = "%s";
                        int p = sprintf(r, s, row[0]);
                        char com[5] = "1";

                        // If row value is the same as 1
                        if(strcmp(r, com) == 0){
                                // Turn corresponding pin on
                                digitalWrite(pinNumbers[i], HIGH);
                        } else {
                                // Else turn it off
                                digitalWrite(pinNumbers[i], LOW);
                        }

                        // Free up memory that result held
                        mysql_free_result(rGetStatus);
                        // Close connection to database
                        mysql_close(dbc);

                }// END FOR

        // Pause for a half second - CHANGE THIS TO A HIGHER NUMBER IF YOU LIKE
        // I wouldnt reccommend setting it any lower, as you may use up to much resources
        delay(500);

        } // END FOR

/* this is optional and only needs to be done when your daemon exits */
        closelog();

} // END MAIN

Please let me know of any modifications this could use.

Cromarty
Posts: 64
Joined: Thu Jan 03, 2013 5:03 pm
Location: Godalming, South-east UK
Contact: Website

Re: Raspberry Pi MySQL_ROW Question

Wed Jan 30, 2013 11:21 pm

Hello,

How about using the atoi function to convert a column containing a string representation of an integer to a real integer? (atoi = ascii to integer).

int i = atoi('1'); // yields an integer of value 1

Mike
--
Michael Ray
Analyst/Programmer
Surrey, UK
4xB, 1xB+,
Creator and admin of:
raspberry-vi@freelists.org
Ham radio call: G4XBF
'Suddenly I am become death, destroyer of SD cards'

User avatar
Paeryn
Posts: 2741
Joined: Wed Nov 23, 2011 1:10 am
Location: Sheffield, England

Re: Raspberry Pi MySQL_ROW Question

Wed Jan 30, 2013 11:24 pm

Martwana wrote:Its setup using ints for each field. row[0] was also correct because the query I ran was only returning a single column. I was able to print the row absolutely fine, but I was unable to compare the row to anything. I was unable to understand from the MYSQL C API what datatype 'row[0]' was stored as in the program after being retrieved. I did solve this error by using the code below to convert(if you like) it to a char, which can then be compared with strcmp() easily.

Code: Select all

char r[5];
                        char s[5] = "%s";
                        int p = sprintf(r, s, row[0]);
row[0] has the type of char *, (row has the type char **). You're not converting it into a char there, just copying the contents of the string from row[0] into r. Doing strcmp(row[0], "1") is identical to your now strcmp(r, com).
If you know that status will only ever be a single digit you can simplify it by just comparing the first character instead of the whole string - note it's only a single quote around the 1 this time.

Code: Select all

    if (*row[0] == '1') {
        // Turn corresponding pin on
You should really #include <string.h> at the top if you're using strcmp() as that's where it's defined.
She who travels light — forgot something.

Martwana
Posts: 30
Joined: Sun Dec 16, 2012 1:12 pm

Re: Raspberry Pi MySQL_ROW Question

Wed Jan 30, 2013 11:42 pm

@Paeryn - I'm sure I tried that method first, but i had no joy with it.

@Cromarty - Again, I'm sure i also tried that and had no joy with it.

Ill try both tomorrow and if they work then ill save 4/5 lines of code, but it works fine as it is just now and if any improvement can be used, then I'll make it :)

Thanks guys!

Another question for you then!
I'm trying to get it all working using a shift register. Any ideas on how to output it to a shift register?

Return to “C/C++”