Question Re: How do I access an OS environment variable from SQL? in Technology Q&A

How do I access an OS environment variable from SQL?

VolkerBarth — Fri, 17 Aug 2012 04:50:10 GMT

Say, I want to access the value of the Windows %TEMP% setting in order to write output to a particular directory...

Just as "cross documentation" - as it has been dealt with as a side effect of this question dealing witb DBISQL.

Re: How do I access an OS environment variable from SQL?

VolkerBarth — Fri, 17 Aug 2012 04:52:56 GMT

AFAIK, there doesn't seem to be a builtin function, but here are some suggestions for Windows:

If you know other, better, smarter, easier solutions - please add them...

Re: How do I access an OS environment variable from SQL?

former_SQLA_member1694884 — Fri, 17 Aug 2012 11:38:35 GMT

If you have the Perl external environment setup, this function will return value of the environment variable passed in:

CREATE FUNCTION getenv( env LONG VARCHAR )
RETURNS LONG VARCHAR
EXTERNAL NAME '$sa_perl_return = $ENV{$sa_perl_arg0}'
LANGUAGE PERL

You can do something similar with any of the other external environments, but Perl is most convenient because you can embed the code right in the external name, without having to deal with distributing dlls or jars.

Re: How do I access an OS environment variable from SQL?

VolkerBarth — Fri, 17 Aug 2012 11:52:54 GMT

other, better, smarter, easier 🙂

Re: How do I access an OS environment variable from SQL?

jeff_albion — Fri, 17 Aug 2012 12:18:31 GMT

I have now formally added this as a product enhancement request as CR #717193 (e.g. something like xp_putenv() and xp_getenv()) to be potentially targeted for a future version of SQL Anywhere.

Thank you for the enhancement suggestion.

Re: How do I access an OS environment variable from SQL?

VolkerBarth — Fri, 17 Aug 2012 12:38:31 GMT

Thanks for considering a "quest for solutions" as an "enhancement suggestion" - you sure listen to your customers:)

Re: How do I access an OS environment variable from SQL?

Breck_Carter — Fri, 17 Aug 2012 13:56:43 GMT

@Jeff: There are a sackload of simple Windows API entry points that deserve the xp_treatment, or something like it. If only it wasn't so screamingly difficult to use the SQL-Anywhere-to-C interface... no, don't go on about the LANGUAGE WHATEVER interfaces, it's nice that the WHATEVER fans have their RFP checkpoints, but while C might be loathsome it's still the best way to get down and dirty with the OS 🙂

Re: How do I access an OS environment variable from SQL?

jeff_albion — Fri, 17 Aug 2012 17:16:13 GMT

Hi Breck,

Are there any Windows API entry points that have equivalents across other POSIX OSes that you would be interested in us implementing?

There are indeed a lot of "Windows specific" functions (I have encountered Registry editing/searching requests from customers before) that could potentially go into xp_* functions, but the xp_* functions are really intended to be available cross-platform: http://www.sybase.com/detail?id=1091125#SA

Which 'sackload' of API points were you specifically thinking of? 🙂