Search the Community

A TFDQuery with PostgreSQL fails to execute when I have parameters in the query. The error message is: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" ParamByName executes but PostgreSQL sees the ":" and the exception occurs in ExecSQL. This seems to indicate there is an issue with FireDAC setting the parameter. Although I could manually work around the issue by substituting the parameters in the SQL at runtime, I would prefer this to work as it does in other databases. Code try v_Step := 'SetParameter'; FDQuery3.ParamByName('increment').AsInteger := 100; v_Step := 'ExecSQL'; FDQuery3.ExecSQL; except on E: Exception do begin Memo1.Lines.Add(v_Step+': '+E.Message); Memo1.Lines.Add(FDQuery3.SQL.Text); end; end; Memo1.Text ExecSQL: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" do $$ begin update mytable set testno = testno+:increment where id = 1; commit; end $$

I would like to get the Firedac Query Editor in the IDE to work with Postgresql. When I click the execute button in the Firedac Query Editor, I get this message: [FireDAC][Phys][PG]-314. Cannot load vendor library [C:\Prograqm Files\PostgreSQL\17\bin\libpq.dll. Library has unsupported architecture [x64]. Required [x86] %1 is not a valid Win32 application. Hint: check it is in the PATH or application EXE directories, and has x86 bitness. The library mentioned is the directory containing the installation for PostgreSQL. I want to override that and have it use the 32-bit library which is in c:\postgresql\odbc32\bin. My system path begins with c:\postgresql\odbc32\bin; and does not mention the path the IDE is using. The IDE override path is set to: $(PUBLIC)\Documents\Embarcadero\InterBase\redist\InterBase2020\IDE_spoof;$(PATH) How do I get the IDE to use the 32-bit library? Thanks

I have a large set of data to insert into a MySQL/MariaDB table. For now I decided to use FireDAC’s array DML feature, so I create a TFDStoredProc, feed it with all the data and call Execute method (all the stuff in a TTask to avoid hanging the UI). Now when the user wants to close the application, the task should obviously be interrupted to prevent the application from hanging in the processes list for quite a long time. So, from the UI thread (say, TForm.OnCloseQuery, doesn’t really matter) I call TFDConnection.AbortJob on the connection I’ve assigned to the TFDStoredProc before executing it. The problem is that AbortJob raises an exception: So, the array DML query doesn’t get aborted, the program stays hanging until the TDStoredProc.Execute returns. Has anyone ever had such problem? Any help is appreciated.

Hello everyone, I' trying to understand where I get the certificate files for the SSL certificate and CA certificate from. I have to specify them in the FireDAC connection component. With the component I want to connect to the Azure cosmos DB for MongoDB (vCore). Do I need to get them from somewhere like " www.digicert.com " or do I get them from Azure itsself? Connection parameter: SSLPEMKeyFile -- is the path name to the .pem file that contains the SSL certificate and key. SSLCAFile -- is the path name to the .pem file that contains the certificate from the Certificate Authority (CA). (Source: https://docwiki.embarcadero.com/RADStudio/Athens/en/Connect_to_MongoDB_Database_(FireDAC)) Thanks in advance. Robert

I tried to buy the pdf version of Delphi in Depth: FireDAC by Cary Jensen (I already have the hard copy book) . The provided link on his site : http://jensendatasystems.com/firedacbook/ works just fine , but it seems impossible to actually buy the ebook from FastSpring . Is there another way to buy the pdf version of Delphi in Depth: FireDAC ?

I’m using FireDAC with MariaDB. Since I have quite a lot of stuff done with stored procedures, I wrote a simple function like this: function TMyDataModule.OpenProc(const AName: String; AArgs: array of Variant): TFDStoredProc; begin Result := TFDStoredProc.Create(nil); try Result.Connection := MyConnectionComponent; Result.Open(AName, AArgs); except Result.Free; raise; end; end; In my database I have a table: CREATE TABLE `Data` ( `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `When` TIMESTAMP NOT NULL DEFAULT current_timestamp(), `MyID` BIGINT(20) UNSIGNED NOT NULL, `Bool1` TINYINT(1) NOT NULL, `FOURCC` CHAR(4) NOT NULL COLLATE 'utf8mb4_unicode_ci', `Comment` TINYTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `Arg1` TINYINT(3) UNSIGNED NOT NULL, `Arg2` TINYINT(3) UNSIGNED NOT NULL, `Arg3` TINYINT(3) UNSIGNED NOT NULL, `Arg4` TINYINT(3) UNSIGNED NOT NULL, PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB ; … and a stored procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `MyProc`( IN `aMyID` BIGINT, IN `aBool1` TINYINT(1), IN `aFOURCC` CHAR(4), IN `aComment` TINYTEXT, IN `aArg1` TINYINT UNSIGNED, IN `aArg2` TINYINT UNSIGNED, IN `aArg3` TINYINT UNSIGNED, IN `aArg4` TINYINT UNSIGNED ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN INSERT INTO `Data`(`MyID`, `Bool1`, `FOURCC`, `Comment`, `Arg1`, `Arg2`, `Arg3`, `Arg4`) VALUES(aMyID, aBool1, aFOURCC, aComment, aArg1, aArg2, aArg3, aArg4); END When I try to invoke MyProc, I get this error: “Out of range value for column 'aArg2' at row 0”. In Delphi debugger I see AArgs containing perfectly valid values: a number, a boolean, a 4-character string, an empty string for aComment and four integers perfectly in the range of 0..255. But at OpenProc call it fails. I used Wireshark to see what data actually goes “onto the wire”: the byte values are those they should be. The same procedure with the same argument values runs just fine with HeidiSQL. But HeidiSQL seems to use simple text queries while FireDAC sends arguments in their binary representation. What could be the reason and fix to the problem? Thanks.

Can I connect to MSSQL Server with FireDAC on android application? When I try it there is a problem Is there any eay for direct connection from my android application to MSSQL Server

Hello - I recently upgraded to Delphi 12 & Windows 11 upon receiving a new laptop at work. I have some older applications that use Access databases, all with the ".accdb" file extension (most current Access format). The applications are essentially deprecated, as we have new software to manage the data. However, in the meantime, I need to maintain these older applications and their data. With my previous laptop and Delphi 11, I had no trouble using a FireDAC Connection to connect to my Access ".accdb" databases, once I installed the Access Database Engine. However, no such luck with my new laptop. Due to changes in the location of the databases (renaming of server and moving to new domain), I need to recompile these applications. The issue appears to be only due to the version of the database file, as I can convert the .accdb file to an .mdb file and open the .mdb file successfully with FireDAC whether it's on the server or on my local drive. However, I cannot open the .accdb file either place. I get this error: (I will avoid converting all my databases to .mdb, leaving that as a last resort). I'm wondering if it might have to do with 32-bit or 64-bit versions of Office/Access. I thought I had been using 64-bit Office on my former laptop, and I know I'm using 64-bit on my new one. Any suggestions? TIA Teri

i'm converted ADO Connections and AdoQuerys To FDConnection and FDQuerys in MyProject to Improving Connection Quality to MS SQL Server 2012 Database The previous Ado version worked fine in Running Query Commands But in the current FD version, the system hangs most of the time. Cursor Shows Hourglass Icon and Sql Word Below HourGlass Cursor and The program is suspended.so that the user is forced to use the َ Alt+Ctrl+Del keys maybe FireDac Not Compatile with sql 2012?

Hello, I have a TFDconnection to Sqlite database. I already have some tables in the database which are created with TFDTable->CreateTable() method. Now I would like to create a table with primary and foreign keys: CREATE TABLE Channels ( channel_id INTEGER PRIMARY KEY, task_id INTEGER, FOREIGN KEY (task_id) REFERENCES tasks (task_id) ); Where can I add Primary key and Foreign key to TFDTable before I create it? Thanks for the replies.

Hello - I recently received a new laptop at work, and, for security purposes, the laptop and my credentials are in a different AD tree than my SQL Server. My laptop is under USDA.net, and the SQL Server is in the tree for the University where our offices are located. I've set up a Windows credential that seems to work, as I can connect my laptop to the SQL Server using SSMS, and I successfully set up a DataSource in ODBC using the 'ODBC Driver 18 for SQL Server'. I just have to make sure that the 'Trust Server Certificate' box is checked. However, when I place & test an TFDConnection to my SQL Server in Delphi, I'm receiving the message: The system cannot contact a domain controller to service the authentication request. Please try again later. To mimic the 'Trust Server Certificate' setting, I've added TrustServerCertificate=yes to the ODBCAdvanced setting in the FireDAC Connection Editor. [I get a '...certificate chain not trusted...' error when I omit that]. The connection seems to work for everything except FireDAC, and I'm at a loss as to what to do next. Any ideas?

Hello everyone, I'm seeking insights on database development practices in Delphi with Firebird. I have two specific questions: When it comes to database development in Delphi, what is the recommended approach: utilizing data-aware components like drag-and-drop fields and linking with TFDQuery for CRUD operations, or segregating CRUD operations into separate units for forms? I'm particularly interested in understanding the balance between ease of use, efficiency and code usability. Any insights or examples you can provide would be greatly appreciated. Often, we encounter situations where we need to fix a bug or implement changes in just one form out of many (over 100 forms) within our system. Currently, we update the complete executable, which includes all VCL forms in one project. What would be the best approach to handle such incremental updates efficiently without impacting all clients? I'm eager to learn about effective strategies in this scenario. Thank you for your valuable input. Warm regards, Noor

Is there a way to find out what driver (ODBC, SQL Native Client, etc. ) that FireDAC is actually using when establishing a connection to the MSSQL database? In the FireDAC connection all we get to specify is DriverID = MSSQL and FireDAC apparently chooses an appropriate driver based on what's available.

Hello everyone, I am in the process of converting an old project from Bde to Firedac. Basically it's relatively easy, but now I'm at a point where I'm a bit desperate. With the Bde, TDatabase and TTable/TQuery components, I had set up a central connection in my main program and opened all the necessary TTable tables. Now DLLs of the program (dynamically loaded with LoadLibrary) could call a function in the central location (export method) and, for example, fetch a TTable object corresponding to a specific table. Regardless of whether the design is good or bad, it worked without any problems. We used "ShareMem" in the main application and all the DLLs. I then had access to the tables of the main program in the DLL and could, for example, call FindKey to jump to a specific position in the DB. With Firedac I left everything as it was, only instead of TDatabase I used TFDConnection and accordingly TFDTable etc. The code is definitely correct, I can connect and display the data in the Dbgrid. FindKey from the main program also works. Nevertheless, I always get false with FindKey from the DLL after I have fetched the TFDTable. Does anyone have any experience with this? Could it be that the connection does not arrive correctly in the DLL when I get the TFDTable object? On the other hand, something like fdtable.fieldbyname(...) works without problems from the DLL. So I don't understand why FindKey doesn't work. It is also the same TFDTable object that I get back from my main application, indexfieldnames is also correct, table and connection are open and everything. Nevertheless it does not work. Would be grateful for a hint, especially since it works with TTable. I hope I was able to explain the problem. FindKey was just an example, I´m not sure, if other methods called in the dll work properly either. The Idea behind this concept is that we have a central place and all DLLs have access to the main tables and can change the cursor position of the table or can be updated when another DLL changes the position of a table, because they all use the same tables from the main application. Is there a better concept for this in FireDac with DLLs? Or do I need to create methods in my main application for Table updates or methods like "FindKey" which can be called from the DLLs? That would be a huge refactoring and I want to avoid this! Thanks a lot!

I would like to add an Offline mode to an existing application, and to do so I need to be able to activate empty datasets on FDQuerys while in that Offline mode. But when I try calling CreateDataset to activate those empty datasets it always tries to open the connection, which fails because the server is unavailable at the moment. If I replace the Connection with a Dummy Connection, the CreateDatasets still fails because although it successfully opens the dummy connection it raises an exception that the original table has not been found, so my guess is that CreateDataset tries to retrieve metadata instead of using the persistent fields to create the empty dataset. I try to remove the fetching of Metadata from the FDQuery.FetchOptions, but I can't make it work (I always get either a Server not found exception or a Table not found when I use a dummy connection). Example : DataModule.Cn.Offline; DataModule.Cn.FetchOptions.Items := []; DataModule.Cn.FetchOptions.Cache := []; DataModule.Cn.FetchOptions.AutoFetchAll := afDisable; DataModule.QCustomer.Offline; DataModule.QCustomer.CachedUpdates := True; DataModule.QCustomer.FetchOptions.Items := []; DataModule.QCustomer.FetchOptions.Cache := []; DataModule.QCustomer.FetchOptions.AutoFetchAll := afDisable; DataModule.QCustomer.CreateDataSet; I know that usually you would use FDMemtables for offline datasets, but TFDMemTable and TFDQuery both derive from the same TFDDataset which already implements the in-memory mode, so duplicating all my components to TFDMemTable doesn't seem to provide any additional benefit. Do you know if it's possible to disable the metadata retrieving in order to activate empty datasets on FDQuerys while their connection is not available ?. Thank you.

I'm using an MS SQL database to store large files in a table. The files are too large for binding to a field directly. It has to go through a command just like the update to get it into the database. No exception is raised but the function returns false. I am finding it difficult to find an answer with Google search. Any help would be apprciated. RIMSDBFireDac.StartTransaction; try with DCaseAttach do begin FireDacQuery.SQL.Text := 'SELECT :p = ''D:\TEMP\TEMP.MP4'' FROM caseattach WHERE id = :id'; FireDacQuery.Params[0].DataType := ftStream; FireDacQuery.Params[0].FDDataType := dtHBFile; FireDacQuery.Params[0].ParamType := ptOutput; FireDacQuery.Params[0].StreamMode := smOpenRead; FireDacQuery.Params[1].AsInteger := CaseN; if FireDacQuery.OpenOrExecute then FireDacQuery.Params[0].AsStream.Read(Buffer, AttachSize) else ErrorMsg('OpenOrExecute failed.'); end; RIMSDBFireDac.Commit; except on E: Exception do begin ErrorMsg('<PullImageFromDB on pulling photo> ' + E.Message); RIMSDBFireDac.Rollback; end; end;

Hello - I wrote an application that uses a TFDQuery to retrieve data from a SQL Server. A few of the fields in the query are typed as TSQLTimeStampField. Everything works beautifully when I run the application, either from within the Delphi IDE or as a standalone app. However, when my colleague, who needs to use the application, tries to run it, she gets the following error. We're using version 2019 of SQL Server, and the data field in the SQL table is typed as DateTime2(0). I tried changing CollectionTime's field type to WideString then DateTime, but each threw Debugger Exceptions. I'm at a loss as to how to remedy this. Any suggestions? Teri

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.