// call PQexec
internal ExecStatusType Exec(byte[] stmt, out IntPtr res)
{
if (mConnection == IntPtr.Zero)
{
res = IntPtr.Zero;
return(ExecStatusType.PGRES_FATAL_ERROR);
}
unsafe
{
fixed(byte *st = stmt)
{
res = PqsqlWrapper.PQexec(mConnection, st);
}
}
ExecStatusType s = ExecStatusType.PGRES_FATAL_ERROR;
if (res != IntPtr.Zero)
{
s = PqsqlWrapper.PQresultStatus(res);
}
return(s);
}
// executes SET parameter=value
internal void SetSessionParameter(string parameter, object value)
{
if (mConnection == IntPtr.Zero)
{
throw new InvalidOperationException("cannot set session parameter on closed connection");
}
if (string.IsNullOrEmpty(parameter))
{
throw new ArgumentOutOfRangeException(nameof(parameter), "parameter out of range");
}
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
StringBuilder sb = new StringBuilder();
sb.Append("set ");
sb.Append(parameter);
sb.Append('=');
sb.Append(value);
byte[] stmt = PqsqlUTF8Statement.CreateUTF8Statement(sb);
ExecStatusType s = Exec(stmt);
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
string err = GetErrorMessage();
string msg = string.Format(CultureInfo.InvariantCulture, "Could not set «{0}» to «{1}»: {2}", parameter, value, err);
throw new PqsqlException(msg);
}
}
// call PQexec and immediately discard PGresult struct
internal ExecStatusType Exec(byte[] stmt)
{
IntPtr res;
ExecStatusType s = Exec(stmt, out res);
Consume(res);
return(s);
}
//
// Summary:
// Starts a database transaction with the specified isolation level.
//
// Parameters:
// isolationLevel:
// Specifies the isolation level for the transaction.
//
// Returns:
// An object representing the new transaction.
public new PqsqlTransaction BeginTransaction(IsolationLevel isolationLevel)
{
#if CODECONTRACTS
Contract.Requires <ArgumentException>(isolationLevel != IsolationLevel.Chaos);
Contract.Ensures(Contract.Result <PqsqlTransaction>() != null);
#else
if (isolationLevel == IsolationLevel.Chaos)
{
throw new ArgumentException("isolationLevel == IsolationLevel.Chaos");
}
#endif
if (mConnection == IntPtr.Zero)
{
Open();
}
PqsqlTransaction t = null;
PqsqlTransaction txn;
try
{
t = new PqsqlTransaction(this, isolationLevel);
// get transaction start command
byte[] txnString = t.TransactionStart;
ExecStatusType s = Exec(txnString);
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
string err = GetErrorMessage();
throw new PqsqlException("Transaction start failed: " + err);
}
// swap t with txn
txn = t;
t = null;
}
finally
{
t?.Dispose(); // only dispose PqsqlTransaction if s != ExecStatusType.PGRES_COMMAND_OK
}
return(txn);
}
//
// Summary:
// Rolls back a transaction from a pending state.
public override void Rollback()
{
ExecStatusType s = SaveTransaction(false);
switch (s)
{
case ExecStatusType.PGRES_COMMAND_OK:
return;
case ExecStatusType.PGRES_EMPTY_QUERY:
throw new PqsqlException("Cannot rollback: connection or transaction is closed");
default:
string err = mConn.GetErrorMessage();
throw new PqsqlException("Could not rollback transaction: " + err);
}
}
public void PqsqlConnectionTest1()
{
PqsqlConnection connection = new PqsqlConnection(string.Empty);
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
Assert.AreEqual(string.Empty, connection.ConnectionString, "wrong connection string");
Assert.AreEqual(0, connection.ConnectionTimeout, "wrong connection timeout");
Assert.AreEqual(string.Empty, connection.Database, "wrong connection database");
connection.ConnectionString = connectionString;
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
connection.ChangeDatabase("postgres");
Assert.AreEqual("postgres", connection.Database, "wrong connection database");
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
ExecStatusType status = connection.Exec(null);
Assert.AreEqual(ExecStatusType.PGRES_FATAL_ERROR, status);
IntPtr res;
status = connection.Exec(null, out res);
Assert.AreEqual(ExecStatusType.PGRES_FATAL_ERROR, status);
Assert.AreEqual(IntPtr.Zero, res);
connection.Consume(res);
connection.Dispose();
connection.Dispose();
}
public static string PQresStatusW(ExecStatusType status)
{
return(Utf8PtrToString(PQresStatus(status)));
}
public static extern IntPtr PQresStatus(ExecStatusType status);
public void End()
{
IntPtr res;
string err = string.Empty;
#if CODECONTRACTS
Contract.Assume(mConn != null);
#endif
IntPtr conn = mConn.PGConnection;
if (mColBuf == IntPtr.Zero)
{
return;
}
int ret = PqsqlBinaryFormat.pqcb_put_end(mColBuf); // flush column buffer
if (ret != 1)
{
err = err.Insert(0, "Could not send end-of-data indication: ");
goto bailout;
}
res = PqsqlWrapper.PQgetResult(conn);
if (res != IntPtr.Zero)
{
ExecStatusType s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
if (s == ExecStatusType.PGRES_COPY_IN)
{
// still in COPY_IN mode? bail out!
byte[] b = PqsqlUTF8Statement.CreateUTF8Statement("COPY FROM cancelled by client");
unsafe
{
fixed(byte *bs = b)
{
ret = PqsqlWrapper.PQputCopyEnd(conn, bs);
}
}
res = PqsqlWrapper.PQgetResult(conn);
if (res != IntPtr.Zero)
{
s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
}
err = err.Insert(0, "Cancelled COPY FROM while still in COPY_IN mode (" + s + "," + ret + "): ");
goto bailout;
}
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
err = err.Insert(0, "COPY FROM failed (" + s + "): ");
goto bailout;
}
// consume all remaining results until we reach the NULL result
while ((res = PqsqlWrapper.PQgetResult(conn)) != IntPtr.Zero)
{
// always free mResult
PqsqlWrapper.PQclear(res);
}
return;
}
bailout:
err += mConn.GetErrorMessage();
throw new PqsqlException(err);
}
private static bool DiscardConnection(IntPtr conn)
{
if (conn == IntPtr.Zero)
{
return(false);
}
bool rollback = false;
ConnStatusType cs = PqsqlWrapper.PQstatus(conn);
if (cs != ConnStatusType.CONNECTION_OK)
{
return(false); // connection broken
}
PGTransactionStatusType ts = PqsqlWrapper.PQtransactionStatus(conn);
switch (ts)
{
case PGTransactionStatusType.PQTRANS_INERROR: /* idle, within failed transaction */
case PGTransactionStatusType.PQTRANS_INTRANS: /* idle, within transaction block */
rollback = true;
break;
case PGTransactionStatusType.PQTRANS_IDLE: /* connection idle */
// nothing to do
break;
// PGTransactionStatusType.PQTRANS_ACTIVE: /* command in progress */
// PGTransactionStatusType.PQTRANS_UNKNOWN: /* cannot determine status */
default:
return(false); // connection broken
}
IntPtr res;
ExecStatusType s = ExecStatusType.PGRES_FATAL_ERROR;
// we need to rollback before we can discard the connection
if (rollback)
{
unsafe
{
fixed(byte *st = PqsqlTransaction.RollbackStatement)
{
res = PqsqlWrapper.PQexec(conn, st);
if (res != IntPtr.Zero)
{
s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
}
}
}
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
return(false); // connection broken
}
s = ExecStatusType.PGRES_FATAL_ERROR;
}
// discard connection
unsafe
{
fixed(byte *st = DiscardAllStatement)
{
res = PqsqlWrapper.PQexec(conn, st);
if (res != IntPtr.Zero)
{
s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
}
}
}
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
return(false); // connection broken
}
return(true); // connection successfully resetted
}
private static bool CheckOrRelease(IntPtr pgConn, out ConnStatusType connStatus, out PGTransactionStatusType tranStatus)
{
if (pgConn == IntPtr.Zero)
{
connStatus = ConnStatusType.CONNECTION_BAD;
tranStatus = PGTransactionStatusType.PQTRANS_UNKNOWN;
return(false);
}
// is connection reusable?
connStatus = PqsqlWrapper.PQstatus(pgConn);
if (connStatus != ConnStatusType.CONNECTION_OK)
{
goto broken;
}
tranStatus = PqsqlWrapper.PQtransactionStatus(pgConn);
if (tranStatus != PGTransactionStatusType.PQTRANS_IDLE)
{
goto broken;
}
//
// now check the connection: first try to fix the client encoding if it is not utf8 for some reason.
// if client_encoding is utf8, we send the empty query to check whether the socket is still usable
// for the backend communiction. here, we could make use of tcp_keepalive settings.
//
int client_encoding = PqsqlWrapper.PQclientEncoding(pgConn);
if (client_encoding == -1)
{
goto broken;
}
if (client_encoding == (int)PgEnc.PG_UTF8) // client_encoding == utf8
{
// send empty query to test whether we are really connected (tcp_keepalive might have closed socket)
unsafe
{
byte[] empty = { 0 }; // empty query string
fixed(byte *eq = empty)
{
if (PqsqlWrapper.PQsendQuery(pgConn, eq) == 0) // could not send query
{
goto broken;
}
}
}
// Reading result: consume and clear remaining results until we reach the NULL result.
// PQgetResult will block here
IntPtr res;
ExecStatusType st = ExecStatusType.PGRES_EMPTY_QUERY;
while ((res = PqsqlWrapper.PQgetResult(pgConn)) != IntPtr.Zero)
{
ExecStatusType st0 = PqsqlWrapper.PQresultStatus(res);
if (st0 != ExecStatusType.PGRES_EMPTY_QUERY)
{
st = st0;
}
// always free res
PqsqlWrapper.PQclear(res);
}
if (st != ExecStatusType.PGRES_EMPTY_QUERY) // received wrong exec status
{
goto broken;
}
}
else // set client_encoding to utf8
{
// set client_encoding to test whether we are really connected (tcp_keepalive might have closed socket)
if (PqsqlWrapper.PQsetClientEncoding(pgConn, PgEncName.PG_UTF8) != 0)
{
goto broken;
}
}
return(true); // successfully reused connection
broken:
// reconnect with current connection setting
PqsqlWrapper.PQreset(pgConn);
connStatus = PqsqlWrapper.PQstatus(pgConn);
if (connStatus == ConnStatusType.CONNECTION_OK)
{
tranStatus = PqsqlWrapper.PQtransactionStatus(pgConn);
if (tranStatus == PGTransactionStatusType.PQTRANS_IDLE)
{
return(true); // successfully reconnected
}
}
else
{
tranStatus = PGTransactionStatusType.PQTRANS_UNKNOWN;
}
// could not reconnect: finally give up and clean up memory
PqsqlWrapper.PQfinish(pgConn);
return(false);
}
public virtual void Start()
{
#if CODECONTRACTS
Contract.Requires <InvalidOperationException>(
!string.IsNullOrWhiteSpace(Table) || !string.IsNullOrWhiteSpace(QueryInternal),
"Table and Query properties are null.");
#else
if (string.IsNullOrWhiteSpace(Table) && string.IsNullOrWhiteSpace(QueryInternal))
{
throw new InvalidOperationException("Table and Query properties are null.");
}
#endif
// PQexec does not set field types in PQresult for COPY FROM statements,
// just retrieve 0 rows for the field types of Table or Query
// TODO use PqsqlCopyColumnsCollection for ColumnList
StringBuilder sb = new StringBuilder();
if (!SuppressSchemaQueryInternal)
{
if (QueryInternal == null)
{
sb.AppendFormat("SELECT {0} FROM {1} LIMIT 0;", (object)ColumnList ?? '*', Table);
}
else
{
sb.AppendFormat("{0} LIMIT 0;", QueryInternal);
}
// fetch number of columns and store column information
using (PqsqlCommand cmd = new PqsqlCommand(mConn))
{
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = CopyTimeout;
using (PqsqlDataReader r = cmd.ExecuteReader(CommandBehavior.Default))
{
// just pick current row information
PqsqlColInfo[] src = r.RowInformation;
if (src == null)
{
throw new PqsqlException("Cannot retrieve RowInformation for '" +
QueryInternal ?? Table + "'.");
}
mColumns = src.Length;
mRowInfo = new PqsqlColInfo[mColumns];
Array.Copy(src, mRowInfo, mColumns);
}
}
sb.Clear();
}
// now build COPY FROM statement
sb.Append("COPY ");
if (QueryInternal == null)
{
sb.AppendFormat("{0} ", Table);
// always create list of columns if not SuppressSchemaQuery
if (string.IsNullOrEmpty(ColumnList))
{
if (!SuppressSchemaQueryInternal)
{
// just assume that we use standard table order
sb.AppendFormat("({0})", string.Join(",", mRowInfo.Select(r => r.ColumnName)));
}
}
else
{
// let user decide the column order
sb.AppendFormat("({0})", ColumnList);
}
}
else
{
sb.AppendFormat("({0})", QueryInternal);
}
sb.AppendFormat(" {0} BINARY", CopyStmtDirection);
byte[] q = PqsqlUTF8Statement.CreateUTF8Statement(sb);
IntPtr res;
ExecStatusType s = mConn.Exec(q, out res);
// result buffer should contain column information and PGconn should be in COPY_IN state
if (res == IntPtr.Zero || s != QueryResultType)
{
mConn.Consume(res); // we might receive several results...
throw new PqsqlException("Could not execute statement «" + sb + "»: " + mConn.GetErrorMessage());
}
// check first column format, current implementation will have all columns set to binary
if (PqsqlWrapper.PQfformat(res, 0) == 0)
{
mConn.Consume(res);
throw new PqsqlException("PqsqlCopyFrom only supports BINARY format.");
}
var nFields = PqsqlWrapper.PQnfields(res);
if (SuppressSchemaQueryInternal)
{
mColumns = nFields;
}
else
{
// sanity check
if (mColumns != nFields)
{
mConn.Consume(res);
throw new PqsqlException("Received wrong number of columns for " + sb);
}
}
// done with result inspection
PqsqlWrapper.PQclear(res);
}
protected string Error()
{
IntPtr res;
string err = string.Empty;
IntPtr conn = mConn.PGConnection;
res = PqsqlWrapper.PQgetResult(conn);
if (res != IntPtr.Zero)
{
ExecStatusType s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
if (s == ExecStatusType.PGRES_COPY_IN || s == ExecStatusType.PGRES_COPY_OUT)
{
if (s == ExecStatusType.PGRES_COPY_IN)
{
// still in COPY_IN mode? bail out!
byte[] b = PqsqlUTF8Statement.CreateUTF8Statement("COPY cancelled by client");
int end;
unsafe
{
fixed(byte *bs = b)
{
end = PqsqlWrapper.PQputCopyEnd(conn, bs);
}
}
if (end != 1)
{
err = err.Insert(0, "Cannot cancel COPY (" + s + "): ");
goto bailout;
}
}
res = PqsqlWrapper.PQgetResult(conn);
if (res != IntPtr.Zero)
{
s = PqsqlWrapper.PQresultStatus(res);
PqsqlWrapper.PQclear(res);
}
}
if (s != ExecStatusType.PGRES_COMMAND_OK)
{
err = err.Insert(0, "COPY failed (" + s + "): ");
goto bailout;
}
// consume all remaining results until we reach the NULL result
while ((res = PqsqlWrapper.PQgetResult(conn)) != IntPtr.Zero)
{
// always free mResult
PqsqlWrapper.PQclear(res);
}
return(err);
}
bailout:
err += mConn.GetErrorMessage();
return(err);
}