Solved

How to get SQL code using sp_helptext?

Posted on 2002-04-21
15
2,085 Views
Last Modified: 2012-05-04
Hi, i'm trying to use sp_helptext to get the SQL code using a PreparedStatement and ResultSet, but the only thing i can get out is the number of line's it has.  

The current code looks something like this,

currentQueryBuffer.append("exec " + DatabaseName + "..sp_helptext " + StoredProcName);

PS = DBConn.prepareStatement(currentQueryBuffer.toString());
Results = PS.executeQuery();

while(Results.next())
  CurrentStoredProc.append(Results.getString(0));

Thanks.
0
Comment
Question by:tjo
15 Comments
 
LVL 92

Expert Comment

by:objects
ID: 6958664
is sp_helptext a stored procedure?
If so, that's not the syntax for calling an sp. Should be something like:

"{ call "+DatabaseName+"..sp_helptext (?) }"

And set the parameter using the relevant set method.
0
 

Author Comment

by:tjo
ID: 6958675
As far as i know, sp_helptext is a system procedure.

I tried your line using a setter, but it comes back with the same result, i can only get the number of lines... i have no idea how to get the actual text.

Thanks for the input though.
0
 
LVL 92

Expert Comment

by:objects
ID: 6958691
>  i can only get the number of lines

How r u getting the number of lines?
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:tjo
ID: 6958693
The line

CurrentStoredProc.append(Results.getString(0));

is giving me the number.
0
 
LVL 92

Expert Comment

by:objects
ID: 6958698
I think getString(0) returns the return value of the stored procedure. The first column of the result set is obtained by Results.getString(1).
0
 

Author Comment

by:tjo
ID: 6958721
The line

CurrentStoredProc.append(Results.getString(0));

is giving me the number.
0
 

Author Comment

by:tjo
ID: 6958722
The line

CurrentStoredProc.append(Results.getString(0));

is giving me the number.
0
 
LVL 92

Expert Comment

by:objects
ID: 6958735
Try getString(1)
0
 

Author Comment

by:tjo
ID: 6958805
The line

CurrentStoredProc.append(Results.getString(0));

is giving me the number.
0
 

Author Comment

by:tjo
ID: 6958820
Sorry, i did do getString(1)... that was a typo
0
 

Author Comment

by:tjo
ID: 6958843
Sorry, i did do getString(1)... that was a typo
0
 
LVL 92

Expert Comment

by:objects
ID: 6958865
So what does sp_helptext return?
How many columns?
How many rows?
0
 
LVL 4

Expert Comment

by:pellep
ID: 6959428
sp_helptext may return multiple resultsets, so try this:

currentQueryBuffer.append("exec " + DatabaseName + "..sp_helptext " + StoredProcName);

PS = DBConn.prepareStatement(currentQueryBuffer.toString());
Results = PS.executeQuery();

while (Results != null) {
while(Results.next())
 CurrentStoredProc.append(Results.getString(0));

Results = PS.getResultSet();
}
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7973679
This question has been abandoned. I will make a recommendation to the moderators on its resolution in a week or two. I appreciate any comments that would help me to make a recommendation.

In the absence of responses, I would recommend the Q to be PAQ'd and pts removed.

Silence = you don't care

Venci75
EE Cleanup Volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8737439
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
micro services spring boot application error 3 183
why cannot we forward request once the response is committed 2 42
use lov values 2 49
hibernate example for saving data 19 37
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

806 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