Solved

How to get SQL code using sp_helptext?

Posted on 2002-04-21
15
2,089 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tomcat: Unable to run tomcat service. 2 29
jmss example java 2 33
jmeter usage 4 36
junit as external jar or library 7 39
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

749 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