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
9 replies
Subscribers
5 subscribers
Views
3432 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
Process
Dear all, I am trying to update some columns in a DB using a Query Da
octavianp
over 7 years ago
Dear all,
I am trying to update some columns in a DB using a Query Database Smart Service Task and I need to use the IN wildcard for the WHERE clause. Typically in my mind it should look like this UPDATE mytable SET row = 0 WHERE id IN (ac!oneVariable). The ac!oneVariable is a number(integer) MULTIPLE came from a process variable updated in a User Task. By implementing the above mentioned approach I get some weird alerts which I think are generated from hibernate : There was a problem executing the SQL query. SQL Message : [Ljava.lang.Long; cannot be cast to java.lang.Long Problem: An error occurred in executing an Activity Class.
I also tried to "inject" the contents of the pv!oneVariable in a text activity class having value: joinarray(pv!oneVariable, '","') but without any success.
Could you please help me with the best practices with this regard?
Thanks in advance,
Octavian
OriginalPostID-183225
OriginalPostID-183225
Discussion posts and replies are publicly visible
0
Vinod Bongoni
Certified Lead Developer
over 7 years ago
It will be good to use "Write to Data Store Entity" smart service, instead Query Database smart service.
forum.appian.com/.../Write_to_Data_Store_Entity_Smart_Service.html
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
@octavianp Just in case, if you haven't made a search already in the forum, the post at https://forum.appian.com/suite/tempo/entry/e-180895 looks similar to what you are looking for.
Re best practices, if you are asking with respect to the implementation of the native sql query, I am not much aware of it. If you are asking with respect to implementation of the smart services (query db or write to data store), then as said by other user, choosing Write to Data Store Entity would be good. But at the same time, as per my knowledge, there isn't any wrong in choosing 'Query DB' if the database vendor is not going to change frequently and the native SQL queries are straight and simple.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sureshrk
over 7 years ago
1. Create Process Variable
idList(Text, Multiple)
2. Create Activity Variable
ac!ids(Text)
3. Set activity Variable(ac!ids) to below definition
= "," & joinarray (pv!idList, ",") & ","
4. Add Query like one below
SELECT idColumn, nameColumn, descColumn FROM table1 WHERE INSTR(ac!ids, CONCAT(',', idColumn, ',')) > 0
5. Use the same logic (where clause) for update.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sureshrk
over 7 years ago
INSTR is MySQL function and you need to find the similar function for Oracle and MsSql
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
octavianp
over 7 years ago
Dear all,
First of all thank you very much for the advice it was indeed helpful. I used sureshrk advice with some modifications: for MsSQL the function is CHARINDEX and I used it like CHARINDEX(CONCAT(',', cast(idColumn as varchar(100)), ',', ac!ids)
Thanks,
Octav
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Venu
over 7 years ago
HI Octav,
i am facing the similar issue,
i need to select two columns from View and used where clause, in operator,
here the values for In operator comes from acp.
SELECT DISTINCT
status,ftn_Id
FROM
schema.my_view
WHERE service IN ('service1','service2')
AND INSTR(ac!ftn_Id, CONCAT(',', ftn_Id, ',')) > 0
AND ROWNUM =1
i am using Oracle.
Any suggestions.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Venu
over 7 years ago
my issue resolved...!
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
rickyc472
over 6 years ago
@venusaikumarc, I am facing same issue. Could you please tell me how did you solve issue?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Venu
over 6 years ago
@ricky, Refert this news post, it may help you.
forum.appian.com/.../e-255471
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel