MATLAB Answers

How do I update a JDBC SQLite database?

16 views (last 30 days)
Kevin Roberts
Kevin Roberts on 21 Aug 2019
Commented: Kojiro Saito on 17 Feb 2020
I am trying to create a database (db), using SQLite and MATLAB. However, the only way I have managed to UPDATE a db row is to delete the row and then insert a new row with the updated information. The MATLAB update function seems (from reading help) to work only for Microsoft Access. Below are the print outs of my db connection handle and the before and after tables with the code ude to attempt the update. As you can see no update was performed.
>> dbConn
dbConn =
connection with properties:
DataSource: 'SQLiteProject'
UserName: ''
Driver: 'org.sqlite.JDBC'
URL: 'jdbc:sqlite:C:\Users\kevi ...'
Message: ''
Type: 'JDBC Connection Object'
Database Properties:
AutoCommit: 'on'
ReadOnly: 'off'
LoginTimeout: 0
MaxDatabaseConnections: 0
Catalog and Schema Information:
DefaultCatalog: ''
Catalogs: {}
Schemas: {}
Database and Driver Information:
DatabaseProductName: 'SQLite'
DatabaseProductVersion: '3.27.2'
DriverName: 'SQLite JDBC'
DriverVersion: '3.27.2.1'
>> getTable = ['SELECT * FROM ' tableName];
>> data = fetch(dbConn, getTable);
>> convertTableDates(data)
ans =
9×9 table
abtm aetm sbtm setm p_id esbt p_nbr p_nm p_upr
______ ______ ______ ______ ____ ______ _____ _________ _____
May 14 Jan 15 May 14 Dec 99 'P1' May 14 'W45' 'wheel' 3.25
Jan 15 Jun 15 May 14 Jan 15 'P1' May 14 'W45' 'wheel' 3.25
Jan 15 Dec 99 May 14 Oct 14 'P1' May 14 'W45' 'wheel' 3.25
>> update(dbConn,tableName,'esbt',{0},{'WHERE abtm = ''P1'''})
>> getTable = ['SELECT * FROM ' tableName];
>> data = fetch(dbConn, getTable);
>> convertTableDates(data)
ans =
9×9 table
abtm aetm sbtm setm p_id esbt p_nbr p_nm p_upr
______ ______ ______ ______ ____ ______ _____ _________ _____
May 14 Jan 15 May 14 Dec 99 'P1' May 14 'W45' 'wheel' 3.25
Jan 15 Jun 15 May 14 Jan 15 'P1' May 14 'W45' 'wheel' 3.25
Jan 15 Dec 99 May 14 Oct 14 'P1' May 14 'W45' 'wheel' 3.25
I've cropped the table for ease of reading. convertTableDates just converts the date columns in the return table from real numbers into human readbale dates.

  3 Comments

Kojiro Saito
Kojiro Saito on 22 Aug 2019
Could you write down your codes by "Insert a line of code"? The screenshots are hard to understand how you write your update command.
Kevin Roberts
Kevin Roberts on 22 Aug 2019
Thanks for the advice. Updated the original post with text instead of images.
Guillaume
Guillaume on 22 Aug 2019
I've edited the post to make it more readable.

Sign in to comment.

Answers (1)

Kojiro Saito
Kojiro Saito on 23 Aug 2019
Thank you for sharing the code. Your update command syntax seems to be fine.
I guess where clause might not be correct. Possibly, p_id =''P1'' is right instead of abtm = ''P1'''?
update(dbConn,tableName,'esbt',{0},{'WHERE p_id = ''P1'''})
If not, could you show the values of esbt and abtm columns as original data types (before converting from convertTableDates function)?

  4 Comments

Show 1 older comment
seu seu
seu seu on 14 Feb 2020
I donnot know why the reason heppens when I use "update" in Matlab to update SQlite database.
update(conn,'Login','password',{'ADMIN'},{'WHERE username = ''admin'''})
the mistake matlab give below:
Undefined function 'update' corresponding to input parameters of type 'sqlite'
Kojiro Saito
Kojiro Saito on 17 Feb 2020
Are you using JDBC driver for accessing the SQLite?
There are differences between just SQLite and SQLite via JDBC driver as described in this document. update can be used for database connection object but not for sqlite connection object. For sqlite, you can use insert or exec instead.

Sign in to comment.

Sign in to answer this question.


Translated by