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 "openils/oils_buildq.h"
15 static jsonObject* get_row( BuildSQLState* state );
16 static jsonObject* get_date_column( dbi_result result, int col_idx );
18 jsonObject* oilsExecSql( BuildSQLState* state ) {
24 dbi_result result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
28 (void) dbi_conn_error( state->dbhandle, &msg );
29 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
30 "Unable to execute query: %s",msg ? msg : "No description available" ));
34 if( !dbi_result_first_row( result ) )
35 return NULL; // No rows returned
37 jsonObject* result_set = jsonNewObjectType( JSON_ARRAY );
40 jsonObject* row = get_row( state );
42 jsonObjectPush( result_set, row );
43 } while( dbi_result_next_row( result ));
45 dbi_result_free( result );
49 jsonObject* oilsFirstRow( BuildSQLState* state ) {
55 dbi_result_free( state->result );
58 state->result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
59 if( !state->result ) {
62 (void) dbi_conn_error( state->dbhandle, &msg );
63 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
64 "Unable to execute query: %s",msg ? msg : "No description available" ));
69 if( dbi_result_first_row( state->result ))
70 return get_row( state );
72 dbi_result_free( state->result );
74 return NULL; // No rows returned
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
93 static jsonObject* get_row( BuildSQLState* state ) {
94 unsigned int col_count = dbi_result_get_numfields( state->result );
95 jsonObject* row = jsonNewObjectType( JSON_ARRAY );
98 for( i = 1; i <= col_count; ++i ) {
100 if( dbi_result_field_is_null_idx( state->result, i )) {
101 jsonObjectPush( row, jsonNewObjectType( JSON_NULL ));
102 continue; // Column is null
105 jsonObject* col_value = NULL;
106 int type = dbi_result_get_field_type_idx( state->result, i );
108 case DBI_TYPE_INTEGER : {
109 long long value = dbi_result_get_longlong_idx( state->result, i );
110 col_value = jsonNewNumberObject( (double) value );
113 case DBI_TYPE_DECIMAL : {
114 double value = dbi_result_get_double_idx( state->result, i );
115 col_value = jsonNewNumberObject( value );
118 case DBI_TYPE_STRING : {
119 const char* value = dbi_result_get_string_idx( state->result, i );
120 col_value = jsonNewObject( value );
123 case DBI_TYPE_BINARY : {
124 osrfLogError( OSRF_LOG_MARK, "Binary types not supported; column set to null" );
125 col_value = jsonNewObjectType( JSON_NULL );
128 case DBI_TYPE_DATETIME : {
129 col_value = get_date_column( state->result, i );
133 osrfLogError( OSRF_LOG_MARK,
134 "Unrecognized column type %d; column set to null", type );
135 col_value = jsonNewObjectType( JSON_NULL );
138 jsonObjectPush( row, col_value );
145 @brief Translate a date column into a string.
146 @param result Reference to the current returned row.
147 @param col_idx Column number (starting with 1) within the row.
148 @return Pointer to a newly-allocated JSON_STRING containing a formatted date string.
150 The calling code is responsible for freeing the returned jsonObject by calling
153 static jsonObject* get_date_column( dbi_result result, int col_idx ) {
155 time_t timestamp = dbi_result_get_datetime_idx( result, col_idx );
156 char timestring[ 256 ] = "";
157 int attr = dbi_result_get_field_attribs_idx( result, col_idx );
160 if( !( attr & DBI_DATETIME_DATE )) {
161 gmtime_r( ×tamp, &gmdt );
162 strftime( timestring, sizeof( timestring ), "%T", &gmdt );
163 } else if( !( attr & DBI_DATETIME_TIME )) {
164 localtime_r( ×tamp, &gmdt );
165 strftime( timestring, sizeof( timestring ), "%F", &gmdt );
167 localtime_r( ×tamp, &gmdt );
168 strftime( timestring, sizeof( timestring ), "%FT%T%z", &gmdt );
171 return jsonNewObject( timestring );