Bug with ICD and SELECT statement

[Vic]

Hi, guys,

I am not sure if you have ever run into it, but I am trying to figure out how to fix the following problem:

Genesys ICD Data Lookup object does not support username.tablename format.

Depending on a data center, sometimes datatable have username followed by [.] attached to it. In fact this is a common practice for most of the data centers. If you look into your db, you will see that there are dbo.systables dbo.something, username.something and so on and so forth.

Well, I need to lookup a data from a database, where the tablename is called username.tablename

After defining fields and Format in CME (where tablename in FORMAT object is defined as: [username].[tablename] ) I create a strategy with a data lookup object. Guess what: after defining the fileds I want to retrieve and loading the strategy, SQL issued by URS is TRUNCATED!!!! Instead of being SELECT a,b,c from [username].[table] where a=123 it has:

SELECT a,b,c from [username]

The cause is actually ICD! I have played with it and noticed that ICD wizard cuts the rest of the sentence off after [.]

I tried different things:
[username].[tablename]
[username.tablename]
username.tablename
username.[tablename]

needless to say, nothing worked!!!

Does anyone have a workaround for this?

Interestingly enough [username].[procedurename] works just fine, so I am left with no choice but to make stored procedures for every SELECT statement I will make.
This does not make sense!!!

someone help!

[Kevin]

Vic
I realize it's a workaround, but have you examined using views out of a central database?
For example, if you have DatabaseA with Tables A, B, and C, and DatabaseB with Tables X, Y, and Z, create views DatabaseA.view_X, DatabaseA.view_Y, and DatabaseA.view_Z. You can specify the other database from within the view, and this shields it from having to specify the database.table in the select statement.

[Vic]

Hi, Kevin,

I tried to do it too, but, since view will need to be placed on the same server, [username].[view] is the only acceptable way to do it.

I am very surprized that Genesys has not noticed it earlier plus I REALLY HATE the fact that we are no longer allowed to create our OWN SQL statements in ICD. What the hell were they thinking?
URS does not process SQL anyway it is just passed as is to SQL server, so why in the world would they limit it? At least they should allow us to add SQL in ANNEX of a strategy or something. Aaarghhh, sometimes they really baffle me with the way they tend to totally discourage some of their most loyal advanced users from using their product.



And don't get me even started on their concept of passing data from SELECT back!!! if you want data to be automatically attached to the call, both keys and data needs | to be added to the end.
Like, key1| value| .... can you imagine storing "value|" in DB instead of "value" just so that URS would be able to retrieve it!?

Frustration, frustration, frustration...Who the hell is testing these products?

[Leshek]

Hi, Vic

I think I have a workaround / solution. I think it should work when you have MSSQL but probably the similar solution exists for another DBMSs.
So:
1. Recreate your tables using CREATE TABLE dbo.table_name statement (you have to be db owner to do it), not just CREATE TABLE table_name. Then any database user (who has apropriate rights) will be able to use just simple table names just "SELECT .. FROM table_name" instead of "SELECT .. FROM dbo.table_name". But be careful: when there are two tables in the database (let's say: dbo.my_table and genesys.my_table) then "SELECT * FROM my_table" will get data from dbo.my_table when you're not "genesys" user and from genesys.my_table when you are "genesys".

or

2. Create view using "CREATE VIEW dbo.view_name as select ..........from username.tablename" statement. This trick is similar to the previous, but you don't need to recreate your tables. Just use view_name and that's all.


The second solution allows to format the data that is returned from select statement (just use expressions in the select clause in the view definition). So it is possible to return "Key|Value" strings from the SELECT statement without storing extra values ("value|" or anything) in the database.

Regards,
Leshek