You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ant.apache.org by Jack Woehr <ja...@purematrix.com> on 2004/04/05 23:00:00 UTC
Sql Task -- quote marks
I think I've isolated a problem in my Ant use of the Sql task. How do I pass a statement that contains
doublequotes?
select @querystring = "alter table " + @tmptablename + " drop constraint " + @constraintname
This very apparently is not parsed correctly by the Sql task. I'm rooting through SQLExec.java, but does
anyone have a quick answer? And is that answer somewhere in TFM but I have missed it? Thanks.
--
Jack J. Woehr # "[F]ar in the empty sky a solitary esophagus slept
http://www.well.com/~jax # upon motionless wing; everywhere brooded stillness,
http://www.softwoehr.com # serenity, and the peace of God." - Mark Twain
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by Jack Woehr <ja...@purematrix.com>.
Stefan Bodewig wrote:
> Try running the statement in a very simplistic JDBC setup via
> Statement#execute but without Ant.
Okay, I coded an example using the problematic line
select @querystring = \"alter table \" + @tmptablename + \" drop constraint \" + @constraintname "
and I get a jdbc error for the same SQL that works interpretively:
com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' drop constraint '.
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2796)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2126)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1683)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1666)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:430)
at SybStoredProc.doSQL(SybStoredProc.java:107)
at SybStoredProc.dropAddProcedure(SybStoredProc.java:125)
at SybStoredProc.do_it(SybStoredProc.java:152)
at SybStoredProc.main(SybStoredProc.java:181)
This is exactly what Ant was reporting. So Ant is innocent in my Sql task problem and the ball is in Sybase's court.
I've reported this as a driver bug.
--
Jack J. Woehr # "[F]ar in the empty sky a solitary esophagus slept
http://www.well.com/~jax # upon motionless wing; everywhere brooded stillness,
http://www.softwoehr.com # serenity, and the peace of God." - Mark Twain
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by "Jack J. Woehr" <ja...@purematrix.com>.
Stefan Bodewig wrote:
> Try running the statement in a very simplistic JDBC setup via
> Statement#execute but without Ant.
Right, that's the plan. I have code I wrote dating back to 1998 that will
easily handle this test. However, I'm only in that environment on Mondays
and Fridays which explains the slow evolution of this discussion!
Thanks again for your help, Stefan.
--
Jack J. Woehr # We have gone from the horse and buggy
Senior Consultant # to the moon rocket in one lifetime, but
Purematrix, Inc. # there has not been a corresponding moral
www.purematrix.com # growth in mankind. - Dwight D. Eisenhower
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by Stefan Bodewig <bo...@apache.org>.
On Tue, 06 Apr 2004, Jack J. Woehr <ja...@purematrix.com> wrote:
> The statement is valid, the statement works correctly in ISQL, yet
> the statement is failing in the Ant Sql task. Either it's Sybase or
> it's Sybase's JDBC driver or it's Ant.
Try running the statement in a very simplistic JDBC setup via
Statement#execute but without Ant.
Stefan
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by "Jack J. Woehr" <ja...@purematrix.com>.
Stefan Bodewig wrote:
> It will start with SQL: at some point and everything after that is the
> literal String passed to Statement#execute. Since the piece you've
> pasted does not start with "SQL:" on each line, Ant has joined several
> lines to build a single statement.
Okay, here it is:
[sql] SQL:
[sql]
[sql] CREATE PROCEDURE sp_drop_constraints AS
[sql] begin
[sql] declare @outstring varchar(200),@tmptablename varchar(200),@constraintname varchar(200),@querystring varchar(200),@i int,@rows int
[sql] create table #temp_constrain (
[sql] id numeric(7,0) identity,
[sql] tablename varchar(200),
[sql] constraintname varchar(200)
[sql] )
[sql] insert into #temp_constrain select so.name,so1.name from sysconstraints sc,sysobjects so,sysconstraints sc1,sysobjects so1 where so.id = sc.tableid and so1.id = sc1.constrid and sc.constrid = so1.id
[sql] select @i=1
[sql] select @rows = count(*) from #temp_constrain
[sql] while @i <= @rows
[sql] begin
[sql] select @tmptablename = tablename from #temp_constrain where id = @i
[sql] select @constraintname = constraintname from #temp_constrain where id = @i
[sql] select @querystring = "alter table " + @tmptablename + " drop constraint " + @constraintname
[sql] print @querystring
[sql] exec(@querystring)
[sql] select @i=@i+1
[sql] end
[sql] end
[sql] Failed to execute:
[sql]
....... repeats the statement ...
[sql] com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' drop constraint '.
[sql] 1 of 2 SQL statements executed successfully
[antcall] Exiting /usr/local/work/jax/jazmin/build/reloadDB.xml.
[ant] Exiting /usr/local/work/jax/jazmin/build/reloadDB.xml.
But like I said, all this doesn't prove to me exactly what's happening in the JDBC call. The statement is
valid, the statement works correctly in ISQL, yet the statement is failing in the Ant Sql task. Either it's Sybase
or it's Sybase's JDBC driver or it's Ant. I don't have enough evidence to exculpate any component in the chain yet.
Since I had seen complaints about the Sql task elsewhere on the Web I had hoped there was some information
in the community on this sort of problem, but I guess that I get to be the pioneer! :-)
--
Jack J. Woehr # We have gone from the horse and buggy
Senior Consultant # to the moon rocket in one lifetime, but
Purematrix, Inc. # there has not been a corresponding moral
www.purematrix.com # growth in mankind. - Dwight D. Eisenhower
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by Stefan Bodewig <bo...@apache.org>.
On Tue, 06 Apr 2004, Jack J. Woehr <ja...@purematrix.com> wrote:
> Stefan Bodewig wrote:
>
>> When you run Ant with -v, you should see what Ant is trying to pass
>> to your DBMS. Any quoting problem should become obvious here.
>
> Well, that's what I thought, but all it does is line-by-line echo
> the text, e.g.:
It will start with SQL: at some point and everything after that is the
literal String passed to Statement#execute. Since the piece you've
pasted does not start with "SQL:" on each line, Ant has joined several
lines to build a single statement.
Stefan
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by "Jack J. Woehr" <ja...@purematrix.com>.
Stefan Bodewig wrote:
> When you run Ant with -v, you should see what Ant is trying to pass to
> your DBMS. Any quoting problem should become obvious here.
Well, that's what I thought, but all it does is line-by-line echo the text, e.g.:
[sql] select @tmptablename = tablename from #temp_constrain where id = @i
[sql] select @constraintname = constraintname from #temp_constrain where id = @i
[sql] select @querystring = "alter table " + @tmptablename + " drop constraint " + @constraintname
.. which doesn't really tell me how successfully this is all being formatted in the JDBC call, right?
I am looking at the SQLExec.java code as time permits. I have Googled the Web and found several user sites
that indicate that peoples' experience with the Sql task in general has been rather ambivalent :-) Perhaps
my "voyage of exploration" will result in some useful code patches!
--
Jack J. Woehr # We have gone from the horse and buggy
Senior Consultant # to the moon rocket in one lifetime, but
Purematrix, Inc. # there has not been a corresponding moral
www.purematrix.com # growth in mankind. - Dwight D. Eisenhower
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by Stefan Bodewig <bo...@apache.org>.
On Tue, 06 Apr 2004, Jack J. Woehr <ja...@purematrix.com> wrote:
> The line in a stored procedure definition
>
> select @querystring = "alter table " + @tmptablename + " drop
> constraint " + @constraintname
>
> results in the error
>
> com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name '
> drop constraint '.
When you run Ant with -v, you should see what Ant is trying to pass to
your DBMS. Any quoting problem should become obvious here.
Stefan
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by "Jack J. Woehr" <ja...@purematrix.com>.
Stefan Bodewig wrote:
> On Mon, 05 Apr 2004, Jack Woehr <ja...@purematrix.com> wrote:
>
> > This very apparently is not parsed correctly by the Sql task.
>
> Why? What happens?
The line in a stored procedure definition
select @querystring = "alter table " + @tmptablename + " drop constraint " + @constraintname
results in the error
com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' drop constraint '.
This same line is accepted when piped directly into the Sybase ISQL interpreter.
It's possible it's a bug in the Sybase SQL driver which I can uncover with some coding but it really
smells like quote parsing problems. Thanks for inquiring into this matter, Stefan.
--
Jack J. Woehr # We have gone from the horse and buggy
Senior Consultant # to the moon rocket in one lifetime, but
Purematrix, Inc. # there has not been a corresponding moral
www.purematrix.com # growth in mankind. - Dwight D. Eisenhower
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by Stefan Bodewig <bo...@apache.org>.
On Mon, 05 Apr 2004, Jack Woehr <ja...@purematrix.com> wrote:
> This very apparently is not parsed correctly by the Sql task.
Why? What happens?
Stefan
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: Sql Task -- quote marks
Posted by "Jack J. Woehr" <ja...@purematrix.com>.
Bill Rich wrote:
> If your problem with the quote marks is in the ant script you can use "e;
No, it's in a separate .sql file ...
--
Jack J. Woehr # We have gone from the horse and buggy
Senior Consultant # to the moon rocket in one lifetime, but
Purematrix, Inc. # there has not been a corresponding moral
www.purematrix.com # growth in mankind. - Dwight D. Eisenhower
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
RE: Sql Task -- quote marks
Posted by Bill Rich <bi...@attglobal.net>.
If your problem with the quote marks is in the ant script you can use "e; in place of
each double-quote mark that is not an attribute delimiter. The ant script is an XML file
so the entity names should work for you.
HTH
Thanks. Bill
Bill Rich
Wilandra Consulting LLC
1325 Addiewell Place
San Jose, CA 95120-3905
phone: +1 408 268-2452
mobile: +1 408 410-9713
Santa Cruz: +1 831 464-9007
fax: +1 413 669-9716
billrich@wilandra.com or billrich@attglobal.net
http://www.wilandra.com
-----Original Message-----
From: jax@purematrix.com [mailto:jax@purematrix.com]
Sent: Monday, April 05, 2004 2:00 PM
To: Ant Users List
Subject: Sql Task -- quote marks
I think I've isolated a problem in my Ant use of the Sql task. How do I pass a statement
that contains
doublequotes?
select @querystring = "alter table " + @tmptablename + " drop constraint " +
@constraintname
This very apparently is not parsed correctly by the Sql task. I'm rooting through
SQLExec.java, but does
anyone have a quick answer? And is that answer somewhere in TFM but I have missed it?
Thanks.
--
Jack J. Woehr # "[F]ar in the empty sky a solitary esophagus slept
http://www.well.com/~jax # upon motionless wing; everywhere brooded stillness,
http://www.softwoehr.com # serenity, and the peace of God." - Mark Twain
---------------------------------------------------------------------
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