Statemente update sqlite matlab problem

Hello guys, I'm trying to use the command execute to make an update to a sqlite database. I'm on 2022b version, so I can't use sqlupdate command.

If I write this code it works:

statementUpdate = ["UPDATE acquisition_table SET model = '" + app.model + "' WHERE ID = 3 "];

execute(conn,statementUpdate) obviously after having estabilished a connection with my database.

If you notice, in the the statement there is app.model. It is a variable which is updated trhough app designer (model is a property) and so I can write that value modified in my database. I'd like to do the same with ID, which I also have set like a property, but I don't know how to modify that statement. It's probably something related to the position of quotes or the apostrophe, I don't know. I need this because that ID is a filter I use to enter in my database, and it changes when I do some stuffs in my app desinger interface. So having it like a fixed value (WHERE ID = 3 for example) is completely useless for me.

Edit: I forgot to say that statement has been suggested to me, so I have doubt also about the use of + symbol

Thanks guys.

Answer 1

[Rik]

This seems a case where you want to compose a string from several elements.

You can do this with the string datatype (as you have done here). The benefit is that this will automagically convert numbers to string before concatenating the elements if you use +, while char will be treated as a number if you use +.

Another option is to move to sprintf. That way you have control over the exact format used.

app.model = "some_name";
app.ID = 3;
statementUpdate = "UPDATE acquisition_table SET model = '" + app.model + "' WHERE ID = 3 "

statementUpdate = "UPDATE acquisition_table SET model = 'some_name' WHERE ID = 3 "

statementUpdate = sprintf('UPDATE acquisition_table SET model = ''%s'' WHERE ID = %d',app.model,app.ID)

statementUpdate = 'UPDATE acquisition_table SET model = 'some_name' WHERE ID = 3'

As you can see, both return the same thing, just a different datatype.

One last thing: I thought you should provide the SQLite string datatype with " instead of ' (and that you need to finish every statement with a semicolon). That might cause the underlying problem with this statement.

statementUpdate = sprintf('UPDATE acquisition_table SET model = "%s" WHERE ID = %d;',app.model,app.ID)

[Massimo]

Thank you for answer, but is not however clear to me how to manage the code. I need to concatenate more informations. For the command SET I need to change the value not only of model, but also of other parameters. For example:

statementUpdate = sprintf('UPDATE acquisition_table SET model = "%s" Type_of_maneuvers WHERE ID = %d;',app.model,app.ID)

That Type_of_maneuvers where have I to collocate inside the code? (I wrote there just to show you it). Obviuosly for that info I'll have another parameter to set app.ManeuversType

Edit: however I don't want to write a string as you wrote. I need to update sqlite dabatase.

[Rik]

Isn't it the goal to prepare a SQLite statement and then execute it with execute(conn,statementUpdate)?

And what is unclear about the documentation of sprintf? After reading it, it should be relatively easy to see how you can adjust the format specification to suit your needs. I suspect simply adding %s and adding app.ManeuversType as an input argument in the sprintf call should do the trick.

I don't have much experience with SQLite databases (only the level required to develop/test sqlite3), so I don't know what the correct syntax would be. However, your current question seemed to be how to compose the command to be executed, not how to find out what the correct command would be. For that last question you should probably look for a forum specific to SQLite or SQL.

[Massimo]

Thanks for the help Rik. I managed to solved it. I write an example:

statementUpdate = ["UPDATE table SET Model = '" + app.model + "',Type_of_maneuvers = '" + app.ManeuversType + "' WHERE ID = " + app.filter.ID];
execute(conn,statementUpdate);

Thank you again

[Rik]

You're welcome.

You should know that you can omit those square brackets when creating the string scalar. Since you use the + operator to concatenate the elements, you don't need the brackets to do anything.

[Massimo]

Ah ok, thanks for the information.

[Massimo]

@Rik thanks again for the help. It helped me a lot.

I have another problem now. I'm trying to do more or less the same thing, but creating a statement to use for a fecth command. I always need to use variables through something like app.model (property of appdesigner). However I write the statement it is not correct. I'm surely making some errors with syntax.

Thanks again

[Rik]

If you don't show your code, I will not be able to help you. It will help if you also show the output of size() and class() for each of the variables/properties you want to use.

[Massimo]

You're right.

First of all, I managed to write a statement which the fetch command can execute. I write you it:

sqlquery = ['Select * FROM acquisition_table WHERE Type_of_maneuvers = ''', app.ManeuvValue,''''];

app.ManeuValue it's a property I set on app designer, so that I can update its value if necessary. What I'm trying to do now (and I don't know if it's possible), is to concatenate more infos inside sqlquery statement. What I need is to specify not just one WHERE information, but more than one.

With the UPDATE statement I could write this to concatenate more SET command:

"UPDATE acquisition_table SET mat_dynamics_RAW = '" + app.MatDynamicsRaw + "',mat_dynamics_Translated = '" + app.MatDynamicsTranslated + "' WHERE ID = " + app.filter.Var1;

Unfortunately I'm not able to do the same thing inside my sqlquery I wrote you at the beginning of this anwser.

Thanks @Rik

[Rik]

This sounds more like a question about SQLite syntax. What is the actual text you want to compose? Does SQLite even support what you want to do?