how to get sql-server identity value from powerbuilder 11.5

gplana
gplana used Ask the Experts™
on
Dear Expert:

I am inserting a record on a SQL-Server 2000 table with an identity on its primary key. I want to get the value inserted on the primary key. I know I should execute this sentence:

SELECT @@IDENTITY;

but it doesn't work on the embedded SQL code in PowerBuilder, I get a syntax error. I tried to use SELECT @@IDENTITY FROM DUAL, where DUAL is a table which I created with a single record, but the gotten result is NULL.

I'm using OLE-DB provider for SQL-Server.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ethier u have to create trigger or Function which is generated key number ..... i mean to say unique Key number

double ld_id

 SELECT nvl(max(<col_name>),0) + 1  into  :ld_id  FROM your_table_name

this above syntax find the max id and try to set into primary Key Column
Sorry, but this is not what I want. I need to guarantee there are not duplicated values, and this way I think it could be duplicated values if two process executes in parallel this sentence. This is why I created the filed with the identity property set on.

Also, this SELECT could get a wrong value if someone inserted between my insert and this select execution.
which database u r using???
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I'm using SQL-Server 2000, as I said on my first post.
The sqlteam link is very useful. However, is not what I'm looking for as all 3 alternatives are used in SELECTs without FROM, and this is my problem, it seems powerbuilder doesn't allow to put a SELECT without a FROM.
u can use system table... in oracle using DUAL... check in SQL Server .. i m not falimiler with SQL Server....
like

select ..... from dual;

I don't think there is a system table on SQL-Server. The phylosophy on SQL-Server is a little different from Oracle.
I think I need the help of a experienced powerbuilder programmer more than an SQL-Server or Database expert.
I think I could solve this issue if I can find a way to bypass SQL directly to the Database without having to pass to the PowerBuilder parser.
You have to do the insert and the @@Identity in a stored procedure and get the SP to return the value back to powerbuilder

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial