» InterBase и FireBird: вопросы по работе и их решение

X11

Если можно добавлять юзверей с помощью SQL, то при чём здесь клиент? Клиент должен передать запрос серверу, а не сам его обрабатывать.

Клиент может парсить некоторые запросы сам, и то, что "не понимает" сервер - эмулировать вызовами Services API. Так, например, IBExpert делает в своем "испольняльщике" скриптов.

...Правда, к ISQL это не относится.

Возможно. Но таких клиентов, как IBExpert единицы.

Omsk
А что мешает создать пользователя штатными средствами того же FlameRobin? Или использовать gsec?

Добавлено:

Цитата:

И написано, что эти команды доступны в Isql

Где написано? в ReleaseNotes к FB 2.1.x?

OXDBA, я начинаю изучать sql, вот и хочу командой все это прописать.

Omsk
Тогда присоединяюсь к X11

Цитата:

Ставь не бойся. Нормальная версия. Уже 2 RC

Kursist

Цитата:

Вот ссылка на файл - в нем база данных и клиент с исходниками.
http://files.wyw.ru/3744134

Похоже, ссылка битая. Можно обновить?

Цитата:

Похоже, ссылка битая. Можно обновить?

EugeneBoss3
битая, т.е. уже более полугода прошло! кто ж так долго файлы держать на бесплатном хостинге будет?!!
1) обновить ссылку, скорее всего, может только сам Kursist. попробуйте с ним связаться.
2) насколько я помню, это был какой-то промежуточный результат работы данного господина (причём, судя по всему - с какими огрехами/недоработками/ошибками). оно Вам надо?

Пытаюсь поюзать Вариант использования КЛАДР Андрея Дмитриева для Firebird, но при созании базы выдает ошибку. Коллеги, помогите разрулить ошибки в хранимых процедурах.
[more=код]
/*******************************************************************************
The next statement causes the following error:

Invalid token.
invalid request BLR at offset 356.
Procedure GET_FULLADDRESS_2 is not selectable (it does not contain a SUSPEND statement).
Error while parsing procedure GET_ADDRESS_LIST_2's BLR.
*******************************************************************************/
ALTER PROCEDURE GET_ADDRESS_LIST_2 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID
FROM KLADR_ADDRESS_2 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_2(:ADDRESS_TYPE, :ADR_REG, :ADR_ID)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
END
;

/*******************************************************************************
The next statement causes the following error:

Invalid token.
invalid request BLR at offset 394.
Procedure GET_FULLADDRESS_20 is not selectable (it does not contain a SUSPEND statement).
Error while parsing procedure GET_ADDRESS_LIST_20's BLR.
*******************************************************************************/
ALTER PROCEDURE GET_ADDRESS_LIST_20 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_VERS SMALLINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID, A.ADDRESS_VERSION
FROM KLADR_ADDRESS_20 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID, :ADR_VERS
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_20(:ADDRESS_TYPE, :ADR_REG, :ADR_ID, :ADR_VERS)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
END
;

[/more].

Добавлено:
SERGE_BLIZNUK
Я только начал осваивать Firebird, поэтому изучаю разные варианты работы с серверными базами на примерах.
Цитата:

оно Вам надо?

Возможно нет, если примеры с ошибками.

EugeneBoss3
Нормальным английским языком написано, что

Цитата:

Procedure GET_FULLADDRESS_2 is not selectable (it does not contain a SUSPEND statement)

Т.е. в процедуре GET_FULLADDRESS_2 нет оператора SUSPEND, поэтому она не возвращает данные, следовательно, выборка из нее не получится:

Код:
SELECT SA FROM GET_FULLADDRESS_2(:ADDRESS_TYPE, :ADR_REG, :ADR_ID)

Tantos

Цитата:

в процедуре GET_FULLADDRESS_2 нет оператора SUSPEND

На эту процедуру IBExpert не ругается и SUSPEND в ней есть. Почему ошибка не понятно?
[more=код]
ALTER PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, A.ADDRESS_PAR,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_2 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA)
INTO :SP1, :NOP, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^

ALTER PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOV INTEGER;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NOV = CV;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, D.ADDRESS_PAR, D.ADDRESS_PARVERS,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_20 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
LEFT JOIN KLADR_ADDRESS_20_DEPEND D ON (
D.ADDRESSREGION_ID = A.ADDRESSREGION_ID AND
D.ADDRESS_ID = A.ADDRESS_ID AND
D.ADDRESS_VERSION = A.ADDRESS_VERSION)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA) AND (A.ADDRESS_VERSION = :NOV)
INTO :SP1, :NOP, :NOV, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
--
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^

[/more].

EugeneBoss3

Я Вам уже ответил в теме "Прикладное программирование" - продублирую:


Цитата:

В коде создания этих процедур (который наверняка вот такой: create procedure ... as begin end;) между begin и end вставьте suspend;.

Процедуры - рекурсивные, причем вызывают себя внутри себя не как execute procedure, а как select ... from - то есть, когда Вы делаете alter procedure с таким кодом, на момет попытки его компиляции эти процедуры еще не выполняют suspend. Поэтому - технология такова, что надо сначала создать эти процедуры с одним ключевым словом suspend внутри begin ... end, без остального кода - а только потом делать alter.

...Собственно - а покажите код именно создания этих процедур, то есть - create procedure ..., а не alter procedure ...

Odysseos

Цитата:

покажите код именно создания этих процедур, то есть - create procedure

Привожу полный код создания базы для Firebird
[more=код]
/******************************************************************************/
/*** Generated by IBExpert 2007.02.16 18.03.2007 17:29:26 ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1251;

/**SET CLIENTLIB 'FBCLIENT.DLL';**/

CREATE DATABASE 'LOCALHOST:C:\Data\KLADR.FDB'
USER 'SYSDBA'
PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1251;



/******************************************************************************/
/*** Domains ***/
/******************************************************************************/

CREATE DOMAIN D_ADDRESS_ABBREV_ID AS
SMALLINT
NOT NULL;

CREATE DOMAIN D_ADDRESS_ABBREV_LEVEL AS
SMALLINT
NOT NULL
CHECK ((Value >= 1) and (Value <= 6));

CREATE DOMAIN D_ADDRESS_ABBREV_NAME AS
VARCHAR(40)
NOT NULL;

CREATE DOMAIN D_ADDRESS_ABBREV_POS AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK ((Value >= 0) and (Value <= 2));

CREATE DOMAIN D_ADDRESS_ABBREV_POSA AS
SMALLINT
DEFAULT -1
NOT NULL
CHECK ((Value >= -1) and (Value <=2));

CREATE DOMAIN D_ADDRESS_ABBREV_SHNAME AS
VARCHAR(10)
NOT NULL;

CREATE DOMAIN D_ADDRESS_ACTUAL AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK ((Value = 0) or (Value = 1));

CREATE DOMAIN D_ADDRESS_GNINMB AS
SMALLINT
DEFAULT 0
NOT NULL;

CREATE DOMAIN D_ADDRESS_ID AS
INTEGER
NOT NULL;

CREATE DOMAIN D_ADDRESS_INDEX AS
INTEGER
DEFAULT 0
NOT NULL
CHECK ((Value = 0) or (Value >= 100000 and Value <= 999999));

CREATE DOMAIN D_ADDRESS_LID AS
BIGINT
NOT NULL;

CREATE DOMAIN D_ADDRESS_NAME AS
VARCHAR(40)
NOT NULL;

CREATE DOMAIN D_ADDRESS_REGION AS
SMALLINT
NOT NULL
CHECK ((Value >= 0) and (Value < 100));

CREATE DOMAIN D_ADDRESS_STATUS AS
SMALLINT
DEFAULT 0
NOT NULL;

CREATE DOMAIN D_ADDRESS_UNO AS
SMALLINT
DEFAULT 0
NOT NULL;

CREATE DOMAIN D_ADDRESS_VERSION AS
SMALLINT
DEFAULT 0
NOT NULL;

CREATE DOMAIN D_OCATOREGION_ID AS
INTEGER
NOT NULL
CHECK ((Value >= 0) and (Value < 100));

CREATE DOMAIN D_OCATO_ID AS
INTEGER
NOT NULL
CHECK ((Value >= 0) and (Value < 1000));



/******************************************************************************/
/*** Exceptions ***/
/******************************************************************************/

CREATE EXCEPTION EXC_MANYVALUES 'Список адресов превашает 100. Задайте более строгие критерии поиска.';



SET TERM ^ ;



/******************************************************************************/
/*** Stored Procedures ***/
/******************************************************************************/

CREATE PROCEDURE GET_ADDRESS_LIST_2 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_FULLNAME VARCHAR(500))
AS
BEGIN
EXIT;
END^


CREATE PROCEDURE GET_ADDRESS_LIST_20 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_VERS SMALLINT,
ADR_FULLNAME VARCHAR(500))
AS
BEGIN
EXIT;
END^


CREATE PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
BEGIN
EXIT;
END^


CREATE PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
BEGIN
EXIT;
END^


CREATE PROCEDURE GET_REGION_LIST (
TYPEBASE SMALLINT)
RETURNS (
ADDRESSREGION_ID SMALLINT,
ADDRESS_NAME VARCHAR(100))
AS
BEGIN
EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/*** Tables ***/
/******************************************************************************/



CREATE TABLE KLADR_ABBREV (
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL NOT NULL,
ABBREV_ID D_ADDRESS_ABBREV_ID NOT NULL,
ABBREV_POS D_ADDRESS_ABBREV_POS,
ABBREV_NAME D_ADDRESS_ABBREV_NAME,
ABBREV_SHNAME D_ADDRESS_ABBREV_SHNAME
);

CREATE TABLE KLADR_ADDRESS_2 (
ADDRESSREGION_ID D_ADDRESS_REGION NOT NULL,
ADDRESS_ID D_ADDRESS_LID NOT NULL,
ADDRESS_PAR D_ADDRESS_LID,
ADDRESS_NAME D_ADDRESS_NAME,
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL,
ABBREV_ID D_ADDRESS_ABBREV_ID,
ABBREV_POS D_ADDRESS_ABBREV_POSA,
ADDRESS_UNO D_ADDRESS_UNO,
ADDRESS_GNINMB D_ADDRESS_GNINMB,
ADDRESS_STATUS D_ADDRESS_STATUS,
ADDRESS_INDEX D_ADDRESS_INDEX,
OCATOREGION_ID D_OCATOREGION_ID,
OCATO1_ID D_OCATO_ID,
OCATO2_ID D_OCATO_ID,
OCATO3_ID D_OCATO_ID
);

CREATE TABLE KLADR_ADDRESS_20 (
ADDRESSREGION_ID D_ADDRESS_REGION NOT NULL,
ADDRESS_ID D_ADDRESS_LID NOT NULL,
ADDRESS_VERSION D_ADDRESS_VERSION,
ADDRESS_ACTUAL D_ADDRESS_ACTUAL,
ADDRESS_NAME D_ADDRESS_NAME,
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL,
ABBREV_ID D_ADDRESS_ABBREV_ID,
ABBREV_POS D_ADDRESS_ABBREV_POSA,
ADDRESS_UNO D_ADDRESS_UNO,
ADDRESS_GNINMB D_ADDRESS_GNINMB,
ADDRESS_STATUS D_ADDRESS_STATUS,
ADDRESS_INDEX D_ADDRESS_INDEX,
OCATOREGION_ID D_OCATOREGION_ID,
OCATO1_ID D_OCATO_ID,
OCATO2_ID D_OCATO_ID,
OCATO3_ID D_OCATO_ID
);

CREATE TABLE KLADR_ADDRESS_20_DEPEND (
ADDRESSREGION_ID D_ADDRESS_REGION,
ADDRESS_ID D_ADDRESS_LID,
ADDRESS_VERSION D_ADDRESS_VERSION,
ADDRESS_PAR D_ADDRESS_LID,
ADDRESS_PARVERS D_ADDRESS_VERSION
);



/******************************************************************************/
/*** Views ***/
/******************************************************************************/


/* View: VW_ADDRESS_2 */
CREATE VIEW VW_ADDRESS_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (ADDRESS_ID > 0) and (K.ABBREV_LEVEL < 5)
;



/* View: VW_ADDRESS_20 */
CREATE VIEW VW_ADDRESS_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL < 5) and (D.ADDRESS_ID > 0)
;



/* View: VW_REGIONS_2 */
CREATE VIEW VW_REGIONS_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where K.ADDRESS_ID = 0 and K.ABBREV_LEVEL = 1
;



/* View: VW_REGIONS_20 */
CREATE VIEW VW_REGIONS_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where K.ADDRESS_ID = 0 and K.ABBREV_LEVEL = 1
;



/* View: VW_STREET_2 */
CREATE VIEW VW_STREET_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL = 5)
;



/* View: VW_STREET_20 */
CREATE VIEW VW_STREET_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL = 5)
;




/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/

ALTER TABLE KLADR_ABBREV ADD CONSTRAINT PK_KLADR_ABBREV PRIMARY KEY (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT PK_KLADR_ADDRESS_2 PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID);
ALTER TABLE KLADR_ADDRESS_20 ADD CONSTRAINT PK_KLADR_ADDRESS_20 PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT PK_KLADR_ADDRESS_20_DEPEND PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);


/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/

ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT FK_KLADR_ADDRESS_2_1 FOREIGN KEY (ABBREV_LEVEL, ABBREV_ID) REFERENCES KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT FK_KLADR_ADDRESS_2_2 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_PAR) REFERENCES KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_ID);
ALTER TABLE KLADR_ADDRESS_20 ADD CONSTRAINT FK_KLADR_ADDRESS_20_1 FOREIGN KEY (ABBREV_LEVEL, ABBREV_ID) REFERENCES KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT FK_KLADR_ADDRESS_20_DEPEND_1 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION) REFERENCES KLADR_ADDRESS_20 (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT FK_KLADR_ADDRESS_20_DEPEND_2 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_PAR, ADDRESS_PARVERS) REFERENCES KLADR_ADDRESS_20 (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);


/******************************************************************************/
/*** Indices ***/
/******************************************************************************/

CREATE INDEX KLADR_ADDRESS_2_IDX_1 ON KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_ID, ABBREV_LEVEL);
CREATE INDEX KLADR_ADDRESS_2_IDX_2 ON KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_PAR, ABBREV_LEVEL);


/******************************************************************************/
/*** Stored Procedures ***/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE GET_ADDRESS_LIST_2 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID
FROM KLADR_ADDRESS_2 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_2(:ADDRESS_TYPE, :ADR_REG, :ADR_ID)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
SUSPEND;
END
^

ALTER PROCEDURE GET_ADDRESS_LIST_20 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_VERS SMALLINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID, A.ADDRESS_VERSION
FROM KLADR_ADDRESS_20 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID, :ADR_VERS
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_20(:ADDRESS_TYPE, :ADR_REG, :ADR_ID, :ADR_VERS)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
SUSPEND;
END
^

ALTER PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, A.ADDRESS_PAR,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_2 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA)
INTO :SP1, :NOP, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^

ALTER PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOV INTEGER;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NOV = CV;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, D.ADDRESS_PAR, D.ADDRESS_PARVERS,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_20 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
LEFT JOIN KLADR_ADDRESS_20_DEPEND D ON (
D.ADDRESSREGION_ID = A.ADDRESSREGION_ID AND
D.ADDRESS_ID = A.ADDRESS_ID AND
D.ADDRESS_VERSION = A.ADDRESS_VERSION)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA) AND (A.ADDRESS_VERSION = :NOV)
INTO :SP1, :NOP, :NOV, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
--
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^

ALTER PROCEDURE GET_REGION_LIST (
TYPEBASE SMALLINT)
RETURNS (
ADDRESSREGION_ID SMALLINT,
ADDRESS_NAME VARCHAR(100))
AS
DECLARE VARIABLE POSORIG INTEGER;
DECLARE VARIABLE POSMAIN INTEGER;
DECLARE VARIABLE ADRNAME VARCHAR(40);
DECLARE VARIABLE ABBRNAME VARCHAR(10);
BEGIN
IF (:TYPEBASE = 0) THEN
BEGIN
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_NAME, A.ABBREV_POS, A.ABBREV_MAINPOS, A.ABBREV_SHNAME
FROM VW_REGIONS_2 A
ORDER BY A.ADDRESSREGION_ID
INTO :ADDRESSREGION_ID, :ADRNAME, OSORIG, OSMAIN, :ABBRNAME
DO BEGIN
IF (:ADDRESSREGION_ID < 10) THEN ADDRESS_NAME = '[0' || :ADDRESSREGION_ID || '] ';
ELSE ADDRESS_NAME = '[' || :ADDRESSREGION_ID || '] ';
IF (:POSORIG < 0) THEN POSORIG = OSMAIN;
IF (:POSORIG = 0) THEN ADDRESS_NAME = :ADDRESS_NAME || :ABBRNAME || ' ' || :ADRNAME;
ELSE
IF (:POSORIG = 1) THEN ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME || ' ' || :ABBRNAME;
ELSE ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME;
SUSPEND;
END
END ELSE BEGIN
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_NAME, A.ABBREV_POS, A.ABBREV_MAINPOS, A.ABBREV_SHNAME
FROM VW_REGIONS_20 A
ORDER BY A.ADDRESSREGION_ID
INTO :ADDRESSREGION_ID, :ADRNAME, OSORIG, OSMAIN, :ABBRNAME
DO BEGIN
IF (:ADDRESSREGION_ID < 10) THEN ADDRESS_NAME = '[0' || :ADDRESSREGION_ID || '] ';
ELSE ADDRESS_NAME = '[' || :ADDRESSREGION_ID || '] ';
IF (:POSORIG < 0) THEN POSORIG = OSMAIN;
IF (:POSORIG = 0) THEN ADDRESS_NAME = :ADDRESS_NAME || :ABBRNAME || ' ' || :ADRNAME;
ELSE
IF (:POSORIG = 1) THEN ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME || ' ' || :ABBRNAME;
ELSE ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME;
SUSPEND;
END
END
END
^


SET TERM ; ^

[/more].

EugeneBoss3

Вот этот код:


Код:
CREATE PROCEDURE GET_FULLADDRESS_2 (ADDRESS_TYPE SMALLINT, CR SMALLINT, CA BIGINT)
RETURNS (SA VARCHAR(500))
AS
BEGIN
EXIT;
END^

CREATE PROCEDURE GET_FULLADDRESS_20 (ADDRESS_TYPE SMALLINT, CR SMALLINT, CA BIGINT, CV SMALLINT)
RETURNS (SA VARCHAR(500))
AS
BEGIN
EXIT;
END^

Odysseos
Применил изменения - ошибок не выдает! Спасибо!!!

Добрый день.
Подскажете, пожалуйста, как обновить одно значение

с Interbase не не очень дружен, (как-то страшат меня все begin'ы,обычно общаюсь с mysql, а там такого нету..) а база не моя, как бы не сломать =)

дано таблицы

dogov
sprav_id, stat,adr_id

adress
adr_id, town_id

нужно обновить поле stat с 0 на 1
там где town_id= 200 и sprav_id=131

kolyas
Проапдейтить поле так же само как и в mysql + подзапрос для получения adr_id.

kolyas

А причем тут begin'ы, если это делается одним одинаковым запросом во всех сиквел-серверах?


Код:
update dogov
set stat = 1
where stat = 0
and sprav_id = 131
and adr_id in (select adr_id from adress where town_id = 200)

volser, Odysseos

спасибо..

а про begin'ы.. просто заглянул в процедуры UPDATE которые в базе были а там куча всего .. вот и подумал что все сложнее чем есть..

есть ли у кого русифицированный interbase.msg

Andryshok
А зачем? Эти ошибки читает только программист.

Цитата:

Andryshok
А зачем? Эти ошибки читает только программист.

Ну допустим попытка записи неправильного значения или недопустимого, ведь сервер вернет ошибку, может можно как то узнать что за ошибка и вывести свое сообщение

Andryshok
Обрабатывай ошибки на клиенте.

Цитата:

есть ли у кого русифицированный interbase.msg

вообще на sql.ru проскакивало что в планах firebird есть локализация ошибок, только наверное это не скоро будет.

Andryshok

Если очень надо - можете перевести самостоятельно: в IBExpert'е есть редактор для этого файла (Инструменты - Локализация сообщений Interbase).

Я как-то пытался переводить. После этого моя программа выдавала вместо кириллицы кракозябры. Не стал разбираться почему. Я бросил эту затею.

Цитата:

Я как-то пытался переводить. После этого моя программа выдавала вместо кириллицы кракозябры. Не стал разбираться почему. Я бросил эту затею.

Мда, это верно... Ни у кого не было такой штуки - есть база на Interbase 2009SMP на одном компе ети процедуры работают так, на другом по другому - неправильно, что это за дела ? не могу понять...

Добавлено:
Я имею в виду хранимые процедуры, вот код, все разобрался простыню убрал

Andryshok
читать не возможно, оформи код как положено.

Andryshok
Неправильно это как?

Сорри, нашел ошибку, надо попросить ShIvADeSt чтоб удалил мою мессагу, просто 3 дня бился ....

2Andryshok: Не надо никого просить, нажмите "Редактировать" над своим постом.
А впредь оборачивайте это в теги "code" (чтоб смайлов не было) и "more" (чтоб простыни на полстраницы не висели).