Page 1 of 1
Qualifying string for ODBC Output
Posted: Wed Jul 25, 2012 11:33 am
by 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
Re: Qualifying string for ODBC Output
Posted: Wed Jul 25, 2012 11:50 am
by 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.
Re: Qualifying string for ODBC Output
Posted: Wed Jul 25, 2012 12:44 pm
by tosca1978
tomok wrote: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 ...
Re: Qualifying string for ODBC Output
Posted: Wed Jul 25, 2012 1:25 pm
by 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)));
Re: Qualifying string for ODBC Output
Posted: Mon Aug 20, 2012 1:18 pm
by 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;