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     SQL passthrough username problem

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQL passthrough username problem
 Login/Join
 
George Brown
Gold member
posted
When we run <br /><br /><br />
 SQL <br /> 
SELECT DISTINCTSELECT DISTINCT av_inst_type<br />  from cr_av_inst_typ_tbl<br />END
We get a nice happy table with our institution types. <br />
<br />but when we
use<br />
SET SQLENGINE=SQLORA<br />SQL  <br />  SELECT DISTINCT av_inst_type 
<br />  from cr_av_inst_typ_tbl<br />END
We get the error<br />
quote:
<br />(FOC1400) SQLCODE IS 942 (HEX: 000003AE)<br />(FOC1409) SQL TABLE NOT FOUND.
(FOR TABLE: CHECK SEGNAME AND TABLE
)<br />: ORA-00942: table or view does not exist<br />: Erroneous token: cr_av_inst_typ_tbl<br />L
(FOC1405) SQL PREPARE ERROR.
<br />
For some reason,
after you specify that you want the oracle environment it chooses a userid (seemingly at random) and executes all SQL commands as that user.
How do we change the user?

This message has been edited. Last edited by: <Mabel>,
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Report This Post
<Pietro De Santis>
posted
Setting the server and User ID/Password may solve the problem:

SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER ServerName
SQL SET CONNECTION_ATTRIBUTES ServerName/UserID,Password
-RUN
 
Report This Post
BIGBMN
Silver Member
posted Hide Post
George,
In your first example, unless you have the RDBMS engine set outside of the focexec, you are running a query against a master file description with the name of cr_av_inst_typ_tbl.
In your second example, you are trying to run a query against an Oracle Table of cr_av_inst_typ_tbl, which apparantly does not exist for the userid that you are connecting to Oracle.
Ben Naphtali
 
Posts: 34 | Location: 2 Penn Plaza | Registered: July 29, 2004Report This Post
George Brown
Gold member
posted Hide Post
quote:
Originally posted by Pietro De Santis:
[qb] Setting the server and User ID/Password may solve the problem:

SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER ServerName
SQL SET CONNECTION_ATTRIBUTES ServerName/UserID,Password
-RUN [/qb]
Not to be knit-picky but if this option is used, what stops any other user from reading the code of the fex to get the database username and password? Isn't this a major breach in security?
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Report This Post
<Dave Parsons>
posted
if you want to hide the username and password in your code, you can set up a data adaptor and enter the connection details (password will be encrypted)and then refer to this in your SQL connection code ..

e.g.
Add an adaptor using the adaptor set up screen

This will add a line like this to EDASPROF
ENGINE SQLORA SET CONNECTION_ATTRIBUTES odssec/report_ro,D6E0CC6588847EEA

note the password is encrypted ;>)

then use this in your code

SQL SQLORA SET SERVER odssec
SQL SQLORA
select
e.COMPANY,
...
..
.
 
Report This Post
<Pietro De Santis>
posted
George, sure this is a major breach of security, if that's what you're worried about. If you're not worried about security, then it isn't. My suggestion to add the connection statement was to get you further in solving your problem. As Dave mentions, you can add the connection statement to EDASPROF.
 
Report This Post
<RJones>
posted
George,

In your first example you are using SQL Translation and the second example is using SQL Passthru. SQL Translation is going to find a MFD called cr_av_inst_typ_tbl and convert the SQL to the correct syntax for whatever dbms it is.

SQL Passthru passes the sql directly to the dbms so the 'from cr_av_inst_typ_tbl' needs to be an actual dbms table name. If it is, you probably need 'from owner.tablename'.
 
Report This Post
George Brown
Gold member
posted Hide Post
For anyone that wanted to know... the actual problem was that I didn't inlude the schema name in front of the table name. Oracle can be very picky sometimes Smiler
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Report This Post
<mohith>
posted
i did this
SET SQLENGINE=SQLMSS
SQL
SELECT DESCRIPTION FROM CATEGORIES;
END

but i get an error 0 ERROR AT OR NEAR LINE 1 IN PROCEDURE SQLOUT FOCEXEC *
(FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.
(FOC098) LIMIT FOR TOTAL LENGTH OF ALL VERB OBJECTS EXCEEDED

also how do i print this output in html?
 
Report 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     SQL passthrough username problem

Copyright © 1996-2020 Information Builders