4

I am working with stored procedures in Snowflake. I want to know how to safely check that there are columns in a resultSet before running getColumnValue() which errors if I try to call it on a non-existent column. If I run this

var query = `SELECT * FROM somewhere`
var result = snowflake.execute({sqlText: query});
var count = result.getColumnCount();

I get an error saying that getColumnCount is not a function. If I run

var query = `SELECT * FROM somewhere`
var result = snowflake.execute({sqlText: query});
result.next();
var count = result.getColumnCount();

I get the same error.

EDIT: I took some advice and tried

var query = `SELECT * FROM somewhere`
var stmt = snowflake.createStatement({sqlText: query});
var result = stmt.execute();
var colCount = stmt.getColumnCount();

but I sometimes get an error saying ResultSet is empty or not prepared, call next() first so I tried

var query = `SELECT * FROM somewhere`
var stmt = snowflake.createStatement({sqlText: query});
var result = stmt.execute();
result.next()
var colCount = stmt.getColumnCount();

but I get the same error. I am assuming in those cases ResultSet might be empty but I would have thought colCount would == 0.

So either the developers haven't implemented a way to get this ahead of time or it isn't documented or I am missing something (the most likely).

Does anyone know of a way to check how many columns are in a row of a result set (or check that a result set has any columns at all) in Snowflake without throwing an error?

aaron
  • 537
  • 2
  • 6
  • 17
  • Not an answer, but a suggestion: never use `SELECT *` as it makes your code very fragile and hard to debug. Always request columns explicitly, so if someone deletes a column or renames one, your code explicitly breaks and throws errors. Otherwise, with '*', it will 'continue to work', giving the incorrect results. Also, explicit columns make it easier to future developers (or future yourself) to understand what happens in this code. – IvanD Nov 11 '20 at 00:28
  • 1
    Thanks for the suggestion... Though I am sure there are cases where `SELECT *` is necessary. I would push back on your use of the word never. Regardless I wanted to make a generic example of a case where it would be useful to be able to ask how many columns are in a result set. – aaron Nov 11 '20 at 01:52
  • https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful – IvanD Nov 12 '20 at 02:02

1 Answers1

3

getColumnCount() is a method of a Statement, not of a ResultSet.

Instead of:

var query = `SELECT * FROM somewhere`
var result = snowflake.execute({sqlText: query});
var count = result.getColumnCount();

Do:

var query = `SELECT * FROM somewhere`
var stmt = snowflake.createStatement({sqlText: query});
var result = stmt.execute();
var col_count = stmt.getColumnCount();
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • Thank you for the reply! This gets me much closer I think, but I am still having an issue where when I run stmt.getColumnCount(); I sometimes get an error where it says `ResultSet is empty or not prepared, call next() first`. I tried calling `result.next()` before calling `stmt.getColumnCount()` but I still get the same error. I am assuming in those situations that result set is in fact empty... so I guess I need a way to check that before trying to get the columnCount as well? – aaron Nov 11 '20 at 01:45
  • Make sure to call stmt.execute() first – Felipe Hoffa Nov 11 '20 at 02:07
  • I did, see my updated question. I am running var query = `SELECT * FROM somewhere` var stmt = snowflake.createStatement({sqlText: query}); var result = stmt.execute(); result.next() var col_count = stmt.getColumnCount(); and still getting that error – aaron Nov 11 '20 at 02:16
  • I can't reproduce the error - even if I have a "LIMIT 0" to make the resultset come out empty. – Felipe Hoffa Nov 11 '20 at 06:40