The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
22.3k9129262

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
26.7k418577825
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
29.3k287438645
accept rate: 32%

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
26.7k418577825
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
29.3k287438645
accept rate: 32%

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:

×71
×19
×17
×10

question asked: 16 Aug '12, 08:57

question was seen: 1,688 times

last updated: 10 Nov '15, 02:26