CODEWITHSUNDEEP
mysqlc
Marco Mac
Marco Mac

Reputation: 79

Prepared SELECT statement executes but always returns 0 rows

I have read the documentation many times and i have searched on google and i found a lot of example and the code below is the result of my research. But now i have a big problem and i don't find the solution to fix it. The code below executes the query but doesn't display the result, it returns always 0 row while it have to return 2 rows... i don't understand where is the mistake.

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <mysql/mysql.h>

#define STRING_SIZE 50

#define SELECT_SAMPLE "select p.id_paz from pazienti p where p.id_doc = ?"

int main(void)
{
    MYSQL *conn;
    MYSQL_STMT *stmt;
    MYSQL_BIND    pbind[1],result[1]; /* results */
    unsigned long length;
    int           row_count;
    char          login[STRING_SIZE];
    my_bool       is_null;

    unsigned long plength;
    char *pdata;
    my_bool      p_is_null;

    // Open Database
    const char *server = "localhost";
    char *user = "root";
    char *password = "password"; /* set me first */
    char *database = "ProgettoSi";
    conn = mysql_init(NULL);
    /* Connect to database */
    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        //...
    }

    // Allocate statement handler
    stmt = mysql_stmt_init(conn);

    if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
    {
        //...
    } 
    fprintf(stdout, " prepare, SELECT successful\n");

    plength = STRING_SIZE * sizeof(char);
    p_is_null = 0;
    pdata = (char*)malloc( STRING_SIZE * sizeof(char) );

    /* STRING PARAMETER */
    pbind[0].buffer_type= MYSQL_TYPE_STRING;
    pbind[0].buffer= (char *)pdata;
    pbind[0].buffer_length=STRING_SIZE * sizeof(char);
    pbind[0].is_null= &p_is_null;
    pbind[0].length= &plength;

    if( mysql_stmt_bind_param( stmt, pbind ) ) {
       //...
    }
    mysql_real_escape_string( conn, pdata, "123", strlen("123")*sizeof(char) );
    plength = strlen( pdata ) + 1;

    printf( "Executing query with parameters %s. \n", pdata);
    /* Execute the SELECT query */
    if (mysql_stmt_execute(stmt))
    {
        //...
    }

    /* Bind the result buffers for all 4 columns before fetching them */

    result[0].buffer_type= MYSQL_TYPE_STRING;
    result[0].buffer= (char *)login;
    result[0].buffer_length= STRING_SIZE;
    result[0].is_null= &is_null;
    result[0].length= &length;

    if (mysql_stmt_bind_result(stmt, result))
    {
        //...
    }

    if (mysql_stmt_store_result(stmt))
    {
        //...
    }

    row_count= 0;
    fprintf(stdout, "Fetching results ...\n");
    while (!mysql_stmt_fetch(stmt))
    {
        row_count++;
        fprintf(stdout, "  row %d\n", row_count);

        /* column 2 */
        fprintf(stdout, "   column1 (string)   : ");
        if (is_null)
            fprintf(stdout, " NULL\n");
        else
            fprintf(stdout, " %s(%ld)\n", login, length);
    }

    /* Validate rows fetched */
    fprintf(stdout, " total rows fetched: %d\n", row_count);
    /* Close the statement */
    if (mysql_stmt_close(stmt))
    {
        //...
    }
    return 0;
}

It works if i insert manually the value in the query.

#define SELECT_SAMPLE "select p.id_paz from pazienti p where p.id_doc = 123"

if i execute this query and obviously comment the code in more... it works...

Upvotes: 1

Views: 999

Answers (1)

DevSolar
DevSolar

Reputation: 70253

mysql_stmt_fetch has four possible return values: 0 (success), 1 (error), MYSQL_NO_DATA and MYSQL_DATA_TRUNCATED. Your loop terminates on anything other than success - but you don't check what actually happened. You might have 0 rows returned, or you might have some error condition best checked through mysql_stmt_errno() and mysql_stmt_error()...

Upvotes: 2

Related Questions