Thread: Problem with sql ant task

Problem with sql ant task

From
"Alejandra Juarez D'Aquino"
Date:
Hi, I'm new using Posgres and in Ant as well. 
I'm trying to use an ant task (sql) to run ours sql scripts to update the database... The test should be re-runnables, so, I was looking for a way to do it and I found this

create or replace function execute(TEXT) RETURNS VOID AS $$ BEGIN EXECUTE $1; END ;

$$ LANGUAGE plpgsql STRICT;

select execute('create table CLIENTES.TEST (x int4); ') where not exsist...;



but when I try to do something like that




select execute($$'create table CLIENTES.TEST (
important_field text not null default   's'
); '$$); 

I am suposed to use $$, however the ant task throws the following exception
org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de «$»


Any Idea why is this happening? in pgAdmin it runs ok.


Thanks very much...



Re: Problem with sql ant task

From
"ml-tb"
Date:
Hi,

first: This is not a PostgreSQL or JDBC issue.

some points to remember:

- in ANT the '$' starts a property reference
- functions/triggers/... in JDBC context ist a little bit tricky

for the first point: Have a look at the ANT manual :-).

The second point is implmentation specific. Many Java SQL script
executors splits a scrips in single statments to send them one after
another. But how to detect procedure definition what as one statement?
The procedure langugage is DB specific. No chance for a generic parser.
The solution "Send the whole procedure definition as one statement" does
not work in this environment and especialy not with the ANT task 'sql'.

Bye Thomas


Am Freitag, 2. September 2011 schrieb Alejandra Juarez D'Aquino:
> Hi, I'm new using Posgres and in Ant as well.
> I'm trying to use an ant task (sql) to run ours sql scripts to update
> the database... The test should be re-runnables, so, I was looking
> for a way to do it and I found this
>
> create or replace function execute(TEXT) RETURNS VOID AS $$ BEGIN
> EXECUTE $1; END ;
>
> $$ LANGUAGE plpgsql STRICT;
>
> select execute('create table CLIENTES.TEST (x int4); ') where not
> exsist...;
>
>
>
> but when I try to do something like that
>
>
>
>
> select execute($$'create table CLIENTES.TEST (
> important_field text not null default   's'
> ); '$$);
>
> I am suposed to use $$, however the ant task throws the following
> exception org.postgresql.util.PSQLException: ERROR: error de
> sintaxis en o cerca de «$»
>
>
> Any Idea why is this happening? in pgAdmin it runs ok.
>
>
> Thanks very much...