[CASE-OPENED] Creating synonym using External SQL Script - Bug in WebFocus

Sounds like you are using the SQL Pass Thru method to get to your data? This method does not need Meta Data and does not use the access file.

It does, however use the Adapter connection and requires that you're IBI_USER account have SELECT rights on the Object on the Oracle side.

There are hundreds of SQL Pass Thru posts here but in short, you invoke your SQL as so.
ENGINE SQLORA SET DEFAULT_CONNECTION [adapt connection name here]
SQL SQLORA PREPARE SQLOUT FOR

SELECT TO_CHAR(TO_DATE(SYSDATE,'DD-MON-RR'),'RRRRWI') FROM DUAL;
-RUN

TABLE FILE SQLOUT
PRINT *
END
-RUN

Look for the Adapt Connection Name in an Access file that uses the same tables from the database that you want to connect to.

Example Access File:
--------------------------------------------------------------------------------
SEGNAME=DATABASENAME, TABLENAME=DATABASENAME, CONNECTION=youradaptname, KEYS=0, $

Good Luck

WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL

Posts: 291 | Location: Greater Cincinnati | Registered: May 11, 2005

Ignored post by Don Garland posted

Member

posted

I'm not using the SQL Pass Thru.
I'm creating the synonym using .sql file.

Here it is my access file-
SEGNAME=SAMPLE, DATASET=/ibi/apps/sample/sample.sql, CONNECTION=ORACLEDEV, $

Same connection(ORACLEDEV) is working when I use table name instead of DATASET.
i.e.
SEGNAME=SAMPLE, TABLENAME=SAMPLE,
CONNECTION=ORACLEDEV, $

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

Don Garland

Guru

posted

Does DATASET= belong in the MAS file?

http://forums.informationbuild...791097802#9791097802

WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL

Posts: 291 | Location: Greater Cincinnati | Registered: May 11, 2005

Ignored post by Don Garland posted

Francis Mariani

Expert

posted

Don, apparently yes!

I never knew this but you can create metadata for s SQL script file, which is very intriguing.

Dhananjay, I would put the SQL file in one of your Application Folders, then use the WebFOCUS Reporting Server Create Synonym page to generate the metadata. My acx file looks like this:

 SEGNAME=TEST_ROUTINE, CONNECTION=mysqltest1, 
   DATASET=baseapp/test_routine.sql, $

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, 2005

Ignored post by Francis Mariani posted

Francis Mariani

Expert

posted

SEGNAME=SAMPLE, DATASET=/ibi/apps/sample/sample.sql, CONNECTION=ORACLEDEV, $

change to

SEGNAME=SAMPLE, DATASET=sample/sample.sql, CONNECTION=ORACLEDEV, $

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, 2005

Ignored post by Francis Mariani posted

Member

posted

Francis,
Thanks for your suggestion.
But it didn't work.

I changed

DATASET=/ibi/apps/sample/sample.sql

DATASET=sample/sample.sql

where "sample" is my application.

But no luck. It is still using the first connection specified in "edasprof.prf" and ignoring the CONNECTION attribute specified in access file. Even if I remove CONNECTION attribute from access file, still it works and use first connection specified.

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

Member

posted

After few days of testing what I seen is criticizing.

It is using randomly selected connection and not the only first one. Just the probability of selecting first connection is more.

To test this I have created synonym from the query(for Oracle database)-
select * from user_users

When I ran report using synonym created for above query, it is giving me different user name every time. If it is using the connection that is specified in access file, it should give the same username every time.

Please help!

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

David Briars

Master

posted

Sounds like you are, at the moment, hand coding the .mas and .acx files?

If so, and this is a syntax issue, perhaps try creating the meta data with Web Console?:

Web Console
Click 'New'
Select 'Synonym'
Select Adapter
Select Restrict object type to
'External SQL Scripts'
and so on..

Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster

Posts: 822 | Registered: April 23, 2003

Ignored post by David Briars posted

Member

posted

Hello David,

I have created .mas and .acx file using web console only, still I'm facing this issue.

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

Francis Mariani

Expert

posted

Until you figure out what's wrong, you could set the connection manually in the program that uses this master:

ENGINE SQLORA SET DEFAULT_CONNECTION ORACLEDEV

This may override the selection of a "random" data adapter connection.

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, 2005

Ignored post by Francis Mariani posted

Member

posted

Thank You Francis. We were just thinking to use the same approach.

Even though there are few cons of this approach like we have to edit the report definition while migrating code from dev to qa or prod which otherwise could have been managed at metadata layer(access file). But we don't have other option.

I have also opened the case with IBI for the same.

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

Member

posted

Here is the new problem that poped up.

When I used

ENGINE SQLORA SET DEFAULT_CONNECTION ORACLEDEV

and query the data using only that synonym then it is working fine.

But when I use it to join with another synonym, it gives following error-

(FOC1356) NO TABLE NAME FOR THE SEGMENT IN THE ACCESS FILE

e.g.
Say if I have created TEST synonym using SQL Script and queried data following way-

TABLE FILE TEST
PRINT
*
END

It gives the data.
But for following code generate error-

JOIN
LEFT_OUTER JOINFLD1 IN ABC TO
UNIQUE   JOINFLD2 IN TEST
 TAG J0 AS J0
 END
TABLE FILE ABC 
PRINT
*
END

Where ABC is another synonym which is working fine in the join with other tables.

7.6.9/7.7
Windows/OS 390
HTML

Posts: 11 | Location: Pune | Registered: March 08, 2011

Ignored post by Dhananjay posted

WF_IL

Platinum Member

posted

Hi

Creating Joins with master files which describe .sql script is not supported.
see case 81492516
as far as I know this is a NFR which might be implemented quite soon in new releases and will pass the join to the DB (join to SQL query "derived table")
I would recommend you to open a case with IBI and also request this functionality

Yours
Eran

Yours,
Eran
SRL Products

http://www.srl.co.il

Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005

Ignored post by WF_IL posted

Doug

Expert

posted

Have you considered the "CREATE SYNONYM" command?

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted