ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Anderson, Rob H - VSCM" <Anderson....@vectorscm.com>
Subject RE: sql task issue
Date Fri, 28 Feb 2003 17:45:30 GMT
Why not use "CREATE OR REPLACE ..." ? Also, you may want to check out my
recent work with the sql task (see attached email thread). I encourage you
to vote for Bug#10719 that is addressing the issue of loading packages,
procedures, triggers without losing the formatting. The most recent
attachement to this bug is the sql task with my updates.
http://nagoya.apache.org/bugzilla/show_bug.cgi?id=10719

With my updated sql task you should be able to use the following usage to
load your script...

        <target name="lib:sql">
                <sql driver="${db.driver}"
                        print="true"
                        delimiter="/"
                        delimitertype="row"
                        keepformat="true"
                        url="${db.url}"
                        userid="${db.userid}"
                        password="${db.password}"
                        src="${db.sql.src}"
                />
        </target>

This usage will require you to edit your script by removing the ";" from the
end of your statements and adding a line with a single "/" to delimit
statements. So your file would look like...

	IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[UpdateCourseIsArchived]') AND 
          OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [UpdateCourseIsArchived]
/
	
	CREATE PROCEDURE UpdateCourseIsArchived
		@courseID int,
		@isArchived bit
	AS
	....
/

-----Original Message-----
From: Hammer, Paul [mailto:PHammer@classroom.com]
Sent: Thursday, February 27, 2003 6:31 PM
To: 'Ant Users List'
Subject: RE: sql task issue



Hi Rob,

Things haven't gone as well as hoped on my large test of plsql.
The test first successfully ran multiple .sql files, and
then attempted to run an sql file that began with:

	IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[UpdateCourseIsArchived]') AND 
          OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [UpdateCourseIsArchived];
	
	CREATE PROCEDURE UpdateCourseIsArchived
		@courseID int,
		@isArchived bit
	AS
	....

This resulted in the error message:

	BUILD FAILED
	file:D:/ATG/Dynamo5.6.1/Database/iK/dbupdate.xml:117:
java.sql.SQLException: [EN
	G--PHAMMER]'CREATE PROCEDURE' must be the first statement in a query
batch.

We've had this error before, and solved the problem by placing the semicolon
";"
on the end of the DROP PROCEDURE... statement.

Do you know of any way we can have the DROP PROC and CREATE PROC in the 
same .sql file?

Paul


-----Original Message-----
From: Anderson, Rob H - VSCM [mailto:Anderson.Rob@vectorscm.com]
Sent: Thursday, February 27, 2003 12:51 PM
To: 'Ant Users List'
Subject: RE: sql task issue


Great. Glad to hear it. I use it to load about 300 packages, triggers, and
procedures and it's been working great for me. Let me know how it goes this
afternoon. 

-Rob Anderson

-----Original Message-----
From: Hammer, Paul [mailto:PHammer@classroom.com]
Sent: Thursday, February 27, 2003 11:57 AM
To: 'Ant Users List'
Subject: RE: sql task issue



Rob A. ,

This new plsql task has passed by first small test.  
This afternoon, I'll be running a much larger test.

Thanks!

Paul

-----Original Message-----
From: Anderson, Rob H - VSCM [mailto:Anderson.Rob@vectorscm.com]
Sent: Wednesday, February 26, 2003 4:51 PM
To: 'Ant Users List'
Subject: RE: sql task issue


It looks like the attachement did not make it. I sent an email from my
hotmail account, with the attachement, to the list. It hasn't bounced yet,
so I guess it is on the way. The latest attachement on the enhancement
request can be downloaded also...

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=16945

-Rob A

-----Original Message-----
From: Hammer, Paul [mailto:PHammer@classroom.com]
Sent: Wednesday, February 26, 2003 4:37 PM
To: 'Ant Users List'
Subject: RE: sql task issue



Rob,

I don't understand.  The attached file only contains the following:

	
---------------------------------------------------------------------
	To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
	For additional commands, e-mail: user-help@ant.apache.org

Is this what you meant to send to me?

Paul


-----Original Message-----
From: Anderson, Rob H - VSCM [mailto:Anderson.Rob@vectorscm.com]
Sent: Wednesday, February 26, 2003 3:22 PM
To: 'Ant Users List'
Subject: RE: sql task issue


Try the attached task. I wrote it with the intention of loading plsql
packages, procedures, and triggers into oracle but I see no reason why you
could not use it for your purposes also. Let me know if it works.



-----Original Message-----
From: Hammer, Paul [mailto:PHammer@classroom.com]
Sent: Wednesday, February 26, 2003 3:02 PM
To: 'Ant Users List'
Subject: sql task issue



I'm new to this mailing list, and glad to be here.

My company is running ant 1.5.1 on Windows NT/2K.  We are
using the sql task to declare stored procedures to 
MS SQL Server 7.0.  These stored procedures work fine. 
The issue is that when the stored procedures are viewed
through the Enterprise Manager intrface, all the comments
and end-of-line chars have been stripped out.  This
makes the result unreadable.

has anyone encoutered this before?  Might there be a
work around for this?

Paul J. Hammer


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org


Mime
View raw message