haven't managed to add into bug tracker, so post here (can't say, that
pasted all needed parts) - problem is with SP_Select_Kolektyvai_Search
stored procedure: when I use for select * got overflow error during
data type conversion - conversion error from string "".
If I write down all fields, there is no overflow


SET SQL DIALECT 3;

CREATE DATABASE 'localhost:E:\error.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 1024
DEFAULT CHARACTER SET UNICODE_FSS;

/* Domains definitions */

CREATE DOMAIN BOOL_TYPE AS SMALLINT
DEFAULT 0
NOT NULL
CHECK (VALUE BETWEEN 0 AND 1);

CREATE DOMAIN CHAR24 AS VARCHAR (24) CHARACTER SET UNICODE_FSS
DEFAULT ''
NOT NULL;

CREATE DOMAIN CHAR50 AS VARCHAR (50) CHARACTER SET UNICODE_FSS
DEFAULT '';

CREATE DOMAIN CITY_TYPE AS VARCHAR (24) CHARACTER SET UNICODE_FSS
DEFAULT '';

CREATE DOMAIN COUNTRY_TYPE AS VARCHAR (24) CHARACTER SET UNICODE_FSS
DEFAULT 'Lietuva'
NOT NULL;

CREATE DOMAIN DATE_TYPE AS DATE
DEFAULT '01/01/1930';

CREATE DOMAIN PK_TYPE AS DECIMAL (15, 0)
DEFAULT 1
NOT NULL
CHECK (VALUE > 0);

CREATE DOMAIN PK_TYPE_NULL AS DECIMAL (15, 0)
DEFAULT NULL
CHECK (VALUE is NULL or VALUE > 0);

CREATE TABLE KOLEKTYVAI (
"Kolektyvo_ID" PK_TYPE NOT NULL,
"Pavadinimas" CHAR50 COLLATE UNICODE_FSS,
"Vadovo1_ID" PK_TYPE_NULL,
"Vadovo2_ID" PK_TYPE_NULL,
"Vadovo3_ID" PK_TYPE_NULL,
"Miestas" CITY_TYPE COLLATE UNICODE_FSS,
"Salis" COUNTRY_TYPE NOT NULL COLLATE UNICODE_FSS);

CREATE TABLE ZMONES (
"Zmogaus_ID" PK_TYPE NOT NULL,
"Vardas" CHAR24 NOT NULL COLLATE UNICODE_FSS,
"Pavarde" CHAR24 NOT NULL COLLATE UNICODE_FSS,
"Lytis" BOOL_TYPE NOT NULL,
"Gimimo_data" DATE_TYPE,
"Miestas" CHAR24 NOT NULL COLLATE UNICODE_FSS,
"Salis" COUNTRY_TYPE NOT NULL COLLATE UNICODE_FSS);


INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (1, 'Stella',
1, NULL, NULL, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (2, 'Kaspinas',
3, 2, NULL, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (3, 'Rolo', 4,
5, NULL, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (4, 'Flamenko',
6, NULL, NULL, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (5, 'SŠS
Gintarėliai', 7, 8, 9, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (6, 'Svaja',
10, NULL, NULL, '', 'Lietuva');
INSERT INTO KOLEKTYVAI ("Kolektyvo_ID", "Pavadinimas", "Vadovo1_ID",
"Vadovo2_ID", "Vadovo3_ID", "Miestas", "Salis") VALUES (7, 'Junda',
11, 12, NULL, '', 'Lietuva');

COMMIT WORK;

INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (1, 'Saulius', 'Skambinas',
1, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (2, 'Daiva', 'Dackevičien=
79;',
0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (3, 'Giedrius',
'Januškevičius', 1, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (4, 'Jonas', 'Baltaragis',
1, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (5, 'Vida', 'Baltaragienė',
0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (6, 'Ingrida', 'Urbutienė',
0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (7, 'Toma', 'Mušauskienė',
0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (8, 'Dalia', 'Šleivienė', 0,=

'01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (9, 'Petras',
'Janulevičius', 1, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (10, 'Jurgita',
'Vaškevičiūtė', 0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (11, 'Vilma',
'Skublickaitė', 0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (12, 'Rasa', 'Žadeikaitė',
0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (13, 'Rolandas',
'Burneikis', 1, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (14, 'Violeta',
'Burneikienė', 0, '01/01/1930', 'Kaunas', 'Lietuva');
INSERT INTO ZMONES ("Zmogaus_ID", "Vardas", "Pavarde", "Lytis",
"Gimimo_data", "Miestas", "Salis") VALUES (15, 'Imantas', 'Brikmanis',
1, '01/01/1930', 'Kaunas', 'Lietuva');

COMMIT WORK;

ALTER TABLE KOLEKTYVAI ADD CONSTRAINT PK_KOLEKTYVO_ID PRIMARY KEY
("Kolektyvo_ID");
ALTER TABLE ZMONES ADD CONSTRAINT PK_ZMONES PRIMARY KEY ("Zmogaus_ID");

ALTER TABLE KOLEKTYVAI ADD CONSTRAINT FK_VADOVO1_ID FOREIGN KEY
("Vadovo1_ID") REFERENCES ZMONES ("Zmogaus_ID") ON DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE KOLEKTYVAI ADD CONSTRAINT FK_VADOVO2_ID FOREIGN KEY
("Vadovo2_ID") REFERENCES ZMONES ("Zmogaus_ID") ON DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE KOLEKTYVAI ADD CONSTRAINT FK_VADOVO3_ID FOREIGN KEY
("Vadovo3_ID") REFERENCES ZMONES ("Zmogaus_ID") ON DELETE CASCADE ON
UPDATE CASCADE;

SET TERM ^ ;

/* Stored procedures definition */

/* Stored Procedure: SP_DELETE_ZMONES */
CREATE PROCEDURE SP_DELETE_ZMONES (
ZMOGAUS_ID DECIMAL (15, 0))
AS
BEGIN
EXIT;
END
^


/* Stored Procedure: SP_EDIT_TEISEJAI */
CREATE PROCEDURE SP_EDIT_TEISEJAI (
TEISEJO_ID DECIMAL (15, 0),
VARDAS VARCHAR (24),
PAVARDE VARCHAR (24),
LYTIS SMALLINT,
GIMIMO_DATA DATE,
MIESTAS VARCHAR (24),
SALIS VARCHAR (24))
AS
BEGIN
EXIT;
END
^

/* Stored Procedure: SP_SELECT_KOLEKTYVAI_SEARCH */
CREATE PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50))
RETURNS (
KOLEKTYVO_ID DECIMAL (15, 0),
PAVADINIMAS VARCHAR (50),
MIESTAS VARCHAR (24),
SALIS VARCHAR (24),
VADOVO1_ID DECIMAL (15, 0),
VADOVO2_ID DECIMAL (15, 0),
VADOVO3_ID DECIMAL (15, 0))
AS
BEGIN
EXIT;
END
^

ALTER PROCEDURE SP_SELECT_KOLEKTYVAI_SEARCH (
PAVADINIMAS_IN VARCHAR (50))
RETURNS (
KOLEKTYVO_ID DECIMAL (15, 0),
PAVADINIMAS VARCHAR (50),
MIESTAS VARCHAR (24),
SALIS VARCHAR (24),
VADOVO1_ID DECIMAL (15, 0),
VADOVO2_ID DECIMAL (15, 0),
VADOVO3_ID DECIMAL (15, 0))
AS
BEGIN
/*
for select *
from KOLEKTYVAI
where "Pavadinimas" starting with :Pavadinimas_in
into :Kolektyvo_ID, :Pavadinimas, :Miestas, :Salis, :Vadovo1_ID,
:Vadovo2_ID, :Vadovo3_ID
for select "Kolektyvo_ID" , "Pavadinimas" , "Miestas" , "Salis" ,
"Vadovo1_ID" , "Vadovo2_ID" , "Vadovo3_ID"
*/
for select *
from KOLEKTYVAI
where "Pavadinimas" starting with :Pavadinimas_in
into :Kolektyvo_ID , :Pavadinimas, :Miestas, :Salis, :Vadovo1_ID,
:Vadovo2_ID, :Vadovo3_ID
do
SUSPEND;
END
^

SET TERM ; ^

SET TERM ^ ;


Gediminas