How to update array inside a database

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?)

[Micke Malmström]

The column has the data type (varbinary(max),null)

[Micke Malmström]

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

Answer 1

[the cyclist]

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.

[Micke Malmström]

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 

Answer 2

[Micke Malmström]

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)

[Micke Malmström]

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...