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

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;

*/