Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sending a list of SQL Statements to the Server (out of the java program)

 
Kilia Verdon
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everybody

I just wrote a small tool which dynamicly produces a list of SQL statements with respect to a few often changing attributes:

(short abstract)

USE DatabaseName
GO

IF EXISTS(select name from master.dbo.sysdatabases WHERE name = 'Database') DROP DATABASE Database;

IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE column_name = 'Total' AND table_name = 'Table' )
BEGIN
ALTER TABLE tArticles_Welle3 ADD Total VARCHAR(6) NULL DEFAULT 'Total' WITH VALUES;
END

GO


and so on...

the whole query will then be stored in a .sql-File.

But instead of storing it in the sql File and then executing it manually in Microsoft SQL Server, I would like to send the statements to the server (they are stored in one String).

Statement stmt = connection.createStatement();
stmt.execute(methodWhichGivesTheListWithTheSQLStamementsBackAsAString());


The above code fragment does not work... e.g. the syntax GO is unknown and many more...
Is there a possibility to execute it out of a java tool or not?

Thanks a lot
Kilia

[ July 01, 2008: Message edited by: Kilia Verdon ]
[ July 01, 2008: Message edited by: Kilia Verdon ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Let me see if I can sum up what you're asking... you'd like to load a script file in JDBC. The short answer is you can do so with plugin tools like ant.

The longer answer is, doing so isn't really JDBC. The idea behind JDBC is you use java to construct/manage your SQL code, and in larger environments your entire database connection. In other words, why not skip the generating of SQL statements and execute the code directly? For example, JDBC can handle batch inserts intelligently with some massaging. You can create a single prepared statement from a single connection, and use it to perform multiple updates either as a single call or as a set of batched calls. Yes it's more work to use JDBC this way but what you get is code that is a lot more maintainable and will work out of the box for most databases right out of the box, instead of just one (unless your SQL queries are particularly database dependent).
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, and keep in mind most JDBC code (most I write anyway) does not issue ALTER TABLE commands or modify the database in anyway except as part of an installation/upgrade tool. I can think of no good reason code should be modifying the database structure based on regular user tasks.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic