IDS Forum

Re: Environment variable in a stored procedure

Posted By: John Miller iii
Date: Friday, 18 February 2011, at 2:45 p.m.

In Response To: Re: Environment variable in a stored procedure (Franck THOMAS)

Here is the code, and a script to compile the code and register the
function
in a database called "d1". Hope it helps.

#-------------------------------------c_udr.c--------------------------=
---------------------

#include "dmi/milib.h"
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include <string.h>

mi_lvarchar *
jfm_getenv(mi_lvarchar *str, MI_FPARAM *fparam)
{

char *tmp;

char *value;

if ( mi_switch_mem_duration(PER_ROUTINE) =3D=3D MI_ERROR ||

( tmp =3D mi_lvarchar_to_string(str)) =3D=3D NULL )

return NULL;

if ( (value =3D getenv(tmp)) =3D=3D NULL)

{

/* Return SQL NULL if the env does not exist */

mi_fp_setreturnisnull(fparam, 0 , MI_TRUE);

return NULL;

}

return mi_string_to_lvarchar(value);
}

#---------------------COMPILE SCRIPT FOR LINUX
----------------------------------------------
#!/bin/sh -x

gcc -I$INFORMIXDIR/incl -fPIC -g -c -Wall c_udr.c
gcc -shared -Wl,-soname,c_udr_lib.so \

-o c_udr_lib.so c_udr.o -lc

cp c_udr_lib.so $INFORMIXDIR/lib/c_udr_lib.so

ls -l $INFORMIXDIR/lib/c_udr_lib.so

dbaccess - -<<END

create database d1 with log;

create function mygetenv( input lvarchar(32) )
returning lvarchar(256)
external name "$INFORMIXDIR/lib/c_udr_lib.so(jfm_getenv)" language c;

execute function mygetenv("HOME");
execute function mygetenv("INFORMIXDIR");
execute function mygetenv("NONE");

END

John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 02/18/2011 11:08:08 AM:

> [image removed]
>
> Re: Environment variable in a stored procedure [22833]
>
> Franck THOMAS
>
> to:
>
> ids
>
> 02/18/2011 11:09 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> [image removed]
>
> From:
>
> "Franck THOMAS" <franck.thomas@consult-ix.fr>
>
> To:
>
> ids@iiug.org
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids@iiug.org
>
> Thank you for the answers.
> But, can we use a C function to get the $HOME variable in the Informi=
x
> database who will call as a stored procedure ?
> Someone told me that is possible, but I don't know how to do ?
>
> Regards
>
> Le 18/02/2011 13:27, Art Kagel a =E9crit :
> > You cannot just use environment variables within stored procedures =
like

> > that. You can get some of the environment variables that were in th=
e
server
> > environment, but not all, from the sysmaster:sysenv table, and you =
can
get
> > some of the environment variables from your own session from the
> > sysmaster:sysenvses table:
> >
> > select * from sysenvses where envses_sid =3D dbinfo('sessionid');
> >
> > However, at least on my system, $HOME is not one of them. I would
suggest
> > that since the server's $INFORMIXDIR is included in sysenv you plac=
e a
> > directory in INFORMIXDIR that will always be the same everywhere an=
d
will
> > always point to a directory containing symbolic links to each
> user's home or
> > bin directory named after the user's username. Call it appbin, then=
in
your
> > procedure you can do:
> >
> > select env_value into appbin from sysmaster:sysenv where env_name =3D=

> > 'INFORMIXDIR';
> > let command =3D rtrim( appbin ) || '/appbin/' || rtrim(USER) ||
'/myprog.sh'
> > system( command );
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor an=
y
other
> > organization with which I am associated either explicitly,
> implicitly, or by
> > inference. Neither do those opinions reflect those of other individ=
uals

> > affiliated with any entity with which I am affiliated nor those of =
the
> > entities themselves.
> >
> > On Fri, Feb 18, 2011 at 7:00 AM, Franck THOMAS
> > <franck.thomas@consult-ix.fr>wrote:
> >
> >> Hi all,
> >>
> >> I'd like to know how to use the environment variables in a stored
> >> procedure, for example $HOME, when I call the SYSTEM command ?
> >>
> >> By example :
> >>
> >> CREATE PROCEDURE proc
> >>
> >> SYSTEM('$HOME/myprog.sh');
> >>
> >> END PROCEDURE;
> >>
> >> Currently, we must write :
> >> CREATE PROCEDURE proc
> >>
> >> SYSTEM('/home/user/myprog.sh');
> >>
> >> END PROCEDURE;
> >>
> >> When we do a dbexport and a dbimport of the database in another
server,
> >> we must change the stored procedure to modify the directory.
> >>
> >> Regards,
> >>
> >> --
> >> Franck Thomas
> >> ConsultiX
> >> franck.thomas@consult-ix.fr
> >> T=E9l=E9phone : 33 (0) 1 39 12 18 00
> >> Mobile : 33 (0) 6 78 81 09 33
> >> Fax : 33 (0) 1 39 12 18 18
> >>
> >>
> >>
> >>
> >
>
***********************************************************************=
********

> >> Forum Note: Use "Reply" to post a response in the discussion forum=
.
> >>
> >>
> > --0015175ce1821ea91a049c8da25e
> >
> >
> >
>
***********************************************************************=
********

> > Forum Note: Use "Reply" to post a response in the discussion forum.=

> >
> >
>
> --
> Franck Thomas
> ConsultiX
> franck.thomas@consult-ix.fr
> T=E9l=E9phone : 33 (0) 1 39 12 18 00
> Mobile : 33 (0) 6 78 81 09 33
> Fax : 33 (0) 1 39 12 18 18
>
>
>
***********************************************************************=
********

> Forum Note: Use "Reply" to post a response in the discussion forum.=

>=

Messages In This Thread

• Environment variable in a stored procedure
  Franck THOMAS -- Friday, 18 February 2011, at 7:00 a.m.
  • Re: Environment variable in a stored procedure
    Fernando Nunes -- Friday, 18 February 2011, at 7:21 a.m.
  • Re: Environment variable in a stored procedure
    Art Kagel -- Friday, 18 February 2011, at 7:27 a.m.
    • Re: Environment variable in a stored procedure
      Franck THOMAS -- Friday, 18 February 2011, at 2:08 p.m.
      • Re: Environment variable in a stored procedure
        John Miller iii -- Friday, 18 February 2011, at 2:45 p.m.
      • Re: Environment variable in a stored procedure
        Mike Aubury -- Friday, 18 February 2011, at 3:01 p.m.
        • Re: Environment variable in a stored procedure
          David Zinder -- Friday, 18 February 2011, at 3:26 p.m.
          • Re: Environment variable in a stored procedure
            Fernando Nunes -- Friday, 18 February 2011, at 7:50 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.