C++ PQexecParams函数代码示例

uint32_t v_count_age_owners(uint32_t ageInfoId)
{
    PostgresStrings<2> parms;
    parms.set(0, ageInfoId);
    parms.set(1, DS::Vault::e_AgeOwnersFolder);
    PGresult* result = PQexecParams(s_postgres,
                       "SELECT idx FROM vault.find_folder($1, $2);",
                       2, 0, parms.m_values, 0, 0, 0);
    uint32_t owners = 0;
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
        PQclear(result);
        return owners;
    }
    parms.set(0, PQgetvalue(result, 0, 0));
    PQclear(result);
    result = PQexecParams(s_postgres,
             "SELECT COUNT(*) FROM vault.\"NodeRefs\" WHERE \"ParentIdx\"=$1",
              1, 0, parms.m_values, 0, 0, 0);
    if (PQresultStatus(result) == PGRES_TUPLES_OK) {
        owners = strtoul(PQgetvalue(result, 0, 0), 0, 10);
    } else {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
    }
    PQclear(result);
    return owners;
}

void dm_save_sdl_state(GameHost_Private* host, const DS::String& descriptor,
                       const MOUL::Uoid& object, const SDL::State& state)
{
    check_postgres(host);

    DS::Blob sdlBlob = state.toBlob();
    PostgresStrings<4> parms;
    host->m_buffer.truncate();
    object.write(&host->m_buffer);
    parms.set(0, host->m_serverIdx);
    parms.set(1, descriptor);
    parms.set(2, DS::Base64Encode(host->m_buffer.buffer(), host->m_buffer.size()));
    parms.set(3, DS::Base64Encode(sdlBlob.buffer(), sdlBlob.size()));
    PGresult* result = PQexecParams(host->m_postgres,
                                    "SELECT idx FROM game.\"AgeStates\""
                                    "    WHERE \"ServerIdx\"=$1 AND \"Descriptor\"=$2 AND \"ObjectKey\"=$3",
                                    3, 0, parms.m_values, 0, 0, 0);
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(host->m_postgres));
        PQclear(result);
        return;
    }
    if (PQntuples(result) == 0) {
        PQclear(result);
        result = PQexecParams(host->m_postgres,
                              "INSERT INTO game.\"AgeStates\""
                              "    (\"ServerIdx\", \"Descriptor\", \"ObjectKey\", \"SdlBlob\")"
                              "    VALUES ($1, $2, $3, $4)",
                              4, 0, parms.m_values, 0, 0, 0);
        if (PQresultStatus(result) != PGRES_COMMAND_OK) {
            fprintf(stderr, "%s:%d:\n    Postgres INSERT error: %s\n",
                    __FILE__, __LINE__, PQerrorMessage(host->m_postgres));
            PQclear(result);
            return;
        }
        PQclear(result);
    } else {
        DS_DASSERT(PQntuples(result) == 1);
        parms.set(0, DS::String(PQgetvalue(result, 0, 0)));
        parms.set(1, parms.m_strings[3]);   // SDL blob
        PQclear(result);
        result = PQexecParams(host->m_postgres,
                              "UPDATE game.\"AgeStates\""
                              "    SET \"SdlBlob\"=$2 WHERE idx=$1",
                              2, 0, parms.m_values, 0, 0, 0);
        if (PQresultStatus(result) != PGRES_COMMAND_OK) {
            fprintf(stderr, "%s:%d:\n    Postgres UPDATE error: %s\n",
                    __FILE__, __LINE__, PQerrorMessage(host->m_postgres));
            PQclear(result);
            return;
        }
        PQclear(result);
    }
}

static uint32_t find_public_age_1(const DS::String& filename)
{
    PGresult* result;
    {
        PostgresStrings<1> parm;
        parm.set(0, filename);
        result = PQexecParams(s_postgres,
                "SELECT \"AgeUuid\" FROM game.\"PublicAges\""
                "    WHERE \"AgeFilename\"=$1 AND \"SeqNumber\"=0",
                1, 0, parm.m_values, 0, 0, 0);
    }
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
        PQclear(result);
        return 0;
    }
    DS_DASSERT(PQntuples(result) == 1);

    DS::Uuid ageId(PQgetvalue(result, 0, 0));
    PQclear(result);

    {
        PostgresStrings<2> parms;
        parms.set(0, DS::Vault::e_NodeAgeInfo);
        parms.set(1, ageId.toString());
        result = PQexecParams(s_postgres,
                "SELECT idx FROM vault.\"Nodes\""
                "    WHERE \"NodeType\"=$1 AND \"Uuid_1\"=$2",
                2, 0, parms.m_values, 0, 0, 0);
    }
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
        PQclear(result);
        return 0;
    }
    if (PQntuples(result) == 0) {
        // Public age not found
        PQclear(result);
        return 0;
    }
    DS_DASSERT(PQntuples(result) == 1);

    uint32_t ageInfoNode = strtoul(PQgetvalue(result, 0, 0), 0, 10);
    PQclear(result);
    return ageInfoNode;
}

CAMLprim value PQexecParams_stub(
  value v_conn, value v_query, value v_params, value v_binary_params)
{
  CAMLparam1(v_conn);
  PGconn *conn = get_conn(v_conn);
  np_callback *np_cb = get_conn_cb(v_conn);
  PGresult *res;
  size_t len = caml_string_length(v_query) + 1;
  char *query = caml_stat_alloc(len);
  size_t nparams = Wosize_val(v_params);
  const char * const *params = copy_params(v_params, nparams);
  int *formats, *lengths;
  copy_binary_params(v_params, v_binary_params, nparams, &formats, &lengths);
  memcpy(query, String_val(v_query), len);
  caml_enter_blocking_section();
    res =
      (nparams == 0)
        ? PQexec(conn, query)
        : PQexecParams(conn, query, nparams, NULL, params, lengths, formats, 0);
    free_binary_params(formats, lengths);
    free_params(params, nparams);
    free(query);
  caml_leave_blocking_section();
  CAMLreturn(alloc_result(res, np_cb));
}

      boost::shared_ptr<ResultSet> executeQuery(const std::string& sql,int type=0) const
      {
         PGresult *result=0;
         if (type==1)
         {
             result=PQexecParams(conn_,sql.c_str(),0,0,0,0,0,1);
         }
         else
         {
             result=PQexec(conn_,sql.c_str());
         }
         if(!result || PQresultStatus(result) != PGRES_TUPLES_OK)
         {
             std::ostringstream s("Postgis Plugin: PSQL error");
             if (conn_ )
             {
                 std::string msg = PQerrorMessage( conn_ );
                 if ( ! msg.empty() )
                 {
                     s << ":\n" <<  msg.substr( 0, msg.size() - 1 );
                 }
                 
                 s << "\nFull sql was: '" <<  sql << "'\n";
             }
             if (result)
                 PQclear(result);
             throw mapnik::datasource_exception( s.str() );
         }

         return boost::make_shared<ResultSet>(result);
      }

			virtual long long sequence_last(std::string const &sequence)
			{
				PGresult *res = 0;
				long long rowid = 0;
				try {
					char const * const param_ptr = sequence.c_str();
					res = PQexecParams(	conn_,
								"SELECT currval($1)",
								1, // 1 param
								0, // types
								&param_ptr, // param values
								0, // lengths
								0, // formats
								0 // string format
								);
					if(PQresultStatus(res) != PGRES_TUPLES_OK) {
						throw pqerror(res,"failed to fetch last sequence id");
					}
					char const *val = PQgetvalue(res,0,0);
					if(!val || *val==0)
						throw pqerror("Failed to get value for sequecne id");
					fmt_.str(val);
					fmt_.clear();
					fmt_ >> rowid;
					fmt_.str(std::string());
					fmt_.clear();
				}
				catch(...) {
					if(res) PQclear(res);
					throw;
				}
				PQclear(res);
				return rowid;
			}

static int _DB_mark_config_del(Database *db, unsigned int id, time_t del_at){
    PGconn   *conn;
    PGresult *res;
    const char *paramValues[2];
    const int   paramLengths[2] = {sizeof(int), sizeof(int)};
    const int   paramFormats[2] = {BINARY_FRMT, BINARY_FRMT};
    uint32_t config_id, time_del_at;
    
    if(_DB_connect(db, &conn)    != 0) return 1;
    if(_DB_begin(db, conn, &res) != 0){ _DB_close_res(db, conn, res); return 1;}
    
    paramValues[0] = chk_time_to_null(del_at, &time_del_at);
    paramValues[1] = order_int(id, &config_id);
            
    res = PQexecParams(conn,
        "UPDATE "CONFIGS_TABNAME" SET deleted_at = "TO_TIMESTAMP("$3")
            " WHERE id = $2::int;",
        2,              /* count of params */
        NULL,           /* let the backend deduce param type */
        paramValues,
        paramLengths,
        paramFormats,
        TEXT_FRMT);             /* text results */
    if(PQresultStatus(res) != PGRES_COMMAND_OK){
        _DB_close_res(db, conn, res);
        return 1;
    }
    PQclear(res);
    
    if(_DB_commit(db, conn, &res) != 0){ _DB_close_res(db, conn, res); return 1;}
    
    PQfinish(conn);
    return 0;
}

static void fillNext(GtkTreeSelection* selection, GtkListStore* model) {
    uint32_t i;
    gtk_list_store_clear(model);

    static char offset[0x100];
    ssize_t len = snprintf(offset,0x100,"%d",page*pagesize);
    
    const char* values[] = { offset, derpagesize };
    const int lengths[] = { len, sizeof(derpagesize)  };
    const int fmt[] = { 0, 0 };

    PGresult* result = PQexecParams(PQconn,"getpage",1,NULL,values,lengths,fmt,0);
    GtkTreeIter iter = {};
    gtk_tree_model_get_iter_first(GTK_TREE_MODEL(model),&iter);
    for(i=0;i<PQntuples(result);++i) {
        gtk_list_store_append(model,&iter);
        gtk_list_store_set(model,&iter,0,strtof(PQgetvalue(result,i,0),NULL),
                1,PQgetvalue(result,i,1),
                2,PQgetvalue(result,i,2),
                -1);
    }

    PQclear(result);

    ++page;

}

bool v_fetch_tree(uint32_t nodeId, std::vector<DS::Vault::NodeRef>& refs)
{
    PostgresStrings<1> parm;
    parm.set(0, nodeId);
    PGresult* result = PQexecParams(s_postgres,
            "SELECT \"ParentIdx\", \"ChildIdx\", \"OwnerIdx\", \"Seen\""
            "    FROM vault.fetch_tree($1);",
            1, 0, parm.m_values, 0, 0, 0);
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
        PQclear(result);
        return false;
    }

    refs.resize(PQntuples(result));
    for (size_t i=0; i<refs.size(); ++i) {
        refs[i].m_parent = strtoul(PQgetvalue(result, i, 0), 0, 10);
        refs[i].m_child = strtoul(PQgetvalue(result, i, 1), 0, 10);
        refs[i].m_owner = strtoul(PQgetvalue(result, i, 2), 0, 10);
        refs[i].m_seen = strtoul(PQgetvalue(result, i, 3), 0, 10);
    }
    PQclear(result);
    return true;
}

bool v_find_public_ages(const DS::String& ageFilename, std::vector<Auth_PubAgeRequest::NetAgeInfo>& ages)
{
    PostgresStrings<2> parms;
    parms.set(0, DS::Vault::e_NodeAgeInfo);
    parms.set(1, ageFilename);
    // ageInfoId, Uuid, InstName, UserName, Description, SeqNumber, Language
    PGresult* result = PQexecParams(s_postgres,
                       "SELECT idx, \"Uuid_1\", \"String64_3\", \"String64_4\","
                       "    \"Text_1\",\"Int32_1\", \"Int32_3\" FROM vault.\"Nodes\""
                       "    WHERE \"NodeType\"=$1 AND \"Int32_2\"=1 AND \"String64_2\"=$2"
                       "    ORDER BY \"ModifyTime\" LIMIT 50",
                       2, 0, parms.m_values, 0, 0, 0);
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "%s:%d:\n    Postgres SELECT error: %s\n",
                __FILE__, __LINE__, PQerrorMessage(s_postgres));
        PQclear(result);
        return false;
    }
    for (int i = 0; i < PQntuples(result); ++i) {
        Auth_PubAgeRequest::NetAgeInfo ai;
        ai.m_instance = DS::Uuid(PQgetvalue(result, i, 1));
        ai.m_instancename = PQgetvalue(result, i, 2);
        ai.m_username = PQgetvalue(result, i, 3);
        ai.m_description = PQgetvalue(result, i, 4);
        ai.m_sequence = strtoul(PQgetvalue(result, i, 5), 0, 10);
        ai.m_language = strtoul(PQgetvalue(result, i, 6), 0, 10);
        ai.m_curPopulation = v_count_age_population(PQgetvalue(result, i, 1));
        ai.m_population = v_count_age_owners(strtoul(PQgetvalue(result, i, 0), 0 , 10));
        ages.push_back(ai);
    }
    PQclear(result);
    return true;
}

static char *pdo_pgsql_last_insert_id(pdo_dbh_t *dbh, const char *name, unsigned int *len)
{
	pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data;
	char *id = NULL;

	if (name == NULL) {
		if (H->pgoid == InvalidOid) {
			return NULL;
		}
		*len = spprintf(&id, 0, ZEND_LONG_FMT, (zend_long) H->pgoid);
	} else {
		PGresult *res;
		ExecStatusType status;
		const char *q[1];
		q[0] = name;
		res = PQexecParams(H->server, "SELECT CURRVAL($1)", 1, NULL, q, NULL, NULL, 0);
		status = PQresultStatus(res);

		if (res && (status == PGRES_TUPLES_OK)) {
			id = estrdup((char *)PQgetvalue(res, 0, 0));
			*len = PQgetlength(res, 0, 0);
		} else {
			pdo_pgsql_error(dbh, status, pdo_pgsql_sqlstate(res));
		}

		if (res) {
			PQclear(res);
		}
	}
	return id;
}

void clearSession()
{
	const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    conninfo = "dbname=echuraev";
	
    conn = PQconnectdb(conninfo);

    if (PQstatus(conn) != CONNECTION_OK)
    {
		printf ("Connection to database failed: %s\n", PQerrorMessage(conn));
		exit_nicely(conn);
    }
    res = PQexecParams(conn, "DELETE FROM sessions", 0, NULL, NULL, NULL, NULL, 0);
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
		printf ("Cannot remove old sessions: %s\n", PQerrorMessage(conn));
		PQclear(res);
        exit_nicely(conn);
    }
	
    PQclear(res);
	PQfinish(conn);
}

/**
 *  Execute a database command that has no result.
 *
 *  Error messages from this function are sent to the message
 *  handler (see msngr_init_log() and msngr_init_mail()).
 *
 *  @param  dbconn  - pointer to the database connection
 *  @param  command - command string
 *  @param  nparams - number of $1, $2, ... parameters in the command
 *  @param  params  - parameters to substitute in the command
 *
 *  @return database status:
 *    - DB_NO_ERROR
 *    - DB_ERROR
 *
 *  @see DBStatus
 */
DBStatus pgsql_exec(
    DBConn      *dbconn,
    const char  *command,
    int          nparams,
    const char **params)
{
    PGconn   *pgconn = (PGconn *)dbconn->dbh;
    PGresult *pgres;
    DBStatus  status;

    if (nparams > 0) {
        pgres = PQexecParams(
            pgconn, command, nparams, NULL, params, NULL, NULL, 0);
    }
    else {
        pgres = PQexec(pgconn, command);
    }

    if (!pgres || PQresultStatus(pgres) != PGRES_COMMAND_OK) {

        PGSQL_ERROR(dbconn, pgconn, pgres,
            "FAILED: %s\n", command);

        status = DB_ERROR;
    }
    else {
        status = DB_NO_ERROR;
    }

    if (pgres) {
        PQclear(pgres);
    }

    return(status);
}

static char *pdo_pgsql_last_insert_id(pdo_dbh_t *dbh, const char *name, size_t *len)
{
	pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data;
	char *id = NULL;
	PGresult *res;
	ExecStatusType status;

	if (name == NULL) {
		res = PQexec(H->server, "SELECT LASTVAL()");
	} else {
		const char *q[1];
		q[0] = name;

		res = PQexecParams(H->server, "SELECT CURRVAL($1)", 1, NULL, q, NULL, NULL, 0);
	}
	status = PQresultStatus(res);

	if (res && (status == PGRES_TUPLES_OK)) {
		id = estrdup((char *)PQgetvalue(res, 0, 0));
		*len = PQgetlength(res, 0, 0);
	} else {
		pdo_pgsql_error(dbh, status, pdo_pgsql_sqlstate(res));
	}

	if (res) {
		PQclear(res);
	}

	return id;
}

/*
 * Receive a single file as a malloc'd buffer.
 */
char *
libpqGetFile(const char *filename, size_t *filesize)
{
	PGresult   *res;
	char	   *result;
	int			len;
	const char *paramValues[1];

	paramValues[0] = filename;
	res = PQexecParams(conn, "SELECT pg_read_binary_file($1)",
					   1, NULL, paramValues, NULL, NULL, 1);

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
		pg_fatal("could not fetch remote file \"%s\": %s",
				 filename, PQresultErrorMessage(res));

	/* sanity check the result set */
	if (PQntuples(res) != 1 || PQgetisnull(res, 0, 0))
		pg_fatal("unexpected result set while fetching remote file \"%s\"\n",
				 filename);

	/* Read result to local variables */
	len = PQgetlength(res, 0, 0);
	result = pg_malloc(len + 1);
	memcpy(result, PQgetvalue(res, 0, 0), len);
	result[len] = '\0';

	PQclear(res);

	pg_log(PG_DEBUG, "fetched file \"%s\", length %d\n", filename, len);

	if (filesize)
		*filesize = len;
	return result;
}