diff options
Diffstat (limited to 'doc/manual/libpq.html')
| -rw-r--r-- | doc/manual/libpq.html | 815 |
1 files changed, 815 insertions, 0 deletions
diff --git a/doc/manual/libpq.html b/doc/manual/libpq.html new file mode 100644 index 00000000000..71f8e7a1502 --- /dev/null +++ b/doc/manual/libpq.html @@ -0,0 +1,815 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - LIBPQ</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xindex.html">[ Previous ]</A> +<A HREF="lobj.html">[ Next ]</A> +</font> +<HR> +<H1>12. <B>LIBPQ</B></H1> +<HR> + <B>LIBPQ</B> is the application programming interface to POSTGRES. + <B>LIBPQ</B> is a set of library routines which allows + client programs to pass queries to the POSTGRES backend + server and to receive the results of these queries. + This version of the documentation describes the <B>C</B> + interface library. Three short programs are included + at the end of this section to show how to write programs that use <B>LIBPQ</B>. + There are several examples of <B>LIBPQ</B> applications in the + following directories: + +<pre> ../src/test/regress + ../src/test/examples + ../src/bin/psql +</pre> + Frontend programs which use <B>LIBPQ</B> must include the + header file <CODE>libpq-fe.h</CODE> and must link with the <B>libpq</B> + library. + +<H2><A NAME="control-and-initialization">12.1. Control and Initialization</A></H2> + The following environment variables can be used to set + up default environment values to avoid hard-coding + database names into an application program: + +<UL> + <LI><B>PGHOST</B> sets the default server name. + <LI><B>PGOPTIONS</B> sets additional runtime options for the POSTGRES backend. + <LI><B>PGPORT</B> sets the default port for communicating with the POSTGRES backend. + <LI><B>PGTTY</B> sets the file or tty on which debugging messages from the backend server are displayed. + <LI><B>PGDATABASE</B> sets the default POSTGRES database name. + <LI><B>PGREALM</B> sets the Kerberos realm to use with POSTGRES, if it is different from the local realm. If + <LI><B>PGREALM</B> is set, POSTGRES applications will attempt + authentication with servers for this realm and use + separate ticket files to avoid conflicts with local + ticket files. This environment variable is only + used if Kerberos authentication is enabled. +</UL> + +<H2><A NAME="database-connection-functions">12.2. Database Connection Functions</A></H2> + The following routines deal with making a connection to + a backend from a <B>C</B> program. + + <DL> + <DT><B>PQsetdb</B> + <DD>Makes a new connection to a backend. +<pre> PGconn *PQsetdb(char *pghost, + char *pgport, + char *pgoptions, + char *pgtty, + char *dbName); +</pre> + <DD>If any argument is NULL, then the corresponding + environment variable is checked. If the environment variable is also not set, then hardwired + defaults are used. + <DD>PQsetdb always returns a valid PGconn pointer. + <DD>The PQstatus (see below) command should be called + to ensure that a connection was properly made + before queries are sent via the connection. <B>LIBPQ</B> + programmers should be careful to maintain the + <DD>PGconn abstraction. Use the accessor functions + below to get at the contents of PGconn. Avoid + directly referencing the fields of the PGconn + structure as they are subject to change in the + future.<br> + <DT><B>PQdb</B> + <DD>Returns the database name of the connection. +<pre> char *PQdb(PGconn *conn) +</pre><br> + <DT><B>PQhost</B> + <DD>Returns the host name of the connection. +<pre> char *PQhost(PGconn *conn) +</pre><br> + <DT><B>PQoptions</B> + <DD>Returns the pgoptions used in the connection. +<pre> char *PQoptions(PGconn *conn) +</pre><br> + <DT><B>PQport</B> + <DD>Returns the pgport of the connection. +<pre> char *PQport(PGconn *conn) +</pre><br> + <DT><B>PQtty</B> + <DD>Returns the pgtty of the connection. +<pre> char *PQtty(PGconn *conn) +</pre><br> + <DT><B>PQstatus</B> + <DD>Returns the status of the connection. + <DD>The status can be CONNECTION_OK or CONNECTION_BAD. +<pre> ConnStatusType *PQstatus(PGconn *conn) +</pre><br> + <DT><B>PQerrorMessage</B> + <DD>Returns the error message associated with the connection +<pre> char *PQerrorMessage(PGconn* conn); +</pre><br> + + <DT><B>PQfinish</B> + <DD>Close the connection to the backend. Also frees + memory used by the PGconn structure. The PGconn + pointer should not be used after PQfinish has been + called. +<pre> void PQfinish(PGconn *conn) +</pre><br> + <DT><B>PQreset</B> + <DD>Reset the communication port with the backend. + This function will close the IPC socket connection + to the backend and attempt to reestablish a new + connection to the same backend. +<pre> void PQreset(PGconn *conn) +</pre><br> + <DT><B>PQtrace</B> + <DD>Enables tracing of messages passed between the + frontend and the backend. The messages are echoed + to the debug_port file stream. +<pre> void PQtrace(PGconn *conn, + FILE* debug_port); +</pre><br> + <DT><B>PQuntrace</B> + <DD>Disables tracing of messages passed between the + frontend and the backend. +<pre> void PQuntrace(PGconn *conn); +</pre><br> +</DL> +<H2><A NAME="query-execution-functions">12.3. Query Execution Functions</A></H2> +<DL> + <DT><B>PQexec</B> + <DD>Submit a query to POSTGRES. Returns a PGresult + pointer if the query was successful or a NULL otherwise. If a NULL is returned, PQerrorMessage can + be used to get more information about the error. +<pre> PGresult *PQexec(PGconn *conn, + char *query); +</pre> + <DD>The <B>PGresult</B> structure encapsulates the query + result returned by the backend. <B>LIBPQ</B> programmers + should be careful to maintain the PGresult + abstraction. Use the accessor functions described + below to retrieve the results of the query. Avoid + directly referencing the fields of the PGresult + structure as they are subject to change in the + future.<br> + <DT><B>PQresultStatus</B> + <DD>Returns the result status of the query. PQresultStatus can return one of the following values: +<pre> PGRES_EMPTY_QUERY, + PGRES_COMMAND_OK, /* the query was a command */ + PGRES_TUPLES_OK, /* the query successfully returned tuples */ + PGRES_COPY_OUT, + PGRES_COPY_IN, + PGRES_BAD_RESPONSE, /* an unexpected response was received */ + PGRES_NONFATAL_ERROR, + PGRES_FATAL_ERROR +</pre> + <DD>If the result status is PGRES_TUPLES_OK, then the + following routines can be used to retrieve the + tuples returned by the query.<br> + <DT><B>PQntuples</B> returns the number of tuples (instances) + in the query result. + +<pre> int PQntuples(PGresult *res); +</pre><br> + <DT><B>PQnfields</B> + <DD>Returns the number of fields + (attributes) in the query result. + +<pre> int PQnfields(PGresult *res); +</pre><br> + <DT><B>PQfname</B> + <DD>Returns the field (attribute) name associated with the given field index. Field indices + start at 0. + +<pre> char *PQfname(PGresult *res, + int field_index); +</pre><br> + <DT><B>PQfnumber</B> + <DD>Returns the field (attribute) index + associated with the given field name. + +<pre> int PQfnumber(PGresult *res, + char* field_name); +</pre><br> + <DT><B>PQftype</B> + <DD>Returns the field type associated with the + given field index. The integer returned is an + internal coding of the type. Field indices start + at 0. + +<pre> Oid PQftype(PGresult *res, + int field_num); +</pre><br> + <DT><B>PQfsize</B> + <DD>Returns the size in bytes of the field + associated with the given field index. If the size + returned is -1, the field is a variable length + field. Field indices start at 0. + +<pre> int2 PQfsize(PGresult *res, + int field_index); +</pre><br> + <DT><B>PQgetvalue</B> + <DD>Returns the field (attribute) value. + For most queries, the value returned by PQgetvalue + is a null-terminated ASCII string representation + of the attribute value. If the query was a result + of a <B>BINARY</B> cursor, then the value returned by + PQgetvalue is the binary representation of the + type in the internal format of the backend server. + It is the programmer's responsibility to cast and + convert the data to the correct C type. The value + returned by PQgetvalue points to storage that is + part of the PGresult structure. One must explicitly + copy the value into other storage if it is to + be used past the lifetime of the PGresult structure itself. + +<pre> char* PQgetvalue(PGresult *res, + int tup_num, + int field_num); +</pre><br> + <DT><B>PQgetlength</B> + <DD>Returns the length of a field + (attribute) in bytes. If the field is a struct + varlena, the length returned here does not include + the size field of the varlena, i.e., it is 4 bytes + less. +<pre> int PQgetlength(PGresult *res, + int tup_num, + int field_num); +</pre><br> + <DT><B>PQcmdStatus</B> + Returns the command status associated with the + last query command. +<pre> + char *PQcmdStatus(PGresult *res); +</pre><br> + <DT><B>PQoidStatus</B> + Returns a string with the object id of the tuple + inserted if the last query is an INSERT command. + Otherwise, returns an empty string. +<pre> char* PQoidStatus(PGresult *res); +</pre><br> + <DT><B>PQprintTuples</B> + Prints out all the tuples and, optionally, the + attribute names to the specified output stream. + The programs psql and monitor both use PQprintTuples for output. + +<pre> void PQprintTuples( + PGresult* res, + FILE* fout, /* output stream */ + int printAttName,/* print attribute names or not*/ + int terseOutput, /* delimiter bars or not?*/ + int width /* width of column, variable width if 0*/ + ); +</pre><br> + + <DT><B>PQclear</B> + Frees the storage associated with the PGresult. + Every query result should be properly freed when + it is no longer used. Failure to do this will + result in memory leaks in the frontend application. +<pre> void PQclear(PQresult *res); +</pre><br> +</DL> +<H2><A NAME="fast-path">12.4. Fast Path</A></H2> + POSTGRES provides a fast path interface to send function calls to the backend. This is a trapdoor into + system internals and can be a potential security hole. + Most users will not need this feature. + +<pre> PGresult* PQfn(PGconn* conn, + int fnid, + int *result_buf, + int *result_len, + int result_is_int, + PQArgBlock *args, + int nargs); +</pre><br> + + The fnid argument is the object identifier of the function to be executed. result_buf is the buffer in which + to load the return value. The caller must have allocated sufficient space to store the return value. The + result length will be returned in the storage pointed + to by result_len. If the result is to be an integer + value, than result_is_int should be set to 1; otherwise + it should be set to 0. args and nargs specify the + arguments to the function. +<pre> typedef struct { + int len; + int isint; + union { + int *ptr; + int integer; + } u; + } PQArgBlock; +</pre> + PQfn always returns a valid PGresult*. The resultStatus should be checked before the result is used. The + caller is responsible for freeing the PGresult with + PQclear when it is not longer needed. +<H2><A NAME="asynchronous-notification">12.5. Asynchronous Notification</A></H2> + POSTGRES supports asynchronous notification via the + LISTEN and NOTIFY commands. A backend registers its + interest in a particular relation with the LISTEN command. All backends listening on a particular relation + will be notified asynchronously when a NOTIFY of that + relation name is executed by another backend. No + additional information is passed from the notifier to + the listener. Thus, typically, any actual data that + needs to be communicated is transferred through the + relation. + <B>LIBPQ</B> applications are notified whenever a connected + backend has received an asynchronous notification. + However, the communication from the backend to the + frontend is not asynchronous. Notification comes + piggy-backed on other query results. Thus, an application must submit queries, even empty ones, in order to + receive notice of backend notification. In effect, the + <B>LIBPQ</B> application must poll the backend to see if there + is any pending notification information. After the + execution of a query, a frontend may call PQNotifies to + see if any notification data is available from the + backend. +<DL> + <DT><B>PQNotifies</B> + <DD>returns the notification from a list of unhandled + notifications from the backend. Returns NULL if + there are no pending notifications from the backend. PQNotifies behaves like the popping of a + stack. Once a notification is returned from PQnotifies, it is considered handled and will be + removed from the list of notifications. +<pre> PGnotify* PQNotifies(PGconn *conn); +</pre><br> +</DL> + The second sample program gives an example of the use + of asynchronous notification. +<H2><A NAME="functions-associated-with-the-copy-command">12.6. Functions Associated with the COPY Command</A></H2> + The copy command in POSTGRES has options to read from + or write to the network connection used by <B>LIBPQ</B>. + Therefore, functions are necessary to access this network connection directly so applications may take full + advantage of this capability. +<DL> + <DT><B>PQgetline</B> + <DD>Reads a newline-terminated line of characters + (transmitted by the backend server) into a buffer + string of size length. Like fgets(3), this routine copies up to length-1 characters into string. + It is like gets(3), however, in that it converts + the terminating newline into a null character. + PQgetline returns EOF at EOF, 0 if the entire line + has been read, and 1 if the buffer is full but the + terminating newline has not yet been read. + Notice that the application must check to see if a + new line consists of the single character ".", + which indicates that the backend server has finished sending the results of the copy command. + Therefore, if the application ever expects to + receive lines that are more than length-1 characters long, the application must be sure to check + the return value of PQgetline very carefully. + The code in + +<pre> ../src/bin/psql/psql.c +</pre> + contains routines that correctly handle the copy + protocol. +<pre> int PQgetline(PGconn *conn, + char *string, + int length) +</pre><br> + <DT><B>PQputline</B> + <DD>Sends a null-terminated string to the backend + server. + The application must explicitly send the single + character "." to indicate to the backend that it + has finished sending its data. + +<pre> void PQputline(PGconn *conn, + char *string); +</pre><br> + <DT><B>PQendcopy</B> + <DD>Syncs with the backend. This function waits until + the backend has finished the copy. It should + either be issued when the last string has been + sent to the backend using PQputline or when the + last string has been received from the backend + using PGgetline. It must be issued or the backend + may get "out of sync" with the frontend. Upon + return from this function, the backend is ready to + receive the next query. + The return value is 0 on successful completion, + nonzero otherwise. +<pre> int PQendcopy(PGconn *conn); +</pre><br> + As an example: +<pre> PQexec(conn, "create table foo (a int4, b char16, d float8)"); + PQexec(conn, "copy foo from stdin"); + PQputline(conn, "3<TAB>hello world<TAB>4.5\n"); + PQputline(conn,"4<TAB>goodbye world<TAB>7.11\n"); + ... + PQputline(conn,".\n"); + PQendcopy(conn); +</pre><br> +</DL> +<H2><A NAME="tracing-functions">12.7. <B>LIBPQ</B> Tracing Functions</A></H2> +<DL> + <DT><B>PQtrace</B> + <DD>Enable tracing of the frontend/backend communication to a debugging file stream. +<pre> void PQtrace(PGconn *conn + FILE *debug_port) +</pre><br> + + <DT><B>PQuntrace</B> + <DD>Disable tracing started by PQtrace +<pre> void PQuntrace(PGconn *conn) +</pre><br> +</DL> +<H2><A NAME="authentication-functions">12.8. User Authentication Functions</A></H2> + If the user has generated the appropriate authentication credentials (e.g., obtaining <B>Kerberos</B> tickets), + the frontend/backend authentication process is handled + by <B>PQexec</B> without any further intervention. The following routines may be called by <B>LIBPQ</B> programs to tailor the behavior of the authentication process. +<DL> + <DT><B>fe_getauthname</B> + <DD>Returns a pointer to static space containing whatever name the user has authenticated. Use of this + routine in place of calls to getenv(3) or getpwuid(3) by applications is highly recommended, as + it is entirely possible that the authenticated + user name is not the same as value of the <B>USER</B> + environment variable or the user's entry in + <CODE>/etc/passwd</CODE>. + +<pre> char *fe_getauthname(char* errorMessage) +</pre><br> + <DT><B>fe_setauthsvc</B> + <DD>Specifies that <B>LIBPQ</B> should use authentication + service name rather than its compiled-in default. + <DD>This value is typically taken from a command-line + switch. +<pre> void fe_setauthsvc(char *name, + char* errorMessage) +</pre> + <DD>Any error messages from the authentication + attempts are returned in the errorMessage argument. +</DL> + +<H2><A NAME="bugs">12.9. BUGS</A></H2> + The query buffer is 8192 bytes long, and queries over + that length will be silently truncated. +<H2><A NAME="sample-programs">12.10. Sample Programs</H2> +<p> +<H3><A NAME="sample-program-1">12.10.1. Sample Program 1</A></H3> +<pre> + /* + * testlibpq.c + * Test the C version of LIBPQ, the POSTGRES frontend library. + * + * + */ + #include <stdio.h> + #include "libpq-fe.h" +<p> + void + exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; +<p> + /* FILE *debug; */ +<p> + PGconn* conn; + PGresult* res; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ + dbName = "template1"; +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); +<p> + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + /* debug = fopen("/tmp/trace.out","w"); */ + /* PQtrace(conn, debug); */ +<p> + /* start a transaction block */ + + res = PQexec(conn,"BEGIN"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"BEGIN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + /* fetch instances from the pg_database, the system catalog of databases*/ + res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"DECLARE CURSOR command failed0); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); +<p> + res = PQexec(conn,"FETCH ALL in myportal"); + if (PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr,"FETCH ALL command didn't return tuples properly0); + PQclear(res); + exit_nicely(conn); + } +<p> + /* first, print out the attribute names */ + nFields = PQnfields(res); + for (i=0; i < nFields; i++) { + printf("%-15s",PQfname(res,i)); + } + printf("0); +<p> + /* next, print out the instances */ + for (i=0; i < PQntuples(res); i++) { + for (j=0 ; j < nFields; j++) { + printf("%-15s", PQgetvalue(res,i,j)); + } + printf("0); + } +<p> + PQclear(res); +<p> + /* close the portal */ + res = PQexec(conn, "CLOSE myportal"); + PQclear(res); +<p> + /* end the transaction */ + res = PQexec(conn, "END"); + PQclear(res); +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); + + /* fclose(debug); */ + } +</pre> +<p> +<H3><A NAME="sample-program-2">12.10.2. Sample Program 2</A></H3> +<pre> + /* + * testlibpq2.c + * Test of the asynchronous notification interface + * + populate a database with the following: +<p> + CREATE TABLE TBL1 (i int4); +<p> + CREATE TABLE TBL2 (i int4); +<p> + CREATE RULE r1 AS ON INSERT TO TBL1 DO [INSERT INTO TBL2 values (new.i); NOTIFY TBL2]; +<p> + * Then start up this program + * After the program has begun, do +<p> + INSERT INTO TBL1 values (10); +<p> + * + * + */ + #include <stdio.h> + #include "libpq-fe.h" +<p> + void exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; +<p> + PGconn* conn; + PGresult* res; + PGnotify* notify; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ + dbName = getenv("USER"); /* change this to the name of your test database*/ +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + res = PQexec(conn, "LISTEN TBL2"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"LISTEN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + while (1) { + /* async notification only come back as a result of a query*/ + /* we can send empty queries */ + res = PQexec(conn, " "); + /* printf("res->status = %s0, pgresStatus[PQresultStatus(res)]); */ + /* check for asynchronous returns */ + notify = PQnotifies(conn); + if (notify) { + fprintf(stderr, + "ASYNC NOTIFY of '%s' from backend pid '%d' received0, + notify->relname, notify->be_pid); + free(notify); + break; + } + PQclear(res); + } +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); +<p> + } +</pre> +<p> +<H3><A NAME="sample-program-3">12.10.3. Sample Program 3</A></H3> +<pre> + /* + * testlibpq3.c + * Test the C version of LIBPQ, the POSTGRES frontend library. + * tests the binary cursor interface + * + * + * + populate a database by doing the following: +<p> + CREATE TABLE test1 (i int4, d float4, p polygon); +<p> + INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon); +<p> + INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon); +<p> + the expected output is: +<p> + tuple 0: got + i = (4 bytes) 1, + d = (4 bytes) 3.567000, + p = (4 bytes) 2 points boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000) + tuple 1: got + i = (4 bytes) 2, + d = (4 bytes) 89.050003, + p = (4 bytes) 2 points boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000) +<p> + * + */ + #include <stdio.h> + #include "libpq-fe.h" + #include "utils/geo-decls.h" /* for the POLYGON type */ +<p> + void exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; + int i_fnum, d_fnum, p_fnum; +<p> + PGconn* conn; + PGresult* res; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ +<p> + dbName = getenv("USER"); /* change this to the name of your test database*/ +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); +<p> + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + /* start a transaction block */ + res = PQexec(conn,"BEGIN"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"BEGIN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + /* fetch instances from the pg_database, the system catalog of databases*/ + res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from test1"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"DECLARE CURSOR command failed0); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); +<p> + res = PQexec(conn,"FETCH ALL in mycursor"); + if (PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr,"FETCH ALL command didn't return tuples properly0); + PQclear(res); + exit_nicely(conn); + } +<p> + i_fnum = PQfnumber(res,"i"); + d_fnum = PQfnumber(res,"d"); + p_fnum = PQfnumber(res,"p"); +<p> + for (i=0;i<3;i++) { + printf("type[%d] = %d, size[%d] = %d0, + i, PQftype(res,i), + i, PQfsize(res,i)); + } + for (i=0; i < PQntuples(res); i++) { + int *ival; + float *dval; + int plen; + POLYGON* pval; + /* we hard-wire this to the 3 fields we know about */ + ival = (int*)PQgetvalue(res,i,i_fnum); + dval = (float*)PQgetvalue(res,i,d_fnum); + plen = PQgetlength(res,i,p_fnum); +<p> + /* plen doesn't include the length field so need to increment by VARHDSZ*/ + pval = (POLYGON*) malloc(plen + VARHDRSZ); + pval->size = plen; + memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen); + printf("tuple %d: got0, i); + printf(" i = (%d bytes) %d,0, + PQgetlength(res,i,i_fnum), *ival); + printf(" d = (%d bytes) %f,0, + PQgetlength(res,i,d_fnum), *dval); + printf(" p = (%d bytes) %d points boundbox = (hi=%f/%f, lo = %f,%f)0, + PQgetlength(res,i,d_fnum), + pval->npts, + pval->boundbox.xh, + pval->boundbox.yh, + pval->boundbox.xl, + pval->boundbox.yl); + } +<p> + PQclear(res); +<p> + /* close the portal */ + res = PQexec(conn, "CLOSE mycursor"); + PQclear(res); +<p> + /* end the transaction */ + res = PQexec(conn, "END"); + PQclear(res); +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); +<p> + } +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xindex.html">[ Previous ]</A> +<A HREF="lobj.html">[ Next ]</A> +</font> +</BODY> +</HTML> |