Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

tsql scripts generate an error with JDBC if conditional statements present

Posted on 2004-08-23
18
Medium Priority
?
397 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 

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 1500 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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

972 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