Red Hat Bugzilla – Bug 1272842
Sybase 15.7: "DataSetLookupException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed"
Last modified: 2015-12-02 10:55:41 EST
Created attachment 1084259 [details]
server.log with exceptions
Description of problem:
The data set used in jbpm-console-ng - Process Instances is not working with Sybase 15.7. After entering the perspective, I'm getting modal dialog with exception:
org.dashbuilder.dataset.exception.DataSetLookupException: Can't lookup on specified data set: jbpmProcessInstances
Caused by: java.sql.SQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query
Version-Release number of selected component (if applicable):
BPM Suite 6.2.0 ER4
Steps to Reproduce:
1. Deploy business-central with Sybase 15.7 as datasource
2. Navigate to Process instances perspective
Modal with error displayed
No error, data set is working
Sorry, missed a step to reproduce:
3. In Process instances create new Filter tab with: column Priority >= 3.
This is also happening in other DBs lie for instance H2. Every time a filter on a numeric values is added to a data lookup calls the SQL query executed by the SQL provider fails because some DBs can't perform automatic conversions from string to numeric.
The solution is to make sure any SQL criteria on top of a numeric value is processed correctly, which means formatting the parameters according to the target column type.
The error has been reported on Dashbuilder's community JIRA:
A fix has been submitted for approval.
They will be merge today by EOD.
Verification blocked by bug # 1277466
Will be verified with the next build after that bug is fixed.
The issue still persists and it is specific to sybase 15.7 (on other databases it's working). The error is the same as in original attachment (pasting it here for reference):
13:49:34,636 ERROR [org.dashbuilder.dataprovider.backend.sql.JDBCUtils] (http-localhost/127.0.0.1:8080-8) SELECT COUNT(*) FROM (SELECT processInstanceId, processId, start_date, end_date, status, parentProcessInstanceId, outcome, duration, user_identity, processVersion, processName, correlationKey, externalId, processInstanceDescription FROM ProcessInstanceLog WHERE status = '1') "dbSQL"
13:49:34,637 ERROR [org.dashbuilder.dataset.backend.exception.ExceptionManager] (http-localhost/127.0.0.1:8080-8) Can't lookup on specified data set: jbpmProcessInstances: org.dashbuilder.dataset.exception.DataSetLookupException: Can't lookup on specified data set: jbpmProcessInstances
Caused by: java.sql.SQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.
Since David already fixed something in Dashbuilder, I'm assigning it to Neus to review the problematic query executed for console (notice the: WHERE status = '1' - status is numeric column, but it's treated as string here) from the error log.
Steps to reproduce:
Just start some process instance and go to process instances persp. to display it.
The query creation has been modified using Integer instead String.
The commits related:
The issue has been fixed! Verified with BPM Suite 6.2.0 CR2