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

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