Hi all,

I'm using TSQLQuery on SQLite in order to manipulation very long unicode strings (in tibetan).

On inserting there is no problem, everything in inserted correctly in the database, using this code, containing a SQLquery :

procedure ExecuteSQL(pRequete: string; pCommit: boolean);
   var
     conn: TSQLConnection;
     query: TSQLQuery;
begin
   query := TSQLQuery.Create(nil);
   try
     try
       conn := getConnection(_TypeConnection);
       query.DataBase := conn;
       query.UsePrimaryKeyAsKey:=false;
       query.SQL.clear;
       query.SQL.Add(pRequete);
       query.ExecSQL;
       // COMMIT de la transaction et ouvre une nouvelle transaction
       if (pCommit = true) then
          conn.Transaction.CommitRetaining;
       query.Close;
     finally
       query.Free;
       //conn.Free;
     end;
   except
     on E: Exception do
     ShowMessage(E.message);
   end;
end;

BUT when I try to get the values, also with a SQLQuery, if the value is not unicode (ascii characters) I can get more than 255 chars, but if it's unicode (tibetan) the returned value is always cut to 255 characters.

     conn: TSQLConnection;
     query: TSQLQuery;
     str: widestring;
     i, n: integer;
   begin
     query := TSQLQuery.Create(nil);
     try
       try
         conn := getConnection(_TypeConnection);
         query.DataBase := conn;
         query.UsePrimaryKeyAsKey:=false;
         query.SQL.Text := pRequest;
         query.Open;
         query.Last;
         pGrid.Clean;
         n := 1;
         pGrid.RowCount := query.RecordCount + 1;
         pGrid.ColCount := pHeaders.Count;
         pGrid.FixedCols := pNbFixedCols;
         for i := 0 to pHeaders.Count - 1 do
             pGrid.Cells[i,0] := pHeaders.ValueFromIndex[i];
         query.First;
         while not query.EOF do
         begin
           for i := 0 to pAttrs.Count - 1 do
           begin
		// HERE str is always cut
             str :=
query.FieldByName(pAttrs.ValueFromIndex[i]).AsWideString;
             pGrid.Cells[i,n] := str;
           end;
           query.Next;
           n := n + 1;
         end;
         // ajuste les colonnes au contenu
         pGrid.AutoAdjustColumns;
       finally
         query.Free;
         //conn.Free;
       end;
     except
       on E: Exception do
       ShowMessage(E.message);
   end;

Would somebody explain my why the value is always cut and give me a solution ? because didn't find any explaination of the internet, and as it's working in one way but not the other.

Thank you very much for your answer.

Hugues MOISY

How is the field defined?
Using fpc 2.7.1 storing 1000 unicode characters in a nvarchar(1000) is no problem. I tested with Chinese characters. If you are using an older fpc (< 2.6.0) you might better upgrade the compiler.

Ludo

Thanks Ludo for your answer,

You were right, it was a problem of field definition. changing to varchar(1000) it worked fine ! Thank you.

But I thikn there is an inconsistency in the driver, because in the update, the whole string is inserted, and in the select stmt only 255 chars are returned.

Hugues

Le Thu, 26 Apr 2012 00:29:48 +0530, Ludo Brands a ?crit:

This is more a SQLite issue. You pass pRequete to the database layer which sends it as is to SQLite. If pRequete contains a string which is too long for the column SQLite stores the excess data while other databases would truncate or generate an error. To read the data back, the driver uses the metadata to allocate memory to store the result set. Datasets can't work with variable length data other than blobs. If you would use tdataset to store the data instead of building your own query, the data would have been trunctated at the insertion also.
If you want variable length strings, use the TEXT datatype in your table declaration. Most dbcontrols can't display memo fields but I noticed you fill in your own grid so that shouldn't be a problem.

Ludo