Qualifying string for ODBC Output

[tosca1978]

Hi,

I have a TI with an ODBCOutput command line to export cube data to a SQL table.

The code on the advanced data tab looks like:

Code: Select all

IF(vMeasure@='Manager);
ODBCOutput('COLUMBUS_TM1_PUBLISH', Expand( 'update [COLUMBUS_TM1_PUBLISH].[dbo].[VENTURES] SET [Manager] = ''%vValue%'' where [NV Versions]=''%vNVVersions%'' and Hierarchy = ''%vHierarchy%'''));
Endif;

The TI aborts when it comes across data string with an apostrophe in. As the data source is full names names this is frequent. Can anyone stell me how to qualify the string so that the apostrophe does not throw up an error?

Many thanks

[tomok]

This is a very common problem in SQL since the apostrophe is a reserved character. I should know since my last name has an apostrophe in it and I can't tell you how many times a web site won't work for me because the numb-nuts programmer didn't take it into consideration. It's easy, just replace the apostrophe (') with a double apostrophe ('') and the SQL will work. Since TM1 doesn't have a replace function you'll have to use a combination of SUBST and INSRT.

[tosca1978]

This is a very common problem in SQL since the apostrophe is a reserved character. I should know since my last name has an apostrophe in it and I can't tell you how many times a web site won't work for me because the numb-nuts programmer didn't take it into consideration. It's easy, just replace the apostrophe (') with a double apostrophe ('') and the SQL will work. Since TM1 doesn't have a replace function you'll have to use a combination of SUBST and INSRT.

Tomok, many thanks for your reply. That makes sense.

I'm still a bit stuck though. The data source is a cube view so the value variable can sometimes be numeric, sometimes string and sometimes a string with an apostrophe.

Would you perform the insertion of the double apostrophe within the advanced data tab script or within a new variable?

I have tried the following:

I have created another variable called vQualifiedString and put the following formula in:

Code: Select all

vQualifiedString=
IF(SCAN('', vValue)=0,vValue,
INSRT(vValue, '',SCAN('', vValue)));

Changed the TI script to reference vQualifiedString rather than vValue.

The ti still aborts on the same record where the value is a name with an apostrophe in it.

I must be doing something wrong ...

[Duncan P]

In TI '' is an empty string. A string containing only an apostrophe is ''''. So your code probably should read :-

Code: Select all

vQualifiedString=
IF(SCAN('''', vValue)=0,vValue,
INSRT(vValue, '''',SCAN('''', vValue)));

[Duncan P]

Sorry I'm late twigging this but you should not just assume there will be only one apostrophe. For example my household inventory could contain Duncan's dog's food bowl.

The following TI fragment should do what you want. After doubling each apostrophe it searches for another apostrophe in the remainder of the string, looping until it doesn't find one.

Code: Select all

quote_character = '''';
escape_character = '''';
quoted_string = string_to_quote;
search_pos = 0;
quote_pos = SCAN( quote_character, quoted_string );

While ( 0 <> quote_pos );
	quoted_string = INSRT( escape_character, quoted_string, search_pos + quote_pos );
	search_pos = search_pos + quote_pos + 1;
	quote_pos = SCAN( quote_character, SUBST( quoted_string, search_pos + 1, LONG( quoted_string ) - search_pos ) );
End;