![]() |
![]() |
![]() |
GNOME Data Access 3.0 manual | ![]() |
---|
libgda offers several ways of executing queries which are outlined here:
Using a GdaCommand structure which is the simplest way, and allows one to specify an SQL statement.
Using a GdaQuery object which needs a bit more code but also offers additional features
A GdaCommand can only contain a static SQL statement without any variables in it, and it is very easy to use.
A GdaCommand structure can be created using gda_command_new (). To execute a SQL statement, specify GDA_COMMAND_TYPE_SQL as the command type. It is also possible to specify execution options using the GdaCommandOptions options.
It is also possible to bundle together several SQL statement within a single GdaCommand structure (the SQL statements are separated by a ";").
For example executing a SELECT statement could be done as in the following code (note that in this code the programmer knows that the "sql" argument is a SELECT statement):
GdaDataModel * execute_sql (GdaConnection *connection, const gchar *sql) { GdaCommand *command; GdaDataModel *result; command = gda_command_new (sql, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); result = gda_connection_execute_select_command (connection, command, NULL, NULL); gda_command_free (command); return result; }
If the programmer knows that the command is a SELECT query (and that there is only one statement) then it's best to use the gda_connection_execute_select_command () method which will return a single GdaDataModel object containing the resultset of the command's execution, or NULL if an error occurred. See the GdaDataModel's documentation for more information about how to use the resultset, or look at the managing data models section.
For example the following function cound be used to execute a non SELECT query from an SQL statement:
static GdaDataModel * run_sql_select (GdaConnection *cnc, const gchar *sql) { GdaCommand *command; GError *error = NULL; GdaDataModel *res; command = gda_command_new (sql, GDA_COMMAND_TYPE_SQL, 0); res = gda_connection_execute_select_command (cnc, command, NULL, &error); gda_command_free (command); if (!res) g_error ("Could not execute query: %s\n",error && error->message ? error->message : "no detail"); return res; }
If the programmer knows that the command is not a SELECT query, such as INSERT, UPDATE or DELETE commands (and that there is only one statement) then it's best to use the gda_connection_execute_non_select_command () method which will return the number of affected rows, or a negative value if an error occurred.
Note that it's also possible to use DDL statements (such as "CREATE TABLE...", etc) statement, but in this case for more portability, it is recommended to use libgda's specific API, see this section for more information.
For example the following function cound be used to execute a non SELECT query from an SQL statement:
void run_sql_non_select (GdaConnection *cnc, const gchar *sql) { GdaCommand *command; GError *error = NULL; gint nrows; command = gda_command_new (sql, GDA_COMMAND_TYPE_SQL, 0); nrows = gda_connection_execute_non_select_command (cnc, command, NULL, &error); gda_command_free (command); if (nrows == -1) g_error ("NON SELECT error: %s\n", error && error->message ? error->message : "no detail"); }
In the event the number of SQL statements in the command is unknown, or the kind of SQL statement is unknown, then the general way is to use the gda_connection_execute_command () which returns a list of objects, one per SQL statement. It is of course also possible to use this method in place of the other methods examined in the previous sections.
A GdaQuery object can contain variables, and a value needs to be assigned to each before the query can be run; the same query object can then be run as a prepared statement, only binding variables each time.
a GdaQuery object can understood by libgda (it can find the fields, the expressions, the tables involved, etc), and the API to maniplate its internals is quite long (it is of a good usage when building queries from known elements without using SQL). However libgda offers a simple method to create a GdaQuery from a SQL statement using gda_query_new_from_sql ().
Using a GdaQuery to execute commands has the following advantages:
the SQL statement can be internally treaded as a prepared statement (depending on the database provider) which speeds query execution
variables (place holders) can be used, their syntax being independant of the database actually used, see the GDA SQL query syntax for more information
if used with a GdaDict dictionary, it is possible to tell if the query only references existing objects (with some limitations, though)
it is possible to know the kind of query a GdaQuery is (SELECT, INSERT, etc)
a GdaQuery can only contain one SQL statement
Executing a GdaQuery query is a matter of calling gda_query_execute (). Note however that the actual connection object used to execute the query is the connection assigned to the dictionary referenced by the GdaQuery object (the 1st argument of the gda_query_new_from_sql() call).
The following example shows how to use a GdaQuery 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):
GdaQuery *query; GdaParameterList *params; GdaParameter *p; GValue *value; gint i; cnc = ...; [...] gda_dict_set_connection (default_dict, cnc); query = gda_query_new_from_sql (NULL, "SELECT * FROM customers WHERE id=##the_id::gint", NULL); params = gda_query_get_parameter_list (query); p = gda_parameter_list_find_param (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_parameter_set_value (p, value); res = gda_query_execute (query, params, FALSE, NULL); gda_data_model_dump (res, stdout); g_object_unref (res); } g_object_unref (params); g_object_unref (query);
Note that in the example above, the GdaQuery object is created using the default dictionary (the NULL value is passed as gda_query_new_from_sql() first argument), which is the reason why we assign the "cnc" connection to "default_dict" before.