Intersystem : sql使用

- 日理万妓 2023-06-25 08:25 38阅读 0赞

### 文章目录 ###

*  使用jdbc:
 *   *  事务回滚
 *  Cache Sql
 *   *  1, Embedded SQL : &sql( sqlstr)
     *  2, Dynamic SQL: %SQL.Statement

# 使用jdbc: #

*  EnsLib.SQL.OutboundAdapter: 配置BO的（java gateway 和sql数据源）

## 事务回滚 ##

Method tx(pRequest As sql2.demo.MSG.Req, Output pResponse As sql2.demo.MSG.Resp1) As %Status
    {
      #Include %occStatus
      try {
        //initialize variables and pResponseects
        set tSC = $$$OK
        set pResponse = ##class(sql2.demo.MSG.Resp1).%New()
    
        #;开始事务
        set tSC = ..Adapter.SetAutoCommit(0)
        $$$ThrowOnError(tSC)
    
        //执行sql1
        set tQueryIns="insert into per (ID, name) values (?,?)"        
        set tSC = ..Adapter.ExecuteUpdate(.tAffectedRows,tQueryIns, 100,"name100")
        $$$ThrowOnError(tSC)
        
        //出现异常
        set res= 1/0
        
        //执行sql2
        set tQueryIns="insert into per (ID, name) values (?,?)"        
        set tSC = ..Adapter.ExecuteUpdate(.tAffectedRows,tQueryIns, 101,"name101")
        $$$ThrowOnError(tSC)
      
        //结束事务
        set tSC=..Adapter.Commit()
        set pResponse.msg= "200"
        $$$ThrowOnError(tSC)
      }
      catch err{
        if (err.%ClassName(1)="common.err.exception") && ($$$ISERR(err.status)) {
          set tSC = err.status
        }
        else {
          set tSC =
          $system.Status.Error(err.Code,err.Name,err.Location,err.InnerException)
        }
         
        set pResponse.msg= "500"
         //异常时，事务回滚
        do ..Adapter.Rollback()
      }
      Quit $$$OK
    }

sql执行的状态码：SQLCODE

*  If SQLCODE=0 the query completed successfully and returned data. The output host variables contain field values.
 *  If SQLCODE=100 the query completed successfully, but output host variable values may differ. Either:

# Cache Sql #

## 1, Embedded SQL : &sql( sqlstr) ##

Class sql2.demo.EmbSql Extends %Persistent
    {
    
    /// 1,执行sql查询: 返回单个值
    ClassMethod t1() As %Integer
    {
    
        &sql(SELECT COUNT(*) INTO :count FROM sql_demo.Patient)
    	w "count="_count
        Quit count
        
       /*
    JDBC2>d ##class(sql2.demo.EmbSql).t1()
    count=3
    */
    }
    
    ///2, 查询多个数据
    ClassMethod t2() As %Status
    {
      /* &sql(SELECT PatientName,TelephoneNumber INTO :name,:tel 
           FROM sql_demo.Patient
           )
         WRITE name," tel=",tel
      */
      
     &sql(DECLARE C1 CURSOR FOR
        SELECT TelephoneNumber,PatientName
        INTO :id, :name
        FROM sql_demo.Patient
        ORDER BY PatientName
     )
    
     &sql(OPEN C1)
          QUIT:(SQLCODE'=0)
     &sql(FETCH C1)
     
     While (SQLCODE = 0) {
         Write id, ":  ", name,!        
        &sql(FETCH C1)
     }
        
     &sql(CLOSE C1)
     /*
    JDBC2>d ##class(sql2.demo.EmbSql).t4()
    3845798354:  Lisi2
    4354546567567:  Maliu
    847584574358735:  zhouqi
    */
    }
    }

## 2, Dynamic SQL: %SQL.Statement ##

*  statement: %Prepare(), %Execute()
 *  statement: %ExecDirect()

Class sql2.demo.DynamicSql Extends %Persistent
    {
    ///
    ClassMethod t1()
    {
      SET myquery = 2
      SET myquery(1) = "SELECT %ID AS id,pid, PatientName, Address"
      SET myquery(2) = "FROM sql_demo.Patient"
      SET myquery(3) = "ORDER BY 2"
      
      SET tStatement = ##class(%SQL.Statement).%New()
      SET qStatus = tStatement.%Prepare(.myquery)
      set res=tStatement.%Execute()
      //d res.%Display()
      
       WHILE res.%Next() {
         //DO res.%Print()
         w res.%Get("pid"),","
         w res.%Get("PatientName"),","
         w res.%Get("Address")
         w !
       } 
      
      /*
    JDBC2>d ##class(sql2.demo.DynamicSql).t1()
    id      pid     PatientName     Address
    1       25      Lisi2   wi8dyfs8df
    2       27      Maliu   dkfhjsv wjhsd  asd
    3       28      zhouqi  lkjlds sdfsea esd sa
    */
    }
    ///
    ClassMethod t2()
    {
      ZNSPACE "SAMPLES"
      SET tStatement = ##class(%SQL.Statement).%New()
      SET tStatement.%SchemaPath = "MyTests,Sample,Cinema"
      
      //定义参数：字段值
      SET myquery=2
      SET myquery(1)="SELECT Name,DOB,Age FROM Person"
      SET myquery(2)="WHERE Age > ? AND Age < ? ORDER BY Age"
      
      SET qStatus = tStatement.%Prepare(.myquery)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
      //设置参数： 字段值
      SET rset = tStatement.%Execute(21,26)
      WRITE !,"Execute OK: SQLCODE=",rset.%SQLCODE,!!
      DO rset.%Display()
      WRITE !,"End of data: SQLCODE=",rset.%SQLCODE
      
      /* 
    JDBC2>d ##class(sql2.demo.DynamicSql).t2()
    Execute OK: SQLCODE=0
     
    Name    DOB     Age
    Koivu,Wilma K.  57032   22
    Vivaldi,Natasha E.      57165   22
    Wijnschenk,James J.     57198   22
     
    3 Rows(s) Affected
    End of data: SQLCODE=100
    */
    }
    ///
    ClassMethod t3()
    {
      ZNSPACE "SAMPLES"
      SET myquery=2
      SET myquery(1)="SELECT Name,Age FROM Sample.Person"
      SET myquery(2)="WHERE Age > ? AND Age < ? ORDER BY Age"
      SET rset = ##class(%SQL.Statement).%ExecDirect(,.myquery,12,20)
        IF rset.%SQLCODE'=0 {WRITE !,"1st ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message  QUIT}
      DO rset.%Display()
      WRITE !,"End of teen data",!!
      
     /*
    JDBC2>d ##class(sql2.demo.DynamicSql).t3()
    Name    Age
    Eastman,Peter G.        14
    Noodleman,Emilio H.     16
    Lee,Marvin B.   19
    3 Rows(s) Affected
    End of teen data
    */
    }