|
|
|
|
Any query within Libgda is represented as a GdaStatement object. Each GdaStatement object can store exactly one SQL statement (SQL statements are generally separated by semi-colons). Several statements can be grouped into a GdaBatch object.
Libgda can execute any SQL understood by the database to which a connection is opened, even SQL code containing extensions specific to a database.
When creating an SQL string which contains values (literals), one can be tempted (as it is the easiest solution) to create a string containing the values themselves, execute that statement and apply the same process the next time the same statement needs to be executed with different values. This approach has two major flaws outlined below which is why Libgda recommends using variables in statements (also known as parameters or place holders) and reusing the same GdaStatement object when only the variable's values change. The flaws are:
The performances of the resulting application are not optimized because the database needs to parse the SQL string each time and build its own internal representation of the statement, execute the statement using its internal representation, then discard that internal representation. Using variables and reusing the same statement, the database only parses once the SQL statement, builds its internal representation, and reuses it every time the statement is executed.
Literals in SQL strings are an open invitation to SQL injection problems. Using variables prevents SQL injection.
Because each database has its own way of representing variables in an SQL string, and because those ways of representing variables don't contain enough information (it is usually impossible to specify the expected type for a variable for example) Libgda has defined a standard way of defining them, and translates it into the syntax understood by the database when needed. For more information about that syntax, see the GdaSqlParser's object description.
GdaStatement objects can be created by:
parsing SQL code using gda_sql_parser_parse_string () from a GdaSqlParser object.
building a GdaSqlStatement structure and creating a GdaStatement object around that structure. This method requires more knowledge but is more portable across databases
Executing a statement is a matter of calling gda_connection_statement_execute () or one of its simplified versions if the nature of the statement (SELECT or not) is known.
The following example shows how to use a GdaStatement to list the details of some data while making a variable (named "the_id") vary from 0 to 9 (for simplicity, error checking has been removed):
GdaConnection *cnc;
GdaSqlParser *parser;
GdaStatement *stmt;
GdaSet *params;
GdaHolder *p;
GValue *value;
gint i;
cnc = ...;
[...]
stmt = gda_sql_parser_parse_string (parser, "SELECT * FROM customers WHERE id=##the_id::gint", NULL, NULL);
gda_statement_get_parameters (stmt, ¶ms, NULL);
p = gda_set_get_holder (params, "the_id");
value = gda_value_new (G_TYPE_INT);
for (i = 0; i < 10; i++) {
GdaDataModel *res;
g_value_set_int (value, i);
gda_holder_set_value (p, value);
res = gda_connection_statement_execute_select (cnc, stmt, params, NULL);
gda_data_model_dump (res, stdout);
g_object_unref (res);
}
g_object_unref (params);
g_object_unref (stmt);