I've been able to get sql:prepared-query($connection, $sql)() working for simple SQL.
SELECT column1 FROM table
This works great, and I can access the value with $sql-result-map[1]?column1
It also works with some joins. But when I join two tables with the same column names, and the query specified which columns to return, the map has a key for the column name, but no value.
SELECT table1.column1 FROM table table1 JOIN table table2
The maps in the returned sequence show the key column1 in map:keys($sql-result-map[1]), but $sql-result-map[1]?column1 is always blank.
I've also noticed that the map keys don't follow any AS alias, so SELECT table1.column1 AS column-alias FROM table table1 JOIN table table2 does not change the key used.
Any suggestions on how to work around these limitations?
I suspect that what is happening is that in the ResultSetMetaData returned by the query, two columns return the same result for getColumnName(), and of course in the map that we return, each key needs to be unique. I need (a) to confirm this is the case, and (b) to decide what to do about it. We should probably be using the result from getColumnLabel() as the key, rather than the result from getColumnName().
I have raised a Saxon issue at https://saxonica.plan.io/issues/6215 -- please watch that issue to track progress.
LATER
So far (running with PostgresSQL) I can reproduce the fact that if two columns have the same name, one of them won't appear in the output. However, in my experiments, if you alias one of the result columns with an AS clause, it works fine.
I'm getting exactly the same result whether I use getColumnName() from the JDBC result set, or getColumnLabel(). It's possible of course that a different database will give a different result.
I'm thinking that we might detect that two columns have the same name and allocate an arbitrary name to the duplicate. It's won't be very useful for querying the result, but it may make it clearer what's going on.