Now you can get the value of server-side environment variables like PATH and LOCALAPPDATA and TEMP inside your SQL scripts, even inside stored procedures, triggers and web services.
When you call xp_getenv ( ‘variable’ ) from a SQL Anywhere 16 database running on Windows 7, it works for
- all the environment variables that appear when you run the SET command at the command prompt on the database server,
- which is a superset of the variables in the Control Panel – All Control Panel Items – System – Advanced System Settings – Environment Variables window,
- which also includes custom SET WHATEVER values you have defined before starting the database engine,
- but xp_getenv() doesn’t work for the dynamic environment variables like CD and ERRORLEVEL; e.g., %CD% returns the current directory inside a batch file but xp_getenv ( ‘CD’ ) returns NULL,
- nor does xp_getenv() work for custom SET variables that fall out of scope before xp_getenv() is called; e.g., the following SELECT returns NULL:
CALL xp_cmdshell ( 'SET WHATEVER=123' ); SELECT xp_getenv ( 'WHATEVER' );
In other words, environment variables are just a teeny bit mysterious, so check your assumptions at the door and test your xp_getenv calls.
But… but… xp_getenv() is still cool…
Here’s how it works:
SELECT xp_getenv ( 'APPDATA' );
xp_getenv('APPDATA')
--------------------------------------------------------------
0x433a5c55736572735c427265636b5c417070446174615c526f616d696e67
Oops, xp_getenv() returns a LONG BINARY string, so CAST is your friend when you’re using ISQL:
SELECT CAST ( xp_getenv ( 'APPDATA' ) AS VARCHAR );
xp_getenv('APPDATA')
------------------------------
C:\Users\Breck\AppData\Roaming
If you need to search the PATH list, you can combine sa_split_list() with xp_getenv() to break it down into its component parts like this:
SELECT row_value AS "Path" FROM sa_split_list ( CAST ( xp_getenv ( 'PATH' ) AS VARCHAR ), ';' ) ORDER BY line_num; Path ----------------------------------------------------------------- C:\Program Files\Common Files\Microsoft Shared\Windows Live C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live C:\Windows\system32 C:\Windows ... C:\Program Files (x86)\Sybase\Shared\win32 C:\Program Files (x86)\Sybase\Shared\Sybase Central 4.3\win32 C:\Program Files\SQL Anywhere 16\bin64 C:\Program Files\SQL Anywhere 16\bin32
Here’s an example of a server startup script that uses a custom SET VCD=%CD% command to create a copy of the CD dynamic environment variable that (unlike CD) is available to xp_getenv():
SET VCD=%CD%
"%SQLANY16%\bin64\dbspawn.exe"^
-f "%SQLANY16%\bin64\dbsrv16.exe"^
ddd16.db
SELECT CAST ( xp_getenv ( 'VCD' ) AS VARCHAR );
xp_getenv('VCD')
----------------
C:\data\xpdemo