Hi guys,

Using 2.04B4. I have a function that I want to call during an insert and have hit a wall.

ALTER FUNCTION "sequence_next2" (IN "sequence_name" VARCHAR(40) COLLATE ANSI_CI)
RETURNS BIGINT
BEGIN
  DECLARE SequenceCursor CURSOR FOR Stmt1;
  DECLARE SeqValue BIGINT DEFAULT NULL;
  DECLARE SeqIncrement BIGINT;

  PREPARE Stmt1 FROM 'SELECT EdbSequenceValue, EdbSequenceIncrement FROM EdbSequence WHERE EdbSequenceName = ?';
  OPEN SequenceCursor USING sequence_name;

  START TRANSACTION ON TABLES 'EdbSequence';
  BEGIN
     FETCH FIRST FROM SequenceCursor ('EdbSequenceValue', 'EdbSequenceIncrement') INTO SeqValue, SeqIncrement;
     SET SeqValue = SeqValue + SeqIncrement;
     IF NOT EOF(SequenceCursor) THEN
        UPDATE SequenceCursor SET 'EdbSequenceValue' = SeqValue;
     END IF;
     COMMIT;

  EXCEPTION
     ROLLBACK;
  END;

  RETURN SeqValue;
END

Executing the function in EDB Manager (right click + execute) with parameter value 'AuditLog' works fine. However, when in the below script I see an error message:
The connection to the ElevateDB Server has been interrupted or timed out and needs to be re-connected. Would you like to continue with the connection?

SCRIPT
BEGIN
EXECUTE IMMEDIATE '
INSERT INTO AuditLog(AuditLogID, BusinessCode, UserCode,AuditReference, System, Operation, Description)
VALUES (sequence_next2(''AuditLogID''), ''001'', '''', 442012, ''DISPENSEUPDATES'', ''PRDADD'',
''Product Added'')';
END


I also came across a related SQL statement that used to work but produces an error in the current build of elevate
SELECT sequence_next2('AuditLog') FROM categorytypelookup WHERE categoryType='A'

This is the error message
ElevateDB Error #1103 An invalid or unknown request was sent to the server

FYI, SELECT * FROM categorytypelookup WHERE categoryType='A' normally returns a single record.

Any tips or workarounds? Is this an elevate bug?

Chris,

<< Executing the function in EDB Manager (right click + execute) with
parameter value 'AuditLog' works fine. However, when in the below script I
see an error message:
The connection to the ElevateDB Server has been interrupted or timed out and
needs to be re-connected. Would you like to continue with the connection? >>

I can't reproduce this here.  Is there anything special about the table
being inserted into, in terms of triggers, etc. ?

<< I also came across a related SQL statement that used to work but produces
an error in the current build of elevate
SELECT sequence_next2('AuditLog') FROM categorytypelookup WHERE
categoryType='A'

This is the error message
ElevateDB Error #1103 An invalid or unknown request was sent to the server
>>

That I *can* reproduce, and is a bug that will be fixed in the next release.
The workaround is to make sure that you aren't asking for a sensitive result
set (RequestSensitive=False).

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for a prompt reply.

Nothing special about the table - script below. There are no triggers or constraints/indexes other than the primiary key.
Not sure if it makes any difference but the database was backed up by build 2.03 and restored in 2.04
There are currently 140K rows in the table and I'm using a remote session through 127.0.0.1

EXECUTE IMMEDIATE 'CREATE TABLE "AuditLog"
(
"AuditLogID" INTEGER NOT NULL,
"AuditLogDateTime" TIMESTAMP DEFAULT CURRENT_TIMESTAMP   NOT NULL,
"BusinessCode" VARCHAR(3) COLLATE "ANSI_CI",
"UserCode" VARCHAR(3) COLLATE "ANSI_CI" NOT NULL,
"AuditReference" INTEGER,
"System" VARCHAR(20) COLLATE "ANSI_CI" NOT NULL,
"Operation" VARCHAR(40) COLLATE "ANSI_CI" NOT NULL,
"Description" VARCHAR(40) COLLATE "ANSI_CI",
CONSTRAINT "PK_AuditLog" PRIMARY KEY ("AuditLogID")
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

I'll try testing this with a different/brand new table.

Just figured out that the above INSERT INTO only disconnects when in a script. Using a statement works ok.
The actual insert *always* succeeds. Even when using the script, a record is inserted into the table.

However, when running the script I see the disconnection message. When I click OK, I get Error#1103 - An invalid or unknown request was sent to the server.

Hope this is enough detail.

Chris,

<< However, when running the script I see the disconnection message. When I
click OK, I get Error#1103 - An invalid or unknown request was sent to the
server. >>

I think this is the same issue, but in the case of the script requires some
special timing or executions prior to the script to get it to happen.  The
core issue is that the progress event handlers are not being disconnected
properly on the server after execution calls, and are being triggered by row
navigations on the sensitive result set that re-generate the sequence UDF
that you wrote.

--
Tim Young
Elevate Software
www.elevatesoft.com