· 8 years ago · Aug 05, 2018, 10:22 AM
1#include <libgda/libgda.h>
2#include <sql-parser/gda-sql-parser.h>
3
4GdaConnection *open_connection (void);
5void display_products_contents (GdaConnection *cnc);
6void create_table (GdaConnection *cnc);
7void insert_data (GdaConnection *cnc);
8void update_data (GdaConnection *cnc);
9void delete_data (GdaConnection *cnc);
10void run_sql_non_select (GdaConnection *cnc, const gchar *sql);
11
12
13int
14main (int argc, char *argv[])
15{
16 gda_init ();
17 GdaConnection *cnc;
18 /* open connections */
19 cnc = open_connection ();
20 create_table (cnc);
21 insert_data (cnc);
22 display_products_contents (cnc);
23 update_data (cnc);
24 display_products_contents (cnc);
25 delete_data (cnc);
26 display_products_contents (cnc);
27 gda_connection_close (cnc);
28 return 0;
29}
30
31
32/*!
33 * \brief Open a connection to the example.db file.
34 */
35GdaConnection *
36open_connection ()
37{
38 GdaConnection *cnc;
39 GError *error = NULL;
40 GdaSqlParser *parser;
41 /* open connection */
42 cnc = gda_connection_open_from_string
43 (
44 "SQLite",
45 "DB_DIR=.;DB_NAME=example_db",
46 NULL,
47 GDA_CONNECTION_OPTIONS_NONE,
48 &error
49 );
50 if (!cnc)
51 {
52 g_print ("Could not open connection to SQLite database in example_db.db file: %s\n",
53 error && error->message ? error->message : "No detail");
54 exit (1);
55 }
56 /* create an SQL parser */
57 parser = gda_connection_create_parser (cnc);
58 if (!parser) /* @cnc doe snot provide its own parser => use default one */
59 {
60 parser = gda_sql_parser_new ();
61 }
62 /* attach the parser object to the connection */
63 g_object_set_data_full
64 (
65 G_OBJECT (cnc),
66 "parser",
67 parser,
68 g_object_unref
69 );
70 return cnc;
71}
72
73
74/*!
75 * \brief Create a "products" table.
76 */
77void
78create_table (GdaConnection *cnc)
79{
80 run_sql_non_select (cnc, "DROP table IF EXISTS products");
81 run_sql_non_select (cnc, "CREATE table products (ref string not null primary key, "
82 "name string not null, price real)");
83}
84
85
86/*!
87 * \brief Insert some data.
88 *
89 * Even though it is possible to use SQL text which includes the values to insert into the
90 * table, it's better to use variables (place holders), or as is done here, convenience functions
91 * to avoid SQL injection problems.
92 */
93void
94insert_data (GdaConnection *cnc)
95{
96 typedef struct
97 {
98 gchar *ref;
99 gchar *name;
100 gboolean price_is_null;
101 gfloat price;
102 } RowData;
103
104 RowData data [] =
105 {
106 {"p1", "chair", FALSE, 2.0},
107 {"p2", "table", FALSE, 5.0},
108 {"p3", "glass", FALSE, 1.1},
109 {"p1000", "???", TRUE, 0.},
110 {"p1001", "???", TRUE, 0.},
111 };
112 gint i;
113 gboolean res;
114 GError *error = NULL;
115 GValue *v1;
116 GValue *v2;
117 GValue *v3;
118
119 for (i = 0; i < sizeof (data) / sizeof (RowData); i++)
120 {
121 v1 = gda_value_new_from_string (data[i].ref, G_TYPE_STRING);
122 v2 = gda_value_new_from_string (data[i].name, G_TYPE_STRING);
123 if (data[i].price_is_null)
124 {
125 v3 = NULL;
126 }
127 else
128 {
129 v3 = gda_value_new (G_TYPE_FLOAT);
130 g_value_set_float (v3, data[i].price);
131 }
132 res = gda_insert_row_into_table
133 (
134 cnc,
135 "products",
136 &error,
137 "ref",
138 v1,
139 "name",
140 v2,
141 "price",
142 v3,
143 NULL
144 );
145 if (!res)
146 {
147 g_error ("Could not INSERT data into the 'products' table: %s\n",
148 error && error->message ? error->message : "No detail");
149 }
150 gda_value_free (v1);
151 gda_value_free (v2);
152 if (v3)
153 {
154 gda_value_free (v3);
155 }
156 }
157}
158
159
160/*!
161 * \brief Update some data.
162 */
163void
164update_data (GdaConnection *cnc)
165{
166 gboolean res;
167 GError *error = NULL;
168 GValue *v1;
169 GValue *v2;
170 GValue *v3;
171
172 /* update data where ref is 'p1000' */
173 v1 = gda_value_new_from_string ("p1000", G_TYPE_STRING);
174 v2 = gda_value_new_from_string ("flowers", G_TYPE_STRING);
175 v3 = gda_value_new (G_TYPE_FLOAT);
176 g_value_set_float (v3, 1.99);
177
178 res = gda_update_row_in_table
179 (
180 cnc,
181 "products",
182 "ref",
183 v1,
184 &error,
185 "name",
186 v2,
187 "price",
188 v3,
189 NULL
190 );
191 if (!res)
192 {
193 g_error ("Could not UPDATE data in the 'products' table: %s\n",
194 error && error->message ? error->message : "No detail");
195 }
196 gda_value_free (v1);
197 gda_value_free (v2);
198 gda_value_free (v3);
199}
200
201
202/*!
203 * \brief Delete some data.
204 */
205void
206delete_data (GdaConnection *cnc)
207{
208 gboolean res;
209 GError *error = NULL;
210 GValue *v;
211
212 /* delete data where name is 'table' */
213 v = gda_value_new_from_string ("table", G_TYPE_STRING);
214 res = gda_delete_row_from_table (cnc, "products", "name", v, &error);
215 if (!res)
216 {
217 g_error ("Could not DELETE data from the 'products' table: %s\n",
218 error && error->message ? error->message : "No detail");
219 }
220 gda_value_free (v);
221 /* delete data where price is NULL */
222 res = gda_delete_row_from_table (cnc, "products", "price", NULL, &error);
223 if (!res)
224 {
225 g_error ("Could not DELETE data from the 'products' table: %s\n",
226 error && error->message ? error->message : "No detail");
227 }
228}
229
230
231/*!
232 * \brief Display the contents of the 'products' table.
233 */
234void
235display_products_contents (GdaConnection *cnc)
236{
237 GdaDataModel *data_model;
238 GdaSqlParser *parser;
239 GdaStatement *stmt;
240 gchar *sql = "SELECT ref, name, price FROM products";
241 GError *error = NULL;
242
243 parser = g_object_get_data (G_OBJECT (cnc), "parser");
244 stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
245 data_model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
246 g_object_unref (stmt);
247 if (!data_model)
248 {
249 g_error ("Could not get the contents of the 'products' table: %s\n",
250 error && error->message ? error->message : "No detail");
251 }
252 gda_data_model_dump (data_model, stdout);
253 g_object_unref (data_model);
254}
255
256/*!
257 * \brief Run a non SELECT command and stops if an error occurs.
258 */
259void
260run_sql_non_select (GdaConnection *cnc, const gchar *sql)
261{
262 GdaStatement *stmt;
263 GError *error = NULL;
264 gint nrows;
265 const gchar *remain;
266 GdaSqlParser *parser;
267
268 parser = g_object_get_data (G_OBJECT (cnc), "parser");
269 stmt = gda_sql_parser_parse_string (parser, sql, &remain, &error);
270 if (remain)
271 {
272 g_print ("REMAINS: %s\n", remain);
273 }
274 nrows = gda_connection_statement_execute_non_select (cnc, stmt, NULL, NULL, &error);
275 if (nrows == -1)
276 {
277 g_error ("NON SELECT error: %s\n", error && error->message ? error->message : "no detail");
278 }
279 g_object_unref (stmt);
280}
281
282
283/* EOF */