存储过程代码如下:CREATE PROCEDURE `SP_FORSQL`(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000),SqlCMD3 varchar(8000))
begin
/*错误处理,遇到任何错误,自动回滚*/
#declare exit handler for sqlexception rollback;
/*事务开始*/
start TRANSACTION;
/*判断第一个SQL是否存在,一般情况下肯定是存在的*/
IF LENGTH(SQLCMD1)<>0 THEN
SET @s=SqlCMD1;
PREPARE stmt1 FROM @s; /*预备一个预置语句,并把第一个SQL命令赋值给此预置语句*/
EXECUTE stmt1; /*执行此预置语句*/
DEALLOCATE PREPARE stmt1; /*释放预置语句*/
END IF;/*判断第二个SQL是否存在,一般情况下肯定是存在的*/
IF LENGTH(SQLCMD2)<>0 THEN
SET @s=SqlCMD2;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF; /*判断第三个SQL是否存在,一般情况下肯定是存在的*/
SET @OrderID = (SELECT MAX(order_id) AS a FROM orderoperate);IF LENGTH(SQLCMD3)<>0 THEN
SET @s=SqlCMD3;
PREPARE stmt1 FROM @s;
EXECUTE stmt1 USING @OrderID,@OrderID ;
DEALLOCATE PREPARE stmt1;
END IF;
/*事务提交*/
COMMIT;
end;
VB调用代码如下Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter'cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpc"
cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpcpd"
cn.OpenWith cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "SP_FORSQL"
Set par = .CreateParameter("@SqlCMD1", adVarChar, adParamInput, 300, "INSERT INTO a VALUES(1,'按时')")
.Parameters.Append parSet par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, Text2.Text)
.Parameters.Append par
Set par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, "")
.Parameters.Append par
.Execute
'Set rs = cn.Execute("select * from A")
End With'cn.Execute ("INSERT INTO a VALUES(2,'撒上')")End Sub现在的问题是:
1:用存储过程来执行 INSERT INTO a VALUES(2,'撒上'),提示Data too long for column 'xxxx' at row y
2: CNN直接执行插入语句,正常MYSQl版本5.0,字符集gbk
begin
/*错误处理,遇到任何错误,自动回滚*/
#declare exit handler for sqlexception rollback;
/*事务开始*/
start TRANSACTION;
/*判断第一个SQL是否存在,一般情况下肯定是存在的*/
IF LENGTH(SQLCMD1)<>0 THEN
SET @s=SqlCMD1;
PREPARE stmt1 FROM @s; /*预备一个预置语句,并把第一个SQL命令赋值给此预置语句*/
EXECUTE stmt1; /*执行此预置语句*/
DEALLOCATE PREPARE stmt1; /*释放预置语句*/
END IF;/*判断第二个SQL是否存在,一般情况下肯定是存在的*/
IF LENGTH(SQLCMD2)<>0 THEN
SET @s=SqlCMD2;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF; /*判断第三个SQL是否存在,一般情况下肯定是存在的*/
SET @OrderID = (SELECT MAX(order_id) AS a FROM orderoperate);IF LENGTH(SQLCMD3)<>0 THEN
SET @s=SqlCMD3;
PREPARE stmt1 FROM @s;
EXECUTE stmt1 USING @OrderID,@OrderID ;
DEALLOCATE PREPARE stmt1;
END IF;
/*事务提交*/
COMMIT;
end;
VB调用代码如下Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter'cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpc"
cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpcpd"
cn.OpenWith cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "SP_FORSQL"
Set par = .CreateParameter("@SqlCMD1", adVarChar, adParamInput, 300, "INSERT INTO a VALUES(1,'按时')")
.Parameters.Append parSet par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, Text2.Text)
.Parameters.Append par
Set par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, "")
.Parameters.Append par
.Execute
'Set rs = cn.Execute("select * from A")
End With'cn.Execute ("INSERT INTO a VALUES(2,'撒上')")End Sub现在的问题是:
1:用存储过程来执行 INSERT INTO a VALUES(2,'撒上'),提示Data too long for column 'xxxx' at row y
2: CNN直接执行插入语句,正常MYSQl版本5.0,字符集gbk
call SP_FORSQL("INSERT INTO a VALUES(2,'撒上')")
是可以正常插入汉字的
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter'cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpc"
cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=localCPC;Initial Catalog=cpcpd"
cn.OpenWith cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "SP_FORSQL"
Set par = .CreateParameter("@SqlCMD1", adVarChar, adParamInput, 300, "INSERT INTO a VALUES(1,'按时')")
.Parameters.Append parSet par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, Text2.Text)
.Parameters.Append par
Set par = .CreateParameter("@SqlCMD2", adVarChar, adParamInput, 300, "")
.Parameters.Append par
.Execute
'Set rs = cn.Execute("select * from A")
End With'cn.Execute ("INSERT INTO a VALUES(2,'撒上')")End Sub
你标出来的红色部分,没有关系。。改了还是一样的错误。
你用我这个存储过程来执行一下SQL,看什么情况??刚才测试用JAVA调用,是没有问题的。。在JAVA代码里执行插入汉字是成功的。。
更改 SP 的参数类型。
b)CreateParameter 中换成 adVarWChar