/* 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;
}
