This service will be undergoing maintenance at 00:00 UTC, 2017-10-23 It is expected to last about 30 minutes
Bug 1272842 - Sybase 15.7: "DataSetLookupException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed"
Sybase 15.7: "DataSetLookupException: Implicit conversion from datatype 'VARC...
Status: VERIFIED
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: Business Central (Show other bugs)
6.2.0
Unspecified Unspecified
urgent Severity urgent
: CR2
: 6.2.0
Assigned To: Neus Miras
Jan Hrcek
: TestBlocker
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-10-19 00:58 EDT by Jan Hrcek
Modified: 2015-12-02 10:55 EST (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
server.log with exceptions (793.39 KB, text/plain)
2015-10-19 00:58 EDT, Jan Hrcek
no flags Details

  None (edit)
Description Jan Hrcek 2015-10-19 00:58:09 EDT
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

How reproducible:
Always

Steps to Reproduce:
1. Deploy business-central with Sybase 15.7 as datasource
2. Navigate to Process instances perspective


Actual results:
Modal with error displayed

Expected results:
No error, data set is working

Additional info:
Comment 1 Jan Hrcek 2015-10-20 09:56:29 EDT
Sorry, missed a step to reproduce:
3. In Process instances create new Filter tab with: column Priority >= 3.
Comment 2 David Gutierrez 2015-10-20 11:16:00 EDT
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:

https://issues.jboss.org/browse/DASHBUILDE-58
Comment 3 David Gutierrez 2015-10-23 06:16:07 EDT
A fix has been submitted for approval.

(master)
https://github.com/dashbuilder/dashbuilder/pull/55

(0.3.x)
https://github.com/dashbuilder/dashbuilder/pull/56


They will be merge today by EOD.
Comment 5 Jan Hrcek 2015-11-09 02:13:00 EST
Verification blocked by bug # 1277466 
Will be verified with the next build after that bug is fixed.
Comment 6 Jan Hrcek 2015-11-19 03:10:50 EST
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.
Comment 8 Neus Miras 2015-11-23 04:07:26 EST
The query creation has been modified using Integer instead String.
The commits related:
6.3.x
https://github.com/droolsjbpm/jbpm-console-ng/commit/43facbbeb448f16e8c4c1085cbfc2ac00a2ae475

master:
https://github.com/droolsjbpm/jbpm-console-ng/commit/5a88172c5dfbabb3c9835abc02e154c536fb5444
Comment 9 Jan Hrcek 2015-12-02 10:55:41 EST
The issue has been fixed! Verified with BPM Suite 6.2.0 CR2

Note You need to log in before you can comment on or make changes to this bug.