App Inventor Fusion Tables UPDATE

[AlexxandroP]

Good Afternoon. I need some help. I'm working on a simple app by App Inventor 2 and It has to insert, update, show and delete some data. I need to know the exact sintax of UPDATE and DELETE commands. 

Someone can explain me the error, please?

[Abraham Getzler]

Your using the '=' block in your query is wrong (but creative and interesting.)

SQL needs to see the ' = ' as part of the text of the SQL command.

You will need to quotify the text constant value.

Here's a great resource ...

http://puravidaapps.com/fusionerror.php

ABG

[AlexxandroP]

I saw that guide, but I can't find "quotify"... Where is it?

[Taifun]

first do the Pizza Party tutorial to learn how to work with fusiontables

Pizza Party with Fusion Tables for App Inventor 2

Taifun

---

Trying to push the limits of App Inventor! Snippets and Tutorials from Pura Vida Apps by Taifun.         

[AlexxandroP]

I watched that guide about 7 or 8 times. But there isn't the function UPDATE. 

[Abraham Getzler]

https://developers.google.com/fusiontables/docs/v1/sql-reference

Quotify means wrap in quotes ...   WHERE x = 'somevalue'
ABG

[Taifun]

I watched that guide about 7 or 8 times. But there isn't the function UPDATE. 

you asked where to find "quotify" ...

Taifun

[Taifun]

for UPDATE or DELETE statements you first have to get the rowid of the row to be updated or deleted with a SELECT query. Then in a second step do the UPDATE or DELETE

Taifun

[AlexxandroP]

Ok. I think I understand... Now I want to try. 

[AlexxandroP]

No... It doesn't work...

[Taifun]

Frequent Made Errors with Fusiontables and how to fix them

Taifun

[Yusuf Jk]

Hi Taifun,

                 Even i want the syntax for update and delete query. As my program is too big how do i get the row id for any table with a procedure?

[AlexxandroP]

Ok... I HOPE I understand (again...). Before I have to SELECT where ROWID ... and AFTER I have to UPDATE or DELETE. Is It right? 

But now I have the most difficult question... the sintax... 

SELECT col1, col2, col3, col4 FROM xxxxxxxxxxxxxxxxxxx WHERE ROWID = 'yyyyyyyyyyyyy' 

and...? 

Got.Result SET textbox1, textbox2,textbox3,textbox4 (or label??)

and where have I to insert the UPDATE or Delete query?

[Taifun]

@AlexxandroP: which columns do you have in your table and which row do you like to update?

the queries will look similar to these:

1. select query

SELECT ROWID FROM <tableid> WHERE Name = 'textbox1.text'

2. update query

UPDATE <tableid> SET Value = <textbox2.text> WHERE ROWID = 'rowid from select statement'

you also can learn from this example: https://puravidaapps.com/fusionDB.php#select

this example uses the web component, but the logic is the same: first do a select statement to get the rowid, then do an update statement

@Yusuf: please start a new thread and post a screenshot including Do it result of your query

Taifun

[AlexxandroP]

Thank You for the fast answer!!

The 1st query works perfectly. But where I have to write the 2nd query?

[Taifun]

you have to send the second query again to the fusiontable using the SendQuery method

It would really help if you provided a screenshot of your relevant blocks, so we can see what you are trying to do, and where the problem may be.

[AlexxandroP]

this is the what I've done...

[Taifun]

as already said, you need 2 queries

1. SELECT ROWID FROM <tableid> WHERE Codice = 'tb_barcode.text'

then you get the rowid in the fusiontables.Gotresult event and store it in a global variable rowid, now do a second query

2. UPDATE <tableid> SET Giacenca = '987654' WHERE ROWID = 'get global rowid'

as already said, you also can learn from this example: https://puravidaapps.com/fusionDB.php#select

Taifun

[Yusuf Jk]

No need for it now Taifun. You have already helped me by helping AlexxandroP. Thank You.

[AlexxandroP]

I suppose the blocks are corrects, but the function doesn't work. 

Do You know where is the error?

[Abraham Getzler]

I don't believe you should be putting quotes around column names.
How then would SQL know that you are talking about the contents of
a table column, versus a piece of text that happens to mention a column name?

ABG
 

[AlexxandroP]

Thank You. I deleted quotes but It doesn't work....

[Abraham Getzler]

http://puravidaapps.com/fusionerror.php

ABG
 

[Taifun]

use Do it to debug your blocks

see also the recommendations in this thread: https://groups.google.com/d/msg/mitappinventortest/2hH3oczGvRE/NUGH4ys2Uj4J

about the result you will get from the table...

Taifun

[Ketaki Rao]

Has anyone on this thread been able to get to UPDATE working the way AlexxandroP wants? From what I understood at this link, the API does not allow updates of cell values, and UPDATE is only to change a table's columns.

https://developers.google.com/fusiontables/docs/v1/reference/table/update

I was unable to get Fusion Tables updating with UPDATE, and switched my real-time data to TinyWebDB for this reason. I continue to use Fusion Tables for data that is static or insert-only.

[Taifun]

@Ketaki: sure, UPDATE works fine, you just have to use the correct syntax...

For UPDATE statements the first step to be done is to get the ROWID of the row to be updated with a SELECT statement. The second step is to do the UPDATE.

see also here https://developers.google.com/fusiontables/docs/v1/sql-reference#updateRow

Taifun

[AlexxandroP]

I think my blocks are correct, but It doesn't work again... Now I'm desperate. I only want to see my simple app working!!!

Someone wants to see my aia file?

[Ghica]

Hi Allexxandro,

Sofar I do not know Fusion tables and I do not know what the definition of your table is, but I do know SQL. It seems that you are quoting the row id, the price and the quantity. You should not do that because they probably are defined as numeric things. Only text and strings must be quoted. With the price you should take care whether it should contain . or , as a decimal separator.

Cheers, Ghica

[Taifun]

see screenshot and adjusted aia file

Taifun

[AlexxandroP]

Thank You for your help!! At this moment the app doesn't work again but tonight I want to try to verify the blocks. Now the sintax is right and the error will be minimum, I suppose. Tomorrow I can say you if It works or not.

Thanks again!!

[AlexxandroP]

I'm trying to update data from about a week but It doesn't works... Have You tried Taifun? If You want You can verify data at this URL: https://www.google.com/fusiontables/DataSource?docid=1zdSnMmtBMesfjYvSxZGqb0vRXMXu62dENGiprejO#rows:id=4

Thanks.

[Taifun]

If you checked for all the Frequent Made Errors and the error message doesn't tell you anything to fix this, add a screenshot of your SQL statement including the "Do it" result and a screenshot of your error message and I will try to help you. Thank you.       

Taifun