How to do a web request call to sqlpad?

Question

Hi I have been trying to do a web request call to sqlpad.

I have got the basis of the script to make the connection

Invoke-WebRequest -uri

However when I run the command I get connection 200 showing it has made a connection but how do I use cached cookies or how do I sign into sqlpad using credentials and run query all from using web request.

Sorry I am new to powershell and webrequest so I appreciate all your advice thank you.

Answer 1

That should be doable. You'd need to use sqlpad api. To keep cookies you need to create a session variable when calling signin and reuse it in later calls. To extract query data you'd need to use query-result end point. You need to know connectionId (can lookup at api/connections using browser) and SQL code (query text). Turns out there is no direct way to run query by query name. So you either need to know the sql of the query or you can extract it from /api/queries for specific query

 $baseUrl = "http://localhost:39325/api"

 $user = "yourEmail"
 $password = "yourPassword"

 $signinUrl = "$baseUrl/signin?email=$user&password=$password&redirect=false"

 # sign in and create session variable $ws
 if(!$ws) { $r = Invoke-WebRequest -Uri $signinUrl -SessionVariable ws -Method Post } else { Write-Host "connected"}

 # list of available queries and connections. May need to run this to determine connection id or existing query sql
 $QueryList = Invoke-RestMethod -Uri  "$baseUrl/queries" -WebSession $ws
 $ConnectionList = Invoke-RestMethod -Uri "$baseUrl/connections" -WebSession $ws
 Write-Host "Available queries:"
 $QueryList.queries | select name, connectionId, queryText | ft -AutoSize

 # Execute Query

 $params = @{
    connectionId = "vhsNXMXCJeI9QlUL" #use $ConnectionList var or just look up on http://localhost:39325/api/connections/
    cacheKey = "null" #just some dummy value, parameter is required but it's not really affecting anything
    queryName = "test2"  #optional
    queryText = "select top 15 * from sys.columns"  # required
 } | ConvertTo-Json

 $head = @{'Content-Type'='application/json'}

 $data = Invoke-RestMethod -Uri "$baseUrl/query-result" -Method Post -Body $params -Headers $head -WebSession $ws

 $data.queryResult.rows | ft