Recent

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

mmelwin

  • New Member
  • *
  • Posts: 13
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

  • Sr. Member
  • ****
  • Posts: 461
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

mmelwin

  • New Member
  • *
  • Posts: 13
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