Hi All,

I have a table strings, and a stored procedure get_string_id
that inserts data into this table:

CREATE GENERATOR strings_generator;
CREATE TABLE strings
(
id BIGINT NOT NULL PRIMARY KEY,
string VARCHAR(252) NOT NULL UNIQUE
);

SET TERM !! ;
CREATE OR ALTER PROCEDURE get_strings_id( string VARCHAR(252))
RETURNS( string_id BIGINT )
AS
BEGIN
SELECT id FROM strings WHERE string = :string INTO :string_id;
IF( :string_id IS NULL ) THEN
BEGIN
string_id = GEN_ID (strings_generator, 1);
INSERT INTO strings VALUES( :string_id, :string );
END
SUSPEND;
END!!

Calling get_strings_id('string example') results in:
code: 18446744073709550617 error: multiple rows in singleton select

running query: SELECT * from strings where string = 'string example';
returns:

id string
935 string example
989 string example

running query: SELECT '|'||string||'|' from strings where string =
'string example';
returns:

id string
935 |string example|
989 |string example|

so no leading or trailing spaces are involved.

I am running a lot if concurrent inserts using get_strings_id, I am
using CONCURRENCY|WAIT
transactions. I could have 100 clients trying to insert the same
string into this table at the same time.

How do I have 2 exactly the same strings in the UNIQUE field?

Is this a server bug? or am I assuming something wrong?

thanks,

stan