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 |
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 );' Thanks this helps :-)
(16 Aug '12, 14:23)
StefanZ
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...
(17 Aug '12, 03:07)
Volker Barth
|
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;
That has changed with v16, see my other answer:)
(10 Nov '15, 02:26)
Volker Barth
|
FWIW, it is also possible to call the Windows API entry point GetEnvironmentVariable(). CAVEAT EMPTOR: The following snippet of C code (scroll down to see a call from SQL) shows a function from an old archive. The function is no longer used, and it was written only for 32-bit Windows... so, no guarantees :) //---------------------------------------------------------- // get_environment_variable //---------------------------------------------------------- __declspec(dllexport) _VOID_ENTRY get_environment_variable ( an_extfn_api *api, void *arg_handle ) { // For information about GetEnvironmentVariable see: // http://msdn2.microsoft.com/en-us/library/ms683188.aspx an_extfn_value api_name; an_extfn_value api_value; an_extfn_value api_return_code; an_extfn_value api_diagnostic_code; an_extfn_value api_diagnostic_string; char * name; char * value; DWORD return_code; DWORD diagnostic_code; char * diagnostic_string; if ( !api -> get_value ( arg_handle, 1, &api_name ) || api_name.data == NULL ) { return; } name = (char *) api_name.data; value = (char *) malloc( 32766 ); strcpy_s ( value, 32766, "" ); return_code = 0; diagnostic_code = 0; diagnostic_string = (char *) malloc( 255 ); strcpy_s ( diagnostic_string, 255, "" ); return_code = GetEnvironmentVariable ( (LPCTSTR) name, (LPTSTR) value, 32767 ); if ( return_code <= 0 ) { diagnostic_code = 1; strcpy_s ( diagnostic_string, 255, "GetEnvironmentVariable failed" ); } // CHECK THE ARGUMENT NUMBERS IN THE SET_VALUE CALLS... api_value.type = DT_VARCHAR; api_value.data = value; api_value.piece_len = ( a_sql_uint32 )( strlen ( value ) ); api_value.len.total_len = ( a_sql_uint32 )( strlen ( value ) ); api -> set_value ( arg_handle, 2, &api_value, 0 ); api_return_code.type = DT_INT; api_return_code.data = &return_code; api -> set_value ( arg_handle, 3, &api_return_code, FALSE ); api_diagnostic_code.type = DT_INT; api_diagnostic_code.data = &diagnostic_code; api -> set_value ( arg_handle, 4, &api_diagnostic_code, FALSE ); api_diagnostic_string.type = DT_VARCHAR; api_diagnostic_string.data = diagnostic_string; api_diagnostic_string.piece_len = ( a_sql_uint32 )( strlen ( diagnostic_string ) ); api_diagnostic_string.len.total_len = ( a_sql_uint32 )( strlen ( diagnostic_string ) ); api -> set_value ( arg_handle, 5, &api_diagnostic_string, 0 ); free ( value ); free ( diagnostic_string ); } // get_environment_variable //------------------------------------------------------------------------------------------------ /* For unit testing... BEGIN DROP PROCEDURE rroad_get_environment_variable; EXCEPTION WHEN OTHERS THEN END; CREATE PROCEDURE rroad_get_environment_variable ( IN name VARCHAR ( 255 ), OUT value VARCHAR ( 32766 ), OUT return_code INTEGER, -- OK if > 0 OUT diagnostic_code INTEGER, OUT diagnostic_string VARCHAR ( 255 ) ) EXTERNAL NAME 'get_environment_variable@C:\\projects\\foxhound\\rroad9\\Release\\rroad1.dll'; BEGIN DECLARE @name VARCHAR ( 255 ); DECLARE @value VARCHAR ( 32766 ); DECLARE @return_code INTEGER; DECLARE @diagnostic_code INTEGER; DECLARE @diagnostic_string VARCHAR ( 255 ); SET @name = 'SQLANY11'; CALL rroad_get_environment_variable ( @name, @value, @return_code, @diagnostic_code, @diagnostic_string ); SELECT @name, @value, @return_code, @diagnostic_code, @diagnostic_string; -- @name,@value,@return_code,@diagnostic_code,@diagnostic_string -- 'SQLANY11','C:\\Program Files\\SQL Anywhere 11',32,0,'' SET @name = 'UNKNOWN'; CALL rroad_get_environment_variable ( @name, @value, @return_code, @diagnostic_code, @diagnostic_string ); SELECT @name, @value, @return_code, @diagnostic_code, @diagnostic_string; -- @name,@value,@return_code,@diagnostic_code,@diagnostic_string -- 'UNKNOWN','',0,1,'GetEnvironmentVariable failed' END; */ |
Just for the record: v16 has introduced the xp_get_env() system procedure that returns the value of an environment variable. (As discussed, it would not help in the particular question as that requires a "DBISQL variable", which is already available via PARAMETERs, as Breck has pointed out.) |
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
but that creates a log file named "filename"