tsql scripts generate an error with JDBC if conditional statements present

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.
vijay2320Asked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
"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
 
vijay2320Author Commented:
This is really urgent. thanks in advance
0
 
arbertCommented:
Do the execute statements execute properly in Query Analyzer?  Do you have SET NOCOUNT ON as the first statement in your batch?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
vijay2320Author Commented:
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
 
arbertCommented:
Ya, it definately sounds like a batching problem of some sort...
0
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
btw it still doesnt work for create proc
0
 
arbertCommented:
"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
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
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
 
arbertCommented:
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
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
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
 
arbertCommented:
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
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
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
 
vijay2320Author Commented:
Thanks, Brett. I have it all working now. I'm sure this question will pop up in other ppl's searches.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.