java sql参数过滤函数,java.sql.SQLException: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效...

java.sql.SQLException: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:636)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)

at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:780)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)

at org.eline.orm.hibernate.SimpleHibernateDao.queryByProc(SimpleHibernateDao.java:199)

at org.eline.service.dis.WarinManager.saveWarin(WarinManager.java:1002)

at org.eline.service.dis.WarinManager$$FastClassByCGLIB$$9547e738.invoke()

at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)

at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)

at org.eline.service.dis.WarinManager$$EnhancerByCGLIB$$31e15384.saveWarin()

at dis.WarinManagerTest.testSaveWarin(WarinManagerTest.java:42)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at junit.framework.TestCase.runTest(TestCase.java:164)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runManaged(AbstractJUnit38SpringContextTests.java:307)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.access$000(AbstractJUnit38SpringContextTests.java:94)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests$1.run(AbstractJUnit38SpringContextTests.java:193)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runTest(AbstractJUnit38SpringContextTests.java:270)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runTestTimed(AbstractJUnit38SpringContextTests.java:228)

at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runBare(AbstractJUnit38SpringContextTests.java:191)

at junit.framework.TestResult$1.protect(TestResult.java:106)

at junit.framework.TestResult.runProtected(TestResult.java:124)

at junit.framework.TestResult.run(TestResult.java:109)

at junit.framework.TestCase.run(TestCase.java:120)

at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

stored procedured code:

USE [hb_lottery]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--DROP PROCEDURE dis_insertWari

ALTER PROCEDURE [dbo].[dis_insertWari]

--@ProductId_Array varChar(800),

--@ModuleId int,

@delivery varchar(50), --@usid bigint,

@postationid varchar(50),    --@stid varchar(50)

@sn varchar(500)

AS

DECLARE @PointerPrev int

DECLARE @PointerCurr int

DECLARE @TSn varchar(50)

DECLARE @usid bigint,@stid varchar(50),@waid varchar(50),@dgid varchar(50)

select @usid = usid from usr_users where username = @delivery  --'shoulijun'

select @stid = stid from sta_station where @postationid = @postationid   --5400010240-- postationid --5480011708

--select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @sn --3500950151246

Set @PointerPrev=1

set @PointerCurr=1

begin transaction

Set NoCount ON

--delete  from ProductListSpecial where ModuleId=@ModuleId

--first

Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)

set @TSn=SUBSTRING(@sn,@PointerPrev,@PointerCurr-@PointerPrev)

--Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)

select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246

insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)

--second

SET @PointerPrev = @PointerCurr

while (@PointerPrev+1 < LEN(@sn))

Begin

Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)

if(@PointerCurr>0)

Begin

set @TSn=SUBSTRING(@sn,@PointerPrev+1,@PointerCurr-@PointerPrev-1)

--Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)

select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246

insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)

SET @PointerPrev = @PointerCurr

End

else

Break

End

--third

set @TSn=SUBSTRING(@sn,@PointerPrev+1,LEN(@sn)-@PointerPrev)

--Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)

select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246

insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)

Set NoCount OFF

if @@error=0

begin

commit transaction

end

else

begin

rollback transaction

end

select 'ok'

把几处Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)改为Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)即可。 PS:哎！copy，paste疏忽大意，没改过来。