From: äÍÉÔÒÉÊ òÕÓÁÎÏ× (rusanovdc.baikal.ru)
Date: Sat Nov 04 2006 - 05:33:08 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]