issues with SQL task and the PRINT statement in external .sql files

[Nathan Hattala]

Hello,

  So I have been banging my head against a wall it seems because I have
been trying to automate the database build and deployment process for
our software.  The goal I have is using ANT and the sql task, to execute
an existing .sql file that was developed and placed in SVN.  I am having
an issue that when I execute the scripts using the ant SQL task, it does
not give me the results for the sql statement PRINT.  It just seems to
ignore it and not give me the message in the output.  All I get is "0
rows affected".  This is frustrating because I can't seem to get ANT to
give me the message.  The print statements contain information that is
good for logging what the results of the script.  

 

The manual process has been to just run the scripts using enterprise
manager and copy the results into a file and just save the file.  If I
didn't need this to run on a unix machine and connecting to MS-SQL
databases I would be using something like sqlplus or the sqlcmd tool on
windows.  But I would like to use ANT to do all the execution of the
scripts.  Any suggestions?  Is there a better solution out there?

 

Some of my digging into the problem is with the print statement itself
and how ANT interprets the return codes.  So I think.  But I can't seem
to get that information out of ANT either.  This is what the MSDN site
says about the print statement....

 

"SQLSTATE is set to 01000, the native error is set to 0, and the error
message string is set to the character string specified in the PRINT
statement. The string is returned to the message handler callback
function in DB-Library applications."

http://msdn2.microsoft.com/en-us/library/ms190715.aspx

 

 

Nothing I have searched and searched for something related to this and
found nothing so far.  Can anybody help?

 

I had thoughts that it might be that I needed a custom logger using
log4j...but I don't understand that enough to know if that is the path
to take.

 

Here is an example of a simple script that will illustrate what I am
trying to do....

 

PRINT 'This is a message to signify that a select statement is going to
be run:'

SELECT * FROM SomeTable

 

Thanks,

-nathan

[Steve Loughran]

Nathan Hattala wrote:

> Hello,
>
>   So I have been banging my head against a wall it seems because I have
> been trying to automate the database build and deployment process for
> our software.  The goal I have is using ANT and the sql task, to execute
> an existing .sql file that was developed and placed in SVN.  I am having
> an issue that when I execute the scripts using the ant SQL task, it does
> not give me the results for the sql statement PRINT.  It just seems to
> ignore it and not give me the message in the output.  All I get is "0
> rows affected".  This is frustrating because I can't seem to get ANT to
> give me the message.  The print statements contain information that is
> good for logging what the results of the script.  
>
>  
>
> The manual process has been to just run the scripts using enterprise
> manager and copy the results into a file and just save the file.  If I
> didn't need this to run on a unix machine and connecting to MS-SQL
> databases I would be using something like sqlplus or the sqlcmd tool on
> windows.  But I would like to use ANT to do all the execution of the
> scripts.  Any suggestions?  Is there a better solution out there?
>

>  
>
> Here is an example of a simple script that will illustrate what I am
> trying to do....
>
>  
>
> PRINT 'This is a message to signify that a select statement is going to
> be run:'
>
> SELECT * FROM SomeTable
>

1. what happens in -verbose mode? in -debug?
2. is PRINT something that the JDBC driver itself handles?

I dont think ant deliberatly drops the print statements, I just suspect
it doesnt display any output because either it gets filtered out, or it
doesn't get the output. If you can find out what is happening, we can
see how to improve the task

...

looking at some code of my own that is derived from it, I call
ResultSet.getWarnings() to get the warnings from a result set, and print
these. but I dont think print statements are in warnings. How do you get
them from a ResultSet?

-steve

--
Steve Loughran                  http://www.1060.org/blogxter/publish/5
Author: Ant in Action           http://antbook.org/

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

[Nathan Hattala]

Hey Steve,
  Sorry took so long to respond.  After some testing with jdbc itself
and trying the print statement this is what I have found.

1. what happens in -verbose mode? in -debug?
        In both of these modes it just does the same thing.  It says
zero rows affected.  It will show the statement being executed...but
then it just doesn't do anything.

2. is PRINT something that the JDBC driver itself handles?

        I did some messing around with this and it seems that it does.

I created a direct connection to the database and then using the
execute() command while in debug mode in Eclipse I looked at the
variables and there were results in there.  However they come across as
warnings.  It appears that the warnings are in a linked list.  The
vendor code is set to zero.  Which is what the mssql site said it does
for that statement.

The thing is that it seems as though the warnings for the print
statements are not in the results set.  Meaning.  When I also setup a
variable to receive the ResultSet from the executed sql statement of
"PRINT 'this is the other test'; PRINT 'Will This one work'" the result
set was null.  However, the SQLWarning warn = s.getWarnings() from the
Statement object is where the print statements text ended up.


If it is possible to get ANT to get those warning messages then that
would make it more like what would be happening if you feed it a file
like Enterprise manager would display the results.

Hope that helps you understand what seems to be going on....

Thanks,
-nathan



-----Original Message-----
From: Steve Loughran [mailto:stevel@...]
Sent: Tuesday, April 01, 2008 7:46 AM
To: Ant Users List
Subject: Re: issues with SQL task and the PRINT statement in external
.sql files

Nathan Hattala wrote:
> Hello,
>
>   So I have been banging my head against a wall it seems because I
have
> been trying to automate the database build and deployment process for
> our software.  The goal I have is using ANT and the sql task, to
execute
> an existing .sql file that was developed and placed in SVN.  I am
having
> an issue that when I execute the scripts using the ant SQL task, it
does
> not give me the results for the sql statement PRINT.  It just seems to
> ignore it and not give me the message in the output.  All I get is "0
> rows affected".  This is frustrating because I can't seem to get ANT
to
> give me the message.  The print statements contain information that is
> good for logging what the results of the script.  
>
>  
>
> The manual process has been to just run the scripts using enterprise
> manager and copy the results into a file and just save the file.  If I
> didn't need this to run on a unix machine and connecting to MS-SQL
> databases I would be using something like sqlplus or the sqlcmd tool
on
> windows.  But I would like to use ANT to do all the execution of the
> scripts.  Any suggestions?  Is there a better solution out there?
>

>  
>
> Here is an example of a simple script that will illustrate what I am
> trying to do....
>
>  
>
> PRINT 'This is a message to signify that a select statement is going
to
> be run:'
>
> SELECT * FROM SomeTable
>

1. what happens in -verbose mode? in -debug?
2. is PRINT something that the JDBC driver itself handles?

I dont think ant deliberatly drops the print statements, I just suspect
it doesnt display any output because either it gets filtered out, or it
doesn't get the output. If you can find out what is happening, we can
see how to improve the task

...

looking at some code of my own that is derived from it, I call
ResultSet.getWarnings() to get the warnings from a result set, and print

these. but I dont think print statements are in warnings. How do you get

them from a ResultSet?

-steve

--
Steve Loughran                  http://www.1060.org/blogxter/publish/5
Author: Ant in Action           http://antbook.org/

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


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