Search the Community

Turning a legacy database into a modern, high-performance platform might seem intimidating. Old routines, custom PL/SQL, proprietary extensions. There is a lot that can break. But with careful planning, the right tools, and a methodical approach, you can make the move safely. Migration can take 3 phases: Assessment phase. The first step is identifying the schema's complexity, custom code, and potential pitfalls. Pilot phase. The second step is about testing the migration path with low-risk components first. Full migration phase. The third step is to minimize downtime and ensure that your data is consistent. You should prepare a rollback plan, backup your data, and carefully cut over with extensive testing afterwards. Some of the most common pitfalls you may face include: Fully trusting automation to do everything. ORM differences. Transaction isolation and locking mechanisms. It is not the same in Oracle and PostgreSQL. Extension dependencies. Missing libraries can break your migration if you are not careful. Our team at Softacom recently successfully performed a complex migration for a financial institution. It required a phased approach. We migrated over 800 tables and 300 routines from Oracle to PostgreSQL, retaining data integrity and functionality, all while avoiding significant outages and preserving business operations. If you want more detailed information about what we did, you can find it here: https://www.softacom.com/wiki/how-to-migrate-a-legacy-system-to-postgresql-real-world-example/

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

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!

I am back from Borland C++ Builder 5, skipped 10 years, and now on Embarcadero C++ Builder 11 PRO. I my have annoying questions with all good intentions of better tools. To the point. Attempting to access PostgreSQL 13 installed on local Windows 10 development box, using FireDAC components with 64 bit VCL Windows desktop project. I am getting error message stating the I need 32 bit version of libpq.dll. I got semi smart and supplied path to 32 bit libpq.dll from older postgres version 10, and all started to work at design time. Unfortunately compiled version trow error that it need 64 bit version of libpq.dll. Is there smart workaround this circle of errors embarcadero put mi in? Hope it is just my 10 years of not using excellent productivity tool. Yes I attempted to access Postgresql 14.1 as well, same issue. That I got running 64 bit application. Is there separate path option for libpq.dll 32bit for design time, and 64 bit for rune time?

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?