How do I access an OS environment variable from SQL?

[VolkerBarth]

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.

[jeff_albion · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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

[Breck_Carter · Accepted solution]

@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 🙂

[jeff_albion · Accepted solution]

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? 🙂

[former_SQLA_member1694884]

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.

[VolkerBarth]

other, better, smarter, easier 🙂

[VolkerBarth]

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

• Using xp_cmdshell and xp_read_file
• Using an external function that wraps the GetEnvironmentVariable() Windows API

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