How to insert and retrieve text with special characters from SQLite Matlab interface?

40 次查看（过去 30 天）
Abel Szkalisity
Abel Szkalisity 2020-2-3
评论： Abel Szkalisity 2020-6-22
I have been struggling recently a lot with trying to insert and retrieve text from a database with SQLite connection with special characters as demonstrated with the code below (it should run as it is). The characters are all in the Unicode range 0-255.
Not only the characters are not correct upon retrieval from the database, even the number of them is different.
I suspect that the problem is somewhere during retrieval (i.e. fetch), as when I checked my db with an external program (DB Browser for SQLite) it showed the string correctly.
Could anyone correct the code below, or suggest how to do it otherwise?
Many thanks for your help in advance.
//Note: I'm sticking a bit to the SQLite interface instead of the JDBC driver. With JDBC connection this issue did not occur, however I'd like to access the database parallely, and the JDBC driver caused quite some issues there. //
%% Test database
% Set character encoding to UTF-8
slCharacterEncoding('UTF-8'); % I tried also without this, no effect
% Initialization
dbLocation = 'myTestDB.db';
rng(11950911);
% Check if the database needs to be created or simple connect to it
if ~exist(dbLocation,'file')
conn = sqlite(dbLocation,'create');
else
conn = sqlite(dbLocation);
end
% Creating a table named "Objects"
conn.exec('DROP TABLE IF EXISTS Objects;');
conn.exec(['CREATE TABLE Objects ( ' ...
'ObjectID INTEGER PRIMARY KEY, ' ...
'Data TEXT'...
');']);
% Creating test data
someTextWithWeirdChars = char((randi(255,1,100)));
% Create table from the input data for inserting
T = table({someTextWithWeirdChars},'VariableNames',{'Data'});
% Insert it into the Objects table
conn.insert('Objects',{'Data'},T);
% Fetching it back
result = conn.fetch('SELECT Data FROM Objects');
% In SQLite interface the result is a cellarray
resText = result{1};
if strcmp(resText,someTextWithWeirdChars)
disp('Yes, the text was correctly recovered.');
else
disp('No, something went wrong');
end

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

采纳的回答

Christoph Schmid
Christoph Schmid 2020-6-19
I now use a workaround: mksqlite
Special characters are saved and retrieved correctly, NULL values are supported, BLOB data is saved and retrieved correctly (not so with the sqlite interface)
  1 个评论
Abel Szkalisity
Abel Szkalisity 2020-6-22
Thanks for pointing out this package, it indeed works very well! I suggest to use this by anyone facing the same problem.
Note: another workaround that I figured out meanwhile was to convert any uint8 texts into an array of only prinatble characters. Naturally this lengthens the array but it works with the built-in SQLite interface.

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

产品


版本

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by