GDA full example

README

The following example is a complete program which creates a example_db.db SQLite database in the current directory, creates a "products" table,add a few rows to it, and then displays its contents. The example uses an SQLite database as it does not require any server setup and can thus be run as-is. That example can be found in the samples/SimpleExample/example.c. 

To compile it, run:
cc -o example example.c `pkg-config --cflags --libs libgda-4.0`

# EOF #

example.c

#include <libgda/libgda.h>
#include <sql-parser/gda-sql-parser.h>

GdaConnection *open_connection (void);
void display_products_contents (GdaConnection *cnc);
void create_table (GdaConnection *cnc);
void insert_data (GdaConnection *cnc);
void update_data (GdaConnection *cnc);
void delete_data (GdaConnection *cnc);
void run_sql_non_select (GdaConnection *cnc, const gchar *sql);


int
main (int argc, char *argv[])
{
        gda_init ();
        GdaConnection *cnc;
        /* open connections */
        cnc = open_connection ();
        create_table (cnc);
        insert_data (cnc);
        display_products_contents (cnc);
        update_data (cnc);
        display_products_contents (cnc);
        delete_data (cnc);
        display_products_contents (cnc);
        gda_connection_close (cnc);
        return 0;
}


/*!
 * \brief Open a connection to the example.db file.
 */
GdaConnection *
open_connection ()
{
        GdaConnection *cnc;
        GError *error = NULL;
        GdaSqlParser *parser;
        /* open connection */
        cnc = gda_connection_open_from_string
        (
                "SQLite",
                "DB_DIR=.;DB_NAME=example_db",
                NULL,
                GDA_CONNECTION_OPTIONS_NONE,
                &error
        );
        if (!cnc)
        {
                g_print ("Could not open connection to SQLite database in example_db.db file: %s\n",
                         error && error->message ? error->message : "No detail");
                exit (1);
        }
        /* create an SQL parser */
        parser = gda_connection_create_parser (cnc);
        if (!parser) /* @cnc doe snot provide its own parser => use default one */
        {
                parser = gda_sql_parser_new ();
        }
        /* attach the parser object to the connection */
        g_object_set_data_full
        (
                G_OBJECT (cnc),
                "parser",
                parser,
                g_object_unref
        );
        return cnc;
}


/*!
 * \brief Create a "products" table.
 */
void
create_table (GdaConnection *cnc)
{
        run_sql_non_select (cnc, "DROP table IF EXISTS products");
        run_sql_non_select (cnc, "CREATE table products (ref string not null primary key, "
                "name string not null, price real)");
}


/*!
 * \brief Insert some data.
 *
 * Even though it is possible to use SQL text which includes the values to insert into the
 * table, it's better to use variables (place holders), or as is done here, convenience functions
 * to avoid SQL injection problems.
 */
void
insert_data (GdaConnection *cnc)
{
        typedef struct
        {
                gchar *ref;
                gchar *name;
                gboolean price_is_null;
                gfloat price;
        } RowData;

        RowData data [] =
        {
                {"p1", "chair", FALSE, 2.0},
                {"p2", "table", FALSE, 5.0},
                {"p3", "glass", FALSE, 1.1},
                {"p1000", "???", TRUE, 0.},
                {"p1001", "???", TRUE, 0.},
        };
        gint i;
        gboolean res;
        GError *error = NULL;
        GValue *v1;
        GValue *v2;
        GValue *v3;

        for (i = 0; i < sizeof (data) / sizeof (RowData); i++)
        {
                v1 = gda_value_new_from_string (data[i].ref, G_TYPE_STRING);
                v2 = gda_value_new_from_string (data[i].name, G_TYPE_STRING);
                if (data[i].price_is_null)
                {
                        v3 = NULL;
                }
                else
                {
                        v3 = gda_value_new (G_TYPE_FLOAT);
                        g_value_set_float (v3, data[i].price);
                }
                res = gda_insert_row_into_table
                (
                        cnc,
                        "products",
                        &error,
                        "ref",
                        v1,
                        "name",
                        v2,
                        "price",
                        v3,
                        NULL
                );
                if (!res)
                {
                        g_error ("Could not INSERT data into the 'products' table: %s\n",
                                error && error->message ? error->message : "No detail");
                }
                gda_value_free (v1);
                gda_value_free (v2);
                if (v3)
                {
                        gda_value_free (v3);
                }
        }
}


/*!
 * \brief Update some data.
 */
void
update_data (GdaConnection *cnc)
{
        gboolean res;
        GError *error = NULL;
        GValue *v1;
        GValue *v2;
        GValue *v3;

        /* update data where ref is 'p1000' */
        v1 = gda_value_new_from_string ("p1000", G_TYPE_STRING);
        v2 = gda_value_new_from_string ("flowers", G_TYPE_STRING);
        v3 = gda_value_new (G_TYPE_FLOAT);
        g_value_set_float (v3, 1.99);

        res = gda_update_row_in_table
        (
                cnc,
                "products",
                "ref",
                v1,
                &error,
                "name",
                v2,
                "price",
                v3,
                NULL
        );
        if (!res)
        {
                g_error ("Could not UPDATE data in the 'products' table: %s\n",
                        error && error->message ? error->message : "No detail");
        }
        gda_value_free (v1);
        gda_value_free (v2);
        gda_value_free (v3);
}


/*!
 * \brief Delete some data.
 */
void
delete_data (GdaConnection *cnc)
{
        gboolean res;
        GError *error = NULL;
        GValue *v;

        /* delete data where name is 'table' */
        v = gda_value_new_from_string ("table", G_TYPE_STRING);
        res = gda_delete_row_from_table (cnc, "products", "name", v, &error);
        if (!res)
        {
                g_error ("Could not DELETE data from the 'products' table: %s\n",
                        error && error->message ? error->message : "No detail");
        }
        gda_value_free (v);
        /* delete data where price is NULL */
        res = gda_delete_row_from_table (cnc, "products", "price", NULL, &error);
        if (!res)
        {
                g_error ("Could not DELETE data from the 'products' table: %s\n",
                        error && error->message ? error->message : "No detail");
        }
}


/*! 
 * \brief Display the contents of the 'products' table.
 */
void
display_products_contents (GdaConnection *cnc)
{
        GdaDataModel *data_model;
        GdaSqlParser *parser;
        GdaStatement *stmt;
        gchar *sql = "SELECT ref, name, price FROM products";
        GError *error = NULL;

        parser = g_object_get_data (G_OBJECT (cnc), "parser");
        stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
        data_model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
        g_object_unref (stmt);
        if (!data_model)
        {
                g_error ("Could not get the contents of the 'products' table: %s\n",
                         error && error->message ? error->message : "No detail");
        }
        gda_data_model_dump (data_model, stdout);
        g_object_unref (data_model);
}

/*!
 * \brief Run a non SELECT command and stops if an error occurs.
 */
void
run_sql_non_select (GdaConnection *cnc, const gchar *sql)
{
        GdaStatement *stmt;
        GError *error = NULL;
        gint nrows;
        const gchar *remain;
        GdaSqlParser *parser;

        parser = g_object_get_data (G_OBJECT (cnc), "parser");
        stmt = gda_sql_parser_parse_string (parser, sql, &remain, &error);
        if (remain)
        {
                g_print ("REMAINS: %s\n", remain);
        }
        nrows = gda_connection_statement_execute_non_select (cnc, stmt, NULL, NULL, &error);
        if (nrows == -1)
        {
                g_error ("NON SELECT error: %s\n", error && error->message ? error->message : "no detail");
        }
        g_object_unref (stmt);
}


/* EOF */

Makefile

CFLAGS = -Wall -g `pkg-config --cflags libgda-4.0`
LDFLAGS = `pkg-config --libs libgda-4.0`

example: example.c
	$(CC) -o example example.c $(CFLAGS) $(LDFLAGS)
clean:
	rm -f *~
	rm -f *.o
	rm -f example

OUTPUT

> ./example 
ref   | name  | price   
------+-------+---------
p1    | chair | 2.000000
p2    | table | 5.000000
p3    | glass | 1.100000
p1000 | ???   | NULL    
p1001 | ???   | NULL    
(5 rows)
ref   | name    | price   
------+---------+---------
p1    | chair   | 2.000000
p2    | table   | 5.000000
p3    | glass   | 1.100000
p1000 | flowers | 1.990000
p1001 | ???     | NULL    
(5 rows)
ref   | name    | price   
------+---------+---------
p1    | chair   | 2.000000
p3    | glass   | 1.100000
p1000 | flowers | 1.990000
(3 rows)