/*
* 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;
}
/*
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
*/
static int
vacuumlo(const char *database, const struct _param * param)
{
PGconn *conn;
PGresult *res,
*res2;
char buf[BUFSIZE];
long matched;
long deleted;
int i;
static char *password = NULL;
bool new_pass;
bool success = true;
/* Note: password can be carried over from a previous call */
if (param->pg_prompt == TRI_YES && password == NULL)
password = simple_prompt("Password: "******"host";
values[0] = param->pg_host;
keywords[1] = "port";
values[1] = param->pg_port;
keywords[2] = "user";
values[2] = param->pg_user;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = database;
keywords[5] = "fallback_application_name";
values[5] = param->progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
conn = PQconnectdbParams(keywords, values, true);
if (!conn)
{
fprintf(stderr, "Connection to database \"%s\" failed\n",
database);
return -1;
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
param->pg_prompt != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
database, PQerrorMessage(conn));
PQfinish(conn);
return -1;
}
if (param->verbose)
{
fprintf(stdout, "Connected to database \"%s\"\n", database);
if (param->dry_run)
fprintf(stdout, "Test run: no large objects will be removed!\n");
}
/*
* Don't get fooled by any non-system catalogs
*/
res = PQexec(conn, "SET search_path = pg_catalog");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to set search_path:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* First we create and populate the LO temp table
*/
buf[0] = '\0';
strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
if (PQserverVersion(conn) >= 90000)
strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
else
strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to create temp table:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* Analyze the temp table so that planner will generate decent plans for
* the DELETEs below.
*/
buf[0] = '\0';
strcat(buf, "ANALYZE vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to vacuum temp table:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* Now find any candidate tables that have columns of type oid.
*
* NOTE: we ignore system tables and temp tables by the expedient of
* rejecting tables in schemas named 'pg_*'. In particular, the temp
* table formed above is ignored, and pg_largeobject will be too. If
* either of these were scanned, obviously we'd end up with nothing to
* delete...
*
* NOTE: the system oid column is ignored, as it has attnum < 1. This
* shouldn't matter for correctness, but it saves time.
*/
buf[0] = '\0';
strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
strcat(buf, " AND a.attrelid = c.oid ");
strcat(buf, " AND a.atttypid = t.oid ");
strcat(buf, " AND c.relnamespace = s.oid ");
strcat(buf, " AND t.typname in ('oid', 'lo') ");
strcat(buf, " AND c.relkind in ('r', 'm')");
strcat(buf, " AND s.nspname !~ '^pg_'");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "Failed to find OID columns:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
for (i = 0; i < PQntuples(res); i++)
{
char *schema,
*table,
*field;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
field = PQgetvalue(res, i, 2);
if (param->verbose)
fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
schema = PQescapeIdentifier(conn, schema, strlen(schema));
table = PQescapeIdentifier(conn, table, strlen(table));
field = PQescapeIdentifier(conn, field, strlen(field));
if (!schema || !table || !field)
{
fprintf(stderr, "Out of memory\n");
PQclear(res);
PQfinish(conn);
if (schema != NULL)
PQfreemem(schema);
if (schema != NULL)
PQfreemem(table);
if (schema != NULL)
PQfreemem(field);
return -1;
}
snprintf(buf, BUFSIZE,
"DELETE FROM vacuum_l "
"WHERE lo IN (SELECT %s FROM %s.%s)",
field, schema, table);
res2 = PQexec(conn, buf);
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to check %s in table %s.%s:\n",
field, schema, table);
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
PQfreemem(schema);
PQfreemem(table);
PQfreemem(field);
return -1;
}
PQclear(res2);
PQfreemem(schema);
PQfreemem(table);
PQfreemem(field);
}
PQclear(res);
/*
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
*
* We don't want to run each delete as an individual transaction, because
* the commit overhead would be high. However, since 9.0 the backend will
* acquire a lock per deleted LO, so deleting too many LOs per transaction
* risks running out of room in the shared-memory lock table. Accordingly,
* we delete up to transaction_limit LOs per transaction.
*/
res = PQexec(conn, "begin");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to start transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
buf[0] = '\0';
strcat(buf,
"DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
param->transaction_limit > 0 ? param->transaction_limit : 1000L);
deleted = 0;
while (1)
{
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH FORWARD failed: %s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
matched = PQntuples(res);
if (matched <= 0)
{
/* at end of resultset */
PQclear(res);
break;
}
for (i = 0; i < matched; i++)
{
Oid lo = atooid(PQgetvalue(res, i, 0));
if (param->verbose)
{
fprintf(stdout, "\rRemoving lo %6u ", lo);
fflush(stdout);
}
if (param->dry_run == 0)
{
if (lo_unlink(conn, lo) < 0)
{
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
fprintf(stderr, "%s", PQerrorMessage(conn));
if (PQtransactionStatus(conn) == PQTRANS_INERROR)
{
success = false;
PQclear(res);
break;
}
}
else
deleted++;
}
else
deleted++;
if (param->transaction_limit > 0 &&
(deleted % param->transaction_limit) == 0)
{
res2 = PQexec(conn, "commit");
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to commit transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res2);
res2 = PQexec(conn, "begin");
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to start transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res2);
}
}
PQclear(res);
}
/*
* That's all folks!
*/
res = PQexec(conn, "commit");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to commit transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
PQfinish(conn);
if (param->verbose)
{
if (param->dry_run)
fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
deleted, database);
else if (success)
fprintf(stdout,
"\rSuccessfully removed %ld large objects from database \"%s\".\n",
deleted, database);
else
fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
database, deleted, matched);
}
return ((param->dry_run || success) ? 0 : -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;
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;
}
/*
* 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 *queryFout_copy = pset.queryFout;
bool queryFoutPipe_copy = pset.queryFoutPipe;
bool started_txn = false;
bool did_pager = 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)
{
/* keep this code in sync with PrintQueryTuples */
pset.queryFout = stdout; /* so it doesn't get closed */
/* open file/pipe */
if (!setQFout(pset.gfname))
{
pset.queryFout = queryFout_copy;
pset.queryFoutPipe = queryFoutPipe_copy;
OK = false;
goto cleanup;
}
}
/* clear any pre-existing error indication on the output stream */
clearerr(pset.queryFout);
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 (did_pager)
{
ClosePager(pset.queryFout);
pset.queryFout = queryFout_copy;
pset.queryFoutPipe = queryFoutPipe_copy;
did_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 (pset.queryFout == stdout && !did_pager)
{
/*
* If query requires multiple result sets, hack to ensure that
* only one pager instance is used for the whole mess
*/
pset.queryFout = PageOutput(100000, &(my_popt.topt));
did_pager = true;
}
printQuery(results, &my_popt, pset.queryFout, 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(pset.queryFout);
/*
* 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(pset.queryFout))
break;
}
/* close \g argument file/pipe, restore old setting */
if (pset.gfname)
{
/* keep this code in sync with PrintQueryTuples */
setQFout(NULL);
pset.queryFout = queryFout_copy;
pset.queryFoutPipe = queryFoutPipe_copy;
}
else if (did_pager)
{
ClosePager(pset.queryFout);
pset.queryFout = queryFout_copy;
pset.queryFoutPipe = queryFoutPipe_copy;
}
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;
}
char *
get_prompt(promptStatus_t status, ConditionalStack cstack)
{
#define MAX_PROMPT_SIZE 256
static char destination[MAX_PROMPT_SIZE + 1];
char buf[MAX_PROMPT_SIZE + 1];
bool esc = false;
const char *p;
const char *prompt_string = "? ";
switch (status)
{
case PROMPT_READY:
prompt_string = pset.prompt1;
break;
case PROMPT_CONTINUE:
case PROMPT_SINGLEQUOTE:
case PROMPT_DOUBLEQUOTE:
case PROMPT_DOLLARQUOTE:
case PROMPT_COMMENT:
case PROMPT_PAREN:
prompt_string = pset.prompt2;
break;
case PROMPT_COPY:
prompt_string = pset.prompt3;
break;
}
destination[0] = '\0';
for (p = prompt_string;
*p && strlen(destination) < sizeof(destination) - 1;
p++)
{
memset(buf, 0, sizeof(buf));
if (esc)
{
switch (*p)
{
/* Current database */
case '/':
if (pset.db)
strlcpy(buf, PQdb(pset.db), sizeof(buf));
break;
case '~':
if (pset.db)
{
const char *var;
if (strcmp(PQdb(pset.db), PQuser(pset.db)) == 0 ||
((var = getenv("PGDATABASE")) && strcmp(var, PQdb(pset.db)) == 0))
strlcpy(buf, "~", sizeof(buf));
else
strlcpy(buf, PQdb(pset.db), sizeof(buf));
}
break;
/* DB server hostname (long/short) */
case 'M':
case 'm':
if (pset.db)
{
const char *host = PQhost(pset.db);
/* INET socket */
if (host && host[0] && !is_absolute_path(host))
{
strlcpy(buf, host, sizeof(buf));
if (*p == 'm')
buf[strcspn(buf, ".")] = '\0';
}
#ifdef HAVE_UNIX_SOCKETS
/* UNIX socket */
else
{
if (!host
|| strcmp(host, DEFAULT_PGSOCKET_DIR) == 0
|| *p == 'm')
strlcpy(buf, "[local]", sizeof(buf));
else
snprintf(buf, sizeof(buf), "[local:%s]", host);
}
#endif
}
break;
/* DB server port number */
case '>':
if (pset.db && PQport(pset.db))
strlcpy(buf, PQport(pset.db), sizeof(buf));
break;
/* DB server user name */
case 'n':
if (pset.db)
strlcpy(buf, session_username(), sizeof(buf));
break;
/* backend pid */
case 'p':
if (pset.db)
{
int pid = PQbackendPID(pset.db);
if (pid)
snprintf(buf, sizeof(buf), "%d", pid);
}
break;
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
*buf = (char) strtol(p, (char **) &p, 8);
--p;
break;
case 'R':
switch (status)
{
case PROMPT_READY:
if (cstack != NULL && !conditional_active(cstack))
buf[0] = '@';
else if (!pset.db)
buf[0] = '!';
else if (!pset.singleline)
buf[0] = '=';
else
buf[0] = '^';
break;
case PROMPT_CONTINUE:
buf[0] = '-';
break;
case PROMPT_SINGLEQUOTE:
buf[0] = '\'';
break;
case PROMPT_DOUBLEQUOTE:
buf[0] = '"';
break;
case PROMPT_DOLLARQUOTE:
buf[0] = '$';
break;
case PROMPT_COMMENT:
buf[0] = '*';
break;
case PROMPT_PAREN:
buf[0] = '(';
break;
default:
buf[0] = '\0';
break;
}
break;
case 'x':
if (!pset.db)
buf[0] = '?';
else
switch (PQtransactionStatus(pset.db))
{
case PQTRANS_IDLE:
buf[0] = '\0';
break;
case PQTRANS_ACTIVE:
case PQTRANS_INTRANS:
buf[0] = '*';
break;
case PQTRANS_INERROR:
buf[0] = '!';
break;
default:
buf[0] = '?';
break;
}
break;
case 'l':
snprintf(buf, sizeof(buf), UINT64_FORMAT, pset.stmt_lineno);
break;
case '?':
/* not here yet */
break;
case '#':
if (is_superuser())
buf[0] = '#';
else
buf[0] = '>';
break;
/* execute command */
case '`':
{
FILE *fd;
char *file = pg_strdup(p + 1);
int cmdend;
cmdend = strcspn(file, "`");
file[cmdend] = '\0';
fd = popen(file, "r");
if (fd)
{
if (fgets(buf, sizeof(buf), fd) == NULL)
buf[0] = '\0';
pclose(fd);
}
if (strlen(buf) > 0 && buf[strlen(buf) - 1] == '\n')
buf[strlen(buf) - 1] = '\0';
free(file);
p += cmdend + 1;
break;
}
/* interpolate variable */
case ':':
{
char *name;
const char *val;
int nameend;
name = pg_strdup(p + 1);
nameend = strcspn(name, ":");
name[nameend] = '\0';
val = GetVariable(pset.vars, name);
if (val)
strlcpy(buf, val, sizeof(buf));
free(name);
p += nameend + 1;
break;
}
case '[':
case ']':
#if defined(USE_READLINE) && defined(RL_PROMPT_START_IGNORE)
/*
* readline >=4.0 undocumented feature: non-printing
* characters in prompt strings must be marked as such, in
* order to properly display the line during editing.
*/
buf[0] = (*p == '[') ? RL_PROMPT_START_IGNORE : RL_PROMPT_END_IGNORE;
buf[1] = '\0';
#endif /* USE_READLINE */
break;
default:
buf[0] = *p;
buf[1] = '\0';
break;
}
esc = false;
}
else if (*p == '%')
esc = true;
else
{
buf[0] = *p;
buf[1] = '\0';
esc = false;
}
if (!esc)
strlcat(destination, buf, sizeof(destination));
}
return destination;
}
int InsertCust(struct db_context_t *dbc,
struct buy_request_t *data)
{
short co_id = 0 ; /* temporarily country ID */
int co_addr_id=0;
char c_uname[21];
char sql_cmd[2048];
int i, j, strmax;
char c_uname_small[21];
bzero(c_uname, sizeof(c_uname));
bzero(c_uname_small, sizeof(c_uname_small));
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
PGresult *res;
/* */
/* STEP 1 Get Country ID */
/* */
/* Create SQL Command */
sprintf(sql_cmd,STMT_InsertCust_getCountry, data->address.co_name);
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) == 0)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain country id\n");
PQclear(res);
return ERROR;
}
/* Get data */
j = 0;
co_id = atoi(PQgetvalue(res, 0, j++));
PQclear(res);
/* pgxc-modif - get the new customer id first */
/* Create SQL Command */
// get new c_id
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_InsertCust_selInsVal);
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) == 0)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain next customer id\n");
PQclear(res);
return ERROR;
}
/* Get data */
j = 0;
data->c_id = atoll(PQgetvalue(res, 0, j++));
PQclear(res);
/* */
if (DigSyl(++(data->c_id),0,c_uname) == W_ERROR)
{
return W_ERROR; // but, always OK.
}
/* pgxc-modif
* we may want to always skip this, as it will always fail...
*/
/* STEP 2 Get Country Address */
/* */
/* create SQL Command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_InsertCust_getAddr,
co_id,
data->address.addr_zip,
data->address.addr_state,
data->address.addr_city,
data->address.addr_street1,
data->address.addr_street2,
data->c_id); /* pgxc modification */
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
j = 0;
if (PQntuples(res) != 0)
{
co_addr_id = atoi(PQgetvalue(res, 0, j++));
}
if( PQntuples(res) == 0 )
{
PQclear(res);
/* Insert Address */
/* Create SQL Command */
//Get addr_id from sequence
memset( sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_InsertCust_insAddr_selInsVal);
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) == 0)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain next address id");
PQclear(res);
return ERROR;
}
/* Get data */
j = 0;
co_addr_id = atoi(PQgetvalue(res, 0, j++));
PQclear(res);
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_InsertCust_insAddr,
co_addr_id,
data->address.addr_street1,
data->address.addr_street2,
data->address.addr_city,
data->address.addr_state,
data->address.addr_zip,
co_id,
data->c_id);
/* use value obtained earlier for c_id. */
/* we cheat a little bit but the c_id is not yet defined, this part is updated when the real c_id is known */
/*data->c_id = 0 here);*/ /* pgxc additional */
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
}
else
{
PQclear(res);
}
/* change big char to small char. */
strmax=strlen(c_uname);
for (i = 0; i < strmax; i++)
{
c_uname_small[i] = (char) tolower(c_uname[i]);
}
/* Create SQL Command */
// insert data to customer tables
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_InsertCust_insCust,
data->c_id ,
c_uname,
c_uname_small,
data->c_fname,
data->c_lname,
co_addr_id,
data->c_phone,
data->c_email,
data->c_discount,
data->c_birthdate,
data->c_data);
/* Execute SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
return OK;
}
/*
* pgfdw_xact_callback --- cleanup at main-transaction end.
*/
static void
pgfdw_xact_callback(XactEvent event, void *arg)
{
HASH_SEQ_STATUS scan;
ConnCacheEntry *entry;
/* Quick exit if no connections were touched in this transaction. */
if (!xact_got_connection)
return;
/*
* Scan all connection cache entries to find open remote transactions, and
* close them.
*/
hash_seq_init(&scan, ConnectionHash);
while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
{
PGresult *res;
/* Ignore cache entry if no open connection right now */
if (entry->conn == NULL)
continue;
/* If it has an open remote transaction, try to close it */
if (entry->xact_depth > 0)
{
elog(DEBUG3, "closing remote transaction on connection %p",
entry->conn);
switch (event)
{
case XACT_EVENT_PARALLEL_PRE_COMMIT:
case XACT_EVENT_PRE_COMMIT:
/* Commit all remote transactions during pre-commit */
do_sql_command(entry->conn, "COMMIT TRANSACTION");
/*
* If there were any errors in subtransactions, and we
* made prepared statements, do a DEALLOCATE ALL to make
* sure we get rid of all prepared statements. This is
* annoying and not terribly bulletproof, but it's
* probably not worth trying harder.
*
* DEALLOCATE ALL only exists in 8.3 and later, so this
* constrains how old a server postgres_fdw can
* communicate with. We intentionally ignore errors in
* the DEALLOCATE, so that we can hobble along to some
* extent with older servers (leaking prepared statements
* as we go; but we don't really support update operations
* pre-8.3 anyway).
*/
if (entry->have_prep_stmt && entry->have_error)
{
res = PQexec(entry->conn, "DEALLOCATE ALL");
PQclear(res);
}
entry->have_prep_stmt = false;
entry->have_error = false;
break;
case XACT_EVENT_PRE_PREPARE:
/*
* We disallow remote transactions that modified anything,
* since it's not very reasonable to hold them open until
* the prepared transaction is committed. For the moment,
* throw error unconditionally; later we might allow
* read-only cases. Note that the error will cause us to
* come right back here with event == XACT_EVENT_ABORT, so
* we'll clean up the connection state at that point.
*/
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot prepare a transaction that modified remote tables")));
break;
case XACT_EVENT_PARALLEL_COMMIT:
case XACT_EVENT_COMMIT:
case XACT_EVENT_PREPARE:
/* Pre-commit should have closed the open transaction */
elog(ERROR, "missed cleaning up connection during pre-commit");
break;
case XACT_EVENT_PARALLEL_ABORT:
case XACT_EVENT_ABORT:
/* Assume we might have lost track of prepared statements */
entry->have_error = true;
/* If we're aborting, abort all remote transactions too */
res = PQexec(entry->conn, "ABORT TRANSACTION");
/* Note: can't throw ERROR, it would be infinite loop */
if (PQresultStatus(res) != PGRES_COMMAND_OK)
pgfdw_report_error(WARNING, res, entry->conn, true,
"ABORT TRANSACTION");
else
{
PQclear(res);
/* As above, make sure to clear any prepared stmts */
if (entry->have_prep_stmt && entry->have_error)
{
res = PQexec(entry->conn, "DEALLOCATE ALL");
PQclear(res);
}
entry->have_prep_stmt = false;
entry->have_error = false;
}
break;
}
}
/* Reset state to show we're out of a transaction */
entry->xact_depth = 0;
/*
* If the connection isn't in a good idle state, discard it to
* recover. Next GetConnection will open a new connection.
*/
if (PQstatus(entry->conn) != CONNECTION_OK ||
PQtransactionStatus(entry->conn) != PQTRANS_IDLE)
{
elog(DEBUG3, "discarding connection %p", entry->conn);
PQfinish(entry->conn);
entry->conn = NULL;
}
}
/*
* Regardless of the event type, we can now mark ourselves as out of the
* transaction. (Note: if we are here during PRE_COMMIT or PRE_PREPARE,
* this saves a useless scan of the hashtable during COMMIT or PREPARE.)
*/
xact_got_connection = false;
/* Also reset cursor numbering for next transaction */
cursor_number = 0;
}
/* rollback if needed */
void
pgut_rollback(PGconn *conn)
{
if (conn && PQtransactionStatus(conn) != PQTRANS_IDLE)
pgut_command(conn, "ROLLBACK", 0, NULL);
}
int commit_transaction(struct db_context_t *dbc)
{
PGresult *res;
res = PQexec(dbc->conn, "COMMIT");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
return OK;
}
static PyObject *
psyco_conn_get_transaction_status(connectionObject *self)
{
return PyInt_FromLong((long)PQtransactionStatus(self->pgconn));
}
/*
* pgfdw_subxact_callback --- cleanup at subtransaction end.
*/
static void
pgfdw_subxact_callback(SubXactEvent event, SubTransactionId mySubid,
SubTransactionId parentSubid, void *arg)
{
HASH_SEQ_STATUS scan;
ConnCacheEntry *entry;
int curlevel;
/* Nothing to do at subxact start, nor after commit. */
if (!(event == SUBXACT_EVENT_PRE_COMMIT_SUB ||
event == SUBXACT_EVENT_ABORT_SUB))
return;
/* Quick exit if no connections were touched in this transaction. */
if (!xact_got_connection)
return;
/*
* Scan all connection cache entries to find open remote subtransactions
* of the current level, and close them.
*/
curlevel = GetCurrentTransactionNestLevel();
hash_seq_init(&scan, ConnectionHash);
while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
{
PGresult *res;
char sql[100];
/*
* We only care about connections with open remote subtransactions of
* the current level.
*/
if (entry->conn == NULL || entry->xact_depth < curlevel)
continue;
if (entry->xact_depth > curlevel)
elog(ERROR, "missed cleaning up remote subtransaction at level %d",
entry->xact_depth);
if (event == SUBXACT_EVENT_PRE_COMMIT_SUB)
{
/* Commit all remote subtransactions during pre-commit */
snprintf(sql, sizeof(sql), "RELEASE SAVEPOINT s%d", curlevel);
do_sql_command(entry->conn, sql);
}
else
{
/* Assume we might have lost track of prepared statements */
entry->have_error = true;
/*
* If a command has been submitted to the remote server by using
* an asynchronous execution function, the command might not have
* yet completed. Check to see if a command is still being
* processed by the remote server, and if so, request cancellation
* of the command.
*/
if (PQtransactionStatus(entry->conn) == PQTRANS_ACTIVE)
{
PGcancel *cancel;
char errbuf[256];
if ((cancel = PQgetCancel(entry->conn)))
{
if (!PQcancel(cancel, errbuf, sizeof(errbuf)))
ereport(WARNING,
(errcode(ERRCODE_CONNECTION_FAILURE),
errmsg("could not send cancel request: %s",
errbuf)));
PQfreeCancel(cancel);
}
}
/* Rollback all remote subtransactions during abort */
snprintf(sql, sizeof(sql),
"ROLLBACK TO SAVEPOINT s%d; RELEASE SAVEPOINT s%d",
curlevel, curlevel);
res = PQexec(entry->conn, sql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
pgfdw_report_error(WARNING, res, entry->conn, true, sql);
else
PQclear(res);
}
/* OK, we're outta that level of subtransaction */
entry->xact_depth--;
}
}
int pgConn::GetTxStatus()
{
return PQtransactionStatus(conn);
}
int execute_buy_confirm(struct db_context_t *dbc,
struct buy_confirm_t *data)
{
short int ship_day = 0;
char ol_comment[OL_COMMENT_LEN + 1];
char cx_auth_id[CX_AUTH_ID_LEN + 1];
char sql_cmd[2048];
long long c_addr_id = 0;
char c_fname[C_FNAME_LEN+1];
char c_lname[C_LNAME_LEN+1];
short co_id = 0;
long long ship_addr_id = 0;
long long scl_i_id[SHOPPING_CART_ITEMS_MAX];
short scl_qty[SHOPPING_CART_ITEMS_MAX];
double scl_cost[SHOPPING_CART_ITEMS_MAX];
int ol_id = 0;
int st_stock = 0;
int ix,iy; /* Loop Counter */
PGresult *res;
int j;
for ( ix =0 ; ix < SHOPPING_CART_ITEMS_MAX ; ix++ )
{
scl_i_id[ix] = 0;
scl_qty[ix] = 0;
scl_cost[ix] = 0;
}
ix = 0;
bzero(ol_comment,sizeof(ol_comment));
bzero(cx_auth_id,sizeof(cx_auth_id));
bzero(sql_cmd,sizeof(sql_cmd));
bzero(c_fname,sizeof(c_fname));
bzero(c_lname,sizeof(c_lname));
/* Generate random day between 1 to 7 for ship_date. */
ship_day = get_random_int(7) + 1;
/* get authentication id from PGE */
strcpy(cx_auth_id, "012345678912345");
/* generate a_string[20..100] as order_line comment */
get_a_string(ol_comment, 20, 100);
/* SELECT shopping_cart Info */
if( data->sc_id != 0 )
{
// if data->sc_id not Zero, Select data from Shopping_Cart Table
/* Create SQL Command */
sprintf(sql_cmd,STMT_BUYCONF_selSC2,data->sc_id);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) != 0)
{
/* Get data */
j = 0;
data->sc_sub_total = strtod(PQgetvalue(res, 0, j++), NULL);
data->sc_tax = strtod(PQgetvalue(res, 0, j++), NULL);
data->sc_ship_cost = strtod(PQgetvalue(res, 0, j++), NULL);
data->sc_total = strtod(PQgetvalue(res, 0, j++), NULL);
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain shopping_cart info for sc_id = %d\n", data->sc_id); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
}
else
{
// if data-> sc_id is Zero, Get Shopping_Cart iD from Shopping_Cart Table
/* Create SQL Command */
sprintf(sql_cmd,STMT_BUYCONF_selInsVal);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
if (PQntuples(res) != 0)
{
j = 0;
data->sc_id = atoll(PQgetvalue(res, 0, j++));
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain shopping_cart_id\n"); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
//insert data is all Zero
data->sc_sub_total = 0;
data->sc_tax = 0;
data->sc_ship_cost = 0;
data->sc_total = 0;
}
/* SELECT customer Info */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_selCS,data->c_id);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) != 0)
{
/* Get data */
j = 0;
strcpy(c_fname, PQgetvalue(res, 0, j++));
strcpy(c_lname, PQgetvalue(res, 0, j++));
data->c_discount = strtod(PQgetvalue(res, 0, j++), NULL);
c_addr_id = atoll(PQgetvalue(res, 0, j++));
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not select customer = %d\n", data->c_id); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
if (data->shipping.addr_street1[0] != 0x00)
{
/* SELECT ADDRESS */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd, STMT_BUYCONF_selADRCNT,
data->shipping.co_name,
data->shipping.addr_zip,
data->shipping.addr_state,
data->shipping.addr_city,
data->shipping.addr_street1,
data->shipping.addr_street2,
data->c_id); /* pgxc additional */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
j = 0;
if (PQntuples(res) != 0)
{
ship_addr_id = atoll(PQgetvalue(res, 0, j++));
co_id = atoi(PQgetvalue(res, 0, j++));
}
if(PQntuples(res) == 0)
{
PQclear(res);
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_getCountry, data->shipping.co_name);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
if (PQntuples(res) != 0)
{
j = 0;
co_id = atoi(PQgetvalue(res, 0, j++));
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain country for %s", data->shipping.co_name);
PQclear(res);
return ERROR;
}
PQclear(res);
//select id from sequense
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_selADRID);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
if (PQntuples(res) != 0)
{
j = 0;
ship_addr_id = atoll(PQgetvalue(res, 0, j++));
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain addr_id");
PQclear(res);
return ERROR;
}
/* INSERT ADDRESS */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_insADR,
ship_addr_id,
data->shipping.addr_street1,
data->shipping.addr_street2,
data->shipping.addr_city,
data->shipping.addr_state,
data->shipping.addr_zip,
co_id,
data->c_id);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
}
else
{
PQclear(res);
}
}
else
{
/* SELECT ADDRESS */
/* Create SQL command */
ship_addr_id = c_addr_id;
memset(sql_cmd, 0x00, sizeof(sql_cmd));
if (data->c_id > 0)
sprintf(sql_cmd,STMT_BUYCONF_selADR,ship_addr_id,data->c_id); /* pgxc modif: addition of c_id */
else
sprintf(sql_cmd,STMT_BUYCONF_selADR_nocust,ship_addr_id); /* pgxc modif */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK )
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
if (PQntuples(res) != 0)
{
j = 0;
co_id = atoi(PQgetvalue(res, 0, j++));
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain address for addr_id = %d, c_id = %lld\n", ship_addr_id, data->c_id); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
}
/* INSERT ORDERS */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_insODR,
data->sc_id,
data->c_id,
data->sc_sub_total,
data->sc_tax,
data->sc_total,
ship_day,
c_addr_id,
ship_addr_id);
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
/* seems to block here in case scl was created with a c_id null if the customer made the shopping cart before being registered */
/* SELECT Shopping Cart Line */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) );
sprintf(sql_cmd,STMT_BUYCONF_selSCL,data->sc_id); /*,data->c_id);*/
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
if (PQntuples(res) != 0)
{
/* Get data */
for (ix=0;ix<SHOPPING_CART_ITEMS_MAX && ix < PQntuples(res);ix++)
{
j = 0;
scl_i_id[ix] = atoll(PQgetvalue(res, ix, j++));
scl_cost[ix] = strtod(PQgetvalue(res, ix, j++), NULL);
scl_qty[ix] = atoi(PQgetvalue(res, ix, j++));
}
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not select shopping_cart for sc_id = %d\n", data->sc_id); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
for (iy=0;iy<ix;iy++)
{
/* INSERT ORDER Line */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_insODRL,
++ol_id,
data->sc_id,
scl_i_id[iy],
scl_qty[iy],
data->c_discount,
ol_comment,
data->c_id); /* pgxc additional */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
/* SELECT Item */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_selITM, scl_i_id[iy]); /* for pgxc nothing changed here, not necessary as stock is a partition of item just depending on it */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
/* Get data */
if (PQntuples(res) != 0)
{
j = 0;
co_id = atoi(PQgetvalue(res, 0, j++));
PQclear(res);
}
else
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
LOG_ERROR_MESSAGE("Could not obtain item = %d\n", scl_i_id[iy]); /* pgxc modif: addition of c_id */
PQclear(res);
return ERROR;
}
if (st_stock - 10 > scl_qty[iy])
{
st_stock -= scl_qty[iy];
}
else
{
st_stock = st_stock - scl_qty[iy] + 21;
}
/* UPDATE Item */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_updITM, st_stock,scl_i_id[iy]); /* pgxc modif: name of stock variable changed to st_stock */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
}
/* INSERT cc_xacts */
/* Create SQL command */
memset(sql_cmd, 0x00, sizeof(sql_cmd));
sprintf(sql_cmd,STMT_BUYCONF_insXACT,
data->sc_id,
data->cx_type,
data->cx_num,
data->cx_name,
data->cx_expiry,
cx_auth_id,
data->sc_total,
co_id,
data->c_id); /* pgxc additional */
/* **MOBILE CODE** SQL Command */
res = PQexec(dbc->conn, sql_cmd);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR_MESSAGE("%s\nPQbackendPID[%d]\nPQstatus[%d]\nPQtransactionStatus[%d]\nPQsocket[%d]\nPQprotocolVersion[%d]\n", PQerrorMessage(dbc->conn), PQbackendPID(dbc->conn), (int)PQstatus(dbc->conn), (int)PQtransactionStatus(dbc->conn), PQsocket(dbc->conn), PQprotocolVersion(dbc->conn));
PQclear(res);
return ERROR;
}
PQclear(res);
if (getSCDetail(dbc, data->scl_data, data->sc_id, data->c_id, &data->sc_size) == W_ERROR)
{
return W_ERROR;
}
return OK;
}
