How to update array inside a database

1 view (last 30 days)
Micke Malmström
Micke Malmström on 29 Sep 2021
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 Comments
Micke Malmström
Micke Malmström on 29 Sep 2021
The column has the data type (varbinary(max),null)
Micke Malmström
Micke Malmström on 7 Oct 2021
The step 2 (point 7) in https://se.mathworks.com/help/database/ug/microsoft-sql-server-jdbc-windows.html#bt5dhtz helped me fixing the JDBC connection by finding the right port to use

Sign in to comment.

Answers (2)

the cyclist
the cyclist on 29 Sep 2021
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 Comment
Micke Malmström
Micke Malmström on 29 Sep 2021
Edited: Micke Malmström on 29 Sep 2021
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

Sign in to comment.


Micke Malmström
Micke Malmström on 6 Oct 2021
Edited: Micke Malmström on 6 Oct 2021
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 Comment
Micke Malmström
Micke Malmström on 7 Oct 2021
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...

Sign in to comment.

Tags

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!