Start a new topic

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!


 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

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.

Marcus,


In the future please create a new thread since your question is not really related to the topic of this thread. 


Secondly, sorry for the delay. For fast response Please use Help->Contact Support in DbVisualizer for support inquiries. 


If you run the procedure in the SQL Commander, what is the result in terms of what column meta data it produces?


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

To verify: in the result set grid, right-click and select Describe Data. It will show information about the columns in the result set such as data types. Please reply all the information in that list.


What is XXXXXX in your example? Assuming it is a column name in the result set, to use it as part of the file name:


@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="C:\temp\${XXXXX||||||scope=post}$.sqlplan";
EXEC MSDB.TUNING.PRC_TUNING_SQLS2 '${BANCO}$';
@export off;


Regards


Roger

Marcus,


Apparently it seems you have already solved this based on a confirmation email to support.


Regards


Roger 

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

Login or Signup to post a comment