mSportsman
Programmer
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:
Here is the SQL Statements and the results
Then execute this immediately after:
I have also tried this with a stored procedure as follows:
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
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:
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