sqlwrite broken in R2020b vs R2020a for date/time types?

sqlwrite behaves differently in R2020a vs R2020b.

R2020b produces errors when handling datetime types, saying this sort of thing:

    Error using database.internal.utilities.TypeMapper/dataTypeConverter/transformCharData (line 289)
    MyDateTime2 column value must be a cell array of character vectors, string array or categorical array.
    
    Error in database.internal.utilities.TypeMapper.dataTypeConverter (line 160)
                                    transformCharData();
    
    Error in database.odbc.connection/sqlwrite (line 172)
    transformedTable = database.internal.utilities.TypeMapper.dataTypeConverter(conn,coltypes,data);

Whereas R2020a behaves fine.

As a test, I created a test table in a SQL Server database with three colums:

    MyDatetime (datetime)
    MyDatetime2 (datetime2(7))
    MyDate (date)

I created a MATLAB table with three columns of the same names as the database table and assigned NaT to each.

  >> mytable = table(NaT, NaT, NaT, 'VariableNames', {'MyDateTime', 'MyDateTime2', 'MyDate'})

mytable =

  1×3 table

    MyDateTime    MyDateTime2    MyDate
    __________    ___________    ______

       NaT            NaT         NaT  

When writing to this database from R2020a, it works:

>> sqlwrite(conn, 'MyTestTable', mytable)
>> 

When writing to this database from R2020b, it insists the dates must be represented as character strings:

>> sqlwrite(conn, 'MyTestTable', mytable)
Error using database.internal.utilities.TypeMapper/dataTypeConverter/transformCharData (line 289)
MyDateTime2 column value must be a cell array of character vectors, string array or categorical array.

Error in database.internal.utilities.TypeMapper.dataTypeConverter (line 160)
                                transformCharData();

Error in database.odbc.connection/sqlwrite (line 172)
transformedTable = database.internal.utilities.TypeMapper.dataTypeConverter(conn,coltypes,data);
 
289                     error(message("database:database:TextColumnData",col_varname));

Digging in a bit, it appears that when the database.internal.utilities.TypeMapper.dataTypeConverter enumerates the types of the database columns, it misinterprets the SQL datetime2 and date types as textual.

I couldn't find anything in documentation or release notes to explain the difference. Help?

[Tim Darrah]

using R2020a

I was debugging this because milliseconds drop off and the unique constraint on the datetime column in the postgresql database triggers.. If using jdbc, I edited this file this file

toolbox\database\database\+database\+jdbc\@connection\sqlwrite.m

% line 126, original file code
 [data,columntypes] = database.internal.utilities.TypeMapper.matlabToDatabaseTypes(data,conn.DatabaseProductName);
% additional code
% fprintf(columntypes)
if ismember('timestamp', columntypes)
    idx = find(strcmp(columntypes, 'timestamp'))
    columntypes(idx) = 'timestamp(6)';
end

Then I realized I was editing +jdbc, yet im using an OBDC driver. The file is different, and the column types return an array of ints. the number 93 is for whatever they are calling the datetime column, what is the number for the equivalent datatype that keeps miliseconds?

Error in matlab code that doesn't properly handle sql datatype of "timestamp(6)" or "timestamptz(6)" is definitely true.

@MathWorks Support Team, whats the fix?

Answer 1

[Tim Darrah]

In the case of dropping milliseconds,

the file

C:\Program Files\MATLAB\R2020a\toolbox\database\database\+database\+internal\+utilities\TypeMapper.m

had the following lines starting at 169 - notice they now have a .SSS in them. When I return the table, it correctly shows a cell matrix with the datetime with milliseconds.

if isdatetime(coldata)
    if enumsqltypes(i) == 93
        transformeddata.(col_varname).Format = 'MM-dd-uuuu HH:mm:ss.SSS';
        if isa(conn,'database.jdbc.connection')
            if ~strcmpi(coldata.Format,'MM-dd-uuuu HH:mm:ss.SSS')
                transformeddata.(col_varname).Format = 'MM-dd-uuuu HH:mm:ss.SSS';
            end
        end
    end

When I let the entire sqlwrite() script execute, this line always errors no matter what

insertStmt.insertData(stmt, querybuilder.preparedInsert(columnnames,tablename).SQLQuery, transformedTable);    

Error in function sqlwrite() at line 173. <<< ive edited this file some in my debugging efforts, your line number might be slightly different >>>

Error Message:

ODBC Driver Error: ERROR: duplicate key value violates unique constraint "flight_telemetry_tb_dt_key"

DETAIL: Key (dt)=(2021-07-16 12:18:15) already exists.;

Error while executing the query

However,

>> res{2,1} % this is the transformedTable that I'm returning in the catch block
ans =
    '07-16-2021 12:18:15.250'    

shows the datetime having the millisecond values. But in the above error (generated by postgresql, and returned thru matlab), it does not show the millisecond values.

If milisecond values were being passed, then a duplicate key error would look like this (a contrived example):

ERROR: duplicate key value violates unique constraint "flight_telemetry_tb_dt_key"

Detail: Key (dt)=(2021-07-19 00:14:47.25) already exists.

Therefore, milisecond values are still being dropped.

@MathWorks Support Team, help?

[Tim Darrah]

@MathWorks Support Team, bump. How to prevent miliseconds from dropping?

% connect to the database
conn = db_connect('nasadb');
% write the data to the database
sqlwrite(conn, 'flight_telemetry_tb', flight_telemetry_tb);
conn.commit();
conn.close();
clear conn;

Error using database.odbc.connection/sqlwrite (line 245)

ODBC Error: ODBC Driver Error: ERROR: duplicate key value violates unique constraint "flight_telemetry_tb_dt_key"

DETAIL: Key (dt)=(2021-08-22 18:38:03) already exists.;

Error while executing the query.

Answer 2

[Tim Darrah]

In the case of the OP, the same file is likely a good place to look to make the edits necessary for what you are trying to do. The files

C:\Program Files\MATLAB\R2020a\toolbox\database\database\+database\+preparedstatement\+internal\mapSQLToMatlabTypes.m

and

C:\Program Files\MATLAB\R2020a\toolbox\database\database\+database\+odbc\+internal\+utilities\matlabTypeSelector.m

might also be of interest.