Intersystem : sql使用

使用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
*/
}