Google Fusion Tables

shrz

Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
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
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
Have you tried just encoding the query so:

B4X:
sdata = "sql=" & su.EncodeUrl(query,"UTF8") &auth & tail
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
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.
 
Upvote 0

thedesolatesoul

Expert
Licensed User
Longtime User
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
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
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.
 
Upvote 0

salvatore75

Member
Licensed User
Longtime User
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!
 
Upvote 0

stevel05

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

Sent from my Hero using Tapatalk
 
Upvote 0

salvatore75

Member
Licensed User
Longtime User
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:wgauth:2.0ob&scope=" & scope)
Button1.Enabled = False
Else If AccessToken = "" Then
GetAccessToken
Else
RequestContactsList
End If
End Sub
 
Upvote 0

thedesolatesoul

Expert
Licensed User
Longtime User
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
 
Last edited:
Upvote 0

salvatore75

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

Thank YOU Stevel05 and Thedesolatesoul !!!!
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
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.
 
Upvote 0

salvatore75

Member
Licensed User
Longtime User
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:wgauth:2.0ob&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:wgauth:2.0ob"
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
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…