2

I've built a CloudPage which runs SQL queries quickly without having to use automation studio.

The SSJS that runs the query works perfectly, but I'm also trying to use this function to also update a data extension to use as an audit log of it being used. It's supposed to simply update a data extension called Query Log. It just has four columns, Target, Type, Definition, andRunDate. The data extension is set up and all fields are nullable, but I can't get it to run. It doesn't give me an error that I can see and the page loads fine, but it doesn't add a record to the data extension. This is the code:

<script>
    Platform.Load("Core","1");
var api = new Script.Util.WSProxy();
var target = Variable.GetValue("@target");
var type = Variable.GetValue("@type");
var qdef = Variable.GetValue("@definition");
/*Build DE Object*/
var updateObject = {
    CustomerKey: 'QueryLog',
    Properties: [
        {
            Name: 'Target'
            Value: target
        },
        {
            Name: 'Type'
            Value: type
        },
        {
            Name: 'QueryDef'
            Value: definition
        },
        {
            Name: 'RunDate'
            Value: Platform.Function.Now()
        }
    ]
};

var options = {SaveOptions:[{'PropertyName':'*',SaveAction:'UpdateAdd'}]};
var res = api.updateItem('DataExtensionObject',updateObject,options);
</script>
0

2 Answers 2

2

I'd put a try/catch around the whole thing and see what you get. You're also missing the runat attribute on your script tag.

<script runat="server">
Platform.Load("Core","1");

try {

    var api = new Script.Util.WSProxy();
    var target = Variable.GetValue("@target");
    var type = Variable.GetValue("@type");
    var qdef = Variable.GetValue("@definition");

    /*Build DE Object*/
    var updateObject = {
    CustomerKey: 'QueryLog',
    Properties: [
        {
            Name: 'Target'
            Value: target
        },
        {
            Name: 'Type'
            Value: type
        },
        {
            Name: 'QueryDef'
            Value: definition
        },
        {
            Name: 'RunDate'
            Value: Platform.Function.Now()
        }
    ]
    };

    var options = {SaveOptions:[{'PropertyName':'*',SaveAction:'UpdateAdd'}]};
    var res = api.updateItem('DataExtensionObject',updateObject,options);

} catch (e) {
    Write("<br>e: " + Stringify(e));
}
</script>
1
  • Thanks. First I forgot the commas in the name-value pairs, so I put those back in, and the script runat. Ran it with the try/catch but it didn't give me an explanation - the Stringify types nothing.
    – TCassa
    Nov 29, 2018 at 10:34
1

To dig into your issue, I did a few debugging items to write it out and find the solution. I found the following that could be what was happening:

Main issue: Your WSProxy was hitting an error, but since you were not outputting or handling the response, you did not know since the page would still load as error responses in WSProxy do not throw a 500 error.

Potential Cause of Error: You were setting your variable for SSJS for the query definition as qdef but then calling it to fill into the WSProxy payload as definition. This could result in a null value there, which if its a non-null field could toss an error.

For reference on what I did to find this issue, please see the below code I created to debug the issue:

%%[
FOR @i=1 TO 4 DO

SET @target = CONCAT("myDE_",@i)
SET @type = "Update"
SET @QueryDef = CONCAT("MyQueryDef",@i)
]%%

<script runat="server">
Platform.Load("Core","1");

    var api = new Script.Util.WSProxy();
    var target = Variable.GetValue("@target");
    var type = Variable.GetValue("@type");
    var qdef = Variable.GetValue("@QueryDef");

    /*Build DE Object*/
    var updateObject = {
    CustomerKey: 'QueryLog',
    Properties: [
        {
            Name: 'Target',
            Value: target
        },
        {
            Name: 'Type',
            Value: type
        },
        {
            Name: 'QueryDef',
            Value: qdef
        },
        {
            Name: 'RunDate',
            Value: Platform.Function.Now()
        }
    ]
    };

    var options = {SaveOptions:[{'PropertyName':'*',SaveAction:'UpdateAdd'}]};
    var res = api.updateItem('DataExtensionObject',updateObject,options);
    var response = Stringify(res);

</script>
<hr>
%%=v(@i)=%%<br><br>
Response: <ctrl:var name="response" /><br><br>
AMP_Target: %%=v(@Target)=%%<br>
AMP_Type: %%=v(@Type)=%%<br>
AMP_QueryDef: %%=v(@QueryDef)=%%<br>
SSJS_Target: <ctrl:var name="target" /><br>
SSJS_Type: <ctrl:var name="type" /><br>
SSJS_QueryDef: <ctrl:var name="qdef" /><br>

%%[
NEXT @i
]%%

When I made the adjustments above to utilize qdef instead of definition (assuming it is a primary key/non nullable) the upsert worked as expected: enter image description here

My advice is to when developing or debugging anything with WSProxy or the API to always write out the response you receive to validate that it is the page/script and not the API that has the issue.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .