CODEWITHSUNDEEP
javamysqlsqljooq
Jim In NH
Jim In NH

Reputation: 233

Can not fix "Unknown table" exception from JOOQ query

I am having trouble getting data from a database I know exists and I know the format of.

In the code snippet below the "if conn != null" is just a test to verify the database name, table name, etc are all correct, and they DO verify.

The last line below is what generates the exception

public static HashMap<Integer, String> getNetworkMapFromRemote(DSLContext dslRemote, Connection conn, Logger logger) {
    HashMap<Integer,String> remoteMap = new HashMap<Integer, String>();
    // conn is only used for test purposes
    if (conn != null) {
        // test to be sure database is ok
        try
        {
            ResultSet rs = conn.createStatement().executeQuery("SELECT networkid, name FROM network");
            while (rs.next()) {
                System.out.println("TEST: nwid " + rs.getString(1) + " name " + rs.getString(2));
            }
            rs.close();
        }
        catch ( SQLException se )
        {
            logger.trace("getNetworksForDevices SqlException: " + se.toString());
        }
    }
    // ----------- JOOQ problem section ------------------------
    Network nR = Network.NETWORK.as("network");
    // THE FOLLOWING LINE GENERATES THE UNKNOWN TABLE 
    Result<Record2<Integer, String>> result      = dslRemote.select( nR.NETWORKID, nR.NAME ).fetch();

This is the output

TEST: nwid 1 name Network 1

org.jooq.exception.DataAccessException: SQL [select `network`.`NetworkId`, `network`.`Name` from dual]; Unknown table 'network' in field list
    at org.jooq.impl.Utils.translate(Utils.java:1288)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:327)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:330)
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2256)
    at com.nvi.kpiserver.remote.KpiCollectorUtil.getNetworkMapFromRemote(KpiCollectorUtil.java:328)
    at com.nvi.kpiserver.remote.KpiCollectorUtilTest.testUpdateKpiNetworksForRemoteIntravue(KpiCollectorUtilTest.java:61)
    .................
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'network' in field list
    .................

For the sake of completness here is part of the JOOQ generated class file for Network

package com.wbcnvi.intravue.generated.tables;
@javax.annotation.Generated(value    = { "http://www.jooq.org", "3.3.1" },
                            comments = "This class is generated by jOOQ")
@java.lang.SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Network extends org.jooq.impl.TableImpl<com.wbcnvi.intravue.generated.tables.records.NetworkRecord> {
    private static final long serialVersionUID = 1729023198;
    public static final com.wbcnvi.intravue.generated.tables.Network NETWORK = new com.wbcnvi.intravue.generated.tables.Network();
    @Override
    public java.lang.Class<com.wbcnvi.intravue.generated.tables.records.NetworkRecord> getRecordType() {
        return com.wbcnvi.intravue.generated.tables.records.NetworkRecord.class;
    }
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.Integer> NWID = createField("NwId", org.jooq.impl.SQLDataType.INTEGER.nullable(false), this, "");
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.Integer> NETWORKID = createField("NetworkId", org.jooq.impl.SQLDataType.INTEGER.nullable(false).defaulted(true), this, "");
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.String> NAME = createField("Name", org.jooq.impl.SQLDataType.CHAR.length(40).nullable(false).defaulted(true), this, "");
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.Integer> USECOUNT = createField("UseCount", org.jooq.impl.SQLDataType.INTEGER.nullable(false).defaulted(true), this, "");
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.Integer> NETGROUP = createField("NetGroup", org.jooq.impl.SQLDataType.INTEGER.nullable(false).defaulted(true), this, "");
    public final org.jooq.TableField<com.wbcnvi.intravue.generated.tables.records.NetworkRecord, java.lang.String> AGENT = createField("Agent", org.jooq.impl.SQLDataType.CHAR.length(16), this, "");

    public Network() {
        this("network", null);
    }
    public Network(java.lang.String alias) {
        this(alias, com.wbcnvi.intravue.generated.tables.Network.NETWORK);
    }
    ..........

Based on the "unknown table" exception I thought there was a problem connected to the wrong database or wrong server, but the console output is correct for a JDBC query.

Any thoughts are appreciated, perhaps something else can be the root cause or the DSLContext is not valid (but I would think that would generate a different exception).

Upvotes: 2

Views: 1038

Answers (1)

Jim In NH
Jim In NH

Reputation: 233

The answer ends up being simple, I did not include the .from() method

Result<Record2<Integer, String>> result = dslRemote.select( nR.NETWORKID, nR.NAME )
    .from(nR)
    .fetch();

That is why the table was unknown, I never put the from method in.

Upvotes: 5

Related Questions