How do I get SCOPE_IDENTITY working correctly.

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

Monica StanleyInformation Technology SpecialistAsked:
Who is Participating?
 
kotukunuiConnect With a Mentor Commented:
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
 
Monica StanleyInformation Technology SpecialistAuthor Commented:
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
 
Monica StanleyInformation Technology SpecialistAuthor Commented:
I guess you are correct.  That is too bad.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.