Thread: 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@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
Dennis Jenkins <dennis.jenkins@sbcglobal.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
Re: SPI_prepare, SPI_execute_plan do not return rows when using parameters
From
Dennis Jenkins
Date:
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dennis Jenkins <dennis.jenkins@sbcglobal.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