Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to read and save data from SQL code activity

Abhishek_Kumar_Keshri
Level 1
Level 1

Hi,

 

I try query def in js but  JS code giving an error

Need JS to read and save output coming from below SQL.

 

Workflow ImageWhatsApp Image 2021-02-04 at 10.10.19.jpeg:-

 

SQL code activity contains:-

SELECT relname,

last_vacuum,
FROM pg_stat_all_tables

WHERE schemaname = 'common'

ORDER BY last_vacuum DESC;

 

Thanks & Regards 

Abhishek Keshri

 

 

 

 

1 Accepted Solution
saikatk2447661
Correct answer by
Level 4
Level 4

Hi @Abhishek_Kumar_Keshri ,

 

Sql code activity does not create temp table so you can not read the result of select statement in this way. Delete the sql code activity and just use query function in js block and run it in js activity. You can read the content of any table in the database this way.

 

https://docs.adobe.com/content/help/en/campaign-classic/technicalresources/api/m-DBEngine-query.html...

 

Thanks,

Saikat

View solution in original post

4 Replies
craigthonis
Employee
Employee

Hi Abhishek,

 

Using the querydef is the correct approach.  Can you provide us with the error that you were seeing with the JS code along with the code itself?

 

Regards,

Craig

Abhishek_Kumar_Keshri
Level 1
Level 1

Hi Craigthonis,

 

Here is the error which I am getting:- 

errorjs.PNG.

 

Below is the JS code that I am using for the same 

 

var query=xtk.queryDef.create(
<queryDef schema="temp:sql4" operation="select">
<select>
<node expr="@relname"/>
<node expr="@last_vacuum"/>
<node expr="@last_autovacuum"/>
<node expr="@last_analyze"/>
<node expr="@last_autoanalyze"/>
</select>
</queryDef>
);
var res = query.ExecuteQuery();

for each(var result in res){
logInfo(res.sql.@relname);
logInfo(res.sql.@last_vacuum);
logInfo(res.sql.@last_autovacuum);
logInfo(res.sql.@last_analyze)
logInfo(res.sql.@last_autoanalyze);
}

 

Thanks & Regards 

Abhishek Keshri

 

saikatk2447661
Correct answer by
Level 4
Level 4

Hi @Abhishek_Kumar_Keshri ,

 

Sql code activity does not create temp table so you can not read the result of select statement in this way. Delete the sql code activity and just use query function in js block and run it in js activity. You can read the content of any table in the database this way.

 

https://docs.adobe.com/content/help/en/campaign-classic/technicalresources/api/m-DBEngine-query.html...

 

Thanks,

Saikat

View solution in original post