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

[Vivien Malerba <vmalerba gmail com>]

2009/1/30 Murray Cumming <murrayc murrayc com>

On Tue, 2009-01-20 at 16:13 +0100, Vivien Malerba wrote:

> As a side remark: creating a SQL string containing values literals is
> not
> the way to go: use variables instead which have the double benefit of
> making
> statement execution faster (the statement is parsed only once) and
> prevents
> SQL injection problems. Please see
> http://library.gnome.org/devel/libgda/unstable/GdaSqlParser.html#GdaSqlParser.descriptionand
> http://library.gnome.org/devel/libgda/unstable/main_example.html for
> some
> code example.

In Glom we generate some fragments of SQL, for instance, for a where
clause or a sort clause, which we then use elsewhere in a complete SQL
query. Can libgda generate these, using parameters, or can it only
generate whole SQL queries?

As an overview: Libgda's parser converts an SQL string to a GdaSqlStatement structure, which is what is actually used by the GdaStatement object.

If you want to parse and SQL portion like for example "WHERE id=##theid::int" then you'll have to prepend some text to it (for example to have "SELECT * FROM dummy WHERE id=theid::int") and parse that string. From there you can use the GdaSqlStatement part corresponding to the WHERE condition.

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").

Please keep in mind however that it's best to define several GdaStatement objects and use the one you need when you have a statement to run as it allows the database to re-use the same prepared statement and simply bind different values as parameters.

Typically, you can have a pool of GdaStatement objects (which you can create when the application starts or when needed), and one (or more) GdaSet objects to hold all (or some) of the parameters to execute the statements, so when you have a statement to execute, all you have to do is:
1) locate the GdaStatement you need, or create it if it does not exist
2) set the GdaHolder's values for each variable used in the statement
3) execute the statement.

IMHO the one constraint you should keep is to use parameters and avoid literals in the SQL statement for all the reasons explained earlier).

Vivien