Solved

How to get SQL code using sp_helptext?

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now