Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 16 Aug '12, 08:57

StefanZ's gravatar image

StefanZ
51115
accept rate: 0%

edited 15 Mar '13, 21:11

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

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"

(16 Aug '12, 10:42) StefanZ

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

answered 16 Aug '12, 13:35

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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;
permanent link

answered 16 Aug '12, 09:36

Volker%20Barth's gravatar image

Volker Barth
40.2k362550822
accept rate: 34%

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

(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;

*/
permanent link

answered 16 Aug '12, 09:54

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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

permanent link

answered 10 Nov '15, 02:25

Volker%20Barth's gravatar image

Volker Barth
40.2k362550822
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×105
×23
×20
×14

question asked: 16 Aug '12, 08:57

question was seen: 4,385 times

last updated: 10 Nov '15, 02:26