## 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);
    guint id_table = gda_sql_builder_add_id (b, "select"); /* SELECT is an SQL reserved keyword */
    guint id_target1 = gda_sql_builder_select_add_target_id (b, id_table, "c");
    guint id_target2 = gda_sql_builder_select_add_target_id (b,
    				   gda_sql_builder_add_id (b, "orders"),
    				   NULL);
    guint id_join = gda_sql_builder_select_join_targets (b, id_target1, id_target2, GDA_SQL_SELECT_JOIN_INNER, 0);

    gda_sql_builder_add_field_value_id (b,
                                  gda_sql_builder_add_id (b, "c.date"), 0); /* DATE is an SQL reserved keyword */
    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");
    guint id_field = gda_sql_builder_add_id (b, "id");
    guint id_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 14);
    guint 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");
    guint id_field = gda_sql_builder_add_id (b, "id");
    guint id_param = gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE);
    guint 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);
    guint 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);
    guint id_field = gda_sql_builder_add_id (b, "id");
    guint id_subselect = gda_sql_builder_add_sub_select (b, sub, TRUE);
    guint 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);
    guint 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);