Re: Materialized Views

Well, I made a test by creating a 'indexed view' in sql server which is
supposed to be the same as a materialized view. (
http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server
)

On a SQL server DB, I created the following items

create table sample_table( id int identity primary key, value varchar(100));
create view sample_ix_view WITH SCHEMABINDING as select id,value from
dbo.sample_table;
create unique clustered index ix_sample_ix_view on dbo.sample_ix_view(id);

I activated ODBC trace log and opened it with Access.

Excerpt from this trace :

MSACCESS 2728-410 ENTER SQLTablesW
...
WCHAR * 0x67DFBBA0 [ -3] "'TABLE','VIEW','SYSTEM
TABLE','ALIAS','SYNONYM'\ 0"
...
MSACCESS 2728-410 EXIT SQLTablesW with return code 0 (SQL_SUCCESS)
...
WCHAR * 0x67DFBBA0 [ -3] "'TABLE','VIEW','SYSTEM
TABLE','ALIAS','SYNONYM'\ 0"
...
MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
...
PTR 0x00717C34 [ 24] "sample_table"
...
MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
...
PTR 0x00717B28 [ 10] "TABLE"
...
MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
...
PTR 0x00717C34 [ 28] "sample_ix_view"
...
MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
...
PTR 0x00717B28 [ 8] "VIEW"
...

JM.

Le jeu. 23 juil. 2015 à 03:16, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> a
écrit :

> On 07/23/2015 05:37 AM, Michael Paquier wrote:
> > On Thu, Jul 23, 2015 at 11:05 AM, Jean-Marc Guazzo <jmguazzo(at)gmail(dot)com>
> wrote:
> >> Materialized Views aren't visible when I try to link them with MS
> Access, MS
> >> Excel or LibreOffice Base.
> >
> > There is the same problem with foreign tables actually.
> >
> >> I guess that's because the ODBC driver doesn't return this
> information...
> >>
> >> Can you tell me whether there will be some adjustement in the next
> version
> >> of the odbc driver regarding the MVs ?
> >
> > Hard to say... The following patch is not loved enough I am afraid:
> >
> http://www.postgresql.org/message-id/CAB7nPqR0apHpiPAi4J2e3oR2jZ8MREyJAzxdWMjrr4h5PsRp4w@mail.gmail.com
>
> Ah, that patch. No-one investigated what e.g. SQL Server returns in the
> table type column for materialized views. Or what DB2 returns for
> federated tables. While the specification gives us free hands to return
> an implementation-specific string, it'd be good to use what the other
> DBMS's use.
>
> - Heikki
>
>