Search code examples
sqlswaggervisual-foxpro

How to retrieve the id of the most recent insert - VisualFoxPro9

After inserting a record into VisualFoxPro9, I would like to include the ID in the swagger response.

The ID is generated with the default value: newId("database name").

I have tried using LASTVAL(), SCOPE_IDENTITY(), LAST_INSERT_ID(), and IDENT_CURRENT(), none of which seemed to retrieve the last inserted ID.

To Summarize, How can I return the ID generated by "newId()" after inserting a new data entry.

Solution

  • Newid("database name") is a custom function that you know of. There are at least 2 ways to get that value:

    After an insert, your record pointer is on that record, you could simply check the value of id. ie:

    insert into yourTable (someColumn) values ('someValue')
newId = yourTable.Id

    Since that newid("database name") is a custom function of yours, it doesn't need to be inserted via default. You could explicitly get its value and insert. ie:

    newId = newId("database name")
insert into yourtable ;
(id, somecolumn) values ;
(m.newId, 'someValue')

    Note: I would suggest using a backend database like postgreSQL, MS SQL server ... instead of VFP's own tables.