Escape Character in Variable

building semantic layer
1

Hi everyone,

I am trying to use the BOBJ variable, @VARIABLE(‘BOUSER’), as a join condition to implement row level security. The problem I am having is that our database is Amazon Redshift and the the value in the business objects user has a “” character.

The “” character is a protected character in Redshift and it needs to be escaped with a second slash. For example, a user name of Mexico\johnsmith, needs to come through as Mexico\johnsmith.

How can I intercept the value of the variable, before it gets sent to the database and add in the second slash?

joseph.bertram (BOB member since 2015-07-06)

2

Would a replace statement work in what you need to achieve?

Don’t know your RDBMS at all so it might not but something like:
REPLACE(@VARIABLE(‘BOUSER’),’’,’\’)

3

Hi,

What database do you use? Can you just simply replace a single backslash with 2 backslashes? Something like this?

Replace(@VARIABLE('BOUSER'),'\','\\')

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

I have to ask, how on earth do you end up with a situation where you have special characters in user names?

:flush:

An absolute no, no, with any system, not just an Amazon database.

Mak 1 :uk: (BOB member since 2005-01-06)

5

So, I agree, the user name shouldn’t have special characters in it. But apparently, it’s how our international users authenticate. They pass in domain\username. Technically, the username doesn’t have the special character in it, but BO takes the full string and not just the user name after the slash.

I can’t use the database replace function, because the moment the single slash shows up in the DB, it gets interpreted as the “Escape” character and tries to escape the following character. Replace won’t match it because it won’t even see it.

I need some way to replace it prior to the DB seeing it. Does BO have server side variables I can setup?

I’ve worked a lot with Oracle BI and their server engine has a location were you can setup formulas based on internal variables without hitting a DB.

joseph.bertram (BOB member since 2015-07-06)

6

[Moderator note - moved to the Semantic Layer forum]

Nick Daniels :uk: (BOB member since 2002-08-15)

7

[quote:dd826b8214=“joseph.bertram”]So, I agree, the user name shouldn’t have special characters in it. But apparently, it’s how our international users authenticate. They pass in domain\username. Technically, the username doesn’t have the special character in it, but BO takes the full string and not just the user name after the slash.

[/quote]

Sounds tough .
I suspect you don’t have individual database accounts implemented? Otherwise you may have been able to use @DBUSER.

I’m really at a loss as to how you could get this to work.
Maybe you could do something with the BEGIN_SQL command, that is covered in the Designer guide.

Mak 1 :uk: (BOB member since 2005-01-06)

8

If you create an object @VARIABLE(‘BOUSER’) and bring it back with a simple list, what does it look like? Does the query succeed?

9

I’m guessing it will fail and return an error, given the actual value stored is:-

Mexico\johnsmith

Mak 1 :uk: (BOB member since 2005-01-06)

10

I don’t know, given that the @Variable would potentially not be touching the database.