Dynamic variable expansion

NN

Hi ,
Can someone please help me out with the correct way to use variable expansion

I have two instances of dss and my project flow requires a pull of data from an sql DB and insert into an SQL DB.

My question is - is there a way i can define a project variable {sqldb} which
When the flow runs in Instance1 will pick up SQLDB1 as the db name
and when it runs in instance2 will pick up SQLDB2 as the db name.

so my select query would be something like select * from ${sqldb}.tablename

Note that i have an instance level variable {ins} which does tell me whether this is instance1 or 2

Marine

Ok makes sense!

I don’t think it’s possible to do exactly what you want.

So I guess the closest solution would be to use an instance variable and a Python code snippet to update the value of the sqldb instance variable. Yet, it only works with instance variables... This code will run :

• Manually, when you click on the “Execute update” button in the Administration part

• Automatically each time you start a job

For more details, have a look at this resource.

If you want to stick with project variables; you could create a scenario to update them and do it once manually.

Marine

Hi,

If my understanding is correct, you have two different instances. In each one, you set a global variable named sqldb. In your first instance, sqdldb = SQLDB1 and in your second, sqldb = SQLDB2. The table that you want to use has the same name in SQLDB1 and in SQLDB2, let's call it "tablename".

In that case, I guess I would :

1) Define my project or instance variable , sqdldb in each instance.

2) Write the query using variable extension :

SELECT * FROM "${sqdldb}_tablename"

Could this work for your use case?

NN

Hi @Marine
,
Thank you for your reply.
I donot wish to define SQLDb as an instance variable.
While i wish to use Db1 in Instance1 some other project may want to use DB2 in instance1 and vice versa, Hence i wanted to define this at the project level.

Secondly our aim is to keep the project codes the same in both instance. Hence if i ended up hardcoding a project variable as DB1 in one instance and DB2 in the other , that would work but not ideal.

I was hoping if we have a way that the variable values can be conditional.
something like {SQLDB : (IF {INS}=='INS1' , "DB1","DB2")}

Marlan

Hi @NN
,

I have a couple of ideas in addition to @Marine
's suggestions... but first I would mention that there is an idea in the Product Ideas board that if implemented in DSS would enable you to do exactly what you'd prefer (use the instance variable to set the database directly within the SQL code). If you like the idea, feel free to kudo it.

Our set up includes database connections that are defined differently on each instance and so we would use this mechanism in a situation like yours. But this assumes that most or all projects would reuse the same databases and it sounds like that is not the case for you.

One option would be to set project variable ${sqldb} to SQLDB2 and then override it on instance1 to SQLDB1. You do this in the 3 dot, Variables screen by specifying the SQLDB2 value in the "Global variables" area and then specifying the SQLDB1 value in the "Local variables" area. Thus on instance1 the value will be SQLDB1 (because "local" variables override "global" variables) and on instance2 it would SQLDB2 (because only "global" variables are include in deployment bundles).

Another option would be to use a Python recipe and in the recipe use Python to apply the logic to set the database and then use the API to execute the SQL (in which you have set the correct database). This is a pretty clean approach (no additional project variables needed) but does require use of Python and testing the SQL part isn't as easy.

If you are OK with handling this within a scenario, then @Marine
's suggestion would work nicely.

Marlan

NN

Thankyou @Marine
and @Marlan

I have been using the Scenario to override the variable till now but like you said it needs to be run once in each instance.

I like the idea of using both local and global variable which would work perfectly if i bundle the project. But i would need to be careful if using git repo branches.
I shall give this a try and i believe it should work fine for now.