Обсуждение: [PATCH] Return the correct column names in SQLTables
As noted in bug #1007689, the column names returned from SQLTables differs between ODBCv2 and ODBCv3. This patch was needed to be able to list the tables when trying to import data in Excel 2010/PowerPivot. A somewhat unrelated question: There is one more "fix" needed to get the import of data in PowerPivot working properly, and that is because PowerPivot insists on creating the query with mssql-style quotes. (select [schema].[table].* instead of select "schema"."table".* ..). I have not managed to find any way of making it issue the query with the correct form of quoting. Unless anyone reading this happens to know of a way, would you accept a patch that adds an option to the driver that makes it translate []-style quoting to ""-style quoting? -- David Hedberg
Вложения
Any comments on this one? On Sat, Aug 9, 2014 at 6:29 PM, David Hedberg <david.hedberg@gmail.com> wrote: > As noted in bug #1007689, the column names returned from SQLTables > differs between ODBCv2 and ODBCv3. This patch was needed to be able to > list the tables when trying to import data in Excel 2010/PowerPivot. > > A somewhat unrelated question: > There is one more "fix" needed to get the import of data in PowerPivot > working properly, and that is because PowerPivot insists on creating > the query with mssql-style quotes. (select [schema].[table].* instead > of select "schema"."table".* ..). > > I have not managed to find any way of making it issue the query with > the correct form of quoting. Unless anyone reading this happens to > know of a way, would you accept a patch that adds an option to the > driver that makes it translate []-style quoting to ""-style quoting? > > -- > David Hedberg
On 08/09/2014 07:29 PM, David Hedberg wrote: > As noted in bug #1007689, the column names returned from SQLTables > differs between ODBCv2 and ODBCv3. Yep, we should fix that. > This patch was needed to be able to > list the tables when trying to import data in Excel 2010/PowerPivot. Hmm. According to the ODBC documentation on SQLTables: "The following columns have been renamed for ODBC 3.x. The column name changes do not affect backward compatibility because applications bind by column number." So, arguably this is a bug in Excel/PowerPivot, if it's binding by column name rather than number. But it doesn't really matter, we should fix the column names anyway. > A somewhat unrelated question: > There is one more "fix" needed to get the import of data in PowerPivot > working properly, and that is because PowerPivot insists on creating > the query with mssql-style quotes. (select [schema].[table].* instead > of select "schema"."table".* ..). Ugh. How does that work with any other ODBC driver than MS SQL Server? - Heikki
On Tue, Sep 23, 2014 at 11:46 AM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote: > On 08/09/2014 07:29 PM, David Hedberg wrote: >> >> This patch was needed to be able to >> list the tables when trying to import data in Excel 2010/PowerPivot. > > > Hmm. According to the ODBC documentation on SQLTables: > > "The following columns have been renamed for ODBC 3.x. The column name > changes do not affect backward compatibility because applications bind by > column number." > > So, arguably this is a bug in Excel/PowerPivot, if it's binding by column > name rather than number. But it doesn't really matter, we should fix the > column names anyway. To the extent the problem falls on the driver side, what do you think about the proposed patch? Feel free to amend it or throw it away if you have a better fix. >> A somewhat unrelated question: >> There is one more "fix" needed to get the import of data in PowerPivot >> working properly, and that is because PowerPivot insists on creating >> the query with mssql-style quotes. (select [schema].[table].* instead >> of select "schema"."table".* ..). > > > Ugh. How does that work with any other ODBC driver than MS SQL Server? > I don't really use these products usually so I can't really comment, but as far as I could tell there was no attempt made to get the correct quoting style from the driver. I guess I will keep my ugly hack for this to myself though unless asked otherwise. :-) > - Heikki > / David
On 09/23/2014 03:46 AM, David Hedberg wrote: > On Tue, Sep 23, 2014 at 11:46 AM, Heikki Linnakangas > <hlinnakangas@vmware.com> wrote: >> On 08/09/2014 07:29 PM, David Hedberg wrote: >>> >>> This patch was needed to be able to >>> list the tables when trying to import data in Excel 2010/PowerPivot. >> >> >> Hmm. According to the ODBC documentation on SQLTables: >> >> "The following columns have been renamed for ODBC 3.x. The column name >> changes do not affect backward compatibility because applications bind by >> column number." >> >> So, arguably this is a bug in Excel/PowerPivot, if it's binding by column >> name rather than number. But it doesn't really matter, we should fix the >> column names anyway. > > To the extent the problem falls on the driver side, what do you think > about the proposed patch? Feel free to amend it or throw it away if > you have a better fix. > >>> A somewhat unrelated question: >>> There is one more "fix" needed to get the import of data in PowerPivot >>> working properly, and that is because PowerPivot insists on creating >>> the query with mssql-style quotes. (select [schema].[table].* instead >>> of select "schema"."table".* ..). >> >> >> Ugh. How does that work with any other ODBC driver than MS SQL Server? >> > > I don't really use these products usually so I can't really comment, > but as far as I could tell there was no attempt made to get the > correct quoting style from the driver. I guess I will keep my ugly > hack for this to myself though unless asked otherwise. :-) This seems to be generic problem with PowerPivot and non-MS data sources. Some searching found a possible solution, use the Power Query add in: http://www.microsoft.com/en-us/download/details.aspx?id=39379 http://office.microsoft.com/en-us/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx > >> - Heikki >> > > / David > > -- Adrian Klaver adrian.klaver@aklaver.com