Getting New RecID after SQL INSERT

[halldorg at vortex.is]

I think the issue is that Valentina employs a rather unusual "pointer" based 
relational schema, in most rdbms system the relation between tables is defined

in terms of the primary key and all primary key values are transferred to the 
child....

In the case that started the original question Valentina is not really being 
used as a relational db, but rather a "pointer" based db - which is fine in 
itself but might cause some confusion when discussed in sql/relational terms.

It is common to use rec_id of sql tables as unique reference but less common 
to use it as the relational key between tables - most rdbms use separate 
constructs such as "sequences" to provide running unique numbering systems for

that purpose(I hope V2 will sequences ?)

Methods such as "select max(rec_id)" from the internal record id could work to

get the latest inserted record in a single user db but in a server environment

it can hardly be relied on.

The first consideration should be to see if the database design could be 
improved and a suitable unique key other than rec_id found, or at least if the

rec_id is the only key, consider if creating a separate number field to 
manually create the relation may be a better choice.

Internal rec_id's are essential when working with "current" record but really 
a careless(lazy) way of constructing tble relations.

Halldor


> 
> Hello Frank,
> 
> Friday, August 20, 2004, 3:49:14 PM, you wrote:
> 
> FS> Hi Ruslan,
> 
> 
> FS> On Aug 19, 2004, at 5:17 PM, Ruslan Zasukhin wrote:
> 
> >> On 8/19/04 11:32 PM, "Frank Schima"
> >> <frank-list2 at mindstarprods.com> 
> >> wrote:
> >>
> >>> I can't seem to recall if there is a way to get the RecID of a newly
> >>> INSERTed record using SQL? I'm using V4RB 1.10. I know how to do it
> >>> with Table methods but I want to use a pure SQL method if possible.
> >>>
> >>> I'm sure we'll have this in Valentina 2.
> >>
> >> Actually I think we have no yet this feature.
> >>
> >> And if I not mistake, ALL dbms I have see, resolve this
> >> Via API function  get_last_id()
> >>
> >> Am I right ?
> 
> FS> Usually they have a SQL way to do that. For instance in MySQL it is:
> 
> FS>     SELECT LAST_INSERT_ID()
> 
> FS> In Sybase, you can use:
> 
> FS>     SELECT @@IDENTITY
> 
> FS> This ability is especially important for stored procedures.
> 
> 
> FS> Best regards.
> FS> Frank
> 
> FS> _______________________________________________
> FS> Valentina mailing list
> FS> Valentina at lists.macserve.net
> FS> http://lists.macserve.net/mailman/listinfo/valentina
> 
> Please note, you have no ability to get the last inserted id as result
> of sql-insertion anyway.
> 
> Definitely, this stuff is usual for SPs.
> But for now I think it could be something like this:
> 
> select max(REC_ID) ....
> 
> Or more correctly...
> Following normalizing rules you need to have PK or uniqie field for
> each table. So what is the problem to:
> 
> select REC_ID from YOUR_TABLE where UNIQUE_FIELD = inserted_value
> 
> ?
> 
> Do I miss something?
> 
> -- 
> Best regards,
>  Ivan                            mailto:IvanSmahin at public.kherson.ua
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
> 
>