Results from DB are truncated

I am encountering a strange limitation in retreteiving the results from SQL database.

So I'm able to store 500000 characters (probably could save more but didn't had the need to test it) in DB without any issue. I can verify this by executing SELECT LEN(Value) FROM my_test_table.

The issue is when I try to read the data I get a table and length(data.Value{1}) is 3999, no metter if I use select, fetch, readall,...

Does anyone know how to remove this limit of 3999 charaters and read all the chars from that field in DB?

[Ognjen]

BTW I'm using odbc driver:

odbc("Driver={ODBC Driver 17 for SQL Server}...

[Ognjen]

% Our table is defined as:
% CREATE TABLE [ogi].[TestTable]([Data] [nvarchar](max) NOT NULL)

% Connect to the SQL server DB database using ODBC
% PLEASE modify the connection string to point to your own MS SQL server DB
conn = odbc("Driver={ODBC Driver 17 for SQL Server};Server=tcp:<SERVER_URL_OR_IP>,1433;Database=<DATABASE>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated");
tableName = "ogi.TestTable";

% Create a long string
N = 500000;
longText = char(zeros(1, N) + 'A');

% write data to DB
inserQuery = sprintf("INSERT INTO [%s] (Data) VALUES ('%s')", tableName, longText);
execute(conn, inserQuery);

% Everything works just fine up till this point. If you check DB now you
% should see the correct entry in DB with all chars written. The issue
% starts when we want to retreive the data from DB

% read data from DB
query = sprintf("SELECT Data, LEN(Data) as LenInDb FROM [%s]", tablename);

res = fetch(conn, query);
% This doesn't work either
%res = select(conn, query);

% Display results
disp('Result from DB:');
numOfRows = numel(res.Data);
for i = 1:numOfRows
    disp([strlength(res.Data{i}), res(i, "LenInDb").LenInDb]);
end

% Close connection
close(conn);

[Ognjen]

I have included the minimal code example to help reproduce this issue. You will have to have an SQL server thoutgh. In case you don't have it this might be a quick way to set it up: microsoft-mssql-server - Official Image | Docker Hub

Does anyone have any idea why strlength(res.Data{i}) is 3999, while res(i, "LenInDb").LenInDb shows the correct length of 500000?

And if N is smaller than 3999, then both will show correct length. This poses a huge issue since I'm trying to write an JSON string to DB and it's being truncated, so I can't deserialize it in MATLAB. Again INSERT INTO works fine, and DB definetly contains the full string.

[Strider]

I tried to recreate your problem in sqlite, out of convenience, and I was unable to.

Try this example and see if it is giving you the same issue.

% ---- my additions
clearvars
dbfile = 'tstdb.db';
if isfile(dbfile); delete(dbfile); end

conn = sqlite(dbfile,'create');
tableName = "TestTable";
sq = sprintf("CREATE TABLE %s(Data char)",tableName);
% ---- my additions


execute(conn,sq)
% Create a long string
N = 500000;
longText = char(zeros(1, N) + 'A');
% write data to DB
inserQuery = sprintf("INSERT INTO [%s] (Data) VALUES ('%s')", tableName, longText);
execute(conn, inserQuery);

% Everything works just fine up till this point. If you check DB now you
% should see the correct entry in DB with all chars written. The issue
% starts when we want to retreive the data from DB
% read data from DB
query = sprintf("SELECT Data, length(Data) as LenInDb FROM [%s]", tableName);
res = fetch(conn, query);
% This doesn't work either
%res = select(conn, query);
% Display results
disp('Result from DB:');

Result from DB:

numOfRows = numel(res.Data);
for i = 1:numOfRows
    disp([strlength(res.Data{i}), res(i, "LenInDb").LenInDb]);
end

   500000   500000

% Close connection
close(conn);

Answer 1

[James Anderson]

I think the issue here is related to ODBC, I beleive if you switch to JDBC your problem will go away.

I can't particularly find any generic issues with ODBC and data truncation online, so I think this is a specific Matlab ODBC integration issue.