MATLAB Answers

0

How do I update a JDBC SQLite database?

Kevin Roberts さんによって質問されました 2019 年 8 月 21 日
最新アクティビティ Kojiro Saito
さんによって コメントされました 2019 年 8 月 24 日
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 件のコメント

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

サインイン to comment.

1 件の回答

Kojiro Saito
Answer by 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)?

  2 件のコメント

Thanks Kojiro, that worked a treat. I do feel a bit of an idiot now for missing the blindingly obvious.
Glad to hear it solved!

サインイン to comment.



Translated by