CODEWITHSUNDEEP
javascriptjsonsnowflake-cloud-data-platform
user1810575
user1810575

Reputation: 831

Execute JS/JSON function in Snowflake with syntax error

Not sure what's wrong with below function but here is the error message

SQL compilation error: syntax error line 1 at position 0 unexpected 'function'.

JSON Script or Javascript?

function rsToJSON(query) {
var i;
var row = {};
var table = [];
while (query.resultSet.next()) {
    for(col = 1; col <= query.statement.getColumnCount(); col++) {
        row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
    }
    table.push(row);
}
return table;
}

Upvotes: 1

Views: 174

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11046

I recognize that code fragment from my blog post here: https://snowflake.pavlik.us/index.php/2021/01/22/running-dynamic-sql-in-snowflake/.

TL;DR version of this is you need to run this contextualized with the other code, especially the part that shows how to handle errors.

That code fragment needs to be contextualized and run with a required Query class (albeit a trivial one).

The query parameter is an instantiation of that simple class. It requires a constructor with a SQL statement in order to create it. In that query object, if there is an error running the SQL you can retrieve it like this:

class Query{
    constructor(statement){
        this.statement = statement;
    }
}
 
var out = {};
var query = getQuery(sqlStatement);
if (query.error == null) {
    return rsToJSON(query);
} else {
    return {"error": query.error};
}

Then later on in the code sample there is a section of code that will add any error information to the Query class:

function getQuery(sql){
    var cmd = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd));
    try {
        query.resultSet = query.statement.execute();
    } catch (e) {
        query.error = e.message;
    }
    return query;
}

If you have that section of code before the rsToJSON function, it will explain what SQL error happened.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Not sure what's wrong with below function but here is the error message

We do not see entire code, but it is possible to define JavaScript functions inside stored procedure:

CREATE OR REPLACE PROCEDURE public.sp_test()
returns VARIANT
language javascript
as
$$
function rsToJSON(query) {
var i;
var row = {};
var table = [];
while (query.next()) {
    for(col = 1; col <= query.getColumnCount(); col++) {
        row[query.getColumnName(col)] = query.getColumnValue(col);
    }
    table.push(row);
}
return table;
}


var statement1 = snowflake.createStatement( {sqlText: 'SELECT * FROM PUBLIC.T'} );
var result_set1 = statement1.execute();

return  rsToJSON(result_set1);

$$
;

Call:

CREATE OR REPLACE TABLE PUBLIC.T(c,d) AS SELECT 1, 2  UNION SELECT 2, 3;

CALL PUBLIC.sp_test();

Output:

enter image description here

Upvotes: 2

Related Questions