Variable Interpolation

Note: Variable Interpolation is available since Slacker version 1.0.7.

Sometimes we pass state between the Ruby test code and a SQL template/script in the form of values. For example, if we had a local Ruby variable x which is loaded by running a query, if we then wanted to run a select statement and fetch all the customers older than the value of x, we would do something like this:

x = query("select top 1 x from dbo.Whatever;")[0][:x]
y = query("select name from dbo.Customer where age > #{x};")[0][:name]

This will work perfectly well, but will generate a hard-coded debug trace SQL file which will look like this:

select top 1 x from dbo.Whatever;
select name from dbo.Customer where age > 36;

The SQL trace file above captures the value 36 for x, which is true at the time of test execution.

The problem here is that 36 is now hard-coded in the SQL trace script which makes it impossible to debug this script at a later date when 36 may not be the top value.

This may not look like an issue with the current example, however, imagine an example where a stored procedure creates a new record and returns the ID of the new record (possibly through an output parameter). Then we may want to use that ID to call other sprocs to perform operations against that record.

What we really want is to have our examples work AND generate executable SQL traces which can reproduce eventual problems observed during example execution.

The solution is called variable interpolation.

We could have implemented the code like this:

@x = query("
       declare @x int;
       select top 1 @x = x from dbo.Whatever;
       select @x as x;
     ")[0][:x]

y = query("select name from dbo.Customer where age > %{@x};")[0][:name]

This will generate the following SQL trace code:

declare @x int;
select top 1 @x = x from dbo.Whatever;
select @x as x;
select name from dbo.Customer where age > @x;

Note that we switched to using an instance variable instead of a local variable. This is important, because Slacker's SQL execution engine will be looking for an instance variable @x when running the query with the interpolation %{@x}.

The above code is now not a snapshot of the previous execution but a live code which will always fetch the same results as the example code itself.