我想查询一个带条件的sql语句,写了两种方法,都有点问题,麻烦高手帮忙修改一下。方法一: string sqltext="select 1 from users where vc_usr='"+username+"' and (v_user_password='"+userpwd+"' or Password ='"+userpwd+"') and i_checkstate=1";
const char * csqltext=sqltext.c_str();
EXEC SQL PREPARE s FROM :csqltext;
EXEC SQL DECLARE cur_user CURSOR FOR s;
EXEC SQL OPEN cur_user;
while(1)
{
EXEC SQL FETCH cur_user into:datacount;
if(sqlca.sqlcode<0)
{
rqst->data="some error occur.";
break;
}
if(sqlca.sqlcode==SQLNOTFOUND)
{
break;
}
if(datacount>0)
{
rqst->data="this user is exist.";
break;
}
else
{
rqst->data="this user is not exist";
break;
}
}
EXEC SQL CLOSE cur_user;
方法二: EXEC SQL SELECT 1 INTO :datacount FROM users WHERE vc_usr=:username AND (v_user_password=:userpwd OR Password=:userpwd) AND i_checkstat=1;
if(datacount>0)
{
char * result="this user is exist.";
rqst->data=result;
}
else
{
char * result="this user is not exist.";
rqst->data=result;
}
const char * csqltext=sqltext.c_str();
EXEC SQL PREPARE s FROM :csqltext;
EXEC SQL DECLARE cur_user CURSOR FOR s;
EXEC SQL OPEN cur_user;
while(1)
{
EXEC SQL FETCH cur_user into:datacount;
if(sqlca.sqlcode<0)
{
rqst->data="some error occur.";
break;
}
if(sqlca.sqlcode==SQLNOTFOUND)
{
break;
}
if(datacount>0)
{
rqst->data="this user is exist.";
break;
}
else
{
rqst->data="this user is not exist";
break;
}
}
EXEC SQL CLOSE cur_user;
方法二: EXEC SQL SELECT 1 INTO :datacount FROM users WHERE vc_usr=:username AND (v_user_password=:userpwd OR Password=:userpwd) AND i_checkstat=1;
if(datacount>0)
{
char * result="this user is exist.";
rqst->data=result;
}
else
{
char * result="this user is not exist.";
rqst->data=result;
}
既然是动态的SQL,在打开的时候,需要用using命令,把实际参数的值与你动态SQL里的变量按顺序依次绑定上。
不然,程序怎么知道每个变量赋什么值?
你这样:
EXEC SQL OPEN cur_user USING :username, :userpwd, :userpwd;
--因为你的动态SQL里,两次用到了userpwd,所以using的时候,要写两次再调调吧,祝你成功。
我说明一下,这种方式的错误不在于此。应该是在与sqltext没有定义在EXEC SQL BEGIN DECLARE SECTION;
中定义就可以了。我想问第一种情况的原因是,不在EXEC SQL BEGIN DECLARE SECTION 中定义,而在使用的时候再定义,应该如何处理