Thread: Materialized Views
Hi,
Materialized Views aren't visible when I try to link them with MS Access, MS Excel or LibreOffice Base.
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 ?
Thanks.
JM.
On Thu, Jul 23, 2015 at 11:05 AM, Jean-Marc Guazzo <jmguazzo@gmail.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 -- Michael
What must be done to show some love for this patch ?
JM.
Le mer. 22 juil. 2015 à 22:37, Michael Paquier <michael.paquier@gmail.com> a écrit :
On Thu, Jul 23, 2015 at 11:05 AM, Jean-Marc Guazzo <jmguazzo@gmail.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
--
Michael
On Thu, Jul 23, 2015 at 12:55 PM, Jean-Marc Guazzo <jmguazzo@gmail.com> wrote: > What must be done to show some love for this patch ? A review would help, as well as finding a good bait to catch some committer's time. -- Michael
On 07/23/2015 05:37 AM, Michael Paquier wrote: > On Thu, Jul 23, 2015 at 11:05 AM, Jean-Marc Guazzo <jmguazzo@gmail.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
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@iki.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@gmail.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
Attachment
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.test1;
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.
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@gmail.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 itemscreate 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@iki.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@gmail.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
Attachment
Thanks for the testing! On 09/24/2015 06:51 PM, Jean-Marc Guazzo wrote: > 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 Hmm. I don't understand how to interpret this. If you create a materialized view on Oracle, and then call SQLTables(), what object type does it report the materialized view as? TABLE? Or VIEW? Or MATERIALIZED VIEW? > 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 Ok, so they use MATERIALIZED QUERY TABLE for the equivalent of our materialized views. What about foreign tables? Are they just TABLEs? > 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 ? Well, depends on what the application is using the information for. If you just want to SELECT, there is no difference between a materialized view, a foreign table, a regular table or a regular view. But other operations are only applicable to certain kinds of object. For example, you cannot REINDEX a regular view or a foreign table, but a materialized view or a regular table you can. - Heikki