Has anyone used the RETURNING id PostgreSQL command in XOJO?

“Returning” command show that generated ID and obtained within the code.

For example:
"insert into “catalog” (“name”, “sku”, “price”)
values (‘foo’, ‘BAR’, 34.89)
returning “product_id”

The syntax works perfectly in the database, but it implies this has a result. Like if you did a SELECT * FROM Table.

Does anyone know how to do it in XOJO?

var rs as RowSet = _
    db.SelectSQL( _
    "INSERT INTO table (a, b, c) " + _
    "VALUES ($1, $2, $3) " + _
    "RETURNING id", _
    a, b, c _
    )
var id as integer = rs.Column( "id" ).IntegerValue
2 Likes

In other words, use it just as you would a SELECT statement.

And you can return all the columns with *, e.g., INSERT ... RETURNING *.

Thanks for the example. I too was wondering about that.

1 Like

Additional info in this blog post:

2 Likes

Thank you, Kem!

It works perfectly.

I will add an option for those interested in the same question.



Var cSQL_Consulta As String = ""

cSQL_Consulta = "INSERT INTO Cat_Votacion (Col01, Col02, Col03, Col04) VALUES ('"+Value01.Text+"',"+Value02.ToString+",'Value03','"+Value04.SQLDateTime+"') RETURNING My_ID"

  Var Recset as RowSet = Session.DBlink.SelectSQL(cSQL_Consulta)
    Insert_Id = Recset.Column("My_ID").IntegerValue
    MessageBox(Insert_Id.ToString)

Please don’t do it like that, that example opens you up to SQL injection. Instead:

Var cSQL_Consulta As String = _
    "INSERT INTO Cat_Votacion (Col01, Col02, Col03, Col04) VALUES ($1, $2, $3, $4) RETURNING My_ID"

Var Recset as RowSet = _
    Session.DBlink.SelectSQL(cSQL_Consulta, Value01.Text, Value02, "Value03", Value04.SQLDateTime)
Insert_Id = Recset.Column("My_ID").IntegerValue
MessageBox(Insert_Id.ToString)
2 Likes

I have another question in this regard.

The benefits of API2 do not include an additional security layer?

So you no longer need to go through the automatically prepared statements security process.

API2 uses prepared statements under the hood. It does the work for you.

1 Like

Thanks Tim !