How do I update a JDBC SQLite database?

4 vues (au cours des 30 derniers jours)
Kevin Roberts
Kevin Roberts le 21 Août 2019
Commenté : Kojiro Saito le 17 Fév 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 commentaires
Kevin Roberts
Kevin Roberts le 22 Août 2019
Thanks for the advice. Updated the original post with text instead of images.
Guillaume
Guillaume le 22 Août 2019
I've edited the post to make it more readable.

Connectez-vous pour commenter.

Réponses (1)

Kojiro Saito
Kojiro Saito le 23 Août 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 commentaires
seu seu
seu seu le 14 Fév 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 le 17 Fév 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.

Connectez-vous pour commenter.

Produits

Community Treasure Hunt

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

Start Hunting!

Translated by