Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2101
  • Last Modified:

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.
0
tjo
Asked:
tjo
1 Solution
 
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
 
objectsCommented:
>  i can only get the number of lines

How r u getting the number of lines?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now