I'm working on a project where I need to connect to a system's API using the OAuth2.0 protocol.
Our solution was to manage refresh tokens with an external database where, theoretically, after manually registering the first refresh token, the algorithm:
1) Query the database to get the refresh
2) Use it to get a new refresh token
3) Store the new refresh token in the database
4) Returns the access token that is available for 6 hours, and is used in other API queries
This closes the cycle and makes it possible to obtain a new refresh token whenever necessary.
let
Fonte1 = MySQL.Database("s*******", "u5********", [ReturnSingleDatabase=true, Query="SELECT token#(lf)FROM u5************"]),
refresh_token = Fonte1{0}[token],
// Codificar as credenciais em base64
credenciaisCodificadas = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret), BinaryEncoding.Base64),
headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Authorization"= credenciaisCodificadas
],
content = "grant_type=refresh_token&refresh_token=" & refresh_token,
response = try Web.Contents(url, [Headers=headers ,Content = Text.ToBinary(content)]) otherwise null,
json = if response <> null then Json.Document(response) else null,
novo_refresh = json[refresh_token],
Fonte = MySQL.Database("s*******", "u5********", [ReturnSingleDatabase=true, Query="SELECT token#(lf)FROM u5************"]),
#"Records Affected" = Text.Combine({Number.ToText(Fonte{0}[Records Affected]) ,json[access_token]}," - "),
Token = json[access_token]
in
Token
In practice it works sometimes and sometimes not, and I don't know exactly where the error could be.
Some observations:
- When I have a PBI file with just the access token query, without API queries linked to it, it always works, i can update it as much as i want and it always refresh the tokens correctly
- As soon as I use the access token in API queries, the error occurs, and the database does not update with the new refresh. (This happens even when queries are successful, it works only the first time, but then if i try to update again it fails)
I know that, with the information I provided, it will probably not be possible to find the exact error, but any further understanding of how power query works in relation to MYSQL queries will be of great help.
