[Partially Solved] dbMemo truncates to 256 bytes when inserted lazarus / 16-Databases ---------------------------------------------------------------------- Pascaluvr · 2013-05-15 03:06 I have a DBMemo as a data aware component, but when I ApplyUpdates it is truncated to 256 bytes.  Anyone with ideas for me? Thanks in advance ---------------------------------------------------------------------- Lacak2 · 2013-05-15 12:16 Which data access component do you use ? Guessing from your signature is it SQLite3Connection + TSQLQuery ? (or sqlite3dataset or zeos ?) How do you have defined "memo" column in your database table? ---------------------------------------------------------------------- Pascaluvr · 2013-05-15 12:24 hi Lacak2.   SQLite3 and TSQLQuery.  In the db tho its just defined as TEXT = I am experimenting with the FTS capability which ignores any type affinity. ---------------------------------------------------------------------- Pascaluvr · 2013-05-16 02:39 To explain a little more ....   see: http://www.sqlite.org/fts3.html Using Connection.Executedirect I can insert a Memo of any size (as in, greater than 256) bytes.  I can understand that DBGrid would put a restriction on size....  but i  cant understand why a TMenu (not a tdbmenu) will not show more than 256 bytes. I have used an independent SQL Manager to view the contents of the database and its all there, so clearly I dont know how to view the contents of the field.   I am guessing this is falling into 'blob' category - an area I have no knowledge. So maybe the question should be:   I have a column in an sqlite db that is text (usually greater that 256 bytes).  How do I access this column and assign it to a TMemo? Any help appreciated ---------------------------------------------------------------------- BigChimp · 2013-05-16 05:33 I'd suspect the TEXT is seen as a VARCHAR, max 256 characters by th e db layer??? Have you tried printing the field value to a memo/editox programmatically? ---------------------------------------------------------------------- Lacak2 · 2013-05-16 05:47 Yes it seems, that SQLite3Connection does not recognize column datat type as TEXT, but falls to char/varchar. And if there is no size specified length fails to 255. Can you test DataType of your TEXT field ? f.e. using writeln(FieldTypeNames(SQLQuery1.FieldByName('x').DataType)); ---------------------------------------------------------------------- Pascaluvr · 2013-05-16 07:20 Lacak ... using your code the field is reported as 'String'. I have used both SQLQuery and Connection.ExecuteDirect to Select this field ....   both give me the 256 byte result. Sqlite Expert Personal is able to display this field (column) correctly. Any ideas how I can access it correctly? Thanks [edit]  This is assigning the returned field directly to a TMemo and not using Data Aware controls.  I am guessing that i am going to need to read this as a blob ...   but i will need some pointers how to do that. ---------------------------------------------------------------------- mdalacu · 2013-05-16 08:02 This is a portion from my program , you don't deed the decompresion part, simple to adapt it. Code: [Select]procedure ReadBLOB;     var       Stream:TMemoryStream;       DS:TDecompressionstream;     begin       Stream:=TMemoryStream.Create;       try         if not TBlobField(SQLQuery3.Fields[0]).Isblob Then ShowMessage('Error, selectul nu intoarce BLOB!');         TBlobField(SQLQuery3.FieldByName('ZipTranscript')).SaveToStrea m(Stream);         if Stream.Size > 0 then           begin             Stream.Position := 4; //Mysql baga un header de 4 octeti = lungimea blob-ului decomprimat. Trb. decartat ca sa mearga decompresia cu zlib             DS:=TDecompressionStream.Create(Stream);             Memo1.Lines.LoadFromStream(DS);           end                            else           Memo1.Text:='ERROR: Stream zize 0.';       except         Memo1.Text:='ERROR: Unknown exception.';       end;       DS.Free;       Stream.Free;     end; ---------------------------------------------------------------------- Lacak2 · 2013-05-16 10:12 Quote from: Pascaluvr on May 16, 2013, 09:20:45 amLacak ... using your code the field is reported as 'String'. So it is the problem. Your result field is mapped to ftString not to ftMemo. Can you post here SELECT which do you use and CREATE TABLE for table which do you access? ---------------------------------------------------------------------- Pascaluvr · 2013-05-17 00:17 mdalacu: Thanks for that code. Obviously I get the message: 'Error, selectul nu intoarce BLOB!' I subsequently get a Runtime 219 (Invalid Type Cast). Lacak2       Conn.ExecuteDirect('Create Virtual Table ' +                      'if not exists ' +                      'FTSdb using FTS4(Body memo) '); and       Q.SQL.Text := 'Select DocID, Body from FTSdb';       Q.Open;       Body.Text := Q.FieldByName('Body').Text ;  { Instead of Text I have tried AsString ) In the CREATE I changed the datatype to BLOB but got identical results to the above.  I wasn't surprised as the SQLite documentation for FTS states: Quote If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way. Unless someone has other suggestions I may have to look for an alternative to SQLite to play with FTS. Thanks for all the help to date. ---------------------------------------------------------------------- Pascaluvr · 2013-05-17 08:14 OK - I have found a sort of solution.  By using a low-level sqlite wrapper I can access the column in its entirety. That would suggest to me that Lazarus is not handling fields in Virtual Tables correctly.  BUT I am the last person to call something a bug .. when its still probably something I am doing wrong. PS  BigChimp - there is a reason for learning low-level stuff ---------------------------------------------------------------------- mse · 2013-05-17 08:39 Quote from: Pascaluvr on May 17, 2013, 02:17:13 am      Conn.ExecuteDirect('Create Virtual Table ' +                      'if not exists ' +                      'FTSdb using FTS4(Body memo) ');                      'FTSdb using FTS4(Body TEXT) '); ---------------------------------------------------------------------- BigChimp · 2013-05-17 08:46 Quote from: Pascaluvr on May 17, 2013, 10:14:23 amPS  BigChimp - there is a reason for learning low-level stuff Agreed, but diving into the FPC sqldb code and the underlying sqlite driver would make more sense then? Unless that is what you are talking about? As for it being a bug: yes, it may well be. My question is whether the data type can be reliably detected in virtual tables though. (I haven't worked with them and don't know the sqldb underlying sqlite code so I may be too pessimistic here). ---------------------------------------------------------------------- LacaK · 2013-05-17 09:49 I do not know much about virtual tables, but do you use properly call to sqlite3_declare_vtab() see: http://www.sqlite.org/c3ref/declare_vtab.html Another idea try use CAST in your select: Select DocID, cast(Body as BLOB) from FTSdb ---------------------------------------------------------------------- Pascaluvr · 2013-05-18 02:44 Quote from: BigChimp on May 17, 2013, 10:46:55 amQuote from: Pascaluvr on May 17, 2013, 10:14:23 amPS  BigChimp - there is a reason for learning low-level stuff Agreed, but diving into the FPC sqldb code and the underlying sqlite driver would make more sense then? Unless that is what you are talking about? As for it being a bug: yes, it may well be. My question is whether the data type can be reliably detected in virtual tables though. (I haven't worked with them and don't know the sqldb underlying sqlite code so I may be too pessimistic here). Diving into the code is my next project on my next days off.  I doubt I will find much tho given my inexperience, but on first hunch it appears that SQLite3Connection recognises a Virtual Table - but does not acknowledge the 'Using FTS4' and thus realise that all columns are VarChar(,,,,) and  not String (limited to 256 bytes).  Since SQLITE3 Expert Personal and the wrapper I used was able to extract the full column (rather that the first 256 bytes) - either both use a LongString as a default or they recognise that FTS uses Longstrings. In my wrapper there was nothing fancy: Quoteprocedure TForm1.Button1Click(Sender: TObject);   var   database: TSqliteDatabase;   tab: TSqliteTable;   s: utf8string; begin   database := TSqliteDatabase.Create('c:\ProgData\fts.db');   try       try         tab := database.GetTable('SELECT DocId, Body FROM  FTSdb WHERE DocID=2');         Memo1.Text := tab.FieldAsString(tab.FieldIndex['Body']);       finally         tab.free;       end;   finally     database.free;   end; end; ---------------------------------------------------------------------- Pascaluvr · 2013-05-18 02:50 Quote from: LacaK on May 17, 2013, 11:49:13 amI do not know much about virtual tables, but do you use properly call to sqlite3_declare_vtab() see: http://www.sqlite.org/c3ref/declare_vtab.html Another idea try use CAST in your select: Select DocID, cast(Body as BLOB) from FTSdb LacaK I dont call sqlite_declare_vtab...  I just use the Lazarus components. Your 2nd suggestion looks very interesting tho and will give it a try as soon as I can, Thanks for the ideas. ---------------------------------------------------------------------- ashleygon · 2014-07-01 06:56 I would recommend you to use the Long Path Tool. I'm sure, it will sort out your problem. ---------------------------------------------------------------------- Mando · 2014-07-01 08:42 Hi: I'm not sure if this is the solution, but you should try it. try to specify compatibility with long strings at begin of your unit. Code: [Select]{$mode objfpc}{$H+} ... or ... {$mode delphi}{$H+} if you don't specify {$H+}, all the strings are short strings (max 255 chars). regards. ---------------------------------------------------------------------- Mando · 2014-07-02 08:33 Hi: Pascaluvr, is solved your problem? regards.