I need your help!! IUS question concerning UDRs

Some info first: I'm using -- Informix IUS 9.14.UC7X1 SunOS 5.6 I'm trying to develop a User-defined routine (UDR). Now, I was able to create a simplistic one using the examples in the book. I created a simple program #include <stdio.h> int add(int a, int b) { return(a+b) ; } and compiled it into an object cc -g -c and then into a shared object cc -G and was able to create a function in Informix that referenced this shared object. Everything worked just fine. At first, I had int main() { } in there, but later removed it, and it worked ok. A bit of background ... I was tasked with writing a stored procedure to do a search. This search has n number of paremeters, where of those parameters, some are null, and it varies. A few of the parameters are always present, but some will not be. Like, searching on a customer - you know the phone number, but not the name, or you might have an ssn, or you might have a street address and a last name. You get the idea. It all boils down to the fact that I needed to write a dynamic SQL which a standard procedure couldn't do. An ESQL/C program can, however. So my idea was to write the ESQL/C function to do this, taking in all the necessary parameters, and then creating it as a .so and linking it into an Informix function. Well, at this point, I have a simplistic .ec program that doesn't even make any calls to Informix. It writes a log to a file. The program has multiple functions, and includes the sqlca.h file. I have tried a number of ways to compile it into a .so file, one of which is : esql search.ec -e -I/informix/incl -I/informix/incl/esql cc search.c -c -I/informix/incl -I/informix/incl/esql cc search.o -G -o search.so When I create the function to reference my function (search) I get a 9791 error. I've tried other methods of compiling, including using esql instead of cc on the second step. I get different sized .so files for each method. Now, I have several questions - 1. What is the correct process for compiling an esql .ec file into a .so? 2. Is what I'm doing even possible? I have my doubts. I'm going to write a function that calls a program that's going to connect to the same database that it's already running under.. it sounds like it might not even work. I mean, this program will open the database, do a query against the database, and then close - all while it is being called from inside a function. I have my doubts. If what I'm trying to do won't even work, then I'm open to suggestions on how to do what I need to do. Thank you for your time, Curtis Bennett Overland Park, KS Sent via Deja.com http://www.deja.com/ Before you buy.

OK. 1. Check out http://www.informix.com/idn and the datablades area within it. This is a developer's corner that describes a lot of topics related to working in the engine. 2. Check out the DataBlade API manual, specifically (9.14 docs?) pages 1-18 through 1-30. You can't use ESQL/C inside the engine. You need to use the engine's own "Server API" or SAPI. I have appended a simple 'C' UDR that illustrates what this looks like. This is the func code. /* * This is the function which takes an arbitrary query known to * return a single string, and processes it, returning that * string. All you get back is a single LVARCHAR row/col * but it's a fairly straightforward exercise either to construct a * collection result, or to turn this into an interator UDR. * * Compile and link as you would any other UDR. Then declare * it and use it as follows: * CREATE FUNCTION ExecIt ( lvarchar ) RETURNING lvarcharEXTERNAL NAME "$INFORMIXDIR/extend/ExecIt/bin/getstring.bld(GetStringlv)" LANGUAGE C; -- GRANT EXECUTE ON FUNCTION ExecIt(lvarchar) TO PUBLIC;-- EXECUTE FUNCTION ExecIt ("CREATE TABLE Foo ( A INTEGER );"); EXECUTE FUNCTION ExecIt ("INSERT INTO Foo VALUES ( 1 ) ;"); EXECUTE FUNCTION ExecIt ("INSERT INTO Foo VALUES ( 2 ) ;"); EXECUTE FUNCTION ExecIt ("SELECT COUNT(*) FROM Foo;"); EXECUTE FUNCTION ExecIt ("SELECT * FROM Foo;"); EXECUTE FUNCTION ExecIt ("DROP TABLE Foo;"); */ #include "getstring.h" mi_char * GetStringc ( mi_char * pszQuery, MI_CONNECTION * conn, MI_FPARAM * pFparam ) { MI_ROW *pszRowReturned; MI_ROW_DESC *pszRowDesc; mi_char * pszRetVal; mi_char szBuf[128]; mi_integer iReturnValue, iMiError, iNumCols, iColLen; /* Send the szQuery */ if (mi_exec(conn, pszQuery, 0) == MI_ERROR) { (void ) sprintf(szBuf,"GetString: Failed on %s\\n",pszQuery); mi_db_error_raise(conn, MI_EXCEPTION, szBuf); /* never reached */ } /* Get the results */ while ((iReturnValue = mi_get_result(conn)) != MI_NO_MORE_RESULTS) { switch(iReturnValue) { default: case MI_ERROR: { (void) sprintf(szBuf,"GetString: Failed to return result"); mi_db_error_raise(conn, MI_EXCEPTION, szBuf); } break; case MI_DML: { iNumCols = mi_result_row_count( conn ); pszRetVal = mi_alloc(32); sprintf(pszRetVal, "%d Rows Affected", iNumCols); return pszRetVal; } break; case MI_ROWS: while ((pszRowReturned = mi_next_row(conn,&iMiError)) != NULL) { pszRowDesc = mi_get_row_desc(pszRowReturned); iNumCols = mi_column_count(pszRowDesc); if (iNumCols != 1) { (void) sprintf(szBuf,"GetString: Wrong return result"); mi_db_error_raise(conn, MI_EXCEPTION, szBuf); } switch(mi_value(pszRowReturned, 0, (MI_DATUM *)&pszRetVal, &iColLen)) { case MI_ERROR: case MI_NULL_VALUE: (void) sprintf(szBuf,"GetString: Bad return value"); mi_db_error_raise(conn, MI_EXCEPTION, szBuf); break; default: return pszRetVal; /* not reached */ break; } } break; case MI_DDL: { return "OK"; /* not reached */ } } } (void) sprintf(szBuf,"GetString: No result"); mi_db_error_raise(conn, MI_EXCEPTION, szBuf); /* not reached */ } /******************************************************************** ** ** Function: GetStringlv ** ** About: ** ** This is the wrapper around the GetStringc function. **/ mi_lvarchar * GetStringlv ( mi_lvarchar * plvQuery, MI_FPARAM * pFparam ) { MI_CONNECTION * conn; mi_char * pchRetVal; conn = mi_open(NULL, NULL, NULL); pchRetVal = GetStringc( mi_lvarchar_to_string (plvQuery), conn, pFparam ); return mi_string_to_lvarchar(pchRetVal); } Hope this helps! KR Pb

> I was tasked with writing a stored procedure to do > a search. This search has n number of paremeters, > where of those parameters, some are null, and it > varies. A few of the parameters are always > present, but some will not be. Like, searching on > a customer - you know the phone number, but not > the name, or you might have an ssn, or you might > have a street address and a last name. You get the > idea. It all boils down to the fact that I needed > to write a dynamic SQL which a standard procedure > couldn't do. Not necessarily dynamic SQL. You could do: Select .... From table1 Where (table1.Name = ArgName or ArgName is null) and (table1.PostCode = ArgPostCode or ArgPostCode is null) and (table1.TelNumber = ArgTelNumber or ArgTelNumber is null) and ... you get the idea. -- Bashar Chalabi CTL, London