CODEWITHSUNDEEP
snowflake-cloud-data-platform
user16595206
user16595206

Reputation: 176

Snowflake JS Proc

 create or replace procedure test_09172(c_custkey varchar(25)
     ,c_mktsegment varchar(25)
     ,cname varchar(25)) returns string not null   language javascript   execute as owner   as   $$
      var sqlquery="";
       var fltConvUomPK="";
       var fltConvFactorPK="";
       var ParentClass="";
       var VMAJOR="";
       var VMINOR="";
              try   {    
         var sql_command  =`SELECT C_ADDRESS,C_NATIONKEY
                        from customers                  
                        WHERE c_custkey=C_CUSTKEY and c_name=CNAME and C_MKTSEGMENT=C_MKTSEGMENT`;
                                 var rs=snowflake.createStatement( {sqlText: sql_command});
     
       var result_set1 = rs.execute();
            }
     catch(err)
     {
     return err.message
     }
     return rs.getSqlText();   $$;

While executing "call test_09172('537289','FURNITURE','Customer#000537289');"

I am getting below error.

JavaScript execution error: Uncaught TypeError: Cannot read property
 'getSqlText' of undefined in TEST_09172 at ' return rs.getSqlText();'
 position 14 stackstrace: TEST_09172 line: 28

Please help me on this to fix

Upvotes: 0

Views: 131

Answers (2)

Gokhan Atil
Gokhan Atil

Reputation: 10039

The error seems related to an undefined object, but your code worked without any errors when I tried to reproduce it.

I noticed that you do not bind your parameters to your SQL:

var sql_command  =`SELECT C_ADDRESS,C_NATIONKEY
from customers                  
WHERE c_custkey=C_CUSTKEY and c_name=CNAME and C_MKTSEGMENT=C_MKTSEGMENT`;

SQL is not case-sensitive, so you just compare the columns with themselves (c_custkey=C_CUSTKEY and C_MKTSEGMENT=C_MKTSEGMENT). c_name=CNAME will probably produce an error.

To avoid confusion between the column and parameter names, I rewrote the query:

create or replace procedure test_09172(c_custkey_p varchar(25)
,c_mktsegment_p varchar(25)
,c_name_p varchar(25)) returns string not null   language javascript   execute as owner   as   $$
var sqlquery="";
var fltConvUomPK="";
var fltConvFactorPK="";
var ParentClass="";
var VMAJOR="";
var VMINOR="";
      try   {    
         var sql_command  =`SELECT C_ADDRESS,C_NATIONKEY
                      from customers                    
                      WHERE c_custkey=? and c_name=? and C_MKTSEGMENT=?`;

         var rs=snowflake.createStatement( {sqlText: sql_command , binds:[ C_CUSTKEY_P, C_NAME_P , C_MKTSEGMENT_P ] });
    }
catch(err)
{
return err.message
}
return rs.getSqlText();   
$$;

On my tests, it works as expected but I don't have your data so you should test it.

Upvotes: 1

Sergiu
Sergiu

Reputation: 4578

The method getSqlText is available for the Statement object not ResultSet, see here:

getSqlText()

This method returns the text of the prepared query in the Statement object.

Upvotes: 0

Related Questions