企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 501|回复: 4

Unable to execute SQL Query using TI process.

[复制链接]

73

主题

386

帖子

554

积分

高级会员

Rank: 4

积分
554
QQ
发表于 2014-3-17 10:23:43 | 显示全部楼层 |阅读模式
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.
回复

使用道具 举报

64

主题

354

帖子

512

积分

高级会员

Rank: 4

积分
512
QQ
发表于 2014-3-17 11:50:33 | 显示全部楼层
Use triple quotes

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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

81

主题

410

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-17 12:25:17 | 显示全部楼层
upali wrote:Use triple quotes

Code: Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' '''|vEN |''' ',' '''| vSt |''' ')');
oops.... not working.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

79

主题

430

帖子

606

积分

高级会员

Rank: 4

积分
606
QQ
发表于 2014-3-17 15:04:32 | 显示全部楼层
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) | ')';
回复 支持 反对

使用道具 举报

76

主题

356

帖子

534

积分

高级会员

Rank: 4

积分
534
QQ
发表于 2014-3-17 15:14:59 | 显示全部楼层
ryan wrote:[quote]upali wrote:Use triple quotes

Code: Query = Expand('Insert INTO Table1(SNO,ENAME,State) VALUES(%vSN% | ',' '''|vEN |''' ',' '''| vSt |''' ')');
oops.... not working.
[/quote]
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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-10-20 19:21 , Processed in 0.134259 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表