SQL statement with keyword "distinct" causes error on data in UTF8
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2020-11-11 23:32

Lazarus 2.0.4. My application works with Informix DB via ODBC driver.
The following fragment:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
DBForm.DBquery2.SQL.text:='select distinct TRIM(cd.datakey) as datakey
from calldata cd';    DBForm.DBquery2.Open;
caused no problem until the the cd.datakey value (datatype CHAR) in
UTF8 coding was met. Error is:

Can't open Data Source Informix_DB.
Could not get field data for field "datakey" (index 1). ODBC error
details: LastReturnCode: SQL_ERROR;Could not get error message: An
invalid parameter was passed to SQLGetDiagRec/Field

The same SQL request without keyword "distinct" cause no problem in
Lazarus.
The same SQL request (with distinct) sent from WinSQL cause no
problem, too (see screenshot).
What may be the reason for that?

----------------------------------------------------------------------
rvk · 2020-11-12 07:09

Try the following:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
DBForm.DBquery2.ParseSQL := false;  // <- add this
lineDBForm.DBquery2.SQL.text:='select distinct TRIM(cd.datakey) as
datakey from calldata cd';DBForm.DBquery2.Open;

----------------------------------------------------------------------
Vodnik · 2020-11-12 09:57

@rvk, this doesn't help, the same error is reported

----------------------------------------------------------------------
rvk · 2020-11-12 10:08

Does this SQL work?

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
TRIM(cd.datakey) AS datakey FROM calldata cd GROUP BY 1
Not sure why it's not working with distinct. Did you try Lazarus
2.0.10 yet?

----------------------------------------------------------------------
Vodnik · 2020-11-12 14:34

No, this doesn't work, too. The same error.
Plan to try Lazarus 2.0.10, then trunk version, then ZeosDB.

----------------------------------------------------------------------
Thaddy · 2020-11-12 14:57

ODBC does not know UTF8

----------------------------------------------------------------------
Vodnik · 2020-11-12 15:23

You mean ODBC driver don't know UTF8?
Don't think so:
- this is IBM driver, no stupid problems expected
- WinSQL via the same driver works fine
- other SQL requests (without distinct or group by) works fine with
UTF8 from Lazarus

----------------------------------------------------------------------
Vodnik · 2020-11-13 05:39

Compiled in Lazarus 2.0.10 produce the same error

----------------------------------------------------------------------
Thaddy · 2020-11-13 09:00

Quote from: Vodnik on November 12, 2020, 04:23:07 pmYou mean ODBC
driver don't know UTF8?
Don't think so:
- this is IBM driver, no stupid problems expected
- WinSQL via the same driver works fine
- other SQL requests (without distinct or group by) works fine with
UTF8 from Lazarus
Win SQL supports just UTF16 and Ansi.

----------------------------------------------------------------------
rvk · 2020-11-13 10:07

Quote from: Vodnik on November 13, 2020, 06:39:04 amCompiled in
Lazarus 2.0.10 produce the same error
Thaddy might be right about the UTF8.

Try the following SQL
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
DISTINCT TRIM(cd.datakey) AS datakey FROM calldata cd WHERE cd.datakey
LIKE '%cb%'
If that works you know the datakey with weird characters is the
problem.

(Otherwise try distinct on a field which has nog characters, for
example integers)