c libmysql

/*

 mysql> CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 mysql> USE testdb;
 mysql> CREATE TABLE exmpltbl ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_exmpltbl primary key (id) );
 mysql> INSERT INTO exmpltbl ( id, name ) VALUES ( null, 'Simon' );
 mysql> INSERT INTO exmpltbl ( id, name ) VALUES ( null, 'Veronica' );
 mysql> INSERT INTO exmpltbl ( id, name ) VALUES ( null, 'Valeria' );
 mysql> INSERT INTO exmpltbl ( id, name ) VALUES ( null, 'Robert' );
 mysql> INSERT INTO exmpltbl ( id, name ) VALUES ( null, 'Eglė' );

 mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass123';
 mysql> GRANT ALL PRIVILEGES ON testdb . * TO 'testuser'@'localhost';
 mysql> FLUSH PRIVILEGES;

 mysql> quit

 build:

 gcc -Wall -Wextra -Wpedantic -I/usr/include/mysql -c -o main.o main.c
 gcc -o test_mysql main.o -L/usr/lib64/mysql -lmysqlclient

 or:

 gcc -Wall -Wextra -Wpedantic `mysql_config --cflags` -c -o main.o main.c
 gcc -o test_mysql main.o `mysql_config --libs`

 */

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

#define DB_HOST "localhost"
#define USER_NAME "testuser"
#define USER_PASSWORD "pass123"
#define DATA_BASE_NAME "testdb"

MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
unsigned int num_fields;
unsigned int num_rows;
unsigned int i;

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

    if(!mysql_init(&mysql))
    {
        printf("Error initializing MySQL client\n");
        return 1;
    }

    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8mb4");

    if (!mysql_real_connect(&mysql, DB_HOST, USER_NAME, USER_PASSWORD, DATA_BASE_NAME, 0, NULL, 0))
    {
        fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
        mysql_close(&mysql);
        mysql_library_end();
        return 1;
    }

    char queryString[]="SELECT name FROM exmpltbl";

    if (mysql_query(&mysql,queryString))
    {
        fprintf(stderr, "mysql_query failed: Error: %s\n", mysql_error(&mysql));
        mysql_close(&mysql);
        mysql_library_end();
        return 1;
    }

    result = mysql_store_result(&mysql);

    if (result)
    {
        num_fields = mysql_num_fields(result);
        printf("num_fields: %d\n", num_fields);

        num_rows = mysql_affected_rows(&mysql);
        printf("num_rows: %d\n", num_rows);

        while ((row = mysql_fetch_row(result)))
        {
            unsigned long *lengths;
            lengths = mysql_fetch_lengths(result);
            for(i = 0; i < num_fields; i++)
            {
                printf("[%.*s] lenght: %d", (int) lengths[i], row[i] ? row[i] : "NULL", (int) lengths[i]);
            }
            printf("\n");
        }

        mysql_free_result(result);

    }
    else
    {
        if (mysql_errno(&mysql))
        {
            fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
            mysql_close(&mysql);
            mysql_library_end();
            return 1;
        }
        else if (mysql_field_count(&mysql) == 0)
        {
            printf("query does not return data (it was not a SELECT)\n");

            num_rows = mysql_affected_rows(&mysql);
            printf("num_rows: %d\n", num_rows);
        }
    }



    mysql_close(&mysql);
    mysql_library_end();

    return 0;
}

/*
output:

num_fields: 1
num_rows: 5
[Simon] lenght: 5
[Veronica] lenght: 8
[Valeria] lenght: 7
[Robert] lenght: 6
[Eglė] lenght: 5
 */