1 /* 2 * Copyright (C) 2007 - 2011 Vivien Malerba <malerba@gnome-db.org> 3 * 4 * This program is free software; you can redistribute it and/or 5 * modify it under the terms of the GNU General Public License 6 * as published by the Free Software Foundation; either version 2 7 * of the License, or (at your option) any later version. 8 * 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * 14 * You should have received a copy of the GNU General Public License 15 * along with this program; if not, write to the Free Software 16 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 17 */ 18 #include <libgda/libgda.h> 19 #include <sql-parser/gda-sql-parser.h> 20 21 GdaConnection *open_connection (void); 22 void display_products_contents (GdaConnection *cnc); 23 void create_table (GdaConnection *cnc); 24 void insert_data (GdaConnection *cnc); 25 void update_data (GdaConnection *cnc); 26 void delete_data (GdaConnection *cnc); 27 28 void run_sql_non_select (GdaConnection *cnc, const gchar *sql); 29 30 int 31 main (int argc, char *argv[]) 32 { 33 gda_init (); 34 35 GdaConnection *cnc; 36 37 /* open connections */ 38 cnc = open_connection (); 39 create_table (cnc); 40 41 insert_data (cnc); 42 display_products_contents (cnc); 43 44 update_data (cnc); 45 display_products_contents (cnc); 46 47 delete_data (cnc); 48 display_products_contents (cnc); 49 50 gda_connection_close (cnc); 51 g_object_unref (cnc); 52 53 return 0; 54 } 55 56 /* 57 * Open a connection to the example.db file 58 */ 59 GdaConnection * 60 open_connection () 61 { 62 GdaConnection *cnc; 63 GError *error = NULL; 64 GdaSqlParser *parser; 65 66 /* open connection */ 67 cnc = gda_connection_open_from_string ("SQLite", "DB_DIR=.;DB_NAME=example_db", NULL, 68 GDA_CONNECTION_OPTIONS_NONE, 69 &error); 70 if (!cnc) { 71 g_print ("Could not open connection to SQLite database in example_db.db file: %s\n", 72 error && error->message ? error->message : "No detail"); 73 exit (1); 74 } 75 76 /* create an SQL parser */ 77 parser = gda_connection_create_parser (cnc); 78 if (!parser) /* @cnc does not provide its own parser => use default one */ 79 parser = gda_sql_parser_new (); 80 /* attach the parser object to the connection */ 81 g_object_set_data_full (G_OBJECT (cnc), "parser", parser, g_object_unref); 82 83 return cnc; 84 } 85 86 /* 87 * Create a "products" table 88 */ 89 void 90 create_table (GdaConnection *cnc) 91 { 92 run_sql_non_select (cnc, "DROP table IF EXISTS products"); 93 run_sql_non_select (cnc, "CREATE table products (ref string not null primary key, " 94 "name string not null, price real)"); 95 } 96 97 /* 98 * Insert some data 99 * 100 * Even though it is possible to use SQL text which includes the values to insert into the 101 * table, it's better to use variables (place holders), or as is done here, convenience functions 102 * to avoid SQL injection problems. 103 */ 104 void 105 insert_data (GdaConnection *cnc) 106 { 107 typedef struct { 108 gchar *ref; 109 gchar *name; 110 111 gboolean price_is_null; 112 gfloat price; 113 } RowData; 114 RowData data [] = { 115 {"p1", "chair", FALSE, 2.0}, 116 {"p2", "table", FALSE, 5.0}, 117 {"p3", "glass", FALSE, 1.1}, 118 {"p1000", "???", TRUE, 0.}, 119 {"p1001", "???", TRUE, 0.}, 120 }; 121 gint i; 122 123 gboolean res; 124 GError *error = NULL; 125 GValue *v1, *v2, *v3; 126 127 for (i = 0; i < sizeof (data) / sizeof (RowData); i++) { 128 v1 = gda_value_new_from_string (data[i].ref, G_TYPE_STRING); 129 v2 = gda_value_new_from_string (data[i].name, G_TYPE_STRING); 130 if (data[i].price_is_null) 131 v3 = NULL; 132 else { 133 v3 = gda_value_new (G_TYPE_FLOAT); 134 g_value_set_float (v3, data[i].price); 135 } 136 137 res = gda_connection_insert_row_into_table (cnc, "products", &error, "ref", v1, "name", v2, "price", v3, NULL); 138 139 if (!res) { 140 g_error ("Could not INSERT data into the 'products' table: %s\n", 141 error && error->message ? error->message : "No detail"); 142 } 143 gda_value_free (v1); 144 gda_value_free (v2); 145 if (v3) 146 gda_value_free (v3); 147 } 148 } 149 150 /* 151 * Update some data 152 */ 153 void 154 update_data (GdaConnection *cnc) 155 { 156 gboolean res; 157 GError *error = NULL; 158 GValue *v1, *v2, *v3; 159 160 /* update data where ref is 'p1000' */ 161 v1 = gda_value_new_from_string ("p1000", G_TYPE_STRING); 162 v2 = gda_value_new_from_string ("flowers", G_TYPE_STRING); 163 v3 = gda_value_new (G_TYPE_FLOAT); 164 g_value_set_float (v3, 1.99); 165 166 res = gda_connection_update_row_in_table (cnc, "products", "ref", v1, &error, "name", v2, "price", v3, NULL); 167 168 if (!res) { 169 g_error ("Could not UPDATE data in the 'products' table: %s\n", 170 error && error->message ? error->message : "No detail"); 171 } 172 gda_value_free (v1); 173 gda_value_free (v2); 174 gda_value_free (v3); 175 } 176 177 /* 178 * Delete some data 179 */ 180 void 181 delete_data (GdaConnection *cnc) 182 { 183 gboolean res; 184 GError *error = NULL; 185 GValue *v; 186 187 /* delete data where name is 'table' */ 188 v = gda_value_new_from_string ("table", G_TYPE_STRING); 189 res = gda_connection_delete_row_from_table (cnc, "products", "name", v, &error); 190 if (!res) { 191 g_error ("Could not DELETE data from the 'products' table: %s\n", 192 error && error->message ? error->message : "No detail"); 193 } 194 gda_value_free (v); 195 196 /* delete data where price is NULL */ 197 res = gda_connection_delete_row_from_table (cnc, "products", "price", NULL, &error); 198 if (!res) { 199 g_error ("Could not DELETE data from the 'products' table: %s\n", 200 error && error->message ? error->message : "No detail"); 201 } 202 } 203 204 /* 205 * display the contents of the 'products' table 206 */ 207 void 208 display_products_contents (GdaConnection *cnc) 209 { 210 GdaDataModel *data_model; 211 GdaSqlParser *parser; 212 GdaStatement *stmt; 213 gchar *sql = "SELECT ref, name, price FROM products"; 214 GError *error = NULL; 215 216 parser = g_object_get_data (G_OBJECT (cnc), "parser"); 217 stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL); 218 data_model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error); 219 g_object_unref (stmt); 220 if (!data_model) 221 g_error ("Could not get the contents of the 'products' table: %s\n", 222 error && error->message ? error->message : "No detail"); 223 gda_data_model_dump (data_model, stdout); 224 g_object_unref (data_model); 225 } 226 227 /* 228 * run a non SELECT command and stops if an error occurs 229 */ 230 void 231 run_sql_non_select (GdaConnection *cnc, const gchar *sql) 232 { 233 GdaStatement *stmt; 234 GError *error = NULL; 235 gint nrows; 236 const gchar *remain; 237 GdaSqlParser *parser; 238 239 parser = g_object_get_data (G_OBJECT (cnc), "parser"); 240 stmt = gda_sql_parser_parse_string (parser, sql, &remain, &error); 241 if (remain) 242 g_print ("REMAINS: %s\n", remain); 243 244 nrows = gda_connection_statement_execute_non_select (cnc, stmt, NULL, NULL, &error); 245 if (nrows == -1) 246 g_error ("NON SELECT error: %s\n", error && error->message ? error->message : "no detail"); 247 g_object_unref (stmt); 248 } 249