How to read and save data from SQL code activity | Community
Skip to main content
Abhishek_Kumar_Keshri
Level 2
Feb 4, 2021 9:32 AM UTC
Solved

How to read and save data from SQL code activity

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 Image:-

 

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

 

 

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
saikatk2447661
saikatk2447661Accepted solution
Level 4
Feb 10, 2021 5:44 PM UTC

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?hl=cnx

 

Thanks,

Saikat

View reply

4 replies

saikatk2447661
saikatk2447661Accepted solution
Level 4
Feb 10, 2021 5:44 PM UTC

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?hl=cnx

 

Thanks,

Saikat

Translate
    Abhishek_Kumar_KeshriAuthor
    Level 2
    Feb 15, 2021 7:30 AM UTC
    Thanks Saikat .
    Translate
    Craig_Thonis
    Craig_Thonis
    Employee
    Feb 4, 2021 2:01 PM UTC

    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

    Translate
    Abhishek_Kumar_KeshriAuthor
    Level 2
    Feb 5, 2021 5:30 PM UTC

    Hi Craigthonis,

     

    Here is the error which I am getting:- 

    .

     

    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

     

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...