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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to set server and database with parameter variables for SQL Passthru?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to set server and database with parameter variables for SQL Passthru?
 Login/Join
 
Platinum Member
posted
I'm brand new to SQL Passthru stuff, and not finding a good resource besides this forum for questions.

I have some code working with the basics, but my requirements are to have the server and database as parameters, so I think what I need is something like DEFAULT_DATABASE, similar to the DEFAULT_CONNECTION option, but I can't find any info on how to do this.

My code is:
ENGINE SQLMSS SET DEFAULT_CONNECTION &CONN
SQL SQLMSS PREPARE SQLOUT FOR
SELECT field1, field2
FROM mydb.dbo.myTable
END 


The value for &CONN will be one of the adapters I've already set up in the server console, so I just need to be able to specify the database for that connection now.

If I specify the database "myDB" then it runs fine, and I tried putting a parameter in there (&database) instead, but that results in an error.

I don't care if I have to put the database parameter in the SQL, or in a SET command above, I've just tried every variation I can think of and nothing works.

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
Try FROM &MYDB...dbo.myTable

When using a Dialogue Manager variable before a period, you need two periods for the variable and one for the existing period. I'm not sure where this is documented...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Wow, Francis, you're the man (once again)!

In everything I've read, that triple period thing never came up. I could understand if you had to put the variable in quotes or something, but that's the first time I've seen the requirement for two periods (outside of the IBM mainframe).

The only thing I found (searching Google for "webfocus two periods") is this document on Parameterizing Master and Access File Values Using Variables:
quote:
In the Access File, concatenate the variables to create the TABLENAME attribute. Note that the separator for between each part is a period, but to concatenate a variable name and retain the period, you must use two periods:

TABLENAME=&db..&usr..&tprf.table&tsuf,

Based on the defaults, the TABLENAME would be:

TABLENAME=mydb.myusrid.test_table


but that doesn't mention three periods, so even if I had read this, I'd still be stuck, so you're a lifesaver!

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Platinum Member
posted Hide Post
So no option though that you (or anyone else) knows of to set the database earlier so I don't have to repeat "&database.." all through my select query?



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
I just scannd my SQL passthru code and cannot find any examples of setting the database.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
Try this
  
-SET &DataAdapter = 'Reporting';
-SET &DBServer    = 'DEVSQL1';
-SET &DBName      = 'CustomerABC';

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES &DataAdapter &DBServer/,;&DBName
SQL SQLMSS PREPARE SQLOUT FOR
SELECT field1, field2
FROM dbo.myTable
END


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Report This Post
Expert
posted Hide Post
You should also be able to use &MYDB|.dbo.myTable


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Mighty Max, thanks for CONNECTION_ATTRIBUTES syntax.

Documentation says:
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES [connection] server/userid,password [;dbname][:provider_string]


I love the combination of / , ; :


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
We have found that it pays to end any open sessions before opening a new one (even the first). It's probably overkill before the first session, but doesn't hurt.

-* end any possible previous SQL sessions
ENGINE SQLMSS END SESSION
-RUN

Of course if you need to keep a particular session open for some reason, don't issue the command.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
 
Posts: 103 | Registered: April 27, 2011Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Mighty Max:
Try this
  
-SET &DataAdapter = 'Reporting';
-SET &DBServer    = 'DEVSQL1';
-SET &DBName      = 'CustomerABC';

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES &DataAdapter &DBServer/,;&DBName
SQL SQLMSS PREPARE SQLOUT FOR
SELECT field1, field2
FROM dbo.myTable
END


I'm having some trouble getting this to work. I have a data adapter with the application-level user ID and password set up already on the server that I want to use, and so wouldn't the server be redundant?

I'm getting the following error:
(FOC1672) SYNTAX ERROR IN SET CONNECTION ATTRIBUTES FOR SQLMSS   INTERFACE
 

Even though my CONNECTION_ATTRIBUTES string matches Mad Max's format exactly (and that of the documentation ). The documentation indicates three different authentication scenarios, none of which seem to be my scenario, which is use the credentials in the Data Adapter.
I just thought about this too, our WF servers are UNIX, not Windows, as it appears the directions above are for, but it doesn't look like the UNIX instructions give me any option to specify the database for a given adapter.



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Guru
posted Hide Post
What is the Security of the Data Adapter?
If it is Explicit I believe you are going to have to provide the credentials.

You mentioned your reporting server is Unix. I scanned the documentation you provided.
Looks like you will have to build the JDBC url appropriately. Just do a quick google search for "jdbc sql server url".
  
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DataAdapter 'jdbc:microsoft:sqlserver://myServer:1433;databaseName=myDatabase'MYUSER,PASS

You can take a look at your server profile (edasprof.prf).
It should have the appropriate syntax for the data adapter connection attributes in the Unix environment. My syntax might be incorrect.

Also open a ticket with IBI Tech Support.

This message has been edited. Last edited by: Mighty Max,


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Report This Post
Platinum Member
posted Hide Post
The security is explicit. What I don't want to do is have the server password in my focus procedure in clear text.

However, when I look at my adapter, it's like this:
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES myAdapter 'jdbc:sqlserver://serverName:1433'/User,980F27B014D04EBEF8AEBCF6DA952BC4


I'll open a case as well.

So the password there is encrypted. So what I don't know is if I could use that same PW in a procedure, or would it take it literally?

Judging by what the documentation says, I'm thinking not (besides it doesn't mention anything about specifying the database for a UNIX adapter):
quote:
The following SET CONNECTION_ATTRIBUTES command connects to a myServer using the Microsoft SQL Server JDBC Driver, with an explicit user ID (MYUSER) and password (PASS). To ensure security, specify connection attributes from the Web Console, which encrypts the password before adding it to the server profile.

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES CON1
'jdbc:microsoft:sqlserver://myServer:1433'MYUSER,PASS



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Platinum Member
posted Hide Post
Okay, this was just solved via my case by Michelle. Thanks Michelle.

This works with a UNIX server, connecting to SQL Server databases.
The -RUN and END commands are what makes this work! Otherwise the procedure thinks you're concatenating FOCUS databases.

First, set your default connection to the server you want to use, providing via a variable the name of the data adapter:
ENGINE SQLMSS SET DEFAULT_CONNECTION &server
-RUN


Now we set which database to use (also using a variable):
ENGINE SQLMSS
USE &database
END
-RUN


Then we can run our SQL query and send it to SQLOUT:
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
[fields]
FROM tblWhateverTable t1
JOIN tblAnotherTable t2 on t1.Field = t2.Field;
END

*Note: be sure you terminate your SQL query with an apostrophe, and END.

So, all together:
ENGINE SQLMSS SET DEFAULT_CONNECTION &server
-RUN
ENGINE SQLMSS
USE &database
END
-RUN
SQL SQLMSS PREPARE SQLOUT FOR
SELECT


Then you can just build your report with:
TABLE FILE SQLOUT



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
Intriguing,

Did they say where it was in the documentation ?

I can't seem to find it.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
Its MS SQL Server syntax to specify the database. Its been awhile since I have played with MS SQL Server.
If you had a Oracle database you would need to find the equivalent command.

http://msdn.microsoft.com/en-u...8366(v=sql.105).aspx
http://www.tutorialspoint.com/...-select-database.htm

This message has been edited. Last edited by: Mighty Max,


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Report This Post
Expert
posted Hide Post
Oh, so it is.

Doh, should have known that.

Another senior moment.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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     [SOLVED] How to set server and database with parameter variables for SQL Passthru?

Copyright © 1996-2020 Information Builders