GdaSqlBuilder

GdaSqlBuilder — Factory object for statements

Synopsis

                    GdaSqlBuilder;
GdaSqlBuilder *     gda_sql_builder_new                 (GdaSqlStatementType stmt_type);
GdaStatement *      gda_sql_builder_get_statement       (GdaSqlBuilder *builder,
                                                         GError **error);
GdaSqlStatement *   gda_sql_builder_get_sql_statement   (GdaSqlBuilder *builder,
                                                         gboolean copy_it);

void                gda_sql_builder_set_table           (GdaSqlBuilder *builder,
                                                         const gchar *table_name);
void                gda_sql_builder_add_field           (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         GType type,
                                                         ...);
void                gda_sql_builder_add_field_value     (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         const GValue *value);
void                gda_sql_builder_add_field_id        (GdaSqlBuilder *builder,
                                                         guint field_id,
                                                         guint value_id);
guint               gda_sql_builder_add_function        (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *func_name,
                                                         ...);
guint               gda_sql_builder_add_function_v      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *func_name,
                                                         const guint *args,
                                                         gint args_size);

guint               gda_sql_builder_add_id              (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *string);
guint               gda_sql_builder_add_expr            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaDataHandler *dh,
                                                         GType type,
                                                         ...);
guint               gda_sql_builder_add_expr_value      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaDataHandler *dh,
                                                         const GValue *value);
guint               gda_sql_builder_add_param           (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *param_name,
                                                         GType type,
                                                         gboolean nullok);
guint               gda_sql_builder_add_case            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint test_expr,
                                                         guint else_expr,
                                                         ...);
guint               gda_sql_builder_add_case_v          (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint test_expr,
                                                         guint else_expr,
                                                         const guint *when_array,
                                                         const guint *then_array,
                                                         gint args_size);
guint               gda_sql_builder_add_sub_select      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlStatement *sqlst,
                                                         gboolean steal);

guint               gda_sql_builder_add_cond            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlOperatorType op,
                                                         guint op1,
                                                         guint op2,
                                                         guint op3);
guint               gda_sql_builder_add_cond_v          (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlOperatorType op,
                                                         const guint *op_ids,
                                                         gint op_ids_size);
void                gda_sql_builder_set_where           (GdaSqlBuilder *builder,
                                                         guint cond_id);

guint               gda_sql_builder_select_add_target   (GdaSqlBuilder *builder,
                                                         const gchar *table_name,
                                                         const gchar *alias);
guint               gda_sql_builder_select_add_target_id
                                                        (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint table_id,
                                                         const gchar *alias);
void                gda_sql_builder_select_add_field    (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         const gchar *table_name,
                                                         const gchar *alias);
guint               gda_sql_builder_select_join_targets (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint left_target_id,
                                                         guint right_target_id,
                                                         GdaSqlSelectJoinType join_type,
                                                         guint join_expr);
void                gda_sql_builder_join_add_field      (GdaSqlBuilder *builder,
                                                         guint join_id,
                                                         const gchar *field_name);
void                gda_sql_builder_select_order_by     (GdaSqlBuilder *builder,
                                                         guint expr_id,
                                                         gboolean asc,
                                                         const gchar *collation_name);

void                gda_sql_builder_compound_add_sub_select
                                                        (GdaSqlBuilder *builder,
                                                         GdaSqlStatement *sqlst,
                                                         gboolean steal);
void                gda_sql_builder_compound_set_type   (GdaSqlBuilder *builder,
                                                         GdaSqlStatementCompoundType compound_type);

Object Hierarchy

  GObject
   +----GdaSqlBuilder

Properties

  "stmt-type"                GdaSqlStatementType   : Write / Construct Only

Description

The GdaSqlBuilder can be used to build a GdaStatement from its structural description, much in the same way a GdaSqlParser can be used to build a GdaStatement from an SQL string.

The GdaBuilder internally constructs a GdaSqlStatement and uses it when requested to produce a GdaStatement (see gda_sql_builder_get_statement()), or a GdaSqlStatement (see gda_sql_builder_get_sql_statement()).

During the building process, some pieces of the statement are constructed, and assembled into the final statement. Each of these pieces can be reused anytime in the same GdaBuilder object, and each is identified using a single unsigned integer ID. That ID can either be specified by the user, or be dynamically allocated by the object (pass 0 as the requested ID).

The following example builds the equivalent of the "name='joe' AND age >= ##ageparam::int" expression:

GdaSqlBuilder *b=...
gda_sql_builder_add_id (b, 1, "name"); // build the "name" SQL identifier with requested ID=1
gda_sql_builder_add_expr (b, 2, NULL, G_TYPE_STRING, "joe"); // 'joe' expression, requested ID=2
gda_sql_builder_add_cond2 (b, 3, GDA_SQL_OPERATOR_TYPE_EQ, 1, 2); // "name='joe'", requested ID=3
gda_sql_builder_add_cond2 (b, 4, GDA_SQL_OPERATOR_TYPE_GT, // requested ID=4
       gda_sql_builder_add_id (b, 0, "age"), // build the "age" SQL identifier, no specific ID
       gda_sql_builder_add_param (b, 0, "ageparam", G_TYPE_INT, FALSE) // parameter, no specific ID
      );
gda_sql_builder_add_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expression, requested ID=5

For more examples, see the Build statements without using a parser section.

Details

GdaSqlBuilder

typedef struct _GdaSqlBuilder GdaSqlBuilder;


gda_sql_builder_new ()

GdaSqlBuilder *     gda_sql_builder_new                 (GdaSqlStatementType stmt_type);

Create a new GdaSqlBuilder object to build GdaStatement or GdaSqlStatement objects of type stmt_type

stmt_type :

the type of statement to build

Returns :

the newly created object, or NULL if an error occurred (such as unsupported statement type)

Since 4.2


gda_sql_builder_get_statement ()

GdaStatement *      gda_sql_builder_get_statement       (GdaSqlBuilder *builder,
                                                         GError **error);

Creates a new GdaStatement statement from builder's contents.

builder :

a GdaSqlBuilder object

error :

a place to store errors, or NULL

Returns :

a new GdaStatement object, or NULL if an error occurred

Since 4.2


gda_sql_builder_get_sql_statement ()

GdaSqlStatement *   gda_sql_builder_get_sql_statement   (GdaSqlBuilder *builder,
                                                         gboolean copy_it);

Creates a new GdaSqlStatement structure from builder's contents.

If copy_it is FALSE, then the returned pointer is considered to be stolen from builder's internal representation and will make it unusable anymore (resulting in a GDA_SQL_BUILDER_MISUSE_ERROR error or some warnings if one tries to reuse it). If, on the other hand it is set to TRUE, then the returned GdaSqlStatement is a copy of the on builder uses internally, making it reusable.

builder :

a GdaSqlBuilder object

copy_it :

set to TRUE to be able to reuse builder

Returns :

a GdaSqlStatement pointer

Since 4.2


gda_sql_builder_set_table ()

void                gda_sql_builder_set_table           (GdaSqlBuilder *builder,
                                                         const gchar *table_name);

Valid only for: INSERT, UPDATE, DELETE statements

Sets the name of the table on which the built statement operates.

builder :

a GdaSqlBuilder object

table_name :

a table name

Since 4.2


gda_sql_builder_add_field ()

void                gda_sql_builder_add_field           (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         GType type,
                                                         ...);

Valid only for: INSERT, UPDATE statements.

Specifies that the field represented by field_name will be set to the value identified by @... of type type.

builder :

a GdaSqlBuilder object

field_name :

a field name

type :

the GType of the following argument

... :

value to set the field to, of the type specified by type

Since 4.2


gda_sql_builder_add_field_value ()

void                gda_sql_builder_add_field_value     (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         const GValue *value);

Valid only for: INSERT, UPDATE statements.

Specifies that the field represented by field_name will be set to the value identified by value

builder :

a GdaSqlBuilder object

field_name :

a field name

value :

value to set the field to

Since 4.2


gda_sql_builder_add_field_id ()

void                gda_sql_builder_add_field_id        (GdaSqlBuilder *builder,
                                                         guint field_id,
                                                         guint value_id);

Valid only for: INSERT, UPDATE, SELECT statements

  • For UPDATE: specifies that the field represented by field_id will be set to the value identified by value_id.

  • For SELECT: add a selected item to the statement, and if value_id is not 0, then use it as an alias

  • For INSERT: if field_id represents an SQL identifier (obtained using gda_sql_builder_add_id()): then if value_id is not 0 then specifies that the field represented by field_id will be set to the value identified by value_id, otherwise just specifies a named field to be given a value. If field_id represents a sub SELECT (obtained using gda_sql_builder_add_sub_select()), then this method call defines the sub SELECT from which values to insert are taken.

builder :

a GdaSqlBuilder object

field_id :

the ID of the field's name or definition

value_id :

the ID of the value to set the field to, or 0

Since 4.2


gda_sql_builder_add_function ()

guint               gda_sql_builder_add_function        (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *func_name,
                                                         ...);

Builds a new expression which reprenents a function applied to some arguments

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

func_name :

... :

a list, terminated with 0, of each function's argument's ID

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_function_v ()

guint               gda_sql_builder_add_function_v      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *func_name,
                                                         const guint *args,
                                                         gint args_size);

Builds a new expression which represents a function applied to some arguments

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

func_name :

args :

an array of IDs representing the function's arguments

args_size :

args's size

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_id ()

guint               gda_sql_builder_add_id              (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *string);

Defines an expression representing an identifier in builder, which may be reused to build other parts of a statement.

The new expression will contain the string literal. For example:

gda_sql_builder_add_id (b, 0, "name")
gda_sql_builder_add_id (b, 0, "date")

will be rendered as SQL as:

name
"date"

because "date" is an SQL reserved keyword.

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

string :

a string

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_expr ()

guint               gda_sql_builder_add_expr            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaDataHandler *dh,
                                                         GType type,
                                                         ...);

Defines an expression in builder which may be reused to build other parts of a statement.

The new expression will contain the value passed as the v argument. It is possible to customize how the value has to be interpreted by passing a specific GdaDataHandler object as dh.

For example:

gda_sql_builder_add_expr (b, 0, G_TYPE_INT, 15);
gda_sql_builder_add_expr (b, 5, G_TYPE_STRING, "joe")

will be rendered as SQL as:

15
'joe'

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

dh :

a GdaDataHandler to use, or NULL

type :

the GType of the following argument

... :

value to set the expression to, of the type specified by type

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_expr_value ()

guint               gda_sql_builder_add_expr_value      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaDataHandler *dh,
                                                         const GValue *value);

Defines an expression in builder which may be reused to build other parts of a statement.

The new expression will contain the value passed as the value argument. It is possible to customize how the value has to be interpreted by passing a specific GdaDataHandler object as dh.

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

dh :

a GdaDataHandler to use, or NULL

value :

value to set the expression to

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_param ()

guint               gda_sql_builder_add_param           (GdaSqlBuilder *builder,
                                                         guint id,
                                                         const gchar *param_name,
                                                         GType type,
                                                         gboolean nullok);

Defines a parameter in builder which may be reused to build other parts of a statement.

The new expression will contain the string literal. For example:

gda_sql_builder_add_param (b, 0, "age", G_TYPE_INT, FALSE)

will be rendered as SQL as:

##age::int

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

param_name :

parameter's name

type :

parameter's type

nullok :

TRUE if the parameter can be set to NULL

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_case ()

guint               gda_sql_builder_add_case            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint test_expr,
                                                         guint else_expr,
                                                         ...);

Creates a new CASE ... WHEN ... THEN ... ELSE ... END expression.

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

test_expr :

the expression ID representing the test of the CASE, or 0

else_expr :

the expression ID representing the ELSE expression, or 0

... :

a list, terminated by a 0, of (WHEN expression ID, THEN expression ID) representing all the test cases

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_case_v ()

guint               gda_sql_builder_add_case_v          (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint test_expr,
                                                         guint else_expr,
                                                         const guint *when_array,
                                                         const guint *then_array,
                                                         gint args_size);

Creates a new CASE ... WHEN ... THEN ... ELSE ... END expression. The WHEN expression and the THEN expression IDs are taken from the when_array and then_array at the same index, for each index inferior to args_size.

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

test_expr :

the expression ID representing the test of the CASE, or 0

else_expr :

the expression ID representing the ELSE expression, or 0

when_array :

an array containing each WHEN expression ID, having at least args_size elements

then_array :

an array containing each THEN expression ID, having at least args_size elements

args_size :

the size of when_array and then_array

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_sub_select ()

guint               gda_sql_builder_add_sub_select      (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlStatement *sqlst,
                                                         gboolean steal);

Adds an expression which is a subselect.

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

sqlst :

a pointer to a GdaSqlStatement, which has to be a SELECT or compound SELECT

steal :

if TRUE, then sqlst will be "stolen" by b and should not be used anymore

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_cond ()

guint               gda_sql_builder_add_cond            (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlOperatorType op,
                                                         guint op1,
                                                         guint op2,
                                                         guint op3);

Builds a new expression which reprenents a condition (or operation).

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

op :

type of condition

op1 :

the ID of the 1st argument (not 0)

op2 :

the ID of the 2nd argument (may be 0 if op needs only one operand)

op3 :

the ID of the 3rd argument (may be 0 if op needs only one or two operand)

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_add_cond_v ()

guint               gda_sql_builder_add_cond_v          (GdaSqlBuilder *builder,
                                                         guint id,
                                                         GdaSqlOperatorType op,
                                                         const guint *op_ids,
                                                         gint op_ids_size);

Builds a new expression which reprenents a condition (or operation).

As a side case, if ops_ids_size is 1, then op is ignored, and the returned ID represents op_ids[0] (this avoids any problem for example when op is GDA_SQL_OPERATOR_TYPE_AND and there is in fact only one operand).

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

op :

type of condition

op_ids :

an array of ID for the arguments (not 0)

op_ids_size :

Returns :

the ID of the new expression, or 0 if there was an error

Since 4.2


gda_sql_builder_set_where ()

void                gda_sql_builder_set_where           (GdaSqlBuilder *builder,
                                                         guint cond_id);

Valid only for: UPDATE, DELETE, SELECT statements

Sets the WHERE condition of the statement

builder :

a GdaSqlBuilder object

cond_id :

the ID of the expression to set as WHERE condition, or 0 to unset any previous WHERE condition

Since 4.2


gda_sql_builder_select_add_target ()

guint               gda_sql_builder_select_add_target   (GdaSqlBuilder *builder,
                                                         const gchar *table_name,
                                                         const gchar *alias);

Adds a new target to a SELECT statement

builder :

a GdaSqlBuilder object

table_name :

the name of the target table

alias :

the alias to give to the target, or NULL

Returns :

the ID of the new target, or 0 if there was an error

Since 4.2


gda_sql_builder_select_add_target_id ()

guint               gda_sql_builder_select_add_target_id
                                                        (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint table_id,
                                                         const gchar *alias);

Adds a new target to a SELECT statement

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

table_id :

the ID of the expression holding a table reference (not 0)

alias :

the alias to give to the target, or NULL

Returns :

the ID of the new target, or 0 if there was an error

Since 4.2


gda_sql_builder_select_add_field ()

void                gda_sql_builder_select_add_field    (GdaSqlBuilder *builder,
                                                         const gchar *field_name,
                                                         const gchar *table_name,
                                                         const gchar *alias);

Valid only for: SELECT statements.

Add a selected selected item to the SELECT statement.

builder :

a GdaSqlBuilder object

field_name :

a field name

table_name :

a table name, or NULL

alias :

an alias (eg. for the "AS" clause), or NULL

Since 4.2


gda_sql_builder_select_join_targets ()

guint               gda_sql_builder_select_join_targets (GdaSqlBuilder *builder,
                                                         guint id,
                                                         guint left_target_id,
                                                         guint right_target_id,
                                                         GdaSqlSelectJoinType join_type,
                                                         guint join_expr);

Joins two targets in a SELECT statement

builder :

a GdaSqlBuilder object

id :

the requested ID, or 0 if to be determined by builder

left_target_id :

the ID of the left target to use (not 0)

right_target_id :

the ID of the right target to use (not 0)

join_type :

the type of join

join_expr :

joining expression's ID, or 0

Returns :

the ID of the new join, or 0 if there was an error

Since 4.2


gda_sql_builder_join_add_field ()

void                gda_sql_builder_join_add_field      (GdaSqlBuilder *builder,
                                                         guint join_id,
                                                         const gchar *field_name);

Alter a joins in a SELECT statement to make its condition on the field which name is field_name

builder :

a GdaSqlBuilder object

join_id :

the ID of the join to modify (not 0)

field_name :

the name of the field to use in the join condition (not NULL)

Since 4.2


gda_sql_builder_select_order_by ()

void                gda_sql_builder_select_order_by     (GdaSqlBuilder *builder,
                                                         guint expr_id,
                                                         gboolean asc,
                                                         const gchar *collation_name);

Adds a new ORDER BY expression to a SELECT statement.

builder :

a GdaSqlBuiler

expr_id :

the ID of the expression to use during sorting (not 0)

asc :

TRUE for an ascending sorting

collation_name :

name of the collation to use when sorting, or NULL

Since 4.2


gda_sql_builder_compound_add_sub_select ()

void                gda_sql_builder_compound_add_sub_select
                                                        (GdaSqlBuilder *builder,
                                                         GdaSqlStatement *sqlst,
                                                         gboolean steal);

Add a sub select to a COMPOUND statement

builder :

a GdaSqlBuilder object

sqlst :

a pointer to a GdaSqlStatement, which has to be a SELECT or compound SELECT

steal :

if TRUE, then sqlst will be "stolen" by b and should not be used anymore

Since 4.2


gda_sql_builder_compound_set_type ()

void                gda_sql_builder_compound_set_type   (GdaSqlBuilder *builder,
                                                         GdaSqlStatementCompoundType compound_type);

Changes the type of compound which builder is making, for a COMPOUND statement

builder :

a GdaSqlBuilder object

compound_type :

a type of compound

Since 4.2

Property Details

The "stmt-type" property

  "stmt-type"                GdaSqlStatementType   : Write / Construct Only

Specifies the type of statement to be built, can only be GDA_SQL_STATEMENT_SELECT, GDA_SQL_STATEMENT_INSERT, GDA_SQL_STATEMENT_UPDATE or GDA_SQL_STATEMENT_DELETE

Default value: GDA_SQL_STATEMENT_UNKNOWN