Jump to content

Search the Community

Showing results for tags 'firedac'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Delphi Questions and Answers
    • Algorithms, Data Structures and Class Design
    • VCL
    • FMX
    • RTL and Delphi Object Pascal
    • Databases
    • Network, Cloud and Web
    • Windows API
    • Cross-platform
    • Delphi IDE and APIs
    • General Help
  • Delphi General Discussions
    • Embarcadero Lounge
    • Tips / Blogs / Tutorials / Videos
    • Delphi Third-Party
    • Job Opportunities / Coder for Hire
    • I made this
  • Software Development
    • Project Planning and -Management
    • Software Testing and Quality Assurance
  • Community
    • Community Management

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start




Found 29 results

  1. 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?
  2. Lajos Juhász

    FireDAC - TFDEventAlerter

    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.
  3. 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?
  4. 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.
  5. bazzer747

    FireDac Query Issue

    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.
  6. 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' );
  7. bazzer747

    FireDac MySQL Connectivity

    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?
  8. 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.
  9. bazzer747


    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.
  10. 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.
  11. bazzer747

    MSSQL Busy

    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.
  12. bazzer747

    Master-Detail Example

    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.
  13. Jacek Laskowski

    FDQuery, threads and CmdExecMode

    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
  14. 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.
  15. Alberto Fornés

    [Firedac] Connection Pooling

    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.
  16. Hi, We are trying to add the functionality to allow the user to change the SQL server password if it is expired. The following code snippet using TFDConnection shows what we are trying to do: FDConnection1.Params.Values['Server'] := 'SQLExpress'; FDConnection1.Params.Values['Database'] := 'TestDatabase'; FDConnection1.Params.Values['User_Name'] := 'testuser'; FDConnection1.Params.Values['Password'] := 'oldpass'; FDConnection1.Params.NewPassword := 'newpass'; FDConnection1.Open; But when we get an error while trying to change it: [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver] Login failed for user 'testuser'. (Error Code is 18456) Tools we use: RAD Studio 10.2 FireDAC = 16.0.0 (Build 88974) Platform = Windows 32 bit SQL Server 2016 express edition ODBC Driver 17 for SQL server. What we observed is that if we use then it works with a patch in FireDAC units. (While debugging we found that it uses a method TFDPhysMSSQLConnection.CheckPasswordChange which uses a connection to check the SQL versions but by that time the connection was not able to make because of the password expiration. So we commented the code inside "CheckPasswordChange" and then it started to work.) But we are told to use ODBC Driver because Microsoft is not going to support Native Client in the future. Is it possible to change an expired password using FireDAC in combination with ODBC driver.? If that is possible then any advice to fix the problem mentioned above? Kind Regards, Soji Mathew.
  17. Rafael Dipold

    TFDEventAlerter bug?

    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'] := ''; 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.
  18. Stuart Clennett

    FireDAC Connections

    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
  19. Mark Williams

    TFDBatchMove delete records

    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?
  20. 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.
  21. Mark Williams

    FireDAC paramamania

    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?
  22. Mark Williams

    FireDAC clearDetails or EmptyDataSet

    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?
  23. Mark Williams

    FireDac PostgreSQL and TBatchMove

    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;
  24. Flavio Basile

    Firedac Json Reflection from XE7 to Rio

    Hi All; we have a big commercial project; It is coded with C++ Builder XE7. We have many Web Broker that after a call returns a dataset by Firedac Json Reflection. We would like to migrate our code from XE7 to Rio but we would like to know if there are difference between Json Reflection format of XE7 and Rio. There are also some important difference in Firedac between XE7 and Rio that we need to evaluate? Could we migrate our code without any important problems? Thanks, Flavio
  25. Martyn Spencer

    Firedac and SQL Server DB with "." in name

    I am using Delphi 10.2 and I have been given a database to connect to and I have no control over its name. It is in the format xxxx.yyyy and when I create the connection definition, all is good and I can connect. I am specifying the database as xxxx.yyyy and setting the other various parameters appropriately (the same as I do for other databases). I can use the SQL tool in the connection editor to query the tables just fine. I create a simple TFDQuery consisting of a basic "select * from table" and when I attempt to run it, I am told that database xxxx is not in the list of valid servers and it should be added to sys.servers using sp_addlinkedserver. Clearly, what Firedac is passing to SQL server leads SQL server to interpret the xxxx as a server name. I have tried "select * from tablename", "select * from dbo.tablename", "select * from [xxxx.yyyy].tablename", "select * from [xxxx.yyyy].dbo.tablename" and other variations and still it refuses to work. I am setting no specific values for the components - I create the definition in the data explorer, test that it connects, test that I can use the "SQL script" tab to run various SQL statements. I then drag one of the tables onto the form designer (a quick test) and try to run the query and it fails with the message: "Could not find server 'xxxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinked server to add the server to sys.servers". I have tried removing the database name from the connection definition, in the hope that the default database has been set - this does not work. Any suggestions, please? I am unable to request that the database name be changed.