LightDB 从23.4开始,支持使用绑定参数的方式执行oracle匿名块。绑定参数支持的参数个数为65535(libpq)。
之前版本会报如下错误:there is no parameter $1
。
下面介绍通过libpq 执行带绑定参数的匿名块。
*
* src/test/examples/lt_testlibpq.c
*
*
* lt_testlibpq.c
* this test program shows to use LIBPQ to exec dostmt with bind param
*
*/
#include
#include
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
const char *paramValues[10];
Oid paramTypes[10];
int nFields;
int i,
j;
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "create table test_dostmt(key1 int, key2 text);");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "insert into test_dostmt values(1,'a');");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
paramTypes[0] = 23;
paramValues[0] = "1";
printf("update test_dostmt key2 to 'test_dostmt'\n");
res = PQexecParams(conn, "begin\
begin\
update test_dostmt set key2 = 'test_dostmt' where key1 = $1 ;\
end;\
end;", 1, paramTypes, paramValues, NULL, NULL, 0);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
printf("get test_dostmt key2 \n");
res = PQexec(conn, "select key2 from test_dostmt where key1=1;");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
/* print out the instances */
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%s: %s", PQfname(res, j), PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
printf("update test_dostmt key2 to 'test_dostmt_new'\n");
paramTypes[0] = 23;
paramValues[0] = "1";
paramTypes[1] = 25;
paramValues[1] = "test_dostmt1";
res = PQexecParams(conn, " \
declare\
id int := $1; \
val text := $2; \
begin\
begin\
val = val || '_new'; \
update test_dostmt set key2 = val where key1 = id ;\
end;\
end;", 2, paramTypes, paramValues, NULL, NULL, 0);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
printf("get test_dostmt key2 \n");
res = PQexec(conn, "select key2 from test_dostmt where key1=1;");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
/* print out the instances */
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%s: %s", PQfname(res, j), PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
res = PQexec(conn, "drop table test_dostmt;");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
PQfinish(conn);
return 0;
}
需要注意的是,在使用PQexecParams或者PQprepare时必须传入paramTypes才能支持匿名块绑定参数,不然会报如下错误:ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0