File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Database Metadata Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database Metadata" Watch "Database Metadata" New topic
Author

Database Metadata

Arjun Shastry
Ranch Hand

Joined: Mar 13, 2003
Posts: 1871
DatabaseMetaData interface is used to get the info about Database.There is a funtion getProcedures(arg1,arg2,arg3) to get list of Stored Procedures.But its Result Set gives procedure Names,types .
How to retrieve the actual stored procedure's content?
Thanks in advance


MH
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
If it can't be done via DatabaseMetadata then, if you really need to do it, you'll need to use database-specific code. For example, in MS SQL Server and Sybase there is a system stored procedure called sp_helptext that does this (after a fashion). You might be better off selecting from the syscomments system table yourself, however. I think Oracle has views on the underlying system tables, usersource being one of the ones for stored procedure code. Note that the source can be encrypted so you may not be able to read it. It can also be deleted independently of the stored procedure, so there's no guarantee of finding any source for a given SP.

Hope that helps.

Jules
Arjun Shastry
Ranch Hand

Joined: Mar 13, 2003
Posts: 1871
Thanks.
So basically we can't retrieve any stored procedure/SQL jobs residing on databse using JDBC(unless we use the method mentioned by you).?
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Well, I can't see anything in the standard API (java.sql.DatabaseMetaData), so I would say no. Your database driver may provide specific functionality to do that, but I doubt it. Why do you want to retrieve the stored procedure source through Java anyway? Are you writing some sort of database coding UI?

Jules
Arjun Shastry
Ranch Hand

Joined: Mar 13, 2003
Posts: 1871
Mainly to see browse stored procs/sql jobs on different database servers.I wanted to see those in browser.I will use JSP/Servlet later to complete that.
Jyothi Lature
Ranch Hand

Joined: Oct 31, 2001
Posts: 60
Hello Arjun,

You can print the source code of a Stored procedure in Oracle using the statement:

select text from user_source where name='PROCEDURE NAME';

So, i guess, you could use the same statement in Java and execute it using JDBC n should work!


Jyothi<br />Sun Certified Java Programmer<br />Brainbench Certified for Javascript
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database Metadata
 
Similar Threads
What does this code mean?
XML response from stored Procedure
cookie
Static Fields and shared memory
java io