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

问题描述

我正在使用SQL Server 2008 R2，并试图使用Google Apps脚本查询它。我正在使用其中一个应该填充电子表格的建议脚本，但是我遇到了连接SQL的问题。

我检查了指定的端口是正确的，所以我怀疑问题是，我真的很感谢确认我的连接字符串是正确的。

这是代码 -

$ b $ p $
$ b $ p $ > 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（）））;


感谢您的帮助！

解决方案

我认为你的连接字符串格式不正确。我相信你需要在服务器名/实例之前有一个 sqlserver：// 。

我没有方便的SQL Server实例，但是我测试了以下针对SQL Azure的代码，并且它运行良好。我能够从名为helloworld的表中选择数据并将其写入电子表格。

我用占位符取代了我的凭据。

 函数readAzure（）{
 var conn = Jdbc.getConnection（jdbc：sqlserver：//XYZ.database .windows.net：1433;的databaseName = MYDATABSENAME， USERNAME， 密码）; 
 var stmt = conn.createStatement（）; 
 var rs = stmt.executeQuery（select * from helloworld）; 
 
 var doc = SpreadsheetApp.create（'azure'）; 
 var cell = doc.getRange（'a1'）; 
 var row = 0; （rs.next（））{
 cell.offset（row，0）.setValue（rs.getString（1））; 
 cell.offset（row，1）.setValue（rs.getString（2））; 
 row ++; 
} 
 rs.close（）; 
 stmt.close（）; 
 conn.close（）; 
} 
  

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.

Here's the code -

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()));

Thanks for your help!

解决方案

I think your Connection string is slightly malformed. I believe you need a sqlserver:// before your servername/instance.

I don't have a SQL Server instance handy but I tested the following code against SQL Azure and it worked well. I was able to select data from a table called helloworld and write it to a spreadsheet.

I've replaced my credentials with placeholders.

function readAzure() {
  var conn = Jdbc.getConnection("jdbc:sqlserver://XYZ.database.windows.net:1433;databaseName=MYDATABSENAME","USERNAME","PASSWORD");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("select * from helloworld");

  var doc = SpreadsheetApp.create('azure');
  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));
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
}

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