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
 


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?




FAQ