PowerShell generated script for ODBC connections

This forum can be browsed by the general public. Posting is limited to current SAPIEN license holders with active maintenance and does not offer a response time guarantee.
Forum rules
DO NOT POST LICENSE NUMBERS, ACTIVATION KEYS OR ANY OTHER LICENSING INFORMATION IN THIS FORUM.
Only the original author and our tech personnel can reply to a topic that is created in this forum. If you find a topic that relates to an issue you are having, please create a new topic and reference the other in your post.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 14 years and 10 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
User avatar
sysadmin267
Posts: 55
Last visit: Fri Jan 17, 2014 2:38 pm

PowerShell generated script for ODBC connections

Post by sysadmin267 »

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.
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

PowerShell generated script for ODBC connections

Post by davidc »

Thank you for reporting these issues. We will look into this and try to resolve it as soon as possible.

In the mean time you will need to alter the script manually so that it uses the "System.Data.Odbc.OdbcConnection" object instead of the "System.Data.OleDb.OledbConnection".
David
David
SAPIEN Technologies, Inc.
This topic is 14 years and 10 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.