The parts of PowerBuilder modernization that don't show up in a line-count.
There's a comforting fiction in application migration: that a database-heavy app is mostly its queries, and if you translate the queries faithfully, you've done the hard part. Embedded SQL looks the most portable thing in the codebase. A SELECT is a SELECT. An UPDATE is an UPDATE. Hand them to any competent engineer, or any competent tool, and they come out the other side as valid ADO.NET or ORM calls.
And that's where the trouble starts. In PowerBuilder, the SQL statements are only half of the story. The other half is the behavioral contract wrapped around them by the transaction object, SQLCA and that contract is invisible in the individual statements. You can translate every query perfectly and still change what the application guarantees about your data.
SQLCA (the SQL Communications Area) is PowerBuilder's default global transaction object. Most developers think of it as the database connection, because that's how it's introduced: you populate its connection properties, CONNECT, and start issuing SQL. But it carries a good deal more than connection details. It defines how every embedded SQL statement behaves, how you find out whether a statement succeeded, and when work becomes permanent.
Three parts of that contract are easy to miss and expensive to get wrong.
AutoCommit does the opposite of what its name suggestsThis is the one that catches people, so it's worth talking about. SQLCA.AutoCommit defaults to FALSE, and FALSE means manual commit mode. In that mode PowerBuilder opens a transaction and holds it. Your INSERTs, UPDATEs, and DELETEs accumulate inside that transaction and become permanent only when your code issues an explicit COMMIT. A ROLLBACK or a disconnect without a commit, depending on configuration throws the whole batch away. Setting AutoCommit = TRUE flips this: each statement commits the instant it runs.
Now look at the modern default. In ADO.NET, a SqlCommand executed without an explicit SqlTransaction (or an ambient TransactionScope) commits immediately. Each statement is its own atomic unit. Most ORMs behave the same way outside of an explicit unit of work.
Put those two defaults side by side and you see the problem. PowerBuilder's default behavior corresponds to ADO.NET's non-default behavior. A PowerBuilder function that runs four UPDATEs and then a single COMMIT is expressing one atomic operation: all four land, or none do. Translate those four statements faithfully into four ADO.NET commands with no transaction wrapper, and you've produced four independent, individually-committed writes. Every query is correct. The atomicity is gone. If the third write fails or the process dies between the second and the third, PowerBuilder would have rolled everything back to a clean state. The naive .NET version has already made two of the four writes permanent, and you're left with a half-applied operation that no SELECT in your test suite will flag.
That's a data-integrity bug, and it's invisible at the statement level.
SQLCode = 100 is not an error, and it's not nothingAfter every embedded SQL statement, PowerBuilder sets status fields on the transaction object: SQLCode (the high-level result), SQLDBCode (the DBMS-specific code), and SQLErrText (the DBMS message). SQLCode = 0 means success and SQLCode = -1 means error. Those map cleanly enough onto exceptions and return values.
The interesting one is SQLCode = 100: no rows. A SELECT ... INTO that matched nothing. A FETCH that ran off the end of a cursor. It's neither success-with-data nor an error, and PowerBuilder code branches on it constantly. The classic upsert is the canonical case:
SELECT customer_id INTO :ll_id FROM customer WHERE email = :ls_email USING SQLCA; IF SQLCA.SQLCode = 100 THEN INSERT INTO customer (email, ...) VALUES (:ls_email, ...) USING SQLCA; ELSE UPDATE customer SET ... WHERE customer_id = :ll_id USING SQLCA; END IF
The entire business rule lives in the distinction between "0 rows" and "1 row." In the .NET world there is no single status code you check after every statement; "no rows" surfaces in a different shape for each access pattern. ExecuteScalar() returns null. A reader's Read() returns false. SingleOrDefault() gives you null, while First() throws. A migration that flattens SQLCode = 100 into an exception path or, worse, folds it in with genuine errors collapses that upsert. Now you either double-insert on the "found" path or blow up on the "not found" path. The logic reads as translated; it does not behave as translated.
PowerBuilder cursor loops are built on the same status-code idiom:
DECLARE cur CURSOR FOR SELECT order_id, amount FROM orders USING SQLCA; OPEN cur; FETCH cur INTO :ll_order, :ld_amount; DO WHILE SQLCA.SQLCode = 0 // process the row FETCH cur INTO :ll_order, :ld_amount; LOOP CLOSE cur;
The loop ends when SQLCode flips to 100. A DataReader loop ends when Read() returns false. Structurally similar, but the modern form quietly merges two things the PowerBuilder form kept apart: at each iteration boundary, the reader distinguishes "another row" from "no more rows," but it does not hand you a separate error status to inspect at that same checkpoint. Any per-fetch error handling that lived inside the original loop has to be relocated, not transliterated. And an open PowerBuilder cursor inside a manual-commit transaction is holding locks for the life of that transaction so how you translate the loop also quietly changes your locking and isolation profile, which brings us to the last piece.
Here's the question that separates a translation from a modernization: for any given save operation in this app, where does the work actually become permanent?
In PowerBuilder it's rarely obvious, because SQLCA is a global and its connection is implicit. Every embedded SQL statement uses SQLCA unless you explicitly say USING <some_other_transaction_object>. That means the entire application shares one ambient connection and one ambient transaction context. A COMMIT in a button's clicked event can finalize work that was started three functions away in an entirely different object. The unit of work is defined by the runtime path through the app, not by anything local to the code you're reading. There are implicit commit points too, a DISCONNECT may commit pending work depending on how the environment is configured, so the transaction can close in places that contain no COMMIT statement at all.
None of that survives a statement-by-statement translation, because none of it is in the statements. Modern data access wants the opposite model: connections that are short-lived, pooled, and scoped to a unit of work; transactions that are explicit objects with a defined Begin/Commit/Rollback lifetime. Getting from one to the other is a reconstruction job. You have to trace the real boundaries of each logical operation through the app's flow, decide where each unit of work begins and ends, and rebuild those boundaries explicitly in code that used to leave them implicit.
Faithful query translation is table stakes, and it's the part everyone can see. The part that determines whether your migrated app is correct, whether it still makes the same promises about your data under failure, concurrency, and partial completion lives in the transaction contract that SQLCA enforced for free: the true meaning of AutoCommit, the SQLCode = 100 branches, the cursor semantics, and the implicit boundaries of each unit of work.
So when you're evaluating a modernization effort, your own, a tool's, or a partner's don't stop at "do the queries come out right?" Ask the harder questions. Where does each operation commit now, versus where it committed before? What happens to the four-write function when the third write fails? How is "no rows" being detected and branched on? Those answers, not the SELECT statements, are where your data-integrity guarantees actually live.