Solved

How do I get SCOPE_IDENTITY working correctly.

Posted on 2014-04-23
3
858 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
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This is about my first experience with programming Arduino.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

791 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