emileverh

Okay, and where is the setting which I am searching for? Thats why I am asking this here on this forum....

Or in a Delphi connection-setting (TFDConnection) which I don't know yet....

What an other tool does, says nothing about Delphi. And the search for '256' I already... so still no result ;-((

I have a SQLite table. Which has a column 'Description' and is initially defined as varchar(256). But that limitation is too small for the customers and I want to go to varchar(1024). According to the SQLite website they don't care; "SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N."" I changed in the TFDQuery the field .Size definition of that field from 256 to 1024 in the Fields-editor. But still Delphi cuts of my string to 256 chars. When I Google I see all kinds of scripts of creating a temp-table and redefine the thing. I don't want to do that. Question; is there a property (somewhere, no size checking?!?) which I can change so that Delphi does not cut of the string? Thanks! -Emile CREATE TABLE IF NOT EXISTS cardlines ( UniquePK INTEGER PRIMARY KEY AUTOINCREMENT, Description varchar(256) NOT NULL, ........

The what they called performance issue I never noticed. You have to see it in a relative way. But I am sure that I have unique values.

FireDAC

If I was you in SQLite I defined 'database_id' as AUTOINCREMENT. No duplicates anymore! CREATE TABLE databases ( database_id INTEGER PRIMARY KEY AUTOINCREMENT And if you want to know the last inserted id, call the following statement: select LAST_INSERT_ID() as seq

The final solution is different than proposed. I did not know but the SUM() function can return NULL values. So on larger datasets I did get 'WideString'!!! conversion error messages on SQLite. So I had to use IFNULL. May be not for all the most elegant solution. But this TFDQUERY works now for both MySQL and SQLite select IFNULL(SUM(AmountValue),0) AS SumVal from camtentries And in the code: var mycurr: currency := qryMyQuery.FieldByName('SumValue').Value; Normally I don't work with .FieldByName and .Value. I prefer this: var mycurr: currency := qryMyQuerySumValue.AsCurrency; But know the app is not complaining. I have hundreds of queries, but only 2 SUM()'s. So I can live with that. I don't use them in a loop, so performance is not an issue. And I develop the app with MySQL. Because I can easy test my data together with HeidiSQL. The field names become also strange select IFNULL(SUM(AmountValue),0) AS "SumVal::DOUBLE:" will be qryMyQuerySumValueDOUBLE.AsCurrency; Although the first answers where not the implemented one. I want to thank all the people in general on this forum. As solo entrepreneur I don't have a collegae to ask. So thank you all!

The ::TYPE syntax is even more elegant! Thanks you helped me a lot!!

I am sorry! Yes it was a bit overwhelming, but you helped me. So thanks 😉 I have a direction now. The code as shown on the EMB website does not compile. With this code it compiles for both versions..... But I am losing data? The dtFloat type does not exist. I am accurate enough with this code? Please no discussions about the "with"-statements, I need to rewrite this.... Thanks in advance! with qrySumPositive.FormatOptions.MapRules.Add do // Need to add the .Add else it does not compile begin SourceDataType := dtDouble; // <<<< THIS IS A GUESS. THE TYPE dtFLOAT DOES NOT EXISTS TargetDataType := dtFmtBCD; end; qrySumPositive.FormatOptions.OwnMapRules := true; with qrySumPositive do begin ParamByName('prmDateStart').AsDateTime := MyStartOfaDay(ADateStart); ParamByName('prmDateEnd').AsDateTime := MyEndOfaDay(ADateEnd); Open; Result := qrySumPositiveSumVal.AsCurrency; Close; end;

Nice. But this is overwhelming, I don't see any practical info for me now....

Team! I have one large application which I compile twice, one is for MySQL and the other for SQLite. For regular queries like select * from everything goes fine. But it goes wrong with SUM() functions. The query defined in a TFDQuery below works fine in MySQL, but when I compile the application for SQLite I get an exception "Type mismatch for field 'SumVal', expecting: FMTBcd actual: Float'. " The definition of AmountValue in the database is: AmountValue DECIMAL(13,4) NOT NULL And here is the query: select SUM(AmountValue) AS SumVal from camtentries Is there a property for this one and only TFDQuery or for the field qrySumPositiveSumVal.AsCurrency where it compiles fine in both applications? I prefer not to set an overall property in the TFDConnection. Thanks!!! -Emile

Correct! But... it's not an easy part of software to write!

Thanks Remy for your help!!!

You are right force deleting a file is not a good idea. The other (unknown for me) process needs the file. Any idea if there is an API to add a kind of duplication number like: file (1).txt, file (2).txt, file (3).txt ? I did Google something but I did not find 1-2-3 an API for that. Anybody knows?