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;
}
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:
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.
functionis not recognised.