Return Unique Id

Doug Easterbrook doug at artsman.com
Tue Mar 7 17:00:38 UTC 2023


hi Daniel:

you have to OVERRIDE $insert in your table class.

and then, you get to build your own $insert which doesn’t take to much.  eg the following is sort of a minimum/bare essentials of what you need to do

Calculate TableName as $cinst.$servertablenames
Calculate InsertString as $cinst.$insertnames()
Calculate returnCols as ‘*’  ## or specify some fields if you only want tem
  
Calculate Statement as con('insert into ',TableName,' ',InsertString)
Calculate Statement as con(Statement,' returning ',returnCols)

— I have a helper for exec direct, so I kludged the code here, but you get the idea
do the ExecDirect on the (Statement) Returns StatusOK

If StatusOK
   Do iStatementRef.$fetch(returnValuesRow,1)
  Do $cinst.$assignrowTM(returnValuesRow,kTrue)   ## note this is is like $assignRow, but I made it case insensitive
End If



thats the basis of it




Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Mar 7, 2023, at 8:23 AM, Daniel Sananes <daniel.s at kopparbergs.se> wrote:
> 
> 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.
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list