Solved

How do I get SCOPE_IDENTITY working correctly.

Posted on 2014-04-23
3
844 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

A short article about a problem I had getting the GPS LocationListener working.
This is an explanation of a simple data model to help parse a JSON feed
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

773 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