use a for ... next loop to set connection strings from an external file

[#1]

Our data source is moving (again) from one IP address to another.  I would like to set up a table to manage the connections and store the connection strings in an excel file so that every time this happens I just have to change the connection one place (the spreadsheet) instead of in each dashboard. 

The connection string is like " OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID= ... "  The concept works when I directly assign the connection string to the variable but not when I try to bring it in from a spreadsheet.  I'm guessing the problem is in my for loop.

I thought I could use a for .. next loop to do this but have been unsuccessful.  My code looks something like this.

Any help with what I am doing wrong would be greatly appreciated. 

// load the connection strings from excel spreadsheet

Connections:

LOAD

     varConnection,

     ConnectString

From Connections.xls

(biff, embedded labels, table is Sheet 1$);

//loop through connections to set

for i=0 to NoOfRows('Connections') - 1

If varConnection = 'DB1' then

   Let varDB1connect = ConnectString;

ELSEIF varConnection = 'DB2' then

     Let varDB2connect = ConnectString;

Endif;

Next i;

//use connection to DB1

$(varDB1connect)

DBTable1:

Load

     A,

     B,

     C;

SQL Select * from DB1.TestTable;

// use connection to DB2

$(varDB2connect)

DBTable2:

Load

     D,

     E,

     F;

SQL Select * from DB2.Test2Table;

[Oleg_Troyansky · Accepted solution]

As an alternative, I'd recommend using INCLUDE with a couple of text files instead of this loop:

$(INCLUDE=\connections\Connect1.txt)

LOAD...

$(INCLUDE=\connections\Connect2.txt)

LOAD

However, if you really want to use the loop, then your loop should use function peek(), - something like this:

//loop through connections to set

for i=0 to NoOfRows('Connections') - 1

   Let varDB$(i)connect = peek('ConnectString', i, 'Connections');

Next i;

This way, for each value of i, you will peek the relevant value...

[markmccoid]

HI,

Your loop looks good to me. 

You should change your Let statements to Set and see how that works.

If varConnection = 'DB1' then

   Set varDB1connect = ConnectString;

ELSEIF varConnection = 'DB2' then

     Set varDB2connect = ConnectString;

Endif;

The Let statement is used to evaluate the statement to the right, while the Set statement is just used to store the info in your variable.

Hope this helps.

Mark

[#4]

Thanks to both of you for your help.  The peek(...) worked.  I ended up modifying it a bit to use it in the if statement as well since my real connection names were a bit more descriptive than DB1, DB2 and I have to connect to at least half a dozen different databases in some of the dashboards.

for i=0 to NoOfRows('Connections') - 1

If peek('varConnection', i, 'Connections')  = 'DB1' then

   Let varDB1connect = peek('ConnectString', i, 'Connections');

ELSEIF peek('varConnection', i, 'Connections') = 'DB2' then

     Let varDB2connect = peek('ConnectString', i, 'Connections');

Endif;

Next i;

Thanks again for your help!!!

Vicky