TM1 Forum

Posted: **Fri Nov 30, 2018 4:39 am**

Hi Team,
We are on tm1 10.2.2, I am trying to insert cube values in to SQL table using ODBCOUTPUT option.
Data in one of the field value has a special char like (Women's Special) and it is failing to load.
How do you handle special char when inserting data in to SQL server.
Thanks in advance.

this is my insert statement
SQLInsert = EXPAND ('INSERT INTO [TM1].[BudgetForecast_CIRC_Stage] (Version,ProductCode,TitleVersion,IssueCode,Channel, MeasureType, Qty) VALUES (''%vVersion%'', ''%vTitle%'',''%vTitleVersion%'', ''%vIssue%'',''%vChannel%'',''%vCircMeasure%'', %vValue% )');

statement dailed to laod -[A03955] MEN'S STYLE","

"Budget","RLS","[A03955] MEN'S STYLE","201803","[AU5010] Subscriptions Mail Paid Regular","Sales","627",Data Source line (27) Error: Data procedure line (28): Error executing SQL query: "..."

Posted: **Fri Nov 30, 2018 8:31 am**

This isn't a TM1 thing as such, it's a SQL thing. If you try and do an insert statement in SQL directly you need to use a double apostrophe e.g. MEN''S STYLE in the insert statement and it will get into the database as you want it displayed MEN'S STYLE.
This is particularly common with names etc - this is why in the first instance its good practice to avoid special characters in all systems but that's not always practical and TM1 being at the end or middle of the chain means it often has to put up with and resolve the issue.

To do this you take your variable/s that might have offending characters. Create a loop and check each character with SUBST. If the character is an apostrophe replace it with 2 apostrophes, keep looping until the variable is replaced with your checked variable. Then your insert statement will work.

Posted: **Fri Nov 30, 2018 2:02 pm**

SCAN will help here too.

Posted: **Mon Dec 03, 2018 11:43 pm**

Thank You, I will give a try.

Posted: **Tue Dec 04, 2018 11:49 pm**

Hi

Here is some sample TI

Code: Select all

# Delete Bad Character from line

vText = ;

vBadCh = '' ;

vPos = Long( vText ) ;
WHILE( vPos > 0 ) ;
  vPos = vPos - 1 ;
  vCh = subst( vText , vPos , 1 ) ;
  IF( vCh @= vBadCh ) ;
    vText = DELET( vText , vPos , 1  ) ;
  ENDIF ;
END ;

# Replace Character in line

vText = ;

vFindCh = '/' ;
vReplaceCh = '_' ; 

vNewText = '' ; 

vLen = Long( vText ) ;
vPos = 0 ;
WHILE( vPos < vLen ) ;
  vPos = vPos + 1 ;
  vCh = subst( vText , vPos , 1 ) ;
  IF( vCh @= vFindCh ) ;
    vCh = vReplaceCh ;
  ENDIF ;
  vNewText = vNewText | vCh ;
END ;

vYourVar = vNewText ;


# Replace String in Line

vText = ;

vFindText = 'abc' ;
vReplaceText = 'cba' ; 
vFindLen = LONG( vFindText ) ;

vLen = Long( vText ) ;
vPos = SCAN( vFindText , vText )  ;
WHILE( vPos > 0 ) ;
  vLeftText = subst( vText , 1 , vPos - 1 ) ;
  vRightText = subst( vText , vPos + vFindLen , 255 ) ;
  vText = vLeftText | vReplaceText | vRightText ;
  vPos = SCAN( vFindText , vText )  ;
END ;

vYourVar = vText ;

TM1 Forum

Database Insert

Database Insert

Re: Database Insert

Re: Database Insert

Re: Database Insert

Re: Database Insert