Solved

How to get SQL code using sp_helptext?

Posted on 2002-04-21
15
2,091 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
[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
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
Independent Software Vendors: 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!

 

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

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

691 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