Connect and run a MINUS query on 2 different database tables?

728 views
Skip to first unread message

Gagneet Singh

unread,
Dec 13, 2015, 4:38:19 AM12/13/15
to dbfit
Hi,

Can I run  the following query on 2 databases, one on MySQL and the other on PostgreSQL on the Cloud?


!|dbfit.MySqlTest|
!|Connect|19.168.0.23|user_mysql|password|mysql_db|

!define q1 {SELECT col1
                 , col2
                 , col3
                 , 'q1' as source
              FROM table1}

!|dbfit.PostgresTest|
!|Connect|sqlhost.aws.com|user_postgres|password|cloud_db|

!define q2 {SELECT col1
                 , col2
                 , col3
                 , 'q2' as source
              FROM table2}


|query|(${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1})|
|col1 |col2          |col3          |source         |


If not, is there any other way possible, or if someone has tried out something which they can help me out with?

Thank You.

Regards,
Gagneet

Yavor Nikolov

unread,
Dec 13, 2015, 5:05:38 AM12/13/15
to db...@googlegroups.com
Hi Gandeet,

Currently DbFit only supports commands accessing single database only.
  • If you want to test that q1 and q2 are producing same outcome: you can use Compare Stored Queries
    Is that what you want to achieve?
  • Depending on the particular database technology - there might be a way to access one of the databases from another. (E.g. Oracle supports that through: heterogeneous gateways and database links).
Regards,
Yavor

--
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+un...@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.

Gagneet Singh

unread,
Dec 13, 2015, 4:37:42 PM12/13/15
to dbfit
Hi Yavor,

Thank you for your reply and the useful link on Store Queries.
Just also wanted to check, if I can run the query with the connection details? 

!|dbfit.MySqlTest|
!|Connect|19.168.0.23|user_mysql|password|mysql_db|

|Store Query|select * from testtbl|fromtable|


!|dbfit.PostgresTest|
!|Connect|sqlhost.aws.com|user_postgres|password|cloud_db|

|Store Query|!-select n, concat('NAME',n) as name from ( select 1 as n union
 select 3 union select 2) x-!|fromdual|

|compare stored queries|fromtable|fromdual|
|name                  |n?                |


As I am trying to get the results from 2 different databases and then compare them. Currently, this also gives me an error. Do I need to run the above commands as 3 different test cases then? If so, how to achieve this?
Sorry if the query is basic in nature, as I did not find any such reference in the documents.

Linking of the databases is currently not possible, due to security restrictions in our environment.

Thank You.

Regards,
Gagneet



On Sunday, December 13, 2015 at 9:05:38 PM UTC+11, Yavor Nikolov wrote:
Hi Gagneet,

Yavor Nikolov

unread,
Dec 13, 2015, 5:48:58 PM12/13/15
to db...@googlegroups.com
What error are you getting?

Gagneet Singh

unread,
Dec 13, 2015, 6:45:32 PM12/13/15
to dbfit
For the first connection, I am able to connect and run the query.
For the second connection:


"java.net.ConnectException: Connection refused: connect"


For the following query test:

 
!|dbfit.PostgresTest|
!|Connect|sdg001.aws.com:5555|user_postgres|password|sdg001|
 
!|Query| set schema 'sor'|
|Store query|!-SELECT A.count(*), 'q1' as source FROM table_postgres A-!|frompostgres|
 
 
!|dbfit.NetezzaTest|
!|Connect|netezzadb|user_netezza|password|netezza_db|
 
|Store query|!-SELECT count(*), 'q2'as source FROM table_netezza-!|fromnetezza|
 
|compare stored queries|frompostgres|fromnetezza| 



Full Error:

dbfit.NetezzaTest
 
Connect
 
java.net.ConnectException: Connection refused: connect
        at java.net.DualStackPlainSocketImpl.connect0(Native Method)
        at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
        at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
        at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
        at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
        at java.net.PlainSocketImpl.connect(Unknown Source)
        at java.net.SocksSocketImpl.connect(Unknown Source)
        at java.net.Socket.connect(Unknown Source)
        at java.net.Socket.connect(Unknown Source)
        at org.postgresql.core.PGStream.(PGStream.java:60)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:101)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:138)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:21)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:31)
        at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:24)
        at org.postgresql.Driver.makeConnection(Driver.java:410)
        at org.postgresql.Driver.connect(Driver.java:280)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:51)
        at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:78)
        at dbfit.DatabaseTest.connect(DatabaseTest.java:44)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at fitlibrary.closure.MethodClosure.invoke(MethodClosure.java:35)
        at fitlibrary.closure.MethodClosure.invokeTyped(MethodClosure.java:28)
        at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:77)
        at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:95)
        at fitlibrary.closure.CalledMethodTarget.invokeAndWrap(CalledMethodTarget.java:358)
        at fitlibrary.traverse.workflow.caller.ActionCaller.run(ActionCaller.java:37)
        at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretRow(DoTraverseInterpreter.java:176)
        at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:96)
        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)
 
Store query
 
org.postgresql.util.PSQLException: ERROR: relation "table_netezza" does not exist
  Position: 37
        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.executeQuery(AbstractJdbc2Statement.java:302)
        at dbfit.fixture.StoreQuery.doTable(StoreQuery.java:44)
        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)
 
compare stored queries
 
java.lang.UnsupportedOperationException: Cannot load a stored query from frompostgres
        at dbfit.util.SymbolUtil.getDataTable(SymbolUtil.java:30)
        at dbfit.fixture.CompareStoredQueries.initialiseDataTables(CompareStoredQueries.java:44)
        at dbfit.fixture.CompareStoredQueries.doTable(CompareStoredQueries.java:50)
        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)
 

Thank You.

Regards,
Gagneet

Gagneet Singh

unread,
Dec 13, 2015, 6:54:03 PM12/13/15
to dbfit
Even if I run the 2 queries on the same server to test if they work, I get the following error:

compare stored queries
 
java.lang.UnsupportedOperationException: Cannot load a stored query from frompostgres
        at dbfit.util.SymbolUtil.getDataTable(SymbolUtil.java:30)
        at dbfit.fixture.CompareStoredQueries.initialiseDataTables(CompareStoredQueries.java:44)
        at dbfit.fixture.CompareStoredQueries.doTable(CompareStoredQueries.java:50)
        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)
 

This is for the query:

!|dbfit.PostgresTest|
!|Connect|sdg001.aws.com:5555|user_postgres|password|sdg001|
 
!|Query| set schema 'sor'|
|Store query|!-SELECT A.count(*), 'q1' as source FROM table_postgres A-!|frompostgres|

!|Query| set schema 'sor'| 
|Store query|!-SELECT B.count(*), 'q2'as source FROM table_postgres B-!|fromnetezza|
 
|compare stored queries|frompostgres|fromnetezza|


Regards,
Gagneet

Yavor Nikolov

unread,
Dec 14, 2015, 8:41:37 AM12/14/15
to db...@googlegroups.com
Hi,

1) I'm not sure "set schema ..." is a valid Query statement for PostgreSql (maybe Execute is more appropriate for such kind of statement. Another question is whether "set schema" is needed at all).
2) It's better to leave a blank line before Store Query (not to be immediately after Query).
3) The A. B. prefixes before count(*) seem suspicious to me (I don't know if it's valid SQL syntax). On the other hand - an alias after count(*) is missing which is not a good idea... Can you try with:

!|Execute|set schema 'sor'|

!|Store Query|select count(*) as cnt, 'q1' as source FROM table_postgres|frompostgres|

!|Store Query|select count(*) as cnt, 'q2' as source FROM table_postgres|fromnetezza|

...

Gagneet Singh

unread,
Dec 15, 2015, 5:33:31 PM12/15/15
to dbfit
Hi Yavor,

Thank you for the answer, that resolved the first issue about the Postgres Db not connecting. 
The other problem is connecting and getting the data from the Netezza DB also and comparing the two.

When I run the second connection in the same test case, it gives me an error saying it cannot connect. Is there some way to get the result from 2 different databases (on separate servers), and then run the compare on them?

Full Error:

        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)


Thank You.

Regards,
Gagneet

Yavor Nikolov

unread,
Dec 15, 2015, 6:19:02 PM12/15/15
to db...@googlegroups.com
Can you connect to Netezza alone (without connecting to PostgreSql first)?

Gagneet Singh

unread,
Dec 16, 2015, 3:33:25 AM12/16/15
to dbfit
Yes alone I can connect and run queries on both the systems individually.
Only when I use them in the same test that they cause this issue.

Thank You.

Regards,
Gagneet

Yavor Nikolov

unread,
Dec 16, 2015, 1:12:53 PM12/16/15
to db...@googlegroups.com
How does your test page look like now? The last error message seems like postgresql related:
...
at org.postgresql.Driver.connect(Driver.java:280)
...

But you're reporting the issue about netezza connection.

Regards,
Yavor

Gagneet Singh

unread,
Dec 16, 2015, 10:00:44 PM12/16/15
to dbfit
Hi Yarov,


The test page has the following:

!|dbfit.PostgresTest                                                                                                         |

!|Connect|sdg001.cgi1234.ap-southeast-2.rds.amazonaws.com:5555|batchuser|password|sdg001|

 

!|Execute|set schema 'sor'|

 

|Store query|!-SELECT count(*) as source_cnt FROM postgres_tbl-!|frompostgres|

 

 

!|dbfit.Netezza                                           |

!|Connect|netezzaprd01|U356965|password|netezza_db|

 

|Store query|!-SELECT count(*) as target_cnt FROM netezza_tbl-!|fromnetezza|

 

 

!|Query|<<frompostgres|

 

!|Query|<<fromnetezza|

 

|compare stored queries|frompostgres|fromnetezza|

|query|(${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1})|

|Count|source_cnt|target_cnt|




If I run this query, it gives me the following:


Test System: fit:fit.FitServer

 

dbfit.PostgresTest

 

Connect

sdg001.cgi1234.ap-southeast-2.rds.amazonaws.com:5555

batchuser

password

sds001

 

Execute

set schema 'sor'

 

Store query

SELECT count(*) as source_cnt postgres_tbl

frompostgres

 

dbfit.Netezza

Missing class or Missing methods: public TypeOfResult getDbfitDotNetezza() { } OR: public TypeOfResult dbfitDotNetezza() { } in class dbfit.PostgresTest

 

Connect

netezza1

user_nz

password

postgres_tbl

 

Store query

org.postgresql.util.PSQLException: ERROR: relation "netezza_tbl" does not exist

  Position: 36

        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.executeQuery(AbstractJdbc2Statement.java:302)

        at dbfit.fixture.StoreQuery.doTable(StoreQuery.java:44)

        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)

SELECT count(*) as target_cnt FROM netezza_tbl

fromnetezza

 

Query

java.lang.NullPointerException

        at fit.ColumnFixture.doRows(ColumnFixture.java:18)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)

        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)

<<frompostgres

 

Query

java.lang.UnsupportedOperationException: Stored queries can only be used on symbols that contain result sets

        at dbfit.fixture.Query.getFromSymbol(Query.java:59)

        at dbfit.fixture.Query.getDataTable(Query.java:39)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:92)

        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)

<<fromnetezza

 

compare stored queries

java.lang.UnsupportedOperationException: Cannot load a stored query from fromnetezza

        at dbfit.util.SymbolUtil.getDataTable(SymbolUtil.java:30)

        at dbfit.fixture.CompareStoredQueries.initialiseDataTables(CompareStoredQueries.java:45)

        at dbfit.fixture.CompareStoredQueries.doTable(CompareStoredQueries.java:50)

        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)

frompostgres

fromnetezza

query

(undefined variable: q1 MINUS undefined variable: q2) UNION (undefined variable: q2 MINUS undefined variable: q1)

Count

source_cnt

target_cnt

 

 


The problem comes when it tries to connect to the second DB, which might be NEtezza or Postgres (I tried reversing the order of the, it gives the following error:


Test System: fit:fit.FitServer

 

dbfit.NetezzaTest

 

Connect

netezzaprd01

netezza_user

password

Netezza_db

 

Store query

SELECT count(*) as target_cnt FROM netezza_tbl

fromnetezza

 

dbfit.PostgresTest

 

Connect

org.netezza.error.NzSQLException: FATAL:  no PostgreSQL user name specified in startup packet
 
        at org.netezza.sql.NzConnection$Initializer.negotiateVersion(NzConnection.java:1981)
        at org.netezza.sql.NzConnection$Initializer.init(NzConnection.java:1941)
        at org.netezza.sql.NzConnection.open(NzConnection.java:257)
        at org.netezza.datasource.NzDatasource.getConnection(NzDatasource.java:523)
        at org.netezza.datasource.NzDatasource.getConnection(NzDatasource.java:510)
        at org.netezza.Driver.connect(Driver.java:153)

sdg001.cgi1234.ap-southeast-2.rds.amazonaws.com:5555

batchuser

password

sds001

 

Execute

org.netezza.error.NzSQLException: ERROR:  'set schema 'sor''
error                   ^ found "'" (at char 16) expecting `TO' or `'=''
 
        at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:276)
        at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
        at org.netezza.sql.NzConnection.execute(NzConnection.java:2673)
        at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
        at org.netezza.sql.NzPreparedStatament.execute(NzPreparedStatament.java:152)
        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)

set schema 'sor'

 

Store query

org.netezza.error.NzSQLException: No results were returned by the query.
        at org.netezza.sql.NzPreparedStatament.executeQuery(NzPreparedStatament.java:176)
        at dbfit.fixture.StoreQuery.doTable(StoreQuery.java:44)
        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)

SELECT count(*) as source_cnt FROM postgres_tbl

frompostgres

 

Query

java.lang.UnsupportedOperationException: Stored queries can only be used on symbols that contain result sets
        at dbfit.fixture.Query.getFromSymbol(Query.java:59)
        at dbfit.fixture.Query.getDataTable(Query.java:39)
        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:92)
        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)

<<frompostgres

 

Query

java.lang.NullPointerException
        at fit.ColumnFixture.doRows(ColumnFixture.java:18)
        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)
        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)

<<fromnetezza

 

compare stored queries

java.lang.UnsupportedOperationException: Cannot load a stored query from frompostgres
        at dbfit.util.SymbolUtil.getDataTable(SymbolUtil.java:30)
        at dbfit.fixture.CompareStoredQueries.initialiseDataTables(CompareStoredQueries.java:44)
        at dbfit.fixture.CompareStoredQueries.doTable(CompareStoredQueries.java:50)
        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)

frompostgres

fromnetezza

query

(undefined variable: q1 MINUS undefined variable: q2) UNION (undefined variable: q2 MINUS undefined variable: q1)

Count

source_cnt

target_cnt

 

 

Thank You.


Regards,

Gagneet


Gagneet Singh

unread,
Dec 16, 2015, 10:04:46 PM12/16/15
to dbfit
If I run the opposite, it gives me (putting Netezza first):

Regards,

Gagneet

Yavor Nikolov

unread,
Dec 17, 2015, 5:24:52 AM12/17/15
to db...@googlegroups.com
You need to use standalone mode connection if you're connecting to multiple databases on same test page:

|import fixture|
|dbfit.fixture|

!|DatabaseEnvironment|postgres|
|Connect|localhost:5432|dbfit|dbfit|dbfit|

....
|Rollback|

!|DatabaseEnvironment|netezza|
|Connect|...|

|Rollback|

!|dbfit.util.ExportFixture|
|dbfit.fixture|


--

Gagneet Singh

unread,
Dec 17, 2015, 3:09:24 PM12/17/15
to dbfit
Hi Yarov,

Thank you for this method of querying multiple DB on the same test page.
My other problem now is that, if I run a query on both these databases using this method, how can I run a MINUS query on the result set from the same?

Currently, when I  use the following test page:

|import fixture|
|dbfit.fixture|

!|DatabaseEnvironment|netezza|
|Connect|192.168.0.3|user|pass|nz_db|

|Store query|!-select * from nz_tbl-!|fromnz|

|Rollback|


!|DatabaseEnvironment|postgres|
|Connect|192.168.0.5|user|pass|pg_db|


!|Execute|set schema 'sor'|

|Store query|!-select * from pg_tbl|frompg|

|Rollback|

|compare stored query|fromnz|frompg|
(OR I use the
|query|(${frompg} MINUS ${fromnz}) UNION (${fromnz} MINUS ${frompg})
)

!|dbfit.util.ExportFixture|
|dbfit.fixture|


the COMPARE QUERY and QUERY results in a NULL result and I cannot run it.

Even using the following results in a Java Null Expection:


|import fixture|
|dbfit.fixture|

!|DatabaseEnvironment|netezza|
|Connect|192.168.0.3|user|pass|nz_db|

|Store query|!-select * from nz_tbl-!|fromnz|

!|Query|<<fromnz|

|Rollback|


!|DatabaseEnvironment|postgres|
|Connect|192.168.0.5|user|pass|pg_db|


!|Execute|set schema 'sor'|

|Store query|!-select * from pg_tbl|frompg|

!|Query|<<frompg|

|Rollback|

|query|(${frompg} MINUS ${fromnz}) UNION (${fromnz} MINUS ${frompg})

!|dbfit.util.ExportFixture|
|dbfit.fixture|



Is there any way I can run a MINUS on these 2 tables from the 2 different databases?


Thank You.

Regards,
Gagneet

Gagneet Singh

unread,
Dec 17, 2015, 3:15:36 PM12/17/15
to dbfit
The error I get for the test page:


|import fixture|
|dbfit.fixture|

!|DatabaseEnvironment|netezza|
|Connect|192.168.0.3|user|
pass|nz_db|

|Store query|!-select * from nz_tbl-!|fromnz|

!|Query|<<fromnz|

|Rollback|


!|DatabaseEnvironment|postgres|
|Connect|192.168.0.5|user|pass|pg_db|


!|Execute|set schema 'sor'|

|Store query|!-select * from pg_tbl|frompg|

!|Query|<<frompg|

|Rollback|

|query|(${frompg} MINUS ${fromnz}) UNION (${fromnz} MINUS ${frompg})

!|dbfit.util.ExportFixture|
|dbfit.fixture|




ERROR for the above Test Page is:

Query

java.lang.NullPointerException

        at fit.ColumnFixture.doRows(ColumnFixture.java:18)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)

        at fit.Fixture.doTable(Fixture.java:156)

        at fit.Fixture.interpretFollowingTables(Fixture.java:121)

        at fit.Fixture.interpretTables(Fixture.java:107)

        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)

<<fromnz

 

Query

java.lang.NullPointerException

        at fit.ColumnFixture.doRows(ColumnFixture.java:18)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)

        at fit.Fixture.doTable(Fixture.java:156)

        at fit.Fixture.interpretFollowingTables(Fixture.java:121)

        at fit.Fixture.interpretTables(Fixture.java:107)

        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)

<<frompg

 

compare stored queries

frompg

fromnz

query

(undefined variable: frompg MINUS undefined variable: fromnz) UNION (undefined variable: fromnz MINUS undefined variable: frompg)

null missing

null

null missing

null
...

Yavor Nikolov

unread,
Dec 17, 2015, 3:26:25 PM12/17/15
to db...@googlegroups.com
- MINUS/UNION or other operations on top of query result sets are not supported.
- "Query" syntax expects a row with column names, values, etc... (In the way you're trying to call it it's normal to fail in that way).

The thing you can try is "Compare Stored Queries" - you haven't posted your tests and error messages related to it...

--
Message has been deleted
Message has been deleted
Message has been deleted

Gagneet Singh

unread,
Dec 17, 2015, 5:30:47 PM12/17/15
to dbfit
Hi Yarov,

Thank you for this method of querying multiple DB on the same test page.
My other problem now is that, if I run a query on both these databases using this method, how can I run a MINUS query on the result set from the same?

Currently, when I  use the following test page:

    |import fixture|
    |dbfit.fixture|

    !|DatabaseEnvironment|netezza|
    |Connect|192.168.0.3|user|pass|nz_db|

    |Store query|!-select * from nz_tbl-!|fromnetezza|
   
    !|Query|<<fromnetezza|


    |Rollback|


    !|DatabaseEnvironment|postgres|
    |Connect|192.168.0.5|user|pass|pg_db|


    !|Execute|set schema 'sor'|

    |Store query|!-select * from pg_tbl|frompostgres|

    !|Query|<<frompostgres|

    |Rollback|

    |compare stored query|fromnetezza|frompostgres|

    !|dbfit.util.ExportFixture|
    |dbfit.fixture|



the COMPARE QUERY and QUERY results in a NULL result and I cannot run it.



The ERROR RESULT is:

Query

java.lang.NullPointerException

        at fit.ColumnFixture.doRows(ColumnFixture.java:18)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)

        at fit.Fixture.doTable(Fixture.java:156)

        at fit.Fixture.interpretFollowingTables(Fixture.java:121)

        at fit.Fixture.interpretTables(Fixture.java:107)

        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)

<<fromnetezza

 

Query

java.lang.NullPointerException

        at fit.ColumnFixture.doRows(ColumnFixture.java:18)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)

        at fit.Fixture.doTable(Fixture.java:156)

        at fit.Fixture.interpretFollowingTables(Fixture.java:121)

        at fit.Fixture.interpretTables(Fixture.java:107)

        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)

<<frompostgres

 

compare stored queries

frompostgres

fromnetezza

query

(undefined variable: frompostgres MINUS undefined variable: fromnetezza) UNION (undefined variable: fromnetezza MINUS undefined variable: frompostgres)

null missing

null

null missing

null

Is there any way I can run a MINUS on these 2 tables from the 2 different databases?

Running the DB queries individually is okay and I do not get any errors, but when I run them within the FIXTURE, it says that the variable defined within the STORED QUERY, is not available...


Thank You.

Regards,
Gagneet

Yavor Nikolov

unread,
Dec 17, 2015, 6:01:23 PM12/17/15
to db...@googlegroups.com
Hi Gandeed,

Please try to read my reply and documentation before replying (and hit reply button only once)!
- As I mentioned, you're not using the Query command correctly. But you don't need it - just remove the !|Query|.. lines
- It's not "compare stored query" it's "compare stored queries"

MINUS query you can only use as part of your SQL statements. There is no special DbFit MINUS command for comparing separate result sets (nor from multiple databases nor from single one).

Best Regards,
Yavor (it's not Yarov :-))

Gagneet Singh

unread,
Dec 20, 2015, 4:38:34 PM12/20/15
to dbfit
Thank you Yavor, and apologies for spelling the name wrong.
I will check on these and correct the same.

Cheers
Gagneet
Reply all
Reply to author
Forward
0 new messages