Build statements without using a parser: GNOME Data Access 5 manual

Build statements without using a parser

Statements can be created using a GdaSqlParser object to parse SQL strings, but an easier way is to use a GdaSqlBuilder object and the associated APIs to construct the statement. This section gives examples to create various statements. Please note that only the DML statements (SELECT, INSERT, UPDATE or DELETE statements can be built using a GdaSqlBuilder object, other types of statements can only be built using a parser).

Each of the examples in this section show the statement construction part, the usage part is not shown for clarity reasons (replaced with [...]). Typically one would use the gda_sql_builder_get_statement() method to actually obtain a GdaStatement object and execute it.

INSERT INTO customers (e, f, g) VALUES (##p1::string, 15, 'joe')

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b,
                                    gda_sql_builder_add_id (b, "e"),
                                    gda_sql_builder_add_param (b, "p1", G_TYPE_STRING, FALSE));
gda_sql_builder_add_field_value (b, "f", G_TYPE_INT, 15);
gda_sql_builder_add_field_value (b, "g", G_TYPE_STRING, "joe")
[...]	
g_object_unref (b);

SELECT people.firstname AS person, people.lastname, "date" AS birthdate, age FROM people

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_field (b, "firstname", "people", "person");
gda_sql_builder_select_add_field (b, "lastname", "people", NULL);
gda_sql_builder_select_add_field (b, "date", NULL, "birthdate");
gda_sql_builder_select_add_field (b, "age", NULL, NULL);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "people"),
				      NULL);
[...]	
g_object_unref (b);

SELECT c."date", name AS person FROM "select" AS c INNER JOIN orders USING (id)

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId id_table = gda_sql_builder_add_id (b, "select"); /* SELECT is an SQL reserved keyword */
GdaSqlBuilderId id_target1 = gda_sql_builder_select_add_target_id (b, id_table, "c");
GdaSqlBuilderId id_target2 = gda_sql_builder_select_add_target_id (b,
                                                                   gda_sql_builder_add_id (b, "orders"),
				                                   NULL);
GdaSqlBuilderId id_join = gda_sql_builder_select_join_targets (b, id_target1, id_target2, GDA_SQL_SELECT_JOIN_INNER, 0);

/* DATE is an SQL reserved keyword */
gda_sql_builder_add_field_value_id (b,
                                    gda_sql_builder_add_id (b, "c.date"), 0);
gda_sql_builder_add_field_value_id (b,
			            gda_sql_builder_add_id (b, "name"),
		  	            gda_sql_builder_add_id (b, "person"));

gda_sql_builder_join_add_field (b, id_join, "id");
[...]	
g_object_unref (b);

UPDATE products set ref='A0E''FESP' WHERE id = 14

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "products");
gda_sql_builder_add_field_value (b, "ref", G_TYPE_STRING, "A0E'FESP");
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 14);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);

DELETE FROM items WHERE id = ##theid::int

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
gda_sql_builder_set_table (b, "items");
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_param = gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_param, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);

SELECT myfunc (a, 5, 'Joe') FROM mytable

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
				   gda_sql_builder_add_id (b, "mytable"),
				   NULL);
GdaSqlBuilderId id_function = gda_sql_builder_add_function (b, "myfunc",
			            gda_sql_builder_add_id (b, "a"),
			            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5),
			            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Joe"),
			            0);
gda_sql_builder_add_field_value_id (b, id_function, 0);
[...]	
g_object_unref (b);

SELECT name FROM master WHERE id IN (SELECT id FROM subdata)

GdaSqlBuilder *b;
GdaSqlStatement *sub;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata"),
				      NULL);
sub = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "master"),
				      NULL);
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_subselect = gda_sql_builder_add_sub_select (b, sub, TRUE);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_IN, id_field, id_subselect, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);

INSERT INTO customers (e, f, g) SELECT id, name, location FROM subdate

GdaSqlBuilder *b;
GdaSqlStatement *sub;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "location"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdate"),
				      NULL);
sub = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "e"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "f"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "g"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_sub_select (b, sub, TRUE), 0);
[...]	
g_object_unref (b);

SELECT id, name FROM subdata1 UNION SELECT ident, lastname FROM subdata2

GdaSqlBuilder *b;
GdaSqlStatement *sub1, *sub2;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata1"),
				      NULL);
sub1 = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "ident"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "lastname"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata2"),
				      NULL);
sub2 = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_COMPOUND);
gda_sql_builder_compound_add_sub_select (b, sub1, TRUE);
gda_sql_builder_compound_add_sub_select (b, sub2, TRUE);
[...]	
g_object_unref (b);

SELECT CASE tag WHEN 'Alpha' THEN 1 WHEN 'Bravo' THEN 2 WHEN 'Charlie' THEN 3 ELSE 0 END FROM data

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId id_case = gda_sql_builder_add_case (b,
			                            gda_sql_builder_add_id (b, "tag"),
			                            0,
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Alpha"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 1),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Bravo"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 2),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Charlie"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 3),
			                            0);
gda_sql_builder_add_field_value_id (b, id_case, 0);
gda_sql_builder_select_add_target_id (b,
				   gda_sql_builder_add_id (b, "data"),
				   NULL);
[...]	
g_object_unref (b);

SELECT product_id, name, sum (4 * 5 * price * 1.200000) FROM invoice_lines

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_field (b, "product_id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", NULL, NULL);

GdaSqlBuilderId op_ids[4], id_function;
op_ids[0] = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 4);
op_ids[1] = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5);
op_ids[2] = gda_sql_builder_add_id (b, "price");
op_ids[3] = gda_sql_builder_add_expr (b, NULL, G_TYPE_FLOAT, 1.2);
id_function = gda_sql_builder_add_function (b, "sum",
                                   gda_sql_builder_add_cond_v (b, GDA_SQL_OPERATOR_TYPE_STAR, op_ids, 4),
                                   0);
gda_sql_builder_add_field_value_id (b, id_function, 0);
gda_sql_builder_select_add_target_id (b,
                                      gda_sql_builder_add_id (b, "invoice_lines"),
				      NULL);
[...]
g_object_unref (b);

SELECT id, name, adress, cntry_id, countries.name FROM customers INNER JOIN countries ON (countries.id = cntry_id)

GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId t1, t2;
GdaSqlBuilderId id1, id2;
GdaSqlBuilderId jid;
t1 = gda_sql_builder_select_add_target_id (b,
                                           gda_sql_builder_add_id (b, "customers"),
                                           NULL);
t2 = gda_sql_builder_select_add_target_id (b,
                                           gda_sql_builder_add_id (b, "countries"),
                                           NULL);
gda_sql_builder_select_add_field (b, "id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", NULL, NULL);
gda_sql_builder_select_add_field (b, "adress", NULL, NULL);
id1 = gda_sql_builder_select_add_field (b, "cntry_id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", "countries", NULL);
id2 = gda_sql_builder_add_field_id (b, "id", "countries");
jid = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id2, id1, 0);
gda_sql_builder_select_join_targets (b, t1, t2, GDA_SQL_SELECT_JOIN_INNER, jid);
[...]
g_object_unref (b);