Question Re: %TEMP% in SQL File? in Technology Q&A

%TEMP% in SQL File?

Former Member — Thu, 16 Aug 2012 08:57:11 GMT

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

Re: %TEMP% in SQL File?

VolkerBarth — Thu, 16 Aug 2012 09:36:33 GMT

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;

Re: %TEMP% in SQL File?

Breck_Carter — Thu, 16 Aug 2012 09:54:46 GMT

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;

*/

Re: %TEMP% in SQL File?

Former Member — Thu, 16 Aug 2012 10:42:32 GMT

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"

Re: %TEMP% in SQL File?

Breck_Carter — Thu, 16 Aug 2012 13:35:26 GMT

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 );'

Re: %TEMP% in SQL File?

Former Member — Thu, 16 Aug 2012 14:23:07 GMT

Thanks this helps 🙂

Re: %TEMP% in SQL File?

VolkerBarth — Fri, 17 Aug 2012 03:07:43 GMT

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...

Re: %TEMP% in SQL File?

VolkerBarth — Tue, 10 Nov 2015 02:25:55 GMT

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.)

Re: %TEMP% in SQL File?

VolkerBarth — Tue, 10 Nov 2015 02:26:53 GMT

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:)