#define SQLITE_VERSION "3.37.0"
#define SQLITE_SOURCE_ID "2021-11-27 14:13:22 bd41822c7424d393a30e92ff6cb254d25c26769889c1499a18a0b9339f5d6c8a"
Using a RETURNING clause within prepared statement in case of ON CONFLICT is not behaving like un-prepared statement.
(I am unable to get back anything with 'prepared mode')

Exemple:
Intent of code is to catch the PK my_pk as created or used by Insert or Update as happend resolving UNIQUE conflict
my_uxi triggering the ON CONFLICT UPDATE and RETURNING clauses
It does properly work with unprepared statement, unachievable using prepared statement.

CREATE TABLE MyTable(
my_pk INTEGER PRIMARY KEY AUTOINCREMENT,
my_uxi NUMERIC NULL DEFAULT 0,
my_value CHAR(256),UNIQUE(my_uxi));

A>> Un-prepared statement:
On a Table where a record already exist with my_uxi already containing '111'
Then the following SQL does work properly trough unprepared mode:

BEGIN TRANSACTION;
INSERT INTO MyTable (my_pk,my_value)
VALUES (111,"A value")
ON CONFLICT(my_key) DO UPDATE SET my_value="A NewValue" RETURNING my_pk
END TRANSACTION;
Works. I received back the proper value for my_pk trough the same call back used for SELECT. All is perfect!

B>> Using prepared statement
BEGIN TRANSACTIONl
INSERT INTO MyTable (my_pk,my_value)
VALUES (?,?)
ON CONFLICT(my_key) DO UPDATE SET my_value=? RETURNING my_pk;
END TRANSACTION;
Doing proper binding with same data, then using rc = sqlite3_step(stmt);
Works. (The ON CONFLICT clause properly trigger an Update instead of a INSERT)
So it worked nearly as expected.
BUT
The return code (rc) of sqlite3_step() is "SQLITE_DONE"
and Not "SQLITE_ROW".
Any call to sqlite3_column_int64(stmt, 0) doesn't deliver back anything
(in appear that in "static Mem *columnMem(sqlite3_stmt *pStmt, int i)" we have pVm->pResultSet==NULL, hence no column are available at all)

I don't expect the transactional mode to influence that, I foresee more an ABI issue.
Any suggestion as way to catch column9s) as the RETURNING clause should allow itt but using 'prepared statement' (and sqlite3_step()) is welcomed

ON CONFLICT(my_key)

no such column: my_key

Please post real, runnable example code that doesn't do what you expect.

Thanks
I will try. hereabove was just a mockup of my C code
Need to submit a C code as the issue only appears to me using "prepared statement" (work fine in C with "unprepared")
and I don't see way to use SQLITE> in a "prepared statement mode"

This does not work at all because your examples contain errors:

sqlite> CREATE TABLE MyTable(
   ...> my_pk INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> my_uxi NUMERIC NULL DEFAULT 0,
   ...> my_value CHAR(256),UNIQUE(my_uxi));
sqlite> INSERT INTO MyTable (my_pk,my_value)
   ...> VALUES (111,'A value')
   ...> ON CONFLICT(my_key) DO UPDATE SET my_value='A NewValue' RETURNING my_pk;
Error: in prepare, no such column: my_key (1)
sqlite>

Perhaps this is the root cause of your problem?

I also fixed your quotation marks. Double-quote is for symbols. Single-quotes are for text/character strings ...

Thanks for your help!
Sorry for the mistake in the SQL sample. The original code is in C, hence way more verbose (too verbose for here)
It doesn't seems to me that there is any way to emulate my issue with SQLTITE3.EXE
Since all work well with it. As well as in my C code as long I only use unprepared statement.
In my C code using prepared statement it execute nearly perfect, I have just no return value coming out of the RETURNING clause
I will dig a little more and submit a C subset.
Thanks again.

All SQL Statments are prepared statements. There is no such thing as executing an unprepared statement as all statements must be compiled into executable code before they are executed. This compilation process (the turning of SQL source into executable code) is called "preparing" the statement.

The first person to do something gets to name it. The procoess of turning C source code into an executable is called "compiling". If someone else had invented the process, then it might be called "frobnicating" instead.

Similarly, IBM decided back in the 1960's that the process of turning SQL "source code" into executable code was to be called "preparing" of the statement for execution.

THere is no reason that it could not be called "compiling" or "frobnicating" -- the result is the same. The hooman readable source code is turned into executable code.

There is no way to execute SQL (in SQLite3 or most other SQL-speaking storage systems) without first "preparing" (or compiling or frobnicating) the SQL into machine executable code.