MSSQL and jdbc
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   baban_kakde
Posted On:   Tuesday, July 10, 2007 01:19 AM

I have following .sql file and I have no idea how to run this file using java.Please give me solution. PRINT '' PRINT '##################################################################' PRINT 'Begin Script: DropTables ' + convert(varchar,current_timestamp) PRINT ' DB server: ' + @@servername PRINT '' BEGIN DECLARE @user_table VARCHAR(80) DECLARE drop_tables CURSOR FOR SELECT name FROM sysobjects WHERE xtype='U' and name not like 'cmi%' and name <>'ERR_CLK' and name <>'TMP_CLK' ORDER BY name --user table PRINT ' Checking for any views before dropping tables.' SELECT s2.n   More>>
			
I have following .sql file and I have no idea how to run this file using java.Please give me solution.


			
PRINT ''
PRINT '##################################################################'
PRINT 'Begin Script: DropTables ' + convert(varchar,current_timestamp)
PRINT ' DB server: ' + @@servername
PRINT ''

BEGIN

DECLARE @user_table VARCHAR(80)


DECLARE
drop_tables CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U' and name not like 'cmi%' and name <>'ERR_CLK' and name <>'TMP_CLK'
ORDER BY name --user table

PRINT ' Checking for any views before dropping tables.'

SELECT s2.name
FROM sysobjects s2, syscomments s1
WHERE (s1.id=s2.id) AND (s2.xtype ='V')
AND (s2.name NOT LIKE 'sys%')
ORDER BY s2.name

IF (@@ROWCOUNT > 0) BEGIN
PRINT ''
PRINT ' ######################################################################################'
PRINT ' ### WARNING -> You must drop all user defined views before dropping tables. ###'
PRINT ' ### Tables will not be dropped. ###'
PRINT ' ######################################################################################'
PRINT ''

END
ELSE BEGIN

PRINT ''
PRINT 'No Views found, OK to drop tables.'
PRINT ''

OPEN drop_tables
FETCH NEXT FROM drop_tables INTO @user_table

WHILE(@@FETCH_STATUS=0) BEGIN
PRINT (' ## ## DROP TABLE ' + @user_table)
EXEC ('DROP TABLE ' + @user_table)
FETCH NEXT FROM drop_tables INTO @user_table
END

CLOSE drop_tables
DEALLOCATE drop_tables
PRINT ''
END

END

PRINT ''
PRINT 'End Script: DropTables ' + convert(varchar,current_timestamp)
PRINT '##################################################################'

GO
   <<Less

Re: MSSQL and jdbc

Posted By:   Tim_Frith  
Posted On:   Tuesday, July 10, 2007 02:31 AM

First off, your script uses the sysobjects table which is a system table and is not guaranteed to have a consistent structure from one version of MSSQL to the next.


You should rewrite your queries to use an information_schema veiw instead. Use information_schema.tables instead of sysobjects. That way your script likely won't break if you upgrade your db.


As for running it in Java, why don't you just make it into a stored procedure. Look at the "Calling a Stored Procedure from JDBC" section in the following for details on how to call a stored procedure from Java:


http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html

About | Sitemap | Contact