CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platformprocedure
Antulii
Antulii

Reputation: 65

Execution error in statement.execute of Snowflake procedure

Trying to create a procedure with JavaScript, which takes in search terms and outputs the columns from the schema where the search term is found.

However, I land up an error which is somewhat difficult to understand what is happening, any leads would be helpful

Execution error in stored procedure SEARCH_VALUE: SQL compilation error: syntax error line 1 at position 7 unexpected 'START'. syntax error line 1 at position 13 unexpected 'FROM'. At Statement.execute, line 38 position 11

The error arises particularly in the

var statement2 = snowflake.createStatement({
        sqlText: search_col
       })
var result_set2 = statement2.execute();

I am new to this topic and totally clueless, where is the START here in the SQL? and why is it unexpected FROM ? Moreover when I tried the same code with a different database it worked without any problem. Any leads would be helpful. Thanks a lot.

The code:

create or replace procedure search_value(SEARCH_SCHEMA varchar, SEARCH_TERM varchar)
  returns variant
  language javascript
  as
  $$
    var search_schema = SEARCH_SCHEMA ;
    var search_term = SEARCH_TERM ;
    var result_set1 = "" ;
    var result_set2 = "" ;
     
    // Create temp table to insert the search results in.
    snowflake.createStatement({sqlText: "create or replace temporary table \
        SEARCH_RESULTS (db varchar, schema varchar, \
        table varchar, col varchar, col_val varchar)"}).execute();
    
    var get_columns = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + search_schema + "';" ;
    
    var statement1 = snowflake.createStatement({
       sqlText: get_columns
    });


    try
    {
    var result_set1 = statement1.execute();

    }
    
    catch(err){return "error "+err;}
    
   
    while(result_set1.next())
    {
        var db_ = result_set1.getColumnValue(1);
        var schema_ = result_set1.getColumnValue(2);
        var table_ = result_set1.getColumnValue(3);
        var column_ = result_set1.getColumnValue(4);
       
        
        var search_col = "SELECT " + column_ +  " FROM " + db_ + "." + schema_ + "." + table_ + " WHERE CONTAINS(" + column_ + ", '" + search_term + "');" ;
        
        var statement2 = snowflake.createStatement({
                sqlText: search_col
            });

        var result_set2 = statement2.execute();
}
     return result_set2;
   
  $$
  ;
 
call search_value('SNOWFLAKE_SAMPLE_DATA', 'requests');

Upvotes: 0

Views: 734

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

I see that the name of the SP is different (in the error message). The above SP fails with a different error:

SQL compilation error:
syntax error line 1 at position 94 unexpected 'table'.
At Statement.execute, line 10 position 56 (line 196)

After fixing the above error, and checking the logic of the SP, I see that your generated SQL tend to produce errors.

For example, if one of the table has START column then you generate this SQL:

SELECT START FROM GOKHAN_DB.PUBLIC.V_SNOP_SALESBYMONTH WHERE CONTAINS(START, 'requests');

And it will fail with the error message you mentioned. To avoid this error, you may surround the column with double quotes:

    var search_col = 'SELECT "' + column_ +  '" FROM ' + db_ + "." + schema_ + "." + table_ + ' WHERE CONTAINS( "' + column_ + `", '` + search_term + "');" ;

Upvotes: 1

Related Questions