In MySQL the 'last_insert_id' returns the id of the last inserted record.

Is there a similar way to get that in EWB from a EDB database table?

On 15/09/2015 10:30 a.m., Trinione wrote:
> In MySQL the 'last_insert_id' returns the id of the last inserted record.
>
> Is there a similar way to get that in EWB from a EDB database table?
>

Hi

It depends what you mean by the "last inserted record".  By whom?

9.13 LASTIDENTITY might do it for you if you want to know the last
insert by YOU.  Not much use if you have a multi-user system where other
users can add records.

SELECT MAX(Id) FROM MyTable could work for You.

Personally, I use a "lastkey" table with a record like:-

(tablename, LastIDUsed)

.... grab a key and update the lastkey table then use that key in a
separate transaction to update the main table.

Cheers

Jeff

<< In MySQL the 'last_insert_id' returns the id of the last inserted record.

Is there a similar way to get that in EWB from a EDB database table? >>

Yes, but I wouldn't recommend doing anything like that.  EWB database/dataset access is done using a disconnected data model, so trying to synch primary keys/IDs is going to be an exercise in frustration and will produce bad results.

Tim Young
Elevate Software
www.elevatesoft.com

Jeff Cook wrote:
<<  LASTIDENTITY might do it for you if you want to know the last
insert by YOU.  >>

Yes, it is the last id entered by me/the particular user.

I have created a DataSet. However, I have not been able to get it to return any value other that '0'.

At what point do I call it?

DataSet RowSource Query is:
SELECT LASTIDENTITY('Tablename', 'ColumnID') AS LastID

Tim Young [Elevate Software] wrote:
<< Yes, but I wouldn't recommend doing anything like that.  EWB database/dataset access is done using a disconnected data model, so trying to synch primary keys/IDs is going to be an exercise in frustration and will produce bad results. >>

Tim,
I have a Grid, and I am creating a new record via an edit box and a date field. When the user hits 'Save', the new record is added to the Grid ordered by the date, which means it can be in any position on the Grid.

Using the InitFind/Find I thought would be the correct way to position the pointer on the New record so it is highlighted to the user and not have it jump back to the top after the grid's dataset,loadrows is fired in order to Refresh the grid.

Records can have the smae Date, so that's not a way to find it. the only unique identifier is the ID.

Any suggestions or is it just not possible?

On 9/15/2015 8:16 AM, Trinione wrote:
> Records can have the smae Date, so that's not a way to find it. the only unique identifier is the ID.
> Any suggestions or is it just not possible?

One alternative option is to have the key added by EWB - GUID would be
the easiest one.  The suggestions others have made to use a separate
table would work as well - you'd need to query this table first to
obtain the next useable ID and then include it during post (this would
result in "Gaps" should user cancel the save so not sure if that's a
problem). Simple web service call could be used to obtain the key from
backend.

Raul

Thanks all.

I decided the simplest way for me to get the record would be to query the userID and the dateEntered fields of the table for the 'highest' record they entered for the specific productID.

That would always be the last record entered by the user.

Thanks again.

<< I decided the simplest way for me to get the record would be to query the userID and the dateEntered fields of the table for the 'highest' record they entered for the specific productID. >>


This may help someone ... somewhere .... someday.

This is the query I am using in my situation.

SELECT recordID FROM tableName
WHERE custAcctNumber = 3939 AND userID = 34
ORDER BY dateTimeEntered DESC
RANGE 1 TO 1

BTW, I forgot to mention yesterday that I'll be adding "echo" support to the transaction commits in 2.03 or 2.04 when I enhance the database layer.  That will allow you to retrieve autoinc/identity/generated/computed column values after inserts/updates without additional requests to the web sever.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
<< BTW, I forgot to mention yesterday that I'll be adding "echo" support to the transaction commits in 2.03 or 2.04 when I enhance the database layer.  That will allow you to retrieve autoinc/identity/generated/computed column values after inserts/updates without additional requests to the web sever. >>

In one word - FANTASTIC!

Thanks Tim.