Обсуждение: 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
(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
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
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
Вложения
(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
[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