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

5 次查看（过去 30 天）
Scott
Scott 2020-12-7
评论： Tim Darrah 2021-8-23
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 个评论
Tim Darrah
Tim Darrah 2021-7-15
编辑：Tim Darrah 2021-7-15
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?

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

Tim Darrah
Tim Darrah 2021-7-19
编辑：Tim Darrah 2021-7-19
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?
  1 个评论
Tim Darrah
Tim Darrah 2021-8-23
@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.

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by