Sv: Return Unique Id
Daniel Sananes
daniel.s at kopparbergs.se
Tue Mar 7 16:23:56 UTC 2023
Hi Das
I now use Postgres instead of SQLite.
As I see it now there are 2 ways of inserting a record:
1. The statement-method where you compose a sql-string and executes it with $execdirect.
2. The session-method where you let the table/schema perform an $insert based on a list.
I am now exploring the second one.
There are some values that I have typed into variables like iRow.user_id and iRow.user_name.
Then I do a Do iRow.$insert() Returns #F.
This inserts the record. Just one line of code for inserting.
Can I use the RETURNING clause in this second way of performing an insert?
Regards
Daniel
-----Ursprungligt meddelande-----
Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Das Goravani
Skickat: den 8 februari 2023 18:07
Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Ämne: Re: Return Unique Id
Daniel,
If you are using Postgres or other powerful SQL database the clause you want is "RETURNING"
At the end of your select statement add RETURNING myColumn to the statement.
Then do a fetch into any undefined row or list and it will put the value as the first column in it, then you can grab it and add it into the row you are proceeding with.
You’re right, this is very important and necessary to intelligently continue with what follows your INSERT command.
If you are on SQLite there is another way of doing it that Omnis provides as a special SQLite command. I forget what it is, how it’s worded, but I can look it up if you need that. It’s something like
Do $statementObject.$lastrowid Returns theValue
With the SQLite DAM you can see $lastrowid as one of the commands offered. It gives you the PK of the last row inserted.
Das
> On Feb 8, 2023, at 11:17 AM, Daniel Sananes <daniel.s at kopparbergs.se> wrote:
>
> Hi
> Thanks for the answers regarding $select().
> I have this under control now and the Begin Text block seems promising.
> The Begin statement approach is promising as well.
>
> I have a field called user_id which is set as unique and to autoincrement.
> So when I do $insert() the record is added correctly with a unique user_id.
> But I would like to have a return of this number immediately after the $insert().
> As it is now I do not know the id-number and so I cannot intelligently continue with the newly created record.
>
> Is this a SQL-command?
> I think I have read about this on the list but I cannot really find these threads.
>
> /Daniel
> 0736 704070
>
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a
> new message -> mailto:omnisdev-en at lists.omnis-dev.com
_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
More information about the omnisdev-en
mailing list