$ErrorActionPreference="Stop"
Set-PSDebug -Strict

# This is the Redshift table
#CREATE TABLE mocsdw.psql_test
#(
#	id INTEGER,
#	varchar_255 VARCHAR(255),
#	date_dt DATE,
#	varchar_10 VARCHAR(10),
#	timestamp_dt TIMESTAMP
#)
#DISTSTYLE EVEN;

Write-Host Started at (Get-Date -Format g)
[System.Boolean]$b = [environment]::Is64BitProcess

if($b) {
Write-Host '64bit PowerShell detected' -ForegroundColor Green
}
else
{
Write-Host '32bit PowerShell detected' -ForegroundColor Green
}

[string] $TableName = "psql_test" 
[System.Data.OleDb.OleDbConnectionStringBuilder]$strBuilder = New-Object System.Data.OleDb.OleDbConnectionStringBuilder -ArgumentList "Provider=SQLOLEDBRSNP.1;Data Source=redmocs.cbzkr8tp4hxq.us-east-1.redshift.amazonaws.com;" 
$strBuilder["AWS_ACCESS_KEY"] = "XYZ"
$strBuilder["AWS_SECRET_KEY"] = 'ABC'
$strBuilder["AWS_BUCKET"] = "mocsdw01"
$strBuilder["Initial Catalog"] = "mocsdw"
$strBuilder["User ID"] = "QWE"
$strBuilder["Password"] = "PWD"
$strBuilder["Port"] = 5439

[System.Data.OleDb.OleDbConnection]$conn = New-Object System.Data.OleDb.OleDbConnection -ArgumentList $strBuilder.ConnectionString 
[System.Data.OleDb.OleDbDataAdapter]$adapter = New-Object System.Data.OleDb.OleDbDataAdapter -ArgumentList "select id, varchar_255, date_dt, varchar_10, timestamp_dt from mocsdw.""$TableName"";", $conn
$adapter.MissingSchemaAction = [System.Data.MissingSchemaAction]::AddWithKey
$adapter.MissingMappingAction = [System.Data.MissingMappingAction]::Passthrough
[System.Data.DataSet]$dataSet = New-Object System.Data.DataSet

try
{
    $conn.Open()
    #$adapter.FillSchema($dataSet, [System.Data.SchemaType]::Source, $TableName)
    $adapter.Fill($dataSet, $TableName)
    [System.Data.OleDb.OleDbCommandBuilder]$cmdBldr = New-Object System.Data.OleDb.OleDbCommandBuilder -ArgumentList $adapter

    $dr = $dataSet.Tables[$TableName].NewRow()
    
    $dr["id"] = 1
    $dr["varchar_255"] = "Zxcvbnm"
    $dr["date_dt"] = [System.DateTime]::Parse("01/03/2019")
    $dr["varchar_10"] = "Asdfghjkl"
    $dr["timestamp_dt"] = [System.DateTime]::Now

    $dataSet.Tables[$TableName].Rows.Add($dr)
    Write-Host $dataSet.Tables[$TableName].Rows.Count;

    Write-Host Started Update at (Get-Date -Format g) 
    $adapter.Update($dataSet, $TableName)
    Write-Host Finished Update at (Get-Date -Format g)
#Write-Host $conn.State
}
finally
{
$dataSet.Clear();
$conn.Close()
}