这样的一个PROCEDURE:
有返回值的CREATE PROCEDURE orders_insert
--This is the insert function for table orders
@ordno int, @cid char(3), @aid char(3), @pid char(3), @qty int
AS--Check the validation of the parameter cid, aid, pid and ordno
IF (SELECT COUNT(*) FROM customers c
WHERE c.cid = @cid) = 0
RETURN(1)IF (SELECT COUNT(*) FROM agents a
WHERE a.aid = @aid) = 0
RETURN(2)IF (SELECT COUNT(*) FROM products p
WHERE p.pid = @pid) = 0
RETURN(3)IF (SELECT COUNT(*) FROM orders o
WHERE o.ordno = @ordno) <> 0
RETURN(4)--Check the storage of the required product to see if it's enough
IF (SELECT quantity FROM products p
WHERE p.pid = @pid) < @qty
RETURN(5)--Update the quantity of the product in the table
UPDATE products SET quantity=quantity-@qty where pid=@pidSELECT p.price * @qty * (1 - c.discnt*0.01) FROM products p, customers c
WHERE p.pid = @pid and c.cid=@cid/*INSERT INTO orders VALUES
@ordno, 'jan', @cid, @aid, @pid, @qty,
(SELECT p.price * @qty * (1 - c.discnt*0.01) FROM products p, customers c
WHERE p.pid = @pid and c.cid=@cid) */
GO我在程序中这样调用:CallableStatement cmt=null;
cmt= con.prepareCall
("{ ? = call dbo.orders_insert(ordno,cid,aid,pid,qty) }");
cmt.registerOutParameter(1,Types.INTEGER); //注册返回值
cmt.executeUpdate();
result=cmt.getInt(1);为什么运行抛下列异常啊!:
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]对于造型说明无效的字符值
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
at InsertFrame.action(InsertFrame.java:285)
at java.awt.Component.handleEvent(Unknown Source)
at InsertFrame.handleEvent(InsertFrame.java:410)
at java.awt.Window.postEvent(Unknown Source)
at java.awt.Component.postEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
有返回值的CREATE PROCEDURE orders_insert
--This is the insert function for table orders
@ordno int, @cid char(3), @aid char(3), @pid char(3), @qty int
AS--Check the validation of the parameter cid, aid, pid and ordno
IF (SELECT COUNT(*) FROM customers c
WHERE c.cid = @cid) = 0
RETURN(1)IF (SELECT COUNT(*) FROM agents a
WHERE a.aid = @aid) = 0
RETURN(2)IF (SELECT COUNT(*) FROM products p
WHERE p.pid = @pid) = 0
RETURN(3)IF (SELECT COUNT(*) FROM orders o
WHERE o.ordno = @ordno) <> 0
RETURN(4)--Check the storage of the required product to see if it's enough
IF (SELECT quantity FROM products p
WHERE p.pid = @pid) < @qty
RETURN(5)--Update the quantity of the product in the table
UPDATE products SET quantity=quantity-@qty where pid=@pidSELECT p.price * @qty * (1 - c.discnt*0.01) FROM products p, customers c
WHERE p.pid = @pid and c.cid=@cid/*INSERT INTO orders VALUES
@ordno, 'jan', @cid, @aid, @pid, @qty,
(SELECT p.price * @qty * (1 - c.discnt*0.01) FROM products p, customers c
WHERE p.pid = @pid and c.cid=@cid) */
GO我在程序中这样调用:CallableStatement cmt=null;
cmt= con.prepareCall
("{ ? = call dbo.orders_insert(ordno,cid,aid,pid,qty) }");
cmt.registerOutParameter(1,Types.INTEGER); //注册返回值
cmt.executeUpdate();
result=cmt.getInt(1);为什么运行抛下列异常啊!:
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]对于造型说明无效的字符值
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
at InsertFrame.action(InsertFrame.java:285)
at java.awt.Component.handleEvent(Unknown Source)
at InsertFrame.handleEvent(InsertFrame.java:410)
at java.awt.Window.postEvent(Unknown Source)
at java.awt.Component.postEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
EXECUTE @result = orders_insert 1233,'c002','a02','p01',110000PRINT @result这样在查询分析器里运行正确啊!
("{ ? = call dbo.orders_insert(?,?,?,?) }");
cmt.registerOutParameter(1,Types.INTEGER); //注册返回值
cmt.setInt(2,ordno);//这里用setString还是setInt,setFloat应该按你传的类型来选择
cmt.setString(3,cid);
cmt.setString(4,aid);
cmt.setString(5,pid);
cmt.setInt(6,qty);
cmt.executeUpdate();
result=cmt.getInt(1);
运行结果也对(该插入的插入了)可是一定会抛这么个异常是怎么回事?:
289是cmt.executeUpdate();这一行
java.sql.SQLException: No row count was produced
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
at InsertFrame.action(InsertFrame.java:289)
at java.awt.Component.handleEvent(Unknown Source)
at InsertFrame.handleEvent(InsertFrame.java:414)
at java.awt.Window.postEvent(Unknown Source)
at java.awt.Component.postEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
No row count was produced是个什么错误啊
CREATE PROCEDURE orders_insert
--This is the insert function for table orders
@ordno int, @cid char(3), @aid char(3), @pid char(3), @qty int
AS
set nocount on--Check the validation of the parameter cid, aid, pid and ordno
IF (SELECT COUNT(*) FROM customers c
WHERE c.cid = @cid) = 0
RETURN(1)........