Re: SPL: table-name as parameter?
WF Software (paulw@wfsoftware.co.uk) wrote:
: In article <36418BD9.A5D7A7CC@y.z>, x@y.z (Michael Firschke) wrote:
:
: > hello informix-world,
: >
: > how do I convert this into SPL?
: >
: > dbaccess mydb << ...
: > select * from $1
: > ...
: >
: > TIA and sorry for my e-mail-adress, I prefer news-postings only.
: > Michael
: >
: You can't unless you get the SPL to drop to a system command.
:
Or you could use UDO. The following little 'C' UDR takes a
string argument and executes it. It does some exception handling,
but not a whole hell of a lot. In general this is most useful
when you use a non-SELECT statement; CREATE , UPDATE etc. If
you pass it a SELECT then this function simply executes it, and
returns as a string the first column of the first row. You
could -- of course -- use this code to do something a bit more
elaborate.
-- FILE: getstring.h --
/*
* File: GetString.h
*
* About:
*
* This file contains the headers for the GetString()
* function implemented in the GetString.c. Use this
* file to ensure appropriate declarations.
*/
#include <stdio.h>
#include <mi.h>
mi_char * GetStringc ( mi_char *, MI_CONNECTION *, MI_FPARAM * );
mi_lvarchar * GetStringlv ( mi_lvarchar *, MI_FPARAM * );
-- FILE: getstring.c --
/*
* This is the function which takes an arbitrary query known to
* return a single string, and processes it, returning that
* string.
*/
#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);
}
-- END --
The Solaris Makefile looks like this.
-- FILE: Makefile --
# This Makefile builds the getstring UDR DataBlade.
# This is the project title.
PROJECT_TITLE= getstring
# TARGET must be set to the location/filename
# of the platform-specific make include file.
# include $(INFORMIXDIR)/incl/dbkd/$(TARGET).mak
#
MI_INCLUDE=$(INFORMIXDIR)/incl
RM= rm
RMFLAGS= -f
# CC= /usr/ucb/cc
# CC= cc -g
CC= gcc -g
CSRVRFLAGS= -DMI_SERVBUILD # not for gcc, but OK for other -KPIC
CFLAGS= $(CSRVRFLAGS) -I$(MI_INCLUDE)/public -I$(MI_INCLUDE)/esql -I$(MI_INCLUDE
) $(COPTS)
LINK= /usr/ccs/bin/ld
LINKFLAGS= -dy -G -Bsymbolic
LIBS= -ldl -lnsl -lsocket -lm -lrpcsvc -lc
DATABLADE_LIBS=
SHLIBSUFF= bld
BINDIR= ../bin
# Platform independent code goes here.
# The following code was generated by BladeSmith.
PROJECT_OBJS= $(BINDIR)/$(PROJECT_TITLE).o
all: server client
# Construct the object file.
$(BINDIR)/$(PROJECT_TITLE).o : $(PROJECT_TITLE).c
$(CC) $(CFLAGS) -o $(BINDIR)/$(PROJECT_TITLE).o -c $(PROJECT_TITLE).c
# Construct the shared library.
$(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF): $(BINDIR)/$(PROJECT_TITLE).o
$(LINK) $(LINKFLAGS) -o $(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF) \\
$(PROJECT_OBJS) $(LIBS) $(DATABLADE_LIBS)
server: $(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF)
client:
echo "Client Makefile entry is empty - nothing to do."
-- END --
This is the create script, and a little test that shows you
how it works.
-- File: register.sql --
--
-- File: ExecIt/install/register.sql
--
-- About:
--
-- This script contains the definitions of the ExecIt() function.
--
-- DROP FUNCTION ExecIt ( lvarchar );
--
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 ("DROP TABLE Foo;");
EXECUTE FUNCTION ExecIt ("CREATE TABLE Foo ( A INTEGER