Re: Materialized Views

Hi,

Same test with Oracle Express 11g:

create table login_test.test1 (id int primary key, contenu varchar(100));
create materialized view login_test.test_vm as select * from login_test.tes
t1;

MSQRY32 192c-1b0c ENTER SQLTablesW
HSTMT 0x00C6CBC0
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00C6DDE0 [ 24] "'TABLE','VIEW','SYNONYM'"
SWORD 24

For DB2, I found this :

*Valid table type identifiers can include: TABLE, VIEW, SYSTEM TABLE,
ALIAS, SYNONYM, GLOBAL TEMPORARY TABLE, AUXILIARY TABLE, MATERIALIZED QUERY
TABLE, or ACCEL-ONLY TABLE.*
src
https://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.odbc/src/tpc/db2z_fntables.dita

On a more rethorical pov,what's the difference between a materialized view
and a view for the end user behind ODBC ? Should there be one ?

JM.

Le jeu. 23 juil. 2015 à 12:05, Jean-Marc Guazzo <jmguazzo(at)gmail(dot)com> a
écrit :

> 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
>>
>>