Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vz0ln-000cPz-0R for pgsql-general@arkaria.postgresql.org; Sat, 07 Mar 2026 23:09:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vz0lj-009qi0-0r for pgsql-general@arkaria.postgresql.org; Sat, 07 Mar 2026 23:09:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vz0li-009qhs-2j for pgsql-general@lists.postgresql.org; Sat, 07 Mar 2026 23:09:11 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vz0lg-00000001PJE-0Asm for pgsql-general@lists.postgresql.org; Sat, 07 Mar 2026 23:09:10 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-78fc4425b6bso104098677b3.1 for ; Sat, 07 Mar 2026 15:09:06 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772924945; cv=none; d=google.com; s=arc-20240605; b=Ui4s42Ktpggi8kr/m+32UNdaQsmjchRLiOrFmbjKe2eWMdyyDUlrHFVaC3UID5gN3M LKK4ZFkqnJY7DW8Q6d3Eh1Wj6s90Nu9qzq6vI/O1jjXh1NyoKfPZQ0B6w9mdRKXdI8Yq lzKD39ghbNNh/Gecaz0fUnsCl4FCaytI/Qk346NFFBnQg27QwOgifRHPcQlT82aZiPxA MEZBBObGW3PbXKGJElJsZAndw6KV2CpwxyOk4unNByuREIfjZggowaCNtQG4ptEH+zWR ZmBN2hXt0/cqlVmzbEGwRhYozlgpSchD+beUyZWZSX2zfnGARc9KMvDPr6elmrn9OxcY 9C7w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=ZWK7GC6TeOM4DHQU0YPbdZYRctKSUuzQC/FQRVV+FS4=; fh=lCy/1SLGRr1blJQmTX3HQsUpJrxtQBAQuvEmV4K4fi0=; b=N4H95+4CWoedrmCW6y1vsfebN6y+5H1Q38/tNUdI/TTtffzNL6cMu6dk1MWM3K/5o7 6k7OTAJTcQPxHyHDgJWLFUo8vSqrhUGvYsBB8S2lCLMQJt2SRAxonbN7JkWKIv85Pam+ 6epmLuqxQel3Vm1ahvlUXRF3JTxZPM/OZkXucINIFSagF74+Vx6taMXt0HN5AZiMo6Gt FKrird6P3/me9L0AwEzYic/lZPZlsYGc/uvIqOWWxd2iJ/2LKJ2wPIiHwdzRQ2uXeCPL dCfY2DZNTJ2sbXZODTsbAgdK6t/uMAuX8FczgDU5LMuWpftRxCelmQN6ctar5VNeD7ie HRYQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772924945; x=1773529745; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ZWK7GC6TeOM4DHQU0YPbdZYRctKSUuzQC/FQRVV+FS4=; b=Q3kSwiyIreg9ZRB3O15TY3W8ciWvF0gaEnOrI/BxMXVU+CEESDJkMBnFeRtikKP6H2 6EbGDLmNyz+qtAManXrxoH+dqvELpYJfjfR0hKGkQtt7koG+ldyd9azw+O4FZNaZd0QQ uMbZ8dXI+oumuspennjO1EEVNdLN2+7zxC2dQmoVQAiS1S3B6W1rKmsgr8izR0kIobfM hAJ/jBDjI3UstR4YCPLyd9usJ+4agaZszZL1EZDIfcgggXYBELkVwDQTOZe6QQH87byw ufNh+JTjpAbDgEe8UtBjB6vcxhV8FvT2CbyTJkK6XHRRhvdWnFEHhIAUGVM8IPqyvBdI kGzw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772924945; x=1773529745; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=ZWK7GC6TeOM4DHQU0YPbdZYRctKSUuzQC/FQRVV+FS4=; b=h36o6S8vfvFJyHxgAu3Rk4MiIwUNMQ14E1gwAh9R7uDKZrzjHnUOeBaF6PQiBdlfFN Y0pU2qBnywmVaYUBEr0AyhCh0qfcqgNQ3WCtbyfYHPfJ+KiG1OEvln4dmSixZD61eH+g Ic2VGL4ZU6cAV0y5PbX07wBblEt1iI13hrSFLe6uqA6y7j4mzToLu1eni7XtcXw7V/N2 N3oIM7QvWI6ubH6BRbUbddaKKRnRgLTkryFnH1NR5k9ym4s6GhLRveTLE2Ez4cT69uyv JAUahWBcXGK3Ah7HVP+T5ep2XAz28OLoKHjv88grq/6F+Vv8ekL8PLP6el0fB+xAF0P9 kmTA== X-Forwarded-Encrypted: i=1; AJvYcCUkEll5/HQat56cvoscqy0oPjI3PjlK1aszgoesNjA6XrMQMcYnlMBwJQFPwl/JM0sVEkLmbG+ARcS7Kxo+@lists.postgresql.org X-Gm-Message-State: AOJu0Yy1WAMI+vWQm/q/nW/gxNv15BSXJiS0yMIccjNGW1PJxNpr9PJK wElSYPTl+RXosMWrdddpHIQeN9mMn5zmrfbxxEG7v0kAVN6i86wLSlHltAVXKNATFSjT8ZbKPn7 XpX5cviR+py73XJl+HyLDh4gO89ivBEU= X-Gm-Gg: ATEYQzzOJ6F/Ur8JxLNu8hoiYDSYBmUOvDlNkERijRHNYo7XxAXsjWlY/y6khjoMVMo x7WAlbL+AqAO8y6kFxJFLm40zdcNVOqFopRDfDyIDewX+ls122O7p2ulUZLPsQxnUzimlq981+1 E0A1xJNT3823J6tA8BmS6LDZm6csYWYpvQGme+cma6ZpniBule/ADnQXz97rsaQTwxcblRVia2B QEP39X3LP15bJ+AWryG0zINfbJo1aQe1KbF0Gg++zaz0uFgkV74M0yYhkukYHLll8pnZ/+a1J9t GXY= X-Received: by 2002:a05:690c:397:b0:798:3a5:5a76 with SMTP id 00721157ae682-798dd69b1c6mr62957797b3.17.1772924944744; Sat, 07 Mar 2026 15:09:04 -0800 (PST) MIME-Version: 1.0 References: <8f73ec0b-8f68-4f87-badc-a86939a211e1@aklaver.com> In-Reply-To: From: Igor Korot Date: Sat, 7 Mar 2026 17:08:52 -0600 X-Gm-Features: AaiRm51DWnSWBmRaPhkXRiOCwrq9d_ly1xIMj6_gfiuL4ONQ5G3Lt1r7c4IFJZs Message-ID: Subject: Re: How to properly use TRIM()? To: "David G. Johnston" Cc: Adrian Klaver , Rob Sargent , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000008b89ae064c7741e3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008b89ae064c7741e3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, David, On Sat, Mar 7, 2026 at 2:27=E2=80=AFPM David G. Johnston wrote: > On Sat, Mar 7, 2026 at 2:46=E2=80=AFPM Igor Korot wr= ote: > >> Hi, Adrian, >> >> On Sat, Mar 7, 2026 at 3:29=E2=80=AFPM Adrian Klaver >> wrote: >> > >> > On 3/7/26 12:46 PM, Igor Korot wrote: >> > > Hi, David, >> > > >> > > On Sat, Mar 7, 2026 at 12:03=E2=80=AFPM David G. Johnston >> > > > >> wrote: >> > > >> > > On Sat, Mar 7, 2026 at 12:58=E2=80=AFPM Igor Korot > > > > wrote: >> > > >> > > So I started looking for a way to return SQL_NO_DATA >> > > on that 4th column... >> > > >> > > >> > > Doesn't "No Data" refer to the result set as a whole, not >> individual >> > > columns? I'd assume NULL is detected some other way. >> > > >> > > >> > > No, I think it=E2=80=99s column based. >> > >> > 1) My knowledge of ODBC is limited. >> > >> > 2) This: >> > >> > >> https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/return-= codes-odbc?view=3Dsql-server-ver17 >> > >> > "SQL_NO_DATA No more data was available. The application calls >> > SQLGetDiagRec or SQLGetDiagField to retrieve additional information. O= ne >> > or more driver-defined status records in class 02xxx may be returned. >> > Note: In ODBC 2.x, this return code was named SQL_NO_DATA_FOUND." >> > >> > would seem to indicate that David Johnston is correct: >> >> From the SQLGetData() ODBC documentation >> ( >> https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-f= unction?view=3Dsql-server-ver17 >> ): >> >> [quote] >> When it returns the last part of the data, SQLGetData returns >> SQL_SUCCESS. Neither SQL_NO_TOTAL nor zero can be returned on the last >> valid call to retrieve data from a column, because the application >> would then have no way of knowing how much of the data in the >> application buffer is valid. If SQLGetData is called after this, it >> returns SQL_NO_DATA. For more information, see the next section, >> "Retrieving Data with SQLGetData." >> [/quote] >> >> However it looks like the driver does not behave as expected. >> >> It keeps returning SQL_SUCCESS continuously... >> >> Or am I misinterpreting the docs? >> >> > Ok, you are indeed performing an iteration of SQLGetData that does return > SQL_NO_DATA when you've exhausted the contents of the field being retriev= ed. > > You still need to check ind[3] for: > > https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-fu= nction?view=3Dsql-server-ver17#retrieving-data-with-sqlgetdata > Step 2 > > I have no idea why you would end up in an infinite loop there though. I > suppose maybe step 2's lack of describing the flow when the data is null > means you need to break out of the loop manually > 3754 while( ( ret =3D SQLGetData( m_hstmt, 3, SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) !=3D SQL_NO_DATA ) (gdb) n 3756 if( ret =3D=3D SQL_SUCCESS || ret =3D=3D SQL_SUCCESS_WITH_INFO ) (gdb) p ind[2] $1 =3D 4 (gdb) n 3758 auto numBytes =3D ind[2]; (gdb) 3759 if( ind[2] =3D=3D 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 =3D SQLGetData( m_hstmt, 3, SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) !=3D SQL_NO_DATA ) (gdb) 3771 includedCol.erase( 0, 1 ); (gdb) p ind[2] $2 =3D 4 (gdb) p ret $3 =3D 100 (gdb) n This is what happens with column 3 when the array is empty. 3779 while( ( ret =3D SQLGetData( m_hstmt, pos, SQL_C_WCHAR, index_param.get(), 255, &ind[3] ) ) !=3D SQL_NO_DATA ) (gdb) 3781 if( ret =3D=3D SQL_SUCCESS || ret =3D=3D SQL_SUCCESS_WI= TH_INFO ) (gdb) p ind[3] $4 =3D -1 (gdb) p ret $5 =3D 0 (gdb) n 3783 auto numBytes =3D ind[3]; (gdb) 3784 if( ind[3] =3D=3D 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 =3D SQLGetData( m_hstmt, pos, SQL_C_WCHAR, index_param.get(), 255, &ind[3] ) ) !=3D SQL_NO_DATA ) (gdb) 3781 if( ret =3D=3D SQL_SUCCESS || ret =3D=3D SQL_SUCCESS_WI= TH_INFO ) (gdb) p ind[3] $6 =3D -1 (gdb) p ret $7 =3D 0 (gdb) And this one is for column 4 of the query. As you can see both calls return SQL_SUCCESS, but the indicator does contain SQL_NULL_DATA (-1). I think this is the bug in the driver, as it should return SQL_NO_DATA. I'll confirm with the ODBC list. Thx. after dealing with the null value in some manner. > > David J. > > > > --0000000000008b89ae064c7741e3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi, David,
<= div dir=3D"auto">

On Sat, Mar 7, 2026 at 2:27=E2=80=AFPM David G. J= ohnston <david.g.johnston@gmail.com> wrote:
On Sat, Mar 7, 2026 at 2:46=E2= =80=AFPM Igor Korot <ikorot01@gmail.com> wrote:
Hi, Adrian,<= br>
On Sat, Mar 7, 2026 at 3:29=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com&= gt; wrote:
>
> On 3/7/26 12:46 PM, Igor Korot wrote:
> > Hi, David,
> >
> > On Sat, Mar 7, 2026 at 12:03=E2=80=AFPM David G. Johnston
> > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wr= ote:
> >
> >=C2=A0 =C2=A0 =C2=A0On Sat, Mar 7, 2026 at 12:58=E2=80=AFPM Igor K= orot <ikorot01@g= mail.com
> >=C2=A0 =C2=A0 =C2=A0<mailto:ikorot01@gmail.com>> wrote:
> >
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0So I started looking for a way t= o return SQL_NO_DATA
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0on that 4th column...
> >
> >
> >=C2=A0 =C2=A0 =C2=A0Doesn't "No Data" refer to the r= esult set as a whole, not individual
> >=C2=A0 =C2=A0 =C2=A0columns?=C2=A0 I'd assume NULL is detected= some other way.
> >
> >
> > No, I think it=E2=80=99s column based.
>
> 1) My knowledge of ODBC is limited.
>
> 2) This:
>
> https://learn.microsoft.com/en-us/sql/odbc/reference/develop-ap= p/return-codes-odbc?view=3Dsql-server-ver17
>
> "SQL_NO_DATA=C2=A0 =C2=A0 No more data was available. The applica= tion calls
> SQLGetDiagRec or SQLGetDiagField to retrieve additional information. O= ne
> or more driver-defined status records in class 02xxx may be returned.<= br> > Note: In ODBC 2.x, this return code was named SQL_NO_DATA_FOUND."=
>
> would seem to indicate that David Johnston is correct:

From the SQLGetData() ODBC documentation
(https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-= function?view=3Dsql-server-ver17):

[quote]
When it returns the last part of the data, SQLGetData returns
SQL_SUCCESS. Neither SQL_NO_TOTAL nor zero can be returned on the last
valid call to retrieve data from a column, because the application
would then have no way of knowing how much of the data in the
application buffer is valid. If SQLGetData is called after this, it
returns SQL_NO_DATA. For more information, see the next section,
"Retrieving Data with SQLGetData."
[/quote]

However it looks like the driver does not behave as expected.

It keeps returning SQL_SUCCESS continuously...

Or am I misinterpreting the docs?


=
Ok, you are indeed performing an iteratio= n of SQLGetData that does return SQL_NO_DATA when you've exhausted the = contents of the field being retrieved.

You still need to check ind[3] for:=C2=A0
Step 2


<= div style=3D"font-family:arial,helvetica,sans-serif">
I have no idea why you would en= d up in an infinite loop there though.=C2=A0 I suppose maybe step 2's l= ack of describing the flow when the data is null means you need to break ou= t of the loop manually

<= div>=C2=A03754 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0whil= e( ( ret =3D SQLGetData( m_hstmt, 3, SQL_C_WCHAR, included.get(), 255, &= ;ind[2] ) ) !=3D SQL_NO_DATA )
(gdb) n
3756 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if( ret =3D=3D SQL_SUCCESS || = ret =3D=3D SQL_SUCCESS_WITH_INFO )
(gdb) p ind[2]
$1 =3D 4
(gdb) n=
3758 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0auto numBytes =3D ind[2];
(gdb)
3759 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if= ( ind[2] =3D=3D SQL_NO_TOTAL )
(gdb)
3761 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0else if( ind[2] = > 255 )
(gdb)
3763 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0str_to_uc_cpy( includedCol, included.= get() );
(gdb)
3764 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0}
(gdb)
3754 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0while( ( ret =3D SQLGetData( m_hstmt, 3, SQL_C_W= CHAR, included.get(), 255, &ind[2] ) ) !=3D SQL_NO_DATA )
(gdb)
= 3771 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0includedCol.er= ase( 0, 1 );
(gdb) p ind[2]
$2 =3D 4
(gdb) p ret
$3 =3D 100
= (gdb) n

This is what happens with column 3 when th= e array is empty.

3779 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0while( ( ret =3D SQLGetData( m_hstmt, pos, SQL_C_WCHAR, in= dex_param.get(), 255, &ind[3] ) ) !=3D SQL_NO_DATA )
(gdb)
3781 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if( ret =3D=3D SQL_= SUCCESS || ret =3D=3D SQL_SUCCESS_WITH_INFO )
(gdb) p ind[3]
$4 =3D -= 1
(gdb) p ret
$5 =3D 0
(gdb) n
3783 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0auto numBytes =3D ind[3];
(= gdb)
3784 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0if( ind[3] =3D=3D SQL_NO_TOTAL )
(gdb)
3786 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0else if( ind[3] >= ; 255 )
(gdb)
3788 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0str_to_uc_cpy( options, index_param.get() );
(gdb) =
3789 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
(gdb)=
3779 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0while( ( ret =3D SQLGet= Data( m_hstmt, pos, SQL_C_WCHAR, index_param.get(), 255, &ind[3] ) ) != =3D SQL_NO_DATA )
(gdb)
3781 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0if( ret =3D=3D SQL_SUCCESS || ret =3D=3D SQL_SUCCESS_WITH_= INFO )
(gdb) p ind[3]
$6 =3D -1
(gdb) p ret
$7 =3D 0
(gdb) <= br>
And this one is for column 4 of the query.

=
As you can see both calls return SQL_SUCCESS, but the
= indicator does contain SQL_NULL_DATA (-1).

I think= this is the bug in the driver, as it should return SQL_NO_DATA.
=
I'll confirm with the ODBC list.

Thx.

after dealing with the null value in some ma= nner.

=
David J.



--0000000000008b89ae064c7741e3--