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,

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...

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,

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?

I just scannd my SQL passthru code and cannot find any examples of setting the database.

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

You should also be able to use &MYDB|.dbo.myTable

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 / , ; :

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.

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.

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):

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

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

Intriguing,

Did they say where it was in the documentation ?

I can't seem to find it.

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,

Oh, so it is.

Doh, should have known that.

Another senior moment.