.InfoWorks ICM - SQL - Illusive Macros.md

Infoworks ICM - Illusive Macros in SQL

In InfoWorks ICM intermediate users may use SQL queries to automate their workflows. One part of the SQL system in InfoWorks which is fairly misunderstood is the macro system.

To be fair, macros aren't really explained very well in the help file either... The explanation given is as follows:

Macros are variables for representing values within a query. The Macro dialog is displayed when you click on either the button or the button on the User Macros tab on the SQL dialog. Enter a title and value for the macro, then click on OK.

Though this is correct, it is also a bit misleading. In this article I want to go into a little more detail as to what the Macro system actually does, and bounce around some ideas as to how it can be used.

Introducing macros in C++

You may ask "Why do they call these macros?", especially since most of us who have worked in Excel with VBA are more knowledgable about VBA macros, which seem entirely different to "The ability to create constants". The reason why this feature is called "macros" is likely because the environment it was built in, C++, has a very similar system called "Macros". This is a lesson to everyone that whenever you question a feature in software, you should first look at what the software was built in.

So what do Macros in C++ do?

You can find a full explanation of all the features of C++ macros here. The specific definition of a Macro is:

A fragment of code which has been given a name. Whenever the name is used, it is replaced by the contents of the macro.

Ultimately if there was a number which I had to use very often I could define it as a macro as follows:

#define SOME_CONSTANT 10

Then whenever I am writing code I could use the constant:

doSomething(SOME_CONSTANT)
doSomethingElse(SOME_CONSTANT)
doSomethingFinally(SOME_CONSTANT)

When the above code is compiled, it will be pre-processed to code like this:

doSomething(10)
doSomethingElse(10)
doSomethingFinally(10)

And afterwards it will be compiled to machine code.

The long and short of it, is that Macros are simply Find-and-Replace engines. You define names, and these names will be replaced with whatever value you assign to it.

Macros in InfoWorks ICM

ICM behaves in a similar way to how C++ acts when defining macros.

1. You define a macro by assigning it a Name and Value.
2. Before the SQL is ran it will replace all the Names in your SQL script with the Values you gave them.
3. Then ICM will run the produced SQL.

Examples

The best way to learn, is by example. Assuming this statement is correct, let's work through some examples which may help you understand what the macro's are doing behind the scenes

Example 1 - Name-Value

If my macros look like this:

MinWidth    300
MaxWidth    800

and my SQL looks like this:

select us_node_id,link_suffix where conduit_width < MinWidth or conduit_width > MaxWidth

When the SQL is run, ICM will replace all instance of MinWidth with 300 and all instances of MaxWidth with 800 leaving the following query:

select us_node_id,link_suffix where conduit_width < 300 or conduit_width > 800

Example 2 - Sub-routines!

If my macros look like this:

smallerConduits    select us_node_id,link_suffix where conduit_width <

and my SQL code looks like this:

smallerConduits 300;
smallerConduits 400;
smallerConduits 500;
smallerConduits 600;

When the SQL is run, ICM will replace all instance of smallerConduits with the text select us_node_id,link_suffix where conduit_width < leaving the following query:

select us_node_id,link_suffix where conduit width < 300;
select us_node_id,link_suffix where conduit width < 400;
select us_node_id,link_suffix where conduit width < 500;
select us_node_id,link_suffix where conduit width < 600;

Example 3 - Using multiple 'parameters'

You can sort of create procedures which consume multiple parameters and use each parameter to do something different. We do this by exploiting lists. The only unfortunate part is we can't actually make functions so we still need to call another sub-routine afterwards, which does all the work. The general syntax becomes:

partA $param1,$param2,... partB;

where partA is defined as LIST $params = and partB uses the params list previously created. Each param can then be accessed using the AREF function.

Currently with the way ICM sets up variables, however, this is not viable as partA can only be ran once. If a way to "undefine" variables were implemented, this would become quite a useful technique for users.

Conclusion

The use of macros can really make your SQLs a lot cleaner and more managable. They also allow you to re-use code, with the use of sub-routines, which is often incredibly useful. If you want true flexibility Ruby scripting will always trump SQL queries, but SQLs still have their benefits as they float with the database, instead of being stand-alone scripts.

Note:

The macro's 'value' textbox is limited to 5 lines of SQL. This isn't a problem since each expression in SQL is seperated by semicolons, but it does lead to potentially messy macros. For example, some code that I was previously experimenting with for example 3 was the following:

; LET $param1=AREF(1,$params); LET $param2=AREF(2,$params);
LET $message = iif($param1>$param2,"Param1 > Param2", iif($param1<$param2,"Param1 < Param2","Param1 == Param2"));
PROMPT TITLE "Comparrsion";
PROMPT LINE $message;
PROMPT DISPLAY;

This query isn't extremely messy but it is easy to see how a complicated query would become messy and difficult to understand. Had this limitation not existed, I could even add comments to the query, but as it stands this is not an option. Hopefully at some point this limitation will be lifted.

It should also be noted that "user macros" are not stored client side, on the user's account. Sub-routines defined like this would become a lot more useful if they could be stored in AppData or in other places on-disk. Alternatively database specific macros would also be useful. Otherwise you have to redefine your macros each time you make an SQL query...