Win a copy of Bad Programming Practices 101 (e-book) this week in the Beginning Java forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Unable to execute Create function using JDBC  RSS feed

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

When i try to execute the sql using JDBC. iam getting exception.


Below is the code:

if exists (select * from [dbo].[sysobjects] where id = object_id(N'[dbo].[FixTimeBand]') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function [dbo].[FixTimeBand];
CREATE FUNCTION [dbo].[FixTimeBand] (@SysDate as datetime,@MatureDate as datetime) 
RETURNS smallint  AS 
BEGIN
declare @Month smallint

if @MatureDate > @SysDate
if day(@MatureDate) - day(@SysDate) > -1
set @Month = datediff(month,@SysDate,@MatureDate) + 1
else
set @Month = datediff(month,@SysDate,@MatureDate)

else
set @Month = datediff(month,@SysDate,@MatureDate)

return coalesce(@Month,0)
END;


SQL state [S0001]; error code [111]; 'CREATE FUNCTION' must be the first statement in a query batch.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 'CREATE FUNCTION' must be the first statement in a query batch.


If i add GO before function also iam getting another exception.

SQL state [S0001]; error code [102]; Incorrect syntax near 'GO'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'GO'.



How to resolve this?
 
Saloon Keeper
Posts: 8975
168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apparently GO is not a valid keyword in your SQL flavor. You might have to execute your CREATE FUNCTION statement separately.
 
vinoth sathi
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:Apparently GO is not a valid keyword in your SQL flavor. You might have to execute your CREATE FUNCTION statement separately.




Thanks for response.


May i know i how to achieve this through jdbc connectivity?.

Otherwords i try to use SQL agent job and created the script and access from java. but not succeeded . getting below exception:


com.microsoft.sqlserver.jdbc.SQLServerException: Database 'msdn' does not exist. Make sure that the name is entered correctly.

exec msdn.dbo.sp_start_job N'test exe';


Please advice.
 
Sheriff
Posts: 4964
136
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We need some additional details from you.  What is the full stack trace? (the entire error message).  What line of Java code is the error occurring at?  Please post the surrounding code (like the method the line is in).
 
vinoth sathi
Greenhorn
Posts: 16
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:We need some additional details from you.  What is the full stack trace? (the entire error message).  What line of Java code is the error occurring at?  Please post the surrounding code (like the method the line is in).



Hi,

I have create the  SQL Agent job and called from java. working fine now.
 
Knute Snortum
Sheriff
Posts: 4964
136
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting the solution!
 
incandescent light gives off an efficient form of heat. You must be THIS smart to ride this ride. Tiny ad:
Why should you try IntelliJ IDEA ?
https://coderanch.com/wiki/696337/IntelliJ-IDEA
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!