JSON SQL query management functions

Functions
int	h_execute_query_json (const struct _h_connection *conn, const char *query, json_t **j_result)
int	h_query_select_json (const struct _h_connection *conn, const char *query, json_t **j_result)
int	h_select (const struct _h_connection *conn, const json_t *j_query, json_t **j_result, char **generated_query)
int	h_insert (const struct _h_connection *conn, const json_t *j_query, char **generated_query)
json_t *	h_last_insert_id (const struct _h_connection *conn)
int	h_update (const struct _h_connection *conn, const json_t *j_query, char **generated_query)
int	h_delete (const struct _h_connection *conn, const json_t *j_query, char **generated_query)
char *	h_build_where_clause (const struct _h_connection *conn, const char *pattern,...)
long long int	h_last_insert_id_sqlite (const struct _h_connection *conn)
int	h_exec_query_sqlite (const struct _h_connection *conn, const char *query)
int	h_execute_query_sqlite (const struct _h_connection *conn, const char *query)
int	h_execute_query_json_sqlite (const struct _h_connection *conn, const char *query, json_t **j_result)
long long int	h_last_insert_id_mariadb (const struct _h_connection *conn)
int	h_execute_query_json_mariadb (const struct _h_connection *conn, const char *query, json_t **j_result)
int	h_execute_query_json_pgsql (const struct _h_connection *conn, const char *query, json_t **j_result)
long long int	h_last_insert_id_pgsql (const struct _h_connection *conn)

Detailed Description

SQL query management for JSON format

Function Documentation

h_build_where_clause()

char* h_build_where_clause	(	const struct _h_connection *	conn,
		const char *	pattern,
			...
	)

h_build_where_clause Generates a where clause based on the pattern and the values given

Parameters

conn

the connection to the database

pattern

the pattern to build the where clause the pattern variables available are the following: s: a string value to escape with quotes S: a string value to escape without quotes c: a string value not to escape with quotes C: a string value not to escape without quotes d: an integer value in json_int_t format f: a double value j: a json_t value, the value must be of the type JSON_INTEGER, JSON_REAL or JSON_STRING, string values will be escaped with quotes %%: the value ''

Returns

a heap-allocated string returned value must be h_free'd after use

h_build_where_clause Generates a where clause based on the pattern and the values given return a heap-allocated string returned value must be h_free'd after use

h_delete()

int h_delete	(	const struct _h_connection *	conn,
		const json_t *	j_query,
		char **	generated_query
	)

h_delete Execute a delete query Uses a json_t * parameter for the query parameters

Parameters

conn	the connection to the database
j_query	the query encapsulated ina JSON object to execute
generated_query	a char * reference that will be allocated by the library and will contain the generated SQL query, optional, must be h_free'd after use

Returns

H_OK on success

h_delete Execute a delete query Uses a json_t * parameter for the query parameters Duplicate the generated query in generated_query if specified, must be h_free'd after use return H_OK on success

h_exec_query_sqlite()

int h_exec_query_sqlite	(	const struct _h_connection *	conn,
		const char *	query
	)

h_exec_query_sqlite legacy function name, didn't realize the typo until now... redirect to h_execute_query_sqlite

Parameters

conn	the connection to the database
query	the SQL query to execute

Returns

H_OK on success

h_execute_query_json()

int h_execute_query_json	(	const struct _h_connection *	conn,
		const char *	query,
		json_t **	j_result
	)

h_execute_query_json Execute a query, set the returned values in the json result

Parameters

conn	the connection to the database
query	the SQL query to execute
j_result	a json_t * reference that will be allocated and filled with the result if the query succeeds and is a SELECT query

Returns

H_OK on success

h_execute_query_json Execute a query, set the returned values in the json result return H_OK on success

h_execute_query_json_mariadb()

int h_execute_query_json_mariadb	(	const struct _h_connection *	conn,
		const char *	query,
		json_t **	j_result
	)

Execute a query on a mariadb connection, set the returned values in the json result This is an internal function, you should use h_execute_query_json instead Should not be executed by the user because all parameters are supposed to be correct

Parameters

conn	the connection to the database
query	the SQL query to execute
j_result	a json_t * reference that will be allocated and filled with the result

Returns

H_OK on success

h_execute_query_json_pgsql()

int h_execute_query_json_pgsql	(	const struct _h_connection *	conn,
		const char *	query,
		json_t **	j_result
	)

h_execute_query_json_pgsql Execute a query on a pgsql connection, set the returned values in the json results This is an internal function, you should use h_execute_query_json instead Should not be executed by the user because all parameters are supposed to be correct

Parameters

conn	the connection to the database
query	the SQL query to execute
j_result	a json_t * reference that will be allocated and filled with the result

Returns

H_OK on success

h_execute_query_json_sqlite()

int h_execute_query_json_sqlite	(	const struct _h_connection *	conn,
		const char *	query,
		json_t **	j_result
	)

h_execute_query_json_sqlite Execute a query on a sqlite connection, set the returned values in the json result This is an internal function, you should use h_execute_query_json instead Should not be executed by the user because all parameters are supposed to be correct

Parameters

conn	the connection to the database
query	the SQL query to execute
j_result	a json_t * reference that will be allocated and filled with the result

Returns

H_OK on success

h_execute_query_sqlite()

int h_execute_query_sqlite	(	const struct _h_connection *	conn,
		const char *	query
	)

h_execute_query_sqlite Execute a query on a sqlite connection This is an internal function, you should use h_exec_query instead Should not be executed by the user because all parameters are supposed to be correct No result is returned, useful for single INSERT, UPDATE or DELETE statements

Parameters

conn	the connection to the database
query	the SQL query to execute

Returns

H_OK on success

h_insert()

int h_insert	(	const struct _h_connection *	conn,
		const json_t *	j_query,
		char **	generated_query
	)

h_insert Execute an insert query Uses a json_t * parameter for the query parameters

Parameters

conn	the connection to the database
j_query	the query encapsulated ina JSON object to execute
generated_query	a char * reference that will be allocated by the library and will contain the generated SQL query, optional, must be h_free'd after use

Returns

H_OK on success

h_insert Execute an insert query Uses a json_t * parameter for the query parameters Duplicate the generated query in generated_query if specified, must be h_free'd after use return H_OK on success

h_last_insert_id()

json_t* h_last_insert_id

(

const struct _h_connection *

conn

)

h_last_insert_id return the id of the last inserted value return a pointer to json_t * on success, NULL otherwise. The returned value is of type JSON_INTEGER

Parameters

conn	the connection to the database

Returns

a json_t * containing the last insert id in integer format

h_last_insert_id return the id of the last inserted value return a pointer to json_t * on success, NULL otherwise. The returned value is of type JSON_INTEGER

h_last_insert_id_mariadb()

long long int h_last_insert_id_mariadb

(

const struct _h_connection *

conn

)

Return the id of the last inserted value This is an internal function, you should use h_last_insert_id instead

Parameters

conn	the connection to the database

Returns

a long long int value

h_last_insert_id_pgsql()

long long int h_last_insert_id_pgsql

(

const struct _h_connection *

conn

)

Return the id of the last inserted value This is an internal function, you should use h_last_insert_id instead

Parameters

conn	the connection to the database

Returns

a long long int value

h_last_insert_id_sqlite()

long long int h_last_insert_id_sqlite

(

const struct _h_connection *

conn

)

Return the id of the last inserted value This is an internal function, you should use h_last_insert_id instead

Parameters

conn	the connection to the database

Returns

a long long int value

h_query_select_json()

int h_query_select_json	(	const struct _h_connection *	conn,
		const char *	query,
		json_t **	j_result
	)

h_query_select_json Execute a select query, set the returned values in the json results

Parameters

conn	the connection to the database
query	the SQL query to execute
j_result	a json_t * reference that will be allocated and filled with the result if the query succeeds

Returns

H_OK on success

h_query_select_json Execute a select query, set the returned values in the json results return H_OK on success

h_select()

int h_select	(	const struct _h_connection *	conn,
		const json_t *	j_query,
		json_t **	j_result,
		char **	generated_query
	)

json queries The following functions run a sql query based on a json_t * object for input parameters The input parameter is called j_query If the j_query is well-formed, the query is executed and if available and specified, the result is stored into the j_result object. j_result must be decref'd after use Also, the sql query generated is stored into generated_query if specified, generated_query must be h_free'd after use The query execution result is returned by the function

A j_query has the following form { "table": "table_name" // String, mandatory, the table name where the query is executed "columns": ["col1", "col2"] // Array of strings, available for h_select, optional. If not specified,will be used "order_by": "col_name [asc|desc]" // String, available for h_select, specify the order by clause, optional "limit": integer_value // Integer, available for h_select, specify the limit value, optional "offset" // Integer, available for h_select, specify the limit value, optional but available only if limit is set "values": [{ // json object or json array of json objects, available for h_insert, mandatory, specify the values to update "col1": "value1", // Generates col1='value1' for an update query "col2": value_integer, // Generates col2=value_integer for an update query "col3", "value3", // Generates col3='value3' for an update query "col4", null // Generates col4=NULL for an update query }] "set": { // json object, available for h_update, mandatory, specify the values to update "col1": "value1", // Generates col1='value1' for an update query "col2": value_integer, // Generates col2=value_integer for an update query "col3", "value3", // Generates col3='value3' for an update query "col4", null // Generates col4=NULL for an update query } "where": { // json object, available for h_select, h_update and h_delete, mandatory, specify the where clause. All clauses are separated with an AND operator "col1": "value1", // Generates col1='value1' "col2": value_integer, // Generates col2=value_integer "col3": null, // Generates col3=NULL "col4", { // Generates col4<12 "operator": "<", "value": 12 }, "col5", { // Generates col5 IS NOT NULL "operator": "NOT NULL" }, "col6", { // Generates col6 LIKE 'value6' "operator": "raw", "value": "LIKE '%value6%'" } } } h_select Execute a select query Uses a json_t * parameter for the query parameters Store the result of the query in j_result if specified. j_result must be decref'd after use

Parameters

conn	the connection to the database
j_query	the query encapsulated ina JSON object to execute
j_result	a json_t * reference that will be allocated and filled with the result if the query succeeds
generated_query	a char * reference that will be allocated by the library and will contain the generated SQL query, optional, must be h_free'd after use

Returns

H_OK on success

h_select Execute a select query Uses a json_t * parameter for the query parameters Store the result of the query in j_result if specified. j_result must be decref'd after use Duplicate the generated query in generated_query if specified, must be h_free'd after use return H_OK on success

h_update()

int h_update	(	const struct _h_connection *	conn,
		const json_t *	j_query,
		char **	generated_query
	)

h_update Execute an update query Uses a json_t * parameter for the query parameters

Parameters

conn	the connection to the database
j_query	the query encapsulated ina JSON object to execute
generated_query	a char * reference that will be allocated by the library and will contain the generated SQL query, optional, must be h_free'd after use

Returns

H_OK on success

h_update Execute an update query Uses a json_t * parameter for the query parameters Duplicate the generated query in generated_query if specified, must be h_free'd after use return H_OK on success