Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
3 replies
Subscribers
5 subscribers
Views
1752 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
Integrations
I am getting below error while configuring Query Database Smart Service:-
vineeta6579
over 10 years ago
I am getting below error while configuring Query Database Smart Service:-
• The database returned ARV's that may not be supported. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \\'\\'\\', \\'\\'\\' at line 1
When I am passing static parameters it works but on passing activity variable it shows above error.
My-sql DB Version: 5.6.16
Appian Version: 7.5
Mysql JDBC Version: mysql-connector-java-5.1.31-bin.jar
...
OriginalPostID-113905
OriginalPostID-113905
Discussion posts and replies are publicly visible
Parents
0
Eduardo Fuentes
Appian Employee
over 10 years ago
You are getting this erro because of the parameters of LIMIT which are ac!'s in this case. As far as I know mysql-connector-java-5.x is more strict in following the documentation of MySQL which states that the LIMIT function only accepts one or two numeric constants. In the case of this model the query is:
SELECT `processid` FROM `absencedetails` where `mainprocessid`=ac!employeeID LIMIT ac!start , ac!Batch
which when subject to validation/execution translates into:
SELECT `processid` FROM `absencedetails` where `mainprocessid`=ac!employeeID LIMIT "" , ""
this is invalid as the parameters for "LIMIT" are empty strings, not a number, thus the error: ".... for the right syntax to use near ''', "" at line x".
If this is what is happening for you then it's because the driver is telling Appian the ac! has to be "Number (Integer)" but when it builds the prepared statement it expects "Text" so it ignores the "Number (Integer)" we are passing and passes an empty string. Assuming this is the issue you are seeing you can consider redesign to use something different for example BETWEEN to limit the amount of data based on a column or try an older driver such as mysql-connector-java-3.x-bin.jar
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
Reply
0
Eduardo Fuentes
Appian Employee
over 10 years ago
You are getting this erro because of the parameters of LIMIT which are ac!'s in this case. As far as I know mysql-connector-java-5.x is more strict in following the documentation of MySQL which states that the LIMIT function only accepts one or two numeric constants. In the case of this model the query is:
SELECT `processid` FROM `absencedetails` where `mainprocessid`=ac!employeeID LIMIT ac!start , ac!Batch
which when subject to validation/execution translates into:
SELECT `processid` FROM `absencedetails` where `mainprocessid`=ac!employeeID LIMIT "" , ""
this is invalid as the parameters for "LIMIT" are empty strings, not a number, thus the error: ".... for the right syntax to use near ''', "" at line x".
If this is what is happening for you then it's because the driver is telling Appian the ac! has to be "Number (Integer)" but when it builds the prepared statement it expects "Text" so it ignores the "Number (Integer)" we are passing and passes an empty string. Assuming this is the issue you are seeing you can consider redesign to use something different for example BETWEEN to limit the amount of data based on a column or try an older driver such as mysql-connector-java-3.x-bin.jar
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
Children
No Data