Skip to Content
Author's profile photo Breck Carter

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' );


Oops, xp_getenv() returns a LONG BINARY string, so CAST is your friend when you’re using ISQL:



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;

C:\Program Files\Common Files\Microsoft Shared\Windows Live
C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live
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():


  -f "%SQLANY16%\bin64\dbsrv16.exe"^

SELECT CAST ( xp_getenv ( 'VCD' ) AS VARCHAR );


Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.