Examples for using ADO Db with SAS to create librefs and read from sas7bdat files with SQL from remote servers

SasAdoDbExample.php

<?php

$conn = new COM("ADODB.Connection");
$conn->Open('Provider=sas.IOMProvider.9.3;User ID=user;Password=pass;Data Source="";SAS Machine DNS Name=my.server.com;SAS Port=8591;SAS Protocol=2');

$cmd = new COM("ADODB.Command");
$cmd->ActiveConnection = $conn;
$cmd->CommandType = 1;
$cmd->CommandText = 'libname foo "/my/path"';
$cmd->Execute();

$recordSet = $conn->Execute("select * from foo.my_table");

$recordSet->MoveFirst();

do {
    for ($i=0; $i < $recordSet->Fields->Count; $i++) {
        echo $recordSet->Fields->Item($i)->Name . ": " . $recordSet->Fields->Item($i)->Value . "\n";
    }
    echo "\n\n";
    $recordSet->MoveNext();
} while ($recordSet->EOF == FALSE);

$recordSet->Close();
$conn->Close();

?>

SasAdoDBExample.ps1

$conn = New-Object -ComObject ADODB.Connection
 
#Connect to a remote server using IOM
$conn.Open('Provider=sas.IOMProvider.9.3;User ID=user;Password=pass;Data Source="";SAS Machine DNS Name=workspace.mycompany.com;SAS Port=8591;SAS Protocol=2')
 
#Assign a new libref using ADODB
$cmd = New-Object -ComObject ADODB.Command
$cmd.ActiveConnection = $conn
$cmd.CommandType = adCmdText
$cmd.CommandText = 'libname foo "/path/to/something"'
$cmd.Execute()
 
#SQL data
$recordSet = $conn.Execute("select * from foo.something")
 
#Collect the data from recordSet into rows array
$recordSet.MoveFirst()
 
$rows = @()
 
do
{
   $row = New-Object psobject
 
   for ($i=0; $i -lt $recordSet.Fields.Count; $i++)
   {
      $row | Add-Member noteproperty `
      -name $recordSet.Fields.Item($i).Name `
      -value $recordSet.Fields.Item($i).Value;
   }
 
   #yield
   $rows += $row
 
   $recordSet.MoveNext()
}
until ($recordSet.EOF -eq $true)
 
#create gridview of data executed in recordSet
$rows | Out-GridView
 
$recordSet.Close()
$conn.Close()