プログラムによる SQL ゲートウェイの使用法

ClassMethod FetchSamples()
{
   #include %occInclude
   //Create new SQL Gateway connection object
   set gc=##class(%SQLGatewayConnection).%New()
   if gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")

   //Make connection to target DSN
   set pDSN="Cache Samples"
   set usr="_system"
   set pwd="SYS"
   set sc=gc.Connect(pDSN,usr,pwd,0)
   if $$$ISERR(sc) quit sc
   if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")

   set sc=gc.AllocateStatement(.hstmt)
   if $$$ISERR(sc) quit sc

   //Prepare statement for execution
   set pQuery= "select * from Sample.Person"
   set sc=gc.Prepare(hstmt,pQuery)
   if $$$ISERR(sc) quit sc
   //Execute statement
   set sc=gc.Execute(hstmt)
   if $$$ISERR(sc) quit sc
   //Get list of columns returned by query
   set sc=gc.DescribeColumns(hstmt, .columnlist)
   if $$$ISERR(sc) quit sc

   //display column headers delimited by ":"
   set numcols=$listlength(columnlist)-1  //get number of columns
   for colnum=2:1:numcols+1 {
      Write $listget($listget(columnlist,colnum),1),":"
   }
   write !

   //Return first 200 rows
   set sc=gc.Fetch(hstmt)
   if $$$ISERR(sc) quit sc
   set rownum=1
   while((gc.sqlcode'=100) && (rownum<=200)) {
      for ii=1:1:numcols {
         set sc=gc.GetData(hstmt, ii, 1, .val)
         write " "_val
         if $$$ISERR(sc) break
      }
      set rownum=rownum+1
      write !
      set sc=gc.Fetch(hstmt)
      if $$$ISERR(sc) break
   }

   //Close cursor and then disconnect
   set sc=gc.CloseCursor(hstmt)
   if $$$ISERR(sc) quit sc

   set sc=gc.Disconnect()
   quit sc
}

ClassMethod SelectAndWrite() as %Status
{
    Set conn=##class(%SQLGatewayConnection).%New()
    Set sc=conn.Connect("AccessPlayground","","")
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set res=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
    Set sc=res.Prepare("SELECT * FROM PEOPLE",,conn)
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set sc=res.Execute()
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    While res.Next()
    { Write !,res.GetData(1)," ",res.GetData(2)," ",res.GetData(3)
    }
    Set sc=conn.Disconnect()
    Quit sc
}

ClassMethod ExecuteQuery(mTable As %String)
{
   set mDSN="DSNtest"
   set mUsrName="SYSDBA"
   set mUsrPwd="masterkey"

   // Create an instance and connect
   set gateway=##class(%SQLGatewayConnection).%New()
   set status=gateway.Connect(mDSN,mUsrName,mUsrPwd)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   set hstmt=""

   // Allocate a statement
  set status=gateway.AllocateStatement(.hstmt)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()

   // Use %SQLGatewayConnection to call ODBC query functions directly
   set status=gateway.Prepare(hstmt,"SELECT * FROM "_mTable)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   set status=gateway.Execute(hstmt)

   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   quit gateway.Disconnect()
}