3 @brief Excecute a specified SQL query and return the results.
9 #include "opensrf/utils.h"
10 #include "opensrf/log.h"
11 #include "opensrf/string_array.h"
12 #include "opensrf/osrf_json.h"
13 #include "opensrf/osrf_application.h"
14 #include "openils/oils_sql.h"
15 #include "openils/oils_buildq.h"
17 static jsonObject* get_row( BuildSQLState* state );
18 static jsonObject* get_date_column( dbi_result result, int col_idx );
19 static int values_missing( BuildSQLState* state );
22 @brief Execute the current SQL statement and return the first row.
23 @param state Pointer to the query-building context.
24 @return Pointer to a newly-allocated jsonObject representing the row, if there is one; or
27 The returned row is a JSON_ARRAY of column values, of which each is a JSON_STRING,
28 JSON_NUMBER, or JSON_NULL.
30 jsonObject* oilsFirstRow( BuildSQLState* state ) {
35 // Make sure all the bind variables have values for them
36 if( !state->values_required && values_missing( state )) {
38 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
39 "Unable to execute query: values not available for all bind variables\n" ));
44 dbi_result_free( state->result );
47 state->result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
48 if( !state->result ) {
51 (void) dbi_conn_error( state->dbhandle, &msg );
52 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
53 "Unable to execute query: %s",msg ? msg : "No description available" ));
54 if( ! oilsIsDBConnected( state->dbhandle ))
60 if( dbi_result_first_row( state->result ))
61 return get_row( state );
63 dbi_result_free( state->result );
65 return NULL; // No rows returned
70 @brief Return the next row from a previously executed SQL statement.
71 @param state Pointer to the query-building context.
72 @return Pointer to a newly-allocated jsonObject representing the row, if there is one; or
75 The returned row is a JSON_ARRAY of column values, of which each is a JSON_STRING,
76 JSON_NUMBER, or JSON_NULL.
78 jsonObject* oilsNextRow( BuildSQLState* state ) {
80 if( !state || !state->result )
84 if( dbi_result_next_row( state->result ))
85 return get_row( state );
87 dbi_result_free( state->result );
89 return NULL; // No next row returned
94 @brief Construct a JSON representation of a returned row.
95 @param state Pointer to the query-building context.
96 @return Pointer to a newly-allocated jsonObject representing the row.
98 static jsonObject* get_row( BuildSQLState* state ) {
99 unsigned int col_count = dbi_result_get_numfields( state->result );
100 jsonObject* row = jsonNewObjectType( JSON_ARRAY );
103 for( i = 1; i <= col_count; ++i ) {
105 if( dbi_result_field_is_null_idx( state->result, i )) {
106 jsonObjectPush( row, jsonNewObjectType( JSON_NULL ));
107 continue; // Column is null
110 jsonObject* col_value = NULL;
111 int type = dbi_result_get_field_type_idx( state->result, i );
113 case DBI_TYPE_INTEGER : {
114 long long value = dbi_result_get_longlong_idx( state->result, i );
115 col_value = jsonNewNumberObject( (double) value );
118 case DBI_TYPE_DECIMAL : {
119 double value = dbi_result_get_double_idx( state->result, i );
120 col_value = jsonNewNumberObject( value );
123 case DBI_TYPE_STRING : {
124 const char* value = dbi_result_get_string_idx( state->result, i );
125 col_value = jsonNewObject( value );
128 case DBI_TYPE_BINARY : {
129 osrfLogError( OSRF_LOG_MARK, "Binary types not supported; column set to null" );
130 col_value = jsonNewObjectType( JSON_NULL );
133 case DBI_TYPE_DATETIME : {
134 col_value = get_date_column( state->result, i );
138 osrfLogError( OSRF_LOG_MARK,
139 "Unrecognized column type %d; column set to null", type );
140 col_value = jsonNewObjectType( JSON_NULL );
143 jsonObjectPush( row, col_value );
150 @brief Translate a date column into a string.
151 @param result Reference to the current returned row.
152 @param col_idx Column number (starting with 1) within the row.
153 @return Pointer to a newly-allocated JSON_STRING containing a formatted date string.
155 The calling code is responsible for freeing the returned jsonObject by calling
158 static jsonObject* get_date_column( dbi_result result, int col_idx ) {
160 time_t timestamp = dbi_result_get_datetime_idx( result, col_idx );
161 char timestring[ 256 ] = "";
162 int attr = dbi_result_get_field_attribs_idx( result, col_idx );
165 if( !( attr & DBI_DATETIME_DATE )) {
166 gmtime_r( ×tamp, &gmdt );
167 strftime( timestring, sizeof( timestring ), "%T", &gmdt );
168 } else if( !( attr & DBI_DATETIME_TIME )) {
169 gmtime_r( ×tamp, &gmdt );
170 strftime( timestring, sizeof( timestring ), "%F", &gmdt );
172 localtime_r( ×tamp, &gmdt );
173 strftime( timestring, sizeof( timestring ), "%FT%T%z", &gmdt );
176 return jsonNewObject( timestring );
180 @brief Determine whether all bind variables have values supplied for them.
181 @param state Pointer to the query-building context.
182 @return The number of bind variables with no available value.
184 static int values_missing( BuildSQLState* state ) {
185 if( !state->bindvar_list || osrfHashGetCount( state->bindvar_list ) == 0 )
186 return 0; // Nothing to count
189 osrfHashIterator* iter = osrfNewHashIterator( state->bindvar_list );
191 BindVar* bind = NULL;
192 while(( bind = osrfHashIteratorNext( iter ))) {
193 if( !bind->actual_value && !bind->default_value ) {
194 sqlAddMsg( state, "No value for bind value \"%s\", with label \"%s\"",
195 bind->name, bind->label );
200 osrfHashIteratorFree( iter );