CODEWITHSUNDEEP
sql-servergoogle-apps-scriptjdbcazure-sql-databasemssql-jdbc
Tom
Tom

Reputation: 453

Google Apps Script JDBC connection to Azure SQL db - type mapping problem

I am trying to connect from Google Apps Script (standalone script now for testing) to my Azure SQL database, using Apps Script JDBC Service.

// connection string (dbUrl): jdbc:sqlserver://...
var conn = Jdbc.getConnection(dbUrl, user, userPwd); 
var stmt = conn.createStatement();
var rst = stmt.executeQuery('SELECT [ID], [DatetimeColumn], [Datetime2Column], [DateColumn], [TimeColumn] FROM [gscript].[testTable]')
// etc.

I am experiencing problems with type mapping of some (newer?) SQL server date/time data types: datetime2, date, time. These are mapped and obtained in my script (rst.getObject) as NVARCHAR, i.e. as a string (confirmed by resultset metadata too, see bellow).

Only datetime works - mapped as java.sql.Timestamp.

When checking database and resultset metadata, getting this:

Resultset - columns with SQL server data type datetime2, date, time:

getColumnClassName: java.lang.String, getColumnType: -9, getColumnTypeName: nvarchar

Database - driver info:

getDriverName: Microsoft SQL Server JDBC Driver 2.0 / getDriverVersion: 2.0.1803.100 / getDriverMajorVersion: 2 / getDriverMinorVersion: 0 getJDBCMajorVersion: 4 / getJDBCMinorVersion: 0

Can anyone help?

Is the problem in the low driver version? Can a newer driver be installed somehow?

(My DBeaver desktop app is showing Microsoft JDBC Driver for SQL Server (MSSQL) version 7.4.1, no type mapping problems with the same table.)

Or is it necessary to avoid the mentioned data types and use only datetime instead?

Or is a better practice to stay with NVARCHAR and parse the data in the script?

Edit:

After some further research I found this in SQL Server T-SQL reference: "Some down-level clients don't support the time, date, datetime2, and datetimeoffset data types..." It seems it is describing my problem. By the question is, can this be overcome somehow in Google Apps Script?

Upvotes: 0

Views: 417

Answers (1)

TheAddonDepot
TheAddonDepot

Reputation: 8974

Based on your findings, it looks like Apps Script's built-in service is limited to JDBC 2.0.

If that's the case it can only support DATE, TIME and TIMESTAMP data types from JDBC 1.0/2.0 (see documentation). So relatively newer SQL Server data types won't be supported.

There are a few ways you can handle this.

You can change the date/time fields of your MSSQL tables to use more compatible data types, but that might mean refactoring your database and any existing code that depends on it.

A less intrusive approach is to create stored procedures on your database server that transforms those data types into formats that App Script's JDBC service can handle and invoke the stored procedures instead of querying the tables directly.

Alternatively, you can also try using the getter methods on the JdbcResultSet object to forcefully cast the incoming data to a date/time type (not sure if this would work but its worth a shot).

Upvotes: 1

Related Questions