通过 JDBC 使用 Google Apps 脚本查询 SQL Server [英] Querying SQL Server with Google Apps Script via JDBC

问题描述

我正在使用 SQL Server 2008 R2 并尝试使用 Google Apps 脚本对其进行查询.我正在使用应该填充电子表格的建议脚本之一，但我在连接 SQL 时遇到问题.

I'm using SQL Server 2008 R2 and am trying to query it using Google Apps Script. I'm using one of the suggested scripts that's supposed to populate a spreadsheet but i'm having problems connect with SQL.

我已经检查了我指定的端口是正确的，所以我怀疑问题出在哪里，我非常感谢确认我的连接字符串是正确的.

I've checked the port i've specified is correct so I doubt the issue is that, i'd really appreciate confirmation that my connection string is right.

这是代码 -

服务器/实例:1433；数据库名称=xxxx；用户=xxxx；密码=xxxx")；

server/instance:1433;databaseName=xxxx;user=xxxx;password=xxxx");

function foo() {
  var conn = Jdbc.getConnection   ("jdbc:sqlserver/instance:1433;databaseName=xxxx;user=xxxx;password=xxxx");
  var stmt = conn.createStatement();
  stmt.setMaxRows(100);
  var start = new Date();
  var rs = stmt.executeQuery("select top 1000 from SOP10100");

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getRange('a1');
  var row = 0;
  while(rs.next()) {
    cell.offset(row, 0).setValue(rs.getString(1));
    cell.offset(row, 1).setValue(rs.getString(2));
    cell.offset(row, 2).setValue(rs.getString(3));
    cell.offset(row, 3).setValue(rs.getString(4));
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
  var end = new Date();
  Logger.log("time took: " + (end.getTime() - start.getTime()));

感谢您的帮助！

推荐答案

我在 GitHub 上有一个完整的项目是一个 Mysql &SQL Server JDBC 连接应用程序.你可以在这里找到它:GitHub 项目.我已经在下面发布了相关代码.SQL Server 案例是您为连接实例化参数的地方，下面是连接的其余部分.要获得完整的工具，请使用 GitHub 项目.

I have an entire project on GitHub that is a Mysql & SQL Server JDBC connection app. You can find it here: GitHub Project . I have posted the related code below. The the SQL Server case is where you instantiate the parameters for your connection and then below is the rest of the connection. To get an entire tool use the GitHub Project.

  function readFromTable(queryType, queryDb, query, tab, startCell) {
  // Replace the variables in this block with real values.
  var address;
  var user;
  var userPwd ;
  var dbUrl;

  switch(queryType) {
    case 'sqlserver':
      address = '%YOUR SQL HOSTNAME%';
      user = '%YOUR USE%';
      userPwd = '%YOUR PW%';
      dbUrl = 'jdbc:sqlserver://' + address + ':1433;databaseName=' + queryDb;
      break;
    case 'mysql':  
      address = '%YOUR MYSQL HOSTNAME%';
      user = '%YOUR USER';
      userPwd = '%YOUR PW%';
      dbUrl = 'jdbc:mysql://'+address + '/' + queryDb;
    break;
  }

  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var start = new Date();
  var stmt = conn.createStatement();
  var results = stmt.executeQuery(query);
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetTab = sheet.getSheetByName(tab);
  var cell = sheetTab.getRange(startCell);
  var numCols = results.getMetaData().getColumnCount();
  var numRows = sheetTab.getLastRow();
  var headers ;
  var row =0;

  clearRange(tab,startCell,numRows, numCols);


  for(var i = 1; i <= numCols; i++){
    headers = results.getMetaData().getColumnName(i);
      cell.offset(row, i-1).setValue(headers);
      }

  while (results.next()) {
    var rowString = '';
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '	';
      cell.offset(row +1, col).setValue(results.getString(col +1 ));
    }
    row++
    Logger.log(rowString)
  }

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

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

这篇关于通过 JDBC 使用 Google Apps 脚本查询 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！