/* open a connection with the same parameters used for in * conHandle */ Con_Handle * RS_PostgreSQL_cloneConnection(Con_Handle * conHandle) { S_EVALUATOR Mgr_Handle * mgrHandle; RS_DBI_connection *con; RS_PostgreSQL_conParams *conParams; s_object *con_params; /* get connection params used to open existing connection */ con = RS_DBI_getConnection(conHandle); conParams = con->conParams; mgrHandle = RS_DBI_asMgrHandle(MGR_ID(conHandle)); /* Connection parameters need to be put into a 8-element character * vector to be passed to the RS_PostgreSQL_newConnection() function. */ MEM_PROTECT(con_params = NEW_CHARACTER((Sint) 7)); SET_CHR_EL(con_params, 0, C_S_CPY(conParams->user)); SET_CHR_EL(con_params, 1, C_S_CPY(conParams->password)); SET_CHR_EL(con_params, 2, C_S_CPY(conParams->host)); SET_CHR_EL(con_params, 3, C_S_CPY(conParams->dbname)); SET_CHR_EL(con_params, 4, C_S_CPY(conParams->port)); SET_CHR_EL(con_params, 5, C_S_CPY(conParams->tty)); SET_CHR_EL(con_params, 6, C_S_CPY(conParams->options)); MEM_UNPROTECT(1); return RS_PostgreSQL_newConnection(mgrHandle, con_params); }
SEXP rmysql_escape_strings(SEXP conHandle, SEXP strings) { MYSQL* con = RS_DBI_getConnection(conHandle)->drvConnection; int n = length(strings); SEXP output = PROTECT(allocVector(STRSXP, n)); long size = 100; char* escaped = S_alloc(size, sizeof(escaped)); for(int i = 0; i < n; i++){ const char* string = CHAR(STRING_ELT(strings, i)); size_t len = strlen(string); if (size <= 2 * len + 1) { escaped = S_realloc(escaped, (2 * len + 1), size, sizeof(escaped)); size = 2 * len + 1; } if (escaped == NULL) { UNPROTECT(1); error("Could not allocate memory to escape string"); } mysql_real_escape_string(con, escaped, string, len); SET_STRING_ELT(output, i, mkChar(escaped)); } UNPROTECT(1); return output; }
s_object * RS_PostgreSQL_closeConnection(Con_Handle * conHandle) { S_EVALUATOR RS_DBI_connection * con; PGconn *my_connection; s_object *status; con = RS_DBI_getConnection(conHandle); if (con->num_res > 0) { RS_DBI_errorMessage("close the pending result sets before closing this connection", RS_DBI_ERROR); } /* make sure we first free the conParams and postgresql connection from * the RS-RBI connection object. */ if (con->conParams) { RS_PostgreSQL_freeConParams(con->conParams); con->conParams = (RS_PostgreSQL_conParams *) NULL; } my_connection = (PGconn *) con->drvConnection; PQfinish(my_connection); con->drvConnection = (void *) NULL; RS_DBI_freeConnection(conHandle); MEM_PROTECT(status = NEW_LOGICAL((Sint) 1)); LGL_EL(status, 0) = TRUE; MEM_UNPROTECT(1); return status; }
SEXP RS_DBI_allocResultSet(SEXP conHandle) { RS_DBI_connection *con = RS_DBI_getConnection(conHandle); int indx = RS_DBI_newEntry(con->resultSetIds, con->length); if (indx < 0) { error( "cannot allocate a new resultSet -- maximum of %d resultSets already reached", con->length ); } RS_DBI_resultSet* result = malloc(sizeof(RS_DBI_resultSet)); if (!result) { RS_DBI_freeEntry(con->resultSetIds, indx); error("could not malloc dbResultSet"); } result->drvResultSet = (void *) NULL; /* driver's own resultSet (cursor)*/ result->statement = (char *) NULL; result->connectionId = CON_ID(conHandle); result->resultSetId = con->counter; result->isSelect = (int) -1; result->rowsAffected = (int) -1; result->rowCount = (int) 0; result->completed = (int) -1; result->fields = NULL; /* update connection's resultSet table */ int res_id = con->counter; con->num_res += (int) 1; con->counter += (int) 1; con->resultSets[indx] = result; con->resultSetIds[indx] = res_id; return RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id); }
SEXP RS_DBI_connectionInfo(SEXP conHandle) { RS_DBI_connection *con; SEXP output; int i; int n = (int) 8; char *conDesc[] = {"host", "user", "dbname", "conType", "serverVersion", "protocolVersion", "threadId", "rsHandle"}; SEXPTYPE conType[] = {STRSXP, STRSXP, STRSXP, STRSXP, STRSXP, INTSXP, INTSXP, INTSXP}; int conLen[] = {1, 1, 1, 1, 1, 1, 1, -1}; con = RS_DBI_getConnection(conHandle); conLen[7] = con->num_res; /* number of resultSets opened */ output = RS_DBI_createNamedList(conDesc, conType, conLen, n); /* dummy */ SET_LST_CHR_EL(output,0,0,mkChar("NA")); /* host */ SET_LST_CHR_EL(output,1,0,mkChar("NA")); /* dbname */ SET_LST_CHR_EL(output,2,0,mkChar("NA")); /* user */ SET_LST_CHR_EL(output,3,0,mkChar("NA")); /* conType */ SET_LST_CHR_EL(output,4,0,mkChar("NA")); /* serverVersion */ LST_INT_EL(output,5,0) = (int) -1; /* protocolVersion */ LST_INT_EL(output,6,0) = (int) -1; /* threadId */ for(i=0; i < con->num_res; i++) LST_INT_EL(output,7,(int) i) = con->resultSetIds[i]; return output; }
s_object * RS_PostgreSQL_CopyIn(Con_Handle * conHandle, s_object * filename) { S_EVALUATOR RS_DBI_connection * con; PGconn *my_connection; char *dyn_filename; char copybuf[COPY_IN_BUFSIZE]; FILE* filehandle; size_t len; int pqretcode; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; dyn_filename = RS_DBI_copyString(CHR_EL(filename, 0)); filehandle=fopen(dyn_filename, "r"); if(filehandle == NULL){ char errmsg[1024]; snprintf(errmsg, 1024, "could not open file: %s", dyn_filename); RS_DBI_errorMessage(dyn_filename, RS_DBI_ERROR); return S_NULL_ENTRY; } while((len = fread(copybuf,1,COPY_IN_BUFSIZE, filehandle))){ pqretcode = PQputCopyData(my_connection, copybuf, len); chkpqcopydataerr(my_connection, pqretcode); } PQputCopyEnd(my_connection, NULL); fclose(filehandle); free(dyn_filename); return S_NULL_ENTRY; }
s_object * RS_PostgreSQL_getResult(Con_Handle * conHandle) { S_EVALUATOR RS_DBI_connection * con; S_EVALUATOR RS_DBI_resultSet * result; PGconn *my_connection; Res_Handle *rsHandle; Sint res_id; PGresult *my_result; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; if (con->num_res > 0) { res_id = (Sint) con->resultSetIds[0]; rsHandle = RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id); result = RS_DBI_getResultSet(rsHandle); if (result->completed == 0) { RS_DBI_errorMessage("connection with pending rows, close resultSet before continuing", RS_DBI_ERROR); } else { RS_PostgreSQL_closeResultSet(rsHandle); } } my_result = PQgetResult(my_connection); if(my_result == NULL) return S_NULL_ENTRY; if (strcmp(PQresultErrorMessage(my_result), "") != 0) { char *errResultMsg; const char *omsg; size_t len; omsg = PQerrorMessage(my_connection); len = strlen(omsg); errResultMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/ snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg); RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR); free(errResultMsg); /* Frees the storage associated with a PGresult. * void PQclear(PGresult *res); */ PQclear(my_result); } /* we now create the wrapper and copy values */ PROTECT(rsHandle = RS_DBI_allocResultSet(conHandle)); result = RS_DBI_getResultSet(rsHandle); result->drvResultSet = (void *) my_result; result->rowCount = (Sint) 0; result->isSelect = 0; result->rowsAffected = 0; result->completed = 1; UNPROTECT(1); return rsHandle; }
RS_DBI_resultSet* RS_DBI_getResultSet(SEXP rsHandle) { RS_DBI_connection* con = RS_DBI_getConnection(rsHandle); int indx = RS_DBI_lookup(con->resultSetIds, con->length, RES_ID(rsHandle)); if (indx < 0) error("internal error in RS_DBI_getResultSet: could not find resultSet in connection"); if (!con->resultSets[indx]) error("internal error in RS_DBI_getResultSet: missing resultSet"); return con->resultSets[indx]; }
/* RS_MySQL_createConnection - internal function * * Used by both RS_MySQL_newConnection and RS_MySQL_cloneConnection. * It is responsible for the memory associated with conParams. */ SEXP RS_MySQL_createConnection(SEXP mgrHandle, RS_MySQL_conParams *conParams) { RS_DBI_connection *con; SEXP conHandle; MYSQL *my_connection; /* Initialize MySQL connection */ my_connection = mysql_init(NULL); // Always enable INFILE option, since needed for dbWriteTable mysql_options(my_connection, MYSQL_OPT_LOCAL_INFILE, 0); /* Load MySQL default connection values from a group. * * MySQL will combine the options found in the '[client]' group and one more group * specified by MYSQL_READ_DEFAULT_GROUP. Typically, this will * be '[rs-dbi]' but the user can override with another group. Note that * while our interface will allow a user to pass in a vector of groups, * only the first group in the vector will be combined with '[client]'. * * Should we make this an error in a later release?) */ if (conParams->groups) mysql_options(my_connection, MYSQL_READ_DEFAULT_GROUP, conParams->groups); /* MySQL reads defaults from my.cnf or equivalent, but the user can supply * an alternative. */ if(conParams->default_file) mysql_options(my_connection, MYSQL_READ_DEFAULT_FILE, conParams->default_file); if(!mysql_real_connect(my_connection, conParams->host, conParams->username, conParams->password, conParams->dbname, conParams->port, conParams->unix_socket, conParams->client_flag)){ RS_MySQL_freeConParams(conParams); error( "Failed to connect to database: Error: %s\n", mysql_error(my_connection) ); } /* MySQL connections can only have 1 result set open at a time */ conHandle = RS_DBI_allocConnection(mgrHandle, (int) 1); con = RS_DBI_getConnection(conHandle); if(!con){ mysql_close(my_connection); RS_MySQL_freeConParams(conParams); error("could not alloc space for connection object"); } con->conParams = (void *) conParams; con->drvConnection = (void *) my_connection; return conHandle; }
SEXP rmysql_result_valid(SEXP res_) { RS_DBI_connection* con = RS_DBI_getConnection(res_); int indx = RS_DBI_lookup(con->resultSetIds, con->length, RES_ID(res_)); if (indx < 0) return ScalarLogical(0); if (!con->resultSets[indx]) return ScalarLogical(0); return ScalarLogical(1); }
SEXP RS_MySQL_moreResultSets(SEXP conHandle) { RS_DBI_connection *con; MYSQL *my_connection; my_bool tmp; con = RS_DBI_getConnection(conHandle); my_connection = (MYSQL *) con->drvConnection; tmp = mysql_more_results(my_connection); return ScalarLogical(tmp); }
SEXP RS_MySQL_nextResultSet(SEXP conHandle) { RS_DBI_resultSet *result; SEXP rsHandle; int num_fields, is_select; RS_DBI_connection* con = RS_DBI_getConnection(conHandle); MYSQL* my_connection = con->drvConnection; int rc = mysql_next_result(my_connection); if (rc < 0) { error("no more result sets"); } else if (rc > 0){ error("error in getting next result set"); } /* the following comes verbatim from RS_MySQL_exec() */ MYSQL_RES* my_result = mysql_use_result(my_connection); if (!my_result) my_result = NULL; num_fields = mysql_field_count(my_connection); is_select = TRUE; if (!my_result) { if (num_fields > 0) { error("error in getting next result set"); } else { is_select = FALSE; } } /* we now create the wrapper and copy values */ rsHandle = RS_DBI_allocResultSet(conHandle); result = RS_DBI_getResultSet(rsHandle); result->statement = RS_DBI_copyString("<UNKNOWN>"); result->drvResultSet = (void *) my_result; result->rowCount = (int) 0; result->isSelect = is_select; if (!is_select){ result->rowsAffected = (int) mysql_affected_rows(my_connection); result->completed = 1; } else { result->rowsAffected = (int) -1; result->completed = 0; } if (is_select) result->fields = RS_MySQL_createDataMappings(rsHandle); return rsHandle; }
/* * Adapter function to PQescapeStringConn() * This function should properly escape the string argument * appropriately depending on the encoding etc. that is specific to * connection. * Note the single quote is not attached in the return val. */ SEXP RS_PostgreSQL_escape(SEXP conHandle, SEXP preescapestring) { S_EVALUATOR PGconn * my_connection; RS_DBI_connection *con; SEXP output; size_t length; const char *statement_cstr; char *escapedstring; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; statement_cstr = CHR_EL(preescapestring, 0); length = strlen(statement_cstr); escapedstring = R_alloc(length * 2 + 1, 1); PQescapeStringConn(my_connection, escapedstring, statement_cstr, length, NULL); output = allocVector(STRSXP, 1); SET_STRING_ELT(output, 0, mkChar(escapedstring)); return output; }
/* * Adapter function to PQescapeByteaConn() * This function should properly escape the raw argument * appropriately depending on connection. * Note the single quote is not attached in the return val. * The returned string could differ depending on the environment. * Especially standard_conforming_strings parameter * is possibly influencial. * http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS */ SEXP RS_PostgreSQL_escape_bytea(SEXP conHandle, SEXP raw_data) { S_EVALUATOR PGconn * my_connection; RS_DBI_connection *con; SEXP output; size_t length; char *escapedstring; size_t escaped_length; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; length = LENGTH(raw_data); escapedstring = (char *)PQescapeByteaConn(my_connection, RAW(raw_data), length, &escaped_length); /* explicit cast to make clang silent for difference in signedness*/ PROTECT(output = allocVector(STRSXP, 1)); SET_STRING_ELT(output, 0, mkChar(escapedstring)); free(escapedstring); UNPROTECT(1); return output; }
void RS_DBI_freeResultSet(SEXP rsHandle) { RS_DBI_connection* con = RS_DBI_getConnection(rsHandle); RS_DBI_resultSet* result = RS_DBI_getResultSet(rsHandle); if(result->drvResultSet) { error("internal error in RS_DBI_freeResultSet: non-freed result->drvResultSet (some memory leaked)"); } if (result->statement) free(result->statement); if (result->fields) rmysql_fields_free(result->fields); free(result); result = NULL; /* update connection's resultSet table */ int indx = RS_DBI_lookup(con->resultSetIds, con->length, RES_ID(rsHandle)); RS_DBI_freeEntry(con->resultSetIds, indx); con->resultSets[indx] = NULL; con->num_res -= 1; }
/* the invoking (freeing) function must provide a function for * freeing the conParams, and by setting the (*free_drvConParams)(void *) * pointer. */ void RS_DBI_freeConnection(SEXP conHandle) { int indx; RS_DBI_connection* con = RS_DBI_getConnection(conHandle); MySQLDriver* mgr = rmysql_driver(); /* Are there open resultSets? If so, free them first */ if (con->num_res > 0) { int i; SEXP rsHandle; for(i=0; i < con->num_res; i++){ rsHandle = RS_DBI_asResHandle(con->managerId, con->connectionId, (int) con->resultSetIds[i]); RS_DBI_freeResultSet(rsHandle); } warning("opened resultSet(s) forcebly closed"); } if(con->drvConnection) { error("internal error in RS_DBI_freeConnection: driver might have left open its connection on the server"); } if(con->conParams){ error("internal error in RS_DBI_freeConnection: non-freed con->conParams (tiny memory leaked)"); } /* delete this connection from manager's connection table */ if(con->resultSets) free(con->resultSets); if(con->resultSetIds) free(con->resultSetIds); /* update the manager's connection table */ indx = RS_DBI_lookup(mgr->connectionIds, mgr->length, con->connectionId); RS_DBI_freeEntry(mgr->connectionIds, indx); mgr->connections[indx] = (RS_DBI_connection *) NULL; mgr->num_con -= (int) 1; free(con); con = (RS_DBI_connection *) NULL; return; }
SEXP RS_PostgreSQL_pqexec(Con_Handle * conHandle, s_object * statement) { S_EVALUATOR RS_DBI_connection * con; SEXP retval; RS_DBI_resultSet *result; PGconn *my_connection; PGresult *my_result; Sint res_id, is_select=0; char *dyn_statement; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; dyn_statement = RS_DBI_copyString(CHR_EL(statement, 0)); /* Here is where we actually run the query */ /* Example: PGresult *PQexec(PGconn *conn, const char *command); */ my_result = PQexec(my_connection, dyn_statement); if (my_result == NULL) { char *errMsg; const char *omsg; size_t len; omsg = PQerrorMessage(my_connection); len = strlen(omsg); free(dyn_statement); errMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/ snprintf(errMsg, len + 80, "could not run statement: %s", omsg); RS_DBI_errorMessage(errMsg, RS_DBI_ERROR); free(errMsg); } if (PQresultStatus(my_result) == PGRES_TUPLES_OK) { is_select = (Sint) TRUE; } if (PQresultStatus(my_result) == PGRES_COMMAND_OK) { is_select = (Sint) FALSE; } if (strcmp(PQresultErrorMessage(my_result), "") != 0) { free(dyn_statement); char *errResultMsg; const char *omsg; size_t len; omsg = PQerrorMessage(my_connection); len = strlen(omsg); errResultMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/ snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg); RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR); free(errResultMsg); /* Frees the storage associated with a PGresult. * void PQclear(PGresult *res); */ PQclear(my_result); } free(dyn_statement); PROTECT(retval = allocVector(LGLSXP, 1)); LOGICAL(retval)[0] = is_select; UNPROTECT(1); return retval; }
/* open a connection with the same parameters used for in conHandle */ SEXP RS_MySQL_cloneConnection(SEXP conHandle) { return RS_MySQL_createConnection( ScalarInteger(0), RS_MySQL_cloneConParams(RS_DBI_getConnection(conHandle)->conParams)); }
/* Execute (currently) one sql statement (INSERT, DELETE, SELECT, etc.), * set coercion type mappings between the server internal data types and * S classes. Returns an S handle to a resultSet object. */ SEXP RS_MySQL_exec(SEXP conHandle, SEXP statement) { RS_DBI_connection *con; SEXP rsHandle; RS_DBI_resultSet *result; MYSQL *my_connection; MYSQL_RES *my_result; int num_fields, state; int res_id, is_select; char *dyn_statement; con = RS_DBI_getConnection(conHandle); my_connection = (MYSQL *) con->drvConnection; dyn_statement = RS_DBI_copyString(CHR_EL(statement,0)); /* Do we have a pending resultSet in the current connection? * MySQL only allows one resultSet per connection. */ if(con->num_res>0){ res_id = (int) con->resultSetIds[0]; /* recall, MySQL has only 1 res */ rsHandle = RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id); result = RS_DBI_getResultSet(rsHandle); if(result->completed == 0){ free(dyn_statement); error("connection with pending rows, close resultSet before continuing"); } else RS_MySQL_closeResultSet(rsHandle); } /* Here is where we actually run the query */ state = mysql_query(my_connection, dyn_statement); if(state) { error("could not run statement: %s", mysql_error(my_connection)); } /* Do we need output column/field descriptors? Only for SELECT-like * statements. The MySQL reference manual suggests invoking * mysql_use_result() and if it succeed the statement is SELECT-like * that can use a resultSet. Otherwise call mysql_field_count() * and if it returns zero, the sql was not a SELECT-like statement. * Finally a non-zero means a failed SELECT-like statement. */ my_result = mysql_use_result(my_connection); if(!my_result) my_result = (MYSQL_RES *) NULL; num_fields = (int) mysql_field_count(my_connection); is_select = (int) TRUE; if(!my_result){ if(num_fields>0){ free(dyn_statement); error("error in select/select-like"); } else is_select = FALSE; } /* we now create the wrapper and copy values */ rsHandle = RS_DBI_allocResultSet(conHandle); result = RS_DBI_getResultSet(rsHandle); result->statement = RS_DBI_copyString(dyn_statement); result->drvResultSet = (void *) my_result; result->rowCount = (int) 0; result->isSelect = is_select; if(!is_select){ result->rowsAffected = (int) mysql_affected_rows(my_connection); result->completed = 1; } else { result->rowsAffected = (int) -1; result->completed = 0; } if(is_select) result->fields = RS_MySQL_createDataMappings(rsHandle); free(dyn_statement); return rsHandle; }
RS_DBI_fields * RS_PostgreSQL_createDataMappings(Res_Handle * rsHandle) { PGresult *my_result; RS_DBI_connection *con; RS_DBI_resultSet *result; RS_DBI_fields *flds; int j, num_fields, internal_type; char errMsg[128]; result = RS_DBI_getResultSet(rsHandle); my_result = (PGresult *) result->drvResultSet; con = RS_DBI_getConnection(rsHandle); num_fields = PQnfields(my_result); flds = RS_DBI_allocFields(num_fields); /* this returns malloced data (not from R) */ char buff[1000]; /* Buffer to hold the sql query to check whether the given column is nullable */ PGconn *conn; PGresult *res; conn = (PGconn *) con->drvConnection; for (j = 0; j < num_fields; j++) { flds->name[j] = RS_DBI_copyString(PQfname(my_result, j)); flds->type[j] = (int) PQftype(my_result, j); flds->length[j] = (Sint) PQfsize(my_result, j); /* NOTE: PQfmod is -1 incase of no information */ flds->precision[j] = (Sint) PQfmod(my_result, j); flds->scale[j] = (Sint) - 1; /* PQftablecol returns the column number (within its table) of * the column making up the specified query result column.Zero * is returned if the column number is out of range, or if the * specified column is not a simple reference to a table * column, or when using pre-3.0 protocol. So * "if(PQftablecol(my_result,j) !=0)" checks whether the * particular colomn in the result set is column of table or * not. Or else there is no meaning in checking whether a * column is nullable or not if it does not belong to the * table. */ flds->nullOk[j] = (Sint) INT_MIN; /* This should translate to NA in R */ if (PQftablecol(my_result, j) != 0) { /* Code to find whether a row can be nullable or not */ /* we might better just store the table id and column number for lazy evaluation at dbColumnInfo call*/ /* although the database structure can change, we are not in transaction anyway and there is no guarantee in current code */ snprintf(buff, 1000, "select attnotnull from pg_attribute where attrelid=%d and attnum='%d'", PQftable(my_result, j), PQftablecol(my_result, j)); res = PQexec(conn, buff); if (res && (PQntuples(res) > 0)) { const char * attnotnull = PQgetvalue(res, 0, 0); if(strcmp(attnotnull, "f") == 0) { flds->nullOk[j] = (Sint) 1; /* nollOK is TRUE when attnotnull is f*/ } if(strcmp(attnotnull, "t") == 0) { flds->nullOk[j] = (Sint) 0; /* nollOK is FALSE when attnotnull is t*/ } } PQclear(res); } internal_type = (int) PQftype(my_result, j); switch (internal_type) { case BOOLOID: flds->Sclass[j] = LOGICAL_TYPE; break; case BPCHAROID: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 0; break; case VARCHAROID: case TEXTOID: case BYTEAOID: case NAMEOID: case MACADDROID: case INETOID: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 1; break; case INT2OID: case INT4OID: case OIDOID: flds->Sclass[j] = INTEGER_TYPE; break; case INT8OID: if (sizeof(Sint) >= 8) { flds->Sclass[j] = INTEGER_TYPE; } else { flds->Sclass[j] = NUMERIC_TYPE; } break; case NUMERICOID: case FLOAT8OID: case FLOAT4OID: flds->Sclass[j] = NUMERIC_TYPE; break; case DATEOID: case TIMEOID: case TIMETZOID: case TIMESTAMPOID: case TIMESTAMPTZOID: case INTERVALOID: flds->Sclass[j] = CHARACTER_TYPE; /*flds->isVarLength[j] = (Sint) 1; */ break; default: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 1; snprintf(buff, 1000, "select typname, typcategory from pg_type where oid = %d", internal_type); res = PQexec(conn, buff); if (res) { char * typename; char * typecat; int ntuples; ntuples = PQntuples(res); if(ntuples == 1) { typename = PQgetvalue(res, 0, 0); typecat = PQgetvalue(res, 0, 1); if(*typecat == 'E') { /* This is enum, ok */ } else if(*typecat == 'A') { /*This is array, ok */ } else { snprintf(errMsg, 128, "unrecognized PostgreSQL field type %s (id:%d) in column %d", typename, internal_type, j); RS_DBI_errorMessage(errMsg, RS_DBI_WARNING); } } else { snprintf(errMsg, 128, "oid: %d, ntuples: %d", internal_type, ntuples); RS_DBI_errorMessage(errMsg, RS_DBI_WARNING); } PQclear(res); } else {
Res_Handle * RS_PostgreSQL_exec(Con_Handle * conHandle, s_object * statement) { S_EVALUATOR RS_DBI_connection * con; Res_Handle *rsHandle; RS_DBI_resultSet *result; PGconn *my_connection; PGresult *my_result; Sint res_id, is_select=0; char *dyn_statement; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; dyn_statement = RS_DBI_copyString(CHR_EL(statement, 0)); /* Do we have a pending resultSet in the current connection? * PostgreSQL only allows one resultSet per connection. */ if (con->num_res > 0) { res_id = (Sint) con->resultSetIds[0]; /* recall, PostgreSQL has only 1 res */ rsHandle = RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id); result = RS_DBI_getResultSet(rsHandle); if (result->completed == 0) { free(dyn_statement); RS_DBI_errorMessage("connection with pending rows, close resultSet before continuing", RS_DBI_ERROR); } else { RS_PostgreSQL_closeResultSet(rsHandle); } } /* Here is where we actually run the query */ /* Example: PGresult *PQexec(PGconn *conn, const char *command); */ my_result = PQexec(my_connection, dyn_statement); if (my_result == NULL) { char *errMsg; const char *omsg; size_t len; omsg = PQerrorMessage(my_connection); len = strlen(omsg); free(dyn_statement); errMsg = R_alloc(len + 80, 1); /* 80 should be larger than the length of "could not ..."*/ snprintf(errMsg, len + 80, "could not run statement: %s", omsg); RS_DBI_errorMessage(errMsg, RS_DBI_ERROR); } /* ExecStatusType PQresultStatus(const PGresult *res); */ if (PQresultStatus(my_result) == PGRES_TUPLES_OK) { is_select = (Sint) TRUE; } if (PQresultStatus(my_result) == PGRES_COMMAND_OK) { is_select = (Sint) FALSE; } /* char *PQresultErrorMessage(const PGresult *res); */ if (strcmp(PQresultErrorMessage(my_result), "") != 0) { char *errResultMsg; const char *omsg; size_t len; omsg = PQerrorMessage(my_connection); len = strlen(omsg); errResultMsg = R_alloc(len + 80, 1); /* 80 should be larger than the length of "could not ..."*/ snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg); /* Frees the storage associated with a PGresult. * void PQclear(PGresult *res); */ PQclear(my_result); free(dyn_statement); RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR); } /* we now create the wrapper and copy values */ PROTECT(rsHandle = RS_DBI_allocResultSet(conHandle)); result = RS_DBI_getResultSet(rsHandle); result->statement = RS_DBI_copyString(dyn_statement); result->drvResultSet = (void *) my_result; result->rowCount = (Sint) 0; result->isSelect = is_select; /* Returns the number of rows affected by the SQL command. * char *PQcmdTuples(PGresult *res); */ if (!is_select) { result->rowsAffected = (Sint) atoi(PQcmdTuples(my_result)); result->completed = 1; } else { result->rowsAffected = (Sint) - 1; result->completed = 0; } if (is_select) { result->fields = RS_PostgreSQL_createDataMappings(rsHandle); } free(dyn_statement); UNPROTECT(1); return rsHandle; }
// output is a named list SEXP RS_MySQL_fetch(SEXP rsHandle, SEXP max_rec) { MySQLDriver *mgr; RS_DBI_resultSet *result; RMySQLFields* flds; MYSQL_RES *my_result; MYSQL_ROW row; SEXP output, s_tmp; unsigned long *lens; int i, j, null_item, expand; int completed; SEXPTYPE *fld_Sclass; int num_rec; int num_fields; result = RS_DBI_getResultSet(rsHandle); flds = result->fields; if(!flds) error("corrupt resultSet, missing fieldDescription"); num_rec = asInteger(max_rec); expand = (num_rec < 0); // dyn expand output to accommodate all rows if(expand || num_rec == 0){ mgr = rmysql_driver(); num_rec = mgr->fetch_default_rec; } num_fields = flds->num_fields; PROTECT(output = NEW_LIST((int) num_fields)); RS_DBI_allocOutput(output, flds, num_rec, 0); fld_Sclass = flds->Sclass; // actual fetching.... my_result = (MYSQL_RES *) result->drvResultSet; completed = (int) 0; for(i = 0; ; i++){ if(i==num_rec){ // exhausted the allocated space if(expand){ // do we extend or return the records fetched so far num_rec = 2 * num_rec; RS_DBI_allocOutput(output, flds, num_rec, expand); } else break; // okay, no more fetching for now } row = mysql_fetch_row(my_result); if(row==NULL){ // either we finish or we encounter an error unsigned int err_no; RS_DBI_connection *con; con = RS_DBI_getConnection(rsHandle); err_no = mysql_errno((MYSQL *) con->drvConnection); completed = (int) (err_no ? -1 : 1); break; } lens = mysql_fetch_lengths(my_result); for(j = 0; j < num_fields; j++){ null_item = (row[j] == NULL); switch((int)fld_Sclass[j]){ case INTSXP: if(null_item) NA_SET(&(LST_INT_EL(output,j,i)), INTSXP); else LST_INT_EL(output,j,i) = (int) atol(row[j]); break; case STRSXP: // BUG: I need to verify that a TEXT field (which is stored as // a BLOB by MySQL!) is indeed char and not a true // Binary obj (MySQL does not truly distinguish them). This // test is very gross. if(null_item) SET_LST_CHR_EL(output,j,i,NA_STRING); else { if((size_t) lens[j] != strlen(row[j])){ warning("internal error: row %d field %d truncated", i, j); } SET_LST_CHR_EL(output,j,i,mkChar(row[j])); } break; case REALSXP: if(null_item) NA_SET(&(LST_NUM_EL(output,j,i)), REALSXP); else LST_NUM_EL(output,j,i) = (double) atof(row[j]); break; default: // error, but we'll try the field as character (!) if(null_item) SET_LST_CHR_EL(output,j,i, NA_STRING); else { warning("unrecognized field type %d in column %d", fld_Sclass[j], j); SET_LST_CHR_EL(output,j,i,mkChar(row[j])); } break; } } } // actual number of records fetched if(i < num_rec){ num_rec = i; // adjust the length of each of the members in the output_list for(j = 0; j<num_fields; j++){ s_tmp = LST_EL(output,j); PROTECT(SET_LENGTH(s_tmp, num_rec)); SET_ELEMENT(output, j, s_tmp); UNPROTECT(1); } } if(completed < 0) warning("error while fetching rows"); result->rowCount += num_rec; result->completed = (int) completed; UNPROTECT(1); return output; }
Con_Handle * RS_PostgreSQL_newConnection(Mgr_Handle * mgrHandle, s_object * con_params) { S_EVALUATOR RS_DBI_connection * con; RS_PostgreSQL_conParams *conParams; Con_Handle *conHandle; PGconn *my_connection; const char *user = NULL, *password = NULL, *host = NULL, *dbname = NULL, *port = NULL, *tty = NULL, *options = NULL; if (!is_validHandle(mgrHandle, MGR_HANDLE_TYPE)) { RS_DBI_errorMessage("invalid PostgreSQLManager", RS_DBI_ERROR); } user = CHR_EL(con_params, 0); password = CHR_EL(con_params, 1); host = CHR_EL(con_params, 2); dbname = CHR_EL(con_params, 3); port = CHR_EL(con_params, 4); tty = CHR_EL(con_params, 5); options = CHR_EL(con_params, 6); my_connection = PQsetdbLogin(host, port, options, tty, dbname, user, password); conParams = RS_postgresql_allocConParams(); /* save actual connection parameters */ conParams->user = RS_DBI_copyString(PQuser(my_connection)); conParams->password = RS_DBI_copyString(PQpass(my_connection)); { const char *tmphost = PQhost(my_connection); if (tmphost) { conParams->host = RS_DBI_copyString(tmphost); } else { conParams->host = RS_DBI_copyString(""); } } conParams->dbname = RS_DBI_copyString(PQdb(my_connection)); conParams->port = RS_DBI_copyString(PQport(my_connection)); conParams->tty = RS_DBI_copyString(PQtty(my_connection)); conParams->options = RS_DBI_copyString(PQoptions(my_connection)); if (PQstatus(my_connection) != CONNECTION_OK) { char buf[1000]; sprintf(buf, "could not connect %s@%s on dbname \"%s\"\n", PQuser(my_connection), host?host:"local", PQdb(my_connection)); PQfinish(my_connection); my_connection = NULL; RS_PostgreSQL_freeConParams(conParams); /*free BEFORE emitting err message that do not come back */ RS_DBI_errorMessage(buf, RS_DBI_ERROR); return R_NilValue; /* don't reach here as it goes back to R proc */ } PROTECT(conHandle = RS_DBI_allocConnection(mgrHandle, (Sint) 1)); /* The second argument (1) specifies the number of result sets allocated */ con = RS_DBI_getConnection(conHandle); if (my_connection && !con) { PQfinish(my_connection); my_connection = NULL; RS_PostgreSQL_freeConParams(conParams); conParams = (RS_PostgreSQL_conParams *) NULL; RS_DBI_errorMessage("could not alloc space for connection object", RS_DBI_ERROR); } if(con) { con->drvConnection = (void *) my_connection; con->conParams = (void *) conParams; } UNPROTECT(1); return conHandle; }
SEXP RS_PostgreSQL_CopyInDataframe(Con_Handle * conHandle, SEXP x, SEXP nrow, SEXP ncol) { S_EVALUATOR RS_DBI_connection * con; int nr, nc, i, j; const char *cna ="\\N", *tmp=NULL /* -Wall */; char cdec = '.'; PGconn *my_connection; int pqretcode; nr = asInteger(nrow); nc = asInteger(ncol); const int buff_threshold = 8000; con = RS_DBI_getConnection(conHandle); my_connection = (PGconn *) con->drvConnection; if(isVectorList(x)) { /* A data frame */ R_StringBuffer rstrbuf = {NULL, 0, 10000}; char *strBuf = Calloc(buff_threshold * 2 + 2, char); /* + 2 for '\t' or '\n' plus '\0'*/ char *strendp = strBuf; SEXP *levels; *strendp = '\0'; R_AllocStringBuffer(10000, &rstrbuf); /* handle factors internally, check integrity */ levels = (SEXP *) R_alloc(nc, sizeof(SEXP)); for(j = 0; j < nc; j++) { SEXP xj; xj = VECTOR_ELT(x, j); if(LENGTH(xj) != nr) error(("corrupt data frame -- length of column %d does not not match nrows"), j+1); if(inherits(xj, "factor")) { levels[j] = getAttrib(xj, R_LevelsSymbol); } else levels[j] = R_NilValue; } for(i = 0; i < nr; i++) { for(j = 0; j < nc; j++) { SEXP xj; xj = VECTOR_ELT(x, j); if(j > 0){ *strendp++ = '\t';/*need no size count check here*/ } if(isna(xj, i)) tmp = cna; else { if(!isNull(levels[j])) { /* We cannot assume factors have integer levels */ if(TYPEOF(xj) == INTSXP){ tmp = EncodeElementS(levels[j], INTEGER(xj)[i] - 1, &rstrbuf, cdec); }else if(TYPEOF(xj) == REALSXP){ tmp = EncodeElementS(levels[j], REAL(xj)[i] - 1, &rstrbuf, cdec); }else error("column %s claims to be a factor but does not have numeric codes", j+1); } else { tmp = EncodeElementS(xj, i, &rstrbuf, cdec); } } { size_t n; size_t len = strendp - strBuf; n = strlen(tmp); if (len + n < buff_threshold){ memcpy(strendp, tmp, n);/* we already know the length */ strendp += n; }else if(n < buff_threshold){ /*copy and flush*/ memcpy(strendp, tmp, n);/* we already know the length */ pqretcode = PQputCopyData(my_connection, strBuf, len + n); chkpqcopydataerr(my_connection, pqretcode); strendp = strBuf; }else{ /*flush and copy current*/ if(len > 0){ pqretcode = PQputCopyData(my_connection, strBuf, len); chkpqcopydataerr(my_connection, pqretcode); strendp = strBuf; } pqretcode = PQputCopyData(my_connection, tmp, n); chkpqcopydataerr(my_connection, pqretcode); } } } *strendp = '\n'; strendp +=1; *strendp='\0'; } pqretcode = PQputCopyData(my_connection, strBuf, strendp - strBuf); chkpqcopydataerr(my_connection, pqretcode); Free(strBuf); R_FreeStringBuffer(&rstrbuf); } PQputCopyEnd(my_connection, NULL); return R_NilValue; }