Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Option to choose the connection and db name at runtime

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Option to choose the connection and db name at runtime
 Login/Join
 
Member
posted
Hi All,

I have report that needs to run on different database servers and databases. The master file/table information is the same in all environments, just the data is different.

I wanted to know if a report can be build in such a way that we have the option to pick which connection and database at report runtime?

Other alternative is creating separate domains for each connection and copy the report to these domains. This works. But, not the best way to approach it.

Thanks.


---------------------------------
Prod - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Test - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Dev - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Output - PDF/HTML/AHTML/Excel
 
Posts: 14 | Registered: September 13, 2007Report This Post
Platinum Member
posted Hide Post
How about using some Dialog Manager code to select the set of connection code strings to execute for that run ?

You would need some minimal user interface to provide a selection choice to pass to DM to selectively set your connection.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Member
posted Hide Post
How can I achieve this? Could you please provide me with a sample/example?


---------------------------------
Prod - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Test - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Dev - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Output - PDF/HTML/AHTML/Excel
 
Posts: 14 | Registered: September 13, 2007Report This Post
Platinum Member
posted Hide Post
OK, here goes, some very simple Dialog Manager code to choose between various connections

-IF &CHOICE EQ 'A' GOTO CONN1
-IF &CHOICE EQ 'B' GOTO CONN2
-IF &CHOICE EQ 'C' GOTO CONN3
-*
-CONN1
connection string for db 1
_GOTO REPRT_CODE
-*
-CONN2
connection string for db 2
-GOTO REPRT_CODE
-*
-CONN3
connection string for db 3
-GOTO REPRT_CODE
-*
-REPRT_CODE
-RUN

TABLE FILE CAR
...
END

-RUN

Since &CHOICE isn't set in this program it will be prompted for, unless you set up some interface to present a more understandable set of options to the user and pass the variable to your report program, and that may vary depending on your environment.

The rest is left up to you, Good Luck.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Member
posted Hide Post
Thanks a lot Dave. I'll try that.


---------------------------------
Prod - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Test - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Dev - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Output - PDF/HTML/AHTML/Excel
 
Posts: 14 | Registered: September 13, 2007Report This Post
Virtuoso
posted Hide Post
BIGuru,

I suppose you have in your edasprof.prf file a number of connections. For example with some SQL/SERVER databases:
  
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv1 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv2 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv3 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv4 webserver/user,pwd;database


Then, in your procedure, if you want to acces Srv3, you can code before any TABLE request:
SQL SQLMSS SET SERVER Srv3

Of course, this statement can be parameterized.
I hope this works for you.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
I can see how the connect strings would work with SQL Passthru.

Correct me if i'm wrong but the CONNECTION setting within the ACX file is hard coded. Once it's accessed it'll use the connection entry for that ACX file. I don't see how the connect string would help under this scenario.
 
Posts: 140 | Registered: May 02, 2007Report This Post
Virtuoso
posted Hide Post
You are right IF the CONNECTION setting is coded in the ACX file. But it is not compulsory. Just delete it and WF will connect to the server defined by SET SERVER. This is very useful, for instance, when you have 2 databases, test and production for example or maybe 2 separate databses for different years and you want to report from both.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
FC,

If your acx files are in differenct directories, you can use the variable to adjust the application path with an APP PREPENDPATH to find the acx files appropriate for the connection that you want.

However, if you are doing passthru there is no master and no acx file.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Member
posted Hide Post
quote:
Originally posted by Danny-SRL:
BIGuru,

I suppose you have in your edasprof.prf file a number of connections. For example with some SQL/SERVER databases:
  
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv1 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv2 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv3 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv4 webserver/user,pwd;database


Then, in your procedure, if you want to acces Srv3, you can code before any TABLE request:
SQL SQLMSS SET SERVER Srv3

Of course, this statement can be parameterized.
I hope this works for you.


Danny,

I cannot use this solution as I have to hard code the connection information, say 'srv3', cos when the report moves to production this connection won't exist, there will only be one connection.


---------------------------------
Prod - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Test - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Dev - WebFOCUS 7.7.03/ MS-SQL Server 2005/ParAccel/Windows 2008 R2 64Bit
Output - PDF/HTML/AHTML/Excel
 
Posts: 14 | Registered: September 13, 2007Report This Post
Virtuoso
posted Hide Post
BIGuru,

So, just remove the SET SERVER statement, or use a parameter which would be blank in the production environment.
You can also use the PREPENDPATH statement but then you need to multiply all your ACX files which becomes quite cumbersome if you have many tables and if your test environment changes.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
BIGuru,

Another idea would be to set the server name/IP Address in edasprof.prf, or, pass it from site.wfs, cgivars, etc.

Then, have a FOCEXEC which contains the environment variables, the server connection being one of them, and, -INCLUDE the fex at the top of each focexec:

 
-INCLUDE WF_ENV_001

Would have within it:

-SET &CONNECTION = IF &SERVER_NAME EQ 'IP address' OR 'Alias Name' THEN  'DEVELOPMENT NAME' ELSE 'PRODUCTION NAME';

Then the SQL code:

SQL SQLMSS SET SERVER &CONNECTION



Thought I would throw out one more idea...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Isn't this similar to a recent problem. You can do this one of several ways. You can use a 'Variable' as the high level identifier qualifier and then enter the value at run time. You can have different -Include .fex that have the correct statements and then either use the correct .fex or prompt for the .fex.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Option to choose the connection and db name at runtime

Copyright © 1996-2020 Information Builders