Sv: Return Unique Id

Tue Mar 7 18:04:06 UTC 2023

Doug
Thanks for the code, I will come back on that.
---
Here it seems you are creating a $statement and using $execdirect.
So you describe the statement-approach and not the session-approach (where no statement is created).
Am I confusing myself here?
---
The approach I was investigating up till now was to simply issue $insert() without having to create a statement.
As it is explained in the online-docs: 
Do iv_SQLData.$definefromsqlclass('SCHEMACLASSNAME').
Do iv_SQLData.$sessionobject.$assign(iSessionObj).
Do iv_SQLData.$select().
Do iv_SQLData.$fetch(1000).
Here I can use the $insert() and $update and all the other built in methods. No statement object is created. At least not by me.
Does Omnis create an object that I am not aware of? Strange if that is the case.
So if I override $insert() it would be called as above without me having to create a statement object.
---
It also says:
To send SQL commands to the server, a statement object must first be created.
---
At the moment I have not been using any Tables in my library but letting Omnis create an instance of it when a Schema is used.

So sometimes you create a statement object and sometimes not. This is what is confusing me.
In the $execdirect approach, how does Omnis know anything about the $insert()?

---

Regards
Daniel

-----Ursprungligt meddelande-----
Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Doug Easterbrook via omnisdev-en
Skickat: den 7 mars 2023 18:01
Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Kopia: Doug Easterbrook <doug at artsman.com>
Ämne: Re: Return Unique Id

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

_____________________________________________________________
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.