File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes is this possible Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "is this possible" Watch "is this possible" New topic

is this possible

jack nick
Ranch Hand

Joined: Jan 11, 2001
Posts: 96
hi there,
this question comes with having minimum experience with databases and jdbc.
well can one have a stored procedure written in say SQL server be retrieved from it and stored in a text file
also would it be possible to programitacally write a stored procedure from a text string written in the file back to the database.
all this using JDBC
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15302

DatabaseMetaData has a few procedures for extracting Stored Procedures from Databases.
getProcedures() - Returns a ResultSet Object containing a list
That is probably the one you want. You could then extract the procedure from the ResultSet and write that to a Text File. Beware that not all Drivers will support all features of JDBC including this one. The JDBC-ODBC Driver I believe does, but if you are using something other than that, you will need to check its documentation to see if that Driver supports that method.
If you want to create a Stored Procedure in a Database from a Text File, all you would need to do is create the SQL Statement to do so and put that in a String and send it to a Database using the Statement object. Just like INSERT, UPDATE, etc.
[ November 20, 2002: Message edited by: Gregg Bolinger ]

GenRocket - Experts at Building Test Data
jack nick
Ranch Hand

Joined: Jan 11, 2001
Posts: 96
Thanks Gregg,
This clears my mind of one of the possibilities, it does get out the meta information about the procecuder like the procedure name and some other information regarding the procedure.
But what if I require the actual contents of the procedure.
The reason being, is to create a transfer utility to transfer all the contents of the database, the data as well as the stored procedures from one database to the next I would like to do this. At the moment it both databases would be SQL Server so compatibility issues between database is not on my mind at the moment.
Would be great if one could do this someway or the other.
Christopher Farnham

Joined: Sep 18, 2002
Posts: 12
Is there a reason why you're trying to do this in Java?
You can do what you want in a matter of seconds using the SQL Server Enterprise Manager. Or you can use the Bulk Copy Program (BCP) to backup and restore data.

Christopher Farnham<br />Boston, MA<p>"Perfect is the Enemy of Good"
I agree. Here's the link:
subject: is this possible
jQuery in Action, 3rd edition