Proxy table and autoincrement and SELECT@@IDENTITY

[roy_bungert]

Hi,

I'm a Powerbuilder developer and I use a ASA 16 with proxy tables in my current project. I guess my question is better ask here as in the Powerbuiler forum.

To get the value from an autoincremented ID inside Powerbuilder, I have to specify the Identity Column at 'Update Properties..." of the table (inside Powerbuilder). This works well with standard tables.

If I change the table to be a proxy table, I always get the value '0' for that incremented ID.

I guess it has something to do with the SELECT @@IDENTITY at the current db. Is there a switch / way to get the IDENTITY-value from the proxy table?

Roy

[JasonHinsperger · Accepted solution]

You can use the FORWARD TO statement to do this:

eg.

INSERT INTO <proxy table>...

FORWARD TO <remote server name>;

  select @@identity;

FORWARD TO;

You can find more details on this statement in the docs:

http://dcx.sap.com/index.html#sa160/en/dbreference/forward-to-statement.html*d5e55634

hth,

--Jason

[roy_bungert · Accepted solution]

That sounds good. Do you know how I can get the name of the remote server connection of a certain proxy table?

[JasonHinsperger · Accepted solution]

I'm not sure what you mean.

The <remote server name> above is the name of the remote server you had to create in order to create the proxy table in the first place.

When the remote server connection is made from your local connection, it is not dropped after each request, which is why this works.

--Jason

[roy_bungert · Accepted solution]

We have different customers with different servernames/ remoteservernames.

Some of the customers work with proxy tables, other don't do it.

So it is important for me to have a possibility do get the name of the remote server (if it is a proxy table.

[JasonHinsperger · Accepted solution]

This statement would do it:

SELECT srvname

FROM SYS.SYSSERVER JOIN SYS.SYSTABLE ON SYS.SYSSERVER.srvid = SYS.SYSTABLE.srvid

WHERE SYS.SYSTABLE.table_name = 'your_table_name';

srvname would be the nme of the remote server if you were using a proxy table and hNULL if it was a local table

--Jason

[Former Member]

Hi Roy,

     You can't get identity value because it was assigned by the remote db. You should write your own function to get identity from remote table using select max() function.

For example in Powerbuilder you could write somthing similar in your sqlpreview event: if sqltype = previewinsert! then    if your table is a proxy table then /* select. max () from proxy table */    setitem(row,id_column,value )    end if end if

[roy_bungert]

Thank you. But this means that I have to do these changes at every place where I use this kind of inserting a row. BUt nevertheless it should work!

Are there any solutions with less work?

I don't know whether it is an error or not. Perhaps it will be corrected?!?!?

If it is not an error, that means that this functionality inside Powerbuilder is very useless. Not only useless because I did't get an error code but I get the value 0! So a wrong row (the one with id= 0) is referenced instead he one which is expected.

[Former Member]

I don't see this as a Powerbuilder bug because it doesn't know the difference between table and proxy table, this is a SQL Anywhere concept.

However you could try modify PBODBXXX.INI as follow:

when you find GetIdentity = 'Select @@identity' you could change in

GetIdentity = 'sp_my_get_identity &TableName'

In your sp you coul differentiate the call using @@identity for your db tables and select max() for remote table.

If it function you write once and it's transparent for Powerbuilder.

I have no other idea.

[roy_bungert]

From my point of view it's an error in the SQL Anywhere concept of proxy tables - not an error of Powerbuilder.

I will try your idea and will give a feedback. But it seems to me that it is a lot of work too for this workaround. In the StoredProcedure I have to diffentiate between each table because my ids are different in each table (ProductID, AdressID etc.) So I have to write a max(...) for each table 😞

Or does anybody have an other idea?

[former_member329524]

Hi, Roy

Proxy table update is auto-commit, as far as I know.

So, I am not sure if it is even possible to retrieve the @@identity value from updating the proxy table.

I would, however, recommend doing the same thing I do - use remote stored procedure for update, which returns the result set together with the @@identity from the remote DB.