#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    SQL ANT Task for postgreSQL plsql script fails with syntax error


    Hello,

    I am unable to run plsql script from ANT SQL Task.
    My ant task -
    <sql
    driver="org.postgresql.Driver"
    url="jdbcostgresql://x.x.x.x:5432/xxx"
    userid="username"
    password ="pwd"
    print="true"
    delimiter="/"
    delimitertype="row"
    classpathref="antclasspath"
    >
    <transaction src="test.sql"/>
    </sql>

    My sql script is -
    DECLARE @l_row_exists;
    BEGIN
    SET @l_row_exists = select count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE upper(TABLE_NAME) = 'xyz' AND upper(COLUMN_NAME) = 'ABC';
    IF (CAST(@l_row_exists AS INTEGER) = 0)
    BEGIN
    ALTER TABLE XYZ add EFG varchar(255) NOT NULL DEFAULT 'dashboard.do';
    END
    END

    I get error -
    org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"
    Position: 10
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)


    The same SQL task works for Oracle and MSSQL plsql scripts.
    Am I missing something here?
    I have tried changing delimiter to ";", delimitertype to normal, tried keepformat and escapeprocessing, nothing worked for me.

    Thanks,
    Ashwini
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    I'm sure that this script is not working for Oracle - what you have posted is SQL Server (and only SQL Server) syntax.

    You will need to adjust that to the PL/pgSQL. Please read the manual it's all documented there.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    The script given here works only for PostgreSQL, the ant SQL task works for other dbs. But for PostgreSQL the ant task is not working

    Originally Posted by shammat
    I'm sure that this script is not working for Oracle - what you have posted is SQL Server (and only SQL Server) syntax.

    You will need to adjust that to the PL/pgSQL. Please read the manual it's all documented there.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by AshwiniGuru
    The script given here works only for PostgreSQL, the ant SQL task works for other dbs. But for PostgreSQL the ant task is not working
    The script that you have posted will only work for SQL Server and nothing else.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by shammat
    The script that you have posted will only work for SQL Server and nothing else.
    dude! it definitely works on postgresql 9.1.5 version. Why do u think this will not work on postgres??

    As mentioned earlier, i think the problem is with how ant is sending the plsql to postgres.

    Forget about this script, any script that has @ or $ fails with same syntax error when run from ANT.

    This issue is critical to us and any help in resolving this issue is appreciated
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by AshwiniGuru
    it definitely works on postgresql 9.1.5 version. Why do u think this will not work on postgres??
    because everything in that script is wrong for Postgres.

    It seems you didn't even look at the manual to understand the syntax of PL/pgSQL:
    • Postgres does not allow @ as part of an identifier (and thus you cannot use it for a variable)
    • PL/pgSQL requires a datatype when declaring a variable
    • PL/pgSQL does not accept the syntax: "set l_row_exists = select ..." (you need an select .. into ...)
    • you are missing the DO keyword to define an anonymous PL/pgSQL block
    • you are missing the semicolon ; to terminate the lines
    • you are missing the THEN keyword for the IF condition
    • an if statemnet is terminated with an END IF keyword
    • to add a column you need ADD COLUMN as part of the ALTER statement

    Additionally your script does not include an / as the delimiter even though you told Ant to use it (delimiter="/")

    So taking all this into account, your script should look something like this:
    Code:
    do
    $body$
    DECLARE 
       l_row_exists integer;
    BEGIN
        select count(*) 
           into l_row_exists 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE upper(TABLE_NAME) = 'xyz' 
        AND upper(COLUMN_NAME) = 'ABC';
        
        if (l_row_exists = 0) then 
           ALTER TABLE perso add column EFG varchar(255) NOT NULL DEFAULT 'dashboard.do';
        end if;
    END;
    $body$
    /
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo