"Would love to see an example..."

Delphi has always had a default non-ORM dataset layer (there are ORMs for it), and it works like this for queries:

1) There's a base TDataSet class (Object Pascal uses "T" to denote "Type") that handles all abstract column and row storage and manipulations, with no pre-disposed notion of how to actually load such rows. Dataset columns are abstracted away also, and provide a lot of automatic type conversions and functionality like BLOB column stream access. Having this base class allows disparate descendant classes to easily interact with each other without knowing the details of how the data got there. All query objects descend from this base class, and fill in all of the functionality for actually interacting with the database server/engine.

2) For query objects, the SQL string gets assigned to a query object property (typically called "SQL"). Parameters are specified as named parameters by using colon notation (:Parameter) instead of "?". During this string assignment, a property setter method parses the SQL string and automatically sets up the parameters as a collection that is another property of the query object. These parameters do not have any type information assigned to them at this point. The developer can choose to just manually assign values to the parameters using type-specific parameter properties at this point. Any type differences are managed by the underlying object, so assigning an integer to a string parameter will result in an automatic conversion.

3) If you manually prepare the query object using a Prepare method, then the SQL is sent over to the database server to be prepared and any type information about the parameters is sent back from the database server to be assigned to the parameters collection (depends upon the database server). If you don't manually prepare the query, then it is automatically prepared during the query execution (4).

4) The SQL is executed using an Execute or Open method. If the SQL is a SELECT statement, the query object is automatically populated with the rows and the rows are managed using the base TDataSet functionality. There are methods for navigation, updating, etc. and updates of non-directly-updateable result set cursors are managed by the query object. Query objects are linked to database objects, and the database objects manage the transactional scope for updates. You can also use such database objects to execute one-off SQL statements where the only thing you're interested in is the result set row count or the affected row count.

The interesting part is how Delphi handles static-typing of result set columns in your application. In the IDE, you can right-click on any query object and have it automatically populate "persistent" column objects whose class type reflects the underlying result set column type. This means that if you try to access or assign the column object's raw Value property, this access/assignment is subject to type enforcement by the compiler. However, you are free to use other properties that perform type conversion, in which case you'll get a runtime exception if the type conversion fails for any reason. You can also assign names to these column objects, allowing you to write easy-to-read code like this:

OrderCustomerID.Value := 1000;

So, you can get a lot of the benefits of an ORM using an architecture like this without having to sacrifice the flexibility of manually-constructed SQL statements. A lot of this depends upon the peculiarities of the Delphi component library and IDE, though, so how transferrable this to other environments is an open question.