From: | Jean-Marc Guazzo <jmguazzo(at)gmail(dot)com> |
---|---|
To: | hlinnaka(at)iki(dot)fi, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Materialized Views |
Date: | 2015-09-24 15:51:56 |
Message-ID: | CAJ3aXhoHD2=Tnz3VMvseegYAgYJOO6oGWObLAbC8EJh=jTOykw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
>>
>>
Attachment | Content-Type | Size |
---|---|---|
odbctrace_oracle.zip | application/x-zip-compressed | 5.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | andreas | 2015-09-24 16:07:15 | Fw: important |
Previous Message | Naoya Anzai | 2015-09-24 01:02:53 | Re: [BUGS] There is a case in which psqlodbc-09.03.0400 returns unterminated strings on Windows. |