Problem retrieving data
Problem retrieving data
От:
Igor Korot <ikorot01@gmail.com>
Дата:
Hi, ALL,
I don't know if this belongs here or in the ODBC list. I'm going to
try this first...
[code]
igor@WaylandGnome ~/dbhandler $ psql -U postgres
Password for user postgres:
psql (16.2)
Type "help" for help.
postgres=# \c draft
You are now connected to database "draft" as user "postgres".
draft=# SELECT * FROM abcatedt;
draft=# \d abcatedt
Table "public.abcatedt"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
abe_name | character varying(30) | | not null |
abe_edit | character varying(254) | | |
abe_type | smallint | | |
abe_cntr | integer | | |
abe_seqn | smallint | | not null |
abe_flag | integer | | |
abe_work | character varying(32) | | |
Indexes:
"abcate_x" UNIQUE, btree (abe_name, abe_seqn)
draft=#
[/code]
[code]
abe_name | abe_edit | abe_type |
abe_cntr | abe_seqn | abe_flag | abe_work
--------------------------+--------------------------+----------+----------+----------+------------+----------
###-##-#### | ###-##-#### | 90 |
1 | 1 | 32 | 00
###,###.00 | ###,###.00 | 90 |
1 | 1 | 32 | 10
##### | ##### | 90 |
1 | 1 | 32 | 10
BenefitsCheckBox | | 85 |
4 | 1 | 536870916 |
BenefitsCheckBox | Y | 85 |
4 | 2 | 0 |
BenefitsCheckBox | N | 85 |
4 | 3 | 0 |
Color List | 0 | 87 |
3 | 1 | -201326590 |
Color List | White | 87 |
3 | 2 | 0 |
Color List | White | 87 |
3 | 3 | 0 |
Color List | Black | 87 |
3 | 4 | 0 |
Color List | Black | 87 |
3 | 5 | 0 |
Color List | Purple | 87 |
3 | 6 | 0 |
Color List | Purple | 87 |
3 | 7 | 0 |
Color List | Orange | 87 |
3 | 8 | 0 |
Color List | Orange | 87 |
3 | 9 | 0 |
Color List | Green | 87 |
3 | 10 | 0 |
Color List | Green | 87 |
3 | 11 | 0 |
Color List | Blue | 87 |
3 | 12 | 0 |
Color List | Blue | 87 |
3 | 13 | 0 |
Color List | Yellow | 87 |
3 | 14 | 0 |
Color List | Yellow | 87 |
3 | 15 | 0 |
lines 1-23
[/code]
As you can see everything is retrieved correctly.
Now in my program I am using unixODBC with the ODBC driver.
This is the function I'm running:
[code]
int ODBCDatabase::GetFieldStyles(std::map > > &styles,
std::vector &errorMsg)
{
SQLWCHAR styleName[32], work[33], styleStyle[255];
int styleType = 0, seqn = 0, flag = 0;
SQLLEN cbStyleName = SQL_NTS, cbStyleType = 0, cbSeqn = 0, cbFlag
= 0, cbWork = SQL_NTS, cbStyleStyle = SQL_NTS;
int result = 0;
std::wstring query = L"SELECT abe_name, abe_type, abe_seqn,
abe_edit, abe_flag, abe_work FROM abcatedt";
std::unique_ptr qry( new SQLWCHAR[query.length() + 2] );
memset( qry.get(), '\0', query.length() + 2 );
uc_to_str_cpy( qry.get(), query );
auto ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, CONN_ERROR );
result = 1;
}
if( !result )
{
ret = SQLPrepare( m_hstmt, qry.get(), SQL_NTS );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLExecute( m_hstmt );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 1, SQL_C_WCHAR, &styleName, 33,
&cbStyleName );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 2, SQL_C_USHORT, &styleType, 0,
&cbStyleType );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 3, SQL_C_USHORT, &seqn, 0, &cbSeqn );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 4, SQL_C_WCHAR, &styleStyle, 33,
&cbStyleStyle );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 5, SQL_C_USHORT, &flag, 0, &cbFlag );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
if( !result )
{
ret = SQLBindCol( m_hstmt, 6, SQL_C_WCHAR, &work, 33, &cbWork );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}
for( ret = SQLFetch( m_hstmt ); ( ret == SQL_SUCCESS || ret ==
SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( m_hstmt
) )
{
std::wstring name, style, styleWork;
str_to_uc_cpy( name, styleName );
str_to_uc_cpy( styleWork, work );
str_to_uc_cpy( style, styleStyle );
styles[name].emplace_back( std::make_shared(
styleType, seqn, style, flag, styleWork ) );
}
if( result == 1 )
{
ret = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK );
}
else
{
ret = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_COMMIT );
}
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, CONN_ERROR );
result = 1;
}
if( !result )
{
ret = SQLFreeHandle( SQL_HANDLE_STMT, m_hstmt );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
m_hstmt = 0;
}
return result;
}
[/code]
and this is what I'm receiving:
[code]
Thread 1 "dbhandler" hit Breakpoint 2, ODBCDatabase::GetFieldStyles
(this=0x616000154880, styles=std::map with 4 elements = {...},
errorMsg=std::vector of length 0, capacity 0) at
../../libodbc/database_odbc.cpp:10710
10710 std::wstring name, style, styleWork;
(gdb) n
10711 str_to_uc_cpy( name, styleName );
(gdb)
10712 str_to_uc_cpy( styleWork, work );
(gdb) p name
$5 = L"BenefitsCheckBo"
(gdb) p cbStyleName
$6 = 32
(gdb) p styleName
$7 = {66, 101, 110, 101, 102, 105, 116, 115, 67, 104, 101, 99, 107,
66, 111, 0, 46832, 22, 24880, 0, 57173, 62688, 32767, 0, 0, 0, 0, 0,
46816,
22, 24880, 0}
(gdb) p (char *) styleName
$8 = 0x7fffffffbca0 "B"
(gdb) p (char *) styleName[1]
$9 = 0x65
(gdb) p (char) styleName[1]
$10 = 101 'e'
(gdb) p (char) styleName[2]
$11 = 110 'n'
(gdb) p (char) styleName[3]
$12 = 101 'e'
(gdb) p (char) styleName[4]
$13 = 102 'f'
(gdb) p (char) styleName[5]
$14 = 105 'i'
(gdb) p (char) styleName[6]
$15 = 116 't'
(gdb) p (char) styleName[7]
$16 = 115 's'
(gdb) p (char) styleName[8]
$17 = 67 'C'
(gdb) p (char) styleName[9]
$18 = 104 'h'
(gdb) p (char) styleName[10]
$19 = 101 'e'
(gdb) p (char) styleName[11]
$20 = 99 'c'
(gdb) p (char) styleName[12]
$21 = 107 'k'
(gdb) p (char) styleName[13]
$22 = 66 'B'
(gdb) p (char) styleName[14]
$23 = 111 'o'
(gdb) p (char) styleName[15]
$24 = 0 '\000'
[/code]
The data is cut off.
Should I forward this to the PG-ODBC ML?
Could someone here confirm/take a look at this?
Thank you.
Re: Problem retrieving data
От:
rob stone <floriparob@tpg.com.au>
Дата:
On Sun, 2026-11-15 at 01:11 -0600, Igor Korot wrote: > $9 = 0x65 Maybe this ought to be rectified?
Re: Problem retrieving data
От:
Kouame Narcisse Koffi <koffinarcisseservice@gmail.com>
Дата:
Hi Igor,
This doesn't look like a driver problem, and I don't think you need to forward it to the PG-ODBC list. The truncation comes from the BufferLength argument you pass to SQLBindCol.
BufferLength is expressed in BYTES, not in characters. SQLWCHAR is 2 bytes on your platform, so a value of 33 gives the driver room for 16 SQLWCHARs: 15 characters plus the terminating null. That is exactly what you see: "BenefitsCheckBo" is 15 characters, followed by the 0 in your gdb dump. Your cbStyleName = 32 is the driver telling you it had 32 bytes (16 characters) of data available, which is more than your buffer could hold. The driver most likely returned SQL_SUCCESS_WITH_INFO (SQLSTATE 01004, string data, right truncated), and your loop accepts that code silently.
Suggested fixes:
1. Pass the real buffer size in bytes:
SQLBindCol( m_hstmt, 1, SQL_C_WCHAR, styleName, sizeof( styleName ), &cbStyleName );
and likewise sizeof( styleStyle ) and sizeof( work ). Right now styleStyle[255] is also bound with 33 bytes, although abe_edit can hold 254 characters. Also make sure the arrays are large enough for the column length + 1 (abe_name is varchar(30), so 31 SQLWCHARs is the minimum).
2. Match the C types to the column types. abe_flag is an integer with values like 536870916 and -201326590, but you bind it as SQL_C_USHORT, which keeps only the low 16 bits. Use SQL_C_SLONG with a SQLINTEGER. For the smallint columns, use SQL_C_SSHORT with SQLSMALLINT (or SQL_C_SLONG with an int). Binding SQL_C_USHORT to an int variable only works by accident on little-endian machines.
3. Check the indicators for SQL_NULL_DATA before using the buffers. abe_work (and abe_edit) can be NULL, as in the first BenefitsCheckBox row.
4. Log the return value of SQLFetch when it is SQL_SUCCESS_WITH_INFO, and call SQLGetDiagRec to read the diagnostics. It would have shown the truncation immediately.
Minor point: in your query buffer, memset( qry.get(), '\0', query.length() + 2 ) clears bytes, not SQLWCHARs, so it should be ( query.length() + 2 ) * sizeof( SQLWCHAR ). It doesn't matter if uc_to_str_cpy terminates the string, but it's worth fixing.
Hope this helps.
Envoyé à partir de Outlook pour Android
From: rob stone <floriparob@tpg.com.au>
Sent: Sunday, 15 November 2026 12:25:58
To: Igor Korot <ikorot01@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Problem retrieving data
Sent: Sunday, 15 November 2026 12:25:58
To: Igor Korot <ikorot01@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Problem retrieving data
On Sun, 2026-11-15 at 01:11 -0600, Igor Korot wrote:
>
$9 = 0x65 <error: Cannot access memory at address 0x65>
Maybe this ought to be rectified?