ant sql call stored procedure

[Chaohua Wang <cw...@incomm.com>]

Hi folks, 


I am using ant sql to call a stored procedure of Ms Sql server

<sql
    driver="${jdbcdriver}"
    url="${dburl}/BillPay"
    userid="${userid}"
    password="${password}"
	src="selectCount.sql"
	classpathref="webtest.path.id"
>
	</sql> 	


I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. Then I use Execute--> File to generate a file called selectCount.sql.  But when I am using ant sql to call the file selectCount.sql that is located with the build file. 

I always got this exception: 
      [sql] Executing file: C:\workspace\billPay\tests\selectCount.sql
      [sql] Failed to execute:   ■U S E   [ B i l l P a y ]  G O  D E C L A R E          @ r e t u r n _ v a l u e   i n t  E X E C      @ r e t u r n _ v a l u e   =   [ d b o ] . [ u s p S e l e c t
 C o u n t ]  S E L E C T        ' R e t u r n   V a l u e '   =   @ r e t u r n _ v a l u e  G O
      [sql] java.sql.SQLException: Incorrect syntax near 'E'.

'E' is the third character of USE. I am not sure the reason why? Please help.

cwang



This is selectCount.sql file 
---------------------------------
USE [BillPay]
GO
DECLARE	@return_value int
EXEC	@return_value = [dbo].[uspSelectCount]
SELECT	'Return Value' = @return_value
GO
--------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

RE: ant sql call stored procedure

["Anderson, Rob (Global Trade)" <Ro...@nike.com>]

When executing sql using Ant, you are using a jdbc connection, not the sql client that is available from the database vendor. Some of the features that are database specific are not available when using a jdbc connection.

If you find that the Ant sql task does not support your vendor specific sql statements, you can use the exec task to use the run the vendor supplied client.

-Rob Anderson

-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com] 
Sent: Thursday, March 13, 2008 10:15 AM
To: Ant Users List
Subject: ant sql call stored procedure 

Hi folks, 


I am using ant sql to call a stored procedure of Ms Sql server

<sql
    driver="${jdbcdriver}"
    url="${dburl}/BillPay"
    userid="${userid}"
    password="${password}"
	src="selectCount.sql"
	classpathref="webtest.path.id"
>
	</sql> 	


I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. Then I use Execute--> File to generate a file called selectCount.sql.  But when I am using ant sql to call the file selectCount.sql that is located with the build file. 

I always got this exception: 
      [sql] Executing file: C:\workspace\billPay\tests\selectCount.sql
      [sql] Failed to execute:   ■U S E   [ B i l l P a y ]  G O  D E C L A R E          @ r e t u r n _ v a l u e   i n t  E X E C      @ r e t u r n _ v a l u e   =   [ d b o ] . [ u s p S e l e c t
 C o u n t ]  S E L E C T        ' R e t u r n   V a l u e '   =   @ r e t u r n _ v a l u e  G O
      [sql] java.sql.SQLException: Incorrect syntax near 'E'.

'E' is the third character of USE. I am not sure the reason why? Please help.

cwang



This is selectCount.sql file 
---------------------------------
USE [BillPay]
GO
DECLARE	@return_value int
EXEC	@return_value = [dbo].[uspSelectCount]
SELECT	'Return Value' = @return_value
GO
--------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org