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