App Inventor and Fusiontables


Frequently Made Errors with Fusion Tables Fusiontables and how to fix them

SQL (Structured Query Language) is the language used to access fusiontables. SQL is a syntax oriented language. The SQL statements have to follow exactly the defined syntax else this results in errors. The syntax is defined in the Fusiontable Row and Query SQL Reference.

What is an SQL statement? An SQL statement is a string of characters that conforms to the defined format and syntax rules. An SQL statement can be sent to a database for example to select, insert, update or delete records.

Example of an SQL statement:

            INSERT INTO 1FRIkIxJ3nqhR23XY2EYorU7vC63Zahgjz1KaDTc (Date, Name, Pizza, Drink, Comment)
            VALUES('26.04.2014 07:58:07', 'Taifun', 'Pepperoni', 'Sprite', 'none')

How to check your SQL statement for errors

App Inventor offers the "Do it" functionality to debug your code (see also the screenshot above). Right click onto the join block of your SQL statement and select "Do it" from the context menu. The "Do it" result will be displayed in a comment window. Then resize the comment window to see the complete "Do it" result.

Also don't hesitate to take a look at the Fusiontable Row and Query SQL Reference.
Normally the error message should give you a good hint, what is wrong.

Do you need help with your App Inventor project?

I can help you: for one hour I charge 25 USD.
Please contact me at info [at] puravidaapps [dot] com for details!
For questions about App Inventor, please use the App Inventor forum. Thank you.

Trying to understand the error messages

The error messages sometimes might be a little bit hard to understand...

400 Bad Request Invalid grant

Spaces at the beginning or the end of the service account email

For Jack, this was the solution: in the blocks editor, my service account email was copied with spaces at the end which seems to caused the issue. Going through and re-copy-pasting the service account into the blocks editor, design editor, and the share link from the fusion table ultimatly allowed me to edit the fusion table from the app.


400 Bad Request Invalid grant - Invalid JWT Signature

There is an error in your quotify procedure

The correct quotify procedure looks like this



400 Bad Request Invalid query: Parse Error

Forgetting the space after INSERT INTO

Can you see what the error message is trying to tell you?

Here is the SQL statement from the "Do it" result window:

INSERT INTO1FRIkIxJ3nqhR23XY2EYorU7vC63Zahgjz1KaDTc(Date, Name, Pizza, Drink, Comment)
VALUES('26.04.2014 09:32:51', 'Taifun', 'Pepperoni', 'Sprite', 'none')
          

There are a lot of different "400 Bad Request" errors possible. For example the mentioned Parse error near ... error. Important in this case is: near what?... Therefore: read the error message completely to find out what is going on...

Frequent Made Errors

  1. Forgetting spaces
    Check your SQL statement for spaces before the table id! Add a space in the text block before the table id (which is the "INSERT INTO " text block in the screenshot of the SQL statement example above).
  2. Quotifying numeric values
    Following the Pizza Party tutorial you might have understood, that you have to quotify all values. This is not correct. Do not quotify colums, which have been defined as numeric!
  3. Using reserved words
    There is a list of reserved words. These are words like DELETE, FROM, TABLE, DESCRIBE etc. If you use one of them as column names, you have to quotify the name of the column in the SQL statement.
  4. Using spaces in column names
    You can use spaces in column names, but then you have to quotify the name of the column in the SQL statement.


401 Unauthorized Invalid Credentials

For some devices, the api key method unfortunately does not work anymore, which results in this error

MIT is aware of that issue, see also here. Meanwhile you can use the service account method instead
How to use the Service account option to connect to a Fusion Tables Fusiontable

403 Forbidden Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup.

Forgetting to assign the API key

There are 3 steps for sending queries to a fusiontable

  1. Assign the API key. In the Pizza Party tutorial this is done in the Screen.Initialize event.
  2. Define the SQL statement
  3. Send the statement to the fusiontable

403 Forbidden Access Not Configured. Please use Google Developers Console to activate the API for your project.

Forgetting to enable the Fusiontable API in the API Console

See again the Pizza Party tutorial, especially the chapter "Getting the API key" step 2 and 3.

Try to create a new project in the Google API console and use a new API key...

403 Forbidden Forbidden.

Trying to query a table with a Google account, which does not have permission to access the table

You set up the API key in the API Console using your Google account. Now you are trying to access the table with another account. Grant access to the table for the other account or use your first Google account instead.

404 Not Found

Using the deprecated DoQuery block instead of SendQuery


Just use the SendQuery block...

/sdcard/AppInventor/assets: open failed: EISDIR (Is a directory)

/sdcard/AppInventor/assets/<your keyfile name>: open failed: ENOENT (No such file or directory)

You are using a mixture between API key method and service account method

I tried everything, what can I do now?

If you checked for all the Frequent Made Errors and the error message doesn't tell you anything to fix this, open a new thread in the MIT App Inventor forum and 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.

App Inventor - Fusiontable interface using the web component

You probably might be interested in my App Inventor - Fusiontable interface, which uses the web component instead of the fusiontable controls. That solution offers some advantages...

Back to top of page ...

Creative Commons License
This work by Pura Vida Apps is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License
with attribution (name=Pura Vida Apps and link to the source site) required.


Home | Snippets | Tutorials | Extensions | News | Forums | Links | Search | Contact