FAQ: Designer Database Specific

building semantic layer
1

This is where our FAQ for Designer Database Specific material will be developed. This topic is posted as a “sticky” so that it will remain at the top of the first page. Only moderators will be able to add to the FAQ.

Eileen King :us: (BOB member since 2002-07-10)

2

:?: How can I use an Oracle Stored Procedure as a data provider?

:!: To use an Oracle Stored Procedure as a data provider you are required to have a REF CURSOR and a SELECT statement. It must be stand alone and not part of a Package.

It cannot do any kind of writing to a table (as Business Objects is a query tool…) so populating temporary tables does not work.

Using the General Supervisor, permission must be granted for access to the Stored Procedures in order to be able to run them.

There is a default Command Restriction in Supervisor that prevents the use of anything but SELECT. It is “Restrict SQL to ‘SELECT’ Only” under the Query Technique folder for the BusinessObjects module. It would be on Inherit for a user or group and prevents the editing of SQL to be anything other than SELECT. You must set this option to be Disabled, for the Stored Procedure to run as a data provider.

The user who creates the Stored Procedure must be the same user that is used when creating the BOMain.key, universe connections, and personal connection used with Free Hand SQL. There is a known issue when accessing a stored procedure in both Free Hand SQL and Business Objects Stored Procedures with a database user that has privileges to access and run the stored procedure but did not create it.

Eileen King :us: (BOB member since 2002-07-10)

3

:?: How can I embed my TNSNames information directly into my Oracle connection?

:idea: See this topic for details.

Dave Rathbun :us: (BOB member since 2002-06-06)

4

:?: How do I setup my DB2 Connection for Uncommitted Reads (Dirty Reads)?

:idea: In your data access folder, open the relevant parameters file (e.g., db2udben.prm) for your version of DB2. Add the following line to the [GENERAL] section:

END_SQL=FOR SELECT ONLY WITH UR

:idea: Also, depending on what version of DB2/Connect you are running, you could try this suggestion: Edit the DB2CLI.INI file in the SQLLIB directory and place TXNISOLATION=1 at the top of this file. This will force all DB2 Datasource to Uncommited read. No customisation is required to BO files.

:idea: e6 users: Watch this thread for latest updates

Chris Pohl :us: (BOB member since 2002-06-18)

5

:?: How do I get the current date as an object in my universe?

:idea: Oracle - define your object as

trunc(sysdate)

:idea: SQL Server - define your object as

cast(convert(char(10),getdate(),23) as datetime)

This will remove the time section from your getdate() function - just use getdate() if you want current date and time.

:idea: DB2 - define your object as one of the following

CURRENT DATE
CURRENT TIME
CURRENT TIMESTAMP
DATE(CURRENT TIMESTAMP)

See this page for more detailed information

:!: These objects will not parse in the universe because they do not have associated tables. Nor will they run if you build a query against just that object. As soon as other objects are included that do have a table associated with them, i.e. a standard column, then the query will work.