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 thanks jack :roll:
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 ]
jack nick
Ranch Hand
Joined: Jan 11, 2001
Posts: 96
posted
0
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. Thanks Jack
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"
0
