• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

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: 9239
177
  • 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: 5124
138
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: 5124
138
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting the solution!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!