How to update array inside a database

3 次查看（过去 30 天）
Micke Malmström
Micke Malmström 2021-9-29
评论： Micke Malmström 2021-10-7
How can i update the contents of one row of ColumnOfInterest (see below) with a new array?
conn = database('DB'....);
data = fetch(conn,'SELECT * FROM TableOfInterest WHERE ID = 1');
head(data)
% ans =
% 1x2 table
% ID ColumnOfInterest
% __ ________________
% 1 {8000×1 uint8}
data.ColumnOfInterest{1}=int16(zeros(16000,1)); % insert new data
% Have also tried data={int16(zeros(16000,1))}; and even tried not to
% modify pass the same data without changeing anything back in there again with no luck
colnames = 'ColumnOfInterest';
whereclause = ['WHERE ID = 1'];
update(conn,'TableOfInterest', colnames, data, whereclause);
% %
but I always get either of the responce below:
Error using database.odbc.connection/update (line 157)
Variable fields and insert fields do not match.
Error using database.odbc.connection/update (line 301)
Input structure must contain fields of type double or cell
Error using database.odbc.connection/update (line 301)
Invalid input value at row 1, column 1. Expected binary.
Error using database.odbc.connection/update (line 99)
The value of 'data' is invalid. Input data must be a uint8, int8, uint16, int16, uint32, int32, uint64, int64, single, double matrix, cell array, structure or table
Am I trying something imposible?
(I read somwhere that a JDBC connection might work better, but I cant seem to be able to connect to my MS SQL Server using the JDBC interface. should I spend time on trying to etablish the connection trhough JDBC instead?)

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

回答（2 个）

the cyclist
the cyclist 2021-9-29
I don't fully know the answer, but here are a couple thoughts.
I highly doubt you need to explore JDBC. If ODBC works for fetching info, it should work for everything.
I speculate that the problem is that you are trying to insert a variable of type int16 into a field that is defined as uint8. Just as an experiment, I would try updating the database field with exactly the same field that you just selected from it. That way you could eliminate some uncertainty from the update.
  1 个评论
Micke Malmström
Micke Malmström 2021-9-29
编辑：Micke Malmström 2021-9-29
Thanks, I have tried
data = fetch(conn,query);
update(conn,'TableOfInterest',fieldnames(data),data,whereclause);
Error using database.odbc.connection/update (line 157)
Variable fields and insert fields do not match.
and
update(conn,'TableOfInterest','ColumnOfInterest',data,whereclause);
Error using database.odbc.connection/update (line 157)
Variable fields and insert fields do not match.
and
update(conn,'TableOfInterest','ColumnOfInterest',data(1,2),whereclause);
Error using database.odbc.connection/update (line 301)
Input structure must contain fields of type double or cell
and
NewData=table(data.ColumnOfInterest{1},'VariableNames','ColumnOfInterest');
update(conn,'TableOfInterest','ColumnOfInterest',NewData,whereclause);
Error using database.odbc.connection/update (line 301)
Input structure must contain fields of type double or cell

请先登录，再进行评论。

Micke Malmström
Micke Malmström 2021-10-6
编辑：Micke Malmström 2021-10-6
I think Ive found half the solution.
When I read out the responce from the database I have to convert the varbin that is interpetd as uint8 into int16 with the typcast function like:
data.ColumnOfInterest=swapbytes( typecast( data.ColumnOfInterest,'int16'));
The swapbytes is necessary to convert the little-endian output to big-endian (or vice versa), see help typecast/swapbytes for more info.
I have not had the chance to test writing the data back to the database but i doubt it will work to just go backwards... since I couldnt even write the the un-altered data back in there.
(to be continued)
  1 个评论
Micke Malmström
Micke Malmström 2021-10-7
It turned out to be only one quarter of the solution... now I realize that I get a truncated arry into the matlab workspace... I konw the database has an aray of >4000 values but the result is alays the first 4000 values (after the converision from the 8000x1 uint8 as above).
In the database explorer I also see 8000x1 uint8 as a result but when I use the JDBC connection to the same database I see that there are 40004x1 uint8 values... however if I then use the fetch function to quirey the table I only get 8000x1 uint8 in the workspace. what is even stranger is that if I in the Database Explorer press the button Import Data I get all the 40004x1 uint8 values in the workspace...

请先登录，再进行评论。

类别

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

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by