1

I am attempting to query an as400 sql database on a date field, with the jt400 npm package. On table t, mydate is a timestamp string that should equal the key, but for some reason it is not working. On a previous query I am getting the value of key. And then passing it back into this query exactly as it came out. How do I make sure the date comparison works?

const key = '123456789' // some date as a timestamp
const sql = `select * from table t where varchar_format(t.mydate, 'YYYYMMDD') = varchar_format(?, 'YYYYMMDD');`

this.pool.query(sql, [key])

Then I get the error

Error running instance method\njava.sql.SQLException: Data type mismatch. (20190525)\n\tat com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:828)\n\tat com.ibm.as400.access.JDError.throwSQLException(JDError.java:495)\n\tat com.ibm.as400.access.JDError.throwSQLException(JDError.java:464)\n\tat com.ibm.as400.access.SQLTimestamp.setTimestampFieldsFromString(SQLTimestamp.java:79)\n\tat com.ibm.as400.access.SQLTimestamp.set(SQLTimestamp.java:469)\n\tat com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3583)\n\tat com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:3231)\n\tat nodejt400.JdbcJsonClient.setParams(Unknown Source)\n\tat nodejt400.JdbcJsonClient.query(Unknown Source)\n\tat nodejt400.JT400.query(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n
Mardok
  • 1,360
  • 10
  • 14
  • Syntax error: `... where and ...`. – jarlh Dec 11 '19 at 21:18
  • good catch, but that is not it. the real query has it lots of stuff in the where. Removing the and with the date stops the error. so I just put the relevant stuff here. – Mardok Dec 11 '19 at 21:22

1 Answers1

0

I missed the fact that key was in the format of YYYYMMDD and varchar_format was looking for a Date, not a string.

const sql = select * from table t where varchar_format(t.mydate, 'YYYYMMDD') = ?;

Mardok
  • 1,360
  • 10
  • 14