Last active
August 29, 2015 14:15
-
-
Save FriedEgg/55ec275ae1e1c8fcbe6a to your computer and use it in GitHub Desktop.
Examples for using ADO Db with SAS to create librefs and read from sas7bdat files with SQL from remote servers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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(); | |
?> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment