The pg_stream class

Public functions

Initialization

pg_stream (const char* query, pg_cnx& db [, int prepare_mode])

Constructor. Creates a pgstream object to execute the SQL query through the db connexion. query is a SQL sentence that can contains parameters. Parameters begin with a colon followed by a name (alphanumeric), optionally followed by a type name enclosed in angle brackets.
Example:

pg_stream s("UPDATE words SET word=:txt<text> WHERE key=:i2", db);

prepare_mode tells if the query should be prepared prior to execution. The values can be:

• 0: no prepare will be issued.
• 1: the query will be prepared.
• 2: the query will be prepared if the connexion's prepare_statements option is set to true, otherwise it won't. That's the default behavior.

pg_stream (std::string query, pg_cnx& db [, int prepare_mode])

This is the same as the previous constructor except for the usage of the std::string type for query. It is provided for convenience.

[virtual] ~pg_stream ()

Destructor. Deallocates the resources used by the object.

void prepare([bool prep=true])

Tell whether the query should be prepared or not (that overwrites the effect of the prepare_mode parameter of the constructor). Preparing a query in a pg_stream means that a SQL PREPARE statement will be implicitly invoked prior to the first execution, so that the server will keep track of the query and avoid to recompute the execution plan for each execution. It makes sense for queries that are expected to be executed many times. When using prepared queries containing parameters, they should not be passed inline (see the inline_parameters pg_cnx option), or the performance will be degraded instead of enhanced.

void use_bind_variables([bool b=true])

Set up pg_stream to internally use bind variables (variables whose values are passed to the database server outside of the SQL text) or not. If not, the placeholders for variables in the SQL sentence are replaced by their values inline. This overrides the pg_cnx's option bind_variables. The function should be called before any variable value is passed to the pg_stream object.
Note that either way, it is not necessary to quote the variable values, the pg_stream code will implicitly do it when needed.

Passing input values

Each distinct variable in the query should have a value assigned to it by using one of the following stream operators, in the order in which the variables appear in the query. If a variable is referenced several times in the query, the assignment of the value shouldn't be repeated.
Once values are assigned to all variables, the query is launched.

pg_stream& operator<<(const char* p)

Bind a C string to the next non-bound parameter (text sql type guessed). A null pointer for p is taken as a sql null value for the parameter. The caller must not escape characters in the string (by calling PQescapeString), since pgstream does it when it's necessary.

pg_stream& operator<<(std::string s)

Bind a C++ string to the next unbound parameter. (text sql type guessed)

pg_stream& operator<<(int i)

Bind a signed integer to the next unbound parameter (integer sql type guessed).

pg_stream& operator<<(unsigned int i)

Bind an unsigned integer to the next unbound parameter (integer sql type guessed).

pg_stream& operator<<(bool b)

Bind a boolean to the next unbound parameter. (bool sql type guessed).

pg_stream& operator<<(short i)

Bind a signed short integer to the next unbound parameter (smallint sql type guessed).

pg_stream& operator<<(unsigned short i)

Bind an unsigned short integer to the next unbound parameter (smallint sql type guessed).

pg_stream& operator<<(long i)

Bind a signed long integer to the next unbound parameter (integer sql type guessed).

pg_stream& operator<<(unsigned long i)

Bind an unsigned long integer to the next unbound parameter (integer sql type guessed).

pg_stream& operator<<(double d)

Bind a double to the next unbound parameter (numeric sql type guessed)

pg_stream& operator<<(long long i)

Bind a signed long long integer (typically, 64 bits wide) to the next unbound parameter (bigint sql type guessed)

pg_stream& operator<<(unsigned long long i)

Bind an unsigned long long integer (typically, 64 bits wide) to the next unbound parameter (bigint sql type guessed).

pg_stream& operator<<(pg_bytea& b)

Bind a pg_bytea object to the next unbound parameter (bytea sql type guessed).

pg_stream& operator<<(sql_null n)

Bind a NULL value to the next unbound parameter (no sql type guessed)
Note: when not using inline parameters (see pg_cnx inline_parameters option), a bind variable should be explicitly typed within the sql query (typename given between angle brackets) if a sql_null value is to be assigned to it through that function. Failing to do so will cause an exception.
The reason behind that requirement is that, in this context, the pg_stream code needs the exact type, but can't deduce it since NULL is non-typed by nature.

Getting results

Query results can be loaded into host variables using the stream output operators below.
The pg_stream code automatically steps to the next row when all columns from a row have been read.
NULLs can be detected by calling val_is_null() immediately after fetching a value. Such values will also be set to 0 for numeric C++ types and the empty string for char* and string types.

bool eos()

End of stream. Returns true when there are no more rows to read in the result set, false otherwise.

pg_stream& operator>>(short&)

Fetch the next column into a short integer.

pg_stream& operator>>(unsigned short&)

Fetch the next column into an unsigned short integer.

pg_stream& operator>>(int&)

Fetch the next column into an int.

pg_stream& operator>>(unsigned int&)

Fetch the next column into an unsigned int.

pg_stream& operator>>(char* p)

Fetch the next column into a character buffer pointed to by p. The buffer must have at least the size of the result (Better use std::string to avoid buffer overflow problems).

pg_stream& operator>>(std::string&)

Fetch the next column into a C++ string. Note that this operator may also be used to retrieve result columns in their ASCII representation, for columns that have a type that is not supported by pg_stream. The programmer can then convert the std::string into whatever memory representation is suitable for further use.

pg_stream& operator>>(pg_bytea&)

Fetch the next column into a pg_bytea class.

pg_stream& operator>>(double&)

Fetch the next column into a double variable.

pg_stream& operator>>(bool&)

Fetch the next column into a bool variable.

Execution results

int affected_rows() const

Returns the number of rows affected by queries like UPDATE or DELETE that return no result sets.

try {
 ...
}
catch(pg_excpt p) {
 ...
}

Implementation notes

1. Prepared statements generated by the pg_stream code are named pgst_prep_ followed by an hexadecimal number. Thus such names should be considered as reserved by the pgstream library, and not used by the application code.

2. C++ int and long types are assumed to match the INT PostgreSQL type: this is incorrect for compilers that have 64 bits wide integers. Using out-of-line bind parameters with such compilers is currently not supported by the pgstream library, unless the sql types are made explicit in the queries. Also inline parameters should no be affected by this problem.

3. OID types supported by the pgstream library through the PQExecParams and PQExecPrepared libpq functions are: oid_int8, oid_bool, oid_bytea, oid_int4, oid_numeric, oid_int2, oid_text, oid_varchar . Other types are currently not supported.