dbdeal.4gl

开发工具 Informix-4gl

#@RETURN lv_result
#-/
##############################################################
FUNCTION syno_create(iv_projectid)
#/--@PARAMETER
DEFINE  iv_projectid       CHAR(30)
#-/
DEFINE  lv_where_part    CHAR(500),
        lv_answer        CHAR(1),
        lv_errtxt        CHAR(80),
        lv_result        SMALLINT
   
    INITIALIZE lv_where_part,lv_answer,lv_errtxt TO NULL
    LET lv_result = 0

    CASE iv_projectid
        WHEN "_UTILITYDB"
              LET lv_where_part ="tabid >= 100 AND ",
                               "(tabtype =\"T\" OR tabtype =\"V\")",
                               " AND tabname <> \"prpversion\""
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_UTILITYDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF
        WHEN "_PRPREINSDB"
              LET lv_where_part ="tabtype =\"T\" AND ",
                                  "tabname in (\"reinscmain\",\"reinscmain1\",",
                                        "\"reinspmain\",\"reinspmain1\",",
                                        "\"reinslnopaid\",\"reinslnopaid1\",",
                                        "\"reinslpaid\",\"reinslpaid1\")"

              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_PRPREINSDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF
        WHEN "_REINSDB"
              LET lv_where_part ="tabtype =\"T\" AND ",
                                 "tabname in (\"fcopolicy\",\"fcoitem\",",
                                      "\"fcotot\",\"fcoclause\",\"fcocoins\",",
                                      "\"fsoplan\",\"fpoendor\",\"fpoitem\",",
                                      "\"fpotot\",\"fpoclause\",\"fpocoins\",",
                                      "\"flopay\",\"floitem\",\"flotot\",",
                                      "\"fhplan\",\"fxloadplan\",",
                                      "\"flopersonloss\",\"flocharge\",",
                                      "\"fcocar\")"
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_REINSDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF
        WHEN "_PRPSTATDB"
              LET lv_where_part ="tabtype =\"T\" AND tabid >= 100"

              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_PRPSTATDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF

        WHEN "_VISADB"
              LET lv_where_part ="tabtype =\"T\" AND ",
                                 "tabname in ",
                                 " (\"vscode\",\"vsmark\",\"vscoderisk\")"
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_VISADB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF

        WHEN "_SFFDB"
              LET lv_where_part ="tabtype=\"T\" AND ",
                                 "tabname in (\"sf01p\",\"sf01pf1\",\"sf02p\",",
                                             "\"sf01\",\"sf01f1\",\"sf02\",",
                                             "\"sf02f1\",\"sf02pf\")"
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_SFFDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF

        WHEN "_WORKFLOWDB"
              LET lv_where_part ="tabid >= 100 AND ",
                                 "(tabtype =\"T\" OR tabtype =\"V\")"

              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_WORKFLOWDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF

        WHEN "_UWDB"
              LET lv_where_part ="tabid >= 100 AND ",
                                 "(tabtype =\"T\" OR tabtype =\"V\")",
                                 " AND tabname <> \"prpversion\""
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_UWDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF

        WHEN "_LTRANSDB"
              LET lv_where_part ="tabid >= 100 AND ",
                                 "(tabtype =\"T\" OR tabtype =\"V\")",
                                 " AND tabname <> \"prpversion\""
              CALL syno_create1(iv_projectid,lv_where_part)
                   RETURNING lv_result
              IF lv_result = 0 THEN
              ELSE
                  PROMPT "$_PRPINSDB到$_LTRANSDB库建立同义词失败,错误号:",
                       lv_result CLIPPED,",按回车继续"
                      FOR lv_answer
              END IF
    END CASE

END FUNCTION

##############################################################
#/--@METHOD
#@DESC creat synonym
#@RETURN lv_result
#-/
##############################################################
FUNCTION syno_create1(iv_dbname,iv_where_part)
#/--@PARAMETER
DEFINE  iv_dbname       CHAR(30)
DEFINE  iv_where_part    CHAR(300)
#-/
DEFINE lv_sourdb        CHAR(40),
       lv_objdb         CHAR(18)
 
DEFINE lv_tabname       CHAR(18)
DEFINE lv_sqltxt        CHAR(500)
DEFINE lv_distxt        CHAR(80)
DEFINE lv_errtxt        CHAR(100)

    INITIALIZE lv_sourdb,lv_objdb,lv_tabname,lv_sqltxt,
               lv_distxt,lv_errtxt TO NULL
    LET lv_objdb=fgl_getenv("_PRPINSDB")
    LET lv_sourdb=fgl_getenv(iv_dbname)
    
    WHENEVER ERROR CONTINUE

    DATABASE lv_sourdb
    IF SQLCA.SQLCODE = 0 THEN
    ELSE
        RETURN SQLCA.SQLCODE
    END IF

    DATABASE lv_objdb
    IF SQLCA.SQLCODE = 0 THEN
    ELSE
        RETURN SQLCA.SQLCODE
    END IF

    CLEAR SCREEN
    DISPLAY "正在创建 ",lv_sourdb CLIPPED," 到 ",
             lv_objdb CLIPPED," 的同义词..." AT 12,12
    SLEEP 1

    #--查询同义名,CREATE 所有同义名表
    INITIALIZE lv_sqltxt TO NULL
    INITIALIZE lv_tabname TO NULL
    LET lv_sqltxt = "SELECT tabname FROM ",
                       lv_sourdb CLIPPED,":systables WHERE ",
                       iv_where_part CLIPPED

    PREPARE pre_crt_syno FROM lv_sqltxt
    IF SQLCA.SQLCODE = 0
    THEN
    ELSE
        DISPLAY "                                                                     "
        AT 12,12
        INITIALIZE lv_errtxt TO NULL
        LET lv_errtxt="创建",lv_sourdb CLIPPED,":",lv_tabname CLIPPED,
             "到",lv_objdb CLIPPED,"同义词失败!"
        CALL errorlog(lv_errtxt)
        RETURN SQLCA.SQLCODE
    END IF

    DECLARE curr_crt_syno CURSOR FOR pre_crt_syno
    FOREACH curr_crt_syno INTO lv_tabname

       INITIALIZE lv_distxt TO NULL

       LET lv_distxt = "CREATE SYNONYM ",lv_tabname CLIPPED,
                         " FOR ",
                         lv_sourdb CLIPPED,":",lv_tabname CLIPPED

         PREPARE pre_syno_create FROM lv_distxt
         EXECUTE pre_syno_create
         -------------------------------------------------------
         # 2002-12-18 liangna add,增加对建同义词失败的判断 
         IF SQLCA.SQLCODE = 0
         THEN
         ELSE
             DISPLAY "                                                                "
             AT 12,12
             RETURN SQLCA.SQLCODE 
         END IF
         -------------------------------------------------------

    END FOREACH

    WHENEVER ERROR STOP

    DISPLAY "创建 ",lv_sourdb CLIPPED," 到 ",
             lv_objdb CLIPPED," 的同义词结束..." AT 12,12
    SLEEP 1
    DISPLAY "                                                                "
        AT 12,12

    RETURN 0

END FUNCTION


##########################################################################
#/--@METHOD
#@DESC to rename the old database and create the new database
#@RETURN SMALLINT 
#-/
##########################################################################
FUNCTION prpdb_change(iv_productid)
#/--@PARAMETER
DEFINE iv_productid CHAR(30)
#-/
DEFINE lv_dbname char(18)
DEFINE lv_tmpdbname char(18)
DEFINE lv_result SMALLINT
DEFINE lv_execsql CHAR(100)

    INITIALIZE lv_dbname TO NULL

    LET lv_dbname=fgl_getenv(iv_productid)
    LET lv_result=0

    WHENEVER ERROR CONTINUE
    DISPLAY lv_dbname
    #--在重命名业务库时删除其同义名
    CALL syno_drop_all(iv_productid) RETURNING lv_result 

    Let lv_execsql = "CLOSE DATABASE "
    PREPARE pre_closeexec FROM lv_execsql
    EXECUTE pre_closeexec

    #--将原有业务数据库重命名
    Let lv_execsql = "RENAME DATABASE ", lv_dbname CLIPPED," TO prpdb151"
    PREPARE pre_renameexec FROM lv_execsql
    EXECUTE pre_renameexec

    IF SQLCA.SQLCODE != 0  THEN
        ERROR "您在将",lv_dbname CLIPPED,
              "重命名为prpdb151过程出错,错误号:",SQLCA.SQLCODE
        SLEEP 2
        RETURN 1
    END IF

    WHENEVER ERROR STOP

    CALL db_create("_PRPINSDB") RETURNING lv_result
    IF SQLCA.SQLCODE != 0  THEN
        ERROR "创建业务数据库失败"
        SLEEP 2
        RETURN 1
    END IF

    RETURN 0

END FUNCTION

##########################################################################
#/--@METHOD
#@DESC to rename the old database and create the new database
#@RETURN SMALLINT 
#-/
##########################################################################
FUNCTION prpdb_change_false(iv_productid)
#/--@PARAMETER
DEFINE iv_productid CHAR(30)
#-/
DEFINE lv_dbname char(18)
DEFINE lv_tmpdbname char(18)
DEFINE lv_result SMALLINT
DEFINE lv_execsql CHAR(100)

    INITIALIZE lv_dbname TO NULL

    LET lv_dbname=fgl_getenv(iv_productid)
    LET lv_result=0

    WHENEVER ERROR CONTINUE
    DISPLAY lv_dbname
    Let lv_execsql = "CLOSE DATABASE "
    PREPARE pre_closeexec2 FROM lv_execsql
    EXECUTE pre_closeexec2

    #--将原有业务数据库重命名
    Let lv_execsql = "RENAME DATABASE ", lv_dbname CLIPPED," TO prpdb_fal"
    PREPARE pre_renameexec3 FROM lv_execsql
    EXECUTE pre_renameexec3

    IF SQLCA.SQLCODE != 0  THEN
        ERROR "您在将",lv_dbname CLIPPED,
              "重命名为prpdb_fal过程出错,错误号:",SQLCA.SQLCODE
        SLEEP 2
        RETURN 
    END IF

    #--将原有数据库重新命名
    Let lv_execsql = "RENAME DATABASE prpdb151 TO ",lv_dbname CLIPPED
    PREPARE pre_renameexec2 FROM lv_execsql
    EXECUTE pre_renameexec2

    IF SQLCA.SQLCODE != 0  THEN
        ERROR "您在将",lv_dbname CLIPPED,
              "重命名为新建业务数据库过程出错,错误号:",SQLCA.SQLCODE
        SLEEP 2
        RETURN 
    END IF

    WHENEVER ERROR STOP

    RETURN 

END FUNCTION