CODEWITHSUNDEEP
sql-serverjdbcgoogle-apps-script
wgw
wgw

Reputation: 601

connection error for a mssql jdbc connection

I connect remotely to my database with Squirrel (with jTDS), so I think I have my parameters set properly. GAS gives me systematically an error: "failed to establish a database connection". Any way to debug that bad connection? My code is from the GAS JDBC example, but I am using mssql.

Any suggestions?

Note: I tried the following as well, because that us how I enter it in Squirrel: 

  var conn = Jdbc.getConnection("jdbc:sqlserver:///my.server.adress:2433/MyName", "MyName", "password");

Stuck!

function foo() {
  var conn = Jdbc.getConnection("jdbc:sqlserver:///my.server.adress:2433", "MyName", "password");
  var stmt = conn.createStatement();
  stmt.setMaxRows(100);
  var start = new Date();
  var rs = stmt.executeQuery("select * from MyTable");

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

Upvotes: 0

Views: 1053

Answers (3)

wgw
wgw

Reputation: 601

This is the complete function (it works!), with USE: 

  function typestring_f() {
  var conn = Jdbc.getConnection("jdbc:sqlserver://my.server.adress:2433", "MyName", "password");
  var stmt = conn.createStatement();

  var ret = stmt.execute("USE wgwDatabase;");

  pstmt=conn.prepareStatement("{ call f_dict_sp(?)}");  // get the stored procedure with 1 parameter.
  pstmt.setString(1, "cat%"); // set the parameter (a string for matching with Like )
  pstmt.setMaxRows(100);
  rs= pstmt.executeQuery(); // run the query

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getActiveRange(); // ac('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();
  pstmt.close();
  conn.close();

 }

Upvotes: 0

Ikai Lan
Ikai Lan

Reputation: 2240

You can specify the database name in the connection String as well:

https://developers.google.com/apps-script/jdbc

var conn = Jdbc.getConnection("jdbc:mysql://:/", "user", "password");

This should preclude the need to specify a database name.

Upvotes: 0

wgw
wgw

Reputation: 601

Ok, silly mistake: extra slash in the connection string.

It also looks like I have to use qualified names. So instead of MyTable, I have to use AccountName.DatabaseName.MyTable

But it works!

Upvotes: 1

Related Questions