General error;-8046 POS(76) Conversion from UNICODE impossible: A300 in recursive query

Sat, 04 Nov 2006 03:32:59 -0800 


CALL PACS.getInvestModel1
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;-8046 POS(74) Conversion from UNICODE impossible: 7FC1
call PACS.getInvestModel1(1,'2Ж1',0)

CALL  PACS.getInvestModel

10  13  МКБ                  type
5   12  Вес                  type
4   12  Рост                 type
1   13  Жалобы               type
9   13  Диагноз              type
2   13  АнамнезЗабалевания   type
8   12  МенструацииСейчас    type
13  0   Гениколог            model
3   7   МенструацииНачались  type
11  13  АнамнезЖизни         group
7   11  Менструации          group
12  13  ОбъективныйСтатус    group

CREATE SCHEMA PACS AUTHORIZATION GIS

//

/*
      12345678901234567890123456789012
 PACS.rtDataObjects

 Таблица хранит имена типов

*/

DROP TABLE PACS.rtDataObjects

//

CREATE TABLE PACS.rtDataObjects
(
   objId                      INTEGER           NOT NULL,

   PRIMARY KEY (objId),

   objType                    VARCHAR(10)       NOT NULL,

   CONSTRAINT objType IN ('type', 'group', 'model'),

   name                       VARCHAR(255),

   revision                   INTEGER           NOT NULL
)

//

/*
      12345678901234567890123456789012
 PACS.rtProtocolModelRevisions
*/

DROP TABLE PACS.rtDataObjectRevisions

//

CREATE TABLE PACS.rtDataObjectRevisions
(
   objId                      INTEGER           NOT NULL,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId),

   revision                   INTEGER           NOT NULL,

   CONSTRAINT unique_revision
       UNIQUE (objId, revision),


   dtFrom                     DATE,
   dtTo                       DATE,
updated TIMESTAMP, /* когда модифицировал*/ modifier VARCHAR(32) /* кто модифицировад, потом сделаем ссылку*/ 
)

//

DROP TABLE PACS.rtObjectHierarchies

//

CREATE TABLE PACS.rtObjectHierarchies
(
   ownerObjId                 INTEGER           NOT NULL,

   FOREIGN KEY (ownerObjId)
   REFERENCES PACS.rtDataObjects (ObjId),

   childObjId                 INTEGER           NOT NULL,

    FOREIGN KEY (childObjId)
   REFERENCES PACS.rtDataObjects (ObjId),


   fromRevision               INTEGER           NOT NULL,
   toRevision                 INTEGER
)

//

/*
      12345678901234567890123456789012
 PACS.rtDataObjectPresentations
*/

DROP TABLE PACS.rtDataObjectPresentations

//

CREATE TABLE PACS.rtDataObjectPresentations
(
   objId                      INTEGER,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId),

   presentation               VARCHAR(255)
)

//

/*
      12345678901234567890123456789012
 PACS.rtResTypes
таблица описывает саму сущность исследования как сейчас она описывается к гис 
*/

DROP TABLE PACS.rtResTypes

//

CREATE TABLE PACS.rtResTypes
(
   resid                      VARCHAR(10)       NOT NULL,

   PRIMARY KEY (resid),

   researchtype               VARCHAR(255)
)

//

DROP TABLE PACS.rtProtocolModels

//

CREATE TABLE PACS.rtProtocolModels
(
   modelId                    INTEGER           NOT NULL,

   PRIMARY KEY (modelId),

 resid                      VARCHAR(10)       NOT NULL,

   FOREIGN KEY (resid)
   REFERENCES PACS.rtResTypes (resid),

   objId                      INTEGER,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId)
)

//

DROP TABLE PACS.pats

//

CREATE TABLE PACS.pats
(
   cartnum                    INTEGER           NOT NULL,

   PRIMARY KEY (cartnum),

   surname                    VARCHAR(255)
)

//

/*
 для совместимости с GIS
*/

DROP TABLE PACS.medorder

//

CREATE TABLE PACS.medorder
(
   medorder                   INTEGER           NOT NULL,

   PRIMARY KEY(medorder),

   cartnum                    INTEGER,

   FOREIGN KEY (cartnum)
   REFERENCES PACS.pats (cartnum)
)

//

DROP TABLE PACS.invest

//

CREATE TABLE PACS.invest
(
   counter                    INTEGER           NOT NULL,

   PRIMARY KEY (counter),

   medorder                   INTEGER           NOT NULL,

   FOREIGN KEY (medorder)
   REFERENCES PACS.medorder (medorder),
resid VARCHAR(10) NOT NULL, 

   FOREIGN KEY (resid)
   REFERENCES PACS.rtResTypes (resid),

   dateres                    DATE,
   timeres                    TIME
)

//

/*
      12345678901234567890123456789012
 PACS.dtProtocols

 аналог таблицы did_doc
*/

DROP TABLE PACS.dtProtocols

//

CREATE TABLE PACS.dtProtocols
(
   revision                   INTEGER           NOT NULL,


   counter                    INTEGER           NOT NULL,

   FOREIGN KEY (counter)
   REFERENCES PACS.invest (counter),

 resid                      VARCHAR(10)       NOT NULL,

   FOREIGN KEY (resid)
   REFERENCES PACS.rtResTypes (resid),

 resnum                     INTEGER           NOT NULL,

   CONSTRAINT unique_protocols
   UNIQUE (counter, resid,resnum),

 /*
 */

   modelId                    INTEGER,

   FOREIGN KEY (modelId)
   REFERENCES PACS.rtProtocolModels (modelId)
)

//

DROP TABLE PACS.dtProtocolRevisions

//

CREATE TABLE PACS.dtProtocolRevisions
(
 counter                    INTEGER           NOT NULL,
 resid                      VARCHAR(10)       NOT NULL,
 resnum                     INTEGER           NOT NULL,


   FOREIGN KEY (counter,resid,resnum)
   REFERENCES PACS.dtProtocols (counter,resid,resnum),
revision INTEGER, 

   CONSTRAINT unique_revision
       UNIQUE (counter,resid,resnum, revision),
updated TIMESTAMP, /* когда модифицировал*/ modifier VARCHAR(32) /* кто модифицировад, потом сделаем ссылку*/ 
)

/*
      12345678901234567890123456789012
 PACS.rtObjectFields

 список полей Тип-объекта, их тип и имена.
*/

//

DROP TABLE PACS.rtObjectFields

//

CREATE TABLE PACS.rtObjectFields
(
   objId                      INTEGER,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId),
type VARCHAR(20) NOT NULL, 
 label                     VARCHAR(255),
 fieldName              VARCHAR(255)       NOT NULL

)

/*
      12345678901234567890123456789012
 PACS.rdData_4

 Таблица с данными типа objId=4

*/

//

DROP TABLE PACS.rdData_4

//

CREATE TABLE PACS.rdData_4
(
   objId                      INTEGER,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId),
counter INTEGER NOT NULL, 
     resid                      VARCHAR(10)       NOT NULL,
     resnum                     INTEGER           NOT NULL,
   FOREIGN KEY (counter,resid,resnum)
   REFERENCES PACS.dtProtocols (counter,resid,resnum),
height FLOAT(2), 
   fromRevision           INTEGER           NOT NULL,
   toRevision               INTEGER


)

/*
      12345678901234567890123456789012
 PACS.rtTypeProperties

 Атрибуты Типов.
*/

//

DROP TABLE PACS.rtTypeProperties

//

CREATE TABLE PACS.rtTypeProperties
(
   objId                      INTEGER,

   FOREIGN KEY (objId)
   REFERENCES PACS.rtDataObjects (objId),
tableName VARCHAR(255) NOT NULL 

)

//

--СОЗДАЕМ МОДЕЛЬ
insert into PACS.rtDataObjects (objId, objType, name, revision) values (1, 'type', 'Жалобы', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (1, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (2, 'type', 'АнамнезЗабалевания', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (2, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (3, 'type', 'МенструацииНачались', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (3, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (4, 'type', 'Рост', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (4, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (5, 'type', 'Вес', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (5, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (6, 'type', 'Давление', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (6, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (7, 'group', 'Менструации', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (7, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (8, 'type', 'МенструацииСейчас', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (8, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (9, 'type', 'Диагноз', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (9, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (10, 'type', 'МКБ', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (10, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (11, 'group', 'АнамнезЖизни', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (11, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (12, 'group', 'ОбъективныйСтатус', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (12, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (13, 'model', 'Гениколог', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (13, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (14, 'model', 'Гастраэнтеролог', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (14, 1)
//
insert into PACS.rtDataObjects (objId, objType, name, revision) values (15, 'model', 'Патаморфолог', 1) 
//
insert PACS.rtDataObjectRevisions(objId, revision) values (15, 1)
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (7, 3, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (11, 7, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (12, 4, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (12, 5, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (12, 8, 1, null) 

//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 1, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 2, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 11, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 12, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 9, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (13, 10, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 1, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 2, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 11, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 12, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 9, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (14, 10, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (15, 11, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (15, 9, 1, null) 
//
insert PACS.rtObjectHierarchies (ownerObjId, childObjId, fromRevision, toRevision) values (15, 10, 1, null) 
//


insert PACS.rtResTypes (resid, researchtype) values ('2Ж1', 'Услуга 1')
//
insert PACS.rtResTypes (resid, researchtype) values ('2Ж2', 'Услуга 2')
//
insert PACS.rtResTypes (resid, researchtype) values ('2Ж3', 'Услуга 3')
//

insert PACS.rtProtocolModels (modelId, resid, objId) values (1, '2Ж1', 13)
//
insert PACS.rtProtocolModels (modelId, resid, objId) values (2, '2Ж2', 14)
//
insert PACS.rtProtocolModels (modelId, resid, objId) values (3, '2Ж3', 15)
//

insert PACS.pats (cartnum, surname) values (6280, 'Я')
//
insert PACS.pats (cartnum, surname) values (6281, 'Ты')
//


insert PACS.medorder (medorder, cartnum) values (1, 6280)
//
insert PACS.medorder (medorder, cartnum) values (2, 6280)
//
insert PACS.medorder (medorder, cartnum) values (3, 6281)
//
insert PACS.medorder (medorder, cartnum) values (4, 6281)
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (1, 1, '2Ж1', null, null) 
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (2, 1, '2Ж2', null, null) 
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (3, 1, '2Ж3', null, null) 
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (4, 4, '2Ж1', null, null) 
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (5, 4, '2Ж2', null, null) 
//
insert PACS.invest (counter, medorder, resid, dateres, timeres) values (6, 4, '2Ж3', null, null) 
//


insert PACS.rtTypeProperties(objId,tableName) values (4,'rdData_4')
//
insert PACS.rtObjectFields(objId,type,label,fieldName) values (4,'float','height','height') 
//
insert PACS.rtTypeProperties(objId,tableName) values (5,'rdData_5')
//
insert PACS.rtObjectFields(objId,type,label,fieldName) values (5,'float','weight','weight') 
//
insert PACS.rtTypeProperties(objId,tableName) values (6,'rdData_6')
//
insert PACS.rtObjectFields(objId,type,label,fieldName) values (6,'float','pressure','pressure') 
//
insert PACS.dtProtocols(revision,counter,resid,resnum,modelId) values (1,1,'2Ж1',0,1) 
//
insert PACS.rdData_4(objId,counter,resid,resnum,height,fromRevision,toRevision) values (4,1,'2Ж1',0,180,1,null) 
//
--insert PACS.rdData_5(objId,counter,resid,resnum,height,fromRevision,toRevision) values (5,1,'2Ж1',0,80,1,null) 
--//

DROP FUNCTION PACS.getRevision
//

CREATE FUNCTION PACS.getRevision(objId INT, qdate DATE)

RETURNS INT
AS

VAR result INT;

BEGIN
TRY IF qdate IS NULL THEN SET qdate=DATE; DECLARE curS CURSOR FOR SELECT revision FROM pacs.rtDataObjectRevisions WHERE DECODE(dtfrom,null,'1970-01-01',dtfrom)<=:qdate and :qdate <DECODE(dtto,null,'2100-12-31',dtto) and objId=:objId; FETCH curS INTO :result; CLOSE curS; RETURN result; CATCH 
                    IF $rc <> 100 THEN BEGIN
                        CLOSE curS;
                        STOP($rc,$errmsg);
                   END;
END;

//

DROP DBPROC PACS.getInvestModel1
//
CREATE DBPROC PACS.getInvestModel1
 ( IN counter INT,
   IN resid VARCHAR(10),
   IN resnum INT
 )
RETURNS CURSOR AS 

VAR objId INT; RevisionDate DATE;
BEGIN
TRY SELECT m.objId,i.dateres INTO :objId,:RevisionDate FROM PACS.dtProtocols p,PACS.invest i, PACS.rtProtocolModels m WHERE p.counter = :counter AND p.resid=:resid AND p.resnum=:resnum AND p.modelId = m.modelId AND p.counter=i.counter; DECLARE Model CURSOR FOR 
               WITH RECURSIVE Nodes (ownerObjId, childObjId)
                   AS
                   (
                   SELECT ownerObjId, childObjId
                   FROM PACS.rtObjectHierarchies
                   WHERE ownerObjId=:objId AND
DECODE(fromRevision,null,1,fromRevision)<= PACS.getRevision(ownerObjId,:RevisionDate) AND PACS.getRevision(ownerObjId,:RevisionDate) < DECODE(toRevision,null,PACS.getRevision(ownerObjId,:RevisionDate)+1,toRevision) 
                       UNION ALL
                   SELECT a.ownerObjId,a.childObjId
                   FROM PACS.rtObjectHierarchies a,Nodes
                   WHERE a.ownerObjId=Nodes.childObjId AND
DECODE(a.fromRevision,null,1,a.fromRevision)<= PACS.getRevision(a.ownerObjId,:RevisionDate) AND PACS.getRevision(a.ownerObjId,:RevisionDate) < DECODE(a.toRevision,null,PACS.getRevision(a.ownerObjId,:RevisionDate)+1,a.toRevision) 
                   )
SELECT childObjId as id, ownerObjId as parent FROM Nodes FOR REUSE; $CURSOR = 'MY_CURSOR'; 
       DECLARE :$CURSOR CURSOR FOR
           SELECT a.Id, a.parent, b.name,b.objtype
       FROM
         GIS.Model a,
         PACS.rtDataObjects b
       WHERE
         a.id = b.objId
            UNION ALL
       SELECT :objId as id, 0 as parent,d.name,d.objtype
       FROM
           dbadmin.dual c,
           PACS.rtDataObjects d
       WHERE
           d.objId=:objId;

   CATCH STOP($rc,$errmsg);
END;

//

DROP DBPROC PACS.getInvestModel
//
CREATE DBPROC PACS.getInvestModel
 ( IN counter INT,
   IN resid VARCHAR(10),
   IN resnum INT
 )
RETURNS CURSOR AS 

VAR objId INT; RevisionDate DATE;
BEGIN
TRY SELECT m.objId,i.dateres INTO :objId,:RevisionDate FROM PACS.dtProtocols p,PACS.invest i, PACS.rtProtocolModels m WHERE p.counter = :counter AND p.resid=:resid AND p.resnum=:resnum AND p.modelId = m.modelId AND p.counter=i.counter; DECLARE Model CURSOR FOR 
               WITH RECURSIVE Nodes (ownerObjId, childObjId)
                   AS
                   (
                   SELECT ownerObjId, childObjId
                   FROM PACS.rtObjectHierarchies
                   WHERE ownerObjId=:objId AND
DECODE(fromRevision,null,1,fromRevision)<= 1 AND 1 < DECODE(toRevision,null, 1+1,toRevision) 
                       UNION ALL
                   SELECT a.ownerObjId,a.childObjId
                   FROM PACS.rtObjectHierarchies a,Nodes
                   WHERE a.ownerObjId=Nodes.childObjId AND
DECODE(a.fromRevision,null,1,a.fromRevision)<= 1 AND 1 < DECODE(a.toRevision,null,1+1,a.toRevision) 
                   )
SELECT childObjId as id, ownerObjId as parent FROM Nodes FOR REUSE; $CURSOR = 'MY_CURSOR'; 
       DECLARE :$CURSOR CURSOR FOR
           SELECT a.Id, a.parent, b.name,b.objtype
       FROM
         GIS.Model a,
         PACS.rtDataObjects b
       WHERE
         a.id = b.objId
            UNION ALL
       SELECT :objId as id, 0 as parent,d.name,d.objtype
       FROM
           dbadmin.dual c,
           PACS.rtDataObjects d
       WHERE
           d.objId=:objId;

   CATCH STOP($rc,$errmsg);
END;



--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to