ZeosDBO and querys using accents.
lazarus / 16-Databases

----------------------------------------------------------------------
hamacker · 2024-03-26 18:04

ZConnection has ClientCodePage=WIN1252 and ControlsCodePage=cGET_ACP.
Database FirebirdSQL v5. Zeos 8.0.0-beta. Lazarus 3.2.
No error with accents in insert or update operations, data inside
database are fine, but when I try do get them using query as:
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 select a.*
from almox awhere(exists(select * from almox_tags zwhere
z.coditem_almox=a.coditem_almox and z.tagname='vergalhão' <--
accent))and (a.status='A')
No records are found, but when I copy and paste and try again using
flamerobin/ibexpert, records are found.
I think that my query are processing using unicode and because of
this, zeos FAILs.

How Can I fix this?

----------------------------------------------------------------------
paweld · 2024-03-26 18:21

Use params in query, like this: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
query.SQL.Text := ' select a.* from almox a where (exists( ' +  '
select * from almox_tags z ' +  ' where
z.coditem_almox=a.coditem_almox and ' +   ' z.tagname=:tn )) and
(a.status=:st) ';query.ParamByName('tn').AsString :=
'vergalhão';query.ParamByName('st').AsString := 'A';query.Open// ...

----------------------------------------------------------------------
marcov · 2024-03-26 18:24

For Zeos with postgresql, I have to put

      codepage=utf8

in the "properties" property of the TZConnection.

I don't know if that is universal or Postgressql specific.

I also have

Quote      'codepage=utf8'
      'client_encoding=utf8'
      'AutoEncodeStrings=ON'
      'controls_cp=CP_UTF16'

in there (without the quotes)

----------------------------------------------------------------------
hamacker · 2024-03-26 18:30

Reply my self,

Change in ZeosConnection:
  RawCharacterTranslitarateOptions.encoding:=encDefaultSystemCodePage;
  SQL:=true;

Now, it´s works!

----------------------------------------------------------------------
hamacker · 2024-03-26 18:34

Thanks, but this query can not be parametrized, ´cause it´s factory by
chackboxes/combo options.

Quote from: paweld on March 26, 2024, 07:21:09 pmUse params in query,
like this: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
query.SQL.Text := ' select a.* from almox a where (exists( ' +  '
select * from almox_tags z ' +  ' where
z.coditem_almox=a.coditem_almox and ' +   ' z.tagname=:tn )) and
(a.status=:st) ';query.ParamByName('tn').AsString :=
'vergalhão';query.ParamByName('st').AsString := 'A';query.Open// ...

----------------------------------------------------------------------
dseligo · 2024-03-27 00:47

Quote from: hamacker on March 26, 2024, 07:34:51 pmThanks, but this
query can not be parametrized, ´cause it´s factory by chackboxes/combo
options.

Why not?

Maybe something like this:
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
query.SQL.Text := ' select a.* from almox a where (exists( ' +  '
select * from almox_tags z ' +  ' where
z.coditem_almox=a.coditem_almox '; If chbTN.Checked then 
query.SQL.Add('and z.tagname=:tn '); query.SQL.Add(')) '); If
chbST.Checked then  query.SQL.Add('and (a.status=:st) '; If
chbTN.Checked then  query.ParamByName('tn').AsString := 'vergalhão';If
chbST.Checked then  query.ParamByName('st').AsString :=
'A';query.Open// ...

----------------------------------------------------------------------
Zvoni · 2024-03-28 08:27

Quote from: hamacker on March 26, 2024, 07:34:51 pmThanks, but this
query can not be parametrized, ´cause it´s factory by chackboxes/combo
options.
What utter nonsense.
i do this daily.

And get rid of the "Exists" and use a INNER JOIN, which is usually way
faster.
And yes, i'm aware that an INNER JOIN in a Master-Detail-Relation can
return duplicates, but that one's easy, too (and no: I'm not talking
about SELECT DISTINCT which is expensive)