Automate Quickbase Integration Tasks from PowerShell

The CData ADO.NET Provider for Quickbase is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Quickbase.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Quickbase; this tutorial shows how to use the Provider to create, retrieve, update, and delete Quickbase data.

Once you have acquired the necessary connection properties, accessing Quickbase data in PowerShell can be enabled in three steps.

User Authentication Method

To authenticate with user credentials, specify the following connection properties:

1. Set the User and Password.
2. If your application requires an ApplicationToken;, you must provide it otherwise an error will be thrown. You can find the ApplicationToken under SpecificApp > Settings > App management > App properties > Advanced settings > Security options > Manage Application Token.

User Token Authentication

To authenticate with a user token, specify the following connection properties:

1. Set UserToken and you are ready to connect. You can find the UserToken under Quick Base > My Preferences > My User Information > Manage User Tokens.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Quickbase\lib\System.Data.CData.QuickBase.dll")
       

2. Connect to Quickbase:

    
   $conn= New-Object System.Data.CData.QuickBase.QuickBaseConnection("[email protected];Password=password;Domain=myinstance.quickbase.com;ApplicationToken=bwkxrb5da2wn57bzfh9xn24")
   $conn.Open()
   

3. Instantiate the QuickBaseDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT Id, Column1 from SampleTable_1"
   
   $da= New-Object System.Data.CData.QuickBase.QuickBaseDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.column1
   }
     

Update Quickbase Data

$cmd =  New-Object System.Data.CData.QuickBase.QuickBaseCommand("UPDATE SampleTable_1 SET Column2='100' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Quickbase Data

$cmd =  New-Object System.Data.CData.QuickBase.QuickBaseCommand("INSERT INTO SampleTable_1 (Column2) VALUES (@myColumn2)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myColumn2","100")))
$cmd.ExecuteNonQuery()

Delete Quickbase Data

$cmd =  New-Object System.Data.CData.QuickBase.QuickBaseCommand("DELETE FROM SampleTable_1 WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.QuickBase.QuickBaseParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()