Обсуждение: How to properly perform this?
Hi, ALL,
I'm trying to execute following query:
draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, c.reloptions AS storage FROM pg_index idx, pg_class c,
pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
AND t.relname = 'leagues';
name | tablespace | included | storage
--------------+------------+----------+---------
leagues_pkey | | {} |
(1 row)
My code is as follows:
while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
{
if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
{
auto numBytes = ind[3];
if( ind[3] == SQL_NO_TOTAL )
numBytes = 255;
else if( ind[3] > 255 )
numBytes = 255;
str_to_uc_cpy( options, index_param.get() );
}
else
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
Unfortunately it goes into the loop because apparently the driver or
the server keeps seeing the last column as having data and returns
SQL_SUCCESS.
What is the proper way to resolve this?
I'm using unixODBC with PostgreSQL 16.
My odbc.ini looks like this:
[code]
[postgres]
Driver=Postgres
Description=Postgres ODBC Driver
UserId=postgres
Password=XXX
Servername=localhost
Database=draft
Port=5432
[/code]
What is the best way to proceed?
Thank you.
Dave Cramer
www.postgres.rocks
On Sat, 7 Mar 2026 at 01:10, Igor Korot <ikorot01@gmail.com> wrote:
Hi, ALL,
I'm trying to execute following query:
draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, c.reloptions AS storage FROM pg_index idx, pg_class c,
pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
AND t.relname = 'leagues';
name | tablespace | included | storage
--------------+------------+----------+---------
leagues_pkey | | {} |
(1 row)
My code is as follows:
while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
{
if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
{
auto numBytes = ind[3];
if( ind[3] == SQL_NO_TOTAL )
numBytes = 255;
else if( ind[3] > 255 )
numBytes = 255;
str_to_uc_cpy( options, index_param.get() );
}
else
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
Unfortunately it goes into the loop because apparently the driver or
the server keeps seeing the last column as having data and returns
SQL_SUCCESS.
What is the proper way to resolve this?
I'm using unixODBC with PostgreSQL 16.
My odbc.ini looks like this:
[code]
[postgres]
Driver=Postgres
Description=Postgres ODBC Driver
UserId=postgres
Password=XXX
Servername=localhost
Database=draft
Port=5432
[/code]
What is the best way to proceed?
is the last column empty or null?
if not null then there is data there.
Dave
Hi, Dave,
I think its just an empty column.
Is there a way to treat it as NULL?
P.S.: Sorry for top-posting.
On Sat, Mar 7, 2026 at 5:41 AM Dave Cramer <davecramer@postgres.rocks> wrote:
>
>
> Dave Cramer
> www.postgres.rocks
>
>
> On Sat, 7 Mar 2026 at 01:10, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> I'm trying to execute following query:
>>
>> draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
>> a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
>> a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
>> array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
>> included, c.reloptions AS storage FROM pg_index idx, pg_class c,
>> pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
>> c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
>> n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
>> AND t.relname = 'leagues';
>> name | tablespace | included | storage
>> --------------+------------+----------+---------
>> leagues_pkey | | {} |
>> (1 row)
>>
>>
>> My code is as follows:
>>
>> while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
>> index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
>> {
>> if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
>> {
>> auto numBytes = ind[3];
>> if( ind[3] == SQL_NO_TOTAL )
>> numBytes = 255;
>> else if( ind[3] > 255 )
>> numBytes = 255;
>> str_to_uc_cpy( options, index_param.get() );
>> }
>> else
>> {
>> GetErrorMessage( errorMsg, STMT_ERROR );
>> result = 1;
>> }
>> }
>> Unfortunately it goes into the loop because apparently the driver or
>> the server keeps seeing the last column as having data and returns
>> SQL_SUCCESS.
>>
>> What is the proper way to resolve this?
>>
>> I'm using unixODBC with PostgreSQL 16.
>>
>> My odbc.ini looks like this:
>>
>> [code]
>> [postgres]
>> Driver=Postgres
>> Description=Postgres ODBC Driver
>> UserId=postgres
>> Password=XXX
>> Servername=localhost
>> Database=draft
>> Port=5432
>>
>> [/code]
>>
>>
>> What is the best way to proceed?
>
>
> is the last column empty or null?
>
> if not null then there is data there.
>
> Dave
Dave,
This is from the gdb session:
Hi, David,
On Sat, Mar 7, 2026 at 1:06 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Dave,
> I think its just an empty column.
>
> Is there a way to treat it as NULL?
>
> P.S.: Sorry for top-posting.
>
> On Sat, Mar 7, 2026 at 5:41 AM Dave Cramer <davecramer@postgres.rocks> wrote:
> >
> >
> > Dave Cramer
> > www.postgres.rocks
> >
> >
> > On Sat, 7 Mar 2026 at 01:10, Igor Korot <ikorot01@gmail.com> wrote:
> >>
> >> Hi, ALL,
> >> I'm trying to execute following query:
> >>
> >> draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
> >> a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
> >> a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
> >> array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
> >> included, c.reloptions AS storage FROM pg_index idx, pg_class c,
> >> pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
> >> c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
> >> n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
> >> AND t.relname = 'leagues';
> >> name | tablespace | included | storage
> >> --------------+------------+----------+---------
> >> leagues_pkey | | {} |
> >> (1 row)
> >>
> >>
> >> My code is as follows:
> >>
> >> while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
> >> index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
> >> {
> >> if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
> >> {
> >> auto numBytes = ind[3];
> >> if( ind[3] == SQL_NO_TOTAL )
> >> numBytes = 255;
> >> else if( ind[3] > 255 )
> >> numBytes = 255;
> >> str_to_uc_cpy( options, index_param.get() );
> >> }
> >> else
> >> {
> >> GetErrorMessage( errorMsg, STMT_ERROR );
> >> result = 1;
> >> }
> >> }
> >> Unfortunately it goes into the loop because apparently the driver or
> >> the server keeps seeing the last column as having data and returns
> >> SQL_SUCCESS.
> >>
> >> What is the proper way to resolve this?
> >>
> >> I'm using unixODBC with PostgreSQL 16.
> >>
> >> My odbc.ini looks like this:
> >>
> >> [code]
> >> [postgres]
> >> Driver=Postgres
> >> Description=Postgres ODBC Driver
> >> UserId=postgres
> >> Password=XXX
> >> Servername=localhost
> >> Database=draft
> >> Port=5432
> >>
> >> [/code]
> >>
> >>
> >> What is the best way to proceed?
> >
> >
> > is the last column empty or null?
> >
> > if not null then there is data there.
3754 while( ( ret = SQLGetData( m_hstmt, 3,
SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA )
(gdb) n
3756 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
(gdb) p ind[2]
$1 = 4
(gdb) n
3758 auto numBytes = ind[2];
(gdb)
3759 if( ind[2] == SQL_NO_TOTAL )
(gdb)
3761 else if( ind[2] > 255 )
(gdb)
3763 str_to_uc_cpy( includedCol, included.get() );
(gdb)
3764 }
(gdb)
3754 while( ( ret = SQLGetData( m_hstmt, 3,
SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA )
(gdb)
3771 includedCol.erase( 0, 1 );
(gdb) p ind[2]
$2 = 4
(gdb) p ret
$3 = 100
(gdb) n
This is what happens with column 3 when the array is empty.
3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR,
index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
(gdb)
3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
(gdb) p ind[3]
$4 = -1
(gdb) p ret
$5 = 0
(gdb) n
3783 auto numBytes = ind[3];
(gdb)
3784 if( ind[3] == SQL_NO_TOTAL )
(gdb)
3786 else if( ind[3] > 255 )
(gdb)
3788 str_to_uc_cpy( options, index_param.get() );
(gdb)
3789 }
(gdb)
3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR,
index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
(gdb)
3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
(gdb) p ind[3]
$6 = -1
(gdb) p ret
$7 = 0
(gdb)
And this one is for column 4 of the query.
The column is NULL, so no data is there.
I think it's a bug in the driver as it should return SQL_NO_DATA on
the very first call....
Thank you.
> >
> > Dave
Dave,
On Sat, Mar 7, 2026 at 5:14 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Dave,
>
> This is from the gdb session:
>
> Hi, David,
>
> On Sat, Mar 7, 2026 at 1:06 PM Igor Korot <ikorot01@gmail.com> wrote:
> >
> > Hi, Dave,
> > I think its just an empty column.
> >
> > Is there a way to treat it as NULL?
> >
> > P.S.: Sorry for top-posting.
> >
> > On Sat, Mar 7, 2026 at 5:41 AM Dave Cramer <davecramer@postgres.rocks> wrote:
> > >
> > >
> > > Dave Cramer
> > > www.postgres.rocks
> > >
> > >
> > > On Sat, 7 Mar 2026 at 01:10, Igor Korot <ikorot01@gmail.com> wrote:
> > >>
> > >> Hi, ALL,
> > >> I'm trying to execute following query:
> > >>
> > >> draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
> > >> a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
> > >> a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
> > >> array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
> > >> included, c.reloptions AS storage FROM pg_index idx, pg_class c,
> > >> pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
> > >> c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
> > >> n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
> > >> AND t.relname = 'leagues';
> > >> name | tablespace | included | storage
> > >> --------------+------------+----------+---------
> > >> leagues_pkey | | {} |
> > >> (1 row)
> > >>
> > >>
> > >> My code is as follows:
> > >>
> > >> while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
> > >> index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
> > >> {
> > >> if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
> > >> {
> > >> auto numBytes = ind[3];
> > >> if( ind[3] == SQL_NO_TOTAL )
> > >> numBytes = 255;
> > >> else if( ind[3] > 255 )
> > >> numBytes = 255;
> > >> str_to_uc_cpy( options, index_param.get() );
> > >> }
> > >> else
> > >> {
> > >> GetErrorMessage( errorMsg, STMT_ERROR );
> > >> result = 1;
> > >> }
> > >> }
> > >> Unfortunately it goes into the loop because apparently the driver or
> > >> the server keeps seeing the last column as having data and returns
> > >> SQL_SUCCESS.
> > >>
> > >> What is the proper way to resolve this?
> > >>
> > >> I'm using unixODBC with PostgreSQL 16.
> > >>
> > >> My odbc.ini looks like this:
> > >>
> > >> [code]
> > >> [postgres]
> > >> Driver=Postgres
> > >> Description=Postgres ODBC Driver
> > >> UserId=postgres
> > >> Password=XXX
> > >> Servername=localhost
> > >> Database=draft
> > >> Port=5432
> > >>
> > >> [/code]
> > >>
> > >>
> > >> What is the best way to proceed?
> > >
> > >
> > > is the last column empty or null?
> > >
> > > if not null then there is data there.
>
> 3754 while( ( ret = SQLGetData( m_hstmt, 3,
> SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA )
> (gdb) n
> 3756 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
> (gdb) p ind[2]
> $1 = 4
> (gdb) n
> 3758 auto numBytes = ind[2];
> (gdb)
> 3759 if( ind[2] == SQL_NO_TOTAL )
> (gdb)
> 3761 else if( ind[2] > 255 )
> (gdb)
> 3763 str_to_uc_cpy( includedCol, included.get() );
> (gdb)
> 3764 }
> (gdb)
> 3754 while( ( ret = SQLGetData( m_hstmt, 3,
> SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA )
> (gdb)
> 3771 includedCol.erase( 0, 1 );
> (gdb) p ind[2]
> $2 = 4
> (gdb) p ret
> $3 = 100
> (gdb) n
>
> This is what happens with column 3 when the array is empty.
>
> 3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR,
> index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
> (gdb)
> 3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
> (gdb) p ind[3]
> $4 = -1
> (gdb) p ret
> $5 = 0
> (gdb) n
> 3783 auto numBytes = ind[3];
> (gdb)
> 3784 if( ind[3] == SQL_NO_TOTAL )
> (gdb)
> 3786 else if( ind[3] > 255 )
> (gdb)
> 3788 str_to_uc_cpy( options, index_param.get() );
> (gdb)
> 3789 }
> (gdb)
> 3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR,
> index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
> (gdb)
> 3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
> (gdb) p ind[3]
> $6 = -1
> (gdb) p ret
> $7 = 0
> (gdb)
>
> And this one is for column 4 of the query.
>
> The column is NULL, so no data is there.
>
> I think it's a bug in the driver as it should return SQL_NO_DATA on
> the very first call....
>
> Thank you.
Using following:
[quote]
WaylandGnome /home/igor/dbhandler/Debug # emerge -pv psqlodbc
* Last emerge --sync was 1y 348d 19h 34m 58s ago.
* IMPORTANT: config file '/etc/portage/package.keywords' needs updating.
* See the CONFIGURATION FILES and CONFIGURATION FILES UPDATE TOOLS
* sections of the emerge man page to learn how to update config files.
These are the packages that would be merged, in order:
Calculating dependencies... done!
Dependency resolution took 30.63 s (backtrack: 0/20).
[ebuild R ] dev-db/psqlodbc-11.01.0000::gentoo USE="ssl -doc
-iodbc -threads" 0 KiB
Total: 1 package (1 reinstall), Size of downloads: 0 KiB
WaylandGnome /home/igor/dbhandler/Debug #
[/quote]
Thank you.
>
> > >
> > > Dave
If I understand the question: if( ind[3] == SQL_NULL_DATA ) Assuming ind is an array of SQLLEN (ie signed). -- Nick Gorham
Hi, Nick, On Sat, Mar 7, 2026 at 11:41 PM Nick Gorham <nick@lurcherlink.org> wrote: > > If I understand the question: > > if( ind[3] == SQL_NULL_DATA ) > > Assuming ind is an array of SQLLEN (ie signed). If I understand the spec correctly, that check shouldn't be necessary as the very first call to SQLGetData() should just return SQL_NO_DATA from the NULL column. But in the meantime I added that check. Thank you. > > -- > Nick Gorham
On 08/03/2026 07:53, Igor Korot wrote: > Hi, Nick, > > On Sat, Mar 7, 2026 at 11:41 PM Nick Gorham <nick@lurcherlink.org> wrote: >> If I understand the question: >> >> if( ind[3] == SQL_NULL_DATA ) >> >> Assuming ind is an array of SQLLEN (ie signed). > If I understand the spec correctly, that check shouldn't be necessary > as the very first call to SQLGetData() should just return SQL_NO_DATA > from the NULL column. > > But in the meantime I added that check. > > Thank you. Can you show me where the spec says that, as I think that's wrong. SQL_NO_DATA will be returned on call after the previous one on a column that returns a variable length has returned SQL_SUCCESS. But anyway that's an ODBC thing not specifically a PostgreSQL one, so maybe these guys don't care. -- Nick Gorham