Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Is there a way to drive the OLEDB connect statement by an IF statement and variable?

I modified a script of ours to try to dynamically pick a OLEDB connect string.  I created a spreadsheet that just has 1 value in it.  Either PROD or TEST.  I read the table based off of the spreadsheet and assign the value to a variable.  I then have the following IF statement:

IF $(ServerEnv) = 'PROD' then

//PROD

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=wms_dw;Data Source=WMS_DWPROD;Extended Properties=""] (XPassword is PSWD);

ELSEIF $(ServerEnv) = 'TEST' then

//TEST

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=wms_dw;Data Source=WMS_DWPROD;Extended Properties=""] (XPassword is PSWD);

It runs and says successful but does not hit any of the code after this statement.  I did put a text object out on a page so that I could see it change between PROD and TEST and that works fine.

Any ideas?

10 Replies
Not applicable
Author

This is not really a layout or visualization question but was the only one that allowed me to post.

Not applicable
Author

Did you put an End if statement at the end of the logic?

maxgro
MVP
MVP

did you see some connect in the log?

try with some trace

IF '$(ServerEnv)' = 'PROD' then

trace PROD;

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=wms_dw;Data Source=WMS_DWPROD;Extended Properties=""] (XPassword is PSWD);

ELSEIF '$(ServerEnv)' = 'TEST' then

trace TEST;

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=wms_dw;Data Source=WMS_DWPROD;Extended Properties=""] (XPassword is PSWD);

end if;

Not applicable
Author

I did not have it, but added it in and still does not work.

Not applicable
Author

Adding the TRACE statement does not add anything to the log.

It shows the following:

IF TEST = 'PROD' then  (**spreadsheet has TEST in it**)

Then it skips over the rest of the IF statement and tries to create the 1st QVD but fails because there is no connection established.  For some reason it is not executing the OLEDB CONNECT32 statement.

Not applicable
Author

Try without the If statements(for testing purposes). Just directly use the Connector statements and see if it works. Also can you attach the log file.

Not applicable
Author

The statements by themselves without the IF statements works because that's how it was originally.  I am trying to add the IF statements so that we do not have to comment out the TEST connection when we promote to PROD and comment out the PROD connection when we promote to TEST.

Not applicable
Author

Just a suggestion. Usually to handle these situations put both the connections in two separate txt file( and give it the same name ex: Connection).Then put the one for Test in Test server and the one for Prod in Prod server.

Then in your script just use an include statement to read from this Connection.txt file.

$(Include=..\Config\Connection.txt);

This will take care of it based on what server you are running your qvw. Another advantage is that the connection is centralized so its easy to manage and other apps can also take advantage of it.

Thanks

Not applicable
Author

Thanks.  We were thinking of possibly going in this direction.  We are going to give it a try.