How to getArray from JdbcResultSet in google app script?

Question

I am able to loop though and get results to an array from jdbc result, but I am wondering how to do this with getArray().

var results = stmt.executeQuery("select col1, col2 from sometable;");
var arr1 = results.getArray("col1");

Also, how to just dump or inspect the results object in google app script ?

Answer 1

Try the following code. It's based in this example from the official docs:

https://developers.google.com/apps-script/guides/jdbc#read_from_the_database

  //Execute query
    var results = stmt.executeQuery("select col1, col2 from sometable;"); 
  
  //Generate a 2d array to insert in spreadsheet
  var array2d = [];
  
  //Generate array with titles                               
  var numCols = results.getMetaData().getColumnCount();
  var titles = [];
  
  for (var col = 0; col < numCols; col++){
    var colName = results.getMetaData().getColumnLabel(col + 1);
    titles.push(colName);
  }
  
  //Insert titles in 2d array
  array2d.push(titles);
  
  //Extract results from query and insert insert into array2d
  
  while (results.next()) {
    //This is an array with data from each row
    rowData = [];
    for (var col = 0; col < numCols; col++) {      
          rowData.push(results.getString(col + 1));
    }
    //Insert data from each row in 2d array
    array2d.push(rowData);
  }
  
  //close  
  results.close();
  
  Logger.log(array2d);

  SpreadsheetApp.getActiveSheet()
  .getRange(1, 1, Object.keys(array2d).length, array2d[0].length)
  .setValues(array2d)

Answer 2

You may want to first check Class JdbcArray and the documentation of this class.

As discussed in java.sql.Array,

The Array interface provides methods for bringing an SQL ARRAY value's data to the client as either an array or a ResultSet object. If the elements of the SQL ARRAY are user defined data type (UDT), they may be custom mapped.

To create a custom mapping, you must do two things:

• create a class that implements the SQLData interface for the UDT to be custom mapped.
• make an entry in a type map that contains
  • the fully-qualified SQL type name of the UDT
  • the Class object for the class implementing SQLData

After which, you can then check Class JdbcResultSet and its documentation.

Lastly, Guide to JDBC might also help.