Solved

How do I get SCOPE_IDENTITY working correctly.

Posted on 2014-04-23
3
871 Views
Last Modified: 2014-04-24
Good Morning Experts,

I am having this issue getting SCOPE_IDENTITY() to work.  Here are the important pieces of information.  I am using PowerBuilder 12.5 Build 2511.  The database is Sybase ASE 15.

Here is what I have as my connection parameters.  I have been using this since the beginning to time so I know this is set up correctly with the exception of the new line about using SCOPE_IDENTITY.
SQLCA.dbms = ProfileString("dbServers.ini","oceanmd_db","dbms","")
SQLCA.servername = ProfileString("dbServers.ini","oceanmd_db","servername","")
SQLCA.database = ProfileString("dbServers.ini","oceanmd_db","database","")
SQLCA.DBParm = "Host = 'Tidebox App'"
SQLCA.DBParm = "Identity='SCOPE_IDENTITY()'"  //new
SQLCA.LogId = logname
SQLCA.LogPass = logpass
SQLCA.AutoCommit = FALSE

Open in new window


Here is what I have in my code where I am trying to retrieve the identity field value.  I have indicated the location_id as the identity field in the datawindow.
dw_station_location.AcceptText()
li_r = dw_station_location.update(true, false)
IF li_r  = 1 THEN	
	
	//SELECT @@identity INTO :new_locId FROM LOCATION USING SQLCA;
	SELECT SCOPE_IDENTITY() INTO :new_locId FROM LOCATION USING SQLCA;
	dw_stations.SetItem(li_row, "tb_station_location_id", new_locId)
	
	// Before we do the update, set all rows to NewModified!
	// This will insure that we inserting a new row and not updating an existing row.
	dw_stations.SetItemStatus(li_row, 0, Primary!, NewModified!)
	
	// do the update
	dw_stations.AcceptText()
	li_r = dw_stations.Update(true, false)
	IF li_r = 1 THEN
		uf_updatecomments(dw_stations).Update(true, true)
		IF li_r = 1 THEN
			Messagebox(confirmname, "Update Completed Successfully.")
			COMMIT USING SQLCA;
		ELSE
			ROLLBACK USING SQLCA;
			//Write to log file
		END IF
	ELSE
		ROLLBACK USING SQLCA;
		//Write to log file
	END IF
ELSE
	ROLLBACK USING SQLCA;
	//Write to log file
END IF

Open in new window


The above code will not compile so I am missing something somewhere.  Here is the error message that I get.

Database  C0038: Function 'SCOPE_IDENTITY' not found.  If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).

Open in new window

0
Comment
Question by:Monica Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:Monica Stanley
ID: 40017625
Also... I have tried getting the location_id from the datawindow but since I have not committed the transaction it is not updated at the point in time I am trying to get the new id.
0
 
LVL 3

Accepted Solution

by:
kotukunui earned 500 total points
ID: 40018753
I thought SCOPE_IDENTITY() was a Microsoft SQL Server thing? I'm not sure that it is supported in Sybase ASE.

I believe using @@IDENTITY is the only tool you get when using Sybase ASE
0
 

Author Closing Comment

by:Monica Stanley
ID: 40020667
I guess you are correct.  That is too bad.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question