Re: [gnome-db] INSERT statement with binary data

[Vivien Malerba <vmalerba gmail com>]

2009/2/6 Murray Cumming <murrayc murrayc com>

On Fri, 2009-01-30 at 16:01 +0100, Vivien Malerba wrote:

[snip]

> Another (more efficient) solution is to directly create your own
> GdaSqlStatement parts and use them to build a complete GdaSqlStatement
> from which a GdaStatement object can be created and executed:
> basically you can manipulate GdaSqlStatement parts instead of string
> parts.
>
> So for example instead of generating SQL portions like "WHERE
> id=##theid::int", you can generate the corresponding GdaSqlStatement
> part to avoid the parsing, which you can combine with another part
> (here it would be the GdaSqlStatement part for example for "SELECT a,
> b, c FROM mytable").

[snip]

So, a "part" is a GdaSqlStatement, right?
http://library.gnome.org/devel/libgda/unstable/libgda-40-GdaSqlStatement.html

No, the GdaSqlStatement represents a complete statement, which is composed of "parts" which are GdaSqlAnyPart structures, specialized for each type of part (GdaSqlExpr "inherits" GdaSqlAnyPart).
 

So, how would I create one for "WHERE id=1".

The "id=1" condition is represented by a GdaSqlOperation part having 2 operands (in the GdaSqlOperation->operands list) which are:
* a GdaSqlExpr for the "id" (GdaSqlExpr->value is a string GValue containing "id")
* a GdaSqlExpr for the "1" (GdaSqlExpr->value is a string GValue containing "1")

Each GdaSqlExpr has a its parent attribute (this attribute comes from the GdaSqlAnyPart inheritance) pointing to the GdaSqlOperation.

As the "id=1" condition is used in a SELECT, DELETE or UPDATE statement's WHERE clause, the GdaSqlOperation is pointed by the GdaSqlStatementSelect::where_cond _expression_ (and similar for the DELETE or UPDATE).

Using the API, this would be expressed as:
GdaSqlStatement *sql_sel_stmt;
GdaSqlStatementSelect *sel;

sql_sel_stm = gda_sql_statement_new (GDA_SQL_STATEMENT_SELECT);
sel = (GdaSqlStatementSelect *) sql_sel_stm->contents;

GdaSqlExpr *where, *op;
where = gda_sql_expr_new (GDA_SQL_ANY_PART (sel));
sel->where_cond = where;

where->cond = gda_sql_operation_new (GDA_SQL_ANY_PART (where));
where->cond->operator_type = GDA_SQL_OPERATOR_TYPE_EQ;

op = gda_sql_expr_new (GDA_SQL_ANY_PART (where->cond));
where->cond->operands = g_slist_prepend (NULL, op);
op->value = gda_value_new (G_TYPE_STRING);
g_value_set_string (op->value, "id");

op = gda_sql_expr_new (GDA_SQL_ANY_PART (where->cond));
where->cond->operands = g_slist_prepend (where->cond->operands , op);
op->value = gda_value_new (G_TYPE_STRING);
g_value_set_string (op->value, "1");

gda_sql_statement_new() seems to require me to choose whether it's a
select, update, or whatever. That's not so bad, though it seem odd if
it's for just a part. I can't see any GdaSqlSelect* that is for a
where_clause.

It would be nice if these objects used GObject. That would make their
memory management simpler.

This API was designed to be memory efficient, fast and used mainly for database provider's implementations or Libgda's internals. It was never defined to be an easy API which is the reason GObjects are not used.

As part of the next evolutions of Libgda, there will be a user friendly API to build statements which I hope can be similar to the SQL Construction Kit (http://code.google.com/p/sqlck/) or JRel (http://www.thimbleware.com/projects/jrel).
 
Vivien