I was hoping I could execute:

select lastidentity('mytable','cust_id');

and this would return the GUID that was generated for 'cust_id' column,
Cust_Id is GUID and is defined with a default to "Current_GUID()" and it is the primary key.

But LastIdentity() on a GUID column always returns 0 after the row is inserted.

So how do I get the Cust_Id column value that was just inserted?
BTW, I don't see any way of defining a GUID column as identity.

Am I missing something?

TIA
Barry

On 20/11/2012 5:35 PM, Barry wrote:
> I was hoping I could execute:
>
> select lastidentity('mytable','cust_id');
>
> and this would return the GUID that was generated for 'cust_id' column,
> Cust_Id is GUID and is defined with a default to "Current_GUID()" and it is the primary key.
>
> But LastIdentity() on a GUID column always returns 0 after the row is inserted.
>
> So how do I get the Cust_Id column value that was just inserted?
> BTW, I don't see any way of defining a GUID column as identity.
>
> Am I missing something?
>
> TIA
> Barry
>

Hi Barry,

Are you after...

SELECT MAX(cust_id) from mytable

If I understand what your after,

Cheers,

Phil.

Phil

>SELECT MAX(cust_id) from mytable

Good idea but GUIDs are not sequential so who knows what you'd get.

Roy Lambert [Team Elevate]

Barry

>I was hoping I could execute:
>
>select lastidentity('mytable','cust_id');
>
>and this would return the GUID that was generated for 'cust_id' column,
>Cust_Id is GUID and is defined with a default to "Current_GUID()" and it is the primary key.
>
>But LastIdentity() on a GUID column always returns 0 after the row is inserted.
>
>So how do I get the Cust_Id column value that was just inserted?
>BTW, I don't see any way of defining a GUID column as identity.

The last point may be the problem. I just had a play and that was the first thing that struck me. EDBManager does not have a way either via the dialogs or entering SQL directly, and looking at th OLH  the AS IDENTITY clauses are targeted at integers only.

I suggest you report it as a bug.

Roy Lambert [Team Elevate]

>>BTW, I don't see any way of defining a GUID column as identity.

>The last point may be the problem. I just had a play and that was the first thing that struck me. EDBManager does not have a way either via the dialogs or entering SQL directly, and looking at th OLH  the AS IDENTITY clauses are targeted at integers only.
<<

>I suggest you report it as a bug.

Roy,
  Thanks for confirming it. I thought it might be "me". I've sent an email off to Tim.

Barry

Barry

> I was hoping I could execute:
>
> select lastidentity('mytable','cust_id');
>
> and this would return the GUID that was generated for 'cust_id' column,
> Cust_Id is GUID and is defined with a default to "Current_GUID()" and it is the primary key.
>
> But LastIdentity() on a GUID column always returns 0 after the row is inserted.
>
> So how do I get the Cust_Id column value that was just inserted?
> BTW, I don't see any way of defining a GUID column as identity.

I think the easiest way is to use a parameter. To show the principle, presuming you have the table set up as described
above,  you can use a script

SCRIPT
BEGIN
DECLARE aguid guid;

EXECUTE IMMEDIATE 'INSERT INTO mytable (cust_id) VALUES(?)' USING aguid;
SET LOG MESSAGE TO aguid;
END

John

John


As ever an interesting approach. However, I think you've solved the wrong problem

My interpretation of what Barry wants is to use it as an automatically generated key. You can do that by creating it as GENERATED and using CURRENT_GUID as the expression. However, you can't create it as an IDENTITY hence LastIdentity doesn't work. You can only create identities using the various types of integers.

Checking up I've realised I, and probably Barry, have misread the manual and it is WAD not a bug. Damn shame though.

Roy Lambert [Team Elevate]

Barry


It was both of us! Replying to John Hay's post I realised I'd misread the manual. It clearly says that LASTIDENTITY returns an integer. My eyes had stopped before they reached the Returns information and my brain switched off. Sorry

Roy Lambert [Team Elevate]

ps Sorry to Tim as well, but a LASTVALUE would make a nice enhancement.

Barry,

I think I use a mechanism which gives the effect you need.

I call a piece of SQL which inserts a record into my table and which has an OUT parameter where the OUT parameter is the value of the NEWROW.ID which is a GUID.

If you are unsure about this just work through creating a procedure with an IN/OUT param in EDBMgr & then call this procedure from a SCRIPT.

I can then pass this GUID back into my application so that when the user wants to POST I can use an UPDATE statement, posting into the same record that has the GUID.

It is a little cumbersome to write the first time, but if you write a Delphi object to do it ... you get the behaviour in all the descendents for free.

The disadvantage is mainly that you have to be sure that there are no values in the table which require values from the User at the INSERT stage, since this step always only generates the GUID + any in-built defaults.

Adam

On 20/11/2012 9:29 PM, Roy Lambert wrote:
> Phil
>
>> SELECT MAX(cust_id) from mytable
>
> Good idea but GUIDs are not sequential so who knows what you'd get.
>
> Roy Lambert [Team Elevate]
>

Thanks Roy,

For some reason I thought MAX worked off some internal RecordID, of
course it doesn't.