Solved

tsql scripts generate an error with JDBC if conditional statements present

Posted on 2004-08-23
18
386 Views
Last Modified: 2012-06-21
I'm trying to execute the following code

PreparedStatement ps = con.prepareStatement(sqlBlock);
done = ps.execute();

where done is of type boolean and sqlBlock contains the tsql script. As long as the tsql script contains only set/select/declare statements, done returns true.
But when the tsql script has any if/while/create/print statements, done is false and there's no resultset returned even if there are select statements in the script.
0
Comment
Question by:vijay2320
  • 12
  • 6
18 Comments
 

Author Comment

by:vijay2320
ID: 11875607
This is really urgent. thanks in advance
0
 
LVL 34

Expert Comment

by:arbert
ID: 11876086
Do the execute statements execute properly in Query Analyzer?  Do you have SET NOCOUNT ON as the first statement in your batch?
0
 

Author Comment

by:vijay2320
ID: 11876195
they execute properly in query analyzer. i dont have a SET NOCOUNT ON.
i think the reason is because my first result is an update or does not return a resultset...
let me get back to u with a little checking of my own.
thanks
0
 
LVL 34

Expert Comment

by:arbert
ID: 11876238
Ya, it definately sounds like a batching problem of some sort...
0
 

Author Comment

by:vijay2320
ID: 11876704
i bypassed all results returned which are not resultsets and only considered the ones which are...
and it worked. i think the problem with "if/while/print" statements is that they are considered different statements
if @i=20 select @i

if @i=20 is considered a different statement
select @i is considered different

so i think it generates an update or something when executed so done, the boolean, was false.
correct me if i m wrong.
0
 

Author Comment

by:vijay2320
ID: 11876707
btw it still doesnt work for create proc
0
 
LVL 34

Expert Comment

by:arbert
ID: 11876772
"btw it still doesnt work for create proc"

What do you mean?  It doesn't work when you put the statements in a proc and execute them?'


Post the entire batch you're trying to execute.

Brett
0
 

Author Comment

by:vijay2320
ID: 11876885
actually if there are any CREATE statements in the script, it throws an error.

CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end

throws an error "The query is invalid. (java.sql.SQLException: Incorrect syntax near the keyword 'FUNCTION'.) this is the one i'm trying right now because my boss wanted a script which i hadnt modified.

Thanks
0
 

Author Comment

by:vijay2320
ID: 11877125
okay i figured this out too but still problems linger around me :o(. its because prepareStatement adds a begin and an end or something like that to the query.
so instead of a prepared statement , i used a Statement in java and it executes fine when i'm only creating the function. but what i want to do is something like this

create FUNCTION whichContinent21(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end
go
declare @ii varchar(30)
select @ii=dbo.whichContinent21('Finland')
select @ii

i want all of it to be executed in one-go... is that possible?
i hope i'm not troubling you too much. thank you.



0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:vijay2320
ID: 11877133
it'd be really great if i could declare a local function.. a function local to the script which is not created in the server. is it possible? thats what i really really really want :)
0
 
LVL 34

Expert Comment

by:arbert
ID: 11885715
Nope, that's not possible.  Why are you creating a function on the fly and then trying to use it?  What's the end result you're looking for---if you simply want a local function, why don't you code it on the java side and not even bring SQL into the mix?
0
 

Author Comment

by:vijay2320
ID: 11886830
the reason for creating a function on the fly is so that tsql scripts as a whole can be executed. And like my boss says these tsql scripts could contain functions that only the admin guy wants to use and nobody could see it. like it is used, and then it vanishes like temp variables.

I read somewhere that temp functions are not possible. is it true?
i wouldnt be defining these functions. the tsql scripts which are to be executed will not be mine. so i dont know what kind of functions they might want to have.
even if temp/local functions are not possible in tsql. how do i create a function and then use it in the same script without having a "go" in between. jdbc doesnt recognize the "go" because apparently its not a tsql keyword. is there a get around?

PS : since this is taking a long time, how do i increase the number of points? i tried before but i couldnt. thanks, bert
0
 

Author Comment

by:vijay2320
ID: 11888278
sorry, brett.. i got ur name wrong. even if local functions are not possible. is there a way to get around "go" in jdbc? let me know how to up the points to 750 since you've been helping me for a long time. thank you.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11888839
hey, we're not here for points (besides, 500 is the max per question lol).


This really doesn't sound like a good way of doing things to me.  You're not going to be able to do what you want within a single batch.  I think if you really want to do this, you'll have to use a dynamic statement to create the function and then use it after the creation.  

Why can't you just create the function and make sure only that certain user has rights to use it?  Remember, even if it's a "temp function", it can still be seen on the server when it's executed in the batch.
0
 

Author Comment

by:vijay2320
ID: 11894538
i've given up on the local functions part, then... but it'd be really nice if i could get around "go" in jdbc. if i cant have everything done in a batch for sure, then i have another idea but not sure if it'd have any unwanted side-effects.
if i have a script and before executing it through jdbc, if i parse it for all the "go" s in between and then execute these batches separately, will it be a sane thing to do? it looks like not many people have tried executing tsql scripts using jdbc. why is that so?
thanks for the useful comments, bret
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 11895041
"why is that so?"

Not sure why they haven't.....

You could always build a proc that you pass the "create" statement too and call that.  The function should then be available to the next step in your batch.

I guess the other thing I would ask, What happens if you DID manage to create your proc in the batch and then your JDBC connection got disconnected?  This function would still exist in the database.
0
 

Author Comment

by:vijay2320
ID: 11895535
thats an error case...will deal with that later on. i think its still acceptable. but if i use a temp procedure which definitely is possible?
so this is my final understanding.

temp functions are not possible.
closest thing is temp procedure.

i can split a script into different batches by parsing through the script looking for "go" delimiters and then executing them. this way. i think it works but are there any possible pitfalls for this method?

0
 

Author Comment

by:vijay2320
ID: 11917683
Thanks, Brett. I have it all working now. I'm sure this question will pop up in other ppl's searches.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now