EnterpriseDB Supplemental Guide: EnterpriseDB libpq API, Callable Statements, Array Binding

3.2.2 EnterpriseDB libpq API

In order to provide support for SPL, the EnterpriseDB libpq interface library has provided the following functions in the libpq application programming interface:

3.2.2.1 Preparing a Callable Statement

PQprepareOut() is used for preparing a callable statement whereas for preparing a prepared statement, the method PQprepare() is used.

API Definition

extern PGresult *PQprepareOut(PGconn *conn,
                              const char *stmtName,
                              const char *query,
                              int nParams,
                              const Oid *paramTypes,
                              const  int *paramDirection);

3.2.2.2 Executing a Callable Statement

The following method is used for executing a callable statement.

API Definition

extern int PQsendQueryPreparedOut(PGconn *conn,
                             const char *stmtName,
                             int nParams,
                             const char *const * paramValues,
                             const int *paramLengths,
                             const int *paramFormats,
                             int resultFormat);

3.2.2.3 Fetching Cursors from a Result Set

This method is used for fetching cursors from PGresult(resultset).

API Definition

extern int PQCursorResult(PGconn *conn,PGresult *res);

3.2.2.4 Returning the Number of Cursors Fetched from a Result Set

This method is used for returning the number of cursors that were fetched from PGresult(resultset).

API Definition

extern int PQnCursor(const PGresult *res);

3.2.2.5 Retrieving the Out Parameter from the Result

PQgetOutResult() is used for retrieving PGresult which contains the values of IN/OUT/IN OUT parameters. The values of these IN/OUT/IN OUT parameters can then be retrieved by passing PGresult to the PQgetvalue() method.

API Definition

extern PGresult *PQgetOutResult(PGconn *conn);

3.2.2.6 Cursor Support Statements in EnterpriseDB

PQgetCursorResult() is used for fetching cursor record (PGresult) residing on a specific index where tupe_num is a row in a recordset and field_num is a column in the recordset table.

API Definition

extern PGresult *PQgetCursorResult(const PGresult *res, int tupe_num, int field_num);

3.2.2.7 Array Binding

The feature of array binding is the ability to bind an array of data over the wire level protocol using prepared statements. This essentially means all the data that needs to be bound is sent over the wire in one shot. Once the back end receives the bulk data, it will make use of that data to perform insert or update operations.

You can perform bulk operations with prepared statement. So for preparing the statement you can call following function:

PGresult *PQprepare(PGconn *conn,
                    const char *stmtName,
                    const char *query,
                    int nParams,
                    const Oid *paramTypes);

Details of PQprepare() can be found in the prepared statement section.

The following are the functions that can be used to perform bulk operations.

● PQBulkStart

● PQexecBulk

● PQBulkFinish

● PQexecBulkPrepared

3.2.2.8 PQBulkStart

This function is used to initialize bulk operations on the server. This function call is mandatory before sending bulk data to the server.

PQBulkStart initializes the bulk operation of the previously prepared statement specified by stmtName. This initializes the bulk operation to receive data in a format specified by paramFormats.

API Definition

PGresult * PQBulkStart(PGconn *conn,
                       const char * Stmt_Name,
                       unsigned int nCol,
                       const int *paramFmts);

3.2.2.9 PQexecBulk

This function is used to send data (paramValues) to the server against the statement that was previously initialized for bulk operation using PQBulkStart ().

This function can be used more than once after PQBulkStart in order to send bulk data multiple times. See the example for more details.

API Definition

PGresult *PQexecBulk(PGconn *conn,
                     unsigned int nRows,
                     const char *const * paramValues,
                     const int *paramLengths);

3.2.2.10 PQBulkFinish

This function stops the previously started bulk operation. After this the previously perpared statement will not be destroyed. You can use this statement again without preparing it again.

API Definition

PGresult *PQBulkFinish(PGconn *conn);

3.2.2.11 PQexecBulkPrepared

PQexecBulkPrepared sends a request to execute a prepared statement with given parameters, and waits for the result/status. This function is a combined function of PQbulkStart (), PQexecBulk (), and PQBulkFinish (). So if you are using this function you don’t need to start or stop the bulk operation. This function starts the bulk operation, passes the data to the server, and closes the bulk operation.

The command to be executed is specified by naming a previously prepared statement instead of giving a query string. stmtName specifies the name of prepared statement. This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. The statement must have been prepared previously in the current session.

API Definition

PGresult *PQexecBulkPrepared(PGconn *conn,
                             const char *stmtName,
                             unsigned int nCols,
                             unsigned int nRows,
                             const char *const *paramValues,
                             const int *paramLengths,		    
                             const int *paramFormats);

3.2.2.12 Example Code (Using PQBulkStart, PQexecBulk, PQBulkFinish)

The following example uses PGBulkStart, PQexecBulk, and PQBulkFinish.

void InsertDataUsingBulkStyle( PGconn *conn )
{
    PGresult            *res;
    Oid                 paramTypes[2];
    char                *paramVals[5][2];
    int                 paramLens[5][2];
    int                 paramFmts[2];
    int                 i;

    int                 a[5] = { 10, 20, 30, 40, 50 };
    char                b[5][10] = { "Test_1", "Test_2", "Test_3",  "Test_4", "Test_5" };


    paramTypes[0] = 23;
    paramTypes[1] = 1043;
    res = PQprepare( conn, "stmt_1", "INSERT INTO testtable1 values( $1, $2 )", 2, paramTypes );
    PQclear( res );

    paramFmts[0] = 1;   /* Binary format */
    paramFmts[1] = 0;

    for( i = 0; i < 5; i++ )
    {
        a[i] = htonl( a[i] );
        paramVals[i][0] = &(a[i]);
        paramVals[i][1] = b[i];

        paramLens[i][0] = 4;
        paramLens[i][1] = strlen( b[i] );
    }

	res = PQBulkStart(conn, "stmt_1", 2, paramFmts);
	PQclear( res );
	printf( "< -- PQBulkStart -- >\n" );

	res = PQexecBulk(conn, 5, (const char *const *)paramVals, (const int *)paramLens);
	PQclear( res );
	printf( "< -- PQexecBulk -- >\n" );

	res = PQexecBulk(conn, 5, (const char *const *)paramVals, (const int *)paramLens);
	PQclear( res );
	printf( "< -- PQexecBulk -- >\n" );

	res = PQBulkFinish(conn);
	PQclear( res );
	printf( "< -- PQBulkFinish -- >\n" );
}

3.2.2.13 Example Code (Using PQexecBulkPrepared)

The following example uses PQexecBulkPrepared.

void InsertDataUsingBulkStyleCombinedVersion( PGconn *conn )
{
	PGresult            *res;
	Oid                 paramTypes[2];
	char                *paramVals[5][2];
	int                 paramLens[5][2];
	int                 paramFmts[2];
	int                 i;

	int                 a[5] = { 10, 20, 30, 40, 50 };
	char                b[5][10] = { "Test_1", "Test_2", "Test_3", "Test_4", "Test_5" };

	paramTypes[0] = 23;
	paramTypes[1] = 1043;
	res = PQprepare( conn, "stmt_2", "INSERT INTO testtable1 values( $1, $2 )", 2, paramTypes );
	PQclear( res );

	paramFmts[0] = 1;   /* Binary format */
	paramFmts[1] = 0;

	for( i = 0; i < 5; i++ )
	{
		a[i] = htonl( a[i] );
		paramVals[i][0] = &(a[i]);
		paramVals[i][1] = b[i];

		paramLens[i][0] = 4;
		paramLens[i][1] = strlen( b[i] );
	}

res = PQexecBulkPrepared(conn, "stmt_2", 2, 5, (const char *const *)paramVals,(const int *)paramLens, (const int *)paramFmts);
	PQclear( res );
}