Google Fusion Tables

shrz · Jun 27, 2011

Is it possible to connect to Google fusion tables to download a csv via an app created by b4a? Or is there another way i can allow users access to my csv file without any additional setup other than installing the app?

salvatore75 · Feb 10, 2012

I tried the code:

sdata = "sql=" & su.EncodeUrl(query &
auth & tail ,"UTF8")
bytes=sdata.GetBytes("UTF8")
HttpUtils.PostBytes
("INSERT",GoogleFusionPath,byte s)

in this case on HttpUtils.PostByte
i get in sub hc_ResponsError StatusCode =
400 & Reason = "parse error near access_
token"
I have seen an example in vb express: it run !!!
excuse my english

stevel05 · Feb 10, 2012

Have you tried just encoding the query so:

B4X:

sdata = "sql=" & su.EncodeUrl(query,"UTF8") &auth & tail

stevel05 · Feb 10, 2012

I've been playing with php & databases lately so I thought this may be an interesting alternative, I managed to Insert a record into a table (once authorised) using this code:

B4X:

Sub InsertRecord
   Dim Query As String
   Dim QueryLink As String 
   QueryLink = "https://www.google.com/fusiontables/api/query"
   
   Query=QueryLink&"?sql=INSERT+INTO+"&TableID&"+(Gig)+VALUES+('TEST')"
   HttpUtils.PostString("Insert", Query & "&access_token=" & AccessToken ,"")
End Sub

The column name is 'Gig' and I added the text 'TEST', it created a new row as expected.

I played with URLEncode but it didn't seem to work properly, so I just added the '+' where spaces would have been. For more complex data it would probably be better to find out why that didn't work.

Note I got rid of the jsonCallback=?, I couldn't find it anywhere in the documentation.

thedesolatesoul · Feb 10, 2012

Good job steve! :sign0098:
You got it working.
However, I must say it is a bit crappy. Google documentation talks about URLEncode all over the bloody page and then, it actually works WITHOUT it!
And also, it was supposed to be a POST request, but apparently everything is sent in the URL.
Regardless, if it works, I dont complain

stevel05 · Feb 10, 2012

I generally find that I can beat these things into submission .... eventually, and then they just work

.

I assume that the poststring methodology takes care of the parsing of the strings into the proper POST syntax, whatever that would be. Thinking about it I do remember testing something with php and $_POST and it worked as expected.

I find all this cloud computing quite fascinating, not that I have a particular use for it yet, but just getting prepared.

salvatore75 · Feb 10, 2012

Thanks a lot Stevel05!! But I still have not solved the problem! I used your sample code:

Sub InsertRecord
Dim tableID As String
tableID= "842604"
Dim Query As String
Dim QueryLink As String
QueryLink = "https://www.google.com/fusiontables/api/query"
Query=QueryLink&"?sql=INSERT+INTO+"&tableID&"+(Barcode,GPS,Data)+VALUES+('22222222','10.1+14.1','28/01/2012')"
Query=Query & "&access_token=" & AccessToken
HttpUtils.PostString("Insert", Query ,"")
End Sub

I get the following log on httputilsservice:

Starting Job: Insert
** Activity (main) Pause, UserClosed = false **
** Service (httputilsservice) Start **
Error. Url=https://www.google.com/fusiontables/api/query?sql=INSERT+INTO+842604+(Barcode,GPS,Data)+VALUES+('22222222','10.1+14.1','28/01/2012')&access_token=ya29.AHES6ZQJQNkbbdqtnOVtaOudwI3K0Jh6Ry94hzvAfos4J0U Message=Login required
<HTML>
<HEAD>
<TITLE>Login required</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Login required</H1>
<H2>Error 401</H2>
</BODY>
</HTML>
** Service (httputilsservice) Destroy **

What is wrong again?? Why do not I have access?

Thanks to Stevel05 and Thedesolatesoul!

stevel05 · Feb 10, 2012

How are you authorizing the session? I used OAUTH, but I think it was the same once authorized.

Sent from my Hero using Tapatalk

salvatore75 · Feb 10, 2012

I used the example of the tutorial oauth2: gmailcontacts
I get the access_token using Button1's code then call your sub InsertRecord instead HttpUtils.Download ("ContactList" ContactsListLink & "? Access_token =" & AccessToken & "& v = 3.0 & max-results = 100") in Example

Sub Button1_Click
If AuthorizationCode = "" Then
'show a WebView that will ask the user to give us access
Dim scope As String
scope = "https://www.google.com/m8/feeds"
Dim wv As WebView
wv.Initialize("wv")
Activity.AddView(wv, 0, 0, 100%x, 100%y)
wv.LoadUrl("https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=" _
& clientId & "&redirect_uri=urn:ietf:wg

auth:2.0

ob&scope=" & scope)
Button1.Enabled = False
Else If AccessToken = "" Then
GetAccessToken
Else
RequestContactsList
End If
End Sub

thedesolatesoul · Feb 10, 2012

oKay!
No wonder your access token was giving me the same error! (yes, i was using your access token but i didnt ruin anything

) ...i was too lazy to set up my own space
Your scope is wrong. You are setting the scope to be able to access:

B4X:

"https://www.google.com/m8/feeds"

which is the scope for Google contacts.

Use this scope:

B4X:

https://www.googleapis.com/auth/fusiontables

EDIT:
Also, salvatore, it is best to give us as much code as possible, because if steve hadnt made his own implementation, we would not have been able to solve this (and i am too lazy to write everything from scratch!)
And, steve, I would like to thank you for solving one of my completely unrelated problems with that POST request

salvatore75 · Feb 11, 2012

it works!!!! Thank you so much!! You were a great help for me!!! I am really happy!

Thank YOU Stevel05 and Thedesolatesoul !!!!

stevel05 · Feb 11, 2012

thedesolatesoul; said:
And, steve, I would like to thank you for solving one of my completely unrelated problems with that POST request

No problem, it"s nice to be able to give something back to the forum. A lot of this stuff is new to many if not most of us, so we're learning together.

salvatore75 · Apr 22, 2012

here is an example of my code (partial) @tpakis:

Dim clientId As String : clientId = "9289.........apps.googleusercontent.com"
Dim clientSecret As String : clientSecret = "cJoy0s......-....-....."
Dim AuthorizationCode, AccessToken As String
Dim XmlParser As SaxParser
Dim hc As HttpClient
End Sub

Sub Activity_Create(FirstTime As Boolean)

tableID="1RA9Ca....."
...
...

Sub btnTrasmettiCodici_Click
If AuthorizationCode = "" Then
'show a WebView that will ask the user to give us access
Dim scope As String
scope = "https://www.googleapis.com/auth/fusiontables"
Dim wv As WebView
wv.Initialize("wv")
Activity.AddView(wv, 0, 0, 100%x, 100%y)
wv.LoadUrl("https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=" _
& clientId & "&redirect_uri=urn:ietf:wg

auth:2.0

ob&scope=" & scope)
' Button1.Enabled = False
Else If AccessToken = "" Then
GetAccessToken
Else
TrasmettiCodiciLetti
End If
' SalvaFileConsegne
' LeggiDatiConsegna
End Sub
Sub wv_PageFinished (Url As String)
If Url.StartsWith("https://accounts.google.com/o/oauth2/approval") Then
Dim wv As WebView
wv = Sender
Dim w As WebViewXtender
Dim result As String
result = w.getTitle(wv)
wv.RemoveView 'remove the WebView
If result.StartsWith("Success code") Then
AuthorizationCode = result.SubString("Success code=".Length)
GetAccessToken
Else
Log("Errore: " & result)
ToastMessageShow(result, True)
End If
End If
End Sub

Sub GetAccessToken
Dim postString As String
postString = "code=" & AuthorizationCode & "&client_id=" & clientId & "&client_secret=" & clientSecret _
& "&grant_type=authorization_code&redirect_uri=urn:ietf:wg

auth:2.0

ob"
HttpUtils.postString("GetAccessToken", "https://accounts.google.com/o/oauth2/token", postString)
ProgressDialogShow("Sending authentication request...")
End Sub

Sub JobDone (Job As String)
ProgressDialogHide
Select Job
Case "Insert"
url = HttpUtils.Tasks.Get(0)
If HttpUtils.IsSuccess(url) Then
ToastMessageShow("Dati trasmessi correttamente", True)
Else
ToastMessageShow("Errore trasmissione dati", True)
End If
Case "GetAccessToken"
Dim url As String
url = HttpUtils.Tasks.Get(0)
'Log(HttpUtils.GetString(url))
If HttpUtils.IsSuccess(url) Then
Dim Parser As JSONParser
Parser.Initialize(HttpUtils.GetString(url))
Dim m As Map
m = Parser.NextObject
AccessToken = m.Get("access_token")
'you may also want to get the refresh_token
TrasmettiCodiciLetti
Else
ToastMessageShow("Error getting access token", True)
' Button1.Enabled = True
End If
End Select
HttpUtils.Complete = False
End Sub

Sub TrasmettiCodiciLetti
Dim indx As Int
Dim Cursore As Cursor
Dim Lettura As TipoDatiConsegna
Dim QueryUrl As String
Dim CodiciInMemoria As Int
Cursore = CaricaCodiciLetti
CodiciInMemoria= Cursore.RowCount
For indx = CodiciInMemoria - 1 To 0 Step -1
Cursore.Position = indx
Lettura.Lat=Cursore.Getdouble("lat")
Lettura.Lon=Cursore.Getdouble("lon")
Lettura.DataeOra=Cursore.GetLong("data")
Lettura.Barcode=Cursore.GetString("Barcode")
QueryUrl=QueryInsert(tableID,Lettura.Barcode,Lettura.Lat,Lettura.Lon,Lettura.DataeOra)
req1.InitializePost2(QueryUrl.Replace(" ","+"),"".GetBytes("UTF8"))
hc.Execute(req1,indx+1)
QueryService.DeleteRow(Lettura.Barcode)
DelRowSV(SVCodiciLetti,indx, Cursore.GetString("Barcode"))
NumeroConsegne=NumeroConsegne-1
' PostQuery("Insert" & i, QueryInsert(tableID,Lettura.Barcode,Lettura.Lat,Lettura.Lon,Lettura.DataeOra))
Next
If CodiciInMemoria<2 Then
ToastMessageShow("Inviato " & CodiciInMemoria & " codice letto" , True)
Else
ToastMessageShow("Inviati " & CodiciInMemoria & " codici letti" , True)
End If
Cursore.Close
End Sub

Sub QueryInsert(TabID As String, Barcode As String, lat As Double, lon As Double, DataLettura As Long) As String
Dim Query As String
Dim QueryLink As String
Dim QuerySql As String
QueryLink = "https://www.google.com/fusiontables/api/query"
QuerySql="?sql=INSERT+INTO+"&TabID&"+(Barcode,GPS,Data)+VALUES+('" & Barcode & "','" _
& lat & "+" & lon & "','" & DateTime.Date(DataLettura) & "+" & DateTime.Time(DataLettura) & "')"
Query=QueryLink & QuerySql & "&access_token=" & AccessToken
Log(Query)
Return Query
End Sub

Sub PostQuery(job As String, QueryCommand As String)
HttpUtils.PostString(job, QueryCommand ,"")
'HttpUtils.Download("ContactList", ContactsListLink & "?access_token=" & AccessToken & "&v=3.0&max-results=100")
End Sub

Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
Dim resultString As String
Result = Response.GetString("UTF8")
'Work with the result
' If Result = True Then QueryService.DeleteRow(
End Sub

Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
Log("Error connecting: " & Reason & " " & StatusCode)
If Response <> Null Then
Log(Response.GetString("UTF8"))
Response.Release
End If
End Sub

Google Fusion Tables

shrz

Member

salvatore75

Member

stevel05

Expert

stevel05

Expert

thedesolatesoul

Expert

stevel05

Expert

salvatore75

Member

stevel05

Expert

salvatore75

Member

thedesolatesoul

Expert

salvatore75

Member

stevel05

Expert

salvatore75

Member