Snowflake Stored Procedures to generate unique IDs and then insert values into a table-CodePudding

I am using Snowflake stored procedures to try and log errors as they occur. The current stored proc has an if/else statement, but I'm just trying to get one section working for now since the code is the more or less the same with variations to the SQL statements.

create or replace Procedure PLog(
    PName varchar,
    CName varchar,
    PType varchar)
returns varchar
not null
language javascript

AS
$$
var cyc_id_sql = `SELECT case when count(CycleId) > 0 then max(CycleID)   1 else 1 end as CycleId from SCHEMA.TABLE1 where CycleName='${CName}'`;
var cycle_id_create = snowflake.createStatement({ sqlText: cyc_id_sql});
var cycleid = cycle_id_create.execute();
var p_id_sql = `Select case when count(ProcessId) > 0 then max(ProcessID)   1 else 1 end as ProcessId from SCHEMA.TABLE2 where ProcessName='${PName}'`;
var p_id_create = snowflake.createStatement({ sqlText: p_id_sql});
var processid = p_id_create.execute();
var insertValuesText = `INSERT INTO SCHEMA.TABLE
           (
            ProcessLogId
           ,CycleId
           ,ProcessId
           ,CycleName
           ,ProcessName
           ,ProcessType
           ,ProcessStatus
           ,StartTime)
     VALUES
           (default,${cycleid},${processid},'${value3}','${value4}','${value5}','Started',current_timestamp )`;

var insertValues = snowflake.createStatement({ sqlText: insertValuesText});
insertValues.execute();
$$

When I call this I get the error:

SQL compilation error: syntax error line 12 at position 28 unexpected 'Object'. syntax error line 12 at position 44 unexpected 'Object'. At Statement.execute, line 22 position 13

I think the issue is that the cycle and process statements create a Javascript object instead of the desired value. The desired value being the unique ID. When I run the SQL separately with an empty database I get 1, which is expected so I know the SELECT statements work. When I run and then call:

create or replace Procedure PLog(
    PName varchar,
    CName varchar,
    PType varchar)
returns varchar
not null
language javascript

AS
$$
var cyc_id_sql = `SELECT case when count(CycleId) > 0 then max(CycleID)   1 else 1 end as CycleId from SCHEMA.TABLE1 where CycleName='${CName}'`;
var cycle_id_create = snowflake.createStatement({ sqlText: cyc_id_sql});
var cycleid = cycle_id_create.execute();
return cycleid
$$

I get [object Object]. I'm not that familiar with JavaScript so any help would be appreciated. I think the issue is that my variables generate instances of an object instead of a usable value for the SQL statement, I just don't know how to fix the issue. Thank you!

CodePudding user response：

when you execute a statement var processid = p_id_create.execute(); processid is the result set, not the actual value you want. you need to fetch it

so here is some example from the doc's

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

so your small example should be:

create or replace Procedure PLog(
    PName varchar,
    CName varchar,
    PType varchar)
returns varchar
not null
language javascript

AS
$$
var cyc_id_sql = `SELECT case when count(CycleId) > 0 then max(CycleID)   1 else 1 end as CycleId from SCHEMA.TABLE1 where CycleName='${CName}'`;

var cycle_id_create = snowflake.createStatement({ sqlText: cyc_id_sql});
var result1 = cycle_id_create.execute();
result1.next();
var cycleid  result1.getColumnValue(1);
return cycleid
$$

CodePudding user response：

You are correct that it's returning a JavaScript object. As Simeon notes, this is returning a resultSet. If you're expecting a query to return a scalar (single row with a single column) you can use inline code to get it. However, it will not be modular and harder to read than modular code. This is a great example of where a helper function helps.

For example:

create or replace procedure TEST()
returns string
language javascript
as
$$

var my_scalar = getScalar("select C_NAME from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER limit 1");

return my_scalar;

function getScalar(sql){
    cmd = {sqlText: sql};
    stmt = snowflake.createStatement(cmd);
    var rs;
    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(1);
}

$$;

You can then use the getScalar helper function to collect scalar values you need. If you're only doing it once it won't matter too much. If you're collecting a few or dozens, then it will keep the code modular, compact, and more readable.