View Issue Details

IDProjectCategoryView StatusLast Update
0017376FPCDatabasepublic2019-08-12 13:31
Reporteryang jixianAssigned ToLacaK 
PrioritynormalSeveritytextReproducibilityalways
Status resolvedResolutionfixed 
PlatformAllOSAllOS VersionAll
Product Version2.4.3Product Build 
Target VersionFixed in Version3.1.1 
Summary0017376: TSQLite3Connection not show whole content for string field when the field is asia language
DescriptionWhen the field contained asia language string, the field shows part of the string.

Because the string from ansi to UTF8, its length as most triple of former.

TagsNo tags attached.
Fixed in Revision
FPCOldBugId
FPCTarget
Attached Files
  • SqlLiteField.patch (580 bytes)
    Index: sqlite/sqlite3conn.pp
    ===================================================================
    --- sqlite/sqlite3conn.pp	(revision 15887)
    +++ sqlite/sqlite3conn.pp	(working copy)
    @@ -371,7 +371,7 @@
                       begin
                       System.Delete(FD,1,fi);
                       fi:=pos(')',FD);
    -                  size1:=StrToIntDef(trim(copy(FD,1,fi-1)),255);
    +                  size1:=StrToIntDef(trim(copy(FD,1,fi-1)),255)*3;
                       if size1 > dsMaxStringSize then size1 := dsMaxStringSize;
                       end
                     else size1 := 255;
    
    SqlLiteField.patch (580 bytes)
  • UTF8DB20101228.patch (8,918 bytes)
    Index: base/bufdataset.pas
    ===================================================================
    --- base/bufdataset.pas	(revision 16327)
    +++ base/bufdataset.pas	(working copy)
    @@ -1629,7 +1629,7 @@
       case FieldDef.DataType of
         ftString,
           ftGuid,
    -      ftFixedChar: result := FieldDef.Size + 1;
    +      ftFixedChar: result := FieldDef.Size * 4 + 1;
         ftFixedWideChar,
           ftWideString:result := (FieldDef.Size + 1)*2;
         ftSmallint,
    Index: base/fields.inc
    ===================================================================
    --- base/fields.inc	(revision 16327)
    +++ base/fields.inc	(working copy)
    @@ -1072,9 +1072,9 @@
     
     begin
       if DataType=ftFixedChar then
    -    Result:=Size
    +    Result := Size * 4
       else
    -    Result:=Size+1;
    +    Result := Size * 4 + 1;
     end;
     
     function TStringField.GetDefaultWidth: Longint;
    Index: sqldb/interbase/ibconnection.pp
    ===================================================================
    --- sqldb/interbase/ibconnection.pp	(revision 16327)
    +++ sqldb/interbase/ibconnection.pp	(working copy)
    @@ -935,8 +935,13 @@
               GetDateTime(CurrBuff, Buffer, SQLDA^.SQLVar[x].SQLType);
             ftString, ftFixedChar  :
               begin
    -            Move(CurrBuff^, Buffer^, VarCharLen);
    -            PChar(Buffer + VarCharLen)^ := #0;
    +            if not IsUTF8 then
    +            begin
    +              CurrBuff[VarCharLen] := #0;
    +              CurrBuff := PChar(UTF8Encode(CurrBuff));
    +            end;
    +            Move(CurrBuff^, Buffer^, VarCharLen * 4);
    +            PChar(Buffer + VarCharLen * 4)^ := #0;
               end;
             ftFloat   :
               GetFloat(CurrBuff, Buffer, SQLDA^.SQLVar[x].SQLLen);
    Index: sqldb/mysql/mysqlconn.inc
    ===================================================================
    --- sqldb/mysql/mysqlconn.inc	(revision 16327)
    +++ sqldb/mysql/mysqlconn.inc	(working copy)
    @@ -930,6 +930,11 @@
     }
           // String-fields which can contain more then dsMaxStringSize characters
           // are mapped to ftBlob fields, while their mysql-datatype is FIELD_TYPE_BLOB
    +      if not IsUTF8 then
    +      begin
    +        Source[ASize] := #0;
    +        Source := PChar(UTF8Encode(Source));
    +      end;
           if AFieldType in [ftBlob,ftMemo] then
             CreateBlob := True
           else if Src<> '' then
    Index: sqldb/odbc/odbcconn.pas
    ===================================================================
    --- sqldb/odbc/odbcconn.pas	(revision 16327)
    +++ sqldb/odbc/odbcconn.pas	(working copy)
    @@ -724,6 +724,7 @@
       BlobMemoryStream:TMemoryStream;
     {$ENDIF}
       Res:SQLRETURN;
    +  CurrBuff: PChar;
     begin
     {$IF (FPC_VERSION>=2) AND (FPC_RELEASE>=1)}
       CreateBlob := False;
    @@ -737,7 +738,13 @@
         ftWideString,ftFixedWideChar: // mapped to TWideStringField
           Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_WCHAR, buffer, FieldDef.Size, @StrLenOrInd);
         ftGuid, ftFixedChar,ftString: // are mapped to a TStringField (including TGuidField)
    -      Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_CHAR, buffer, FieldDef.Size, @StrLenOrInd);
    +    begin
    +      CurrBuff := Buffer;
    +      Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_CHAR, CurrBuff, FieldDef.Size * 4, @StrLenOrInd);
    +      if not IsUTF8 then
    +        CurrBuff := PChar(UTF8Encode(CurrBuff));
    +      Move(CurrBuff^, Buffer^, FieldDef.Size * 4);
    +    end;
         ftSmallint:           // mapped to TSmallintField
           Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_SSHORT, buffer, SizeOf(Smallint), @StrLenOrInd);
         ftInteger,ftWord,ftAutoInc:     // mapped to TLongintField
    Index: sqldb/oracle/oracleconnection.pp
    ===================================================================
    --- sqldb/oracle/oracleconnection.pp	(revision 16327)
    +++ sqldb/oracle/oracleconnection.pp	(working copy)
    @@ -291,7 +291,10 @@
               ftString  : begin OFieldType := SQLT_STR; OFieldSize := 4000; end;
     
             end;
    -        parambuffers[tel].buffer := getmem(OFieldSize);
    +        if AParams[tel].DataType = ftString then
    +          parambuffers[tel].buffer := getmem(OFieldSize * 4)
    +        else
    +          parambuffers[tel].buffer := getmem(OFieldSize);
     
     
             FOciBind := nil;
    @@ -522,7 +525,10 @@
             FieldType := ftUnknown;
           end;
     
    -      FieldBuffers[tel-1].buffer := getmem(OFieldSize);
    +      if OFieldType in [OCI_TYPECODE_CHAR, OCI_TYPECODE_VARCHAR, OCI_TYPECODE_VARCHAR2] then
    +        FieldBuffers[tel-1].buffer := getmem(OFieldSize * 4)
    +      else
    +        FieldBuffers[tel-1].buffer := getmem(OFieldSize);
     
           FOciDefine := nil;
           if OciDefineByPos(FOciStmt,FOciDefine,FOciError,tel,fieldbuffers[tel-1].buffer,OFieldSize,OFieldType,@(fieldbuffers[tel-1].ind),nil,nil,OCI_DEFAULT) = OCI_ERROR then
    @@ -563,7 +569,7 @@
         exp       : shortint;
         cur       : Currency;
         odt       : POCIdateTime;
    -
    +    CurrBuff  : PChar;
     begin
       CreateBlob := False;
       with cursor as TOracleCursor do if fieldbuffers[FieldDef.FieldNo-1].ind = -1 then
    @@ -572,7 +578,17 @@
         begin
         result := True;
         case FieldDef.DataType of
    -      ftString          : move(fieldbuffers[FieldDef.FieldNo-1].buffer^,buffer^,FieldDef.Size);
    +      ftString          :
    +        begin
    +          CurrBuff := fieldbuffers[FieldDef.FieldNo-1].buffer;
    +          if not IsUTF8 then
    +          begin
    +            CurrBuff[FieldDef.Size] := #0;
    +            CurrBuff := PChar(UTF8Encode(CurrBuff));
    +          end;
    +          move(CurrBuff^, buffer^, FieldDef.Size * 4);
    +          PChar(Buffer + FieldDef.Size * 4)^ := #0;
    +        end;
           ftBCD             :  begin
                                b := fieldbuffers[FieldDef.FieldNo-1].buffer;
                                size := b[0];
    Index: sqldb/postgres/pqconnection.pp
    ===================================================================
    --- sqldb/postgres/pqconnection.pp	(revision 16327)
    +++ sqldb/postgres/pqconnection.pp	(working copy)
    @@ -748,12 +748,17 @@
               end; {case}
               end;
             ftString  :
    -          begin
    +        begin
               li := pqgetlength(res,curtuple,x);
               if li > dsMaxStringSize then li := dsMaxStringSize;
    -          Move(CurrBuff^, Buffer^, li);
    -          pchar(Buffer + li)^ := #0;
    +          if not IsUTF8 then
    +          begin
    +            CurrBuff[li] := #0;
    +            CurrBuff := PChar(UTF8Encode(CurrBuff));
               end;
    +          Move(CurrBuff^, Buffer^, li * 4);
    +          pchar(Buffer + li * 4)^ := #0;
    +        end;
             ftBlob : Createblob := True;
             ftdate :
               begin
    Index: sqldb/sqldb.pp
    ===================================================================
    --- sqldb/sqldb.pp	(revision 16327)
    +++ sqldb/sqldb.pp	(working copy)
    @@ -89,7 +89,7 @@
         FHostName            : string;
         FCharSet             : string;
         FRole                : String;
    -
    +    FIsUTF8              : Boolean;
         FSQLServerFormatSettings : TFormatSettings;
         function GetPort: cardinal;
         procedure Setport(const AValue: cardinal);
    @@ -148,7 +148,7 @@
         property UserName : string read FUserName write FUserName;
         property CharSet : string read FCharSet write FCharSet;
         property HostName : string Read FHostName Write FHostName;
    -
    +    property IsUTF8: Boolean read FIsUTF8 write FIsUTF8;
         property Connected;
         Property Role :  String read FRole write FRole;
         property DatabaseName;
    Index: sqldb/sqlite/sqlite3conn.pp
    ===================================================================
    --- sqldb/sqlite/sqlite3conn.pp	(revision 16327)
    +++ sqldb/sqlite/sqlite3conn.pp	(working copy)
    @@ -479,7 +479,7 @@
      str1: string;
      ar1,ar2: TStringArray;
      st    : psqlite3_stmt;
    -
    + CurrBuff: PChar;
     begin
       st:=TSQLite3Cursor(cursor).fstatement;
       fnum:= FieldDef.fieldno - 1;
    @@ -507,13 +507,19 @@
                    end
                  else
                    Pdatetime(buffer)^:= sqlite3_column_double(st,fnum);
    -    ftString: begin
    -              int1:= sqlite3_column_bytes(st,fnum);
    -              if int1>FieldDef.Size then 
    -                int1:=FieldDef.Size;
    -              if int1 > 0 then 
    -                 move(sqlite3_column_text(st,fnum)^,buffer^,int1);
    -              end;
    +    ftString:
    +    begin
    +      int1 := sqlite3_column_bytes(st, fnum);
    +      if int1 > FieldDef.Size then
    +        int1 := FieldDef.Size;
    +      if int1 > 0 then
    +      begin
    +        CurrBuff := sqlite3_column_text(st, fnum);
    +        if not IsUTF8 then
    +          CurrBuff := PChar(UTF8Encode(CurrBuff));
    +        move(CurrBuff^, buffer^, int1*4);
    +      end;
    +    end;
         ftMemo,
         ftBlob: CreateBlob:=True;
       else { Case }
    @@ -695,6 +701,7 @@
       inherited Create(AOwner);
       FConnOptions := FConnOptions + [sqEscapeRepeat] + [sqEscapeSlash];
       FieldNameQuoteChars:=DoubleQuotes;
    +  IsUTF8 := True;
     end;
     
     procedure TSQLite3Connection.UpdateIndexDefs(IndexDefs: TIndexDefs; TableName: string);
    
    UTF8DB20101228.patch (8,918 bytes)

Relationships

related to 0031078 resolvedLacaK SQLQuery string filed limited to number of bytes rather than symbols with UTF8 
related to 0019341 resolvedJoost van der Sluis TOracleConnection and SQLQuery1 ,got error ORA-01455 converting column overflows integer datatype 
related to 0024745 closedReinier Olislagers Losing data when saving Database fileds with "Size" defined and UTF8 chars 
related to 0025801 resolvedMichael Van Canneyt TStringField may return wrong size for TStringField.DataSize 
related to 0012206 resolvedLacaK ODBC Query in DBGrid, some Cells are Empty (Encoding) 

Activities

2010-09-09 04:27

 

SqlLiteField.patch (580 bytes)
Index: sqlite/sqlite3conn.pp
===================================================================
--- sqlite/sqlite3conn.pp	(revision 15887)
+++ sqlite/sqlite3conn.pp	(working copy)
@@ -371,7 +371,7 @@
                   begin
                   System.Delete(FD,1,fi);
                   fi:=pos(')',FD);
-                  size1:=StrToIntDef(trim(copy(FD,1,fi-1)),255);
+                  size1:=StrToIntDef(trim(copy(FD,1,fi-1)),255)*3;
                   if size1 > dsMaxStringSize then size1 := dsMaxStringSize;
                   end
                 else size1 := 255;
SqlLiteField.patch (580 bytes)

yang jixian

2010-09-09 04:28

reporter   ~0040906

The patch set the field lengh to triple

LacaK

2010-09-09 08:29

developer   ~0040908

Last edited: 2010-09-10 07:30

IMHO the Size property of TStringField says about the number of CHARACTERS not BYTES (it correspond to size of database field as defined, so CHAR(10) maps to TStringField with Size=10). See please: http://docwiki.embarcadero.com/VCL/XE/en/DB.TStringField.Size

What is missing here is conversion from database charset UTF-8 (used by SQLite) to TStringField charset (which is IMO current system charset used by AnsiString type ... UTF8ToAnsi())
(of course it will be successful only if all characters currently used and encoded by UTF-8 can be translated to current system charset)

Another approach can be use TWideStringField instead of TStringField and use conversion from UTF-8 to UNICODE UTF-16 (or UCS-2 or encoding used by WideString type ... UTF8Decode() or UTF8ToUnicode())

Note also, that TField.Size is also used in determning TField.DisplayWidth, which is used for example in DBGrids to determine column Width, so extend Size *3 will also extend column *3. Which is not good IMHO.

yang jixian

2010-09-10 04:45

reporter   ~0040922

Here we get wrong buffer while sqlite3_column_text(st,fnum) is right:
move(sqlite3_column_text(st,fnum)^,buffer^,int1);

yang jixian

2010-09-10 04:51

reporter   ~0040923

Last edited: 2010-09-10 04:52

It seems that the buffer here is not UTF8 buffer while the field size is right too.

LacaK

2010-09-10 07:42

developer   ~0040924

Yes, TStringField is "internaly" simple AnsiString (1char=1byte), so moving directly UTF8String to AnsiString buffer is not good.
This is why I proposed use TWideStringField instead of TStringField and/or some kind of string conversion/translation.

IMO situation will be much better when FPC will implement native string types like UTF8String, UnicodeString, AnsiString and implicit conversions between them.
ATM we must perform conversion from UTF8String explicitly where needed.

yang jixian

2010-12-27 15:48

reporter   ~0044631

Last edited: 2010-12-27 15:50

The UTF8DBBug.patch fixed all UTF8 issues of database component by setting buffer = buffer * 4.

But with latest SVN of fpc 2.5.1, the oracle component did not work fine with a ORA-01062 error.

So the patch is based on a ealier version of fpc 2.5.1.

Marco van de Voort

2010-12-27 18:58

manager   ~0044633

Please do not run formatters over code. It makes patches useless.

2010-12-28 09:07

 

UTF8DB20101228.patch (8,918 bytes)
Index: base/bufdataset.pas
===================================================================
--- base/bufdataset.pas	(revision 16327)
+++ base/bufdataset.pas	(working copy)
@@ -1629,7 +1629,7 @@
   case FieldDef.DataType of
     ftString,
       ftGuid,
-      ftFixedChar: result := FieldDef.Size + 1;
+      ftFixedChar: result := FieldDef.Size * 4 + 1;
     ftFixedWideChar,
       ftWideString:result := (FieldDef.Size + 1)*2;
     ftSmallint,
Index: base/fields.inc
===================================================================
--- base/fields.inc	(revision 16327)
+++ base/fields.inc	(working copy)
@@ -1072,9 +1072,9 @@
 
 begin
   if DataType=ftFixedChar then
-    Result:=Size
+    Result := Size * 4
   else
-    Result:=Size+1;
+    Result := Size * 4 + 1;
 end;
 
 function TStringField.GetDefaultWidth: Longint;
Index: sqldb/interbase/ibconnection.pp
===================================================================
--- sqldb/interbase/ibconnection.pp	(revision 16327)
+++ sqldb/interbase/ibconnection.pp	(working copy)
@@ -935,8 +935,13 @@
           GetDateTime(CurrBuff, Buffer, SQLDA^.SQLVar[x].SQLType);
         ftString, ftFixedChar  :
           begin
-            Move(CurrBuff^, Buffer^, VarCharLen);
-            PChar(Buffer + VarCharLen)^ := #0;
+            if not IsUTF8 then
+            begin
+              CurrBuff[VarCharLen] := #0;
+              CurrBuff := PChar(UTF8Encode(CurrBuff));
+            end;
+            Move(CurrBuff^, Buffer^, VarCharLen * 4);
+            PChar(Buffer + VarCharLen * 4)^ := #0;
           end;
         ftFloat   :
           GetFloat(CurrBuff, Buffer, SQLDA^.SQLVar[x].SQLLen);
Index: sqldb/mysql/mysqlconn.inc
===================================================================
--- sqldb/mysql/mysqlconn.inc	(revision 16327)
+++ sqldb/mysql/mysqlconn.inc	(working copy)
@@ -930,6 +930,11 @@
 }
       // String-fields which can contain more then dsMaxStringSize characters
       // are mapped to ftBlob fields, while their mysql-datatype is FIELD_TYPE_BLOB
+      if not IsUTF8 then
+      begin
+        Source[ASize] := #0;
+        Source := PChar(UTF8Encode(Source));
+      end;
       if AFieldType in [ftBlob,ftMemo] then
         CreateBlob := True
       else if Src<> '' then
Index: sqldb/odbc/odbcconn.pas
===================================================================
--- sqldb/odbc/odbcconn.pas	(revision 16327)
+++ sqldb/odbc/odbcconn.pas	(working copy)
@@ -724,6 +724,7 @@
   BlobMemoryStream:TMemoryStream;
 {$ENDIF}
   Res:SQLRETURN;
+  CurrBuff: PChar;
 begin
 {$IF (FPC_VERSION>=2) AND (FPC_RELEASE>=1)}
   CreateBlob := False;
@@ -737,7 +738,13 @@
     ftWideString,ftFixedWideChar: // mapped to TWideStringField
       Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_WCHAR, buffer, FieldDef.Size, @StrLenOrInd);
     ftGuid, ftFixedChar,ftString: // are mapped to a TStringField (including TGuidField)
-      Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_CHAR, buffer, FieldDef.Size, @StrLenOrInd);
+    begin
+      CurrBuff := Buffer;
+      Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_CHAR, CurrBuff, FieldDef.Size * 4, @StrLenOrInd);
+      if not IsUTF8 then
+        CurrBuff := PChar(UTF8Encode(CurrBuff));
+      Move(CurrBuff^, Buffer^, FieldDef.Size * 4);
+    end;
     ftSmallint:           // mapped to TSmallintField
       Res:=SQLGetData(ODBCCursor.FSTMTHandle, FieldDef.Index+1, SQL_C_SSHORT, buffer, SizeOf(Smallint), @StrLenOrInd);
     ftInteger,ftWord,ftAutoInc:     // mapped to TLongintField
Index: sqldb/oracle/oracleconnection.pp
===================================================================
--- sqldb/oracle/oracleconnection.pp	(revision 16327)
+++ sqldb/oracle/oracleconnection.pp	(working copy)
@@ -291,7 +291,10 @@
           ftString  : begin OFieldType := SQLT_STR; OFieldSize := 4000; end;
 
         end;
-        parambuffers[tel].buffer := getmem(OFieldSize);
+        if AParams[tel].DataType = ftString then
+          parambuffers[tel].buffer := getmem(OFieldSize * 4)
+        else
+          parambuffers[tel].buffer := getmem(OFieldSize);
 
 
         FOciBind := nil;
@@ -522,7 +525,10 @@
         FieldType := ftUnknown;
       end;
 
-      FieldBuffers[tel-1].buffer := getmem(OFieldSize);
+      if OFieldType in [OCI_TYPECODE_CHAR, OCI_TYPECODE_VARCHAR, OCI_TYPECODE_VARCHAR2] then
+        FieldBuffers[tel-1].buffer := getmem(OFieldSize * 4)
+      else
+        FieldBuffers[tel-1].buffer := getmem(OFieldSize);
 
       FOciDefine := nil;
       if OciDefineByPos(FOciStmt,FOciDefine,FOciError,tel,fieldbuffers[tel-1].buffer,OFieldSize,OFieldType,@(fieldbuffers[tel-1].ind),nil,nil,OCI_DEFAULT) = OCI_ERROR then
@@ -563,7 +569,7 @@
     exp       : shortint;
     cur       : Currency;
     odt       : POCIdateTime;
-
+    CurrBuff  : PChar;
 begin
   CreateBlob := False;
   with cursor as TOracleCursor do if fieldbuffers[FieldDef.FieldNo-1].ind = -1 then
@@ -572,7 +578,17 @@
     begin
     result := True;
     case FieldDef.DataType of
-      ftString          : move(fieldbuffers[FieldDef.FieldNo-1].buffer^,buffer^,FieldDef.Size);
+      ftString          :
+        begin
+          CurrBuff := fieldbuffers[FieldDef.FieldNo-1].buffer;
+          if not IsUTF8 then
+          begin
+            CurrBuff[FieldDef.Size] := #0;
+            CurrBuff := PChar(UTF8Encode(CurrBuff));
+          end;
+          move(CurrBuff^, buffer^, FieldDef.Size * 4);
+          PChar(Buffer + FieldDef.Size * 4)^ := #0;
+        end;
       ftBCD             :  begin
                            b := fieldbuffers[FieldDef.FieldNo-1].buffer;
                            size := b[0];
Index: sqldb/postgres/pqconnection.pp
===================================================================
--- sqldb/postgres/pqconnection.pp	(revision 16327)
+++ sqldb/postgres/pqconnection.pp	(working copy)
@@ -748,12 +748,17 @@
           end; {case}
           end;
         ftString  :
-          begin
+        begin
           li := pqgetlength(res,curtuple,x);
           if li > dsMaxStringSize then li := dsMaxStringSize;
-          Move(CurrBuff^, Buffer^, li);
-          pchar(Buffer + li)^ := #0;
+          if not IsUTF8 then
+          begin
+            CurrBuff[li] := #0;
+            CurrBuff := PChar(UTF8Encode(CurrBuff));
           end;
+          Move(CurrBuff^, Buffer^, li * 4);
+          pchar(Buffer + li * 4)^ := #0;
+        end;
         ftBlob : Createblob := True;
         ftdate :
           begin
Index: sqldb/sqldb.pp
===================================================================
--- sqldb/sqldb.pp	(revision 16327)
+++ sqldb/sqldb.pp	(working copy)
@@ -89,7 +89,7 @@
     FHostName            : string;
     FCharSet             : string;
     FRole                : String;
-
+    FIsUTF8              : Boolean;
     FSQLServerFormatSettings : TFormatSettings;
     function GetPort: cardinal;
     procedure Setport(const AValue: cardinal);
@@ -148,7 +148,7 @@
     property UserName : string read FUserName write FUserName;
     property CharSet : string read FCharSet write FCharSet;
     property HostName : string Read FHostName Write FHostName;
-
+    property IsUTF8: Boolean read FIsUTF8 write FIsUTF8;
     property Connected;
     Property Role :  String read FRole write FRole;
     property DatabaseName;
Index: sqldb/sqlite/sqlite3conn.pp
===================================================================
--- sqldb/sqlite/sqlite3conn.pp	(revision 16327)
+++ sqldb/sqlite/sqlite3conn.pp	(working copy)
@@ -479,7 +479,7 @@
  str1: string;
  ar1,ar2: TStringArray;
  st    : psqlite3_stmt;
-
+ CurrBuff: PChar;
 begin
   st:=TSQLite3Cursor(cursor).fstatement;
   fnum:= FieldDef.fieldno - 1;
@@ -507,13 +507,19 @@
                end
              else
                Pdatetime(buffer)^:= sqlite3_column_double(st,fnum);
-    ftString: begin
-              int1:= sqlite3_column_bytes(st,fnum);
-              if int1>FieldDef.Size then 
-                int1:=FieldDef.Size;
-              if int1 > 0 then 
-                 move(sqlite3_column_text(st,fnum)^,buffer^,int1);
-              end;
+    ftString:
+    begin
+      int1 := sqlite3_column_bytes(st, fnum);
+      if int1 > FieldDef.Size then
+        int1 := FieldDef.Size;
+      if int1 > 0 then
+      begin
+        CurrBuff := sqlite3_column_text(st, fnum);
+        if not IsUTF8 then
+          CurrBuff := PChar(UTF8Encode(CurrBuff));
+        move(CurrBuff^, buffer^, int1*4);
+      end;
+    end;
     ftMemo,
     ftBlob: CreateBlob:=True;
   else { Case }
@@ -695,6 +701,7 @@
   inherited Create(AOwner);
   FConnOptions := FConnOptions + [sqEscapeRepeat] + [sqEscapeSlash];
   FieldNameQuoteChars:=DoubleQuotes;
+  IsUTF8 := True;
 end;
 
 procedure TSQLite3Connection.UpdateIndexDefs(IndexDefs: TIndexDefs; TableName: string);
UTF8DB20101228.patch (8,918 bytes)

yang jixian

2010-12-28 09:09

reporter   ~0044638

Sorry for the wrong patch.

The UTF8DB20101228.patch is not include formatted code.

Marco van de Voort

2010-12-28 14:14

manager   ~0044652

Joost will make the final decision, but afaik fcl doesn't keep to the lazarus convention that all ansistrings are UTF8

yang jixian

2010-12-31 13:44

reporter   ~0044755

Last edited: 2010-12-31 14:40

In the last patch, there is a new property IsUTF8.
If it is true, that means the strings in database is already UTF8 else it is ansi string(1char=1byte) which is need to be encoded to UTF8.
In delphi, ansistring is different from string. String is UTF8 and ansistring is 1char=1byte.
if String = AnsiString, it seems a kind of waste.

LacaK

2011-01-04 17:49

developer   ~0044877

Last edited: 2011-01-10 07:17

IMHO the original TStringField type in Delphi was designed to hold only SBCS character data (See DataSize, which returns Size+1 ... and Size is number of characters ... http://docwiki.embarcadero.com/VCL/en/DB.TStringField.GetDataSize )
(Like TWideStringField type was designed to hold wide character data DBCS 1char=2bytes)

ATM in Delphi is String same like UnicodeString (uses UTF-16 encoding (not UTF-8), 2 or 4 bytes per character)

IMHO if we want use TStringField type to store UTF-8 encoded unicode data then we must allocate buffer 4*Size+1 (or 4*(Size+1) I am not sure about null termination in case of UTF-8?) not Size+1 (how is it now)
and
also all TSQLConnection components must write into buffer (in LoadField method) UTF-8 encoded data.
(somehow like in proposed patch ... although I will prefer solution without using new property IsUTF8 per TSQLConnection ... rather some kind of "auto-detection" ... may be test actual connection CharSet?)
--
Also important question is if FPC will follow Delphi in case of reinterpretation of String=UnicodeString, if yes, then for example TField.AsString and TField.Text should also return UnicodeString (UTF-16), not UTF-8 string like expected ATM by LCL.

yang jixian

2011-04-26 16:05

reporter   ~0047794

There is a small difference between TSQLite3Connection and Zeoslib component, that is no data loss in Zeoslib no matter what kind of the coding is.

So how can we store data into database or retrieve from it in Asia language with TSQLite3Connection?

Marco van de Voort

2011-07-14 15:40

manager   ~0049982

Probably, like Zeos there must be a properly defined what string where in the "connection" encoding is, and what in the "widgets" encoding is, with proper conversions where possible. With different settings. (so e.g. one can be utf8 while the other is ansi8859 etc).

The current utf8 proposals look too much like quick fixes IMHO.

haword

2011-11-30 07:49

reporter   ~0054562

Hi. I encountered the same problem. I check interbase unicode table and found what of FieldDefs.Size in interbase = Field data size set in table * 4. if change next code in procedure TSQLite3Connection.AddFieldDefs:
      ftWideString:
               begin
                 size1 := 255; //sql: if length is omitted then length is 1
                 size2 := 0;
                 ExtractPrecisionAndScale(FD, size1, size2);
                 if size1 > dsMaxStringSize then size1 := dsMaxStringSize;
               end;

to

      ftWideString:
               begin
                 size1 := 255; //sql: if length is omitted then length is 1
                 size2 := 0;
                 ExtractPrecisionAndScale(FD, size1, size2);
                 size1:=size1*SizeOf(String);
                 if size1 > dsMaxStringSize then size1 := dsMaxStringSize;
               end;

and all work fine. But this is not fix bug, this is only bugs workaround in this moments.

Marco van de Voort

2012-01-08 14:16

manager   ~0055585

Last edited: 2014-03-04 13:17

View 2 revisions

sizeof(string) looks like a random hack. That is a pointer, and thus 8 bytes on 64-bit, which doesn't correspond to anything unicode at all.

Reading this back, another comment to Lacak2: I'm not sure if unicode really changes that much, except that it provides an execuse to change the default string for the database base classes to tunicodestring or tutf8string or some alias (which exactly that is a separate discussion not related to database)

But that could have been done in 2.4.0 or 2.6.0 (both have tunicodestring) too.

The new (ansi)string support is strictly typed, so it doesn't help much in the case where encodings can vary runtime, unless you take the UTF16 variant (and even then you have the same problem occasionally, with surrogates and multi codepoint characters)

LacaK

2018-05-16 16:21

developer   ~0108343

There is in TRUNK already added support for CodePage aware TStringField

Issue History

Date Modified Username Field Change
2010-09-09 04:27 yang jixian New Issue
2010-09-09 04:27 yang jixian Status new => assigned
2010-09-09 04:27 yang jixian Assigned To => Joost van der Sluis
2010-09-09 04:27 yang jixian File Added: SqlLiteField.patch
2010-09-09 04:28 yang jixian Note Added: 0040906
2010-09-09 08:29 LacaK Note Added: 0040908
2010-09-10 04:45 yang jixian Note Added: 0040922
2010-09-10 04:51 yang jixian Note Added: 0040923
2010-09-10 04:51 yang jixian Note Edited: 0040923
2010-09-10 04:51 yang jixian Note Edited: 0040923
2010-09-10 04:52 yang jixian Note Edited: 0040923
2010-09-10 07:30 LacaK Note Edited: 0040908
2010-09-10 07:42 LacaK Note Added: 0040924
2010-12-27 15:44 yang jixian File Added: UTF8DBBug.patch
2010-12-27 15:48 yang jixian Note Added: 0044631
2010-12-27 15:50 yang jixian Note Edited: 0044631
2010-12-27 18:58 Marco van de Voort Note Added: 0044633
2010-12-28 09:07 yang jixian File Added: UTF8DB20101228.patch
2010-12-28 09:09 yang jixian Note Added: 0044638
2010-12-28 14:07 Marco van de Voort File Deleted: UTF8DBBug.patch
2010-12-28 14:14 Marco van de Voort Note Added: 0044652
2010-12-31 13:44 yang jixian Note Added: 0044755
2010-12-31 14:26 yang jixian Note Edited: 0044755
2010-12-31 14:28 yang jixian Note Edited: 0044755
2010-12-31 14:40 yang jixian Note Edited: 0044755
2011-01-04 17:49 LacaK Note Added: 0044877
2011-01-10 07:17 LacaK Note Edited: 0044877
2011-04-26 16:05 yang jixian Note Added: 0047794
2011-07-14 15:33 Marco van de Voort Relationship added related to 0019341
2011-07-14 15:40 Marco van de Voort Note Added: 0049982
2011-11-30 07:49 haword Note Added: 0054562
2012-01-08 14:16 Marco van de Voort Note Added: 0055585
2013-07-16 10:35 Reinier Olislagers Relationship added related to 0024745
2014-03-02 09:45 Reinier Olislagers Relationship added related to 0025801
2014-03-04 13:17 Marco van de Voort Note Edited: 0055585 View Revisions
2016-12-11 17:15 LacaK Relationship added related to 0031078
2016-12-11 17:16 LacaK Assigned To Joost van der Sluis => LacaK
2018-05-16 16:21 LacaK Note Added: 0108343
2018-05-16 16:21 LacaK Status assigned => resolved
2018-05-16 16:21 LacaK Fixed in Version => 3.1.1
2018-05-16 16:21 LacaK Resolution open => fixed
2019-08-12 13:31 LacaK Relationship added related to 0012206