"Invalid Query" error when trying to update a record in a Fusion Table

[Forrest Kendall]

I am getting the following error:

Invalid query: Parse error near '2001' (line 1, Position 81).  for the following UPDATE statement:

UPDATE <table id key> SET userid='admin2' WHERE rowid=2001

The parse error occurs with the rowid that I saved when the record was accessed earlier.    I have tried putting quotes around the rowid value, but that generates another error stating that only numbers and underlines are allow in the value.

Thanks for your help on this!

[Taifun]

how do you extract the rowid? probably there is an invisible \n included?

why this can happen and how to do it correctly, see also here https://groups.google.com/d/msg/mitappinventortest/8Q96DKcylqA/O0hObwUQbd8J

see also

Frequently Made Errors with Fusiontables and how to fix them

Taifun

---

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

[Forrest Kendall]

I extracted the ROWID from the queried record.

The SELECT QUERY was as follows:

SELECT rowid,userid,password,organization,delimiter FROM <table id key> WHERE ....

I then parsed the record on the string ",delimiter",   The parsed the data record on commas (",").

You could be right regarding the newline character.   I will try parsing the header record from the data record on newline to see what happens.

Thank you for the suggestion.    I will let you know.

[Forrest Kendall]

Hi:

just for a test I hard-coded the ROWID=2001 into my UPDATE query.   I get the same results as I initially reported.

[Forrest Kendall]

The "Do It" test produced no errors.

[Taifun]

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.

[Forrest Kendall]

I have made a simplified App to isolate the Fusion Table UPDATE query problem. All of the code for performing the UPDATE is in the logic for one button.

The ROWID for this test is: 3001.   This value was retrieved and stored when the App initialized.  Below is a screen shot of the button logic to perform the update.  I have used a text box for diagnostic information to try to see the problem.

Below are 2 screen shots of the Application.   The information in the text box is what is most significant.  The first screen shot the text box contains information for the record to be updated.   This is when the record id (rowid) is captured and saved.

The next screen shot shows the text box with both the UPDATE query string (built in the click event for the button logic above.  The text box also shows the results of the UPDATE query.

[Abraham Getzler]

What's the purpose of the FORGET LOGIN  call immediately after sending the UPDATE

and before any response can  arrive?

It looks rude.

ABG

[Taifun]

I'm not sure, if the trim block can remove the invisible \n in the rowid

I extracted the ROWID from the queried record.
The SELECT QUERY was as follows:
SELECT rowid,userid,password,organization,delimiter FROM <table id key> WHERE ....
I then parsed the record on the string ",delimiter",   The parsed the data record on commas (",").

my suggestion to get the rowid is to convert the result of your select query in to a list of lists

set myList to list from csv table get result

and then select the rowid (which is the first item in the second sublist) like this

set firstDataRow to select list item myList

                             2

set rowid to select list item firstDataRow

                                        1

Taifun

[Forrest Kendall]

In my original project I extracted the rowid exactly as you suggested.

If you look at the first screen image (from my phone) it shows the record id (rowid) as 3001, right below it I verified that its length is 4 (i.e. there are no hidden characters in the rowid string.

[Taifun]

as far as I know, the rowid does not need to be quotified...

but it's worth a try 

... WHERE rowid = '3001'

Taifun

[Forrest Kendall]

I have tried it both ways .... with quotes it tells me that only numeric values are expected for the value.

[Taifun]

If you look at the first screen image (from my phone) it shows the record id (rowid) as 3001, right below it I verified that its length is 4 (i.e. there are no hidden characters in the rowid string.

post a screenshot of your blocks after receiving the result of the select statement and how you extract the rowid

 I verified that its length is 4 (i.e. there are no hidden characters in the rowid string.

I think, the invisible \n does not have any length...

Taifun

[Forrest Kendall]

Screen image of SELECT query parsing block ....

temp - receives the split (parsed) result of the SELECT query - parsed on newline (\n).   This should have removed all newline characters.
         temp has a list of the returned records: List item 1 - header record; List item 2 - the data record including the rowid.

rec - string that has record number 2 in it (a CSV string)

userrec - is a list - it contains the split (parse) record 2 (rec string) - parsed on commas (,)

global recid - receives the first item of the userrec list; this should be the rowid returned with as part of the SELECT query

global recid is used when build the UPDATE query string segement "rowid=" + global recid

[Abraham Getzler]

Is that trailing blank in your SQL UPDATE join necessary?

ABG

[Forrest Kendall]

No.  It is not necessary.   I have tried not having it in the join, I have put a space in that string, and I have tried a semi-colon (;).   None of these seem make any difference on the problem I am experiencing.

[Taifun]

instead of using the split blocks, try the list from csv table block as recommended

Taifun

[Forrest Kendall]

Just as a test I downloaded the PizzaParty app and test it.   This app INSERTs records without problems into my linked Fusion Table.  However, when I modified this app a bit to UPDATE one of the records I get the same error as we are tracking in this post dialog.

[Forrest Kendall]

OK ... I replaced the "split" on comma block with the "list from csv".   The extracted rowid from the new list is the same (no change); and the Invalid Query error still persists at the same location.

[Forrest Kendall]

do you have any examples of an UPDATE query that works correctly?  If so please send me the aia file for the project and/or test code.

[Paul LeChef]

No doubt about it, Fusion Tables are weird. In your shoes, I would check the fusion table itself to make sure the rowid hasn't changed, which it will do if you re-insert rows or, sometimes, when you delete some. Next I would try using ROWID instead of rowid. I know it doesn't make sense, but rowid seems to work intermittently while ROWID always seems to.

I always, quote-out the row id.

Are you using the FusiontablesControl? If so, the key should not be included in the query. the error reads, "near 2001&" so I'm thinking that may be the problem. Put the key in the block that AI2 has for it.

Cheers

Paul

[Taifun]

an update statement like this

UPDATE 1Ty90w_LWJEgZHNwEWgoebigLDEXPYLAgJHVHSFkk SET Date='11.02.2015 16:23:59', Pizza='Pepperoni', Drink='Cerveza', Comment='and beer' WHERE ROWID='2001'

worked fine for me (note: the rowid must be quotified)

Taifun

[Forrest Kendall]

Paul: Thank you for the suggestions.

The API Key (I assume that is the Key you referred to) is set with at different block, it is NOT part of the SQL UPDATE string.

The strange number you see in the query example in the Table ID number (i.e the table name) for the query.

I will test variations of ROWID and rowid again, but I think I have already done this.   I will let you know.

[Forrest Kendall]

Paul:

THANK YOU!!   What finally worked was as you suggested:   UPDATE ..... WHERE ROWID='3001' (rowid in caps and the number in single quotes.

[Forrest Kendall]

Taifun:

Thank you for your suggestion, I also had the same suggestion from Paul LeChef in post right before yours.   The proper syntax for and UPDATE query is:

UPDATE ..... WHERE ROWID='3001' (rowid in caps and the number in single quotes.)

Thank for you patience and diligence in tracking this problem down.    It would be helpful if the documentation for the FusionTable control included this.   I am going to mark this problem as "closed."

Thanks to everyone who got involved!!

[Taifun]

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

Taifun

<row_id>

The ID of the row you want to update. A quoted string. To get the row ID, perform a SELECT before the UPDATE.