PowerShell generated script for ODBC connections
Posted: Fri May 29, 2009 12:58 am
Perhaps I am doing something wrong. The only ODBC database connection option I can find is the 'Microsoft OLE DB Provider for ODBC Drivers'. I can connect to my System DSN, design and perform queries. However, the PowerShell script that is generated:$DBConnection = New-Object System.Data.OleDb.OledbConnection$DBConnection.ConnectionString = 'Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=username;Data Source=Data Source Name;Extended Properties="DSN=DSN Name;UID=userid;PWD=password;HOST=xxx.xxx.xxx.xxx;PORT=12345;DB=dbname"'$DBConnection.Open()# Query data$Cmd = New-Object System.Data.OleDb.OleDbCommand$Cmd.Connection = $DBConnection$Cmd.CommandText = 'Select Yada Yada Command'Results in the error:Exception setting "ConnectionString": "The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc)."Changing the code to the following is successful in connecting and retreiving data:# Database connection$DBConnection = New-Object System.Data.Odbc.OdbcConnection$DBConnection.ConnectionString = "DSN=Data Source Name;UID=userid;PWD=password;HOST=xxx.xxx.xxx.xxx;PORT=12345;DB=dbname"$DBConnection.Open()# Query data$Cmd = New-Object System.Data.Odbc.OdbcCommand$Cmd.Connection = $DBConnection$Cmd.CommandText = 'Select Yada Yada'Additionally, for any field name that requires quoting, such as "ENTITY-ID", get double quoted in the reader loop: $Record += $rs_Reader.GetValue($rs_Reader.GetOrdinal(""SCHOOL-YEAR""))While it's not too much trouble to search and replace double quotes, it would be nice to have that resolved.