|
|
|
|
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-3.0`
The code is:
#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;
}
/*
* 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 does not 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;
}
/*
* 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)");
}
/*
* 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, *v2, *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_connection_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);
}
}
/*
* Update some data
*/
void
update_data (GdaConnection *cnc)
{
gboolean res;
GError *error = NULL;
GValue *v1, *v2, *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_connection_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);
}
/*
* 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_connection_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_connection_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");
}
}
/*
* 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);
}
/*
* 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);
}
and executing should output something like:
> ./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)