sql_identifier_quote
Description:
[ Version ( since = "4.0.3" ) ]
public string? sql_identifier_quote (string id, Connection? cnc, ServerProvider? prov, bool meta_store_convention, bool force_quotes)
Use this function for any SQL identifier to make sure that: <itemizedlist> <listitem> <para>it is correctly formatted to
be used with cnc
(if cnc
is null, then some default SQL quoting rules will be
applied, similar to PostgreSQL's way) if for_meta_store
is false; </para> </listitem
> <listitem> <para>it is correctly formatted to be used with the
MetaStore's object associated to cnc
is for_meta_store
is true.
</para> </listitem> </itemizedlist>
The force_quotes
allow some control of how to interpret id
: if false, then id
will be left unchanged most of the time (except for example if it's a reserved keyword), otherwise if force_quotes
is
true, then the returned string will most probably have quotes around it to request that the database keep the
case sensitiveness (but again, this may vary depending on the database being accessed through cnc
).
For example, the following table gives the result of this function depending on the arguments when cnc
is
null (and prov
is also null): <table frame="all"> <
tgroup cols="6" colsep="1" rowsep="1" align="justify"> <thead> <row> <entry>id</entry> <entry>
for_meta_store=false, force_quotes=false</entry> <entry>
for_meta_store=true, force_quotes=false</entry> <entry>
for_meta_store=false, force_quotes=true</entry> <entry>
for_meta_store=true, force_quotes=true</entry> <entry>remark
</entry> </row> </thead> <tbody> <row> <entry>"double word"</entry> <entry>"double word"
</entry> <entry>"double word"</entry> <entry>"double word"</entry> <entry>"double word"</entry>
<entry>non allowed character in SQL identifier</entry> </row> <row> <entry>"CapitalTest"</entry> <
entry>"CapitalTest"</entry> <entry>"CapitalTest"</entry> <entry>"CapitalTest"</entry> <entry>
"CapitalTest"</entry> <entry>Mixed case SQL identifier, already quoted</entry> </row> <row> <entry>
CapitalTest</entry> <entry>CapitalTest</entry> <entry>capitaltest</entry> <entry>"CapitalTest"</entry
> <entry>"CapitalTest"</entry> <entry>Mixed case SQL identifier, non quoted</entry> </row> <row>
<entry>"mytable"</entry> <entry>"mytable"</entry> <entry>mytable</entry> <entry>"mytable"</entry
> <entry>mytable</entry> <entry>All lowser case, quoted</entry> </row> <row> <entry>mytable
</entry> <entry>mytable</entry> <entry>mytable</entry> <entry>"mytable"</entry> <entry>
mytable</entry> <entry>All lowser case</entry> </row> <row> <entry>MYTABLE</entry> <entry>
MYTABLE</entry> <entry>mytable</entry> <entry>"MYTABLE"</entry> <entry>"MYTABLE"</entry> <entry
>All upper case</entry> </row> <row> <entry>"MYTABLE"</entry> <entry>"MYTABLE"</entry> <
entry>"MYTABLE"</entry> <entry>"MYTABLE"</entry> <entry>"MYTABLE"</entry> <entry>All upper case,
quoted</entry> </row> <row> <entry>desc</entry> <entry>"desc"</entry> <entry>"desc"<
/entry> <entry>"desc"</entry> <entry>"desc"</entry> <entry>SQL reserved keyword</entry> </row>
<row> <entry>5ive</entry> <entry>"5ive"</entry> <entry>"5ive"</entry> <entry>"5ive"<
/entry> <entry>"5ive"</entry> <entry>SQL identifier starting with a digit</entry> </row> </tbody>
</tgroup> </table>
Here are a few examples of when and how to use this function: <itemizedlist> <listitem> <para> When creating a table, the user has entered the table name, this function can be used to create a valid SQL identifier from the user provided table name: < programlisting> gchar *user_sqlid=... gchar *valid_sqlid = gda_sql_identifier_quote (user_sqlid, cnc, NULL, FALSE, FALSE); gchar *sql = g_strdup_printf ("CREATE TABLE s ...", valid_sqlid); g_free (valid_sqlid); </programlisting> Note that this is an illustration and creating a table should be sone using a ServerOperation object. </para> </listitem> <listitem> <para> When updating the meta data associated to a table which has been created with the code above: <programlisting> GValue table_name_value = { 0 }; gchar* column_names[] = { (gchar*) "table_name" }; GValue* column_values[] = { &table_name_value }; GdaMetaContext mcontext = { (gchar*)"_tables", 1, column_names, column_values }; g_value_init (&table_name_value, G_TYPE_STRING); g_value_take_string (&table_name_value, gda_sql_identifier_quote (user_sqlid, cnc, NULL, TRUE, FALSE); gda_connection_update_meta_store (cnc, &mcontext, NULL); g_value_reset ( &table_name_value); </programlisting> </para> </listitem> <listitem> <para> When using a MetaStruct object to fetch information about a table (which has been created with the code above): <programlisting> GValue table_name_value = { 0 }; g_value_init (&table_name_value, G_TYPE_STRING); g_value_take_string (&table_name_value, gda_sql_identifier_quote (user_sqlid, cnc, NULL, TRUE, FALSE); GdaMetaDbObject *dbo; dbo = gda_meta_struct_complement (mstruct, GDA_META_DB_TABLE, NULL, NULL, &table_name_value, NULL); g_value_reset (&table_name_value); </programlisting> </para> </listitem> </itemizedlist>
Note that id
must not be a composed SQL identifier (such as "mytable.mycolumn" which should be treated as the "mytable" and
"mycolumn" SQL identifiers). If unsure, use sql_identifier_split.
Also note that if cnc
is null, then it's possible to pass an non null
prov
to have a result specific to prov
.
For more information, see the <link linkend="gen:sql_identifiers">SQL identifiers and abstraction</link> and <link linkend="information_schema:sql_identifiers">SQL identifiers in meta data</link> sections.
Parameters:
id |
an SQL identifier |
cnc |
a Connection object, or null |
prov |
a ServerProvider object, or null
|
force_quotes |
set to true to force the returned string to be quoted |
Returns:
the representation of |