SQL Anywhere 16 Sneak Peek: xp_getenv()

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