Re: SPI_prepare, SPI_execute_plan do not return rows when using parameters

Postgresql v8.0.3 on FreeBSD 5.4.

I'm developing my first major 'C' language stored
procedure. Inside this procedure I need to query a
lookup table stored in postgres. I found several
examples of using SPI_* functions via google searches.
I read the documents on the postgres web site several
times. I've searched the mailing list archive for
similar problems (no luck).

My problem is that a query that should be returning a
row is returning zero rows when I use a parametrized
query. If I use "sprintf()" to create the query
string, it returns the results that I expect.
However, I really want to use a parametrized query.

Below is my table definition, the sample query working
from 'psql', excerpts from 'C' code and excerpts from
the log files.

Can anyone spot my errors? As far as I can tell, I'm
calling all of the correct functions, in the correct
order. I'm testing every return result that is
documented as returning any failure condition.

Thank you very much for your time.

djenkins(at)devl-app-1$ psql -Upgsql devl -c "select
version();"
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled
by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728
(1 row)

CREATE TABLE micr_parser_exception
(
transit char(9) NOT NULL,
doc_type char(1) NOT NULL,
del_spaces char(1) NOT NULL,
field int2 NOT NULL,
skip int2 NOT NULL,
size int2 NOT NULL,
account_pos char(1) NOT NULL,
user_id int4 NOT NULL,
added_ts timestamp NOT NULL DEFAULT now(),
"comment" varchar(200),
CONSTRAINT micr_parser_exception_transit_pkey
PRIMARY KEY (transit)
)
WITHOUT OIDS;

djenkins(at)devl-app-1$ psql -Upgsql devl -c "SELECT
transit, doc_type, del_spaces, field, skip, size,
account_pos FROM micr_parser_exception WHERE transit =
'123456789';"
transit | doc_type | del_spaces | field | skip |
size | account_pos
-----------+----------+------------+-------+------+------+-------------
123456789 | P | Y | 1 | 3 |
4 | R
(1 row)

djenkins(at)devl-app-1$ psql -Upgsql devl -c "select
parse_micr(':123456789: 123456789090<');"
NOTICE: Plan: 0x843d418/0x843d818 wants 1 args.
NOTICE: No rows found for transit "123456789" (0).
parse_micr
-----------------------------------------------------------------------------
(0,0,0,2,"",0,123456789090,2,"",2,"",1,123456789,2,"
",0,123456789090,2,"")
(1 row)

static const char *g_szExceptionQuery =
"SELECT transit, doc_type, del_spaces, field, skip,
size, account_pos FROM micr_parser_exception WHERE
transit = $1";
//"SELECT doc_type, del_spaces, field, skip, size,
account_pos FROM micr_parser_exception WHERE
transit='123456789'";

static const int QUERY_FIELDS = 7;

static int SearchForExceptionRecord
(
struct MICR_EXCEPTION *ex, // OUT
const char *transit // IN
)
{
int result = 0;
int rows = 0;
TupleDesc tupdesc = NULL;
SPITupleTable *tuptable = NULL;
HeapTuple tuple = NULL;
char *data_transit = NULL;
char *data_doc_type = NULL;
char *data_del_spaces = NULL;
char *data_field = NULL;
char *data_skip = NULL;
char *data_size = NULL;
char *data_account_pos = NULL;
static void *saved_plan = NULL;
Datum bind[1];

assert(ex);
assert(transit);

memset(ex, 0, sizeof(struct MICR_EXCEPTION));

if (SPI_OK_CONNECT != (result = SPI_connect()))
{
elog(ERROR, "SPI_connect() failed with code
'%d'.\n", result);
return 0;
}

if (NULL == saved_plan)
{
void *plan = NULL;
Oid argtypes[1];

argtypes[0] = CHAROID;
if (NULL == (plan = SPI_prepare(g_szExceptionQuery,
1, argtypes)))
{
elog(ERROR, "SPI_prepare('%s') failed.\n",
g_szExceptionQuery);
goto done;
}

if (NULL == (saved_plan = SPI_saveplan(plan)))
{
elog(ERROR, "SPI_saveplan('%s') failed.\n",
g_szExceptionQuery);
goto done;
}

elog(NOTICE, "Plan: %p/%p wants %d args.", plan,
saved_plan, SPI_getargcount(saved_plan));
}

bind[0] = CStringGetDatum(transit);
if (SPI_OK_SELECT != (result =
SPI_execute_plan(saved_plan, bind, NULL, 1, 1)))
{
elog(ERROR, "Could not execute statement
\"%s\": %s",
g_szExceptionQuery,
SPI_result_code_string(SPI_result));
goto done;
}

if ((rows = SPI_processed) < 1)
{
elog(NOTICE, "No rows found for transit \"%s\"
(%d).", transit, rows);
goto done;
}

// The rest of the code has been omitted as the
problem has already occurred. "SPI_processed" is
zero. If I remove the parametrization, I get back the
row that I was looking for.

Portions of my "Makefile" (using FreeBSD 'make', not
GNU's 'gmake'):

INCDIR != pg_config --includedir-server
CFLAGS = -fpic -ggdb -Wall -O2 -I$(INCDIR)
-I/usr/local/include

parse_micr.so: parse_micr.o
gcc -shared -o $@ parse_micr.o

Dennis Jenkins

Dennis Jenkins <dennis(dot)jenkins(at)sbcglobal(dot)net> writes:
> My problem is that a query that should be returning a
> row is returning zero rows when I use a parametrized
> query.

You're passing the wrong parameter value, and probably not declaring it
to be the right type either. CHAROID is not the type you think it is
(BPCHAROID is what you want), and "CStringGetDatum" is not the way to
convert a C string into a char(N) datum. The most bulletproof way
to do the latter is to use DirectFunctionCall3 to invoke bpcharin().

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dennis Jenkins <dennis(dot)jenkins(at)sbcglobal(dot)net>
> writes:
> > My problem is that a query that should be
> returning a
> > row is returning zero rows when I use a
> parametrized
> > query.
>
> You're passing the wrong parameter value, and
> probably not declaring it
> to be the right type either. CHAROID is not the
> type you think it is
> (BPCHAROID is what you want), and "CStringGetDatum"
> is not the way to
> convert a C string into a char(N) datum. The most
> bulletproof way
> to do the latter is to use DirectFunctionCall3 to
> invoke bpcharin().
>

Thank you. I was able to make the code work. I
changed "CHAROID" to "BPCHAROID" and the bind[0] line
now reads:

bind[0] = DirectFunctionCall3(bpcharin,
(Datum)transit, 0 /*unused*/ , 9 + sizeof(VARHDRSZ));

However, I must admit that I could find no usable
documentation for either "DirectFunctionCall3" and
"bpcharin" online. I had to extract the source code
to the engine and read
"src/backend/utils/adt/varchar.c" to learn what
arguments to pass to DFC3.

If I create a sample stored procedure that uses the
above functions and submit it to "the documentation
people", would they want it? Would they include it in
the docs? Who are these magic people and where do I
find them?

Thank you very much for your time. Our code is now
working, so I'm a happy camper.

Dennis Jenkins