Search the Community

In Delphi Rio 10.3.3 (I've not tested this on other versions) there seems to be a parsing bug with this recursive CTE: WITH RECURSIVE example1_cte (Id, No, ParsedText, Unparsed) AS ( SELECT Id, 0 as No, '' as ParsedText, TextToParse || ',' as TextToParseWithTrailingComma FROM test1 UNION ALL -- "ALL" is not strictly necessary in this example SELECT Id, No+1, -- increment the counter for this row SUBSTR(Unparsed, 0, INSTR(Unparsed, ',') ), SUBSTR(Unparsed, INSTR(Unparsed, ',') + 1) FROM example1_cte WHERE Unparsed <> '' ) -- SELECT from the recursive cte SELECT Id, No, TRIM(ParsedText) as ParsedText FROM example1_cte WHERE ParsedText <> '' ORDER BY Id ASC, No ASC; When executed a Firedac error message appears: [FireDac][Phys][SQLite] Error: no such table column: example1_cte.Id. And yet, if the line below the UNION ALL is changed to SELECT Id+0, (I.e. just adding a zero to Id), the error message goes away, and the CTE works correctly. btw, this query works fine without the "+0" in other SQLite query processors (SQLiteStudio). I think this is a bug, but cannot locate it in JIRA. Attached is a quick and dirty example project. (Change the FDQuery1 to remove the "+0" to see the error.) I don't have 10.4 so not sure this still exists. Project1.dpr Project1.dproj Unit1.dfm Unit1.pas

If I connect a TFDTable to a MariaDB table containing 2000 rows, and immediately call FindLast(), I get back True for success, but it positions on the 100th row of the table, not the 2000th. FDTable1.Open(); B:=FDTable1.FindLast(); Memo1.Lines.Add(Format('Return=%s A=%d', [NameFromBoolean(B), FDTable1A.AsInteger])); FDTable1.Close(); I'm running Delphi XE10.2. I've dug through the FireDAC source code and found it is applying LIMIT 100 to the initial select query to populate the TFDTable, but I'm not understanding why the FindLast runs in that initial query result rather than the whole table. Is there any workaround for this? Thanks.

What exactly are PoolCleanupTimeout and PoolExpireTimeout in FireDAC connection definition params? Documentation not explain these parameters. @Dmitry Arefiev ?

I have recently encountered a situation where code was explicitly setting queries to use FetchOptions.RecordCountMode := cmTotal. The query involved was rather complex and therefore expensive to execute and due to the RecordCountMode set, was being executed twice as per the FireDAC Documentation. As a result, together with all the other workload it was bringing SQL Server to it's knees. That got me thinking about what are appropriate (read least resource utilization with best performance) settings to use in the following scenarios: 1) You open a query to gather data and iterate over each record not updating the record itself but perhaps issuing other SQL Statements., or generating a report -such a query could be ReadOnly, ForwardOnly 2) You open a query with RequestLive = True and iterate over it updating values which FireDAC posts back to the database In both of the scenarios above, to display progress information you want the total record count. What do developers usually use for FireDAcC FetchMode/RecordCountMode and other settings in such scenarios and what if any combination of settings are done globally with a TFDManager vs. locally at the query level?

Did anyone else tried to use TDEVentAlerter on Informix? I have installed the DBMS_ALERT and setup everything. When the component tried to register itself the error is: When the Event alerter tries to execute: EXECUTE PROCEDURE DBMS_ALERT_WAITANY(:name, :message, :status, :timeout)", ATimes=0, AOffset=0] The error message is: [FireDAC][Phys][ODBC][Informix][Informix ODBC Driver][Informix]Argument must be a Statement Local Variable or SPL variable or argument for an OUT or INOUT parameter. Delphi 10.4.1 there is on RSP-30452.

Few months ago I prepared small demo application used FireDAC SQLite connection on an Android system. And it worked fine in an application built in Delphi 10.3. Then I updated Delphi to 10.4. And now I got: Project xxx.apk raised exception class ESQLiteNativeException with message ''. and after that: [FireDAC][Phys][SQLite] ERROR: no such table: items. Even table 'items' exists in the database file and I did not any changes in sources. Anyone had the same issue?

I have had issues before with master/detail relationships with FireDAC. Here is my situation: RadStudio 10.1-Berlin, connecting to Pervasive SQL via ODBC (although I have had similar problems with MSSQL-Native, and Access via ODBC. Master dataset is a simple TFDMemTable with a few fields, the important one being an invoice number named "InvNmbr". Detail dataset is an equally simple TFDQuery . In this case I only need ONE field in the query If I set the detail query SQL to a simple single-table "SELECT InvNmbr, TaxAmount1 FROM XTOTAL WHERE InvNmbr=:INVNMBR;", set MasterSource to my FDMemTable's datasource and set MaterFields to 'InvNmbr', I get the error in the subject line. HOWEVER, if I get the detail query SQL to include a join on two related tables (which returns an identical recordset) "SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR", and set the MasterSource/MasterFields the same way, it works properly?! I am at a loss to explain this? Of note: - There is only the one parameter so why is FireDAC even messing about with a parameter #2? - I thought maybe the InvNmbr field and INVNMBNR parameters might be muddling things, so I tried fully qualifying it in the single table query (as "XTOTAL.InvNmbr") and it does the same thing. - In my ODBC database the XMASTER and XTOTAL tables are both keyed on InvNmbr, and one matching record per InvNmbr exists in each table. Code that does not work: fdqStatementMaster.SQL.Text := 'SELECT XTOTAL.InvNmbr, TaxAmount1 FROM XTOTAL WHERE XTOTAL.InvNmbr=:INVNMBR;'; fdqStatementMaster.MasterSource := dsCustomerStatement; fdqStatementMaster.MasterFields := 'InvNmbr'; fdqStatementMaster.Open(); Code that does work: PrepTempQuery(fdqStatementMaster, 'SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR'; fdqStatementMaster.MasterSource := dsCustomerStatement; fdqStatementMaster.MasterFields := 'InvNmbr'; fdqStatementMaster.Open(); I am baffled and frustrated and will be extremely grateful for a nudge in the right direction.

Hi, I'm using a FireDac query to select different sets of data in an MSSQL database. I open the first query like this: dm.fdq.Open('SELECT * FROM tblUsers WHERE MSL = :pMG ORDER BY Sname, Fname',[ 1 ] ); which works fine. I then need to select a different set of records so I issue this (assuming this replaces the above statement): dm.fdq.Open('SELECT DISTINCT Fullname, Fname FROM vPGCEnrolments WHERE MatchGroup = :pMG AND MatchYear = :pY AND YES = 1',[ gvMatchGroup, gvYear ]); This works but I start to get Access violation error messages. The select works fine though, as the correct data displays. As I think this has something to do with the select statement I issue the following statement at the end of this procedure: dm.fdq.SQL.Clear; .. but when I look at the SQL string with ShowMessage( dm.SQL.text ); it still shows the Select Distinct line shown above. The Clear statement doesn't seem to clear anything. I've saved the SQL (assigning dm.fdq.SQL.text to a variable: cSQL, at the start of the procedure), and at the end of the procedure restore with dm.fdq.SQL.Add( cSQL ); but this does nothing either. I've a feeling I'm not understanding how the above should work correctly. Any advice would be appreciated.

Hi, I have searched for 'simple' stuff in using FireDac but havn't found the answer to the following, which would save me lots of keying ... I have a datamodule with a fdQuery & Datasource, and the fdQuery has the following: 'Select * from tTable where Company = :pC order by oOrderName' - so a parameter is created in the query. If in my code I want to set the query to different values do I have to do the select statement again in it's entirety, like: dm.fdc.Open( 'Select * from tTable where Company = :pC order by oOrderName', [ 'Debenhams'] ); Or can I simply specify the new parameter value like: dm.fdc.Open( '', [ 'Woolworths' );

Hi I've just seen the above function which Firedac provides, and read the DocWiki about it and it would be very helpful in my project as I create new records. However, I'm having difficulty understanding quite how it works, or rather what it requires passed with it. The Docwiki provides an example of how to program with it: ShowMessage(VarToStr(ADConnection1.GetLastAutoGenValue('MyGen'))); My question is - what is 'MyGen' referring to? I'm using MS SQL and the info says the method returns the last autogenerated value in the session. In my table I have an autogenerated column called 'ID' (an integer). I've changed the above example to read: ShowMessage( VarToStr( dm.fdcMSLTest.GetLastAutoGenValue( 'ID' ))); assuming what it wants passed to it is the fieldname that is autogenerated. However, this gives an error when run 'Capability is not supported'. Any thoughts would be appreciated.

Hi I'm trying to use a MySQL database on a remote MySQL Server. I've setup the Firedac TFDConnection with the correct parameters but when I try to test I get the following message: 'Cannot load vendor library [libmysql.dll, libmariadb or libmysqld.dll) The specified module could not be found Hint: check it is in the PATH or application EXE directories, and has x86 bitness' (see attached screenshot). I then went to the Embarcadero docwiki site which says much the same with a little more detail. I've followed the links it has for the libmysql.dll library and download it and put it in my EXE directory. However, when I try the test again I get the same message. I've closed Delphi (10.3.3) down and tried again but still get the same error message. Has anyone had similar issues and can suggest a way to fix this?

Hi, When I open my project I cycle through all the queries I use making sure they are all active, this works fine. Depending on what year I want to view I have a radio box with '2019' and '2020' as selections and from that I need to 'filter' the data to only show the year chosen., I load the value into cYear. So I have this line: qQueryname.Open( 'SELECT * FROM tTablename WHERE mYear = :pY ORDER BY eDate',[ cYear ] ); When this line is executed I get the error message above on this query, which is confusing me as it is an 'Open' command. The weirder thing is that the SQL is executed OK and the resulting data is the correct year's data. Using: Filtered:= False; Filter:= 'MatchYear = ' + cYear; Filtered:= True; code instead works perfectly with no issues.

Hi I'm having great difficulty in getting the syntax correct on a query I need to make via a Firedac Q!uery. Basically I want to set a WHERE clause that picks up all records that have a 'S' or 'R' in a certain field. In 'normal' SQL this codes like this: SELECT * FROM tTableName WHERE Status IN ('S', 'R') ORDER BY EventDate ... which works fine on the SQL Server, but how do I do this via a Firedac query? It may look something like this: qQueryName.Open('SELECT * FROM tTableName WHERE Status IN :pSR ORDER BY EventDate',[ ('S','R') ] ); The issue is how do I format the parameter - the above doesn't work, nor any of the different ways I can think of of setting the parameter to 'S','R'. Any help would be appreciated.

A client has several of the applications I've written over the past several years and all work OK, They all connect (with FireDac) to a Microsoft SQL Server. However, I've just loaded a newer application (compiled in Delphi 10.3.3) and when I run it on this PC I get the following FireDac error message immediately (see attached): Exception EMSSQLNativeException in module ... at ..... [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt The PC is not running any other application that could be accessing the MS SQL Server, so I'm at a loss as to why it is busy at all. This application works fine on my development machine. Would this be a driver issue, maybe a newer driver is needed (is there one)? Or does Delphi 10.3.3/FireDAC do something differently to what it did in earlier versions? Any thoughts would be much appreciated.

Does anybody know where I can find a (simple) example of setting up a Master-Detail on two tables (MSSQL in my case)? All I can find via the internet are either overly complicated examples or in non-English languages. The Help in Delphi/Firedac is the former and very difficult to follow.

When I create FDQuery in threads, should I set CmdExecMode to amNonBlocking or leave amBlocking? http://docwiki.embarcadero.com/Libraries/Tokyo/en/FireDAC.Stan.Option.TFDResourceOptions.CmdExecMode

I just noticed in Rio 10.3.2 that the TFDSQLScript SQL editor has the Code Editor button in the String List Editor disabled. I popped up the help and there does not seem to be a reason for this. The SQL property is of type TSrings in the Inpsector and the actual implementation uses TFDStringList just the same as the connection Params ancestor class in which you can use the Code Editor. Is this a case of a missing property editor registration? It would be very nice to be able to use the Code Editor to modify scripts instead of cop[y/pasting from an external editor.

Hi, I'm trying to establish a connection to Firebird, using the connection pool. In my project I have several datamodules, which I create and destroy continuously. The first datamodule that is created reads the connection configuration, connects (this does it well) and then saves the parameters of this connection in the connection definition (I use the folowing code): Connect is a TFDConnection component var oDef: IFDStanConnectionDef: = FDManager.ConnectionDefs.AddConnectionDef; oDef.Name:= defConn; oDef.Params.Pooled: = true; oDef.Params.DriverID: = 'FB'; oDef.Params.Database: = Connect.Params.Values ['Database']; oDef.Params.Password: = Connect.Params.Values ['Password']; oDef.Params.UserName: = Connect.Params.Values ['UserName']; oDef.Params.Values ['ExtendedMetadata']: = 'true'; oDef.Params.Values ['CharacterSet']: = 'UTF8'; Then, when I open other datamodules, the first thing I do is assign the definition of the connection, and then I connect, but this gives an error it seems that it does not assign the user and password correctly: Connect is the TFDConnection component of the new created datamodule Connect.ConnectionDefName: = defConn; Connect.Connected: = true; (this gives connection error) Any suggestions of things to review and / or change?, thanks Note: The datamodule where I set the connection parameters and establish the connection definition, is also destroyed after read and apply this settings.

Hi, I'm looking at the TMARSFDDataModuleResource and I see that it has protected members `FD` (TMarsFireDAC) and `URL` (TMARSURL). In my descendant class both of these are `nil` references. Is there a way to use this? Reason is I'm trying to switch DB connections based on the server's own host URL. E.g. if it's accessed via localhost or 192.168.x.x then use the LOCAL_DB connection def, otherwise use PROD_DB Edit: Which also begs the question, where is the best place to call `FD.ConnectionDefName := MyConnectionDefName;` ? Thanks Stuart

I've created an MCVE that simulates a problem that eventually happens in our software, where software freezes when it tries to register an event when a previously registered event triggers within the same cycle. In an internal test, we found that this also occurs using the TIB_Events component. Would this be a Firebird or FireDAC bug? unit Unit1; interface uses Winapi.Windows, Winapi.Messages, System.Classes, System.SysUtils, Vcl.Controls, Vcl.Forms, Vcl.StdCtrls, FireDAC.Comp.Client, FireDAC.Comp.UI, FireDAC.Phys, FireDAC.Phys.FB, FireDAC.Phys.FBDef, FireDAC.Phys.IBBase, FireDAC.Stan.Async, FireDAC.Stan.Def, FireDAC.Stan.Intf, FireDAC.UI.Intf, FireDAC.VCLUI.Wait; type TForm1 = class(TForm) Button1: TButton; procedure FormCreate(Sender: TObject); procedure FormDestroy(Sender: TObject); procedure Button1Click(Sender: TObject); private FConn : TFDConnection; FEvents: TFDEventAlerter; procedure EventAlert(ASender: TFDCustomEventAlerter; const AEventName: String; const AArgument: Variant); public { Public declarations } end; var Form1: TForm1; implementation {$R *.dfm} procedure TForm1.Button1Click(Sender: TObject); begin FConn.ExecSQL('EXECUTE BLOCK AS BEGIN POST_EVENT ''EVENT_1''; END'); FEvents.Names.Add('EVENT_2'); end; procedure TForm1.EventAlert(ASender: TFDCustomEventAlerter; const AEventName: String; const AArgument: Variant); begin Caption := AEventName + ' ' + DateTimeToStr(Now); end; procedure TForm1.FormCreate(Sender: TObject); begin FConn := TFDConnection.Create(nil); FConn.LoginPrompt := False; FConn.DriverName := 'FB'; FConn.Params.Values['DriverID'] := 'FB'; FConn.Params.Values['CharacterSet'] := 'ISO8859_1'; FConn.Params.Values['Server'] := '127.0.0.1'; FConn.Params.Values['Database'] := 'c:\temp\whatever.fdb'; FConn.Params.Values['User_Name'] := 'SYSDBA'; FConn.Params.Values['Password'] := 'masterkey'; FEvents := TFDEventAlerter.Create(nil); FEvents.Connection := FConn; FEvents.Names.Add('EVENT_1'); FEvents.OnAlert := EventAlert; FEvents.Register; end; procedure TForm1.FormDestroy(Sender: TObject); begin FEvents.Free; FConn.Free; end; end.

I am trying to work with integer arrays in a Postgres integer[] field. I have read somewhere that with Postgres you need to use NestedDataSets to read the data rather than TArrayField and that seems to be correct. I can successfully write back to the dataset using TArrayField in an Insert Query. I can read from the integer[] field using TDataSet and nested fields. However, I cannot work out how to write to the TDataSetField. I have tried the following: Edit; TT:=TDataSetField(Fields[2]); for i:=0 to high(arr) do begin TT.NestedDataSet.Append; TT.NestedDataSet.Fields.Fields[0].Value:=arr[i]; end; The error I get on trying to post data edited as above is I tried to use "AsInteger" instead of value, but got the same error.

I am using a TFDQuery component to load and edit records from a table using CachedUpdates. Records can be deleted from the table as well as edited and appended. The BatchMove component in dmDelete mode deletes all records in the query and not just those that have been flagged for deletion. Is there any way of using the BatchMove component so that it only deletes records where the updateStatus is usDeleted?

I am having difficulties with a parameterized query designed to be used repeatedly. It works first time but never a second. The query is fairly complicated so I prepare it on app initialization for repeated use, but it works just once and returns an empty dataset on every other occasion (even with the same parameter). If I reset the query text (FDQuery.SQL.Text := FDQuery.SQL.Text) it works as expected. That rather defeats the purpose of preparing the query in advance. Is it the case that parameters cannot be used in repeated queries?

I have been using ClearDetails to remove all records from TFDQuery components. However, I have experienced a problem today where a call to ClearDetails for a reusable query that is designed to return a single record is not removing that record. The description for this function states: Pretty much what I was expecting it to do, but (in this one case at least) it is not. I have switched to EmptyDataSet which does the job, but should I be updating all places where I use ClearDetails to update to EmptyDataSet?

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;