sorry for the duplicate post

-stan

--- In firebird-support@yahoogroups.com, "Stan" <shizius@...> wrote:
>
> 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
>