Пример #1
1
/*
 * SendQuery: send the query string to the backend
 * (and print out results)
 *
 * Note: This is the "front door" way to send a query. That is, use it to
 * send queries actually entered by the user. These queries will be subject to
 * single step mode.
 * To send "back door" queries (generated by slash commands, etc.) in a
 * controlled way, use PSQLexec().
 *
 * Returns true if the query executed successfully, false otherwise.
 */
bool
SendQuery(const char *query)
{
	PGresult   *results;
	TimevalStruct before,
				after;
	bool		OK;

	if (!pset.db)
	{
		psql_error("You are currently not connected to a database.\n");
		return false;
	}

	if (GetVariableBool(pset.vars, "SINGLESTEP"))
	{
		char		buf[3];

		printf(gettext("***(Single step mode: verify command)*******************************************\n"
					   "%s\n"
					   "***(press return to proceed or enter x and return to cancel)********************\n"),
			   query);
		fflush(stdout);
		if (fgets(buf, sizeof(buf), stdin) != NULL)
			if (buf[0] == 'x')
				return false;
	}
	else if (VariableEquals(pset.vars, "ECHO", "queries"))
	{
		puts(query);
		fflush(stdout);
	}

	SetCancelConn();

	if (PQtransactionStatus(pset.db) == PQTRANS_IDLE &&
		!GetVariableBool(pset.vars, "AUTOCOMMIT") &&
		!is_transact_command(query))
	{
		results = PQexec(pset.db, "BEGIN");
		if (PQresultStatus(results) != PGRES_COMMAND_OK)
		{
			psql_error("%s", PQerrorMessage(pset.db));
			PQclear(results);
			ResetCancelConn();
			return false;
		}
		PQclear(results);
	}

	if (pset.timing)
		GETTIMEOFDAY(&before);

	results = PQexec(pset.db, query);

	/* these operations are included in the timing result: */
	OK = (AcceptResult(results) && ProcessCopyResult(results));

	if (pset.timing)
		GETTIMEOFDAY(&after);

	/* but printing results isn't: */
	if (OK)
		OK = PrintQueryResults(results);

	PQclear(results);

	/* Possible microtiming output */
	if (OK && pset.timing)
		printf(gettext("Time: %.3f ms\n"), DIFF_MSEC(&after, &before));

	/* check for events that may occur during query execution */

	if (pset.encoding != PQclientEncoding(pset.db) &&
		PQclientEncoding(pset.db) >= 0)
	{
		/* track effects of SET CLIENT_ENCODING */
		pset.encoding = PQclientEncoding(pset.db);
		pset.popt.topt.encoding = pset.encoding;
		SetVariable(pset.vars, "ENCODING",
					pg_encoding_to_char(pset.encoding));
	}

	PrintNotifications();

	return OK;
}
Пример #2
0
int msPOSTGRESQLJoinNext(joinObj *join)
{
  msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
  int i, length, row_count;
  char *sql, *columns;

  /* We need a connection, and a join value. */
  if(!joininfo || !joininfo->conn) {
    msSetError(MS_JOINERR, "Join has not been connected.\n",
               "msPOSTGRESQLJoinNext()");
    return MS_FAILURE;
  }

  if(!joininfo->from_value) {
    msSetError(MS_JOINERR, "Join has not been prepared.\n",
               "msPOSTGRESQLJoinNext()");
    return MS_FAILURE;
  }

  /* Free the previous results. */
  if(join->values) {
    msFreeCharArray(join->values, join->numitems);
    join->values = NULL;
  }

  /* We only need to execute the query if no results exist. */
  if(!joininfo->query_result) {
    /* Write the list of column names. */
    length = 0;
    for(i = 0; i < join->numitems; i++) {
      length += 8 + strlen(join->items[i]) + 2;
    }

    columns = (char *)malloc(length);
    if(!columns) {
      msSetError(MS_MEMERR, "Failure to malloc.\n",
                 "msPOSTGRESQLJoinNext()");
      return MS_FAILURE;
    }

    strcpy(columns, "");
    for(i = 0; i < join->numitems; i++) {
      strcat(columns, "\"");
      strcat(columns, join->items[i]);
      strcat(columns, "\"::text");
      if(i != join->numitems - 1) {
        strcat(columns, ", ");
      }
    }

    /* Create the query string. */
    sql = (char *)malloc(26 + strlen(columns) + strlen(join->table) +
                         strlen(join->to) + strlen(joininfo->from_value));
    if(!sql) {
      msSetError(MS_MEMERR, "Failure to malloc.\n",
                 "msPOSTGRESQLJoinNext()");
      return MS_FAILURE;
    }
    sprintf(sql, "SELECT %s FROM %s WHERE %s = '%s'", columns, join->table, join->to, joininfo->from_value);
    if(joininfo->layer_debug) {
      msDebug("msPOSTGRESQLJoinNext(): executing %s.\n", sql);
    }

    free(columns);

    joininfo->query_result = PQexec(joininfo->conn, sql);

    if(!joininfo->query_result ||
        PQresultStatus(joininfo->query_result) != PGRES_TUPLES_OK) {
      msSetError(MS_QUERYERR, "Error executing queri %s: %s\n",
                 "msPOSTGRESQLJoinNext()", sql,
                 PQerrorMessage(joininfo->conn));
      if(joininfo->query_result) {
        PQclear(joininfo->query_result);
        joininfo->query_result = NULL;
      }
      free(sql);
      return MS_FAILURE;
    }
    free(sql);
  }
  row_count = PQntuples(joininfo->query_result);

  /* see if we're done processing this set */
  if(joininfo->row_num >= row_count) {
    return(MS_DONE);
  }
  if(joininfo->layer_debug) {
    msDebug("msPOSTGRESQLJoinNext(): fetching row %ld.\n",
            joininfo->row_num);
  }

  /* Copy the resulting values into the joinObj. */
  join->values = (char **)malloc(sizeof(char *) * join->numitems);
  for(i = 0; i < join->numitems; i++) {
    join->values[i] = msStrdup(PQgetvalue(
                                 joininfo->query_result, joininfo->row_num, i));
  }

  joininfo->row_num++;

  return MS_SUCCESS;
}
Пример #3
0
int main(int argc, char * argv[])
{
	PGconn *conn;
	PGresult *res;
	char * conninfo = calloc (MAX_LEN, sizeof (char));
	char * query = calloc (MAX_LEN, sizeof (char));
	char * nama = calloc (MAX_LEN, sizeof (char));
	char * alamat = calloc (MAX_LEN, sizeof (char));
	char * temp = calloc (MAX_LEN, sizeof (char));
	int field_count;
	int rec_count;
	int c,menu;
	int i,j;

	strcpy (conninfo, "dbname=test user=nop");
	conn = PQconnectdb (conninfo);

	if (PQstatus (conn) != CONNECTION_OK)
	{	

		fprintf (stderr, "Kesalahan koneksi: %s\n", PQerrorMessage (conn));
		exit (1);
	}

	while ( 1 )
	{
		fprintf(stdout, "\n\n\nDATA PASIEN\n");
		fprintf(stdout, "***********\n\n");
		fprintf(stdout, "a Tambah data\n");
		fprintf(stdout, "b Tampil data\n");
		fprintf(stdout, "x Keluar aplikasi\n");
		fprintf(stdout, "Pilihan Anda: ");
		c = tolower(fgetc (stdin));
		menu = c;

		while (c != '\n' && c != EOF) c = fgetc (stdin);

		if (menu == 'a')
		{
			fprintf(stdout, "Tambah data\n");
			fprintf(stdout, "===========\n");
			fprintf(stdout, "Nama    : ");
			fgets (nama, MAX_LEN-1, stdin);
			fprintf(stdout, "Alamat  : ");
			fgets (alamat, MAX_LEN-1, stdin);
			sprintf (query, "insert into pasien (nama, alamat) values ('%s','%s')", nama, alamat);
			res = PQexec (conn, query);
			PQclear (res);
		}
		else
		if (menu == 'b')
		{
			fprintf(stdout, "Tampil data\n");
			fprintf(stdout, "===========\n");
			sprintf (query, "select nama,alamat from pasien");
			res = PQexec (conn, query);
			field_count = PQnfields (res);
			for (i=0; i< field_count; i++)
			{
				fprintf (stdout, "%-40s", PQfname (res, i));
			}
			fprintf (stdout, "\n");

			rec_count = PQntuples (res);
			for (i=0; i< rec_count; i++)
			{
				for (j=0; j< field_count; j++)
				{
					strcpy (temp, PQgetvalue (res, i, j));
					temp[strlen(temp)-1] = 0;
					fprintf (stdout, "%-40s", temp);
				}
				fprintf (stdout, "\n");
			}

			PQclear (res);


		}
		else
		if (menu == 'x')
		{
			fprintf(stdout, "Bye\n");
			break;

		}
	};
	
	PQfinish (conn);

	free (nama);
	free (alamat);
	free (query);
	free (conninfo);
	free (temp);

	return 0;
}
Пример #4
0
/*+++++++++++++++++++++++++ Main Program or Function +++++++++++++++*/
void SCIA_WR_SQL_CH4_TILE( PGconn *conn, const char *prodName,
			   unsigned int num_rec, 
			   const struct imap_rec *rec )
{
     register unsigned int nr;
     register unsigned int affectedRows = 0u;

     char   sql_query[SQL_STR_SIZE], cbuff[SQL_STR_SIZE];

     char   *pntr;
     int    nrow, numChar, meta_id;

     long long tile_id;

     PGresult *res;
/*
 * check if product is already in database
 */
     (void) snprintf( sql_query, SQL_STR_SIZE, 
		      "SELECT pk_meta FROM %s WHERE name=\'%s\'", 
		      META_TBL_NAME, prodName );
     res = PQexec( conn, sql_query );
     if ( PQresultStatus( res ) != PGRES_TUPLES_OK ) {
          NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     }
     if ( (nrow = PQntuples( res )) == 0 ) {
          NADC_GOTO_ERROR( NADC_ERR_FATAL, prodName );
     }
     pntr = PQgetvalue( res, 0, 0 );
     meta_id = (int) strtol( pntr, (char **) NULL, 10 );     
     PQclear( res );
/*
 * Start a transaction block
 */
     res = PQexec( conn, "BEGIN" );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
          NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     PQclear( res );
/*
 * insert all tiles in products
 */
     for ( nr = 0; nr < num_rec; nr++ ) {
	  /* obtain next value for serial pk_tile */
	  res = PQexec( conn,
			"SELECT nextval(\'tile_imap_ch4_pk_tile_seq\')" );
	  if ( PQresultStatus( res ) != PGRES_TUPLES_OK )
	       NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
	  pntr = PQgetvalue( res, 0, 0 );
	  tile_id = strtoll( pntr, (char **) NULL, 10 );
	  PQclear( res );

	  numChar = snprintf( sql_query, SQL_STR_SIZE, SQL_INSERT_TILE, 
			      TILE_TBL_NAME, tile_id, meta_id, rec[nr].jday,
			      NINT(16 * rec[nr].meta.intg_time),
			      rec[nr].meta.elev, 
			      rec[nr].ch4_vcd, rec[nr].ch4_error,
			      rec[nr].co2_vcd, rec[nr].co2_error,
			      rec[nr].ch4_vmr,
			      rec[nr].lon_corner[0],rec[nr].lat_corner[0],
			      rec[nr].lon_corner[1],rec[nr].lat_corner[1],
			      rec[nr].lon_corner[2],rec[nr].lat_corner[2],
			      rec[nr].lon_corner[3],rec[nr].lat_corner[3],
			      rec[nr].lon_corner[0],rec[nr].lat_corner[0] );

  	  (void) fprintf( stderr, "%s [%-d]\n", sql_query, numChar );
	  if ( numChar >= SQL_STR_SIZE )
	       NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
	  res = PQexec( conn, sql_query );
	  if ( PQresultStatus( res ) != PGRES_COMMAND_OK ) {
	       NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
	       PQclear( res );
	       res = PQexec( conn, "ROLLBACK" );
	       if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
		    NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
	       goto done;
	  }
	  PQclear( res );

	  affectedRows += 1;
     }
/*
 * end the transaction
 */
     res = PQexec( conn, "COMMIT" );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
          NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
 done:
     PQclear( res );
     (void) snprintf( cbuff, SQL_STR_SIZE, "affectedRows=%-u", affectedRows );
     NADC_ERROR( NADC_ERR_NONE, cbuff );
}
Пример #5
0
void save_TUBii_command(client *c, int argc, sds *argv)
{
    /* Update the TUBii state. */
    uint32_t key;
    PGconn *conn;
    char conninfo[1024];
    PGresult *res = NULL;
    char command[10000];

    //char* dbname="test", dbhost="", dbuser="", dbpass="";
    sprintf(conninfo, "dbname=%s host=%s user=%s password=%s", dbconfig.name, dbconfig.host, dbconfig.user, dbconfig.password);

    /* Now we update the database */
    conn = PQconnectdb(conninfo);

    if (PQstatus(conn) != CONNECTION_OK) {
        addReplyErrorFormat(c, "connection to database failed: %s", PQerrorMessage(conn));
        goto pq_error;
    }

    sprintf(command, "insert into TUBii ("
                     "control_reg, trigger_mask, speaker_mask, counter_mask,"
    				 "caen_gain_reg, caen_channel_reg, lockout_reg, dgt_reg, dac_reg,"
    				 "combo_enable_mask, combo_mask, counter_mode, clock_status,"
    				 "prescale_value, prescale_channel, burst_rate, burst_channel"
    				 ") "
                     "VALUES ("
                     "%u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u"
                     ") "
                     "RETURNING key",
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset10),
                     getTriggerMask(), getSpeakerMask(), getCounterMask(),
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset11),
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset12),
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset14),
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset15),
                     mReadReg((u32) MappedRegsBaseAddress, RegOffset13),
                     mReadReg((u32) MappedComboBaseAddress, RegOffset2),
    				 mReadReg((u32) MappedComboBaseAddress, RegOffset3),
    				 counter_mode, clockStatus(),
    				 mReadReg((u32) MappedPrescaleBaseAddress, RegOffset2),
    				 mReadReg((u32) MappedPrescaleBaseAddress, RegOffset3),
    				 mReadReg((u32) MappedBurstBaseAddress, RegOffset2),
    				 mReadReg((u32) MappedBurstBaseAddress, RegOffset3)
                     );


    res = PQexec(conn, command);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        addReplyErrorFormat(c, "insert command failed: %s",
                            PQerrorMessage(conn));
        goto pq_error;
    }

    if (PQnfields(res) != 1) {
        addReplyError(c, "failed to get key from insert");
        goto pq_error;
    }

    if (safe_strtoul(PQgetvalue(res, 0, 0), &key)) {
        addReplyErrorFormat(c, "couldn't convert key from '%s' -> int", PQgetvalue(res, 0, 0));
        goto pq_error;
    }

    PQclear(res);
    PQfinish(conn);

    addReply(c, ":%u", key);
    return;

err:
    addReplyError(c, tubii_err);
    return;

pq_error:
    if (res) PQclear(res);
    PQfinish(conn);
}
/*************************************************************************
 *
 *	Function: sql_query
 *
 *	Purpose: Issue a query to the database
 *
 *************************************************************************/
static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {

    rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
    int numfields = 0;
    char *errorcode;
    char *errormsg;

    if (config->sqltrace)
        radlog(L_DBG,"rlm_sql_postgresql: query:\n%s", querystr);

    if (pg_sock->conn == NULL) {
        radlog(L_ERR, "rlm_sql_postgresql: Socket not connected");
        return SQL_DOWN;
    }

    pg_sock->result = PQexec(pg_sock->conn, querystr);
    /*
     * Returns a PGresult pointer or possibly a null pointer.
     * A non-null pointer will generally be returned except in
     * out-of-memory conditions or serious errors such as inability
     * to send the command to the server. If a null pointer is
     * returned, it should be treated like a PGRES_FATAL_ERROR
     * result.
     */
    if (!pg_sock->result)
    {
        radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL Query failed Error: %s",
               PQerrorMessage(pg_sock->conn));
        /* As this error COULD be a connection error OR an out-of-memory
         * condition return value WILL be wrong SOME of the time regardless!
         * Pick your poison....
         */
        return  SQL_DOWN;
    } else {
        ExecStatusType status = PQresultStatus(pg_sock->result);
        radlog(L_DBG, "rlm_sql_postgresql: Status: %s", PQresStatus(status));

        switch (status) {

        case PGRES_COMMAND_OK:
            /*Successful completion of a command returning no data.*/

            /*affected_rows function only returns
            the number of affected rows of a command
            returning no data...
            */
            pg_sock->affected_rows	= affected_rows(pg_sock->result);
            radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i", pg_sock->affected_rows);
            return 0;

            break;

        case PGRES_TUPLES_OK:
            /*Successful completion of a command returning data (such as a SELECT or SHOW).*/

            pg_sock->cur_row = 0;
            pg_sock->affected_rows = PQntuples(pg_sock->result);
            numfields = PQnfields(pg_sock->result); /*Check row storing functions..*/
            radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i , fields = %i", pg_sock->affected_rows, numfields);
            return 0;

            break;

        case PGRES_BAD_RESPONSE:
            /*The server's response was not understood.*/
            radlog(L_DBG, "rlm_sql_postgresql: Bad Response From Server!!");
            return -1;

            break;

        case PGRES_NONFATAL_ERROR:
            /*A nonfatal error (a notice or warning) occurred. Possibly never returns*/

            return -1;

            break;

        case PGRES_FATAL_ERROR:
#if defined(PG_DIAG_SQLSTATE) && defined(PG_DIAG_MESSAGE_PRIMARY)
            /*A fatal error occurred.*/

            errorcode = PQresultErrorField(pg_sock->result, PG_DIAG_SQLSTATE);
            errormsg  = PQresultErrorField(pg_sock->result, PG_DIAG_MESSAGE_PRIMARY);
            radlog(L_DBG, "rlm_sql_postgresql: Error %s", errormsg);
            return check_fatal_error(errorcode);
#endif

            break;

        default:
            /* FIXME: An unhandled error occurred.*/

            /* PGRES_EMPTY_QUERY PGRES_COPY_OUT PGRES_COPY_IN */

            return -1;

            break;


        }

        /*
        	Note to self ... sql_store_result returns 0 anyway
        	after setting the sqlsocket->affected_rows..
        	sql_num_fields returns 0 at worst case which means the check below
        	has a really small chance to return false..
        	lets remove it then .. yuck!!
        */
        /*
        } else {
        	if ((sql_store_result(sqlsocket, config) == 0)
        			&& (sql_num_fields(sqlsocket, config) >= 0))
        		return 0;
        	else
        		return -1;
        }
        */
    }
    return -1;
}
Пример #7
0
static int begin_transaction(db_con_t * _h, char *_s)
{
	PGresult *mr;
	int rv;

	/*
	** Note:
	**  The upper layers of code may attempt a transaction
	**  before opening or having a valid connection to the
	**  database.  We try to sense this, and open the database
	**  if we have the sqlurl in the _h structure.  Otherwise,
	**  all we can do is return an error.
	*/

	if(_h)
	{
		if(CON_CONNECTED(_h))
		{
			mr = PQexec(CON_CONNECTION(_h), "BEGIN");
			if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
			{
				/*
				** We get here if the connection to the
				** db is corrupt, which can happen a few
				** different ways, but all of them are
				** related to the parent process forking,
				** or being forked.
				*/
				PLOG("begin_transaction","corrupt connection");
				CON_CONNECTED(_h) = 0;
			}
			else
			{
				/*
				** this is the normal way out.
				** the transaction ran fine.
				*/
				PQclear(mr);
				return(0);
			}
		}
		else
		{
			DLOG("begin_transaction", "called before db_init");
		}

		/*
		** if we get here we have a corrupt db connection,
		** but we probably have a valid db_con_t structure.
		** attempt to open the db.
		*/

		if((rv = connect_db(_h, CON_SQLURL(_h))) != 0)
		{
			/*
			** our attempt to fix the connection failed
			*/
			char buf[256];
			sprintf(buf, "no connection, FATAL %d!", rv);
			PLOG("begin_transaction",buf);
			return(rv);
		}
	}
	else
	{
		PLOG("begin_transaction","must call db_init first!");
		return(-1);
	}

	/*
	** we get here if the database connection was corrupt,
	** i didn't want to use recursion ...
	*/

	mr = PQexec(CON_CONNECTION(_h), "BEGIN");
	if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
	{
		char buf[256];
		sprintf("FATAL %s, '%s'!\n",
			PQerrorMessage(CON_CONNECTION(_h)), _s);
		PLOG("begin_transaction", buf);
		return(-1);
	}

	DLOG("begin_transaction", "db channel reset successful");

	PQclear(mr);
	return(0);
}
Пример #8
0
/**
 * query = select * from pg_database
 */
char darkkernel_bdd_execute(bdd *self,char *query)
{
 PGresult   *res;
 char portal[1000],result=1;

 int         nFields;
 int         i,
             j;

 if (!self->conn)
  self->connect(self);

 if (!self->conn)
  return 0;

 /* Start a transaction block */
 res = PQexec(self->conn, "BEGIN");
 if (PQresultStatus(res) != PGRES_COMMAND_OK)
 {
     fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(self->conn));
     PQclear(res);
     self->disconnect(self);
     result=0;
 }
 PQclear(res);

 /*
  * Fetch rows from pg_database, the system catalog of databases
  */
 sprintf(portal, "DECLARE myportal CURSOR FOR %s\0", query);
 res = PQexec(self->conn, portal);
 if (PQresultStatus(res) != PGRES_COMMAND_OK)
 {
     fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(self->conn));
     PQclear(res);
     PQfinish(self->conn);
 }
 PQclear(res);

 res = PQexec(self->conn, "FETCH ALL in myportal");
 if (PQresultStatus(res) != PGRES_TUPLES_OK)
 {
     fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(self->conn));
     PQclear(res);
     PQfinish(self->conn);
 }

 /* first, print out the attribute names */
 nFields = PQnfields(res);
 for (i = 0; i < nFields; i++)
     printf("%-15s", PQfname(res, i));
 printf("\n\n");

 /* next, print out the rows */
 for (i = 0; i < PQntuples(res); i++)
 {
     for (j = 0; j < nFields; j++)
         printf("%-15s", PQgetvalue(res, i, j));
     printf("\n");
 }

 PQclear(res);

 /* close the portal ... we don't bother to check for errors ... */
 res = PQexec(self->conn, "CLOSE myportal");
 PQclear(res);

 /* end the transaction */
 res = PQexec(self->conn, "END");
 PQclear(res);

 self->disconnect(self);

 return result;
}
Пример #9
0
/*
 * Execute SQL statement. For non-select statements only.
 */
int	zbx_db_vexecute(const char *fmt, va_list args)
{
	char	*sql = NULL;
	int	ret = ZBX_DB_OK;
	double	sec = 0;

#if defined(HAVE_IBM_DB2)
	SQLHANDLE	hstmt = 0;
	SQLRETURN	ret1;
	SQLLEN		row1;
	SQLLEN		rows = 0;
#elif defined(HAVE_MYSQL)
	int		status;
#elif defined(HAVE_ORACLE)
	OCIStmt		*stmthp = NULL;
	sword		err = OCI_SUCCESS;
#elif defined(HAVE_POSTGRESQL)
	PGresult	*result;
	char		*error = NULL;
#elif defined(HAVE_SQLITE3)
	int		err;
	char		*error = NULL;
#endif

	if (0 != CONFIG_LOG_SLOW_QUERIES)
		sec = zbx_time();

	sql = zbx_dvsprintf(sql, fmt, args);

	if (0 == txn_init && 0 == txn_level)
		zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");

	if (1 == txn_error)
	{
		zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
		ret = ZBX_DB_FAIL;
		goto clean;
	}

	zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);

#if defined(HAVE_IBM_DB2)
	/* allocate a statement handle */
	if (SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &hstmt)))
		ret = ZBX_DB_DOWN;

	/* directly execute the statement; returns SQL_NO_DATA_FOUND when no rows were affected */
  	if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success_ext(SQLExecDirect(hstmt, (SQLCHAR *)sql, SQL_NTS)))
		ret = ZBX_DB_DOWN;

	/* get number of affected rows */
	if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &rows)))
		ret = ZBX_DB_DOWN;

	/* process other SQL statements in the batch */
	while (ZBX_DB_OK == ret && SUCCEED == zbx_ibm_db2_success(ret1 = SQLMoreResults(hstmt)))
	{
		if (SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &row1)))
			ret = ZBX_DB_DOWN;
		else
			rows += row1;
	}

	if (ZBX_DB_OK == ret && SQL_NO_DATA_FOUND != ret1)
		ret = ZBX_DB_DOWN;

	if (ZBX_DB_OK != ret)
	{
		zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
		zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, hstmt);

		ret = (SQL_CD_TRUE == IBM_DB2server_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
	}
	else if (0 <= rows)
	{
		ret = (int)rows;
	}

	if (hstmt)
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
#elif defined(HAVE_MYSQL)
	if (NULL == conn)
	{
		zabbix_errlog(ERR_Z3003);
		ret = ZBX_DB_FAIL;
	}
	else
	{
		if (0 != (status = mysql_query(conn, sql)))
		{
			zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);

			switch (mysql_errno(conn))
			{
				case CR_CONN_HOST_ERROR:
				case CR_SERVER_GONE_ERROR:
				case CR_CONNECTION_ERROR:
				case CR_SERVER_LOST:
				case ER_SERVER_SHUTDOWN:
				case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
				case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
				case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
				case ER_UNKNOWN_ERROR:
					ret = ZBX_DB_DOWN;
					break;
				default:
					ret = ZBX_DB_FAIL;
					break;
			}
		}
		else
		{
			do
			{
				if (0 != mysql_field_count(conn))
				{
					zabbix_log(LOG_LEVEL_DEBUG, "cannot retrieve result set");
					break;
				}
				else
					ret += (int)mysql_affected_rows(conn);

				/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
				if (0 < (status = mysql_next_result(conn)))
					zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
			}
			while (0 == status);
		}
	}
#elif defined(HAVE_ORACLE)
	err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);

	if (OCI_SUCCESS == err)
	{
		err = OCIStmtPrepare(stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
				(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
	}

	if (OCI_SUCCESS == err)
	{
		err = OCIStmtExecute(oracle.svchp, stmthp, oracle.errhp, (ub4)1, (ub4)0,
				(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);

		if (OCI_SUCCESS == err)
		{
			ub4	nrows = 0;

			err = OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, (ub4 *)&nrows,
					  (ub4 *)0, OCI_ATTR_ROW_COUNT, oracle.errhp);

			ret = nrows;
		}
	}

	if (OCI_SUCCESS != err)
	{
		zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
		ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
	}

	if (NULL != stmthp)
	{
		(void)OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
		stmthp = NULL;
	}
#elif defined(HAVE_POSTGRESQL)
	result = PQexec(conn,sql);

	if (NULL == result)
	{
		zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
		ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
	}
	else if (PGRES_COMMAND_OK != PQresultStatus(result))
	{
		error = zbx_dsprintf(error, "%s:%s",
				PQresStatus(PQresultStatus(result)),
				PQresultErrorMessage(result));
		zabbix_errlog(ERR_Z3005, 0, error, sql);
		zbx_free(error);

		ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
	}

	if (ZBX_DB_OK == ret)
		ret = atoi(PQcmdTuples(result));

	PQclear(result);
#elif defined(HAVE_SQLITE3)
	if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
	{
		zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
		exit(FAIL);
	}

lbl_exec:
	if (SQLITE_OK != (err = sqlite3_exec(conn, sql, NULL, 0, &error)))
	{
		if (SQLITE_BUSY == err)
			goto lbl_exec;

		zabbix_errlog(ERR_Z3005, 0, error, sql);
		sqlite3_free(error);

		switch (err)
		{
			case SQLITE_ERROR:	/* SQL error or missing database; assuming SQL error, because if we
						   are this far into execution, zbx_db_connect() was successful */
			case SQLITE_NOMEM:	/* A malloc() failed */
			case SQLITE_TOOBIG:	/* String or BLOB exceeds size limit */
			case SQLITE_CONSTRAINT:	/* Abort due to constraint violation */
			case SQLITE_MISMATCH:	/* Data type mismatch */
				ret = ZBX_DB_FAIL;
				break;
			default:
				ret = ZBX_DB_DOWN;
				break;
		}
	}

	if (ZBX_DB_OK == ret)
		ret = sqlite3_changes(conn);

	if (0 == txn_level)
		php_sem_release(&sqlite_access);
#endif	/* HAVE_SQLITE3 */

	if (0 != CONFIG_LOG_SLOW_QUERIES)
	{
		sec = zbx_time() - sec;
		if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
			zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
	}

	if (ZBX_DB_FAIL == ret && 0 < txn_level)
	{
		zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
		txn_error = 1;
	}
clean:
	zbx_free(sql);

	return ret;
}
Пример #10
0
int
main(int argc, char *argv[])
{
	static struct option long_options[] = {
		{"host", required_argument, NULL, 'h'},
		{"port", required_argument, NULL, 'p'},
		{"username", required_argument, NULL, 'U'},
		{"no-password", no_argument, NULL, 'w'},
		{"password", no_argument, NULL, 'W'},
		{"echo", no_argument, NULL, 'e'},
		{"interactive", no_argument, NULL, 'i'},
		{NULL, 0, NULL, 0}
	};

	const char *progname;
	int			optindex;
	int			c;

	char	   *dbname = NULL;
	char	   *host = NULL;
	char	   *port = NULL;
	char	   *username = NULL;
	enum trivalue prompt_password = TRI_DEFAULT;
	bool		echo = false;
	bool		interactive = false;

	PQExpBufferData sql;

	PGconn	   *conn;
	PGresult   *result;

	progname = get_progname(argv[0]);
	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));

	handle_help_version_opts(argc, argv, "dropdb", help);

	while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
	{
		switch (c)
		{
			case 'h':
				host = optarg;
				break;
			case 'p':
				port = optarg;
				break;
			case 'U':
				username = optarg;
				break;
			case 'w':
				prompt_password = TRI_NO;
				break;
			case 'W':
				prompt_password = TRI_YES;
				break;
			case 'e':
				echo = true;
				break;
			case 'i':
				interactive = true;
				break;
			default:
				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
				exit(1);
		}
	}

	switch (argc - optind)
	{
		case 0:
			fprintf(stderr, _("%s: missing required argument database name\n"), progname);
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
			exit(1);
		case 1:
			dbname = argv[optind];
			break;
		default:
			fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
					progname, argv[optind + 1]);
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
			exit(1);
	}

	if (interactive)
	{
		printf(_("Database \"%s\" will be permanently removed.\n"), dbname);
		if (!yesno_prompt("Are you sure?"))
			exit(0);
	}

	initPQExpBuffer(&sql);

	appendPQExpBuffer(&sql, "DROP DATABASE %s;\n",
					  fmtId(dbname));

    /*
     * Connect to the 'postgres' database by default, except have
     * the 'postgres' user use 'template1' so he can drop the
     * 'postgres' database.
     */
	conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
						   host, port, username, prompt_password, progname);

	if (echo)
		printf("%s", sql.data);
	result = PQexec(conn, sql.data);
	if (PQresultStatus(result) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: database removal failed: %s"),
				progname, PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}

	PQclear(result);
	PQfinish(conn);
	exit(0);
}
Пример #11
0
bool QgsShapeFile::insertLayer( QString dbname, QString schema, QString primary_key, QString geom_col,
                                QString srid, PGconn * conn, QProgressDialog& pro, bool &fin,
                                QString& errorText )
{
  Q_UNUSED( dbname );
  connect( &pro, SIGNAL( canceled() ), this, SLOT( cancelImport() ) );
  import_canceled = false;
  bool result = true;

  QString query = QString( "CREATE TABLE %1.%2(%3 SERIAL PRIMARY KEY" )
                  .arg( QgsPgUtil::quotedIdentifier( schema ) )
                  .arg( QgsPgUtil::quotedIdentifier( table_name ) )
                  .arg( QgsPgUtil::quotedIdentifier( primary_key ) );

  for ( uint n = 0; n < column_names.size() && result; n++ )
  {
    query += QString( ",%1 %2" )
             .arg( QgsPgUtil::quotedIdentifier( column_names[n] ) )
             .arg( column_types[n] );
  }
  query += " )";

  QgsDebugMsg( "Query string is: " + query );

  PGresult *res = PQexec( conn, query.toUtf8() );

  if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
  {
    // flag error and send query and error message to stdout on debug
    errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
                 .arg( query ).arg( PQresultErrorMessage( res ) );
    PQclear( res );
    return false;
  }
  else
  {
    PQclear( res );
  }

  query = QString( "SELECT AddGeometryColumn(%1,%2,%3,%4,%5,2)" )
          .arg( QgsPgUtil::quotedValue( schema ) )
          .arg( QgsPgUtil::quotedValue( table_name ) )
          .arg( QgsPgUtil::quotedValue( geom_col ) )
          .arg( srid )
          .arg( QgsPgUtil::quotedValue( geom_type ) );

  res = PQexec( conn, query.toUtf8() );

  if ( PQresultStatus( res ) != PGRES_TUPLES_OK )
  {
    errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
                 .arg( query ).arg( PQresultErrorMessage( res ) );
    PQclear( res );
    return false;
  }
  else
  {
    PQclear( res );
  }

  if ( isMulti )
  {
    query = QString( "select constraint_name from information_schema.table_constraints where table_schema=%1 and table_name=%2 and constraint_name in ('$2','enforce_geotype_the_geom')" )
            .arg( QgsPgUtil::quotedValue( schema ) )
            .arg( QgsPgUtil::quotedValue( table_name ) );

    QStringList constraints;
    res = PQexec( conn, query.toUtf8() );
    if ( PQresultStatus( res ) == PGRES_TUPLES_OK )
    {
      for ( int i = 0; i < PQntuples( res ); i++ )
        constraints.append( PQgetvalue( res, i, 0 ) );
    }
    PQclear( res );

    if ( constraints.size() > 0 )
    {
      // drop the check constraint
      // TODO This whole concept needs to be changed to either
      // convert the geometries to the same type or allow
      // multiple types in the check constraint. For now, we
      // just drop the constraint...
      query = QString( "alter table %1 drop constraint %2" )
              .arg( QgsPgUtil::quotedIdentifier( table_name ) )
              .arg( QgsPgUtil::quotedIdentifier( constraints[0] ) );

      res = PQexec( conn, query.toUtf8() );
      if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
      {
        errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
                     .arg( query ).arg( PQresultErrorMessage( res ) );
        PQclear( res );
        return false;
      }

      PQclear( res );
    }

  }

  //adding the data into the table
  for ( int m = 0; m < features && result; m++ )
  {
    if ( import_canceled )
    {
      fin = true;
      break;
    }

    OGRFeatureH feat = OGR_L_GetNextFeature( ogrLayer );
    if ( feat )
    {
      OGRGeometryH geom = OGR_F_GetGeometryRef( feat );
      if ( geom )
      {
        query = QString( "INSERT INTO %1.%2(" )
                .arg( QgsPgUtil::quotedIdentifier( schema ) )
                .arg( QgsPgUtil::quotedIdentifier( table_name ) );
        QString values = " VALUES (";

        char *geo_temp;
        // 'GeometryFromText' supports only 2D coordinates
        // TODO for proper 2.5D support we would need to use 'GeomFromEWkt'
        if ( hasMoreDimensions )
          OGR_G_SetCoordinateDimension( geom, 2 );
        OGR_G_ExportToWkt( geom, &geo_temp );
        QString geometry( geo_temp );
        CPLFree( geo_temp );

        for ( uint n = 0; n < column_types.size(); n++ )
        {
          QString val;

          // FIXME: OGR_F_GetFieldAsString returns junk when called with a 8.255 float field
          if ( column_types[n] == "float" )
            val = QString::number( OGR_F_GetFieldAsDouble( feat, n ) );
          else
            val = codec->toUnicode( OGR_F_GetFieldAsString( feat, n ) );

          if ( val.isEmpty() )
            val = "NULL";
          else
            val = QgsPgUtil::quotedValue( val );

          if ( n > 0 )
          {
            query += ",";
            values += ",";
          }
          query += QgsPgUtil::quotedIdentifier( column_names[n] );
          values += val;
        }
        query += "," + QgsPgUtil::quotedIdentifier( geom_col );
        values += QString( ",st_geometryfromtext(%1,%2)" )
                  .arg( QgsPgUtil::quotedValue( geometry ) )
                  .arg( srid );

        query += ")" + values + ")";

        if ( result )
          res = PQexec( conn, query.toUtf8() );

        if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
        {
          // flag error and send query and error message to stdout on debug
          result = false;
          errorText += tr( "The database gave an error while executing this SQL:" ) + "\n";
          // the query string can be quite long. Trim if necessary...
          if ( query.count() > 100 )
            errorText += query.left( 150 ) +
                         tr( "... (rest of SQL trimmed)", "is appended to a truncated SQL statement" ) +
                         "\n";
          else
            errorText += query + '\n';
          errorText += tr( "The error was:\n%1\n" ).arg( PQresultErrorMessage( res ) );
          errorText += '\n';
        }
        else
        {
          PQclear( res );
        }

        pro.setValue( pro.value() + 1 );
        qApp->processEvents();
      }
      OGR_F_Destroy( feat );
    }
  }
  // create the GIST index if the the load was successful
  if ( result )
  {
    // prompt user to see if they want to build the index and warn
    // them about the potential time-cost
  }
  OGR_L_ResetReading( ogrLayer );
  return result;
}
Пример #12
0
/*
 * Actual code to make call to the database and print the output data.
 */
int
sql_exec(PGconn *conn, const char *todo, bool quiet)
{
	PGresult   *res;

	int			nfields;
	int			nrows;
	int			i,
				j,
				l;
	int		   *length;
	char	   *pad;

	/* make the call */
	res = PQexec(conn, todo);

	/* check and deal with errors */
	if (!res || PQresultStatus(res) > 2)
	{
		fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
		fprintf(stderr, "oid2name: query was: %s\n", todo);

		PQclear(res);
		PQfinish(conn);
		exit(-1);
	}

	/* get the number of fields */
	nrows = PQntuples(res);
	nfields = PQnfields(res);

	/* for each field, get the needed width */
	length = (int *) pg_malloc(sizeof(int) * nfields);
	for (j = 0; j < nfields; j++)
		length[j] = strlen(PQfname(res, j));

	for (i = 0; i < nrows; i++)
	{
		for (j = 0; j < nfields; j++)
		{
			l = strlen(PQgetvalue(res, i, j));
			if (l > length[j])
				length[j] = strlen(PQgetvalue(res, i, j));
		}
	}

	/* print a header */
	if (!quiet)
	{
		for (j = 0, l = 0; j < nfields; j++)
		{
			fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
			l += length[j] + 2;
		}
		fprintf(stdout, "\n");
		pad = (char *) pg_malloc(l + 1);
		MemSet(pad, '-', l);
		pad[l] = '\0';
		fprintf(stdout, "%s\n", pad);
		free(pad);
	}

	/* for each row, dump the information */
	for (i = 0; i < nrows; i++)
	{
		for (j = 0; j < nfields; j++)
			fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
		fprintf(stdout, "\n");
	}

	/* cleanup */
	PQclear(res);
	free(length);

	return 0;
}
Пример #13
0
static PGresult *c_psql_exec_query_noparams (c_psql_database_t *db,
		udb_query_t *q)
{
	return PQexec (db->conn, udb_query_get_statement (q));
} /* c_psql_exec_query_noparams */
Пример #14
0
void
GenSQL (PGconn *dbh,const char *file)
{
    int i,ntuples;
    int reverse=0;
    int type_check=0;
    int preamble=0;
    PGresult *result;
    Oid col_type;
    FILE *out;
    char token_data[TOKEN_DATA_LEN+1];
    char token_type[TOKEN_TYPE_LEN+1];
    unsigned long long token;
    memset((void *)token_type, 0, TOKEN_TYPE_LEN+1);
    memset((void *)token_data, 0, TOKEN_DATA_LEN+1);

    if (strncmp(file,"-",1)==0) {
        out=stdout;
    } else {
        if ( (out = fopen(file,"w+")) == NULL ) {
            fprintf(stderr, "Failed to open file %s for writing - %s\n",
                    file, strerror(errno));
            PQfinish(dbh);
            exit(EXIT_FAILURE);
        }
    }
    result = PQexec(dbh, "SELECT uid, token, spam_hits, innocent_hits, last_hit "
                    "FROM dspam_token_data");
    if (! result || PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Failed to run result: %s\n", PQresultErrorMessage(result));
        if (result) PQclear(result);
        PQfinish(dbh);
        fclose (out);
        exit(EXIT_FAILURE);
    }

    ntuples = PQntuples(result);
    for (i=0; i<ntuples; i++)
    {
        if (!type_check)
        {
            type_check = 1;
            col_type = PQftype(result, 1);

            if (col_type == BIGINTOID)
            {
                fprintf(stderr, "Datatype of dspam_token_data.token *not* NUMERIC;\n"
                        "assuming that you want to revert back to NUMERIC(20) type from BIGINT type\n");
                reverse=1;
            } else if (col_type != NUMERICOID)
            {
                fprintf(stderr, "Type of dspam_token_data.token is not BIGINT *or* NUMERIC(20)!\n"
                        "I have got no clue of how to deal with this and I am going to sulk now.\n");
                if (result) PQclear(result);
                PQfinish(dbh);
                fclose (out);
                exit(EXIT_FAILURE);
            }
        }
        if (!preamble)
        {
            preamble = 1;
            if (reverse == 0) {
                snprintf(token_type, TOKEN_TYPE_LEN, "bigint");
            } else {
                snprintf(token_type, TOKEN_TYPE_LEN, "numeric(20)");
            }
            fprintf(out,"BEGIN;\n"
                    "DROP TABLE dspam_token_data;\n"
                    "COMMIT;\n"
                    "BEGIN;\n"
                    "CREATE TABLE dspam_token_data (\n"
                    "  uid smallint,\n"
                    "  token %s,\n"
                    "  spam_hits int,\n"
                    "  innocent_hits int,\n"
                    "  last_hit date,\n"
                    "  UNIQUE (token, uid)\n"
                    ") WITHOUT OIDS;\n"
                    "COMMIT;\n"
                    "BEGIN;\n"
                    "COPY dspam_token_data (uid,token,spam_hits,innocent_hits,last_hit) FROM stdin;\n"
                    , token_type);
        }
        if (!reverse) {
            token = strtoull( PQgetvalue(result,i,1), NULL, 0);
            snprintf(token_data, TOKEN_DATA_LEN, "%lld", token);
        } else {
            token = (unsigned long long) strtoll( PQgetvalue(result,i,1), NULL, 0);
            snprintf(token_data, TOKEN_DATA_LEN, "%llu", token);
        }
        fprintf(out,"%s\t%s\t%s\t%s\t%s\n",
                PQgetvalue(result,i,0), token_data,
                PQgetvalue(result,i,2),
                PQgetvalue(result,i,3),
                PQgetvalue(result,i,4) );
    }
    if (result) PQclear(result);
    fprintf(out, "\\.\n\n"
            "COMMIT;\n"
            "BEGIN;\n"
            "CREATE INDEX id_token_data_03 ON dspam_token_data(token);\n"
            "CREATE INDEX id_token_data_04 ON dspam_token_data(uid);\n"
            "COMMIT;\n"
            "ANALYSE;\n");
    fclose (out);
}
Пример #15
0
/*
 * SendQuery: send the query string to the backend
 * (and print out results)
 *
 * Note: This is the "front door" way to send a query. That is, use it to
 * send queries actually entered by the user. These queries will be subject to
 * single step mode.
 * To send "back door" queries (generated by slash commands, etc.) in a
 * controlled way, use PSQLexec().
 *
 * Returns true if the query executed successfully, false otherwise.
 */
bool
SendQuery(const char *query)
{
	PGresult   *results;
	PGTransactionStatusType transaction_status;
	double		elapsed_msec = 0;
	bool		OK = false;
	bool		on_error_rollback_savepoint = false;
	static bool on_error_rollback_warning = false;

	if (!pset.db)
	{
		psql_error("You are currently not connected to a database.\n");
		goto sendquery_cleanup;
	}

	if (pset.singlestep)
	{
		char		buf[3];

		printf(_("***(Single step mode: verify command)*******************************************\n"
				 "%s\n"
				 "***(press return to proceed or enter x and return to cancel)********************\n"),
			   query);
		fflush(stdout);
		if (fgets(buf, sizeof(buf), stdin) != NULL)
			if (buf[0] == 'x')
				goto sendquery_cleanup;
	}
	else if (pset.echo == PSQL_ECHO_QUERIES)
	{
		puts(query);
		fflush(stdout);
	}

	if (pset.logfile)
	{
		fprintf(pset.logfile,
				_("********* QUERY **********\n"
				  "%s\n"
				  "**************************\n\n"), query);
		fflush(pset.logfile);
	}

	SetCancelConn();

	transaction_status = PQtransactionStatus(pset.db);

	if (transaction_status == PQTRANS_IDLE &&
		!pset.autocommit &&
		!command_no_begin(query))
	{
		results = PQexec(pset.db, "BEGIN");
		if (PQresultStatus(results) != PGRES_COMMAND_OK)
		{
			psql_error("%s", PQerrorMessage(pset.db));
			PQclear(results);
			ResetCancelConn();
			goto sendquery_cleanup;
		}
		PQclear(results);
		transaction_status = PQtransactionStatus(pset.db);
	}

	if (transaction_status == PQTRANS_INTRANS &&
		pset.on_error_rollback != PSQL_ERROR_ROLLBACK_OFF &&
		(pset.cur_cmd_interactive ||
		 pset.on_error_rollback == PSQL_ERROR_ROLLBACK_ON))
	{
		if (on_error_rollback_warning == false && pset.sversion < 80000)
		{
			psql_error("The server (version %d.%d) does not support savepoints for ON_ERROR_ROLLBACK.\n",
					   pset.sversion / 10000, (pset.sversion / 100) % 100);
			on_error_rollback_warning = true;
		}
		else
		{
			results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint");
			if (PQresultStatus(results) != PGRES_COMMAND_OK)
			{
				psql_error("%s", PQerrorMessage(pset.db));
				PQclear(results);
				ResetCancelConn();
				goto sendquery_cleanup;
			}
			PQclear(results);
			on_error_rollback_savepoint = true;
		}
	}

	if (pset.fetch_count <= 0 || !is_select_command(query))
	{
		/* Default fetch-it-all-and-print mode */
		instr_time	before,
					after;

		if (pset.timing)
			INSTR_TIME_SET_CURRENT(before);

		results = PQexec(pset.db, query);

		/* these operations are included in the timing result: */
		ResetCancelConn();
		OK = ProcessResult(&results);

		if (pset.timing)
		{
			INSTR_TIME_SET_CURRENT(after);
			INSTR_TIME_SUBTRACT(after, before);
			elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
		}

		/* but printing results isn't: */
		if (OK && results)
			OK = PrintQueryResults(results);
	}
	else
	{
		/* Fetch-in-segments mode */
		OK = ExecQueryUsingCursor(query, &elapsed_msec);
		ResetCancelConn();
		results = NULL;			/* PQclear(NULL) does nothing */
	}

	if (!OK && pset.echo == PSQL_ECHO_ERRORS)
		psql_error("STATEMENT:  %s\n", query);

	/* If we made a temporary savepoint, possibly release/rollback */
	if (on_error_rollback_savepoint)
	{
		const char *svptcmd = NULL;

		transaction_status = PQtransactionStatus(pset.db);

		switch (transaction_status)
		{
			case PQTRANS_INERROR:
				/* We always rollback on an error */
				svptcmd = "ROLLBACK TO pg_psql_temporary_savepoint";
				break;

			case PQTRANS_IDLE:
				/* If they are no longer in a transaction, then do nothing */
				break;

			case PQTRANS_INTRANS:

				/*
				 * Do nothing if they are messing with savepoints themselves:
				 * If the user did RELEASE or ROLLBACK, our savepoint is gone.
				 * If they issued a SAVEPOINT, releasing ours would remove
				 * theirs.
				 */
				if (results &&
					(strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
					 strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
					 strcmp(PQcmdStatus(results), "ROLLBACK") == 0))
					svptcmd = NULL;
				else
					svptcmd = "RELEASE pg_psql_temporary_savepoint";
				break;

			case PQTRANS_ACTIVE:
			case PQTRANS_UNKNOWN:
			default:
				OK = false;
				/* PQTRANS_UNKNOWN is expected given a broken connection. */
				if (transaction_status != PQTRANS_UNKNOWN || ConnectionUp())
					psql_error("unexpected transaction status (%d)\n",
							   transaction_status);
				break;
		}

		if (svptcmd)
		{
			PGresult   *svptres;

			svptres = PQexec(pset.db, svptcmd);
			if (PQresultStatus(svptres) != PGRES_COMMAND_OK)
			{
				psql_error("%s", PQerrorMessage(pset.db));
				PQclear(svptres);
				OK = false;

				PQclear(results);
				ResetCancelConn();
				goto sendquery_cleanup;
			}
			PQclear(svptres);
		}
	}

	PQclear(results);

	/* Possible microtiming output */
	if (pset.timing)
		printf(_("Time: %.3f ms\n"), elapsed_msec);

	/* check for events that may occur during query execution */

	if (pset.encoding != PQclientEncoding(pset.db) &&
		PQclientEncoding(pset.db) >= 0)
	{
		/* track effects of SET CLIENT_ENCODING */
		pset.encoding = PQclientEncoding(pset.db);
		pset.popt.topt.encoding = pset.encoding;
		SetVariable(pset.vars, "ENCODING",
					pg_encoding_to_char(pset.encoding));
	}

	PrintNotifications();

	/* perform cleanup that should occur after any attempted query */

sendquery_cleanup:

	/* reset \g's output-to-filename trigger */
	if (pset.gfname)
	{
		free(pset.gfname);
		pset.gfname = NULL;
	}

	/* reset \gset trigger */
	if (pset.gset_prefix)
	{
		free(pset.gset_prefix);
		pset.gset_prefix = NULL;
	}

	return OK;
}
Пример #16
0
/******************************************************************************
 *                                                                            *
 * Function: zbx_db_vselect                                                   *
 *                                                                            *
 * Purpose: execute a select statement                                        *
 *                                                                            *
 * Return value: data, NULL (on error) or (DB_RESULT)ZBX_DB_DOWN              *
 *                                                                            *
 ******************************************************************************/
DB_RESULT	zbx_db_vselect(const char *fmt, va_list args)
{
	char		*sql = NULL;
	DB_RESULT	result = NULL;
	double		sec = 0;

#if defined(HAVE_IBM_DB2)
	int		i;
	SQLRETURN	ret = SQL_SUCCESS;
#elif defined(HAVE_ORACLE)
	sword		err = OCI_SUCCESS;
	ub4		counter;
#elif defined(HAVE_POSTGRESQL)
	char		*error = NULL;
#elif defined(HAVE_SQLITE3)
	int		ret = FAIL;
	char		*error = NULL;
#endif

	if (0 != CONFIG_LOG_SLOW_QUERIES)
		sec = zbx_time();

	sql = zbx_dvsprintf(sql, fmt, args);

	if (1 == txn_error)
	{
		zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
		goto clean;
	}

	zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);

#if defined(HAVE_IBM_DB2)
	result = zbx_malloc(result, sizeof(ZBX_IBM_DB2_RESULT));
	memset(result, 0, sizeof(ZBX_IBM_DB2_RESULT));

	/* allocate a statement handle */
	if (SUCCEED != zbx_ibm_db2_success(ret = SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &result->hstmt)))
		goto error;

	/* directly execute the statement */
	if (SUCCEED != zbx_ibm_db2_success(ret = SQLExecDirect(result->hstmt, (SQLCHAR *)sql, SQL_NTS)))
		goto error;

	/* identify the number of output columns */
	if (SUCCEED != zbx_ibm_db2_success(ret = SQLNumResultCols(result->hstmt, &result->ncolumn)))
		goto error;

	if (0 == result->ncolumn)
		goto error;

	result->nalloc = 0;
	result->values = zbx_malloc(result->values, sizeof(char *) * result->ncolumn);
	result->values_cli = zbx_malloc(result->values_cli, sizeof(char *) * result->ncolumn);
	result->values_len = zbx_malloc(result->values_len, sizeof(SQLINTEGER) * result->ncolumn);

	for (i = 0; i < result->ncolumn; i++)
	{
		/* get the display size for a column */
		if (SUCCEED != zbx_ibm_db2_success(ret = SQLColAttribute(result->hstmt, (SQLSMALLINT)(i + 1),
				SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &result->values_len[i])))
		{
			goto error;
		}

		result->values_len[i] += 1; /* '\0'; */

		/* allocate memory to bind a column */
		result->values_cli[i] = zbx_malloc(NULL, result->values_len[i]);
		result->nalloc++;

		/* bind columns to program variables, converting all types to CHAR */
		if (SUCCEED != zbx_ibm_db2_success(ret = SQLBindCol(result->hstmt, (SQLSMALLINT)(i + 1),
				SQL_C_CHAR, result->values_cli[i], result->values_len[i], &result->values_len[i])))
		{
			goto error;
		}
	}
error:
	if (SUCCEED != zbx_ibm_db2_success(ret) || 0 == result->ncolumn)
	{
		zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
		zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, result->hstmt);

		IBM_DB2free_result(result);

		result = (SQL_CD_TRUE == IBM_DB2server_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
	}
#elif defined(HAVE_MYSQL)
	if (NULL == conn)
	{
		zabbix_errlog(ERR_Z3003);
		result = NULL;
	}
	else
	{
		if (0 != mysql_query(conn, sql))
		{
			zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
			switch (mysql_errno(conn))
			{
				case CR_CONN_HOST_ERROR:
				case CR_SERVER_GONE_ERROR:
				case CR_CONNECTION_ERROR:
				case CR_SERVER_LOST:
				case ER_SERVER_SHUTDOWN:
				case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
				case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
				case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
				case ER_UNKNOWN_ERROR:
					result = (DB_RESULT)ZBX_DB_DOWN;
					break;
				default:
					result = NULL;
					break;
			}
		}
		else
			result = mysql_store_result(conn);
	}
#elif defined(HAVE_ORACLE)
	result = zbx_malloc(NULL, sizeof(ZBX_OCI_DB_RESULT));
	memset(result, 0, sizeof(ZBX_OCI_DB_RESULT));

	err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&result->stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);

	if (OCI_SUCCESS == err)
	{
		err = OCIStmtPrepare(result->stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
				(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
	}

	if (OCI_SUCCESS == err)
	{
		err = OCIStmtExecute(oracle.svchp, result->stmthp, oracle.errhp, (ub4)0, (ub4)0,
				(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
	}

	if (OCI_SUCCESS == err)
	{
		/* get the number of columns in the query */
		err = OCIAttrGet((void *)result->stmthp, OCI_HTYPE_STMT, (void *)&result->ncolumn,
				  (ub4 *)0, OCI_ATTR_PARAM_COUNT, oracle.errhp);
	}

	if (OCI_SUCCESS != err)
		goto error;

	assert(0 < result->ncolumn);

	result->values = zbx_malloc(NULL, result->ncolumn * sizeof(char *));
	memset(result->values, 0, result->ncolumn * sizeof(char *));

	for (counter = 1; OCI_SUCCESS == err && counter <= result->ncolumn; counter++)
	{
		OCIParam	*parmdp = NULL;
		OCIDefine	*defnp = NULL;
		ub4		char_semantics;
		ub2		col_width;

		/* request a parameter descriptor in the select-list */
		err = OCIParamGet((void *)result->stmthp, OCI_HTYPE_STMT, oracle.errhp, (void **)&parmdp, (ub4)counter);

		if (OCI_SUCCESS == err)
		{
			/* retrieve the length semantics for the column */
			char_semantics = 0;
			err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&char_semantics, (ub4 *)0,
					(ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp);
		}

		if (OCI_SUCCESS == err)
		{
			col_width = 0;
			if (char_semantics)
			{
				/* retrieve the column width in characters */
				err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
						(ub4)OCI_ATTR_CHAR_SIZE, (OCIError *)oracle.errhp);
			}
			else
			{
				/* retrieve the column width in bytes */
				err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
						(ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp);
			}
		}
		col_width++;

		result->values[counter - 1] = zbx_malloc(NULL, col_width);
		memset(result->values[counter - 1], 0, col_width);

		if (OCI_SUCCESS == err)
		{
			/* represent any data as characters */
			err = OCIDefineByPos(result->stmthp, &defnp, oracle.errhp, counter,
					(dvoid *)result->values[counter - 1], col_width, SQLT_STR,
					(dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
		}

		/* free cell descriptor */
		OCIDescriptorFree(parmdp, OCI_DTYPE_PARAM);
		parmdp = NULL;
	}

error:
	if (OCI_SUCCESS != err)
	{
		zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);

		OCI_DBfree_result(result);

		result = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
	}
#elif defined(HAVE_POSTGRESQL)
	result = zbx_malloc(NULL, sizeof(ZBX_PG_DB_RESULT));
	result->pg_result = PQexec(conn, sql);
	result->values = NULL;
	result->cursor = 0;
	result->row_num = 0;

	if (NULL == result->pg_result)
		zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);

	if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
	{
		error = zbx_dsprintf(error, "%s:%s",
				PQresStatus(PQresultStatus(result->pg_result)),
				PQresultErrorMessage(result->pg_result));
		zabbix_errlog(ERR_Z3005, 0, error, sql);
		zbx_free(error);

		PG_DBfree_result(result);
		result = (CONNECTION_OK == PQstatus(conn) ? NULL : (DB_RESULT)ZBX_DB_DOWN);
	}
	else	/* init rownum */
		result->row_num = PQntuples(result->pg_result);
#elif defined(HAVE_SQLITE3)
	if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
	{
		zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
		exit(FAIL);
	}

	result = zbx_malloc(NULL, sizeof(ZBX_SQ_DB_RESULT));
	result->curow = 0;

lbl_get_table:
	if (SQLITE_OK != (ret = sqlite3_get_table(conn,sql, &result->data, &result->nrow, &result->ncolumn, &error)))
	{
		if (SQLITE_BUSY == ret)
			goto lbl_get_table;

		zabbix_errlog(ERR_Z3005, 0, error, sql);
		sqlite3_free(error);

		SQ_DBfree_result(result);

		switch (ret)
		{
			case SQLITE_ERROR:	/* SQL error or missing database; assuming SQL error, because if we
						   are this far into execution, zbx_db_connect() was successful */
			case SQLITE_NOMEM:	/* a malloc() failed */
			case SQLITE_MISMATCH:	/* data type mismatch */
				result = NULL;
				break;
			default:
				result = (DB_RESULT)ZBX_DB_DOWN;
				break;
		}
	}

	if (0 == txn_level)
		php_sem_release(&sqlite_access);
#endif	/* HAVE_SQLITE3 */

	if (0 != CONFIG_LOG_SLOW_QUERIES)
	{
		sec = zbx_time() - sec;
		if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
			zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
	}

	if (NULL == result && 0 < txn_level)
	{
		zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
		txn_error = 1;
	}
clean:
	zbx_free(sql);

	return result;
}
Пример #17
0
int center_manage_updatedata(){
    int i;

    PGconn *db_conn;
    PGresult *db_res;
    int db_count;
    int cacheid;
    center_jmod_info *jmod_info;
    int proid;
    int lang_flag;
    std::map<std::string,center_jmod_info*>::iterator jmod_it;
    center_pro_info *pro_info;

    std::vector<std::pair<int,int> > pro_list;

    if((db_conn = center_manage_conndb()) == NULL){
	return -1;
    }

    db_res = PQexec(db_conn,"SELECT DISTINCT \"jmodname\" FROM \"mod\";"); 
    if(PQresultStatus(db_res) != PGRES_TUPLES_OK){
	center_manage_closedb(db_conn);
	return -1;
    }

    db_count = PQntuples(db_res);
    for(i = 0;i < db_count;i++){
	jmod_info = new center_jmod_info(PQgetvalue(db_res,i,0),2);
	center_manage_jmodmap.insert(std::pair<std::string,center_jmod_info*>(jmod_info->name,jmod_info));
    }
    PQclear(db_res);

    db_res = PQexec(db_conn,"SELECT \"proid\",\"cacheid\",\"lang\",\"jmodname\" FROM \"problem\" INNER JOIN \"mod\" ON (\"problem\".\"modid\"=\"mod\".\"modid\");"); 
    if(PQresultStatus(db_res) != PGRES_TUPLES_OK){
	center_manage_closedb(db_conn);
	return -1;
    }

    db_count = PQntuples(db_res);
    for(i = 0;i < db_count;i++){
	sscanf(PQgetvalue(db_res,i,0),"%d",&proid);
	sscanf(PQgetvalue(db_res,i,1),"%d",&cacheid);
	sscanf(PQgetvalue(db_res,i,2),"%d",&lang_flag);
	if((jmod_it = center_manage_jmodmap.find(PQgetvalue(db_res,i,3))) == center_manage_jmodmap.end()){
	    continue;    
	}

	if(manage_updatepro(proid,cacheid,jmod_it->second,lang_flag) == 1){
	    pro_list.push_back(std::make_pair(proid,cacheid));
	    
	    printf("pro update %d %d\n",proid,cacheid);
	}
    }
    PQclear(db_res);

    if(!pro_list.empty()){
	center_judge_updatepro(pro_list);
    }

    center_manage_closedb(db_conn);
    return 0;
}
Пример #18
0
static void
BaseBackup(void)
{
	PGresult   *res;
	char	   *sysidentifier;
	uint32		timeline;
	char		current_path[MAXPGPATH];
	char		escaped_label[MAXPGPATH];
	int			i;
	char		xlogstart[64];
	char		xlogend[64];

	/*
	 * Connect in replication mode to the server
	 */
	conn = GetConnection();

	/*
	 * Run IDENTIFY_SYSTEM so we can get the timeline
	 */
	res = PQexec(conn, "IDENTIFY_SYSTEM");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not identify system: %s\n"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1)
	{
		fprintf(stderr, _("%s: could not identify system, got %i rows\n"),
				progname, PQntuples(res));
		disconnect_and_exit(1);
	}
	sysidentifier = strdup(PQgetvalue(res, 0, 0));
	timeline = atoi(PQgetvalue(res, 0, 1));
	PQclear(res);

	/*
	 * Start the actual backup
	 */
	PQescapeStringConn(conn, escaped_label, label, sizeof(escaped_label), &i);
	snprintf(current_path, sizeof(current_path), "BASE_BACKUP LABEL '%s' %s %s %s %s",
			 escaped_label,
			 showprogress ? "PROGRESS" : "",
			 includewal && !streamwal ? "WAL" : "",
			 fastcheckpoint ? "FAST" : "",
			 includewal ? "NOWAIT" : "");

	if (PQsendQuery(conn, current_path) == 0)
	{
		fprintf(stderr, _("%s: could not send base backup command: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}

	/*
	 * Get the starting xlog position
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not initiate base backup: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1)
	{
		fprintf(stderr, _("%s: no start point returned from server\n"),
				progname);
		disconnect_and_exit(1);
	}
	strcpy(xlogstart, PQgetvalue(res, 0, 0));
	if (verbose && includewal)
		fprintf(stderr, "xlog start point: %s\n", xlogstart);
	PQclear(res);
	MemSet(xlogend, 0, sizeof(xlogend));

	/*
	 * Get the header
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not get backup header: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) < 1)
	{
		fprintf(stderr, _("%s: no data returned from server\n"), progname);
		disconnect_and_exit(1);
	}

	/*
	 * Sum up the total size, for progress reporting
	 */
	totalsize = totaldone = 0;
	tablespacecount = PQntuples(res);
	for (i = 0; i < PQntuples(res); i++)
	{
		if (showprogress)
			totalsize += atol(PQgetvalue(res, i, 2));

		/*
		 * Verify tablespace directories are empty. Don't bother with the
		 * first once since it can be relocated, and it will be checked before
		 * we do anything anyway.
		 */
		if (format == 'p' && !PQgetisnull(res, i, 1))
			verify_dir_is_empty_or_create(PQgetvalue(res, i, 1));
	}

	/*
	 * When writing to stdout, require a single tablespace
	 */
	if (format == 't' && strcmp(basedir, "-") == 0 && PQntuples(res) > 1)
	{
		fprintf(stderr, _("%s: can only write single tablespace to stdout, database has %d\n"),
				progname, PQntuples(res));
		disconnect_and_exit(1);
	}

	/*
	 * If we're streaming WAL, start the streaming session before we start
	 * receiving the actual data chunks.
	 */
	if (streamwal)
	{
		if (verbose)
			fprintf(stderr, _("%s: starting background WAL receiver\n"),
					progname);
		StartLogStreamer(xlogstart, timeline, sysidentifier);
	}

	/*
	 * Start receiving chunks
	 */
	for (i = 0; i < PQntuples(res); i++)
	{
		if (format == 't')
			ReceiveTarFile(conn, res, i);
		else
			ReceiveAndUnpackTarFile(conn, res, i);
	}							/* Loop over all tablespaces */

	if (showprogress)
	{
		progress_report(PQntuples(res), NULL);
		fprintf(stderr, "\n");	/* Need to move to next line */
	}
	PQclear(res);

	/*
	 * Get the stop position
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not get WAL end position from server\n"),
				progname);
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1)
	{
		fprintf(stderr, _("%s: no WAL end position returned from server\n"),
				progname);
		disconnect_and_exit(1);
	}
	strcpy(xlogend, PQgetvalue(res, 0, 0));
	if (verbose && includewal)
		fprintf(stderr, "xlog end point: %s\n", xlogend);
	PQclear(res);

	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: final receive failed: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}

	if (bgchild > 0)
	{
#ifndef WIN32
		int			status;
		int			r;
#else
		DWORD       status;
#endif

		if (verbose)
			fprintf(stderr, _("%s: waiting for background process to finish streaming...\n"), progname);

#ifndef WIN32
		if (pipewrite(bgpipe[1], xlogend, strlen(xlogend)) != strlen(xlogend))
		{
			fprintf(stderr, _("%s: could not send command to background pipe: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}

		/* Just wait for the background process to exit */
		r = waitpid(bgchild, &status, 0);
		if (r == -1)
		{
			fprintf(stderr, _("%s: could not wait for child process: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (r != bgchild)
		{
			fprintf(stderr, _("%s: child %i died, expected %i\n"),
					progname, r, bgchild);
			disconnect_and_exit(1);
		}
		if (!WIFEXITED(status))
		{
			fprintf(stderr, _("%s: child process did not exit normally\n"),
					progname);
			disconnect_and_exit(1);
		}
		if (WEXITSTATUS(status) != 0)
		{
			fprintf(stderr, _("%s: child process exited with error %i\n"),
					progname, WEXITSTATUS(status));
			disconnect_and_exit(1);
		}
		/* Exited normally, we're happy! */
#else							/* WIN32 */

		/*
		 * On Windows, since we are in the same process, we can just store the
		 * value directly in the variable, and then set the flag that says
		 * it's there.
		 */
		if (sscanf(xlogend, "%X/%X", &xlogendptr.xlogid, &xlogendptr.xrecoff) != 2)
		{
			fprintf(stderr, _("%s: could not parse xlog end position \"%s\"\n"),
					progname, xlogend);
			exit(1);
		}
		InterlockedIncrement(&has_xlogendptr);

		/* First wait for the thread to exit */
		if (WaitForSingleObjectEx((HANDLE) bgchild, INFINITE, FALSE) != WAIT_OBJECT_0)
		{
			_dosmaperr(GetLastError());
			fprintf(stderr, _("%s: could not wait for child thread: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (GetExitCodeThread((HANDLE) bgchild, &status) == 0)
		{
			_dosmaperr(GetLastError());
			fprintf(stderr, _("%s: could not get child thread exit status: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (status != 0)
		{
			fprintf(stderr, _("%s: child thread exited with error %u\n"),
					progname, (unsigned int) status);
			disconnect_and_exit(1);
		}
		/* Exited normally, we're happy */
#endif
	}

	/*
	 * End of copy data. Final result is already checked inside the loop.
	 */
	PQfinish(conn);

	if (verbose)
		fprintf(stderr, "%s: base backup completed\n", progname);
}
Пример #19
0
static int submit_query(db_con_t* _h, const char* _s)
{	
	int rv;

	/*
	** this bit of nonsense in case our connection get screwed up 
	*/
	switch(rv = PQstatus(CON_CONNECTION(_h)))
	{
		case CONNECTION_OK: break;
		case CONNECTION_BAD:
			PLOG("submit_query", "connection reset");
			PQreset(CON_CONNECTION(_h));
		break;
	}

	/*
	** free any previous query that is laying about
	*/
	if(CON_RESULT(_h))
	{
		free_query(_h);
	}

	/*
	** exec the query
	*/
	CON_RESULT(_h) = PQexec(CON_CONNECTION(_h), _s);

	rv = 0;
	if(PQresultStatus(CON_RESULT(_h)) == 0)
	{
		PLOG("submit_query", "initial failure, FATAL");
		/* 
		** terrible error??
		*/
		rv = -3;
	}
	else
	{
		/*
		** the query ran, get the status
		*/
		switch(PQresultStatus(CON_RESULT(_h)))
		{
			case PGRES_EMPTY_QUERY: rv = -9; break;
			case PGRES_COMMAND_OK: rv = 0; break;
			case PGRES_TUPLES_OK: rv = 0; break;
			case PGRES_COPY_OUT: rv = -4; break;
			case PGRES_COPY_IN: rv = -5; break;
			case PGRES_BAD_RESPONSE: rv = -6; break;
			case PGRES_NONFATAL_ERROR: rv = -7; break;
			case PGRES_FATAL_ERROR: rv = -8; break;
			default: rv = -2; break;
		}
	}
	if(rv < 0)
	{
		/*
		** log the error
		*/
		char buf[256];
		sprintf(buf, "query '%s', result '%s'\n",
			_s, PQerrorMessage(CON_CONNECTION(_h)));
		PLOG("submit_query", buf);
	}

	return(rv);
}
Пример #20
0
int
main(int argc, char *argv[])
{
	static struct option long_options[] = {
		{"list", no_argument, NULL, 'l'},
		{"host", required_argument, NULL, 'h'},
		{"port", required_argument, NULL, 'p'},
		{"username", required_argument, NULL, 'U'},
		{"no-password", no_argument, NULL, 'w'},
		{"password", no_argument, NULL, 'W'},
		{"dbname", required_argument, NULL, 'd'},
		{"echo", no_argument, NULL, 'e'},
		{NULL, 0, NULL, 0}
	};

	const char *progname;
	int			optindex;
	int			c;

	bool		listlangs = false;
	const char *dbname = NULL;
	char	   *host = NULL;
	char	   *port = NULL;
	char	   *username = NULL;
	enum trivalue prompt_password = TRI_DEFAULT;
	bool		echo = false;
	char	   *langname = NULL;

	char	   *p;

	PQExpBufferData sql;

	PGconn	   *conn;
	PGresult   *result;

	progname = get_progname(argv[0]);
	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));

	handle_help_version_opts(argc, argv, "createlang", help);

	while ((c = getopt_long(argc, argv, "lh:p:U:wWd:e", long_options, &optindex)) != -1)
	{
		switch (c)
		{
			case 'l':
				listlangs = true;
				break;
			case 'h':
				host = optarg;
				break;
			case 'p':
				port = optarg;
				break;
			case 'U':
				username = optarg;
				break;
			case 'w':
				prompt_password = TRI_NO;
				break;
			case 'W':
				prompt_password = TRI_YES;
				break;
			case 'd':
				dbname = optarg;
				break;
			case 'e':
				echo = true;
				break;
			default:
				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
				exit(1);
		}
	}

	if (argc - optind > 0)
	{
		if (listlangs)
			dbname = argv[optind++];
		else
		{
			langname = argv[optind++];
			if (argc - optind > 0)
				dbname = argv[optind++];
		}
	}

	if (argc - optind > 0)
	{
		fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
				progname, argv[optind]);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
		exit(1);
	}

	if (dbname == NULL)
	{
		if (getenv("PGDATABASE"))
			dbname = getenv("PGDATABASE");
		else if (getenv("PGUSER"))
			dbname = getenv("PGUSER");
		else
			dbname = get_user_name(progname);
	}

	initPQExpBuffer(&sql);

	/*
	 * List option
	 */
	if (listlangs)
	{
		printQueryOpt popt;
		static const bool translate_columns[] = {false, true};

		conn = connectDatabase(dbname, host, port, username, prompt_password,
							   progname);

		printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", "
				"(CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as \"%s\" "
						  "FROM pg_catalog.pg_language WHERE lanispl;",
						  gettext_noop("Name"),
						  gettext_noop("yes"), gettext_noop("no"),
						  gettext_noop("Trusted?"));
		result = executeQuery(conn, sql.data, progname, echo);

		memset(&popt, 0, sizeof(popt));
		popt.topt.format = PRINT_ALIGNED;
		popt.topt.border = 1;
		popt.topt.start_table = true;
		popt.topt.stop_table = true;
		popt.topt.encoding = PQclientEncoding(conn);
		popt.title = _("Procedural Languages");
		popt.translate_header = true;
		popt.translate_columns = translate_columns;
		printQuery(result, &popt, stdout, NULL);

		PQfinish(conn);
		exit(0);
	}

	if (langname == NULL)
	{
		fprintf(stderr, _("%s: missing required argument language name\n"), progname);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
		exit(1);
	}

	for (p = langname; *p; p++)
		if (*p >= 'A' && *p <= 'Z')
			*p += ('a' - 'A');

	conn = connectDatabase(dbname, host, port, username, prompt_password, progname);

	/*
	 * Make sure the language isn't already installed
	 */
	printfPQExpBuffer(&sql,
			  "SELECT oid FROM pg_catalog.pg_language WHERE lanname = '%s';",
					  langname);
	result = executeQuery(conn, sql.data, progname, echo);
	if (PQntuples(result) > 0)
	{
		PQfinish(conn);
		fprintf(stderr,
		  _("%s: language \"%s\" is already installed in database \"%s\"\n"),
				progname, langname, dbname);
		/* separate exit status for "already installed" */
		exit(2);
	}
	PQclear(result);

	printfPQExpBuffer(&sql, "CREATE LANGUAGE \"%s\";\n", langname);

	if (echo)
		printf("%s", sql.data);
	result = PQexec(conn, sql.data);
	if (PQresultStatus(result) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: language installation failed: %s"),
				progname, PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}

	PQclear(result);
	PQfinish(conn);
	exit(0);
}
/*****************************************************
 * \brief Read a natural block of raster band data
 *****************************************************/
CPLErr PostGISRasterTileRasterBand::IReadBlock(CPL_UNUSED int nBlockXOff,
                                               CPL_UNUSED int nBlockYOff,
                                               void * pImage)
{
    CPLString osCommand;
    PGresult * poResult = NULL;
    int nWKBLength = 0;

    int nPixelSize = GDALGetDataTypeSize(eDataType)/8;

    PostGISRasterTileDataset * poRTDS =
        (PostGISRasterTileDataset *)poDS;
        
    // Get by PKID
    if (poRTDS->poRDS->pszPrimaryKeyName) {
        osCommand.Printf("select st_band(%s, %d) from %s.%s where "
            "%s = '%s'", poRTDS->poRDS->pszColumn, nBand, poRTDS->poRDS->pszSchema, poRTDS->poRDS->pszTable,
            poRTDS->poRDS->pszPrimaryKeyName, poRTDS->pszPKID);

    }
    
    // Get by upperleft
    else {
        CPLLocaleC oCLocale; // Force C locale to avoid commas instead of decimal points (for QGIS e.g.)
        osCommand.Printf("select st_band(%s, %d) from %s.%s where "
            "abs(ST_UpperLeftX(%s) - %.8f) < 1e-8 and abs(ST_UpperLeftY(%s) - %.8f) < 1e-8", 
            poRTDS->poRDS->pszColumn, nBand, poRTDS->poRDS->pszSchema, poRTDS->poRDS->pszTable, poRTDS->poRDS->pszColumn, 
            poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_X], poRTDS->poRDS->pszColumn, 
            poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_Y]);
        
    }
    
    poResult = PQexec(poRTDS->poRDS->poConn, osCommand.c_str());
    
#ifdef DEBUG_QUERY
    CPLDebug("PostGIS_Raster", "PostGISRasterTileRasterBand::IReadBlock(): "
             "Query = \"%s\" --> number of rows = %d",
             osCommand.c_str(), poResult ? PQntuples(poResult) : 0 );
#endif

    if (poResult == NULL || 
        PQresultStatus(poResult) != PGRES_TUPLES_OK ||
        PQntuples(poResult) <= 0) {
            
        if (poResult)
            PQclear(poResult);
            
        ReportError(CE_Failure, CPLE_AppDefined,
            "Error getting block of data (upperpixel = %f, %f)",
                poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_X], 
                poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_Y]);
            
        return CE_Failure;
    }


    // TODO: Check this
    if (bIsOffline) {
        CPLError(CE_Failure, CPLE_AppDefined, "This raster has outdb "
            "storage. This feature isn't still available");
        
        PQclear(poResult);    
        return CE_Failure;
    }
    
    /* Copy only data size, without payload */
    int nExpectedDataSize = 
        nBlockXSize * nBlockYSize * nPixelSize;
        
    GByte * pbyData = CPLHexToBinary(PQgetvalue(poResult, 0, 0), 
        &nWKBLength);
    int nExpectedWKBLength = RASTER_HEADER_SIZE + BAND_SIZE(nPixelSize, nExpectedDataSize);
    CPLErr eRet = CE_None;
    if( nWKBLength != nExpectedWKBLength )
    {
        CPLDebug("PostGIS_Raster", "nWKBLength=%d, nExpectedWKBLength=%d", nWKBLength, nExpectedWKBLength );
        eRet = CE_Failure;
    }
    else
    {
        GByte * pbyDataToRead = 
        (GByte*)GET_BAND_DATA(pbyData,1, nPixelSize, 
            nExpectedDataSize);

        // Do byte-swapping if necessary */
        int bIsLittleEndian = (pbyData[0] == 1);
#ifdef CPL_LSB
        int bSwap = !bIsLittleEndian;
#else
        int bSwap = bIsLittleEndian;
#endif
        if( bSwap && nPixelSize > 1 )
        {
            GDALSwapWords( pbyDataToRead, nPixelSize,
                           nBlockXSize * nBlockYSize,
                           nPixelSize );
        }

        memcpy(pImage, pbyDataToRead, nExpectedDataSize);
    }

    CPLFree(pbyData);
    PQclear(poResult);

    return eRet;
}
Пример #22
0
int main(int argc, char *argv[]) {
	/* First, we need to take in input from the items file. */
	if (argc != 6) {
		printf("Usage: workload total alpha beta gamma delta\n");
		printf("The values for alpha, beta, gamma and delta need to be integers that sum to 100.\n");
		exit(0);
	}
	int i, j, k, numrecs;
	// These are the parameters that come from the user.
	int total, nalpha, nbeta, ngamma, ndelta;
	// These represent thresholds.
	int talpha, tbeta, tgamma, tdelta;
	// These are derived parameters from the database.
	int alphacells, betacells, gammacells, deltacells;
	char **recs;
	PGconn *psql;

	AttributeInfo *head_alpha, *head_beta, *head_gamma, *head_delta;
	AttributeInfo *tail_alpha, *tail_beta, *tail_gamma, *tail_delta;
	AttributeInfo **alpha, **beta, **gamma, **delta;
	head_alpha = NULL; head_beta = NULL; head_gamma = NULL; head_delta = NULL;
	tail_alpha = NULL; tail_beta = NULL; tail_gamma = NULL; tail_delta = NULL;

	// Storing our parameters.
	total = atoi(argv[1]);
	nalpha = atoi(argv[2]);
	nbeta = atoi(argv[3]);
	ngamma = atoi(argv[4]);
	ndelta = atoi(argv[5]);

	// Establish thresholds for our RNG.
	tdelta = 100 - ndelta;
	tgamma = tdelta - ngamma;
	tbeta = tgamma - nbeta;
	talpha = 0;

	if (nalpha+nbeta+ngamma+ndelta != 100) {
		printf("The values for alpha, beta, gamma and delta need to be integers that sum to 100.\n");
		exit(0);
	}

	// Seeding our RNG.
	srand(time(NULL));

	// We start off by getting a recommender list.
	recs = recommenderList(&numrecs);
	printf("Numrecs: %d\n",numrecs);

	/* Connect to the database. */
	psql = PQconnectdb("host = 'localhost' port = '5432' dbname = 'recathon'");
	if (PQstatus(psql) != CONNECTION_OK) printf("bad conn\n");

	printf("%s, %s, %s, %s, %s\n",PQdb(psql), PQuser(psql), PQpass(psql), PQhost(psql), PQport(psql));
	if (psql == NULL) printf("connection failed\n");

	// Next, we need to query the index of each recommender, to get the attribute information and
	// cell types.
	for (i = 0; i < numrecs; i++) {
		char *querystring, *celltype;
		PGresult *query;
		int rows, cols;
		AttributeInfo *newatt;

		querystring = (char*) malloc(1024*sizeof(char));
		// Since we don't know all of the attributes, we need to request everything.
		sprintf(querystring,"select * from %sindex;",recs[i]);
		query = PQexec(psql,querystring);

		rows = PQntuples(query);
		cols = PQnfields(query);

		// A new AttributeInfo for each row.
		for (j = 0; j < rows; j++) {
			// Get query information. Cell type is attribute #8. Recommender-specific
			// attributes begin at #13.
			newatt = (AttributeInfo*) malloc(sizeof(AttributeInfo));
			newatt->next = NULL;

			newatt->recname = (char*) malloc(128*sizeof(char));
			sprintf(newatt->recname,"%s",recs[i]);

			newatt->numatts = cols - 12;
			newatt->attnames = (char**) malloc(newatt->numatts*sizeof(char*));
			for (k = 0; k < newatt->numatts; k++)
				newatt->attnames[k] = (char*) malloc(64*sizeof(char));
			newatt->attvalues = (char**) malloc(newatt->numatts*sizeof(char*));
			for (k = 0; k < newatt->numatts; k++)
				newatt->attvalues[k] = (char*) malloc(64*sizeof(char));

			celltype = PQgetvalue(query,j,7);
			if (strcmp(celltype,"Alpha") == 0)
				newatt->celltype = CELL_ALPHA;
			else if (strcmp(celltype,"Beta") == 0)
				newatt->celltype = CELL_BETA;
			else if (strcmp(celltype,"Gamma") == 0)
				newatt->celltype = CELL_GAMMA;
			else
				newatt->celltype = CELL_DELTA;

			// Get column information.
			for (k = 0; k < cols-12; k++) {
				sprintf(newatt->attnames[k],"%s",PQfname(query,k+12));
				sprintf(newatt->attvalues[k],"%s",PQgetvalue(query,j,k+12));
			}

			// With the item complete, we put it into the appropriate bucket.
			switch (newatt->celltype) {
				case CELL_ALPHA:
					if (!head_alpha) {
						head_alpha = newatt;
						tail_alpha = newatt;
					} else {
						tail_alpha->next = newatt;
						tail_alpha = newatt;
					}
					break;
				case CELL_BETA:
					if (!head_beta) {
						head_beta = newatt;
						tail_beta = newatt;
					} else {
						tail_beta->next = newatt;
						tail_beta = newatt;
					}
					break;
				case CELL_GAMMA:
					if (!head_gamma) {
						head_gamma = newatt;
						tail_gamma = newatt;
					} else {
						tail_gamma->next = newatt;
						tail_gamma = newatt;
					}
					break;
				default:
					if (!head_delta) {
						head_delta = newatt;
						tail_delta = newatt;
					} else {
						tail_delta->next = newatt;
						tail_delta = newatt;
					}
					break;
			}
		}
		PQclear(query);
		free(querystring);
	}

	// For easy randomization, we should flatten our AttributeInfo lists.
	alpha = flatten(head_alpha, &alphacells);
	beta = flatten(head_beta, &betacells);
	gamma = flatten(head_gamma, &gammacells);
	delta = flatten(head_delta, &deltacells);

	// DEBUG: loop through the lists of alpha/beta/gamma/delta cells and print info.
	if (DEBUG) {
		printf("--- ALPHA CELLS ---\n");
		printCellList(alpha, alphacells);
		printf("--- BETA CELLS ---\n");
		printCellList(beta, betacells);
		printf("--- GAMMA CELLS ---\n");
		printCellList(gamma, gammacells);
		printf("--- DELTA CELLS ---\n");
		printCellList(delta, deltacells);
	}

	// One more thing we need to do is obtain a list of users that will work for
	// each AttributeInfo. We can semi-randomize by sorting based on zip code.
	addUsers(alpha, alphacells, psql);
	addUsers(beta, betacells, psql);
	addUsers(gamma, gammacells, psql);
	addUsers(delta, deltacells, psql);

	// Now to issue the given number of queries, with the frequencies established
	// probabilistically.
	for (i = 0; i < total; i++) {
		int randnum, randatt, randuser, userid;
		recathon_cell celltype;
		bool valid = false;
		PGresult *workquery;
		char *qstring;
		AttributeInfo *queryatt;

		// It's possible one of our buckets will have nothing in it, so
		// we need to continue choosing until we get something valid.
		while (!valid) {
			// A RNG chooses which kind of cell we work with.
			randnum = rand() % 100;
			if (randnum < tbeta) {
				if (alphacells > 0) {
					valid = true;
					celltype = CELL_ALPHA;
				}
			}
			else if (randnum < tgamma) {
				if (betacells > 0) {
					valid = true;
					celltype = CELL_BETA;
				}
			}
			else if (randnum < tdelta) {
				if (gammacells > 0) {
					valid = true;
					celltype = CELL_GAMMA;
				}
			}
			else {
				if (deltacells > 0) {
					valid = true;
					celltype = CELL_DELTA;
				}
			}
		}

		// Depending on our cell type, we'll have a different set of possible
		// queries to issue; we can choose from the alpha, beta, gamma or delta
		// buckets. Which item we get is also random.
		switch (celltype) {
			case CELL_ALPHA:
				randatt = rand() % alphacells;
				queryatt = alpha[randatt];
				break;
			case CELL_BETA:
				randatt = rand() % betacells;
				queryatt = beta[randatt];
				break;
			case CELL_GAMMA:
				randatt = rand() % gammacells;
				queryatt = gamma[randatt];
				break;
			default:
				randatt = rand() % deltacells;
				queryatt = delta[randatt];
				break;
		}

		randuser = rand() % 10;
		userid = queryatt->valid_users[randuser];
		qstring = (char*) malloc(1024*sizeof(char));
		sprintf(qstring,"select itemid from %s recommend(10) userid=%d",queryatt->recname,userid);
		if (queryatt->numatts > 0) {
			strncat(qstring," and ",5);
			for (j = 0; j < queryatt->numatts; j++) {
				char addition[128];
				sprintf(addition,"%s = '%s' ",
					queryatt->attnames[j],queryatt->attvalues[j]);
				strncat(qstring,addition,strlen(addition));
				if (j+1 < queryatt->numatts)
					strncat(qstring,"and ",5);
			}
		}
		strncat(qstring,";",1);

		workquery = PQexec(psql,qstring);
		PQclear(workquery);
		free(qstring);
	}
	PQfinish(psql);
}
Пример #23
0
/**
 \brief Add a new license to license_ref table

 Adds a license to license_ref table.

 @param  licenseName Name of license

 @return rf_pk for success, 0 for failure
 */
FUNCTION long add2license_ref(char *licenseName)
{

  PGresult *result;
  char query[myBUFSIZ];
  char insert[myBUFSIZ];
  char escLicName[myBUFSIZ];
  char *specialLicenseText;
  long rf_pk;

  int len;
  int error;
  int numRows;

  // escape the name
  len = strlen(licenseName);
  PQescapeStringConn(gl.pgConn, escLicName, licenseName, len, &error);
  if (error)
  LOG_WARNING("Does license name %s have multibyte encoding?", licenseName)

  /* verify the license is not already in the table */
  sprintf(query, "SELECT rf_pk FROM " LICENSE_REF_TABLE " where rf_shortname='%s'", escLicName);
  result = PQexec(gl.pgConn, query);
  if (fo_checkPQresult(gl.pgConn, result, query, __FILE__, __LINE__))
    return 0;
  numRows = PQntuples(result);
  if (numRows)
  {
    rf_pk = atol(PQgetvalue(result, 0, 0));
    PQclear(result);
    return rf_pk;
  }
  PQclear(result);

  /* Insert the new license */
  specialLicenseText = "License by Nomos.";

  sprintf(insert, "insert into license_ref(rf_shortname, rf_text, rf_detector_type) values('%s', '%s', 2)", escLicName,
      specialLicenseText);
  result = PQexec(gl.pgConn, insert);
  // ignore duplicate constraint failure (23505), report others
  if ((result == 0)
      || ((PQresultStatus(result) != PGRES_COMMAND_OK)
          && (strncmp(PG_ERRCODE_UNIQUE_VIOLATION, PQresultErrorField(result, PG_DIAG_SQLSTATE), 5))))
  {
    printf("ERROR: %s(%d): Nomos failed to add a new license. %s/n: %s/n",
    __FILE__, __LINE__, PQresultErrorMessage(result), insert);
    PQclear(result);
    return (0);
  }
  PQclear(result);

  /* retrieve the new rf_pk */
  result = PQexec(gl.pgConn, query);
  if (fo_checkPQresult(gl.pgConn, result, query, __FILE__, __LINE__))
    return 0;
  numRows = PQntuples(result);
  if (numRows)
    rf_pk = atol(PQgetvalue(result, 0, 0));
  else
  {
    printf("ERROR: %s:%s:%d Just inserted value is missing. On: %s", __FILE__, "add2license_ref()", __LINE__, query);
    PQclear(result);
    return (0);
  }
  PQclear(result);

  return (rf_pk);
}
Пример #24
0
static void
BaseBackup(void)
{
	PGresult   *res;
	char	   *sysidentifier;
	uint32		latesttli;
	uint32		starttli;
	char		current_path[MAXPGPATH];
	char		escaped_label[MAXPGPATH];
	int			i;
	char		xlogstart[64];
	char		xlogend[64];
	int			minServerMajor,
				maxServerMajor;
	int			serverMajor;

	/*
	 * Connect in replication mode to the server
	 */
	conn = GetConnection();
	if (!conn)
		/* Error message already written in GetConnection() */
		exit(1);

	/*
	 * Check server version. BASE_BACKUP command was introduced in 9.1, so we
	 * can't work with servers older than 9.1.
	 */
	minServerMajor = 901;
	maxServerMajor = PG_VERSION_NUM / 100;
	serverMajor = PQserverVersion(conn) / 100;
	if (serverMajor < minServerMajor || serverMajor > maxServerMajor)
	{
		const char *serverver = PQparameterStatus(conn, "server_version");

		fprintf(stderr, _("%s: incompatible server version %s\n"),
				progname, serverver ? serverver : "'unknown'");
		disconnect_and_exit(1);
	}

	/*
	 * If WAL streaming was requested, also check that the server is new
	 * enough for that.
	 */
	if (streamwal && !CheckServerVersionForStreaming(conn))
	{
		/* Error message already written in CheckServerVersionForStreaming() */
		disconnect_and_exit(1);
	}

	/*
	 * Build contents of recovery.conf if requested
	 */
	if (writerecoveryconf)
		GenerateRecoveryConf(conn);

	/*
	 * Run IDENTIFY_SYSTEM so we can get the timeline
	 */
	res = PQexec(conn, "IDENTIFY_SYSTEM");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
				progname, "IDENTIFY_SYSTEM", PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1 || PQnfields(res) != 3)
	{
		fprintf(stderr,
				_("%s: could not identify system: got %d rows and %d fields, expected %d rows and %d fields\n"),
				progname, PQntuples(res), PQnfields(res), 1, 3);
		disconnect_and_exit(1);
	}
	sysidentifier = pg_strdup(PQgetvalue(res, 0, 0));
	latesttli = atoi(PQgetvalue(res, 0, 1));
	PQclear(res);

	/*
	 * Start the actual backup
	 */
	PQescapeStringConn(conn, escaped_label, label, sizeof(escaped_label), &i);
	snprintf(current_path, sizeof(current_path),
			 "BASE_BACKUP LABEL '%s' %s %s %s %s",
			 escaped_label,
			 showprogress ? "PROGRESS" : "",
			 includewal && !streamwal ? "WAL" : "",
			 fastcheckpoint ? "FAST" : "",
			 includewal ? "NOWAIT" : "");

	if (PQsendQuery(conn, current_path) == 0)
	{
		fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
				progname, "BASE_BACKUP", PQerrorMessage(conn));
		disconnect_and_exit(1);
	}

	/*
	 * Get the starting xlog position
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not initiate base backup: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1)
	{
		fprintf(stderr,
				_("%s: server returned unexpected response to BASE_BACKUP command; got %d rows and %d fields, expected %d rows and %d fields\n"),
				progname, PQntuples(res), PQnfields(res), 1, 2);
		disconnect_and_exit(1);
	}

	strcpy(xlogstart, PQgetvalue(res, 0, 0));

	/*
	 * 9.3 and later sends the TLI of the starting point. With older servers,
	 * assume it's the same as the latest timeline reported by
	 * IDENTIFY_SYSTEM.
	 */
	if (PQnfields(res) >= 2)
		starttli = atoi(PQgetvalue(res, 0, 1));
	else
		starttli = latesttli;
	PQclear(res);
	MemSet(xlogend, 0, sizeof(xlogend));

	if (verbose && includewal)
		fprintf(stderr, _("transaction log start point: %s on timeline %u\n"),
				xlogstart, starttli);

	/*
	 * Get the header
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not get backup header: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) < 1)
	{
		fprintf(stderr, _("%s: no data returned from server\n"), progname);
		disconnect_and_exit(1);
	}

	/*
	 * Sum up the total size, for progress reporting
	 */
	totalsize = totaldone = 0;
	tablespacecount = PQntuples(res);
	for (i = 0; i < PQntuples(res); i++)
	{
		if (showprogress)
			totalsize += atol(PQgetvalue(res, i, 2));

		/*
		 * Verify tablespace directories are empty. Don't bother with the
		 * first once since it can be relocated, and it will be checked before
		 * we do anything anyway.
		 */
		if (format == 'p' && !PQgetisnull(res, i, 1))
			verify_dir_is_empty_or_create(PQgetvalue(res, i, 1));
	}

	/*
	 * When writing to stdout, require a single tablespace
	 */
	if (format == 't' && strcmp(basedir, "-") == 0 && PQntuples(res) > 1)
	{
		fprintf(stderr,
				_("%s: can only write single tablespace to stdout, database has %d\n"),
				progname, PQntuples(res));
		disconnect_and_exit(1);
	}

	/*
	 * If we're streaming WAL, start the streaming session before we start
	 * receiving the actual data chunks.
	 */
	if (streamwal)
	{
		if (verbose)
			fprintf(stderr, _("%s: starting background WAL receiver\n"),
					progname);
		StartLogStreamer(xlogstart, starttli, sysidentifier);
	}

	/*
	 * Start receiving chunks
	 */
	for (i = 0; i < PQntuples(res); i++)
	{
		if (format == 't')
			ReceiveTarFile(conn, res, i);
		else
			ReceiveAndUnpackTarFile(conn, res, i);
	}							/* Loop over all tablespaces */

	if (showprogress)
	{
		progress_report(PQntuples(res), NULL);
		fprintf(stderr, "\n");	/* Need to move to next line */
	}
	PQclear(res);

	/*
	 * Get the stop position
	 */
	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr,
		 _("%s: could not get transaction log end position from server: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1)
	{
		fprintf(stderr,
			 _("%s: no transaction log end position returned from server\n"),
				progname);
		disconnect_and_exit(1);
	}
	strcpy(xlogend, PQgetvalue(res, 0, 0));
	if (verbose && includewal)
		fprintf(stderr, "transaction log end point: %s\n", xlogend);
	PQclear(res);

	res = PQgetResult(conn);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: final receive failed: %s"),
				progname, PQerrorMessage(conn));
		disconnect_and_exit(1);
	}

	if (bgchild > 0)
	{
#ifndef WIN32
		int			status;
		int			r;
#else
		DWORD		status;
		uint32		hi,
					lo;
#endif

		if (verbose)
			fprintf(stderr,
					_("%s: waiting for background process to finish streaming ...\n"), progname);

#ifndef WIN32
		if (write(bgpipe[1], xlogend, strlen(xlogend)) != strlen(xlogend))
		{
			fprintf(stderr,
					_("%s: could not send command to background pipe: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}

		/* Just wait for the background process to exit */
		r = waitpid(bgchild, &status, 0);
		if (r == -1)
		{
			fprintf(stderr, _("%s: could not wait for child process: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (r != bgchild)
		{
			fprintf(stderr, _("%s: child %d died, expected %d\n"),
					progname, r, (int) bgchild);
			disconnect_and_exit(1);
		}
		if (!WIFEXITED(status))
		{
			fprintf(stderr, _("%s: child process did not exit normally\n"),
					progname);
			disconnect_and_exit(1);
		}
		if (WEXITSTATUS(status) != 0)
		{
			fprintf(stderr, _("%s: child process exited with error %d\n"),
					progname, WEXITSTATUS(status));
			disconnect_and_exit(1);
		}
		/* Exited normally, we're happy! */
#else							/* WIN32 */

		/*
		 * On Windows, since we are in the same process, we can just store the
		 * value directly in the variable, and then set the flag that says
		 * it's there.
		 */
		if (sscanf(xlogend, "%X/%X", &hi, &lo) != 2)
		{
			fprintf(stderr,
				  _("%s: could not parse transaction log location \"%s\"\n"),
					progname, xlogend);
			disconnect_and_exit(1);
		}
		xlogendptr = ((uint64) hi) << 32 | lo;
		InterlockedIncrement(&has_xlogendptr);

		/* First wait for the thread to exit */
		if (WaitForSingleObjectEx((HANDLE) bgchild, INFINITE, FALSE) !=
			WAIT_OBJECT_0)
		{
			_dosmaperr(GetLastError());
			fprintf(stderr, _("%s: could not wait for child thread: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (GetExitCodeThread((HANDLE) bgchild, &status) == 0)
		{
			_dosmaperr(GetLastError());
			fprintf(stderr, _("%s: could not get child thread exit status: %s\n"),
					progname, strerror(errno));
			disconnect_and_exit(1);
		}
		if (status != 0)
		{
			fprintf(stderr, _("%s: child thread exited with error %u\n"),
					progname, (unsigned int) status);
			disconnect_and_exit(1);
		}
		/* Exited normally, we're happy */
#endif
	}

	/* Free the recovery.conf contents */
	destroyPQExpBuffer(recoveryconfcontents);

	/*
	 * End of copy data. Final result is already checked inside the loop.
	 */
	PQclear(res);
	PQfinish(conn);

	if (verbose)
		fprintf(stderr, "%s: base backup completed\n", progname);
}
Пример #25
0
void load_TUBii_command(client *c, int argc, sds *argv)
{
    /* Load CAEN hardware settings from the database. */
    uint32_t key;
    PGconn *conn;
    PGresult *res = NULL;
    char conninfo[1024];
    char command[10000];
    char *name, *value_str;
    uint32_t value;
    int i;
    int rows;

    if (safe_strtoul(argv[1], &key)) {
        addReplyErrorFormat(c, "'%s' is not a valid uint32_t", argv[1]);
        return;
    }

    sprintf(command, "select * from TUBii where key = %i", key);

    sprintf(conninfo, "dbname=%s host=%s user=%s password=%s", dbconfig.name, dbconfig.host, dbconfig.user, dbconfig.password);

    /* Request row from the database. */
    conn = PQconnectdb(conninfo);

    if (PQstatus(conn) != CONNECTION_OK) {
        addReplyErrorFormat(c, "connection to database failed: %s",
                            PQerrorMessage(conn));
        goto pq_error;
    }

    res = PQexec(conn, command);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        addReplyErrorFormat(c, "select command failed: %s",
                            PQerrorMessage(conn));
        goto pq_error;
    }

    rows = PQntuples(res);
    if (rows != 1) {
        if (rows == 0) {
            addReplyErrorFormat(c, "no database row with key = %i", key);
        } else {
            addReplyError(c, "this should never happen. Call Tony");
        }
        goto pq_error;
    }

    for (i = 0; i < PQnfields(res); i++) {
        name = PQfname(res, i);

        if (!strcmp(name, "key") || !strcmp(name, "timestamp")) continue;

        value_str = PQgetvalue(res, 0, i);

        if (safe_strtoul(value_str, &value)) {
            addReplyErrorFormat(c, "unable to convert value '%s' for field %s",
                                value_str, name);
            goto pq_error;
        }

        if (!strcmp(name, "control_reg")) {
        	ControlReg(value);
        } else if (!strcmp(name, "trigger_mask")) {
        	triggerMask(value,0);
        } else if (!strcmp(name, "speaker_mask")) {
            speakerMask(value);
        } else if (!strcmp(name, "counter_mask")) {
            counterMask(value);
        } else if (!strcmp(name, "caen_gain_reg")) {
        	CAENWords(value, mReadReg((u32) MappedRegsBaseAddress, RegOffset12));
        } else if (!strcmp(name, "caen_channel_reg")) {
        	CAENWords(mReadReg((u32) MappedRegsBaseAddress, RegOffset11), value);
        } else if (!strcmp(name, "lockout_reg")) {
        	GTDelays(value, mReadReg((u32) MappedRegsBaseAddress, RegOffset15));
        } else if (!strcmp(name, "dgt_reg")) {
        	GTDelays(mReadReg((u32) MappedRegsBaseAddress, RegOffset14), value);
        } else if (!strcmp(name, "dac_reg")) {
        	DACThresholds(value);
        } else if (!strcmp(name, "counter_mode")) {
        	counterMode(value);
        } else if (!strcmp(name, "clock_status")) {
        	// Do Nowt
        } else if (!strcmp(name, "combo_enable_mask")) {
        	mWriteReg((u32) MappedComboBaseAddress, RegOffset2, value);
        } else if (!strcmp(name, "combo_mask")) {
        	mWriteReg((u32) MappedComboBaseAddress, RegOffset3, value);
        } else if (!strcmp(name, "prescale_value")) {
        	mWriteReg((u32) MappedPrescaleBaseAddress, RegOffset2, value);
        } else if (!strcmp(name, "prescale_channel")) {
        	mWriteReg((u32) MappedPrescaleBaseAddress, RegOffset3, value);
        } else if (!strcmp(name, "burst_rate")) {
        	mWriteReg((u32) MappedBurstBaseAddress, RegOffset2, value);
        } else if (!strcmp(name, "burst_channel")) {
        	mWriteReg((u32) MappedBurstBaseAddress, RegOffset3, value);
        } else {
            addReplyErrorFormat(c, "got unknown field '%s'", name);
            goto pq_error;
        }
    }

    addReplyStatus(c, "OK");

    PQclear(res);
    PQfinish(conn);

    return;

err:
 	addReplyError(c, tubii_err);
 	return;

pq_error:
    if (res) PQclear(res);
    PQfinish(conn);
}
Пример #26
0
/*
 * ExecQueryUsingCursor: run a SELECT-like query using a cursor
 *
 * This feature allows result sets larger than RAM to be dealt with.
 *
 * Returns true if the query executed successfully, false otherwise.
 *
 * If pset.timing is on, total query time (exclusive of result-printing) is
 * stored into *elapsed_msec.
 */
static bool
ExecQueryUsingCursor(const char *query, double *elapsed_msec)
{
	bool		OK = true;
	PGresult   *results;
	PQExpBufferData buf;
	printQueryOpt my_popt = pset.popt;
	FILE	   *fout;
	bool		is_pipe;
	bool		is_pager = false;
	bool		started_txn = false;
	int			ntuples;
	int			fetch_count;
	char		fetch_cmd[64];
	instr_time	before,
				after;
	int			flush_error;

	*elapsed_msec = 0;

	/* initialize print options for partial table output */
	my_popt.topt.start_table = true;
	my_popt.topt.stop_table = false;
	my_popt.topt.prior_records = 0;

	if (pset.timing)
		INSTR_TIME_SET_CURRENT(before);

	/* if we're not in a transaction, start one */
	if (PQtransactionStatus(pset.db) == PQTRANS_IDLE)
	{
		results = PQexec(pset.db, "BEGIN");
		OK = AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
		PQclear(results);
		if (!OK)
			return false;
		started_txn = true;
	}

	/* Send DECLARE CURSOR */
	initPQExpBuffer(&buf);
	appendPQExpBuffer(&buf, "DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s",
					  query);

	results = PQexec(pset.db, buf.data);
	OK = AcceptResult(results) &&
		(PQresultStatus(results) == PGRES_COMMAND_OK);
	PQclear(results);
	termPQExpBuffer(&buf);
	if (!OK)
		goto cleanup;

	if (pset.timing)
	{
		INSTR_TIME_SET_CURRENT(after);
		INSTR_TIME_SUBTRACT(after, before);
		*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
	}

	/*
	 * In \gset mode, we force the fetch count to be 2, so that we will throw
	 * the appropriate error if the query returns more than one row.
	 */
	if (pset.gset_prefix)
		fetch_count = 2;
	else
		fetch_count = pset.fetch_count;

	snprintf(fetch_cmd, sizeof(fetch_cmd),
			 "FETCH FORWARD %d FROM _psql_cursor",
			 fetch_count);

	/* prepare to write output to \g argument, if any */
	if (pset.gfname)
	{
		if (!openQueryOutputFile(pset.gfname, &fout, &is_pipe))
		{
			OK = false;
			goto cleanup;
		}
		if (is_pipe)
			disable_sigpipe_trap();
	}
	else
	{
		fout = pset.queryFout;
		is_pipe = false;		/* doesn't matter */
	}

	/* clear any pre-existing error indication on the output stream */
	clearerr(fout);

	for (;;)
	{
		if (pset.timing)
			INSTR_TIME_SET_CURRENT(before);

		/* get fetch_count tuples at a time */
		results = PQexec(pset.db, fetch_cmd);

		if (pset.timing)
		{
			INSTR_TIME_SET_CURRENT(after);
			INSTR_TIME_SUBTRACT(after, before);
			*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
		}

		if (PQresultStatus(results) != PGRES_TUPLES_OK)
		{
			/* shut down pager before printing error message */
			if (is_pager)
			{
				ClosePager(fout);
				is_pager = false;
			}

			OK = AcceptResult(results);
			Assert(!OK);
			PQclear(results);
			break;
		}

		if (pset.gset_prefix)
		{
			/* StoreQueryTuple will complain if not exactly one row */
			OK = StoreQueryTuple(results);
			PQclear(results);
			break;
		}

		ntuples = PQntuples(results);

		if (ntuples < fetch_count)
		{
			/* this is the last result set, so allow footer decoration */
			my_popt.topt.stop_table = true;
		}
		else if (fout == stdout && !is_pager)
		{
			/*
			 * If query requires multiple result sets, hack to ensure that
			 * only one pager instance is used for the whole mess
			 */
			fout = PageOutput(INT_MAX, &(my_popt.topt));
			is_pager = true;
		}

		printQuery(results, &my_popt, fout, is_pager, pset.logfile);

		PQclear(results);

		/* after the first result set, disallow header decoration */
		my_popt.topt.start_table = false;
		my_popt.topt.prior_records += ntuples;

		/*
		 * Make sure to flush the output stream, so intermediate results are
		 * visible to the client immediately.  We check the results because if
		 * the pager dies/exits/etc, there's no sense throwing more data at
		 * it.
		 */
		flush_error = fflush(fout);

		/*
		 * Check if we are at the end, if a cancel was pressed, or if there
		 * were any errors either trying to flush out the results, or more
		 * generally on the output stream at all.  If we hit any errors
		 * writing things to the stream, we presume $PAGER has disappeared and
		 * stop bothering to pull down more data.
		 */
		if (ntuples < fetch_count || cancel_pressed || flush_error ||
			ferror(fout))
			break;
	}

	if (pset.gfname)
	{
		/* close \g argument file/pipe */
		if (is_pipe)
		{
			pclose(fout);
			restore_sigpipe_trap();
		}
		else
			fclose(fout);
	}
	else if (is_pager)
	{
		/* close transient pager */
		ClosePager(fout);
	}

cleanup:
	if (pset.timing)
		INSTR_TIME_SET_CURRENT(before);

	/*
	 * We try to close the cursor on either success or failure, but on failure
	 * ignore the result (it's probably just a bleat about being in an aborted
	 * transaction)
	 */
	results = PQexec(pset.db, "CLOSE _psql_cursor");
	if (OK)
	{
		OK = AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
	}
	PQclear(results);

	if (started_txn)
	{
		results = PQexec(pset.db, OK ? "COMMIT" : "ROLLBACK");
		OK &= AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
		PQclear(results);
	}

	if (pset.timing)
	{
		INSTR_TIME_SET_CURRENT(after);
		INSTR_TIME_SUBTRACT(after, before);
		*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
	}

	return OK;
}
Пример #27
0
int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join)
{
  char *maskeddata, *temp, *sql, *column;
  char *conn_decrypted;
  int i, test;
  PGresult *query_result;
  msPOSTGRESQLJoinInfo *joininfo;

  if(join->joininfo)
    return MS_SUCCESS;

  joininfo = (msPOSTGRESQLJoinInfo *)malloc(sizeof(msPOSTGRESQLJoinInfo));
  if(!joininfo) {
    msSetError(MS_MEMERR, "Error allocating join info struct.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  joininfo->conn = NULL;
  joininfo->row_num = 0;
  joininfo->query_result = NULL;
  joininfo->from_index = 0;
  joininfo->to_column = join->to;
  joininfo->from_value = NULL;
  joininfo->layer_debug = layer->debug;
  join->joininfo = joininfo;

  /*
   * We need three things at a minimum, the connection string, a table
   * name, and a column to join on.
   */
  if(!join->connection) {
    msSetError(MS_QUERYERR, "No connection information provided.",
               "MSPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  if(!join->table) {
    msSetError(MS_QUERYERR, "No join table name found.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  if(!joininfo->to_column) {
    msSetError(MS_QUERYERR, "No join to column name found.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }

  /* Establish database connection */
  conn_decrypted = msDecryptStringTokens(layer->map, join->connection);
  if (conn_decrypted != NULL) {
    joininfo->conn = PQconnectdb(conn_decrypted);
    free(conn_decrypted);
  }
  if(!joininfo->conn || PQstatus(joininfo->conn) == CONNECTION_BAD) {
    maskeddata = (char *)malloc(strlen(layer->connection) + 1);
    strcpy(maskeddata, join->connection);
    temp = strstr(maskeddata, "password="******"Unable to connect to PostgreSQL using the string %s.\n  Error reported: %s\n",
               "msPOSTGRESQLJoinConnect()",
               maskeddata, PQerrorMessage(joininfo->conn));
    free(maskeddata);
    if(!joininfo->conn) {
      free(joininfo->conn);
    }
    free(joininfo);
    join->joininfo = NULL;
    return MS_FAILURE;
  }

  /* Determine the number and names of columns in the join table. */
  sql = (char *)malloc(36 + strlen(join->table) + 1);
  sprintf(sql, "SELECT * FROM %s WHERE false LIMIT 0", join->table);

  if(joininfo->layer_debug) {
    msDebug("msPOSTGRESQLJoinConnect(): executing %s.\n", sql);
  }

  query_result = PQexec(joininfo->conn, sql);
  if(!query_result || PQresultStatus(query_result) != PGRES_TUPLES_OK) {
    msSetError(MS_QUERYERR, "Error determining join items: %s.",
               "msPOSTGRESQLJoinConnect()", PQerrorMessage(joininfo->conn));
    if(query_result) {
      PQclear(query_result);
      query_result = NULL;
    }
    free(sql);
    return MS_FAILURE;
  }
  free(sql);
  join->numitems = PQnfields(query_result);
  join->items = malloc(sizeof(char *) * (join->numitems));

  /* We want the join-to column to be first in the list. */
  test = 1;
  for(i = 0; i < join->numitems; i++) {
    column = PQfname(query_result, i);
    if(strcmp(column, joininfo->to_column) != 0) {
      join->items[i + test] = (char *)malloc(strlen(column) + 1);
      strcpy(join->items[i + test], column);
    } else {
      test = 0;
      join->items[0] = (char *)malloc(strlen(column) + 1);
      strcpy(join->items[0], column);
    }
  }
  PQclear(query_result);
  query_result = NULL;
  if(test == 1) {
    msSetError(MS_QUERYERR, "Unable to find join to column: %s",
               "msPOSTGRESQLJoinConnect()", joininfo->to_column);
    return MS_FAILURE;
  }

  if(joininfo->layer_debug) {
    for(i = 0; i < join->numitems; i++) {
      msDebug("msPOSTGRESQLJoinConnect(): Column %d named %s\n", i, join->items[i]);
    }
  }

  /* Determine the index of the join from column. */
  for(i = 0; i < layer->numitems; i++) {
    if(strcasecmp(layer->items[i], join->from) == 0) {
      joininfo->from_index = i;
      break;
    }
  }

  if(i == layer->numitems) {
    msSetError(MS_JOINERR, "Item %s not found in layer %s.",
               "msPOSTGRESQLJoinConnect()", join->from, layer->name);
    return MS_FAILURE;
  }

  return MS_SUCCESS;
}
Пример #28
0
/*
 * PSQLexecWatch
 *
 * This function is used for \watch command to send the query to
 * the server and print out the results.
 *
 * Returns 1 if the query executed successfully, 0 if it cannot be repeated,
 * e.g., because of the interrupt, -1 on error.
 */
int
PSQLexecWatch(const char *query, const printQueryOpt *opt)
{
	PGresult   *res;
	double		elapsed_msec = 0;
	instr_time	before;
	instr_time	after;

	if (!pset.db)
	{
		psql_error("You are currently not connected to a database.\n");
		return 0;
	}

	SetCancelConn();

	if (pset.timing)
		INSTR_TIME_SET_CURRENT(before);

	res = PQexec(pset.db, query);

	ResetCancelConn();

	if (!AcceptResult(res))
	{
		PQclear(res);
		return 0;
	}

	if (pset.timing)
	{
		INSTR_TIME_SET_CURRENT(after);
		INSTR_TIME_SUBTRACT(after, before);
		elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
	}

	/*
	 * If SIGINT is sent while the query is processing, the interrupt will be
	 * consumed.  The user's intention, though, is to cancel the entire watch
	 * process, so detect a sent cancellation request and exit in this case.
	 */
	if (cancel_pressed)
	{
		PQclear(res);
		return 0;
	}

	switch (PQresultStatus(res))
	{
		case PGRES_TUPLES_OK:
			printQuery(res, opt, pset.queryFout, false, pset.logfile);
			break;

		case PGRES_COMMAND_OK:
			fprintf(pset.queryFout, "%s\n%s\n\n", opt->title, PQcmdStatus(res));
			break;

		case PGRES_EMPTY_QUERY:
			psql_error(_("\\watch cannot be used with an empty query\n"));
			PQclear(res);
			return -1;

		case PGRES_COPY_OUT:
		case PGRES_COPY_IN:
		case PGRES_COPY_BOTH:
			psql_error(_("\\watch cannot be used with COPY\n"));
			PQclear(res);
			return -1;

		default:
			psql_error(_("unexpected result status for \\watch\n"));
			PQclear(res);
			return -1;
	}

	PQclear(res);

	fflush(pset.queryFout);

	/* Possible microtiming output */
	if (pset.timing)
		printf(_("Time: %.3f ms\n"), elapsed_msec);

	return 1;
}
bool PostgreSQLInterface::ExecuteBlockingCommand(const char *command, PGresult **result, bool rollbackOnFailure)
{
	*result = PQexec(pgConn, command);
	return IsResultSuccessful(*result, rollbackOnFailure);
}
Пример #30
0
/*
 * handleCopyOut
 * receives data as a result of a COPY ... TO STDOUT command
 *
 * conn should be a database connection that you just issued COPY TO on
 * and got back a PGRES_COPY_OUT result.
 * copystream is the file stream for the data to go to.
 *
 * result is true if successful, false if not.
 */
bool
handleCopyOut(PGconn *conn, FILE *copystream)
{
	bool		OK = true;
	char	   *buf;
	int			ret;
	PGresult   *res;

	for (;;)
	{
		ret = PQgetCopyData(conn, &buf, 0);

		if (ret < 0)
			break;				/* done or error */

		if (buf)
		{
			if (fwrite(buf, 1, ret, copystream) != ret)
			{
				if (OK)			/* complain only once, keep reading data */
					psql_error("could not write COPY data: %s\n",
							   strerror(errno));
				OK = false;
			}
			PQfreemem(buf);
		}
	}

	if (OK && fflush(copystream))
	{
		psql_error("could not write COPY data: %s\n",
				   strerror(errno));
		OK = false;
	}

	if (ret == -2)
	{
		psql_error("COPY data transfer failed: %s", PQerrorMessage(conn));
		OK = false;
	}

	/*
	 * Check command status and return to normal libpq state.  After a
	 * client-side error, the server will remain ready to deliver data.  The
	 * cleanest thing is to fully drain and discard that data.  If the
	 * client-side error happened early in a large file, this takes a long
	 * time.  Instead, take advantage of the fact that PQexec() will silently
	 * end any ongoing PGRES_COPY_OUT state.  This does cause us to lose the
	 * results of any commands following the COPY in a single command string.
	 * It also only works for protocol version 3.  XXX should we clean up
	 * using the slow way when the connection is using protocol version 2?
	 *
	 * We must not ever return with the status still PGRES_COPY_OUT.  Our
	 * caller is unable to distinguish that situation from reaching the next
	 * COPY in a command string that happened to contain two consecutive COPY
	 * TO STDOUT commands.  We trust that no condition can make PQexec() fail
	 * indefinitely while retaining status PGRES_COPY_OUT.
	 */
	while (res = PQgetResult(conn), PQresultStatus(res) == PGRES_COPY_OUT)
	{
		OK = false;
		PQclear(res);

		PQexec(conn, "-- clear PGRES_COPY_OUT state");
	}
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		psql_error("%s", PQerrorMessage(conn));
		OK = false;
	}
	PQclear(res);

	return OK;
}