• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

Unable to execute Create function using JDBC  RSS feed

 
Greenhorn
Posts: 18
  • 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?
 
Bartender
Posts: 9564
189
  • 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: 18
  • 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.
 
Marshal
Posts: 5635
147
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: 18
  • 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
Marshal
Posts: 5635
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting the solution!
 
Run away! Run away! Here, take this tiny ad with you:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!