CODEWITHSUNDEEP
javasqlsql-serverexception
Stepan
Stepan

Reputation: 1431

SQL response is treated as column name, rather than content in Java

Method below gets result set from SQL database and populates a Map<String,String>. It works well in most cases, but with this particular query I get an exception saying:

Unable to execute query: select RULEID, GRAPHRULE from GRAPHRULE3D WHERE GRAPHRULE LIKE '%1717-002%' AND GRAPHRULE NOT LIKE '%DRAWING%' Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name ('1717-002'); is not valid.

Query itself works on SQL Server. Why does it think that ('1717-002'); in second column is a column name, while it is actually a value?

Problem follows String value = rs.getString(rs.getString(2).trim()); if I drop it I get a set of String key =Integer.toString((Integer) rs.getObject(1)); without a problem.

//String query = "select RULEID, GRAPHRULE from GRAPHRULE3D WHERE GRAPHRULE LIKE '%1717-002%' AND GRAPHRULE NOT LIKE '%DRAWING%'"
public Map<String,String> getStrinfStringPairsFromIntegerStringSQL(String query) {

    return processContent(query, (rs) -> {
        Map<String,String> optionDescriptions = new TreeMap<>();

        while (rs.next()) {

            String key =Integer.toString((Integer) rs.getObject(1));
            String value = rs.getString(rs.getString(2).trim());

            optionDescriptions.put(key, value);
        }
        return optionDescriptions;
    });
}


@FunctionalInterface
public static interface ResultSetHandler<S, T> {

    Map<S, T> accept(ResultSet rs) throws SQLException;
}

public <S, T> Map<S, T> processContent(String query, ResultSetHandler handler) {
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        try (Connection con = DriverManager.getConnection(connectionUrl)) {
            try (Statement stmt = con.createStatement()) {
                try (ResultSet resultSet = stmt.executeQuery(query)) {
                    return handler.accept(resultSet);
                }
            }
        }
    } catch (SQLException e) {
        throw new IllegalStateException("Unable to execute query: " + query, e);
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(EngineSQLUtils.class.getName()).log(Level.SEVERE, null, ex);
    }

    return null;
}

Upvotes: 0

Views: 277

Answers (2)

TT.
TT.

Reputation: 16146

You're taking a value from the resultset and use it as a column name in that same resultset. That will not work with the query you have.

If your resultset has 1717-002 as a value, that line rs.getString(rs.getString(2).trim()); will expand to rs.getString("1717-002");. Surely you didn't mean something like that? You only have columns RULEID and GRAPHRULE.

Upvotes: 3

Nir Levy
Nir Levy

Reputation: 12953

RULE is a reserved word in SQL-server, so your query is wrong.
If you can, I recommend to change the column name, just a source of problems.
If you can't, try WHERE RULESET.RULE LIKE '%1717-002%' or WHERE [RULE] LIKE '%1717-002%'

Upvotes: 1

Related Questions