Run dynamically created SQL against a PostgeSQL DB from DbFit

[Eoin O'Hehir]

Hi,

I am trying to create a table from DbFit with a table name containing a date a few days before the DbFit test is run.

I've tried to create the CREATE TABLE statement as follows:

!|Query|SELECT to_char(current_date - interval '3', 'YYYYMMDD') as mindate|

|mindate?|

|>>mindate|

!|Query|SELECT format('CREATE TABLE processing.bkp_%s_testtable(col1 int)',_:mindate) as createstmt|

|createstmt?|

|>>createstmt|

createstmt will contain the following

 CREATE TABLE processing.bkp_20170615_testtable(col1 int)

I then try to execute the SQL contained within that variable 

!|Execute|_:createstmt|  

But get the following error

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Position: 1
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
at dbfit.fixture.StatementExecution.run(StatementExecution.java:24)
at dbfit.fixture.Execute.doRows(Execute.java:27)
at fit.Fixture.doTable(Fixture.java:156)
at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:21)
at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:104)
at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:89)
at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:73)
at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:27)
at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
at fit.Fixture.doTables(Fixture.java:81)
at fit.FitServer.process(FitServer.java:81)
at fit.FitServer.run(FitServer.java:56) 

at fit.FitServer.main(FitServer.java:41) 

Can anyone advise how I can do this?

Thanks,

Eoin

[Yavor Nikolov]

Postgresql doesn't support whole statements as bind variables. Maybe you can create a stored procedure which does the work accepting a date or string parameter.

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[Eoin O'Hehir]

Hi Yavor,

Thanks for the suggestion. I implemented it and all working now.

For completeness, the solution was to create a simple PostGreSQL function that executes a passed in SQL statement...

    CREATE OR REPLACE FUNCTION processing.utl_execute_sql
    (
      IN   a_sql_stmt varchar
    )
    RETURNS void AS
    $$
    BEGIN
        EXECUTE a_sql_stmt;
    END
    $$
    LANGUAGE 'plpgsql';

This function is then used within the DbFit fixture...

    !|Query|SELECT to_char(current_date - interval '3', 'YYYYMMDD') as mindate|
    |mindate?|
    |>>mindate|
   
    !|Query|SELECT format('CREATE TABLE processing.bkp_%s_testtable(col1 int)',_:mindate) as createstmt|
    |createstmt?|
    |>>createstmt|
   

    !|Execute Procedure|processing.utl_execute_sql|
    |a_sql_stmt|
    |<<createstmt|

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.