Errors with parameter datatype in PostgreSql query
- by John
Im trying to execute a query to postgresql using the following code. It's written in C/C++ and I keep getting the following error when declaring a cursor:
DECLARE CURSOR failed: ERROR: could not determine data type of parameter $1
Searching on here and on google, I can't find a solution. Can anyone find where I have made and error and why this is happening? thanks!
void searchdb( PGconn *conn, char* name, char* offset )
{
// Will hold the number of field in table
int nFields;
// Start a transaction block
PGresult *res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("BEGIN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
// Clear result
PQclear(res);
printf("BEGIN command - OK\n");
//set the values to use
const char *values[3] = {(char*)name, (char*)RESULTS_LIMIT, (char*)offset};
//calculate the lengths of each of the values
int lengths[3] = {strlen((char*)name), sizeof(RESULTS_LIMIT), sizeof(offset)};
//state which parameters are binary
int binary[3] = {0, 0, 1};
res = PQexecParams(conn, "DECLARE emprec CURSOR for SELECT name, id, 'Events' as source FROM events_basic WHERE name LIKE '$1::varchar%' UNION ALL "
" SELECT name, fsq_id, 'Venues' as source FROM venues_cache WHERE name LIKE '$1::varchar%' UNION ALL "
" SELECT name, geo_id, 'Cities' as source FROM static_cities WHERE name LIKE '$1::varchar%' OR FIND_IN_SET('$1::varchar%', alternate_names) != 0 LIMIT $2::int4 OFFSET $3::int4",
3, //number of parameters
NULL, //ignore the Oid field
values, //values to substitute $1 and $2
lengths, //the lengths, in bytes, of each of the parameter values
binary, //whether the values are binary or not
0); //we want the result in text format
// Fetch rows from table
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
// Clear result
PQclear(res);
res = PQexec(conn, "FETCH ALL in emprec");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
printf("FETCH ALL failed");
PQclear(res);
exit_nicely(conn);
}
// Get the field name
nFields = PQnfields(res);
// Prepare the header with table field name
printf("\nFetch record:");
printf("\n********************************************************************\n");
for (int i = 0; i < nFields; i++)
printf("%-30s", PQfname(res, i));
printf("\n********************************************************************\n");
// Next, print out the record for each row
for (int i = 0; i < PQntuples(res); i++)
{
for (int j = 0; j < nFields; j++)
printf("%-30s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
// Close the emprec
res = PQexec(conn, "CLOSE emprec");
PQclear(res);
// End the transaction
res = PQexec(conn, "END");
// Clear result
PQclear(res);
}
