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 firstname.lastname@example.org for further assistance. Reference the community FAQ to learn more about the community.
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
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,
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).
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:
Based on the defaults, the TABLENAME would be:
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,
-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
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.
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,
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):
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