Recent

Author Topic: SQLDB problem with truncated field content  (Read 1722 times)

mmelwin

  • New Member
  • *
  • Posts: 23
SQLDB problem with truncated field content
« on: February 21, 2021, 06:52:11 pm »
The field content is truncated when the field is defined as VARCHAR and contains text as long as the field size. And when it contains national characters.
In the attachments: sample code, screenshot of error, screenshot of table contents in DB Manager.

Code: Pascal  [Select][+][-]
  1. CREATE TABLE public.test1 (
  2.   test VARCHAR(20)
  3. )
  4. WITH (oids = false);
  5. insert into test1 (test) values ('12345678901234567890');
  6.  

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2. {$mode objfpc}{$H+}
  3. interface
  4. uses
  5.   Classes, SysUtils, SQLDB, PQConnection, Forms, Controls, Graphics, Dialogs,
  6.   StdCtrls;
  7. type
  8.   { TForm1 }
  9.   TForm1 = class(TForm)
  10.     ButtonUpdate: TButton;
  11.     Button2: TButton;
  12.     ButtonUpdateUnicode: TButton;
  13.     Edit1: TEdit;
  14.     PQConnection1: TPQConnection;
  15.     SQL1: TSQLQuery;
  16.     SQLTransaction1: TSQLTransaction;
  17.     procedure ButtonUpdateClick(Sender: TObject);
  18.     procedure Button2Click(Sender: TObject);
  19.     procedure ButtonUpdateUnicodeClick(Sender: TObject);
  20.   private
  21.   public
  22.   end;
  23. var
  24.   Form1: TForm1;
  25. implementation
  26. {$R *.lfm}
  27. { TForm1 }
  28. procedure TForm1.ButtonUpdateClick(Sender: TObject);
  29. begin
  30.   PQConnection1.Connected:=True;
  31.   SQL1.Active := False;
  32.   SQL1.SQL.Clear;
  33.   SQL1.SQL.Add('SET CLIENT_ENCODING TO ''UTF8'';');
  34.   SQL1.ExecSQL;
  35.   SQL1.Active := False;
  36.   SQL1.SQL.Clear;
  37.   SQL1.SQL.Add('update test1 set test=''12345678901234567890''');
  38.   SQL1.ExecSQL;
  39.   if SQLTransaction1.Active then SQLTransaction1.CommitRetaining;
  40.   SQL1.Active := False;
  41.   SQL1.SQL.Clear;
  42.   PQConnection1.Connected:=False;
  43. end;
  44.  
  45. procedure TForm1.Button2Click(Sender: TObject);
  46. begin
  47.   PQConnection1.Connected:=True;
  48.   SQL1.Active := False;
  49.   SQL1.SQL.Clear;
  50.   SQL1.SQL.Add('SET CLIENT_ENCODING TO ''UTF8'';');
  51.   SQL1.ExecSQL;
  52.   SQL1.Active := False;
  53.   SQL1.SQL.Clear;
  54.   SQL1.SQL.Add('select test from test1');
  55.   SQL1.Active := True;
  56.   Edit1.Text:=SQL1['test'];
  57.   SQL1.Active := False;
  58.   SQL1.SQL.Clear;
  59.   PQConnection1.Connected:=False;
  60. end;
  61.  
  62. procedure TForm1.ButtonUpdateUnicodeClick(Sender: TObject);
  63. begin
  64.   PQConnection1.Connected:=True;
  65.   SQL1.Active := False;
  66.   SQL1.SQL.Clear;
  67.   SQL1.SQL.Add('SET CLIENT_ENCODING TO ''UTF8'';');
  68.   SQL1.ExecSQL;
  69.   SQL1.Active := False;
  70.   SQL1.SQL.Clear;
  71.   SQL1.SQL.Add('update test1 set test=''ąęć45678901234567890''');
  72.   SQL1.ExecSQL;
  73.   if SQLTransaction1.Active then SQLTransaction1.CommitRetaining;
  74.   SQL1.Active := False;
  75.   SQL1.SQL.Clear;
  76.   PQConnection1.Connected:=False;
  77. end;
  78.  
  79. end.
           

Zvoni

  • Hero Member
  • *****
  • Posts: 805
Re: SQLDB problem with truncated field content
« Reply #1 on: February 22, 2021, 08:24:20 am »
What encoding did you use when you created the Database?
https://www.sqlite.org/pragma.html#pragma_encoding
Quote
Once an encoding has been set for a database, it cannot be changed.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

mmelwin

  • New Member
  • *
  • Posts: 23
Re: SQLDB problem with truncated field content
« Reply #2 on: February 22, 2021, 06:00:40 pm »
This is PostgreSQL, not SQLite.
Database encoding is WIN1250.

 

TinyPortal © 2005-2018