MATLAB Answers

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

11 views (last 30 days)
Scott
Scott on 7 Dec 2020
Commented: Tim Darrah on 23 Aug 2021
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?
  1 Comment
Tim Darrah
Tim Darrah on 15 Jul 2021
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?

Sign in to comment.

Answers (2)

Tim Darrah
Tim Darrah on 19 Jul 2021
Edited: Tim Darrah on 19 Jul 2021
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.
  1 Comment
Tim Darrah
Tim Darrah on 23 Aug 2021
@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;
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.

Sign in to comment.


Tim Darrah
Tim Darrah on 19 Jul 2021
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by