I want to use dbviscmd with a connection that has a parameter in its database url. Is this possible?
Let's say the connection named conn has database url is jdbc:db2://$(param):100/dbname.
Is there a way to pass the value of param to dbviscmd?
dbviscmd <command defining param value> -connection "conn"
I see that I can specify url and driver instead of a connection name, but I specifically want to use -conn so that all of the connection properties used.
In fact, my question is more general than that specific parameter. When using dbviscmd, how do you set parameter values for anything? E.g., in the -sql or -sqlfile arguments.
You need to encapsulate the call of dbviscmd in an environment script and let the script interpreter take care of any variables. Variables are not supported when using dbviscmd.
Thanks Roger. Does that mean that it is impossible to use connections that rely on dbvisualizer variables?
I think my only option is to duplicate the connection and hardcode $(param). Is that correct?
When I connect to this connection in the dbvisualizer gui, I get a popup prompt that allows me to define $(param).
What you're suggesting would work if I wanted to replace dbvisualizer variables with bash variables, e.g. using a shell variable within a query works fine
SQL="select column_name from $TABLE"
dbviscmd -connection "conn" -sql $SQL
but there is no way to define a shell script variable that dbvisualizer can use as a value of its variable $(param), defined in the connection url jdbc:db2://$(param):100/dbname.
(My example is probably not a valid script for bash or any other shell, I'm rusty, but that's not the point here)
DbVisualizer variables in connection details and in scripts are unfortunately not supported when using the dbviscmd command-line interface. I will add your vote for supporting it.
To workound this for connection details in dbviscmd you can instead of specifying a pre-defined -connection, use the -driver, -url, -userid, and -password arguments which will setup a transient connection. That is used only for that single invocation of dbviscmd. Instead of hardcoded values you can invoke dbviscmd from for example a bash script and use shell variables.
The same applies to variables in the scripts whereas you need to rely on bash variables as of now.
What I ended up doing was writing a python script that modifies the xml file that stores my connection info and inserts new <Database> tags with the connection info hard coded.
I'm sure it's a terrible idea that will eventually crash dbvis or delete all my connections, but it worked.
That should work as well.
Just to inform that we have released a beta version of the upcoming DbVisualizer 11.0 with support for prompting of variables and parameter markers when running the command-line interface for DbVisualizer, dbviscmd.
Would be great to get your feedback on the new version.