Thread: [NOVICE] Executing prepared statements in the multithreaded envs
Hi All!
I'd like to use a pool of connections in multi-threaded environment, so:
1. I created a pool of N-connection by calling PQconnectdb().
2. Do I'm need to calling PQprepare() for the every sql_query for the every connection from the pool ?
#define POOLSZ 3
PGconn pool[POOLSZ];
#define QUERYSZ 2
const char SQL_Qry_name [QUERYSZ][32] = { {"add_pdp11"}, {"add_mvax2"} };
const char SQL_Qry_expr [QUERYSZ][128] = { {"select add_pdp11($1, $1, $3);"}, {"select add_mvax2($1, $1, $3, $4, $5);" } };
/* Create connections pool ... */
for (i = 0; i < POOLSZ; i++)
pool[i] = PGconnectdb(...);
/* Prepare every SQL expression in the every session */
for (i = 0; i < POOLSZ; i++)
for (j = 0; j < QUERYSZ; i++)
PQrepare(SQL_Qry_name[j], SQL_Qry_expr[j], ...);
So, do I running in the right direction ?
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
Hi All!I'd like to use a pool of connections in multi-threaded environment, so:1. I created a pool of N-connection by calling PQconnectdb().2. Do I'm need to calling PQprepare() for the every sql_query for the every connection from the pool ?
Named prepared statements are session-local information. When you first start a new session there are none. The ones you create remain until you either disconnect the session or "RESET" the session.
David J.
Ho to check the PQprepare result ?
NULL or noNULL ?
17.10.2017, 17:09, "David G. Johnston" <david.g.johnston@gmail.com>:
Hi All!I'd like to use a pool of connections in multi-threaded environment, so:1. I created a pool of N-connection by calling PQconnectdb().2. Do I'm need to calling PQprepare() for the every sql_query for the every connection from the pool ?Named prepared statements are session-local information. When you first start a new session there are none. The ones you create remain until you either disconnect the session or "RESET" the session.David J.
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
Ho to check the PQprepare result ?NULL or noNULL ?
That seems like it should be documented...I'm not a C programmer and don't have time right now to go look at the docs for you.
David J.
A quotation from the docs "for me" :
PQprepare
Submits a request to create a prepared statement with the given parameters, and waits for com-
pletion.
PGresult
* PQprepare(PGconn * conn,
const char
* stmtName,
const char
* query,
int nParams,
const Oid
* paramTypes);
PQprepare creates a prepared statement for later execution with PQexecPrepared . This fea-
ture allows commands to be executed repeatedly without being parsed and planned each time;
see PREPARE for details. PQprepare is supported only in protocol 3.0 and later connections; it
will fail when using protocol 2.0.
The function creates a prepared statement named stmtName from the query string, which must
contain a single SQL command. stmtName can be "" to create an unnamed statement, in which
case any pre-existing unnamed statement is automatically replaced; otherwise it is an error if the
statement name is already defined in the current session. If any parameters are used, they are
referred to in the query as $1 , $2 , etc. nParams is the number of parameters for which types
are pre-specified in the array paramTypes[] . (The array pointer can be NULL when nParams is
zero.) paramTypes[] specifies, by OID, the data types to be assigned to the parameter symbols.
If paramTypes is NULL , or any particular element in the array is zero, the server assigns a data
type to the parameter symbol in the same way it would do for an untyped literal string. Also, the
query can use parameter symbols with numbers higher than nParams ; data types will be inferred
for these symbols as well. (See PQdescribePrepared for a means to find out what data types
were inferred.)
The result is not documented at all. So ...
Thanks David for your time. :-)
17.10.2017, 18:08, "David G. Johnston" <david.g.johnston@gmail.com>:
Ho to check the PQprepare result ?NULL or noNULL ?That seems like it should be documented...I'm not a C programmer and don't have time right now to go look at the docs for you.David J.
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
A quotation from the docs "for me" :PQprepareSubmits a request to create a prepared statement with the given parameters, and waits for com-pletion.PGresult* PQprepare(PGconn * conn,const char* stmtName,const char* query,int nParams,const Oid* paramTypes);
The function returns a PGresult so I'd assume you'd handle PQprepare just like any other result-returning function.
Specifically, "ExecStatusType PQresultStatus(const PGresult *res);" seems useful.
David J.