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@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@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@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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
