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); }
int mode_balance(PGconn* db, int argc, char** argv){ static const char* QUERY_SNACK_ACCOUNT_BALANCE="SELECT users.user_id, user_name, user_full_name, balance FROM garfield.users JOIN garfield.user_balances ON users.user_id = user_balances.user_id WHERE user_name = $1"; char* user_name=PQuser(db); PGresult* result=PQexecParams(db, QUERY_SNACK_ACCOUNT_BALANCE, 1, NULL, (const char**)&user_name, NULL, NULL, 0); if(!result){ printf("Failed to execute query\n"); return -1; } if(PQresultStatus(result)!=PGRES_TUPLES_OK){ printf("Query returned failure\n"); PQclear(result); return -1; } if(PQntuples(result)!=1){ printf("Query returned invalid number of rows\n"); PQclear(result); return -1; } //print balance printf("Snack account balance for user %s (%s) is %s\n",PQgetvalue(result,0,1),PQgetvalue(result,0,2),PQgetvalue(result,0,3)); PQclear(result); return 0; }
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 ¶m_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_has_node(uint32_t parentId, uint32_t childId) { if (parentId == 0) return false; if (parentId == childId) return true; PostgresStrings<2> parms; parms.set(0, parentId); parms.set(1, childId); check_postgres(); PGresult* result = PQexecParams(s_postgres, "SELECT vault.has_node($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 false; } DS_DASSERT(PQntuples(result) == 1); bool retval = (*PQgetvalue(result, 0, 0) == 't'); PQclear(result); return retval != 0; }
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; }
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; }
DS::Vault::NodeRef v_send_node(uint32_t nodeId, uint32_t playerId, uint32_t senderId) { DS::Vault::NodeRef ref; ref.m_child = ref.m_owner = ref.m_parent = 0; PostgresStrings<2> parms; parms.set(0, playerId); parms.set(1, DS::Vault::e_InboxFolder); PGresult* result = PQexecParams(s_postgres, "SELECT idx FROM vault.find_folder($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 ref; } DS_DASSERT(PQntuples(result) == 1); uint32_t inbox = strtoul(PQgetvalue(result, 0, 0), 0, 10); PQclear(result); if (v_ref_node(inbox, nodeId, senderId)) { ref.m_child = nodeId; ref.m_owner = senderId; ref.m_parent = inbox; } return ref; }
void cmdev_list_game(const struct lh_kv_elem *params, const struct lh_kv_elem *cookies, struct lh_response* resp) { //check auth cm::Session session; int err = cm::find_session(cookies, session); if (err) { return cm_send_error(resp, CMERR_AUTH); } //check param const char *appid = lh_kv_string(params, "appid", &err); if (err) { return cm_send_error(resp, CMERR_PARAM); } //check db PGconn *cmatchdb = cm::get_context()->cmatchdb; if (PQstatus(cmatchdb) != CONNECTION_OK) { return cm_send_error(resp, CMERR_DB); } //qurey db const int VAR_NUMS = 1; const char *vars[VAR_NUMS] = { appid, }; PGresult *res = PQexecParams(cmatchdb, "SELECT id, name FROM game WHERE app_id=$1;", VAR_NUMS, NULL, vars, NULL, NULL, 0); Autofree af_res_cl(res, (Freefunc)PQclear); if (PQresultStatus(res) != PGRES_TUPLES_OK) { return cm_send_error(resp, CMERR_DB); } int rownums = PQntuples(res); if (rownums == 0) { return cm_send_ok(resp); } std::stringstream ssout; ssout << "{\"error\":0,\"games\":{"; bool first = true; for (int i = 0; i < rownums; ++i) { const char* sid = PQgetvalue(res, i, 0); const char* sname = PQgetvalue(res, i, 1); if (sid && sname) { if (first) first = false; else ssout << ","; ssout << "\"" << sid << "\"" << ":\"" << sname << "\""; } } ssout << "}}"; //send to client lh_append_body(resp, ssout.str().c_str()); }
PGresult *OGRPG_PQexec(PGconn *conn, const char *query, int bMultipleCommandAllowed, int bErrorAsDebug) { PGresult* hResult; #if defined(PG_PRE74) /* PQexecParams introduced in PG >= 7.4 */ hResult = PQexec(conn, query); #else if (bMultipleCommandAllowed) hResult = PQexec(conn, query); else hResult = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0); #endif #ifdef DEBUG const char* pszRetCode = "UNKNOWN"; char szNTuples[32]; szNTuples[0] = '\0'; if (hResult) { switch(PQresultStatus(hResult)) { case PGRES_TUPLES_OK: pszRetCode = "PGRES_TUPLES_OK"; snprintf(szNTuples, sizeof(szNTuples), ", ntuples = %d", PQntuples(hResult)); break; case PGRES_COMMAND_OK: pszRetCode = "PGRES_COMMAND_OK"; break; case PGRES_NONFATAL_ERROR: pszRetCode = "PGRES_NONFATAL_ERROR"; break; case PGRES_FATAL_ERROR: pszRetCode = "PGRES_FATAL_ERROR"; break; default: break; } } if (bMultipleCommandAllowed) CPLDebug("PG", "PQexec(%s) = %s%s", query, pszRetCode, szNTuples); else CPLDebug("PG", "PQexecParams(%s) = %s%s", query, pszRetCode, szNTuples); #endif /* -------------------------------------------------------------------- */ /* Generate an error report if an error occurred. */ /* -------------------------------------------------------------------- */ if ( !hResult || (PQresultStatus(hResult) == PGRES_NONFATAL_ERROR || PQresultStatus(hResult) == PGRES_FATAL_ERROR ) ) { if( bErrorAsDebug ) CPLDebug("PG", "%s", PQerrorMessage( conn ) ); else CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage( conn ) ); } return hResult; }
void cmdev_add_match(const struct lh_kv_elem *params, const struct lh_kv_elem *cookies, struct lh_response* resp) { //check auth cm::Session session; int err = cm::find_session(cookies, session); if (err) { return cm_send_error(resp, CMERR_AUTH); } //parse param const char *appid = lh_kv_string(params, "appid", &err); const char *gameid = lh_kv_string(params, "gameid", &err); const char *begin = lh_kv_string(params, "begin", &err); const char *end = lh_kv_string(params, "end", &err); const char *matchname = lh_kv_string(params, "matchname", NULL); if (matchname && matchname[0] == 0) { matchname = NULL; } if (err) { return cm_send_error(resp, CMERR_PARAM); } //check db PGconn *cmatchdb = cm::get_context()->cmatchdb; if (PQstatus(cmatchdb) != CONNECTION_OK) { return cm_send_error(resp, CMERR_DB); } //insert into db const int VAR_NUMS = 6; const char *vars[VAR_NUMS] = { matchname, gameid, appid, session.userid.c_str(), begin, end, }; PGresult *res = PQexecParams(cmatchdb, "INSERT INTO match (name, game_id, app_id, developer_id, begin_time, end_time) " "SELECT $1, $2, $3, $4, $5::TIMESTAMP, $6::TIMESTAMP " "WHERE $4=(SELECT developer_id FROM game WHERE id=$2) " "AND $3=(SELECT app_id FROM game WHERE id=$2) " "AND $5::TIMESTAMP<$6::TIMESTAMP " "RETURNING id;", VAR_NUMS, NULL, vars, NULL, NULL, 0); Autofree af_res_insert(res, (Freefunc)PQclear); if (PQresultStatus(res) != PGRES_TUPLES_OK){ return cm_send_error(resp, CMERR_DB); } //get match id const char* matchid = PQgetvalue(res, 0, 0); if (matchid == NULL) { return cm_send_error(resp, CMERR_DB); } //send to client lh_appendf_body(resp, "{\"error\":0, \"matchid\":%s}", matchid); }
void real_query() { char const * const *pvalues = 0; int *plengths = 0; int *pformats = 0; std::vector<char const *> values; std::vector<int> lengths; std::vector<int> formats; if(params_>0) { values.resize(params_,0); lengths.resize(params_,0); formats.resize(params_,0); for(unsigned i=0;i<params_;i++) { if(params_set_[i]!=null_param) { if(params_pvalues_[i]!=0) { values[i]=params_pvalues_[i]; lengths[i]=params_plengths_[i]; } else { values[i]=params_values_[i].c_str(); lengths[i]=params_values_[i].size(); } if(params_set_[i]==binary_param) { formats[i]=1; } } } pvalues=&values.front(); plengths=&lengths.front(); pformats=&formats.front(); } if(res_) { PQclear(res_); res_ = 0; } if(prepared_id_.empty()) { res_ = PQexecParams( conn_, query_.c_str(), params_, 0, // param types pvalues, plengths, pformats, // format - text 0 // result format - text ); } else { res_ = PQexecPrepared( conn_, prepared_id_.c_str(), params_, pvalues, plengths, pformats, // format - text 0 // result format - text ); } }
int CHttpThread::PgExecuteSQL(PGconn *conn, PGresult * &dataset, const char * szSQL, int nParams, const char * const *paramValues, char * szErrMsg) { int retrycnt=0; sqlexecretry: dataset = PQexecParams(m_pq, szSQL, nParams, /* 参数个数 */ NULL, /* 让后端推出参数类型 */ paramValues, NULL, /* 因为是文本,所以必须要参数长度 */ NULL, /* 缺省是全部文本参数 */ 0); /* 是否是二进制结果 */ if( (PQresultStatus(dataset) == PGRES_COMMAND_OK ) ||(PQresultStatus(dataset) == PGRES_TUPLES_OK)) { printf("Successfully execute SQL : %s\n",szSQL); return 0; } else { sprintf(szErrMsg,"%s",PQerrorMessage(m_pq)); printf("%s\n",szErrMsg); PQclear(dataset); if(PQstatus(m_pq) != CONNECTION_OK) { if(retrycnt > 3) { return -1; } sleep(1); PQreset(m_pq); retrycnt++; if(PQstatus(m_pq)!=CONNECTION_OK) { printf("Thread %d reconnect database fail!\n",m_id); PQclear(dataset); goto sqlexecretry; } else { printf("Thread %d reconnect database success!\n",m_id); } } else //非连接性错误,可能是SQL语句错误等原因 { return -1; } } }
DB_Detail *DB_get_details(Database *db, unsigned int computer_id){ PGconn *conn; PGresult *res; DB_Detail *details; int nTuples, i; const char *paramValues[1]; const int paramFormats[1] = {BINARY_FRMT}; const int paramLengths[1] = {sizeof(uint32_t)}; uint32_t tmp_id; if(_DB_connect(db, &conn) != 0) return NULL; paramValues[0] = order_int(computer_id, &tmp_id); res = PQexecParams(conn, "SELECT d.id, d.vendor_id, d.device_id, d.subsystem_id, " "d.serial, d.created_at " "FROM "CONFIGS_TABNAME" cd " "JOIN "DETAILS_TABNAME" d ON d.id = cd.detail_id " "WHERE " "cd.computer_id = $1::int AND " "cd.allow_del_at IS NULL;", 1, /* count of params */ NULL, /* let the backend deduce param type */ paramValues, paramLengths, paramFormats, TEXT_FRMT); /* text results */ if (PQresultStatus(res) != PGRES_TUPLES_OK){ _DB_close_res(db, conn, res); return NULL; } nTuples = PQntuples(res); details = (DB_Detail *) malloc(sizeof(DB_Detail) * nTuples); for(i = 0; i < nTuples; i++){ details[i].id = (unsigned int) atoi(PQgetvalue(res, i, 0)); details[i].vendor_id = (unsigned int) atoi(PQgetvalue(res, i, 1)); details[i].device_id = (unsigned int) atoi(PQgetvalue(res, i, 2)); details[i].subsystem_id = (unsigned int) atoi(PQgetvalue(res, i, 3)); strncpy(details[i].serial, PQgetvalue(res, i, 4), DETAIL_SERIAL_SIZE); details[i].created_at = str_to_time(PQgetvalue(res, i, 5)); printf("Detail: %4d, %.4x, %.4x, %.8x, '%s', %d\n", details[i].id, details[i].vendor_id, details[i].device_id, details[i].subsystem_id, details[i].serial, (int) details[i].created_at ); } PQclear(res); PQfinish(conn); return details; }
/** Determine the format of timestamps used by the server. * A PostgresSQL server can be configured to store timestamps either as 8-byte * integers or floating point numbers with double precision. This functions * sends a simple SQL query to the server and tries to determine the format of * timestamps from the reply. This function is executed once after connecting * to a PostgreSQL server and the result of the detection is then stored in * form of a flag in pg_con connection structure. * @param con A PostgreSQL connection handle * @retval 0 If the server stores timestamps as floating point numbers. * @retval 1 If the server stores timestamps as 8-byte integers. * @retval A negative number on error. */ static int timestamp_format(PGconn *con) { unsigned long long offset; PGresult *res = 0; char *val; str sql; if(build_timestamp_format_sql(&sql) != 0) { ERR("postgres: Error while building SQL query to obtain timestamp " "format\n"); return -1; } res = PQexecParams(con, sql.s, 0, 0, 0, 0, 0, 1); pkg_free(sql.s); if(PQfformat(res, 0) != 1) { ERR("postgres: Binary format expected but server sent text\n"); goto error; } if(PQntuples(res) != 1) { ERR("postgres: Only one column expected, %d received\n", PQntuples(res)); goto error; } if(PQnfields(res) != 1) { ERR("postgres: Only one row expected, %d received\n", PQnfields(res)); goto error; } val = PQgetvalue(res, 0, 0); offset = ((unsigned long long)ntohl(((unsigned int *)val)[0]) << 32) + ntohl(((unsigned int *)val)[1]); PQclear(res); /* Server using int8 timestamps would return 1000000, because it stores * timestamps in microsecond resolution across the whole range. Server * using double timestamps would return 1 (encoded as double) here because * subsection fraction is stored as fractional part in the IEEE * representation. 1 stored as double would result in 4607182418800017408 * when the memory location occupied by the variable is read as unsigned * long long. */ if(offset == 1000000) { DBG("postgres: Server uses int8 format for timestamps.\n"); return 1; } else { DBG("postgres: Server uses double format for timestamps.\n"); return 0; } error: PQclear(res); return -1; }
/* * Execute an SQL request */ PGresult * ows_psql_exec(ows *o, const char *sql) { assert(o); assert(sql); assert(o->pg); ows_log(o, 8, sql); return PQexecParams(o->pg, sql, 0, NULL, NULL, NULL, NULL, 0); }
void pp_export(PGconn * conn, const char * query) { const int ascFmt = 0; int len = 0, i; int rows, ncol, icol; char * val; char * file; PGresult * res; const char * const * ival = (const char * const *) &val; FILE * fimg; /* execute the user provided query */ res = PQexecParams(conn, query, 0, //n. of params NULL, //oids guessed by backend NULL, NULL, NULL, ascFmt); if (PQresultStatus(res) != PGRES_TUPLES_OK) { pp_print_error(PQerrorMessage(conn)); PQclear(res); return; } /* some check */ icol = PQfnumber(res, opts.imagecol); if(icol==-1) { fprintf(stderr, "ERROR: Image column '%s' does not exist in result set.\n", opts.imagecol); PQclear(res); return; } if (!opts.nameprefix) { ncol = PQfnumber(res, opts.namecol); if(ncol==-1) { fprintf(stderr, "ERROR: Name column '%s' does not exist in result set.\n", opts.namecol); PQclear(res); return; } } rows = PQntuples(res); /* fetch the data and save */ for (i = 0; i < rows; ++i) { val = PQunescapeBytea( PQgetvalue(res, i, icol), &len); if(opts.namecol) file = PQgetvalue(res, i, ncol); else file = image_name(opts.nameprefix, i); fimg = fopen(file, "w"); fwrite(val, len, 1, fimg); fclose(fimg); PQfreemem(val); fprintf(stderr, "INFO: exported file %s\n", file); } PQclear(res); }
static void doStuffWithConnection(PGconn * const conn) { struct H { // just for the helper function inline static void helper(PGconn * const conn, PGresult * const result) { const ExecStatusType es = PQresultStatus(result); if (PGRES_TUPLES_OK == es) { const bool schemaIsOk = verifyTheSchema(conn, result); if (schemaIsOk) { std::cout << "All schema checks passed\n"; processReturnedTable(result); } // else rely on specific checks to print messages PQclear(result); // Despite name, is *not* just a memset-style 'clear'. // It calls 'free' for us, see https://git.io/vKpNI } else { // then PGRES_TUPLES_OK != es if ( PGRES_COMMAND_OK == es ) { std::cerr << "Expected to run a command which returns column data\n"; } else { // we have a real error char * const errorMessage = PQresultErrorMessage(result); BOOST_ASSERT(( NULL != errorMessage )); std::cerr << "Error attempting query:\n"; printHorizontalBar(std::cerr); std::cerr << '\n' << errorMessage << '\n'; printHorizontalBar(std::cerr); std::cerr << "\n\n"; } } } }; BOOST_ASSERT( NULL != conn ); //PGresult * const result = PQexec(conn, "SELECT * FROM my_table WHERE ((number = 1) or (english='Twenty'));"); //PGresult * const result = PQexec(conn, "SELECT * FROM my_table WHERE ((number = 1) or (lower(english)='two'));"); PGresult * const result = PQexecParams(conn, "SELECT * FROM my_table WHERE ((number = 1) or (english='Twenty'));", 0, NULL, NULL, NULL, NULL, 1); // request binary format outputs if (NULL != result) { H::helper(conn,result); } else { std::cerr << "Error attempting SELECT query. Likely causes: failed network connection, server down, or out-of-memory\n"; } }
static std::tuple<uint32_t, uint32_t> find_a_friendly_neighborhood_for_our_new_visitor() { PGresult* result = PQexec(s_postgres, "SELECT idx FROM vault.\"Nodes\" WHERE \"String64_2\"=" " 'Neighborhood' AND \"String64_4\" = '" HOOD_USER_NAME "'" " ORDER BY \"Int32_1\""); if (PQresultStatus(result) != PGRES_TUPLES_OK) { fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n", __FILE__, __LINE__, PQerrorMessage(s_postgres)); PQclear(result); return std::make_tuple<uint32_t, uint32_t>(0, 0); } uint32_t theHoodInfo = 0; for (int i = 0; i < PQntuples(result); ++i) { uint32_t ageInfoId = strtoul(PQgetvalue(result, i, 0), 0, 10); uint32_t owners = v_count_age_owners(ageInfoId); if (owners < HOOD_POPULATION_THRESHOLD) { theHoodInfo = ageInfoId; break; } } PQclear(result); // Need new hood? if (theHoodInfo == 0) { AuthServer_AgeInfo age; age.m_ageId = gen_uuid(); age.m_filename = "Neighborhood"; age.m_instName = HOOD_INSTANCE_NAME; age.m_userName = HOOD_USER_NAME; age.m_description = HOOD_USER_NAME " " HOOD_INSTANCE_NAME; age.m_seqNumber = -1; // Auto-generate theHoodInfo = std::get<1>(v_create_age(age, e_AgePublic)); } // It's important to BCast new hood members, so we'll return the ageOwners folder PostgresStrings<2> parms; parms.set(0, theHoodInfo); parms.set(1, DS::Vault::e_AgeOwnersFolder); result = PQexecParams(s_postgres, "SELECT idx FROM vault.find_folder($1, $2);", 2, 0, parms.m_values, 0, 0, 0); if (PQresultStatus(result) == PGRES_TUPLES_OK) { uint32_t ownersFolder = strtoul(PQgetvalue(result, 0, 0), 0, 10); PQclear(result); return std::make_tuple(theHoodInfo, ownersFolder); } else { fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n", __FILE__, __LINE__, PQerrorMessage(s_postgres)); PQclear(result); return std::make_tuple(0, 0); } }
DBMSqlDatatable* PostgreSql::exec(const core::string &sql, const params_type ¶ms) { usize const sz_params = params.size(); if (0 == sz_params) return this->exec(sql); params_type str_params = params; //Oid* pm_types = (Oid*)malloc(sz_params * sizeof(Oid)); char const* *pm_vals = (char const* *)malloc(sz_params * sizeof(char const*)); int* pm_lens = (int*)malloc(sz_params * sizeof(int)); int* pm_fmts = (int*)malloc(sz_params * sizeof(int)); for (core::counter<params_type::iterator> each = str_params.begin(); each != str_params.end(); ++each) { variant_t& var = each->second; var.stringize(); pm_vals[each.count] = (char const*)var; pm_lens[each.count] = var.size(); pm_fmts[each.count] = 0; } PGresult* res = PQexecParams(d_ptr->pg, sql.c_str(), sz_params, NULL, pm_vals, pm_lens, pm_fmts, 0); //free(pm_types); free(pm_vals); free(pm_lens); free(pm_fmts); PgSqlDatatable* dt = NULL; switch (PQresultStatus(res)) { default: trace_msg(PQresultErrorMessage(res)); break; case PGRES_COMMAND_OK: break; case PGRES_TUPLES_OK: { dt = new PgSqlDatatable(res); dt->update(); } break; }; if (dt == NULL) PQclear(res); else if (dt->rows().size() == 0) zero_drop(dt); return dt; }