%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"

[Breck_Carter · Accepted solution]

Gosh, it would help a lot if I read the question before answering, wouldn't it?

You may find the ISQL READ and PARAMETERS statements useful.

If you code your script like this, and put it in a file called (for example) logging.sql...

PARAMETERS path;

START LOGGING '{path}\\sql.log';

CREATE TABLE t ( c INTEGER );

STOP LOGGING;

INPUT INTO DBA.DB_UPDATE_LOG (TEXT) FROM '{path}\\sql.log';

COMMIT; 

and then execute it from a command line that looks like this...

"%SQLANY12%\\bin32\\dbisql.com" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" READ ENCODING Cp1252 "logging.sql" [%TEMP%]

PAUSE

the command window will look something like this...

C:\\projects\\$SA_templates\\run\\dbisql>"C:\\Program Files\\SQL Anywhere 12\\bin32\\dbi
sql.com" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" READ ENCODING Cp1252 "logging.
sql" [C:\\Users\\Breck\\AppData\\Local\\Temp]
Importing into table "DBA"."DB_UPDATE_LOG"
1 rows read into table "DBA"."DB_UPDATE_LOG"
Execution time: 0.093 seconds

C:\\projects\\$SA_templates\\run\\dbisql>PAUSE
Press any key to continue . . .

and the table will look like this...

SELECT * FROM DB_UPDATE_LOG;

text
'CREATE TABLE t ( c INTEGER );'

[Former Member · Accepted solution]

Thanks this helps 🙂

[VolkerBarth · Accepted solution]

Yes, ISQL parameters - they came to my mind, too:)

@StefanZ: Feel free to accept that as the "correct" answer - for a HOWTO, see the FAQ or this question...