Open-ILS/src/c-apps/oils_execsql.c

   1 /**
   2         @file oils_execsql.c
   3         @brief Excecute a specified SQL query and return the results.
   4 */
   5
   6 #include <stdlib.h>
   7 #include <stdio.h>
   8 #include <dbi/dbi.h>
   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"
  14
  15 static jsonObject* get_row( BuildSQLState* state );
  16 static jsonObject* get_date_column( dbi_result result, int col_idx );
  17
  18 jsonObject* oilsExecSql( BuildSQLState* state ) {
  19
  20         if( !state )
  21                 return NULL;
  22
  23         // Execute the query
  24         dbi_result result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
  25         if( !result ) {
  26                 state->error = 1;
  27                 const char* msg;
  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" ));
  31                 return NULL;
  32         }
  33
  34         if( !dbi_result_first_row( result ) )
  35                 return NULL;         // No rows returned
  36
  37         jsonObject* result_set = jsonNewObjectType( JSON_ARRAY );
  38
  39         do {
  40                 jsonObject* row = get_row( state );
  41                 if( row )
  42                         jsonObjectPush( result_set, row );
  43         } while( dbi_result_next_row( result ));
  44
  45         dbi_result_free( result );
  46         return result_set;
  47 }
  48
  49 jsonObject* oilsFirstRow( BuildSQLState* state ) {
  50
  51         if( !state )
  52                 return NULL;
  53
  54         if( state->result )
  55                 dbi_result_free( state->result );
  56
  57         // Execute the query
  58         state->result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
  59         if( !state->result ) {
  60                 state->error = 1;
  61                 const char* msg;
  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" ));
  65                 return NULL;
  66         }
  67
  68         // Get the first row
  69         if( dbi_result_first_row( state->result ))
  70                 return get_row( state );
  71         else {
  72                 dbi_result_free( state->result );
  73                 state->result = NULL;
  74                 return NULL;         // No rows returned
  75         }
  76 }
  77
  78 jsonObject* oilsNextRow( BuildSQLState* state ) {
  79
  80         if( !state || !state->result )
  81                 return NULL;
  82
  83         // Get the next row
  84         if( dbi_result_next_row( state->result ))
  85                 return get_row( state );
  86         else {
  87                 dbi_result_free( state->result );
  88                 state->result = NULL;
  89                 return NULL;         // No next row returned
  90         }
  91 }
  92
  93 static jsonObject* get_row( BuildSQLState* state  ) {
  94         unsigned int col_count = dbi_result_get_numfields( state->result );
  95         jsonObject* row = jsonNewObjectType( JSON_ARRAY );
  96
  97         unsigned int i = 1;
  98         for( i = 1; i <= col_count; ++i ) {
  99
 100                 if( dbi_result_field_is_null_idx( state->result, i )) {
 101                         jsonObjectPush( row, jsonNewObjectType( JSON_NULL ));
 102                         continue;       // Column is null
 103                 }
 104
 105                 jsonObject* col_value = NULL;
 106                 int type = dbi_result_get_field_type_idx( state->result, i );
 107                 switch( type ) {
 108                         case DBI_TYPE_INTEGER : {
 109                                 long long value = dbi_result_get_longlong_idx( state->result, i );
 110                                 col_value = jsonNewNumberObject( (double) value );
 111                                 break;
 112                         }
 113                         case DBI_TYPE_DECIMAL : {
 114                                 double value = dbi_result_get_double_idx( state->result, i );
 115                                 col_value = jsonNewNumberObject( value );
 116                                 break;
 117                         }
 118                         case DBI_TYPE_STRING : {
 119                                 const char* value = dbi_result_get_string_idx( state->result, i );
 120                                 col_value = jsonNewObject( value );
 121                                 break;
 122                         }
 123                         case DBI_TYPE_BINARY : {
 124                                 osrfLogError( OSRF_LOG_MARK, "Binary types not supported; column set to null" );
 125                                 col_value = jsonNewObjectType( JSON_NULL );
 126                                 break;
 127                         }
 128                         case DBI_TYPE_DATETIME : {
 129                                 col_value = get_date_column( state->result, i );
 130                                 break;
 131                         }
 132                         default :
 133                                 osrfLogError( OSRF_LOG_MARK,
 134                                         "Unrecognized column type %d; column set to null", type );
 135                                 col_value = jsonNewObjectType( JSON_NULL );
 136                                 break;
 137                 }
 138                 jsonObjectPush( row, col_value );
 139         }
 140
 141         return row;
 142 }
 143
 144 /**
 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.
 149
 150         The calling code is responsible for freeing the returned jsonObject by calling
 151         jsonObjectFree().
 152 */
 153 static jsonObject* get_date_column( dbi_result result, int col_idx ) {
 154
 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 );
 158         struct tm gmdt;
 159
 160         if( !( attr & DBI_DATETIME_DATE )) {
 161                 gmtime_r( &timestamp, &gmdt );
 162                 strftime( timestring, sizeof( timestring ), "%T", &gmdt );
 163         } else if( !( attr & DBI_DATETIME_TIME )) {
 164                 localtime_r( &timestamp, &gmdt );
 165                 strftime( timestring, sizeof( timestring ), "%F", &gmdt );
 166         } else {
 167                 localtime_r( &timestamp, &gmdt );
 168                 strftime( timestring, sizeof( timestring ), "%FT%T%z", &gmdt );
 169         }
 170
 171         return jsonNewObject( timestring );
 172 }