Execution error in stored procedure : Unsupported type for binding argument 2undefined

Question

I am executing

CALL  LOAD_CUSTDIM_HOUSTONS:

This does the below:

1. I am running this code to get unique Customer Details such as Consumer No and Customer Name from STG_HOUSTON table and Get Max(CUST_DImKEY) from the DIM_CUSTOMER Key for the CUSTOMER Name from above i.e Houston's.

2. Next I am checking if the Max key value is null i mean no match for the CUSTOMER so Key value first time to be always 1 next time it will be max value + 1.

3. Once we get this bind this value and the select where clause will get the unique customer and insert into Customer Dimension table.

Stored procedure looks like this:

create or replace procedure LOAD_CUSTDIM_HOUSTONS()
    returns varchar not null
    language javascript
    as
    $$
    var return_value = "";
    
 try 
   {
    var SQL_SMT = "SELECT DISTINCT CONSUMER_NUMBER,CUSTOMER_NAME FROM STG_HOUSTONS ORDER BY CONSUMER_NUMBER ASC";
    var stmt1 = snowflake.createStatement(
           {
           sqlText: SQL_SMT
           }
                                        );
    /* Creates result set */
    var res1 = stmt1.execute(); 
     
    while (res1.next())  
    {
      var  var_ConsumNo=res1.getColumnValue(1);
      var  var_custname=res1.getColumnValue(2);
      return_value =" Inside While";        
      var_custname = "'" + var_custname + "'";
        var t_SQLstmt = " SELECT GET_CUSTDIMKEYS(?)" ;       

        var t_stmt = snowflake.createStatement ({sqlText: t_SQLstmt,binds: [var_custname]});
        
        var res2 = t_stmt.execute();
        res2.next();
        var_Custkey =res2.getColumnValue(1)
        return_value= return_value + var_Custkey;
        if (var_CustKey == null)
          {
            var_CustdimKey =1
          }
        else
          {
           var_CustdimKey=var_CustKey+1
          }
          return_value= return_value + var_CustdimKey.toString();
           var SQL_INSDIMCUS="INSERT INTO DIM_CUSTOMER(CUSTOMER_KEY,CUSTOMER_ID,CUSTOMER_NAME,CONSUMER_ID,CONSUMER_NAME,CUSTOMER_IND,TODATE)" +  " SELECT DISTINCT :1,  CUSTOMER_NUMBER,CUSTOMER_NAME,CONSUMER_NUMBER,CONSUMER_SHPPNG_FNAME + ' ' + CONSUMER_SHPPNG_LNAME AS CONSUMER_NAME, FALSE, NULL FROM STG_HOUSTONS WHERE CONSUMER_NUMBER = :2 ;";  
           
           var stmt = snowflake.createStatement({sqlText: SQL_INSDIMCUS,binds: [var_CustdimKey,var_ConsumNo]} ); 
           var res3 = stmt.execute();  
                                
           result = "COMPLETED SUCCESSFULLY!";
     }                                     
 }              
 catch (err)  
 {    result = "Failed!";
      var time_st = snowflake.execute( {sqlText: `SELECT CURRENT_TIMESTAMP;`} );
      snowflake.execute({
      sqlText: `insert into PROC_ERROR_LOG 
                 VALUES (?,?,?,?,?)`
      ,binds: ['LOAD CUSTOMER DIMENSION HOUSTONS',err.code, err.state,err.message, err.stackTraceTxt]
      });
 }
    return return_value;
    $$
    ;

This is the function I am using it to get max(DIM CUST KEY).

create or replace function GET_CUSTDIMKEYS ( CUSTOMERNAME varchar )
returns bigint
as 'Select max(CUSTOMER_KEY)
from PUBLIC.DIM_CUSTOMER
where CUSTOMER_NAME = CUSTOMERNAME ';

I tried with Binding variable / direct substitution of the values to dynamically create the SQL and execute it.

Using DBeaver21.1.1 IDE to create snowflake stored procedure and functions

This is the execution log Created by the IDE.

2021-10-05 20:44:48.030 - SQL Error [100183] [P0000]: Execution error in store procedure LOAD_CUSTDIM_HOUSTONS:
Unsupported type for binding argument 2undefined
At Snowflake.execute, line 49 position 14
org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [100183] [P0000]: Execution error in store procedure LOAD_CUSTDIM_HOUSTONS:
Unsupported type for binding argument 2undefined
At Snowflake.execute, line 49 position 14
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeStatement(JDBCPreparedStatementImpl.java:208)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:510)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:441)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:428)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:813)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3280)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4624)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Answer 1

The issue is first the second result set should have a while loop which was missing, And rest of the code proceeds will solve the issue and also we have to make sure the sql statement variables, resultset variables with in the procedures are not named the same or used multiple times. And also declare or initialize variables used in the stored procedure

lessons learnt by me and happy learning.

var var_Custkey=0; 

    while (res2.next())
    {
      
            var_Custkey =res2.getColumnValue(1)
            return_value= return_value + var_Custkey;
            if (var_CustKey == 0)
              {
                var_CustdimKey =1
              }
            else
              {
               var_CustdimKey=var_CustKey+1
              }
              return_value= return_value + var_CustdimKey.toString();
var SQL_INSDIMCUS="INSERT INTO 
DIM_CUSTOMER(CUSTOMER_KEY,CUSTOMER_ID,CUSTOMER_NAME,CONSUMER_ID,CONSUMER_NAME,CUSTOMER_IND,TODATE)" +  " SELECT DISTINCT :1,  CUSTOMER_NUMBER,CUSTOMER_NAME,CONSUMER_NUMBER,CONSUMER_SHPPNG_FNAME + ' ' + CONSUMER_SHPPNG_LNAME AS CONSUMER_NAME, FALSE, NULL FROM STG_HOUSTONS WHERE CONSUMER_NUMBER = :2 ;";  
               
               var stmt = snowflake.createStatement({sqlText: SQL_INSDIMCUS,binds: [var_CustdimKey,var_ConsumNo]} ); 
               var res3 = stmt.execute();  
    }

Answer 2

This is in addition to Eric's answer. You should not wrap strings in single quotes when used as bind variables:

  var_custname = "'" + var_custname + "'";
    var t_SQLstmt = " SELECT GET_CUSTDIMKEYS(?)" ;       

    var t_stmt = snowflake.createStatement ({sqlText: t_SQLstmt,binds: [var_custname]});

This should be:

  //var_custname = "'" + var_custname + "'";
    var t_SQLstmt = " SELECT GET_CUSTDIMKEYS(?)" ;       

    var t_stmt = snowflake.createStatement ({sqlText: t_SQLstmt,binds: [var_custname]});

If you wrap a string type bind variable in single quotes, the single quotes become part of the variable. The practical effect is that instead of running a statement like this:

SELECT GET_CUSTDIMKEYS('MyCustomer'); -- Looks for MyCustomer

You run a statement like this:

SELECT GET_CUSTDIMKEYS('\'MyCustomer\''); -- Looks for 'MyCustomer'

Answer 3

Line 49 is:

      sqlText: `insert into PROC_ERROR_LOG 
                 VALUES (?,?,?,?,?)`
      ,binds: ['LOAD CUSTOMER DIMENSION HOUSTONS',err.code, err.state,err.message, err.stackTraceTxt]

The error message:

Unsupported type for binding argument 2undefined

means the variable passed into the binding argument is not defined.

This tells me that the variable "err" was not initialized properly. I have seen some cases that under certain conditions, like when calling a function that does not exist on an object, though exception is thrown, the argument passed into the catch block is still NULL.

You need to look into the query history for this particular session, and find out which one of the queries that ran from the SP failed, and find out why.

And in the catch block, try to check the "err" object before using its attributes, something like:

if (err) {
  snowflake.execute({
      sqlText: `insert into PROC_ERROR_LOG 
                 VALUES (?,?,?,?,?)`
      ,binds: ['LOAD CUSTOMER DIMENSION HOUSTONS',err.code, err.state,err.message, err.stackTraceTxt]
      });
} else {
  snowflake.execute({
      sqlText: `insert into PROC_ERROR_LOG 
                 VALUES (?,?,?,?,?)`
      ,binds: ['LOAD CUSTOMER DIMENSION HOUSTONS','my code', 'my state','my error', 'my stack']
      });
}

This can avoid such failure.