search

loopbackio / loopback-connector-ibmi

Other
2 stars 4 forks source link

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

Open markus-hsk opened 4 years ago

markus-hsk commented 4 years ago

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.

Fehler Loopback 11 03 2020

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[bot] commented 4 years ago

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 commented 4 years ago

This is still an issue.

markdirish commented 4 years ago

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 thesqlandparametersvariables at the top of the function withconsole.log`? That would help give me some clues

markus-hsk commented 4 years ago

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[bot] commented 4 years ago

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 commented 4 years ago

unstale Coworker still had no time for checking.

markus-hsk commented 4 years ago

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[bot] commented 3 years ago

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 commented 3 years ago

unstale not yet solved

markdirish commented 3 years ago

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 commented 3 years ago

No need to say sorry - everything fine so far!

nedi-dev commented 3 years ago

@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 commented 3 years ago

@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[bot] commented 3 years ago

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 commented 3 years ago

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[bot] commented 3 years ago

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 commented 2 years ago

unstale

stale[bot] commented 2 years ago

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.