各位csdn友,大家好。。
最近做了个java调用存储过程
当使用带输入参数时候在sql查询分析器没有问题,但在java调用语法错误.
错误信息:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 13 行: '10' 附近有语法错误。
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at com.lzw.dao.Dao.findForListTonJi(Dao.java:446)java代码调用存储过程片段:
proc = conn.prepareCall("{call usp_ProName(?,?)}");
proc.setString(1, timeStat);
proc.setString(2, timeEnd);
ResultSet rs = (ResultSet) proc.executeQuery();
存储过程代码:
create PROC usp_ProName
@xsDateStat NVARCHAR(50),---备注,去除,无输入参数存储过程@xsDateEnd NVARCHAR(50)----备注,去除,无输入参数存储过程
As
DECLARE @col NVARCHAR(4000);
SET @col=N'';SELECT
@col=@col+N',SUM(CASE WHEN C.category='''+category+N''' THEN B.dj*B.sl ELSE 0 END)
AS ['+category+N']'
FROM [tb_spinfo] GROUP BY category;---department & total price
DECLARE @cmd NVARCHAR(4000);
SET @cmd=N'SELECT ISNULL(A.khname,''总计'') AS 部门'+@col+N',SUM(B.dj*B.sl)
AS 总计 FROM tb_sell_main AS A
JOIN tb_sell_detail AS B ON A.sellID=B.sellID
JOIN tb_spinfo AS C
ON B.spid=C.id
where A.xsdate>= ' +@xsDateStat + ' and A.xsdate< =' +@xsDateEnd+' GROUP BY A.khname WITH ROLLUP';EXEC sp_executesql @cmd
(存储过程在sql查询分析器没有问题,符合执行结果)
当我调用无输入参数的时候,java code
proc = conn.prepareCall("{call usp_ProNameNoP()}");
ResultSet rs = (ResultSet) proc.executeQuery()
没有任何问题,符合查询结果
请路过的指点
最近做了个java调用存储过程
当使用带输入参数时候在sql查询分析器没有问题,但在java调用语法错误.
错误信息:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 13 行: '10' 附近有语法错误。
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at com.lzw.dao.Dao.findForListTonJi(Dao.java:446)java代码调用存储过程片段:
proc = conn.prepareCall("{call usp_ProName(?,?)}");
proc.setString(1, timeStat);
proc.setString(2, timeEnd);
ResultSet rs = (ResultSet) proc.executeQuery();
存储过程代码:
create PROC usp_ProName
@xsDateStat NVARCHAR(50),---备注,去除,无输入参数存储过程@xsDateEnd NVARCHAR(50)----备注,去除,无输入参数存储过程
As
DECLARE @col NVARCHAR(4000);
SET @col=N'';SELECT
@col=@col+N',SUM(CASE WHEN C.category='''+category+N''' THEN B.dj*B.sl ELSE 0 END)
AS ['+category+N']'
FROM [tb_spinfo] GROUP BY category;---department & total price
DECLARE @cmd NVARCHAR(4000);
SET @cmd=N'SELECT ISNULL(A.khname,''总计'') AS 部门'+@col+N',SUM(B.dj*B.sl)
AS 总计 FROM tb_sell_main AS A
JOIN tb_sell_detail AS B ON A.sellID=B.sellID
JOIN tb_spinfo AS C
ON B.spid=C.id
where A.xsdate>= ' +@xsDateStat + ' and A.xsdate< =' +@xsDateEnd+' GROUP BY A.khname WITH ROLLUP';EXEC sp_executesql @cmd
(存储过程在sql查询分析器没有问题,符合执行结果)
当我调用无输入参数的时候,java code
proc = conn.prepareCall("{call usp_ProNameNoP()}");
ResultSet rs = (ResultSet) proc.executeQuery()
没有任何问题,符合查询结果
请路过的指点
解决方案 »
- 我在我的frame中放置了两个Splitpane,三个panel,每个panel中放了一个jtable,但是拖动不了,不知是为什么
- Help!高手们进来帮帮小菜鸟啊!
- 谁能帮我讲明白“抽象工厂”和“工厂方法”这两种模式到底有什么不同?
- 请问这里有做软件界面图片设计的吗?就想图标ICO之类的图片!我们需要希望通过这里能找到专业做这个的,聘请也行.
- 求助:关于在jtree中得到结点的名称
- java 操作数据库通用类
- 在子树中怎样插入图片?
- 初学者:::救救我,什么是Serializable
- ResultSet作为形参传送的问题,在线等待
- 如何在客户端判断下拉列表框!!!!!!!!!!!!!
- 如何将文件的绝对路径转化为URL
- Java中keyPressed的求助
JAVA的SQL驱动分2000和2005的
你看看是不是驱动COPY错了,或者是连接串写错了
usp_ProName(?,?) 带参数的存储过程。
usp_ProNameNoP() 无输入参数的存储过程,去除备注这块代码域,删除where 子句,就是无参数的存储过程
sql驱动是2000的
@xsDateStat DATETIME,---备注,去除,无输入参数存储过程
@xsDateEnd DATETIME----备注,去除,无输入参数存储过程
As
DECLARE @col NVARCHAR(4000);
SET @col=N''; SELECT
@col=@col+N',SUM(CASE WHEN C.category='''+category+N''' THEN B.dj*B.sl ELSE 0 END) AS ['+category+N']'
FROM [tb_spinfo] GROUP BY category; ---department & total price
DECLARE @cmd NVARCHAR(4000);
SET @cmd=N'SELECT ISNULL(A.khname,''总计'') AS 部门'+@col+N',SUM(B.dj*B.sl) AS 总计
FROM tb_sell_main AS A
JOIN tb_sell_detail AS B
ON A.sellID=B.sellID
JOIN tb_spinfo AS C
ON B.spid=C.id
WHERE A.xsdate BETWEEN @bgdate AND @eddate
GROUP BY A.khname
WITH ROLLUP'; EXEC sp_executesql @cmd,N'@bgdate DATETIME,@eddate DATETIME',@xsDateStat,@xsDateEnd
GO--在查询分析器里用EXEC去执行这个存储过程..
--不是CREATE PROC完了就代表这个存储过程没错.
--编译时出错跟运行时出错是两码事.EXEC dbo.usp_ProName '2008-10-12','2009-05-08'; --这里传的数据,根据你实际情况去传--如果这里没有错误了.那再在JAVA里去调用这个存储过程.
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: [tb_sell_main]
IF OBJECT_ID('[tb_sell_main]') IS NOT NULL DROP TABLE [tb_sell_main]
CREATE TABLE [tb_sell_main] (部门 VARCHAR(6),销售ID VARCHAR(13),销售日期 DATETIME) --这里销售日期要用DATETIME类型
INSERT INTO [tb_sell_main]
SELECT '总务科','XS20050104002','2009-05-05' UNION ALL
SELECT '总务科','XS20050120001','2009-04-12' UNION ALL
SELECT '总务科','XS20050220001','2009-02-15' UNION ALL
SELECT '总务科','XS20050309001','2009-02-12' UNION ALL
SELECT '总务科','XS20050309002','2008-10-30' UNION ALL
SELECT '药剂科','XS20050923001','2008-09-22' UNION ALL
SELECT '药剂科','XS20050923002','2009-03-12' UNION ALL
SELECT '药剂科','XS20050926001','2008-05-05'
--> 生成测试数据: [sell_detail]
IF OBJECT_ID('[sell_detail]') IS NOT NULL DROP TABLE [sell_detail]
CREATE TABLE [sell_detail] (spid VARCHAR(6),price NUMERIC(7,4),amount NUMERIC(2,1),sellId VARCHAR(13))
INSERT INTO [sell_detail]
SELECT 'sp1003',3.5000,1.0,'XS20050923001' UNION ALL
SELECT 'sp1001',789.0000,1.0,'XS20050923001' UNION ALL
SELECT 'sp1003',3.5000,2.0,'XS20050923002' UNION ALL
SELECT 'sp1005',10.0000,1.0,'XS20050926001' UNIoN ALL
SELECT 'sp1005',8.7000,2.0,'XS20051004001' UNION ALL
SELECT 'sp1002',890.0000,1.0,'XS20050104001' UNION ALL
SELECT 'sp1002',890.0000,1.0,'XS20050104002' UNION ALL
SELECT 'sp1002',890.0000,1.0,'XS20050120001' UNION ALL
SELECT 'sp1002',890.0000,1.0,'XS20050220001' UNION ALL
SELECT 'sp1003',3.5000,1.0,'XS20050923001'
--> 生成测试数据: [goods]
IF OBJECT_ID('[goods]') IS NOT NULL DROP TABLE [goods]
CREATE TABLE [goods] (spid VARCHAR(20),category VARCHAR(8))
INSERT INTO [goods]
SELECT 'sp1001','卫生材料' UNION ALL
SELECT 'sp1002','其它材料' union all
SELECT 'sp1003','卫生材料' UNION ALL
SELECT 'sp1005','其它材料' union all
SELECT 'sp1009','卫生材料' UNION ALL
SELECT 'sp10094','卫生材料' union all
select 'sp10089','卫生材料'GOCREATE PROC dbo.usp_ProName
@xsDateStat DATETIME,---备注,去除,无输入参数存储过程
@xsDateEnd DATETIME----备注,去除,无输入参数存储过程
As
DECLARE @col NVARCHAR(4000);
SET @col=N''; SELECT
@col=@col+N',SUM(CASE WHEN C.category='''+category+N''' THEN B.price*B.amount ELSE 0 END) AS ['+category+N']'
FROM [goods] GROUP BY category; ---department & total price
DECLARE @cmd NVARCHAR(4000);
SET @cmd=N'SELECT ISNULL(A.部门,''总计'') AS 部门'+@col+N',SUM(B.price*B.amount) AS 总计
FROM tb_sell_main AS A
JOIN sell_detail AS B
ON A.销售ID=B.sellid
JOIN [goods] AS C
ON B.spid=C.spid
WHERE A.销售日期 BETWEEN @bgdate AND @eddate
GROUP BY A.部门
WITH ROLLUP'; EXEC sp_executesql @cmd,N'@bgdate DATETIME,@eddate DATETIME',@xsDateStat,@xsDateEnd
GO--调用存储过程
EXEC dbo.usp_ProName '2008-10-30','2009-05-05'
/*
部门 其它材料 卫生材料 总计
------ --------------------------------------- --------------------------------------- ---------------------------------------
药剂科 0.00000 7.00000 7.00000
总务科 2670.00000 0.00000 2670.00000
总计 2670.00000 7.00000 2677.00000(3 行受影响)
*/--GO
--DROP TABLE tb_sell_main,sell_detail,[goods]
--DROP PROC dbo.usp_ProName
package sqldemo;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;public class Main {
public static void main(String[] args) throws Exception {
//我的是sql server 2005.这里的连接字符串,改为你2000的
//com.microsoft.jdbc.sqlserver.SQLServerDriver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;instanceName=LIANGCK;database=MyTest;","sa","[email protected]"); CallableStatement call = con.prepareCall("{call usp_ProName(?,?)}");
Calendar bgdate = Calendar.getInstance();
bgdate.set(2008,10,10);
Calendar eddate = Calendar.getInstance();
eddate.set(2009, 5,5);
call.setTimestamp(1, new Timestamp(bgdate.getTimeInMillis()));
call.setTimestamp(2, new Timestamp(eddate.getTimeInMillis())); if(call.execute()) {
ResultSet rs = call.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
for(int i = 1; i <= colCount; i++) {
System.out.print(rsmd.getColumnName(i)+"\t");
}
System.out.println();
while(rs.next()) {
for(int i = 1; i <= colCount; i++){
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
} rs.close();
} con.close(); con = null;
System.gc();
}
}/*
run:
部门 其它材料 卫生材料 总计
药剂科 0.00000 7.00000 7.00000
总务科 2670.00000 0.00000 2670.00000
总计 2670.00000 7.00000 2677.00000
成功生成(总时间:0 秒)*/