How to get SQL code using sp_helptext?

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.
tjoAsked:
Who is Participating?
 
SpideyModConnect With a Mentor Commented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0
 
objectsCommented:
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
 
tjoAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
objectsCommented:
>  i can only get the number of lines

How r u getting the number of lines?
0
 
tjoAuthor Commented:
The line

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

is giving me the number.
0
 
objectsCommented:
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
 
tjoAuthor Commented:
The line

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

is giving me the number.
0
 
tjoAuthor Commented:
The line

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

is giving me the number.
0
 
objectsCommented:
Try getString(1)
0
 
tjoAuthor Commented:
The line

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

is giving me the number.
0
 
tjoAuthor Commented:
Sorry, i did do getString(1)... that was a typo
0
 
tjoAuthor Commented:
Sorry, i did do getString(1)... that was a typo
0
 
objectsCommented:
So what does sp_helptext return?
How many columns?
How many rows?
0
 
pellepCommented:
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
 
Venci75Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.