aspose file tools*
The moose likes JDBC and the fly likes Sending a list of SQL Statements to the Server (out of the java program) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sending a list of SQL Statements to the Server (out of the java program)" Watch "Sending a list of SQL Statements to the Server (out of the java program)" New topic
Author

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

Kilia Verdon
Greenhorn

Joined: Jan 20, 2008
Posts: 13
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

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).


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sending a list of SQL Statements to the Server (out of the java program)
 
Similar Threads
WITHOUT STORED PROCEDUREs
IBM Exam 157 - Questions and Answers (Sample Exam)
JDBC and Stored Procedure
SQL Server support and migrating from HSQLDB to MS SQL
Data Base DUMP through Java