[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How do I get SCOPE_IDENTITY working correctly.

Posted on 2014-04-23
3
Medium Priority
?
979 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is about my first experience with programming Arduino.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses

650 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