Search the Community

Hello friends, Would anyone have a tip why the read-only error happens when I run the FireDAC commands in this order, like the MRE below? //READ ONLY TRANSACTION LTransactionRO := TFDTransaction.Create(nil); LQueryRO := TFDQuery.Create(nil); try LTransactionRO.Connection := FDManager.AcquireConnection('MY_DB', ''); LTransactionRO.Options.ReadOnly := True; LQueryRO.Connection := FDManager.AcquireConnection('MY_DB', ''); LQueryRO.Transaction := LTransactionRO; LQueryRO.Open('SELECT * FROM mY_TABLE'); //OK finally LQueryRO.Free; LTransactionRO.Free; end; //READ WRITE TRANSACTION LTransactionRW := TFDTransaction.Create(nil); LQueryRW := TFDQuery.Create(nil); try LTransactionRW.Connection := FDManager.AcquireConnection('MY_DB', ''); LTransactionRW.Options.ReadOnly := False; LQueryRW.Connection := FDManager.AcquireConnection('MY_DB', ''); LQueryRW.Transaction := LTransactionRW; //ERROR: cannot execute UPDATE in a read-only transaction. LQueryRW.ExecSQL('UPDATE MY_TABLE SET COLUM1 = COLUM1 WHERE 1 = 1'); finally LQueryRW.Free; LTransactionRW.Free; end; if I comment the line `//LTransactionRO.Options.ReadOnly := True;`, the SQL UPDATE run without errors. Thanks for any tips!

Hi, I had an old D7 + BDE + ODBC -> Sage app that did a simple extract from Sage, did a bit of magic and produced a report. Excellent. We upgraded Sage to V29 and the app wouldn't connect, with a not very useful error message. Excel -> ODBC -> Sage was fine. So I thought I would try with D11 Update 1 + FireDAC. After a few hours of fun (there aren't a lot of ODBC FireDAC examples and I don't have the Sage ODBC driver on my dev box), I got a proof of concept that could connect and I could run an SQL against Sage and get data. To cut a long story short, I built the bare minimum to get the data, save it to XML (Using TClientDataset), load it back up and process the invoice & Invoice_Line tables. The actual bits I want look fine, but then I noticed that some of the data in columns appeared to be the same for the whole column. Many hours later, tracing back through, I go back to my fist example AND "select * from invoice" and then finding invoice 99 Doesn't have the same data as "select * from invoice where invoice_number = 99" FOREIGN_INVOICE_NET="1300" (Actually -5.83 if selected via excel & ODBC or using the specific Where clause) FOREIGN_INVOICE_TAX="260" (Actually -1.17) FOREIGN_INVOICE_GROSS="1560" (Actually -7) INVOICE_NET="1300" (Actually -5.83) INVOICE_TAX="260" (Actually -1.17) INVOICE_GROSS="1560" (Actually -7) I'm a couple of days in now and properly pulling my hair out as they found the problem last week and they need the calculation for the monthly ledger entries to close the month off (read a lot of pressure). Does this ring any bells with anyone, any ideas? My next steps are: - get someone to compile my simple app on the latest D11.3 and see if it has the same issue. - get the data out via Excel and use that as the input to my calcs (a temporary solution and a chunk of work). Thanks for your time and thoughts, Jason Added a short video to demonstrate

So, my project group with 25 applications, built 7.6M lines of code in 3 min 16 secs without a hitch on my Lenovo P16. I was not surprised, having already tested the builds in my VM during the subscriber beta. But - when I ran the applications against certain production systems - I got an error I had never seen before. After numerous fiddlings with breakpoints, I discovered that the hosts that failed all were ip.ad.dr.es\instance instead of hostname\instance. When googling the error message, I eventually found references to Kerberos security failure and spelunking further, I found a suggestion to add the following to the connection string: In Delphi code, that means adding FDConnection.Params.Values['ODBCAdvanced'] := 'TrustServerCertificate=yes'; to your connection initalization code. Studying the release notes after 11.1, the 11.2 release states which is the one I had installed, but perhaps the Kerberos authentication bit was ignored because the older FireDAC code didn't twiddle the right params? Anyways - problem solved - No more The target principal name is incorrect for ip address host names.

Hi all, I recently migrated from D7 BDE (SqlServer) to D11 FireDac. All is working just fine, but the performance are getting worse. Have you got any advice about the better set up? Most of my query are read only select, aming to popolate DbGrid or to save data to other kind of structure. Thanks in advance

Hi all, is FireDAC extensible, so that I can add a custom database driver, e.g. for Absolute Database?

Win7, D11.2, FMX app, using TStringGrid and FireDAC and SQLite In a near-perfect working database, it should never happen, but it could happen elsewhere, and since I have the issue now, I would like to know how to resolve this. I was doing some what-if scenarios and came upon this issue. I searched around here and elsewhere on the internet but I can not find the answer. In this scenario, I was adding a new record, which has an auto-increment field in this test database, IDNo -- ('IDNo INTEGER NOT NULL PRIMARY KEY,'). And it generated an error. Of course I understand why this happened, and can later figure out a method to avoid it from happening. But for now, i need to know how to resolve the "closed dataset" issue because I can't see the data any longer. I've tried closing the Query, and reopening it. And tried closing the Connection and reopening it too. But they are not working. When a database or dataset closes, how do I re-open it through code so that I can see the data again in the StringGrid?

Hi Can someone take look at this call stack and threadlock that appears to happen very occasionally with our app on one VM. I have not been able to reproduce on a dev machine. We are using amBlocking for the firedac query but getting a call to check synchronize twice where we get a threadlock on second one. This is main thread. Have checked the 22.0 code and no changes this is from v19.0, procedure in FireDAC.Stan.Async: procedure TFDStanAsyncExecutor.Run Here is the offending code: else if (FMode = amNonBlocking) or not ( (rMsg.message >= WM_KEYDOWN) and (rMsg.message <= WM_DEADCHAR) and ((FAsyncDlg = nil) or not FAsyncDlg.IsFormActive) or (rMsg.message >= WM_MOUSEFIRST) and (rMsg.message <= WM_MOUSELAST) and ((FAsyncDlg = nil) or not FAsyncDlg.IsFormMouseMessage(rMsg)) or (rMsg.message >= WM_SYSKEYDOWN) and (rMsg.message <= WM_SYSDEADCHAR) ) then begin TranslateMessage(rMsg); DispatchMessage(rMsg); end; To fix im proposing to add in this to the above code block so messages can not be processed with amBlocking. if FMode <> amBlocking then Anyone else seen this behavior? Now also it could be possible for a threadlock if we were using amNonBlocking from what i can see - that would also be bad.

Hi, I am trying to rewrite a piece of legacy code which inserts a number of records in a bulk to SQL server tables(Total number of lines is around 200000 across 15 tables). Currently it uses ADO query to insert multiple records one by one. If the number of records are too high then this process takes a lot of time. I was trying to make it faster and came across FireDAC array DML and according to the documentation, inserting 100000 records can be done less than a second! So I implemented it and in my development machine (where application and database resides) it improved a lot. From 8 minutes to less than a minute. But when I tested it on a test environment where application and database resides on different machines connected by a network, performance is worse. (It was taking 10 minutes with ADO and with array DML it takes 15 minutes) I can't figure it out why array DML insert is taking more time than normal record by record insert. Any ideas? Delphi version: Delphi 10.4 SQL Server: 2017 SQL Driver: ODBC DRIVER 17 FOR SQL SERVER Kind regards, Soji.

I'm using the FireDAC TFDQuery component to run a SQL SELECT statement against a SQL Server database. The SQL statement contains a few 'calculated' column expressions using SQL functions such as CONVERT and CAST. For example : CONVERT(DECIMAL(9, 6), 0) AS TotalHours and CONVERT(TINYINT, 0) AS WorkflowState. In the resulting dataset, these calculated TField objects are always set to ReadOnly=True. I want to populate these calculated fields in code after the dataset has been returned. I know I can do this by changing the ReadOnly property of each field : Dataset.FieldByName('MyCalcField').ReadOnly := False; But is there a setting/option within the query components that I can use to force the fields created to always default to ReadOnly=False?

Hi I have discovered that update sql executed via TFDConnection.ExecSQL against a TFDMemTable fails with "List index out of bounds (8)" when there is a calculated field in the dataset. In this case the first calculated field has index 8 (9th field) as referenced in the error message. If I remove the calculated field, the update executes without issue. I assume, then, that the update tries to do something with the calculated fields that it shouldn't... is there a way to avoid this? Thanks

I am using an old version of FireDAC (Berlin time frame) and when opening an updateable query it implicitly calls sp_pkeys which can take ~1.5s to return. It seems to do so only once and then caches the PK info for the table appearing in the SQL FROM clause. Grepping the source has yielded no information, and my Google fu is failing me. Depending on the use case, this sometimes results in poor performance. For instance if the user fires up the app and goes into a screen to update some data and then closes the app, they trigger all of the metadata queries during their usage. Over slower VPN connections this can be a real problem. It would be nice to be able to prevent FireDAC from making such a metadata call. Can we specify the PK column at design-time or run-time and prevent the metadata query? After all the database PKs seldom change.

Hello Folks, we are trying to use the TSQLTimeStampOffsetField defined in Data.DB.pas for a TFDMemTable. We created a static field list and tried to open the TFDMemtable. We run into an error: The call stack ... Data.DB.DatabaseError('Type mismatch for field ''Start'', expecting: TimeStampOffset actual: Unknown',$1767270) Data.DB.DatabaseErrorFmt('Type mismatch for field ''%s'', expecting: %s actual: %s',(...),$1767270) Data.DB.TDataSet.CheckFieldCompatibility($1758D20,$17FFE10) Data.DB.DoBindFields($17FFF60) Data.DB.TDataSet.BindFields(True) FireDAC.Comp.DataSet.TFDDataSet.InternalOpen Data.DB.TDataSet.DoInternalOpen Data.DB.TDataSet.OpenCursor(???) FireDAC.Comp.DataSet.TFDDataSet.OpenCursor(False) Data.DB.TDataSet.SetActive(???) FireDAC.Comp.DataSet.TFDDataSet.SetActive(???) Data.DB.TDataSet.Open FMain.TfrmMain.FormCreate($1780E30) Vcl.Forms.TCustomForm.DoCreate Vcl.Forms.TCustomForm.AfterConstruction System._AfterConstruction($1780E30) Vcl.Forms.TCustomForm.Create(???) Vcl.Forms.TApplication.CreateForm(???,(no value)) ... indicates that the TDataSet.CheckFieldCompatibility method points to a FieldDef for the column Start, for which the data type is unknown. I have tried to setup the FieldDefs first before opening the TFDMemTable but that did not help either. Why can I define a field of type TSQLTimeStampOffsetField but when I open the dataset the data type is unknown? I am using Delphi 11 Alexandria Version 28.0.42600.6491. Thanks for a short answer in advance. Salut, Mathias

Dear fellow programmers, Can anyone tell me if Firebird 4 is fully supported by Delphi/FireDAC? Back in July I read a post, in a Google groups stating that there not support. Yesterday I tried again and the story was the same. During a talk by Carlos Canty yesterday it seemed to me that there were people already using it in production, so I am a little bit confused here. Has FireDAC been updated to work with FB4? If yes, since which version? I'm on Delphi 10.4.2 and just waiting for things workwise to calm a bit to migrate to Delphi 11. Thanks in advance. Best regards, Carlos

Hi, The goal, I have a text file with words and I want to export in a FDMemTable words with length between 4 and 12 ? I don't know how to get the value in onWriteRecord event, is there a way to ? I can get the value in the OnWriteValue one but fired after onWriteRecord it's unusefull. Any hints (not involving a localsql DELETE)

Hi, I'll try auto-increment value in mongodb although, basically not support autoincrement field value in mongodb. example) https://www.tutorialspoint.com/mongodb/mongodb_autoincrement_sequence.htm that is working in cli. but, i want do it firedac component. and i can't find any support execute script procedures. can i use that script in firedac? or other way??? or impossible? p.s : sorry for my poor english.

Never worked with SQLite before and now for my project need to use it but some questions and misunderstandings came on application model (VCL). I used to use GUID data type for unique records, in SQLite table the field 'uuid' is declared as binnary 'guid', and I created a trigger to generate the value, in application in FDConnection.Params.GUIDFormat=guiBinary, but when try to execute a clean FDQuery a message shows up: [FireDAC][DatS]-10. Fixed length column [uuid] data length mismatch. Value length - [32], column fixed length - [16]. changing FDConnection.Params.GUIDFormat=guiString show up a 'Wrong guid format error'. and here is the trigger for guid generation: CREATE TRIGGER demotable_after_insert AFTER INSERT ON demotable FOR EACH ROW WHEN (NEW.uuid IS NULL) BEGIN UPDATE demotable SET uuid = lower(hex(randomblob(16))) WHERE id = NEW.id; END So the question is how to deal with guids in Delphi+SQLite? Should I generate them from application? Do I need to make some mappings? The second question is about using generators for auto incremented fields, is this possible?

I've written a fairly small web app for testing and learning using WebBroker that makes a few InterBase database queries and I've got it working as both an ISAPI DLL under IIS on Windows and as an Apache Web Module for Apache 2.4 on Windows. I'm now testing it under Apache for Linux and while a simple database query still works, another part of this app does not. One of the web actions is hooked up to a TDataSetTableProducer and it requires a wait cursor (TFDGUIxWaitCursor) in order to work. That component has a Provider property where leaving it at the default of FMX worked just fine for Windows versions (IIS and Apache) but fails under Apache for Linux. I tried changing the Provider to Console and replacing the used units but that did not help. Does anyone know how to use datasets for Apache web modules under Linux using FireDAC which requires a wait cursor? Reference: http://docwiki.embarcadero.com/Libraries/Sydney/en/FireDAC.Comp.UI.TFDGUIxComponent.Provider

Hello - I think I must just be missing something, but I wrote some code that creates a list of strings setting up a transaction that writes data to a parent table and several child tables, linked by the key generated from the record in the parent table. (We have text files containing disparate data in a single record, all keyed by location and date/time. It had worked but now is not. I have done some editing of the code and tweaking of the table structures, but none I can think of that would cause the issue. The app loads the data from 2 text files into 2 temporary tables that are then queried in the sequence created in the transaction. I get all the records in the parent table, but none in any of the child tables. I'm at my wits end trying to figure out why it's not working. If someone can point me to something, even if it embarrasses me to no end, I'd be ever grateful. I have attached the generated transaction. Thanks. Teri SQLStr_Append.txt

Has anyone been able to get the BatchMove component to work correctly with PostgreSQL in AppendUpdate mode where there is an auto inc key field? My BatchMove component creation code is below. In addition, either the FDConnectionPG ExtendedMetaData param is set to true or the FDQueryPG updateOptions.AutoIncFields is set. Either way produces the same result, although I understand there is an efficiency hit with use of ExtendedMetaData. The problem is that whilst new rows get added to the table and my auto inc key field value gets set, it is running backwards ie -1, -2, -3 etc. Am I missing something or is this a bug? var FBatchMove: TFDBatchMove; FReader: TFDBatchMoveDataSetReader; FWriter: TFDBatchMoveSQLWriter; F:TField; begin FBatchMove := TFDBatchMove.Create(nil); FReader := TFDBatchMoveDataSetReader.Create(FBatchMove); FWriter := TFDBatchMoveSQLWriter.Create(FBatchMove); try FReader.DataSet:=FDQueryPG; FWriter.Connection:=FDConnectionPG; FWriter.TableName:='dummy'; FBatchMove.CommitCount:=1000; FBatchMove.Mode := dmAppendUpdate; FBatchMove.options:=FBatchmove.Options+[poIdentityInsert]; FBatchMove.Execute; finally FWriter.Free; FReader.Free; FBatchMove.Free; end;

Guys, i'm having trouble using TBatchMove in my project. I'm migrating data from a Firebird database to a PostgreSQL database. I thought to retrive the data from de FB database without the primary key fields, because the destination tables in the PG database already have a primary key setted as serial, therefore the DB would create the indexes i needed. However, TBatchMove doesn't seem to work well with implicit values, everytime i try to execute the command, it tries to pull the primary key value from the closest field. When i added mappings to the component... Well, it started to try sending null values to the table. I am getting desperate, any solutions for this problem?

I am using a TFDMemTable to import text data. When processing comma delimited files with double-quoted values an error occurs when the last line in the file does not have a line feed. To reproduce this problem create a text file with the following values in notepad: "name","date","amount" "Alpha","1/1/2021","100" "Bravo","1/2/2021","200" "Charlie","1/3/2021","300" Make sure you save the file with the final cursor at the end of the "Charlie" line. The following error will occur on BatchMove.Execute; The error does not occur if double-quote characters are not used. The error does not occur if you add a line feed so the cursor is on the line after "Charlie" when the file is saved. myTable := TFDMemTable; myReader := TFDBatchMoveTextReader; myWriter := TFDBatchMoveDataSetWriter; myMover := TFDBatchMove; procedure Test (const p_FileName: String); var // --- Added in attemp to bypass the issue I: Integer; // --- Added in attemp to bypass the issue begin myReader.FileName := p_FileName; for i := 0 to myReader.Datadef.Fields.Count-1 do // --- Added in attemp to bypass the issue myReader.Datadef.Fields.DataType := atString; // --- Added in attemp to bypass the issue myReader.DataDef.WithFieldNames := True; myWriter.DataSet := myTable; myWriter.Optimise := False; myMover.Reader := myReader; myMover.Writer := myWriter; myMover.GuessFormat; myMover.Analyze := [taDelimSep, taHeader, taFields]; myMover.AnalyzeSample := 50; myMover.Execute; end; The files being processed are automatically obtained from other systems. We cannot control the presence of the linefeed without modifying the received file. Can you please advise on how to handle this issue?

Hi, I am surely doing something wrong there, but I can't understand where. I wrote a FDSQLiteFunction (well more than one) All of these return me widestrings, I was expecting Currency, Date, Currency. Note, first one came from help http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.SQLite_Sample procedure TDM.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject); begin // from sample AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsInteger; end; procedure TDM.FDSQLiteFunctionAAAAMMJJ2DateCalculate( AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject); var vSDate : String; vDate : TDate; begin vsDate:=AInputs[0].AsString; vDate:=EncodeDate(StrToInt(Copy(vsdate,1,4)), StrToInt(Copy(vsdate,5,2)), StrToInt(Copy(vsdate,7,2))); AOutput.AsDate:=vDate; end; procedure TDM.FDSQLiteFunctionMontantCalculate( AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject); var vMontant : Currency; IMontant : String; begin IMontant:=AInputs[0].AsString; Vmontant:=StrToFloat(IMontant); if Ainputs[1].AsString='-' then vMontant:=Vmontant * -1; AOutput.AsCurrency:=vMontant; end; Don't understand why! Ok, it's at design time, but I think that matters.

I have a MSSQL table of financial transactions and I retrieve the data with a FDQuery which has the following statement: 'SELECT * FROM tAccounts ORDER BY AccName, TxnDate, Deposit' And executing that from the TFDQuery gives the correct results - a list in Account order and within that date order. See attached SQLView.jpg, specifically records 501 and 502 (correct date order). However, when I view this same table in a DBGrid in my application, the order is not the same. See attached DBGView.jpg, note the 20 Feb is before 17th Feb. The field is a Date datatype and there is no alteration to the Select statement anywhere in the application. The DBGrid is the first thing shown in the application and neither Formcreate or FormActivate do anything to alter the data returned. This 'mis-sorting' shows on several other of the records and my thinking is that in the DBGrid the TxnDate field is sorted in yyyy-mm-dd order, as in '20210217', which numerically comes before '20210220'. So this is what's happening, it seems, but why, when the SQL returns the data in the correct order does the DBGrid 're-sort' in a numeric fashion?

Hi, I'm aware that you can use a macro substitution in a select statement when you don't know some value until runtime (like a fieldname). If there a similar function that can be used in a Locate? I am developing a 'Search' on an accounts program where the user first selects a fieldname in which he looks for a value, maybe a 'Payee' fieldname or an 'Account' fieldname or a 'Deposit' fieldname. So the field on which to conduct the locate isn't known until runtime. A usual locate might look like this: if fdqA.Locate('Payee', cPayee ), []) then ShowMessage('Located!') else ShowMessage('Failed!'); But the user might want to search on 'Account' or 'Deposit'.

Hi I have a table with a field called 'cTaxYear', which is created as an Internal Calculated field (fkInternalCalc), it's a 7 character string field and contains, for instance, '2008-09', being a tax year (here in the UK) from 7Apr2008 to 6Apr2009. The calculation is based on the date a dividend is paid out and works out what tax year that dividend falls into. It works perfectly well in dbgrids etc. However, when I try to create a filter, for instance to filter all the records with a tax year of '2008-09' the dbgrid displays nothing. There are about 150 records which do contain '2008-09' in the 'cTaxYear' field and the filter expression is 'cTaxYear = ' + QuotedStr( '2008-09' ); Reading about filters I see it says 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' (which is why I created an fkInternalCalc field)) but says nothing more about any settings that should be used (like maybe setting indexfieldnames). Does anyone know for sure that an InternalCalc field can actually be used in a filter expression? Or if it definitely can't?