Обсуждение: Error selecting constant

I'm getting an error when selecting a constant through the jdbc driver
(it works fine with the psql command line).

I don't have access to the exact java code as this is just to do some
opennms trickery and my select is in the opennms config files, but I'm
getting the following.
I'd appreciate any clues as to whether there is a simple fix (ie
changing the query, not the code).


org.springframework.dao.DataRetrievalFailureException: Problem executing
statement: SELECT '?_?' as result with values [str1, str2]; nested
exception is org.postgresql.util.PSQLException: The column index is out
of range: 1, number of columns: 0.
Caused by: org.postgresql.util.PSQLException: The column index is out of
range: 1, number of columns: 0.
         at
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:57)
         at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:121)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2112)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1238)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1224)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1698)
         at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:365)
         at
org.opennms.netmgt.utils.JDBCTemplate.doExecute(JDBCTemplate.java:110)
         at
org.opennms.netmgt.utils.JDBCTemplate.execute(JDBCTemplate.java:84)
         at
org.opennms.netmgt.config.EventTranslatorConfigFactory$SqlValueSpec.getResult(EventTranslatorConfigFactory.java:621)
         at
org.opennms.netmgt.config.EventTranslatorConfigFactory$AssignmentSpec.apply(EventTranslatorConfigFactory.java:444)
         at

org.opennms.netmgt.config.EventTranslatorConfigFactory$TranslationMapping.translate(EventTranslatorConfigFactory.java:373)
         at
org.opennms.netmgt.config.EventTranslatorConfigFactory$TranslationSpec.translate(EventTranslatorConfigFactory.java:298)
         at
org.opennms.netmgt.config.EventTranslatorConfigFactory.translateEvent(EventTranslatorConfigFactory.java:262)
         at
org.opennms.netmgt.translator.EventTranslator.onEvent(EventTranslator.java:128)
         at
org.opennms.netmgt.eventd.EventIpcManagerDefaultImpl$ListenerThread.run(EventIpcManagerDefaultImpl.java:189)
         at java.lang.Thread.run(Thread.java:595)

Mat wrote:

> org.springframework.dao.DataRetrievalFailureException: Problem executing
> statement: SELECT '?_?' as result with values [str1, str2]; nested
> exception is org.postgresql.util.PSQLException: The column index is out
> of range: 1, number of columns: 0.

Try "SELECT ? || '_' || ?"

Parameter substitution is not just substitution into the query string,
you can only put ? where it makes sense to have a selfcontained data
value. And it's definitely not going to work inside a string constant,
the driver must interpret that '?' as a literal '?' not a parameter
placeholder!

-O