1 /*
2 * Copyright (C) 2007 Armin Burgmeier <armin@openismus.com>
3 * Copyright (C) 2007 Murray Cumming <murrayc@murrayc.com>
4 * Copyright (C) 2007 - 2011 Vivien Malerba <malerba@gnome-db.org>
5 * Copyright (C) 2010 David King <davidk@openismus.com>
6 *
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; either version 2
10 * of the License, or (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program; if not, write to the Free Software
19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20 */
21 #include <libgda/libgda.h>
22 #include <sql-parser/gda-sql-parser.h>
23 #include <glib/gi18n-lib.h>
24 #include <string.h>
25
26 /* options */
27 gchar *pass = NULL;
28 gchar *user = NULL;
29 gchar *dsn = NULL;
30 gchar *direct = NULL;
31 gchar *prov = NULL;
32
33 static GOptionEntry entries[] = {
34 { "cnc", 'c', 0, G_OPTION_ARG_STRING, &direct, "Direct connection string", NULL},
35 { "provider", 'p', 0, G_OPTION_ARG_STRING, &prov, "Provider name", NULL},
36 { "dsn", 's', 0, G_OPTION_ARG_STRING, &dsn, "Data source", NULL},
37 { "user", 'U', 0, G_OPTION_ARG_STRING, &user, "Username", "username" },
38 { "password", 'P', 0, G_OPTION_ARG_STRING, &pass, "Password", "password" },
39 { NULL, 0, 0, 0, NULL, NULL, NULL }
40 };
41
42 static gboolean clear_blobs (GdaConnection *cnc, GError **error);
43 static gboolean insert_blob (GdaConnection *cnc, gint id, const gchar *data, glong binary_length, GError **error);
44 static gboolean update_blob (GdaConnection *cnc, gint id, const gchar *data, glong binary_length, GError **error);
45 static gboolean update_multiple_blobs (GdaConnection *cnc, const gchar *data, glong binary_length, GError **error);
46 static gboolean display_blobs (GdaConnection *cnc, GError **error);
47 static gboolean exec_statement (GdaConnection *cnc, GdaStatement *stmt, GdaSet *plist, GError **error);
48
49 GdaSqlParser *parser;
50
51 int
main(int argc,char ** argv)52 main (int argc, char **argv)
53 {
54 GError *error = NULL;
55 GOptionContext *context;
56
57 GdaConnection *cnc;
58 gchar *auth_string = NULL;
59 gchar *blob_data;
60
61 /* command line parsing */
62 context = g_option_context_new ("Tests opening a connection");
63 g_option_context_add_main_entries (context, entries, GETTEXT_PACKAGE);
64 if (!g_option_context_parse (context, &argc, &argv, &error)) {
65 g_warning ("Can't parse arguments: %s", error->message);
66 exit (1);
67 }
68 g_option_context_free (context);
69
70 if (direct && dsn) {
71 g_print ("DSN and connection string are exclusive\n");
72 exit (1);
73 }
74
75 if (!direct && !dsn) {
76 g_print ("You must specify a connection to open either as a DSN or a connection string\n");
77 exit (1);
78 }
79
80 if (direct && !prov) {
81 g_print ("You must specify a provider when using a connection string\n");
82 exit (1);
83 }
84
85 gda_init ();
86
87 /* open connection */
88 if (user) {
89 if (pass)
90 auth_string = g_strdup_printf ("USERNAME=%s;PASSWORD=%s", user, pass);
91 else
92 auth_string = g_strdup_printf ("USERNAME=%s", user);
93 }
94 if (dsn) {
95 GdaDsnInfo *info = NULL;
96 info = gda_config_get_dsn_info (dsn);
97 if (!info)
98 g_error (_("DSN '%s' is not declared"), dsn);
99 else {
100 cnc = gda_connection_open_from_dsn (info->name, auth_string ? auth_string : info->auth_string,
101 0, &error);
102 if (!cnc) {
103 g_warning (_("Can't open connection to DSN %s: %s\n"), info->name,
104 error && error->message ? error->message : "???");
105 exit (1);
106 }
107 prov = info->provider;
108 }
109 }
110 else {
111
112 cnc = gda_connection_open_from_string (prov, direct, auth_string, 0, &error);
113 if (!cnc) {
114 g_warning (_("Can't open specified connection: %s\n"),
115 error && error->message ? error->message : "???");
116 exit (1);
117 }
118 }
119 g_free (auth_string);
120
121 g_print (_("Connection successfully opened!\n"));
122
123 parser = gda_connection_create_parser (cnc);
124 gda_connection_begin_transaction (cnc, NULL, GDA_TRANSACTION_ISOLATION_UNKNOWN, NULL);
125
126 /*
127 * clear all blobs
128 */
129 if (!clear_blobs (cnc, &error))
130 g_error ("Blobs clear error: %s", error && error->message ? error->message : "No detail");
131
132 /* insert a blob */
133 blob_data = "Blob Data 1";
134 if (!insert_blob (cnc, 1, blob_data, strlen (blob_data), &error))
135 g_error ("Blob insert error: %s", error && error->message ? error->message : "No detail");
136 else if (error) {
137 g_print ("Msg: %s\n", error->message);
138 g_error_free (error);
139 error = NULL;
140 }
141
142 /* insert a blob */
143 blob_data = "Blob Data 2";
144 if (!insert_blob (cnc, 2, blob_data, strlen (blob_data), &error))
145 g_error ("Blob insert error: %s", error && error->message ? error->message : "No detail");
146 else if (error) {
147 g_print ("Msg: %s\n", error->message);
148 g_error_free (error);
149 error = NULL;
150 }
151 if (!display_blobs (cnc, &error))
152 g_error ("Blobs display error: %s", error && error->message ? error->message : "No detail");
153
154
155 /* update blob */
156 blob_data = "New blob 1 contents is now this one...";
157 if (!update_blob (cnc, 1, blob_data, strlen (blob_data), &error))
158 g_error ("Blob update error: %s", error && error->message ? error->message : "No detail");
159 else if (error) {
160 g_print ("Msg: %s\n", error->message);
161 g_error_free (error);
162 error = NULL;
163 }
164 if (!display_blobs (cnc, &error))
165 g_error ("Blobs display error: %s", error && error->message ? error->message : "No detail");
166
167 /* update blob */
168 blob_data = "After several blobs updated";
169 if (!update_multiple_blobs (cnc, blob_data, strlen (blob_data), &error))
170 g_error ("Multiple blob update error: %s", error && error->message ? error->message : "No detail");
171 else if (error) {
172 g_print ("Msg: %s\n", error->message);
173 g_error_free (error);
174 error = NULL;
175 }
176 if (!display_blobs (cnc, &error))
177 g_error ("Blobs display error: %s", error && error->message ? error->message : "No detail");
178
179
180 /* SQL Postgres:
181 create table blobs (id serial not null primary key, name varchar (50), data oid);
182 SQL Oracle:
183 CREATE TABLE blobs (id number primary key, name varchar2 (50), data BLOB);
184 */
185
186 gda_connection_commit_transaction (cnc, NULL, NULL);
187 gda_connection_close (cnc);
188
189 return 0;
190 }
191
192 static void
show_header(const gchar * str)193 show_header (const gchar *str)
194 {
195 g_print ("\n\n******** %s ********\n", str);
196 }
197
198 static gboolean
clear_blobs(GdaConnection * cnc,GError ** error)199 clear_blobs (GdaConnection *cnc, GError **error)
200 {
201 GdaStatement *stmt;
202 gboolean retval;
203 #define SQL_DELETE "DELETE FROM blobs"
204 show_header ("Clear blobs");
205 stmt = gda_sql_parser_parse_string (parser, SQL_DELETE, NULL, error);
206 if (!stmt)
207 return FALSE;
208
209 retval = exec_statement (cnc, stmt, NULL, error);
210 g_object_unref (stmt);
211
212 return retval;
213 }
214
215 static gboolean
insert_blob(GdaConnection * cnc,gint id,const gchar * data,glong binary_length,GError ** error)216 insert_blob (GdaConnection *cnc, gint id, const gchar *data, glong binary_length, GError **error)
217 {
218 GdaStatement *stmt;
219 GdaSet *plist;
220 GdaHolder *param;
221 GValue *value;
222 gchar *str;
223 gboolean retval;
224 #define SQL_INSERT "INSERT INTO blobs (id, name, data) VALUES (##/*name:'id' type:gint*/, ##/*name:'name' type:gchararray*/, ##/*name:'theblob' type:'GdaBlob'*/)"
225
226 show_header ("Insert a blob");
227 stmt = gda_sql_parser_parse_string (parser, SQL_INSERT, NULL, error);
228 if (!stmt)
229 return FALSE;
230 if (!gda_statement_get_parameters (stmt, &plist, NULL))
231 return FALSE;
232
233 /* blob id */
234 param = gda_set_get_holder (plist, "id");
235 str = g_strdup_printf ("%d", id);
236 if (! gda_holder_set_value_str (param, NULL, str, error))
237 return FALSE;
238 g_free (str);
239
240 /* blob name */
241 param = gda_set_get_holder (plist, "name");
242 str = g_strdup_printf ("BLOB_%d", id);
243 if (! gda_holder_set_value_str (param, NULL, str, error))
244 return FALSE;
245 g_free (str);
246
247 /* blob data */
248 param = gda_set_get_holder (plist, "theblob");
249 value = gda_value_new_blob ((guchar*) data, binary_length);
250 if (! gda_holder_set_value (param, value, error))
251 return FALSE;
252 gda_value_free (value);
253
254 gda_connection_clear_events_list (cnc);
255 retval = exec_statement (cnc, stmt, plist, error);
256 g_object_unref (stmt);
257 g_object_unref (plist);
258
259 return retval;
260 }
261
262 static gboolean
update_blob(GdaConnection * cnc,gint id,const gchar * data,glong binary_length,GError ** error)263 update_blob (GdaConnection *cnc, gint id, const gchar *data, glong binary_length, GError **error)
264 {
265 GdaStatement *stmt;
266 GdaSet *plist;
267 GdaHolder *param;
268 GValue *value;
269 gchar *str;
270 gboolean retval;
271 const gchar* SQL_UPDATE = "UPDATE blobs set name = ##/*name:'name' type:gchararray*/, data = ##/*name:'theblob' type:'GdaBlob'*/ WHERE id= ##/*name:'id' type:gint*/";
272
273 show_header ("Update a blob");
274 stmt = gda_sql_parser_parse_string (parser, SQL_UPDATE, NULL, error);
275 if (!stmt)
276 return FALSE;
277 if (!gda_statement_get_parameters (stmt, &plist, NULL))
278 return FALSE;
279
280 /* blob id */
281 param = gda_set_get_holder (plist, "id");
282 str = g_strdup_printf ("%d", id);
283 if (! gda_holder_set_value_str (param, NULL, str, error))
284 return FALSE;
285 g_free (str);
286
287 /* blob name */
288 param = gda_set_get_holder (plist, "name");
289 str = g_strdup_printf ("BLOB_%d", id);
290 if (! gda_holder_set_value_str (param, NULL, str, error))
291 return FALSE;
292 g_free (str);
293
294 /* blob data */
295 param = gda_set_get_holder (plist, "theblob");
296 value = gda_value_new_blob ((guchar*) data, binary_length);
297 if (! gda_holder_set_value (param, value, error))
298 return FALSE;
299 gda_value_free (value);
300
301 gda_connection_clear_events_list (cnc);
302 retval = exec_statement (cnc, stmt, plist, error);
303 g_object_unref (stmt);
304 g_object_unref (plist);
305
306 return retval;
307 }
308
309 static gboolean
update_multiple_blobs(GdaConnection * cnc,const gchar * data,glong binary_length,GError ** error)310 update_multiple_blobs (GdaConnection *cnc, const gchar *data, glong binary_length, GError **error)
311 {
312 GdaStatement *stmt;
313 GdaSet *plist;
314 GdaHolder *param;
315 GValue *value;
316 gboolean retval;
317 const gchar* SQL_UPDATE = "UPDATE blobs set name = ##/*name:'name' type:gchararray*/, data = ##/*name:'theblob' type:'GdaBlob'*/";
318
319 show_header ("Update several blobs at once");
320 stmt = gda_sql_parser_parse_string (parser, SQL_UPDATE, NULL, error);
321 if (!stmt)
322 return FALSE;
323 if (!gda_statement_get_parameters (stmt, &plist, NULL))
324 return FALSE;
325
326 /* blob name */
327 param = gda_set_get_holder (plist, "name");
328 if (! gda_holder_set_value_str (param, NULL, "---", error))
329 return FALSE;
330
331 /* blob data */
332 param = gda_set_get_holder (plist, "theblob");
333 value = gda_value_new_blob ((guchar*) data, binary_length);
334 if (! gda_holder_set_value (param, value, error))
335 return FALSE;
336 gda_value_free (value);
337
338 gda_connection_clear_events_list (cnc);
339 retval = exec_statement (cnc, stmt, plist, error);
340 g_object_unref (stmt);
341 g_object_unref (plist);
342
343 return retval;
344 }
345
346 static gboolean
exec_statement(GdaConnection * cnc,GdaStatement * stmt,GdaSet * plist,GError ** error)347 exec_statement (GdaConnection *cnc, GdaStatement *stmt, GdaSet *plist, GError **error)
348 {
349 GObject *exec_res;
350 exec_res = gda_connection_statement_execute (cnc, stmt, plist, GDA_STATEMENT_MODEL_RANDOM_ACCESS, NULL, error);
351 if (!exec_res)
352 return FALSE;
353
354 if (GDA_IS_DATA_MODEL (exec_res)) {
355 g_print ("Query returned a GdaDataModel...\n");
356 gda_data_model_dump ((GdaDataModel*) exec_res, stdout);
357 }
358 else {
359 if (GDA_IS_SET (exec_res)) {
360 GSList *list;
361
362 g_print ("Query returned a GdaSet:\n");
363 for (list = GDA_SET (exec_res)->holders; list; list = list->next) {
364 gchar *str;
365 str = gda_holder_get_value_str (GDA_HOLDER (list->data), NULL);
366 g_print (" %s => %s\n", gda_holder_get_id (GDA_HOLDER (list->data)), str);
367 g_free (str);
368 }
369
370 }
371 else
372 g_print ("Query returned a %s object\n", G_OBJECT_TYPE_NAME (exec_res));
373 }
374
375 return TRUE;
376 }
377
378 static gboolean
display_blobs(GdaConnection * cnc,GError ** error)379 display_blobs (GdaConnection *cnc, GError **error)
380 {
381 GdaStatement *stmt;
382 gboolean retval;
383 GdaDataModel *model;
384
385 stmt = gda_sql_parser_parse_string (parser, "SELECT * FROM blobs", NULL, error);
386 if (!stmt)
387 return FALSE;
388
389 model = gda_connection_statement_execute_select (cnc, stmt, NULL, error);
390
391 retval = model ? TRUE : FALSE;
392 if (model) {
393 gda_data_model_dump (model, stdout);
394 g_object_unref (model);
395 }
396 return retval;
397 }
398