Solved

How to get SQL code using sp_helptext?

Posted on 2002-04-21
15
2,087 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Weekend adv creator 3 52
java stored proc example 9 44
servlet web applications   metadata-complete="true" or false 3 112
Problem to Alipay 10 43
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
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…
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:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

860 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