Tomáš Grepl
Copying my question from StackOverflow.com:
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?