%TEMP% in SQL File?

[Former Member]

Hi,

I was wondering if there is a way to use "OS" environment variable in an sql file.

Im trying to to something like this ->

START LOGGING '%temp%\\sql.log';

UPDATE...;
CREATE... ;
ALTER... ;

STOP LOGGING;

INPUT INTO DBA.DB_UPDATE_LOG (TEXT) FROM '%temp%\\sql.log';

COMMIT; 

Works fine, if i use a path without the %temp% variable. I want to avoid "access denied" errors while writing the log file.

Regards Stefan

[Former Member]

nice solutions, but i can't use them, because i need the variable in "start logging" and in the "input" statement, which are both interactive sql "only" statements .

I already tried the following

CREATE VARIABLE filename varchar(255);

set filename = 'c:\\temp\\log.log';

start logging filename;

but that creates a log file named "filename"

[VolkerBarth]

AFAIK, there's no builtinfunction to get the value of an environment variable in SQL Anywhere. And the feature you're asking for deals with a particular ISQL command, which would be executed outside the database server - so this might be a reasonable product enhancement suggestion.

---

For a general access to environment variables in SQL code, here is a code snippet to do this via an OS SET command re-directed to a file, which is then read via xp_read_file into a local variable. Note that in order to write to a file, you will also have access to a particular directory.

CAVEAT: Error handling has been omitted...

begin
   declare strTempDir varchar(255);
   call xp_cmdshell('SET TEMP > C:\\\\TempPath.txt', 'no_output');
   set strTempDir = xp_read_file('C:\\\\TempPath.txt');
   set strTempDir = trim(substr(strTempDir, locate(strTempDir, '=')+1));
   select strTempDir;
end;

[VolkerBarth]

AFAIK, there's no builtinfunction to get the value of an environment variable in SQL Anywhere.

That has changed with v16, see my other answer:)