Monday, February 11, 2013

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


5 comments:

John Smirnios said...
This comment has been removed by the author.
John Smirnios said...

Some variables such as CD and ERRORLEVEL are not actually environment variables. They are special cases handled by the shell. For example, in a shell run the "set" command all by itself and it will show you all of the environment variables. You won't see CD or ERRORLEVEL.

Also remember that each process has its own environment that is (usually) inherited from its parent. If you use xp_cmdshell( "SET WHATEVER=123" ), we run a child to invoke that command and then that child exits. The environment of the parent process (the database server) is unaffected.

John Smirnios said...

I also think it was an unfortunate choice to have xp_getenv return BINARY. If you just cast it to CHAR, you might end up not doing a necessary charset conversion -- such as when your database CHAR charset doesn't match your OS charset.

Breck Carter said...

@John: Well, Microsoft calls CD and ERRORLEVEL "environment variables" in their docs: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds_shelloverview.mspx?mfr=true

They're different in scope from other environment variables, which is why the blog post called them "dynamic environment variables".

Anonymous said...

FWIW, if one wants to add/alter the environment variables used by the database engine itself, one could use an in-process external function (i.e. without using an external environment) and call the according OS function, say the SetEnvironmentVariable() WinAPI on Windows.

Regards
Volker