How to return auto increment value from INSERT Database Script in #Script?

ServiceStack
1

Given the following script, how would I return the newly assigned auto increment value (Id) from the INSERT statement?

{{ requestBodyAsJson |> to => jsonBody }}
{{#if isHttpPost }}
{{
 `INSERT INTO test_model (name) VALUES (@name)`
           |> dbExec(jsonBody)
           |> return
}}
{{else}}
{{
     `SELECT * FROM test_model WHERE name = @name`
           |> dbSelect(jsonBody)
           |> return
}}
{{/if}}
2

The DB Scripts are just executing raw SQL, how would you normally do it in the RDBMS you’re using?

3

I thought it was using OrmLite so I was hoping for some of the same functionality in a regular app.

4

You’re not using OrmLite’s Typed APIs here, you’re executing raw SQL which OrmLite executes as-is.

Please do some self-research looking into how it’s done with SQL in the RDBMS you’re using as you’re going to need to do this a lot during normal app development when using APIs that execute raw SQL like this. If you’re not comfortable learning SQL, the only other way around it is using OrmLite’s Typed APIs in your custom script methods, as there’s no existing Types you’ll need to create OrmLite data models in C# which you can populate from a #Script Object Dictionary using the FromObjectDictionary Reflection Utils.

5

I was able to accomplish this in PostgreSQL with

{{
 `INSERT INTO test_model (name) VALUES (@name) RETURNING id`
           |> dbSelect(jsonBody)
           |> return
}}