Unable to execute SQL Query using TI process.

[ryan]

I am unable to execute SQL Query using TI process.

I would like to insert a row in simple table.

Database: MS SQL Server (Quoted_Identifier is set OFF in MS SQL Server, in order to entertain double quotes)
Table Details:
Name: Table1
Element1: SNO (Int)
Element2: ENAME (VChar(10))
Element3: State (vChar(10))

What I want is:
1, 'Ryan', 'Victoria' to be inserted in the Table1

What I have done:
- Established ODBC Connectivity, I have tested and working fine, I could able to retrieve rows from the table to TM1 environment using the ODBC connectivity.

Code @ Data procedure tab (tried two ways):
#1
vSN = 1;
vEN = 'Ryan';
vSt = 'Victoria';

Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% , "%vEN%" , "%vSt%")');
ODBCOutput(DSNName,Query);

Error: Error executing SQL query: "INSERT INTO Table1(SNO,ENAME,State) Values( 1.000,"Ryan","Victoria")"


#2
Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' |vEN | ',' | vSt | ')');
ODBCOutput(DSNName,Query);

Error: Error executing SQL query: "INSERT INTO Table1(SNO,ENAME,State) Values( 1.000,Ryan,Victoria)"


None of the two query are eligible to run MS SQL Server environment as the string values are not enclosed with single quote(which is not possible in TM1). Is there any extra TI function that could turn double quote in to single quote?. Could anyone help me in running this query without fail? I appreciate your help.

[upali]

Use triple quotes

Code: Select all

Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' '''|vEN |''' ',' '''| vSt |''' ')');

[ryan]

Use triple quotes

Code: Select all

Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' '''|vEN |''' ',' '''| vSt |''' ')');

oops.... not working.

[PlanningDev]

Concatenate using the char() function in TM1. Also, I dont think you need the expand function here. The variables should resolve unless there is some reason you are dynamically naming the variables.

Char(39) is for a single quote

Something along the lines of

Query = 'Insert INTO Table1(SNO,ENAME,State) VALUES( ' | NumberToString(vSN) | ', ' | CHAR(39) | vEN | CHAR(39) | ' , ' | CHAR(39) | vSt | CHAR(39) | ')';

[Michel Zijlema]

Use triple quotes

Code: Select all

Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' '''|vEN |''' ',' '''| vSt |''' ')');

oops.... not working.

Please note (without looking more in-depth at your code) that you're putting a lot of quotes after each other: %vSN% | ',' '''|vEN |''' ',' '''| vSt.
Try %vSN% | ', ' | '''|vEN |''' | ', ' | '''| vSt or %vSN% | ', '''|vEN |''', '''| vSt instead.

Michel