The C++ SQL Parameter Substitution Format Specifiers

When executing the McoSqlEngine methods executeStatement(), executeQuery() or prepare(), or the C API equivalents, the format specifiers for substituting variable values into the SQL string are defined in the sections below. The parameter substitution occurs wherever a per cent sign is followed by a specifier ("%<specifier>") in the SQL string argument.

executeStatement() and executeQuery()

The following table lists the allowable format specifiers for SQL statement arguments to the C++ McoSqlEngine methods executeStatement()and executeQuery(), or the C API equivalents mcosql_execute_statement() or mcosql_execute_query():

Specifier	C++ Type	Meaning
b	bool	Boolean value
i	int	Signed 32-bit integer
u	unsigned int	Unsigned 32-bit integer
l	int64_t	Signed 64-bit integer
p	ref	eXtremeDB reference
f	double	Double precision 64-bit floating point value
t	time_t	Date/Time
w	wchar_t	Wide character string
s	char *	Multibyte character string
r	-	Pointer to a structure
R	-	Pointer to a fixed structure
*	-	Indirect qualifier: used in conjunction with another type specifier to represent a pointer to a value of the specified type
v	Value	Arbitrary value
0-9	-	The element that corresponds to this index in the parameters array

Examples

1. Map all fields of the variable c of type struct _Contribution to the fields of table Contribution:

 
    struct _Contribution
    {
        char const * name;
        uint4        date_val;
        uint4        amount;
    } c;
    ...
    executeStatement("insert into Contributions %r", c);
     

2. Map variable age (int), weight (double) and name (char*) to the corresponding columns of the Person table:

 
    executeStatement("update Person set age=%i, weight=%f where name=%s", age, weight, name);
 

3) Map variable cutoff_date (date) to the where clause of a query select statement:

 
    executeQuery("select * from Person where date_of_birth > %t", cuttoff_date);.
     

4) Using the C API, map variables salary (unsigned int), rating (double) and name (char*) to an update statement:

 
    mcosql_execute_statement(sql_db, NULL, &rc, 
        "update Person set salary=%u, rating=%f where name=%s",
        salary, rating, name );
         

 

prepare()

For prepared statements the allowable format specifiers are slightly different than those listed above. The following table lists the allowable format specifiers for SQL statement arguments to the C++ McoSqlEngine method prepare() or the C API equivalent mcosql_prepare_statement():

Specifier	C++ Type	Meaning
b	bool	Boolean value
h	int2	Integer
i<N>	int	Signed integer with width=N: 1, 2, 4 or 8
u<N>	unsigned int	Unsigned integer with width=N: 1, 2, 4 or 8
l	int64_t	Signed 64-bit integer
p	ref	eXtremeDB reference
f	double	Double precision 64-bit floating point value
t	time_t	Date/Time
w	wchar_t	Wide character string
s	char **	Pointer to a char pointer
S	char *	Pointer to a char array
r	-	Struct mapped to the record (record fields with string type should have type char*)
R	-	Struct mapped to the record (record fields with char<N> type should have type char[N])
*b	bool *	Pointer to a bool
*h	int2*	Signed int* with width=2
*i<N>	int *	Signed int* with width=N: 1, 2, 4 or 8
*u<N>	unsigned int *	Unsigned int* with width=N: 1, 2, 4 or 8
*l	int64_t *	Signed 64-bit integer
*p	ref *	eXtremeDB reference
*f	double*	Pointer to a double precision 64-bit floating point value
*t	time_t*	Pointer to a Date/Time
*s	char**	Pointer to a char pointer
*w	wchar_t**	Pointer to a wide character string
*W	char *	Pointer to a fixed size unicode string
A<N,S>	-	Array: passed as a C-style array. Arguments: N=number of elements, S=size of elements
I<N>	-	Array of signed integers: N=number of elements
U<N>	-	Array of unsigned integers: N=number of elements
F<N>	-	Array of float or double depending on N which can be 4 (float) or 8 (double)
L	-	Integer value passed as a double
v	-	Pointer to a McoSql::Value
%	-	The percent symbol

Example

 
    PreparedStatement pStmt = PreparedStatement();
    char * name = “Michael”;
 
    engine.prepare(pStmt, "select * from Person where name like %s", name));
            
     
    QueryResult result( engine.executePreparedQuery(pStmt);