Toad World® Forums

Using Bind Variables to Replace Information Directly in SQL (DB2 Specifically)

Hi, I remember being told before that bind variables cannot be used inside a query unless it specifically equaled the bind variable. For example you can say Where customer = :account, and this should allow you to input the variable.

We use DB2 query language though and OFTEN have times where we need to substitute a database name around without having to cut and paste. For example if we have a database name of TXFILE, we’d like to be able to have something that allowed a variable that said :stateFILE, to change for example the state to change the database name. I hope this makes sense. We have another very old query tool that allowed us to do this and since we work in so many different databases utilizing these substitutions would be huge to be able to run queries quickly without having to set up ten different queries each time for each database.

Is there any enhancement whatsoever that will allow us to use variables on say things such as the Database name or even the table name so we can switch that by using the variable prompt so it’s recognized and can run?

Any help or ideas you could give would be very appreciated.

Chad King

Are you talking about variables used in the SQL Editor or in Automation? If you are talking about automation you can use variables anywhere in the SQL and use the [tag:var]# symbols. This is a literal replacement that is done before execution.Does this help?

Debbie, I’m speaking of variables in the SQL editor actually. Like for example we could be running some QC queries and want to keep the same query and without having to change the database name each time and run, we want the variable to be on the database name, then hit run, prompt for the variable, enter that, and it will run whatever database we select. This way, we dont have to copy and paste or replace each time. If it’s not a feature that’s available, can it be???

We have a two tools that I could use for this enhancement. But let me clarify if this would work.

First of all, from your description you don't really want a bind variable but a lteral replacement replacement variable. A true bind variable would be bound at the database end. I think we you want to do something like below, where you want to replace the '&newDatabase' with a database/schema name. These must be scripts you use on various instances that have the same objects but in different databases. This would be literal replacements.

In Oracle we have the '&' symbol which does mean this.

create table &newDatabase.NewTable1 as select * from address;

create table &newDatabase.NewTable2 as select * from region;

select * from &newDatabase.NewTable1;

select * from &newDatabase.NewTable2;

Here is screen shot of this literal replacement symbol when executing with Oracle.

The only issue here is that it prompts for every use of the variable.

We also have a global parameter window that would not prompt and just apply value for every occurrence. My proposal is to add support of the literal replacement variable '&' for both the circumstances (single prompt and global replace)

Would this cover your needs?

Debbie

I entered QAT-2770 for this enhancement.

Debbie, would this only apply to Oracle queries? When I asked before, I believe they said it would only work for Oracle queries. Our company does IBM DB2 queries, so I’m not sure if it would work for us would it? If not, is this why you entered the enhancement request?

Thanks again!

Chad

The enhancement would be to have the Oracle feature work on all other connection types. TDP implements this on the client side so we can apply this to DB2 or any other provider.

Great, thanks. I look forward to seeing if it can be done. It would be a great enhancement.

Chad

I started to work on this today and found out we already have this using our Toad custom syntax. If you use the Toad Unquote function is will prompt you for your value and replace it as a literal. Here is sample syntax. Can you try in your scripts for DB2? I will get this documented in the help file.

Create table {{Unquote(:name)}} as select * from dual

Debbie, you are the best! Yes this works EXACTLY like we wanted!!! This is something that will be extremely useful having so many different libraries and database names on our end!

Thanks

Chad

Just in case you were interested there are other things that can be done in the editor. See this blog.

toad.henrik.org/…/flow-control-for-your-scripts.html