Обсуждение: Issues when using schema names with odbc

Поиск
Список
Период
Сортировка

Issues when using schema names with odbc

От
Дата:

I think there is an issue with the following version of postgresql-odbc where I am unable to use schema names.

 

Using “isql ”  “help table_name”  returns information about the column names but “help schema.table_name returns nothing.

 

This assumes the identical tables are in public.table_name and schema.table_name

 

I actually come across this when using asterisk realtime – I was about to raise this as an asterisk issue and came across this – which suggests it is actually an underlying odbc issue.

 

https://issues.asterisk.org/view.php?id=15963

 

 

Has this been seen before..?

 

Many Thanks

 

Mike

 

rpm -qa |grep odbc

postgresql-odbc-08.01.0200-3.1

 

rpm -qa |grep postgres

postgresql-server-8.1.11-1.el5_1.1

postgresql-libs-8.1.11-1.el5_1.1

postgresql-8.1.11-1.el5_1.1

postgresql-libs-8.1.11-1.el5_1.1

postgresql-odbc-08.01.0200-3.1

 

Asterisk 1.6.2.12,

 

 


Mike Smith
Voice & Multimedia Platform | BT Innovate & Design
Tel: +44 (0) 20 7876 8005

ClickDial Me

 

Re: Issues when using schema names with odbc

От
Hiroshi Inoue
Дата:
(2010/11/19 1:22), michael.28.smith@bt.com wrote:
> I think there is an issue with the following version of postgresql-odbc
> where I am unable to use schema names.
>
> Using “isql ” “help table_name” returns information about the column
> names but “help schema.table_name returns nothing.

ODBC doesn't handle schema_name.table_name type format.
For example the syntax of SQLColumns() is as follows.

    SQLRETURN SQLColumns(
        SQLHSTMT       StatementHandle,
        SQLCHAR *      CatalogName,
        SQLSMALLINT    NameLength1,
        SQLCHAR *      SchemaName,
        SQLSMALLINT    NameLength2,
        SQLCHAR *      TableName,
        SQLSMALLINT    NameLength3,
        SQLCHAR *      ColumnName,
        SQLSMALLINT    NameLength4);

regards,
Hiroshi Inoue

> This assumes the identical tables are in public.table_name and
> schema.table_name
>
> I actually come across this when using asterisk realtime – I was about
> to raise this as an asterisk issue and came across this – which suggests
> it is actually an underlying odbc issue.
>
> https://issues.asterisk.org/view.php?id=15963
>
> Has this been seen before..?
>
> Many Thanks
>
> Mike
>
> rpm -qa |grep odbc
>
> postgresql-odbc-08.01.0200-3.1
>
> rpm -qa |grep postgres
>
> postgresql-server-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-odbc-08.01.0200-3.1
>
> Asterisk 1.6.2.12

Re: Issues when using schema names with odbc

От
Дата:
Used a little test app to verify this and I get different results,

Assuming create schema test_schema;

create table public.test(
    row1 int,
    row2 text);

create table test_schema.test(
    row_t_1 int,
    row_t_2 text);

grant usage on schema test_schema to public;
grant select on public.test to public;
grant select on test_schema.test to public;

insert into public.test values (1,'public schema');
insert into test_schema.test values (2,'test schema');



The test app shows the following results....:


[root@winston scripts]# ./sqltest
SQLColumns Connection Test
Handles Allocated

Getting columns from test_schema.test
No data!

Getting all rows from test_schema.test
Row 0:1=2;2=test schema;

Getting columns from public.test
No data!

Getting all rows from public.test
Row 0:1=1;2=public schema;

Getting columns from (null).test
Column 0: row
Column 1: row
Column 2: row
Column 3: row

Getting all rows from (null).test
Row 0:1=1;2=public schema;


It shows that when using schema.table_name no column data is returned, but returns when not using a schema name.

Any other ideas?

Cheers


Mike Smith
Voice & Multimedia Platform | BT Innovate & Design
Tel: +44 (0) 20 7876 8005
ClickDial Me


-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Sent: 20 November 2010 02:33
To: Smith,M,Michael,DLW C
Cc: pgsql-odbc@postgresql.org; Hindmarch,SJ,Stephen,DLW R
Subject: Re: [ODBC] Issues when using schema names with odbc

(2010/11/19 1:22), michael.28.smith@bt.com wrote:
> I think there is an issue with the following version of postgresql-odbc
> where I am unable to use schema names.
>
> Using "isql " "help table_name" returns information about the column
> names but "help schema.table_name returns nothing.

ODBC doesn't handle schema_name.table_name type format.
For example the syntax of SQLColumns() is as follows.

    SQLRETURN SQLColumns(
        SQLHSTMT       StatementHandle,
        SQLCHAR *      CatalogName,
        SQLSMALLINT    NameLength1,
        SQLCHAR *      SchemaName,
        SQLSMALLINT    NameLength2,
        SQLCHAR *      TableName,
        SQLSMALLINT    NameLength3,
        SQLCHAR *      ColumnName,
        SQLSMALLINT    NameLength4);

regards,
Hiroshi Inoue

> This assumes the identical tables are in public.table_name and
> schema.table_name
>
> I actually come across this when using asterisk realtime - I was about
> to raise this as an asterisk issue and came across this - which suggests
> it is actually an underlying odbc issue.
>
> https://issues.asterisk.org/view.php?id=15963
>
> Has this been seen before..?
>
> Many Thanks
>
> Mike
>
> rpm -qa |grep odbc
>
> postgresql-odbc-08.01.0200-3.1
>
> rpm -qa |grep postgres
>
> postgresql-server-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-odbc-08.01.0200-3.1
>
> Asterisk 1.6.2.12

Re: Issues when using schema names with odbc

От
Дата:
Attaching test app source for reference.



Mike Smith
Voice & Multimedia Platform | BT Innovate & Design
Tel: +44 (0) 20 7876 8005
ClickDial Me


-----Original Message-----
From: Smith,M,Michael,DLW C
Sent: 29 November 2010 14:51
To: 'Hiroshi Inoue'
Cc: pgsql-odbc@postgresql.org; Hindmarch,SJ,Stephen,DLW R
Subject: RE: [ODBC] Issues when using schema names with odbc

Used a little test app to verify this and I get different results,

Assuming create schema test_schema;

create table public.test(
    row1 int,
    row2 text);

create table test_schema.test(
    row_t_1 int,
    row_t_2 text);

grant usage on schema test_schema to public;
grant select on public.test to public;
grant select on test_schema.test to public;

insert into public.test values (1,'public schema');
insert into test_schema.test values (2,'test schema');



The test app shows the following results....:


[root@winston scripts]# ./sqltest
SQLColumns Connection Test
Handles Allocated

Getting columns from test_schema.test
No data!

Getting all rows from test_schema.test
Row 0:1=2;2=test schema;

Getting columns from public.test
No data!

Getting all rows from public.test
Row 0:1=1;2=public schema;

Getting columns from (null).test
Column 0: row
Column 1: row
Column 2: row
Column 3: row

Getting all rows from (null).test
Row 0:1=1;2=public schema;


It shows that when using schema.table_name no column data is returned, but returns when not using a schema name.

Any other ideas?

Cheers


Mike Smith
Voice & Multimedia Platform | BT Innovate & Design
Tel: +44 (0) 20 7876 8005
ClickDial Me


-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Sent: 20 November 2010 02:33
To: Smith,M,Michael,DLW C
Cc: pgsql-odbc@postgresql.org; Hindmarch,SJ,Stephen,DLW R
Subject: Re: [ODBC] Issues when using schema names with odbc

(2010/11/19 1:22), michael.28.smith@bt.com wrote:
> I think there is an issue with the following version of postgresql-odbc
> where I am unable to use schema names.
>
> Using "isql " "help table_name" returns information about the column
> names but "help schema.table_name returns nothing.

ODBC doesn't handle schema_name.table_name type format.
For example the syntax of SQLColumns() is as follows.

    SQLRETURN SQLColumns(
        SQLHSTMT       StatementHandle,
        SQLCHAR *      CatalogName,
        SQLSMALLINT    NameLength1,
        SQLCHAR *      SchemaName,
        SQLSMALLINT    NameLength2,
        SQLCHAR *      TableName,
        SQLSMALLINT    NameLength3,
        SQLCHAR *      ColumnName,
        SQLSMALLINT    NameLength4);

regards,
Hiroshi Inoue

> This assumes the identical tables are in public.table_name and
> schema.table_name
>
> I actually come across this when using asterisk realtime - I was about
> to raise this as an asterisk issue and came across this - which suggests
> it is actually an underlying odbc issue.
>
> https://issues.asterisk.org/view.php?id=15963
>
> Has this been seen before..?
>
> Many Thanks
>
> Mike
>
> rpm -qa |grep odbc
>
> postgresql-odbc-08.01.0200-3.1
>
> rpm -qa |grep postgres
>
> postgresql-server-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-8.1.11-1.el5_1.1
>
> postgresql-libs-8.1.11-1.el5_1.1
>
> postgresql-odbc-08.01.0200-3.1
>
> Asterisk 1.6.2.12
Вложения

Re: Issues when using schema names with odbc

От
Hiroshi Inoue
Дата:
(2010/11/29 23:51), michael.28.smith@bt.com wrote:
> Used a little test app to verify this and I get different results,
>
> Assuming create schema test_schema;
>
> create table public.test(
>     row1 int,
>     row2 text);
>
> create table test_schema.test(
>     row_t_1 int,
>     row_t_2 text);
>
> grant usage on schema test_schema to public;
> grant select on public.test to public;
> grant select on test_schema.test to public;
>
> insert into public.test values (1,'public schema');
> insert into test_schema.test values (2,'test schema');
>
> The test app shows the following results....:
>
> [root@winston scripts]# ./sqltest
> SQLColumns Connection Test
> Handles Allocated
>
> Getting columns from test_schema.test
> No data!
>
> Getting all rows from test_schema.test
> Row 0:1=2;2=test schema;
>
> Getting columns from public.test
> No data!
>
> Getting all rows from public.test
> Row 0:1=1;2=public schema;
>
> Getting columns from (null).test
> Column 0: row
> Column 1: row
> Column 2: row
> Column 3: row
>
> Getting all rows from (null).test
> Row 0:1=1;2=public schema;

Which version of psqlodbc　driver are you using?
Here I get the following results.

Getting columns from test_schema.test
Column 0: row_t_1
Column 1: row_t_2

Getting all rows from test_schema.test
Row 0:1=2;2=test schema;

Getting columns from public.test
Column 0: row1
Column 1: row2

Getting all rows from public.test
Row 0:1=1;2=public schema;

Getting columns from (null).test
Column 0: row1
Column 1: row2

Getting all rows from (null).test
Row 0:1=1;2=public schema;

regards,
Hiroshi Inoue

Re: Issues when using schema names with odbc

От
Дата:
[btoc@rdl10090app15 ~]$ rpm -qa |grep odbc
postgresql-odbc-08.01.0200-3.1

Are you using a later version ? is so which one and any ideas what version it has been fixed in ?

Cheers


Mike Smith
Voice & Multimedia Platform | BT Innovate & Design
Tel: +44 (0) 20 7876 8005
ClickDial Me

-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp] 
Sent: 30 November 2010 14:17
To: Smith,M,Michael,DLW C
Cc: pgsql-odbc@postgresql.org; Hindmarch,SJ,Stephen,DLW R
Subject: Re: [ODBC] Issues when using schema names with odbc

(2010/11/29 23:51), michael.28.smith@bt.com wrote:
> Used a little test app to verify this and I get different results,
>
> Assuming create schema test_schema;
>
> create table public.test(
>     row1 int,
>     row2 text);
>
> create table test_schema.test(
>     row_t_1 int,
>     row_t_2 text);
>
> grant usage on schema test_schema to public;
> grant select on public.test to public;
> grant select on test_schema.test to public;
>
> insert into public.test values (1,'public schema');
> insert into test_schema.test values (2,'test schema');
>
> The test app shows the following results....:
>
> [root@winston scripts]# ./sqltest
> SQLColumns Connection Test
> Handles Allocated
>
> Getting columns from test_schema.test
> No data!
>
> Getting all rows from test_schema.test
> Row 0:1=2;2=test schema;
>
> Getting columns from public.test
> No data!
>
> Getting all rows from public.test
> Row 0:1=1;2=public schema;
>
> Getting columns from (null).test
> Column 0: row
> Column 1: row
> Column 2: row
> Column 3: row
>
> Getting all rows from (null).test
> Row 0:1=1;2=public schema;

Which version of psqlodbc　driver are you using?
Here I get the following results.

Getting columns from test_schema.test
Column 0: row_t_1
Column 1: row_t_2

Getting all rows from test_schema.test
Row 0:1=2;2=test schema;

Getting columns from public.test
Column 0: row1
Column 1: row2

Getting all rows from public.test
Row 0:1=1;2=public schema;

Getting columns from (null).test
Column 0: row1
Column 1: row2

Getting all rows from (null).test
Row 0:1=1;2=public schema;

regards,
Hiroshi Inoue