You can read Dave Roth's articles published in the Windows IT Pro magazine (formally known as Windows .NET Magazine) that use Perl to accomplish interesting tasks.
Our Administrator's Handbook book is here and not a moment too soon! Win32 Perl Scripting: Administrator's Handbook
Available at Amazon.com
The 2nd edition of our Win32 Perl Programming: Standard Extensions is available.
Check out our recommended reading list.
Back to the Script Repository
You can download the script here.
################################################################# # Description: Running SQL queries & store procedures using ADO # ################################################################# use Win32::OLE; $ConnStr = "DSN=DsnName;UID=UserName;PWD=pass;APP=PerlTest;WSID=MyComp;DATABASE=MyDB"; $Conn = Win32::OLE->new('ADODB.Connection'); $Conn->Open($ConnStr); #----------------------------------------------------- #Run SQL query my $Statement = "select XXX ,YYY from ZZZ where a=b "; if(! ($RS = $Conn->Execute($Statement))) { print Win32::OLE->LastError() ; exit;; } while (! $RS->EOF) { $XXX = $RS->Fields(0)->value; $YYY = $RS->Fields(1)->value; $RS->MoveNext; } #----------------------------------------------------- #Get record set results from store procedure $oSP = Win32::OLE->new("ADODB.Command"); $RS = Win32::OLE->new("ADODB.Recordset"); $oSP->{'CommandText'} = "sp_GetData"; $oSP->{'CommandType'} = 4; $oSP->{'ActiveConnection'} = $Conn; $oSP->{'CommandTimeout'} = 1200000; $oSP->{'@Param1'} = $Param1; $oSP->{'@Param2'} = $Param2; $RS = $oSP->Execute(); while (! $RS->EOF) { $XXX = $RS->Fields(0)->value; $YYY = $RS->Fields(1)->value; $RS->MoveNext; } #----------------------------------------------------- #Get return value from store procedure $oSP = Win32::OLE->new("ADODB.Command"); $oSP->{'CommandText'} = "sp_GetUserID"; $oSP->{'CommandType'} = 4; $oSP->{'ActiveConnection'} = $Conn; $oSP->{'@Param1'} = $Param1; $oSP->{'@Param2'} = $Param2; $oSP->Execute() ; $Userid = $oSP->{'RETURN_VALUE'}->value; #----------------------------------------------------- #Get returned parameters from store procedure $oSP = Win32::OLE->new("ADODB.Command"); $oSP->{'CommandText'} = "sp_GetUserID"; $oSP->{'CommandType'} = 4; $oSP->{'ActiveConnection'} = $Conn; $oSP->{'@Param1'} = $Param1; $oSP->{'@Param2'} = $Param2; $oSP->Execute() ; $Userid = $oSP->{'@UserId'}->value; $UserName = $oSP->{'@UserName'}->value; #-----------------------------------------------------
Note: Neither Roth Consulting nor its affiliates are responsible for problems resulting from the misuse, modification or execution of any of the scripts on this web site. It is best that the the user read over the script carefully to insure that the code is not harmful to his environment.
This page has been viewed 1 times.