Select field descriptions over JDBC in Google Apps Script

Question

I'm trying to select all table fields in a database and grab their descriptions. This can be achieved as shown here: SQL Server: Extract Table Meta-Data (description, fields and their data types)

That code works fine in SSMS but errors if used in Google Apps Script, returning:

The "variant" data type is not supported. (line 31, file "Code")

How can I get this code to execute correctly? Thanks.

Have you tried to use " and + instead of \ in your query ? Like : "SELECT u.name + '.' + t.name AS [table]," + "td.value AS [table_desc]," + ... Not sure, just trying to help. — Pierre-Marie Richard, May 09 '17 at 11:54
Maybe by casting your columns as varchar ? See this [SO question](http://stackoverflow.com/questions/34675258/com-microsoft-sqlserver-jdbc-sqlserverexception-the-variant-data-type-is-not) — Pierre-Marie Richard, May 09 '17 at 13:48

score 1 · Accepted Answer · answered May 09 '17 at 15:17

Got it working in the end thanks to Pierre-Marie Richard. Code below:

function getFieldList() {

  var conn = Jdbc.getConnection(connParams);
  var stmt = conn.createStatement();

  var results = stmt.executeQuery("SELECT     CAST(t.name AS NVARCHAR(128)) AS [table], \
                                              CAST(td.value AS NVARCHAR(128)) AS [table_desc], \
                                              CAST(c.name AS NVARCHAR(128)) AS [column], \
                                              CAST(cd.value AS NVARCHAR(128)) AS [column_desc] \
                                  FROM        sysobjects t \
                                  INNER JOIN  sysusers u \
                                      ON      u.uid = t.uid \
                                  LEFT OUTER JOIN sys.extended_properties td \
                                      ON      td.major_id = t.id \
                                      AND     td.minor_id = 0 \
                                      AND     td.name = 'MS_Description' \
                                  INNER JOIN  syscolumns c \
                                      ON      c.id = t.id \
                                  LEFT OUTER JOIN sys.extended_properties cd \
                                      ON      cd.major_id = c.id \
                                      AND     cd.minor_id = c.colid \
                                      AND     cd.name = 'MS_Description' \
                                  WHERE t.type = 'u' \
                                  ORDER BY    t.name, c.colorder");   

  var numCols = results.getMetaData().getColumnCount();

  var dataArray = [];

  while (results.next()) {
    var rowArray = [];
    for (var col = 0; col < numCols; col++) {
      rowArray.push(results.getString(col + 1));
    }
    dataArray.push(rowArray);
  }

  results.close();
  stmt.close();

  Logger.log(dataArray);

  return(dataArray);
}

Don't forget to put your answer as accepted, so people who go througt the same kind of problem can look for a solution. — Pierre-Marie Richard, May 09 '17 at 15:24

Select field descriptions over JDBC in Google Apps Script

1 Answers1