Solved

tsql scripts generate an error with JDBC if conditional statements present

Posted on 2004-08-23
18
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql update 2 38
T-SQL: Stored Procedure Syntax 3 34
Inserting LocalDB Table to SQL Server C# 3 19
T-SQL: problem comparing datetime 4 50
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 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