You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ant.apache.org by Chaohua Wang <cw...@incomm.com> on 2008/03/12 21:12:37 UTC
about ant sql connection being pulled out , into a property file
Folks,
I am using ant sql,
<sql
driver="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://10.3.1.201/Test"
userid="settle"
password="settle"
print="yes"
classpathref="webtest.path.id"
output="${tmp.dir}/dbModification.txt"
>
Because I have a few of ant sql statements, could you tell me how to put
above part into a property file. So there is no duplicate code.
In this part like
<sql refer=@connecionproperty > something like this.
Thank you
cwang
RE: a stored procedure example for an sql src.
Posted by "Anderson, Rob (Global Trade)" <Ro...@nike.com>.
The documentation has examples.
http://ant.apache.org/manual/CoreTasks/sql.html
-Rob A
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Thursday, March 13, 2008 2:49 PM
To: Ant Users List
Subject: a stored procedure example for an sql src.
Hi folks,
could somebody give me a stored procedure example to let me call
<sql
src=select.sql
>
Very simple one select * from table.
I am stuck in stored procedure.
I just want to execute select.sql. I have created a stored procedure in
my database.
Thank you.
cwang
---------------------------------------------------------------------
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
a stored procedure example for an sql src.
Posted by Chaohua Wang <cw...@incomm.com>.
Hi folks,
could somebody give me a stored procedure example to let me call
<sql
src=select.sql
>
Very simple one select * from table.
I am stuck in stored procedure.
I just want to execute select.sql. I have created a stored procedure in
my database.
Thank you.
cwang
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: ant sql call stored procedure
Posted by Steve Loughran <st...@apache.org>.
Chaohua Wang wrote:
> Hi folks,
>
>
> I am using ant sql to call a stored procedure of Ms Sql server
>
> <sql
> driver="${jdbcdriver}"
> url="${dburl}/BillPay"
> userid="${userid}"
> password="${password}"
> src=" uspSelectCount.sql "
> classpathref="webtest.path.id"
> </sql>
>
>
> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>
> I always got this exception:
>
> test:
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ?U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>
>
> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
I dont like the look of all the spaces in the message ... it hints that
the SQL task isnt loading in the text in the right encoding, which is
bound to cause trouble. Try playing with the encoding attribute of the
task to get it and the text consistent.
--
Steve Loughran http://www.1060.org/blogxter/publish/5
Author: Ant in Action http://antbook.org/
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: 答复: how to load a bunch data from a txt file, and ant sql call these data
Posted by Kevin Jackson <fo...@gmail.com>.
Hi,
> I have a ant sql statement
>
>
> <target name="insertData">
> <sql driver="${jdbcdriver}" url="${dburl}/BillPay"
> userid="${userid}" password="${password}"
> print="yes" onerror="continue"
> output="${tmp.dir}/dbModification.txt">
> <transaction> INSERT INTO Payment (RefNumber,
> VAN, Amount, OptCode, VendorID,
> PriorRefNumber, TransactionTS,
> IsProcessed) VALUES
>
> ('6569998989','2131213','1131',2031,232,'7898', { fn NOW() },1);
> </transaction>
> <transaction> INSERT INTO Payment (RefNumber,
> VAN, Amount, OptCode, VendorID,
> PriorRefNumber, TransactionTS,
> IsProcessed) VALUES
>
> ('9565689232323','21313213','1131',2031,232,'7898', { fn NOW() },1);
> </transaction>
> </sql>
>
> Here there are 2 sql statement to insert. But I have more than 100 data
> put in txt file.
>
> Like this
> '1569998989','2131213','1131',2031,232,'7898', { fn NOW() },
> '2569998989','2131213','1131',2031,232,'7898', { fn NOW() },
> '3569998989','2131213','1131',2031,232,'7898', { fn NOW() },
>
>
> Could you tell me How can load these data from a txt file. And using
> loop to insert these data into database. Which means dynamically.
> So I don't need to put more thant 100 sql statement in the ant build
> file.
Ant doesn't usually deal with 'loops' in the usual sense (imperative
programming style). In ant it's more normal to define a dataset and
perform the same operation for the entire set - not sure if I
explained that properly (morning coffee hasn't kicked in)
Here are a couple of suggestions:
- checkout dbunit it has good ant integration and makes this kind of
db interaction easy
- read the ant manual http://ant.apache.org/manual/CoreTasks/sql.html
<sql
driver="org.database.jdbcDriver"
url="jdbc:database-url"
userid="sa"
password="pass"
src="data.sql"
/>
Please be aware that we are all busy, and this is not an uncommon
thing to want to do so there's a good chance that someone has an
example or has asked the question before.
In the future if you could try:
- Check the ant manual (see if there's a task that matches what you want to do)
- Read the mailing list archives (google search for them or try
http://marc.info/)
- Finally ask on the users list
You may feel that it was rude to ask you to read the standard 'How to
ask for help page', but really there's a good reason that page exists
and especially with open source software where everyone is a
volunteer.
Thanks,
Kev
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
RE: how to load a bunch data from a txt file, and ant sql call these data
Posted by "Anderson, Rob (Global Trade)" <Ro...@nike.com>.
Good luck with that.
-Rob A
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Wednesday, March 19, 2008 6:15 PM
To: Ant Users List; Ant Users List
Subject: ??: how to load a bunch data from a txt file, and ant sql call these data
I am not interested in your link. I don't ask people for doing homework for me.
I am asking for hint and some helpful links.
Pleas understand my question first.
-----Original Message-----
From: Anderson, Rob (Global Trade) [mailto:Rob.Anderson@nike.com]
Sent: 2008-3-19 (星期三) 18:56
To: Ant Users List
Subject: RE: how to load a bunch data from a txt file, and ant sql call these data
I'm not trying to be a jerk, but you would probably benefit from reading
the following...
http://catb.org/‾esr/faqs/smart-questions.html
I don't think anybody here is interested in doing your work for you. But
if you have specific questions about things you are having difficulty
with, we are happy to help if we can.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Wednesday, March 19, 2008 3:52 PM
To: Ant Users List
Cc: floess@mindspring.com
Subject: how to load a bunch data from a txt file, and ant sql call
these data
Hi Folks,
I have a ant sql statement
<target name="insertData">
<sql driver="${jdbcdriver}" url="${dburl}/BillPay"
userid="${userid}" password="${password}"
print="yes" onerror="continue"
output="${tmp.dir}/dbModification.txt">
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('6569998989','2131213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('9565689232323','21313213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
</sql>
Here there are 2 sql statement to insert. But I have more than 100 data
put in txt file.
Like this
'1569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'2569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'3569998989','2131213','1131',2031,232,'7898', { fn NOW() },
Could you tell me How can load these data from a txt file. And using
loop to insert these data into database. Which means dynamically.
So I don't need to put more thant 100 sql statement in the ant build
file.
Thank you very much.
Cwang
---------------------------------------------------------------------
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
答复: how to load a bunch data from a txt file, and ant sql call these data
Posted by Chaohua Wang <cw...@incomm.com>.
I am not interested in your link. I don't ask people for doing homework for me.
I am asking for hint and some helpful links.
Pleas understand my question first.
-----Original Message-----
From: Anderson, Rob (Global Trade) [mailto:Rob.Anderson@nike.com]
Sent: 2008-3-19 (星期三) 18:56
To: Ant Users List
Subject: RE: how to load a bunch data from a txt file, and ant sql call these data
I'm not trying to be a jerk, but you would probably benefit from reading
the following...
http://catb.org/~esr/faqs/smart-questions.html
I don't think anybody here is interested in doing your work for you. But
if you have specific questions about things you are having difficulty
with, we are happy to help if we can.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Wednesday, March 19, 2008 3:52 PM
To: Ant Users List
Cc: floess@mindspring.com
Subject: how to load a bunch data from a txt file, and ant sql call
these data
Hi Folks,
I have a ant sql statement
<target name="insertData">
<sql driver="${jdbcdriver}" url="${dburl}/BillPay"
userid="${userid}" password="${password}"
print="yes" onerror="continue"
output="${tmp.dir}/dbModification.txt">
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('6569998989','2131213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('9565689232323','21313213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
</sql>
Here there are 2 sql statement to insert. But I have more than 100 data
put in txt file.
Like this
'1569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'2569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'3569998989','2131213','1131',2031,232,'7898', { fn NOW() },
Could you tell me How can load these data from a txt file. And using
loop to insert these data into database. Which means dynamically.
So I don't need to put more thant 100 sql statement in the ant build
file.
Thank you very much.
Cwang
---------------------------------------------------------------------
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
RE: how to load a bunch data from a txt file, and ant sql call these data
Posted by "Anderson, Rob (Global Trade)" <Ro...@nike.com>.
I'm not trying to be a jerk, but you would probably benefit from reading
the following...
http://catb.org/~esr/faqs/smart-questions.html
I don't think anybody here is interested in doing your work for you. But
if you have specific questions about things you are having difficulty
with, we are happy to help if we can.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Wednesday, March 19, 2008 3:52 PM
To: Ant Users List
Cc: floess@mindspring.com
Subject: how to load a bunch data from a txt file, and ant sql call
these data
Hi Folks,
I have a ant sql statement
<target name="insertData">
<sql driver="${jdbcdriver}" url="${dburl}/BillPay"
userid="${userid}" password="${password}"
print="yes" onerror="continue"
output="${tmp.dir}/dbModification.txt">
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('6569998989','2131213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('9565689232323','21313213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
</sql>
Here there are 2 sql statement to insert. But I have more than 100 data
put in txt file.
Like this
'1569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'2569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'3569998989','2131213','1131',2031,232,'7898', { fn NOW() },
Could you tell me How can load these data from a txt file. And using
loop to insert these data into database. Which means dynamically.
So I don't need to put more thant 100 sql statement in the ant build
file.
Thank you very much.
Cwang
---------------------------------------------------------------------
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
how to load a bunch data from a txt file, and ant sql call these data
Posted by Chaohua Wang <cw...@incomm.com>.
Hi Folks,
I have a ant sql statement
<target name="insertData">
<sql driver="${jdbcdriver}" url="${dburl}/BillPay"
userid="${userid}" password="${password}"
print="yes" onerror="continue"
output="${tmp.dir}/dbModification.txt">
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('6569998989','2131213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
<transaction> INSERT INTO Payment (RefNumber,
VAN, Amount, OptCode, VendorID,
PriorRefNumber, TransactionTS,
IsProcessed) VALUES
('9565689232323','21313213','1131',2031,232,'7898', { fn NOW() },1);
</transaction>
</sql>
Here there are 2 sql statement to insert. But I have more than 100 data
put in txt file.
Like this
'1569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'2569998989','2131213','1131',2031,232,'7898', { fn NOW() },
'3569998989','2131213','1131',2031,232,'7898', { fn NOW() },
Could you tell me How can load these data from a txt file. And using
loop to insert these data into database. Which means dynamically.
So I don't need to put more thant 100 sql statement in the ant build
file.
Thank you very much.
Cwang
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
Re: ant sql call stored procedure
Posted by "Scot P. Floess" <fl...@mindspring.com>.
Its not a vendor issue. I was trying to help out...and it appears it
was an editor issue...
Anderson, Rob (Global Trade) wrote:
> It looks like the sql you are trying to execute is vendor specific. Keep in mind that the Ant sql task uses a jdbc connection, and cannot accommodate vendor specific statements that may work fine in the vendor supplied client.
>
> -Rob Anderson
>
> -----Original Message-----
> From: Chaohua Wang [mailto:cwang@incomm.com]
> Sent: Monday, March 17, 2008 9:23 AM
> To: Ant Users List; olivier.gies@bull.net
> Subject: RE: ant sql call stored procedure
>
>
> Hi,
>
> Thank you for your suggestion. But I used UTF-8,
> It changed a little bit, still can not get through.
>
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ?USE [BillPay] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSelectCount] AS BEGIN SET NOCOUNT ON; Select count(*) from dbo.Payments_S
> ng; End
> [sql] java.sql.SQLException: Incorrect syntax near '?'.
>
>
>
> Actually, I have no "?" in my sql file.
>
>
>
> This my sql file:
>
>
> USE [BillPay]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[uspSelectCount]
> AS
> BEGIN
> SET NOCOUNT ON;
> Select count(*)
> from dbo.Payments_Staging;
>
> End
>
>
>
>
> -----Original Message-----
> From: Olivier Gies [mailto:olivier.gies@bull.net]
> Sent: Sunday, March 16, 2008 8:02 PM
> To: Ant Users List
> Subject: Re: ant sql call stored procedure
>
> Hi,
>
> It looks like the SQL file is encoded in some version of Unicode (UTF-8
> or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
> task is loading it in ISO-8599 by default.
>
> You should verify your source file format (using 'file' command on *nix
> systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
> <sql> task's "encoding" attribute accordingly (see
> http://ant.apache.org/manual/CoreTasks/sql.html)
>
> BR,
> Olivier
>
> -------- Original Message --------
> Subject: ant sql call stored procedure
> From: Chaohua Wang <cw...@incomm.com>
> To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
> Date: 14/03/2008 06:31
>
>
>> Hi folks,
>>
>>
>> I am using ant sql to call a stored procedure of Ms Sql server
>>
>> <sql
>> driver="${jdbcdriver}"
>> url="${dburl}/BillPay"
>> userid="${userid}"
>> password="${password}"
>> src=" uspSelectCount.sql "
>> classpathref="webtest.path.id"
>> </sql>
>>
>>
>> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>>
>> I always got this exception:
>>
>> test:
>> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
>> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
>> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
>> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
>> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
>> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
>> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
>> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
>> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>>
>>
>> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>>
>> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>>
>> cwang
>>
>>
>>
>> This is uspSelectCount.sql file
>> --------------------------------------------
>> USE [BillPay]
>> GO
>> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> -- =============================================
>> -- Author: <Author,,Name>
>> -- Create date: <Create Date,,>
>> -- Description: <Description,,>
>> -- =============================================
>> CREATE PROCEDURE [dbo].[uspSelectCount]
>>
>> AS
>> BEGIN
>> -- SET NOCOUNT ON added to prevent extra result sets from
>> -- interfering with SELECT statements.
>> SET NOCOUNT ON;
>>
>> Select count(*)
>> from dbo.Payments_Staging
>>
>> End
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
>> For additional commands, e-mail: user-help@ant.apache.org
>>
>>
>>
>
>
--
Scot P. Floess
27 Lake Royale
Louisburg, NC 27549
252-478-8087 (Home)
919-754-4592 (Work)
Chief Architect JPlate http://sourceforge.net/projects/jplate
Chief Architect JavaPIM http://sourceforge.net/projects/javapim
Architect Keros http://sourceforge.net/projects/keros
RE: ant sql call stored procedure
Posted by Chaohua Wang <cw...@incomm.com>.
Thank you very much for your help.
-----Original Message-----
From: Anderson, Rob (Global Trade) [mailto:Rob.Anderson@nike.com]
Sent: Wednesday, March 19, 2008 12:30 PM
To: Ant Users List
Subject: RE: ant sql call stored procedure
It looks like the sql you are trying to execute is vendor specific. Keep in mind that the Ant sql task uses a jdbc connection, and cannot accommodate vendor specific statements that may work fine in the vendor supplied client.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Monday, March 17, 2008 9:23 AM
To: Ant Users List; olivier.gies@bull.net
Subject: RE: ant sql call stored procedure
Hi,
Thank you for your suggestion. But I used UTF-8,
It changed a little bit, still can not get through.
[sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
[sql] Failed to execute: ?USE [BillPay] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSelectCount] AS BEGIN SET NOCOUNT ON; Select count(*) from dbo.Payments_S
ng; End
[sql] java.sql.SQLException: Incorrect syntax near '?'.
Actually, I have no "?" in my sql file.
This my sql file:
USE [BillPay]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspSelectCount]
AS
BEGIN
SET NOCOUNT ON;
Select count(*)
from dbo.Payments_Staging;
End
-----Original Message-----
From: Olivier Gies [mailto:olivier.gies@bull.net]
Sent: Sunday, March 16, 2008 8:02 PM
To: Ant Users List
Subject: Re: ant sql call stored procedure
Hi,
It looks like the SQL file is encoded in some version of Unicode (UTF-8
or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
task is loading it in ISO-8599 by default.
You should verify your source file format (using 'file' command on *nix
systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
<sql> task's "encoding" attribute accordingly (see
http://ant.apache.org/manual/CoreTasks/sql.html)
BR,
Olivier
-------- Original Message --------
Subject: ant sql call stored procedure
From: Chaohua Wang <cw...@incomm.com>
To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
Date: 14/03/2008 06:31
> Hi folks,
>
>
> I am using ant sql to call a stored procedure of Ms Sql server
>
> <sql
> driver="${jdbcdriver}"
> url="${dburl}/BillPay"
> userid="${userid}"
> password="${password}"
> src=" uspSelectCount.sql "
> classpathref="webtest.path.id"
> </sql>
>
>
> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>
> I always got this exception:
>
> test:
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>
>
> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>
> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>
> cwang
>
>
>
> This is uspSelectCount.sql file
> --------------------------------------------
> USE [BillPay]
> GO
> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> CREATE PROCEDURE [dbo].[uspSelectCount]
>
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> Select count(*)
> from dbo.Payments_Staging
>
> End
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>
>
--
*Olivier Gies*
*Delivery Manager
Customs & Tax Software Engineering Center
Bull, Architect of an Open World ^TM
Phone: +86 (10) 65978001 - Ext 555 *
*www.bull.com <http://www.bull.com/>*
*This e-mail contains material that is confidential for the sole use of
the intended recipient. Any review, reliance or distribution by others
or forwarding without express permission is strictly prohibited. If you
are not the intended recipient, please contact the sender and delete all
copies.*
---------------------------------------------------------------------
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
RE: ant sql call stored procedure
Posted by "Anderson, Rob (Global Trade)" <Ro...@nike.com>.
It looks like the sql you are trying to execute is vendor specific. Keep in mind that the Ant sql task uses a jdbc connection, and cannot accommodate vendor specific statements that may work fine in the vendor supplied client.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Monday, March 17, 2008 9:23 AM
To: Ant Users List; olivier.gies@bull.net
Subject: RE: ant sql call stored procedure
Hi,
Thank you for your suggestion. But I used UTF-8,
It changed a little bit, still can not get through.
[sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
[sql] Failed to execute: ?USE [BillPay] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSelectCount] AS BEGIN SET NOCOUNT ON; Select count(*) from dbo.Payments_S
ng; End
[sql] java.sql.SQLException: Incorrect syntax near '?'.
Actually, I have no "?" in my sql file.
This my sql file:
USE [BillPay]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspSelectCount]
AS
BEGIN
SET NOCOUNT ON;
Select count(*)
from dbo.Payments_Staging;
End
-----Original Message-----
From: Olivier Gies [mailto:olivier.gies@bull.net]
Sent: Sunday, March 16, 2008 8:02 PM
To: Ant Users List
Subject: Re: ant sql call stored procedure
Hi,
It looks like the SQL file is encoded in some version of Unicode (UTF-8
or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
task is loading it in ISO-8599 by default.
You should verify your source file format (using 'file' command on *nix
systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
<sql> task's "encoding" attribute accordingly (see
http://ant.apache.org/manual/CoreTasks/sql.html)
BR,
Olivier
-------- Original Message --------
Subject: ant sql call stored procedure
From: Chaohua Wang <cw...@incomm.com>
To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
Date: 14/03/2008 06:31
> Hi folks,
>
>
> I am using ant sql to call a stored procedure of Ms Sql server
>
> <sql
> driver="${jdbcdriver}"
> url="${dburl}/BillPay"
> userid="${userid}"
> password="${password}"
> src=" uspSelectCount.sql "
> classpathref="webtest.path.id"
> </sql>
>
>
> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>
> I always got this exception:
>
> test:
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>
>
> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>
> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>
> cwang
>
>
>
> This is uspSelectCount.sql file
> --------------------------------------------
> USE [BillPay]
> GO
> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> CREATE PROCEDURE [dbo].[uspSelectCount]
>
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> Select count(*)
> from dbo.Payments_Staging
>
> End
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>
>
--
*Olivier Gies*
*Delivery Manager
Customs & Tax Software Engineering Center
Bull, Architect of an Open World ^TM
Phone: +86 (10) 65978001 - Ext 555 *
*www.bull.com <http://www.bull.com/>*
*This e-mail contains material that is confidential for the sole use of
the intended recipient. Any review, reliance or distribution by others
or forwarding without express permission is strictly prohibited. If you
are not the intended recipient, please contact the sender and delete all
copies.*
---------------------------------------------------------------------
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
Re: ant sql call stored procedure
Posted by "Scot P. Floess" <fl...@mindspring.com>.
You might check out: http://www.thescripts.com/forum/thread143248.html
I looks like "GO" is a batch delimiter?
Chaohua Wang wrote:
> Hi,
>
> Thank you for your suggestion. But I used UTF-8,
> It changed a little bit, still can not get through.
>
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ?USE [BillPay] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSelectCount] AS BEGIN SET NOCOUNT ON; Select count(*) from dbo.Payments_S
> ng; End
> [sql] java.sql.SQLException: Incorrect syntax near '?'.
>
>
>
> Actually, I have no "?" in my sql file.
>
>
>
> This my sql file:
>
>
> USE [BillPay]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[uspSelectCount]
> AS
> BEGIN
> SET NOCOUNT ON;
> Select count(*)
> from dbo.Payments_Staging;
>
> End
>
>
>
>
> -----Original Message-----
> From: Olivier Gies [mailto:olivier.gies@bull.net]
> Sent: Sunday, March 16, 2008 8:02 PM
> To: Ant Users List
> Subject: Re: ant sql call stored procedure
>
> Hi,
>
> It looks like the SQL file is encoded in some version of Unicode (UTF-8
> or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
> task is loading it in ISO-8599 by default.
>
> You should verify your source file format (using 'file' command on *nix
> systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
> <sql> task's "encoding" attribute accordingly (see
> http://ant.apache.org/manual/CoreTasks/sql.html)
>
> BR,
> Olivier
>
> -------- Original Message --------
> Subject: ant sql call stored procedure
> From: Chaohua Wang <cw...@incomm.com>
> To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
> Date: 14/03/2008 06:31
>
>
>> Hi folks,
>>
>>
>> I am using ant sql to call a stored procedure of Ms Sql server
>>
>> <sql
>> driver="${jdbcdriver}"
>> url="${dburl}/BillPay"
>> userid="${userid}"
>> password="${password}"
>> src=" uspSelectCount.sql "
>> classpathref="webtest.path.id"
>> </sql>
>>
>>
>> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>>
>> I always got this exception:
>>
>> test:
>> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
>> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
>> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
>> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
>> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
>> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
>> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
>> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
>> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>>
>>
>> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>>
>> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>>
>> cwang
>>
>>
>>
>> This is uspSelectCount.sql file
>> --------------------------------------------
>> USE [BillPay]
>> GO
>> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> -- =============================================
>> -- Author: <Author,,Name>
>> -- Create date: <Create Date,,>
>> -- Description: <Description,,>
>> -- =============================================
>> CREATE PROCEDURE [dbo].[uspSelectCount]
>>
>> AS
>> BEGIN
>> -- SET NOCOUNT ON added to prevent extra result sets from
>> -- interfering with SELECT statements.
>> SET NOCOUNT ON;
>>
>> Select count(*)
>> from dbo.Payments_Staging
>>
>> End
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
>> For additional commands, e-mail: user-help@ant.apache.org
>>
>>
>>
>
>
--
Scot P. Floess
27 Lake Royale
Louisburg, NC 27549
252-478-8087 (Home)
919-754-4592 (Work)
Chief Architect JPlate http://sourceforge.net/projects/jplate
Chief Architect JavaPIM http://sourceforge.net/projects/javapim
Architect Keros http://sourceforge.net/projects/keros
RE: ant sql call stored procedure
Posted by Chaohua Wang <cw...@incomm.com>.
Hi,
Thank you for your suggestion. But I used UTF-8,
It changed a little bit, still can not get through.
[sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
[sql] Failed to execute: ?USE [BillPay] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSelectCount] AS BEGIN SET NOCOUNT ON; Select count(*) from dbo.Payments_S
ng; End
[sql] java.sql.SQLException: Incorrect syntax near '?'.
Actually, I have no "?" in my sql file.
This my sql file:
USE [BillPay]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspSelectCount]
AS
BEGIN
SET NOCOUNT ON;
Select count(*)
from dbo.Payments_Staging;
End
-----Original Message-----
From: Olivier Gies [mailto:olivier.gies@bull.net]
Sent: Sunday, March 16, 2008 8:02 PM
To: Ant Users List
Subject: Re: ant sql call stored procedure
Hi,
It looks like the SQL file is encoded in some version of Unicode (UTF-8
or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
task is loading it in ISO-8599 by default.
You should verify your source file format (using 'file' command on *nix
systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
<sql> task's "encoding" attribute accordingly (see
http://ant.apache.org/manual/CoreTasks/sql.html)
BR,
Olivier
-------- Original Message --------
Subject: ant sql call stored procedure
From: Chaohua Wang <cw...@incomm.com>
To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
Date: 14/03/2008 06:31
> Hi folks,
>
>
> I am using ant sql to call a stored procedure of Ms Sql server
>
> <sql
> driver="${jdbcdriver}"
> url="${dburl}/BillPay"
> userid="${userid}"
> password="${password}"
> src=" uspSelectCount.sql "
> classpathref="webtest.path.id"
> </sql>
>
>
> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>
> I always got this exception:
>
> test:
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>
>
> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>
> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>
> cwang
>
>
>
> This is uspSelectCount.sql file
> --------------------------------------------
> USE [BillPay]
> GO
> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> CREATE PROCEDURE [dbo].[uspSelectCount]
>
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> Select count(*)
> from dbo.Payments_Staging
>
> End
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>
>
--
*Olivier Gies*
*Delivery Manager
Customs & Tax Software Engineering Center
Bull, Architect of an Open World ^TM
Phone: +86 (10) 65978001 - Ext 555 *
*www.bull.com <http://www.bull.com/>*
*This e-mail contains material that is confidential for the sole use of
the intended recipient. Any review, reliance or distribution by others
or forwarding without express permission is strictly prohibited. If you
are not the intended recipient, please contact the sender and delete all
copies.*
---------------------------------------------------------------------
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
Re: ant sql call stored procedure
Posted by Olivier Gies <ol...@bull.net>.
Hi,
It looks like the SQL file is encoded in some version of Unicode (UTF-8
or Unicode/UTF-16 ?), with each character using 2 bytes, and your <sql>
task is loading it in ISO-8599 by default.
You should verify your source file format (using 'file' command on *nix
systems, or any editing tool in Windows, e.g. UltraEdit32) and set the
<sql> task's "encoding" attribute accordingly (see
http://ant.apache.org/manual/CoreTasks/sql.html)
BR,
Olivier
-------- Original Message --------
Subject: ant sql call stored procedure
From: Chaohua Wang <cw...@incomm.com>
To: Chaohua Wang <cw...@incomm.com>, Ant Users List <us...@ant.apache.org>
Date: 14/03/2008 06:31
> Hi folks,
>
>
> I am using ant sql to call a stored procedure of Ms Sql server
>
> <sql
> driver="${jdbcdriver}"
> url="${dburl}/BillPay"
> userid="${userid}"
> password="${password}"
> src=" uspSelectCount.sql "
> classpathref="webtest.path.id"
> </sql>
>
>
> I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
>
> I always got this exception:
>
> test:
> [sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
> [sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
> a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
> [sql] java.sql.SQLException: Incorrect syntax near 'E'.
> [sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
> t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
> E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
> S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
> [sql] java.sql.SQLException: Incorrect syntax near 'T'.
>
>
> 'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
>
> I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
>
> cwang
>
>
>
> This is uspSelectCount.sql file
> --------------------------------------------
> USE [BillPay]
> GO
> /****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> CREATE PROCEDURE [dbo].[uspSelectCount]
>
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> Select count(*)
> from dbo.Payments_Staging
>
> End
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>
>
--
*Olivier Gies*
*Delivery Manager
Customs & Tax Software Engineering Center
Bull, Architect of an Open World ^TM
Phone: +86 (10) 65978001 - Ext 555 *
*www.bull.com <http://www.bull.com/>*
*This e-mail contains material that is confidential for the sole use of
the intended recipient. Any review, reliance or distribution by others
or forwarding without express permission is strictly prohibited. If you
are not the intended recipient, please contact the sender and delete all
copies.*
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
RE: ant sql src
Posted by "Karr, David" <da...@wamu.net>.
It will be SQL statements. Stored procedures are a "server-side" notion
of the database, not a client-side notion. If you want examples, that
page you cited has several examples. I think you didn't read it very
carefully.
> -----Original Message-----
> From: Chaohua Wang [mailto:cwang@incomm.com]
> Sent: Friday, March 14, 2008 8:38 AM
> To: Ant Users List
> Subject: ant sql src
>
> Hi Folks,
>
> I read this link
> http://ant.apache.org/manual/CoreTasks/sql.html about ant sql.
> There is attribute src----- File containing SQL statements.
> I am not sure this src is a sql statement or it is a stored
> procedure. I am using MS Sqlserver.
> Could someone give me a example? Please Help.
>
> cwang
>
> ---------------------------------------------------------------------
> 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
ant sql src
Posted by Chaohua Wang <cw...@incomm.com>.
Hi Folks,
I read this link http://ant.apache.org/manual/CoreTasks/sql.html about
ant sql.
There is attribute src----- File containing SQL statements.
I am not sure this src is a sql statement or it is a stored procedure. I
am using MS Sqlserver.
Could someone give me a example? Please Help.
cwang
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
ant sql call stored procedure
Posted by Chaohua Wang <cw...@incomm.com>.
Hi folks,
I am using ant sql to call a stored procedure of Ms Sql server
<sql
driver="${jdbcdriver}"
url="${dburl}/BillPay"
userid="${userid}"
password="${password}"
src=" uspSelectCount.sql "
classpathref="webtest.path.id"
>
</sql>
I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. But when I am using ant sql to call the file uspSelectCount.sql that is located with the build file.
I always got this exception:
test:
[sql] Executing file: C:\workspace\Canoo\billPay\tests\uspSelectCount.sql
[sql] Failed to execute: ■U S E [ B i l l P a y ] G O / * * * * * * O b j e c t : S t o r e d P r o c e d u r e [ d b o ] . [ u s p S e l e c t C o u n t ] S c r i p t D
a t e : 0 3 / 1 3 / 2 0 0 8 1 5 : 5 1 : 1 3 * * * * * *
[sql] java.sql.SQLException: Incorrect syntax near 'E'.
[sql] Failed to execute: S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= = = = = = = = = = = - - A u t h o r : < A u t h o r , , N a m e > - - C r e a t e d a t e : < C r e a t e D a t e , , > - - D e s c r i p t i o n : < D e s c r i p
t i o n , , > - - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = C R E A T E P R O C E D U R E [ d b o ] . [ u s p S e l e c t C o u n t ] A S B
E G I N - - S E T N O C O U N T O N a d d e d t o p r e v e n t e x t r a r e s u l t s e t s f r o m - - i n t e r f e r i n g w i t h S E L E C T s t a t e m e n t s .
S E T N O C O U N T O N ; S e l e c t c o u n t ( * ) f r o m d b o . P a y m e n t s _ S t a g i n g E n d
[sql] java.sql.SQLException: Incorrect syntax near 'T'.
'E' is the third character of USE. 'T' is the third character of SET. I am not sure the reason why? Please help.
I can execute uspSelectCount.sql in the SQLunit. <stmt>{call uspSelectCount()}</stmt>
cwang
This is uspSelectCount.sql file
--------------------------------------------
USE [BillPay]
GO
/****** Object: StoredProcedure [dbo].[uspSelectCount] Script Date: 03/13/2008 15:51:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[uspSelectCount]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select count(*)
from dbo.Payments_Staging
End
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
RE: ant sql call stored procedure
Posted by "Anderson, Rob (Global Trade)" <Ro...@nike.com>.
When executing sql using Ant, you are using a jdbc connection, not the sql client that is available from the database vendor. Some of the features that are database specific are not available when using a jdbc connection.
If you find that the Ant sql task does not support your vendor specific sql statements, you can use the exec task to use the run the vendor supplied client.
-Rob Anderson
-----Original Message-----
From: Chaohua Wang [mailto:cwang@incomm.com]
Sent: Thursday, March 13, 2008 10:15 AM
To: Ant Users List
Subject: ant sql call stored procedure
Hi folks,
I am using ant sql to call a stored procedure of Ms Sql server
<sql
driver="${jdbcdriver}"
url="${dburl}/BillPay"
userid="${userid}"
password="${password}"
src="selectCount.sql"
classpathref="webtest.path.id"
>
</sql>
I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. Then I use Execute--> File to generate a file called selectCount.sql. But when I am using ant sql to call the file selectCount.sql that is located with the build file.
I always got this exception:
[sql] Executing file: C:\workspace\billPay\tests\selectCount.sql
[sql] Failed to execute: ■U S E [ B i l l P a y ] G O D E C L A R E @ r e t u r n _ v a l u e i n t E X E C @ r e t u r n _ v a l u e = [ d b o ] . [ u s p S e l e c t
C o u n t ] S E L E C T ' R e t u r n V a l u e ' = @ r e t u r n _ v a l u e G O
[sql] java.sql.SQLException: Incorrect syntax near 'E'.
'E' is the third character of USE. I am not sure the reason why? Please help.
cwang
This is selectCount.sql file
---------------------------------
USE [BillPay]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspSelectCount]
SELECT 'Return Value' = @return_value
GO
--------------------------------------
---------------------------------------------------------------------
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
ant sql call stored procedure
Posted by Chaohua Wang <cw...@incomm.com>.
Hi folks,
I am using ant sql to call a stored procedure of Ms Sql server
<sql
driver="${jdbcdriver}"
url="${dburl}/BillPay"
userid="${userid}"
password="${password}"
src="selectCount.sql"
classpathref="webtest.path.id"
>
</sql>
I can execute stored procedure in Ms Sql server. My stored procedure name is uspSelectCount. Then I use Execute--> File to generate a file called selectCount.sql. But when I am using ant sql to call the file selectCount.sql that is located with the build file.
I always got this exception:
[sql] Executing file: C:\workspace\billPay\tests\selectCount.sql
[sql] Failed to execute: ■U S E [ B i l l P a y ] G O D E C L A R E @ r e t u r n _ v a l u e i n t E X E C @ r e t u r n _ v a l u e = [ d b o ] . [ u s p S e l e c t
C o u n t ] S E L E C T ' R e t u r n V a l u e ' = @ r e t u r n _ v a l u e G O
[sql] java.sql.SQLException: Incorrect syntax near 'E'.
'E' is the third character of USE. I am not sure the reason why? Please help.
cwang
This is selectCount.sql file
---------------------------------
USE [BillPay]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspSelectCount]
SELECT 'Return Value' = @return_value
GO
--------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
RE: about ant sql connection being pulled out , into a property file
Posted by Chaohua Wang <cw...@incomm.com>.
Thank you very much
-----Original Message-----
From: Steve Loughran [mailto:stevel@apache.org]
Sent: Thursday, March 13, 2008 3:52 AM
To: Ant Users List
Subject: Re: about ant sql connection being pulled out , into a property
file
Chaohua Wang wrote:
> Folks,
>
> I am using ant sql,
>
>
>
> <sql
>
> driver="net.sourceforge.jtds.jdbc.Driver"
>
> url="jdbc:jtds:sqlserver://10.3.1.201/Test"
>
> userid="settle"
>
> password="settle"
>
> print="yes"
>
> classpathref="webtest.path.id"
>
> output="${tmp.dir}/dbModification.txt"
>
> >
>
>
>
> Because I have a few of ant sql statements, could you tell me how to
put
> above part into a property file. So there is no duplicate code.
Look at <presetdef> to define a template you can use and reuse
--
Steve Loughran http://www.1060.org/blogxter/publish/5
Author: Ant in Action http://antbook.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
Re: about ant sql connection being pulled out , into a property
file
Posted by Steve Loughran <st...@apache.org>.
Chaohua Wang wrote:
> Folks,
>
> I am using ant sql,
>
>
>
> <sql
>
> driver="net.sourceforge.jtds.jdbc.Driver"
>
> url="jdbc:jtds:sqlserver://10.3.1.201/Test"
>
> userid="settle"
>
> password="settle"
>
> print="yes"
>
> classpathref="webtest.path.id"
>
> output="${tmp.dir}/dbModification.txt"
>
> >
>
>
>
> Because I have a few of ant sql statements, could you tell me how to put
> above part into a property file. So there is no duplicate code.
Look at <presetdef> to define a template you can use and reuse
--
Steve Loughran http://www.1060.org/blogxter/publish/5
Author: Ant in Action http://antbook.org/
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org