"Numeric value out of range" then filtering on a numeric table column

[markus-hsk]

We are getting the Error [IBM][System i Access ODBC Driver]Column 3: Numeric value out of range. by using the loopback-connector-ibmi. We debugged through the progress and found out, that it happens on filtering records by a numeric field which is allowed to contain 7 digits. The given filter got a value with 8 digits.

Our database table column is defined like this: KOATRNR NUMERIC(7,0)

Our filter-Json, which we are passing to the ODBC-connector, looks like this:
{where: {KOARTNR: {inq: [1,1234567,12345678]}}}

As you can see, the last value contains a number with 8 digits, one more as the table column is capable to store. The interesting point is, a simple SQL-SELECT like this SELECT * FROM table WHERE KOARTNR IN (1,1234567,12345678) works without an error.

Based on the above data we are now assuming, that there might be a bug inside the connector.

Currently using these versions:

    "@loopback/boot": "^1.5.6",
    "@loopback/context": "^1.23.0",
    "@loopback/core": "^1.10.2",
    "@loopback/openapi-v3": "^1.9.7",
    "@loopback/repository": "^1.14.0",
    "@loopback/rest": "^1.19.0",
    "@loopback/rest-explorer": "^1.3.7",
    "@loopback/service-proxy": "^1.3.6",
    "dotenv": "^8.2.0",
    "loopback-connector-ibmi": "^1.0.0-beta.1"
    "@loopback/build": "^2.0.11",
    "source-map-support": "^0.5.13",
    "@loopback/testlab": "^1.8.1",
    "@types/node": "^10.14.18",
    "@typescript-eslint/parser": "^2.3.0",
    "@typescript-eslint/eslint-plugin": "^2.3.0",
    "@loopback/eslint-config": "^4.1.0",
    "eslint": "^6.4.0",
    "eslint-config-prettier": "^6.3.0",
    "eslint-plugin-eslint-plugin": "^2.1.0",
    "eslint-plugin-mocha": "^6.1.1",
    "typescript": "~3.6.3"

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[markus-hsk]

This is still an issue.

[markdirish]

Sorry, my fault that this got marked stale. I'll take a look at this today.

I'm curious as to what SQL it is generating. Could you maybe change the executeSQL function in lib/imbiconnector.js' to print the sqlandparametersvariables at the top of the function withconsole.log`? That would help give me some clues

[markus-hsk]

I'm sorry for not responding, yet. Made a ticket for my colleague, but he had not the time to look into it, yet. I think it will take a while, till we're able to look on this again.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[markus-hsk]

unstale
Coworker still had no time for checking.

[markus-hsk]

Hi @markdirish
I'm sorry that it took so long, but now my colleague was finally able to do what you requested.

SQL: SELECT "KOART#","KOUVP","KOSTAT" FROM "WINKLERC"."KOPUG" WHERE ("KOART#"=?) AND ("KOSTAT"=?) ORDER BY "KOART#" FETCH FIRST 1 ROWS ONLY
params: 80901420,A
options: {}

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[markus-hsk]

unstale
not yet solved

[markdirish]

Sorry Markus, didn't mean to let this get stale. I'm pretty sure I know the issue, the ODBC driver gets upset when it tries to pass a signed smallint value to an unsigned smallint. This is a problem in the odbc connector package. I have a big update to that package coming hopefully this week or next that should fix a lot of these data type issues. I will be sure to update this issue when I push it.

[markus-hsk]

No need to say sorry - everything fine so far!

[nedi-dev]

@markdirish : You mentioned a new version of the ODBC-driver coming up soon.
Do you mean 2.3.5 from 09/14/2020?
When do you plan to publish the next version?

[markdirish]

@markus-hsk and @nedi-dev

I know this is a long time later, but 2.4.0 beta is out now and should be available by running npm install odbc@beta.

I am fairly sure this if fixed in that push. I was able to recreate the error on the latest branch (2.3.6), but 2.4.0-beta.1 doesn't choke on the same query. If you could install it and run the same commands that were causing the issue, that would be great.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[markdirish]

odbc 2.4.0 is now published. I will create PR to require this latest version, which fixes the "Numeric value out of range" issue.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[markus-hsk]

unstale

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.