INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Strange Problem with Identity field

Strange Problem with Identity field

(OP)
I am having a problem where @@IDENTITY is returning null after a record has been successfully inserted into a table that contains an IDENTITY Column.
The methods I have tried always work again a test database. The same methods sometimes work against the production data, but sometimes they do not.
The Database Schema is the same for the TEST data and the production data.

Were are running PERVASIVE Version 11.30 Running on a Virtual Server ( Windows Server 2008R Standard )

Here is the table Schema:

CODE --> SQL

CREATE TABLE "BFCX_NCR"(
 "ID" IDENTITY DEFAULT '0',
 "JOB" CHAR(6),
 "SUFFIX" CHAR(3),
 "SEQUENCE" CHAR(6),
 "DATE_INCIDENT" DATE,
 "EMPLOYEE" CHAR(5),
 "EMPLOYEE_NAME" CHAR(30),
 "SHIFT" CHAR(1),
 "CAUSE" CHAR(30),
 "DESC_INCIDENT" CHAR(512),
 "DISPOSITION" CHAR(20),
 "DESC_DISPOSITION" CHAR(512),
 "SUBMITTED" CHAR(5),
 "SUBMITTED_NAME" CHAR(30),
 "SUBMITTED_SHIFT" CHAR(1),
 "DATE_LAST_CHANGE" DATE,
 PRIMARY KEY ("ID"),
 UNIQUE ("ID")); 

Here is the SQL Statements and the results

CODE -->

INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
VALUES ( 0, '011695', 'ENG', '997000', '2017-05-04', '00678', 'JOHN DOE', '1', 'Galv/Paint','TEST XXX','NOT Provived', 'THIS IS A TEST', '00712', 'JOE BLOW', '1', '2017-05-04' );

<<<<<<<<<<<<<<<<<<<<<<<<
INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
VALUES ( 0, '011695', 'ENG', '997000', '2017-05-04', '00678', 'BRANDI WRIGHT', '1', 'Galv/Paint','TEST XXX','NOTProvived', 'THIS IS A TEST', '00712', 'BROOKE MAUZEY', '1', '2017-05-04' )
SQL statement(script) has executed successfully.
1 row was affected.
>>>>>>>>>>>>>>>>>>>>>>>> 

Then execute this immediately after:

CODE -->

SELECT @@IDENTITY;
<<<<<<<<<<<<<<<<<<<<<<<<
     EXPR_1
===========
     (Null)

1 row was affected.

>>>>>>>>>>>>>>>>>>>>>>>> 

I have also tried this with a stored procedure as follows:

CODE -->

CREATE PROCEDURE "spInsertNCR" 
	( :JOB CHAR(6), :SUFFIX CHAR(3), :SEQUENCE CHAR(6), :DATE_INCIDENT DATE, :EMPLOYEE CHAR(5), :EMPLOYEE_NAME CHAR(30), :SHIFT CHAR(1), :CAUSE CHAR(30), :DESC_INCIDENT CHAR(512), :DISPOSITION CHAR(20), :DESC_DISPOSITION CHAR(512), :SUBMITTED CHAR(5), :SUBMITTED_NAME CHAR(30), :SUBMITTED_SHIFT CHAR(1), :DATE_LAST_CHANGE DATE )
 	RETURNS  (NewID INTEGER);
BEGIN
	// DECLARE :NewID INTEGER;
	INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
	VALUES ( 0, :JOB, :SUFFIX, :SEQUENCE, :DATE_INCIDENT, :EMPLOYEE, :EMPLOYEE_NAME, :SHIFT, :CAUSE, :DESC_INCIDENT, :DISPOSITION, :DESC_DISPOSITION, :SUBMITTED, :SUBMITTED_NAME, :SUBMITTED_SHIFT, :DATE_LAST_CHANGE );
	SELECT @@IDENTITY as NewID;
END 

This also has the same behavior and return null most of the time.

Any help to a solution or advice on how to trouble shoot this problem will be deeply appreciated.
Thank you,
MS

RE: Strange Problem with Identity field

If it always works against the test database but fails against the production database, I would say there might be something wrong with the production database. A few questions:
1. Are the production and test databases on the same server?
2. If they aren't, are the servers running exactly the same version of PSQL?
3. What happens if you create a new database, create the table in the new database, and run the insert statement?
4. What happens if you create a new table in the production database and insert the record? Does the @@IDENTITY work?
5. As a nuclear option, you could export the data from the current production table, drop the table, re-create it, and then import the data.

I've tried it here with my v11.30 server and @@IDENTITY worked every time I tried it.

Mirtheil
http://www.mirtheil.com

RE: Strange Problem with Identity field

(OP)
Answer to questions:
1. Both databases are running on the same server.
3. ( I did not try this, because I got results from question #4 )
4. I created a new table in the production database, and methods of inserting record seemed to work.
5. There is another table that has a foreign key constraint to this table, so to try to fix the problem I dropped both of the tables from the database. I then recreated the tables with the same names as before. Added the constraints as before.
This did not work.

Next I tried creating the tables with different names in the same production database.... This worked..... But only a few times before it started returning null again.

My next move is .... I have asked the IT people here if they could reboot that server. They said they will over lunch break.

I will let you know what I find out after the reboot.

Thanks for all of your help...
MS

RE: Strange Problem with Identity field

(OP)
The reboot of the server seemed to have fixed this issue.
Any ideas on how this database may have been affected in this way?

MS

RE: Strange Problem with Identity field

In all the years I've supported and worked on the Pervasive engine (since 1995), I've never seen anything like it. The only guess I have would be that somehow a cache or memory corruption occurred in the SQL engine. Restarting cleared the memory and allowed everything to work again.
If it comes back, you might want to contact Actian to see if they can offer any insight.

Mirtheil
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close