Variable substitution in client-side commands

Is it possible to use variables in the "@cd" or "@spool" client-side commands? For example, I'd like to do something like the following:

-- Set the variable value
@echo Script directory = ${SCRIPT_DIR||/Users/steve||String||noshow}$
;
-- Change to directory indicated by the variable
@cd ${SCRIPT_DIR}$
;
-- Run my custom script in that directory
@run myscript.sql
;

If this is not currently possible then I'd like to request that this be added.

Thanks!

Hi Steve,

It already works:

@echo ${var||/Users/hans/tmp||||noshow}$;

@cd ${var}$;

@run something

gives:

 13:18:50  [@ECHO - 0 rows, 0.000 secs]  /Users/hans/tmp

 13:18:50  [@CD - 0 rows, 0.000 secs]  Command processed

 13:18:50  [@RUN - 0 rows, 0.000 secs]  /Users/hans/tmp/something does not exist or is not a readable file

... 3 statement(s) executed, 0 rows affected, exec/fetch time: 0.000/0.000 sec  [2 successful, 1 errors]

Does it not work for you?

Best Regards,

Hans

 Odd. It didn't seem to want to work for me. I'll try again on Monday.

Thanks Hans.

Hi Hans,

I've done some more testing and the behaviour is rather strange. I created a script called "myscript.sql" which is called by the @run statement in the script in my original post above. The contents of "myscript.sql" are:

 

-- myscript.sql

@export on;
@export set filename="myscript_result.csv"
            format="CSV"
            CsvIncludeColumnHeader="true"
;

SELECT 
  'Success!!' AS test_result,
  ${SCRIPT_DIR}$ AS script_dir
;

@export off;

When I call run the caller script everything appears to work but there is no export file generated:

 12:37:33  [@ECHO - 0 rows, 0.000 secs]  Script directory = /Users/sjakob/projects
 12:37:33  [@CD - 0 rows, 0.000 secs]  Command processed
 12:37:33  [@RUN - 0 rows, 0.000 secs]  Command processed
... 3 statement(s) executed, 0 rows affected, exec/fetch time: 0.000/0.000 sec  [3 successful, 0 errors]

If I change my "@cd" statement to use the directory name instead of the variable then everything works as expected (although I'd rather the export file be created in the current directory rather than the DbVisualizer application directory):

 12:41:27  [@ECHO - 0 rows, 0.000 secs]  Script directory = /Users/sjakob/projects
 12:41:27  [@CD - 0 rows, 0.000 secs]  Command processed
 12:41:27  [@RUN - 0 rows, 0.000 secs]  Command processed
 12:41:27  [@EXPORT - 0 rows, 0.000 secs]  Command processed
 12:41:27  [@EXPORT - 0 rows, 0.001 secs]  Command processed
 12:41:27  [SELECT - 1 rows, 0.001 secs]  Export processed [ /Applications/DbVisualizer-9.5/DbVisualizer.app/myscript_result.csv ]
 12:41:27  [@EXPORT - 0 rows, 0.000 secs]  Command processed
... 7 statement(s) executed, 1 rows affected, exec/fetch time: 0.002/0.000 sec  [7 successful, 0 errors]

 

 

Hi Steve,

Yes, you're right. It does not work as it should with variables. I am able to reproduce this with your input. 

There are two problems:

1) Variables in the @cd command are only partly handled, resulting in what you see.

2) A relative file path specified with @export set is not based on the current directory set by the @cd command. That is why the result ends up in the wrong directory when you use a directory name for the @cd command.

It works if I change the myscript.sql script like this:

 

-- myscript.sql

@export on;
@export set filename="${SCRIPT_DIR}$/myscript_result.csv"
            format="CSV"
            CsvIncludeColumnHeader="true"
;

SELECT 
  'Success!!' AS test_result,
  ${SCRIPT_DIR}$ AS script_dir
;

@export off;

 

And the invoking script to this:

 

-- Set the variable value
@echo Script directory = ${SCRIPT_DIR||/Users/hans/tmp||String||noshow}$
;

-- Change to directory indicated by the variable
@cd /Users/hans/tmp
;

-- Run my custom script in that directory
@run myscript.sql
;

 

Not sure if that helps. I've opened a ticket to look into fixing these two problems.

Best Regards,

Hans

 Thanks Hans. That's basically what I did to work around the issue. I'm happy to hear that the issues are being addressed.

Please.. I want to export a Query result to a XLS file, but write each CLOB field inside a different file..

The File will need have the follow format: 

   C:\TEMP\SQLS\XXXXX.sqlplan

How to do this?

@export on;

@export set filename="c:\temp\${ARQUIVO}$.xlsx" ExcelIncludeColumnHeader="true" QuoteTextData="None" QuoteDuplicateEmbedded="false" appendFile="true" ExcelTitle="SQL Cache" ExcelSheetName="SQL Cache" CLOBFormat="File" CLOBFileDir=???????

EXEC MSDB.TUNING.PRC_TUNING_SQLS2 '${BANCO}$';

@export off;

   CLOBFileDir=???

Thank You Very Much