on 12-10-2014 11:43 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.