解决高亮度显示内容问题（onFocus)

oracle和sqlserver互訪

by HuiYi_Love from ITPUB

oracle和sqlserver互訪!

前几天由於工作的原因查找了oracle中查找sqlserver數据的資料,現測試成功,整理一下貼出!

要求:從Oracle中能取SqlServer的數据
環境:
OracleDb: Linux + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.52(TOPPROD)
MSQLDB: Windows2000 + SqlServer2000 IP:192.168.1.50(ERPSQL),測試用戶:sa/pass 測試數据表:EK.ACPTA
网關: WindowsXp + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221(S0504027),因為暫在測試階段,所以网關裝在我用的机器,网關可以裝在MSQLDB上

1.通過ODBC通用方式聯接

代码:

// A. 安裝HS部件

//     默認情況下HS部件是安裝的,查詢視圖 SYS.HS_BASE_CAPS 可得出有沒有安裝此部件!

// B. 配置ODBC

//     在"系統DNS"中配置"ODBC FOR SQLSERVER",例如:[ERPSQL]

// C. 配置TNSNAMES.ORA,路徑:ORACLE_HOME\NETWORK\ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!

Lnk2sql =                  # tnsName

  (DESCRIPTION = 

    (ADDRESS_LIST = 

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))     # 网關IP

     )

    (CONNECT_DATA = 

        (SID = hs4sql)    #SID,要和監听器裡的SID一致!

    ) 

    (HS=OK)

// D. 配置listener.ora,路徑:ORACLE_HOME\NETWORK\ADMIN

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

      )

    )

  )


SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =         # 這一段為加入的

        (SID_NAME = hs4sql)

        (ORACLE_HOME = D:oracleora9i)

        (PROGRAM = hsodbc)   # 要使用的HS服務程序.

    ) 

  )

// E. 重啟監听器服務

// F. 編輯ORACLE_HOME\HS\ADMIN內init.ora，這裡是iniths4sql.ora,因為上面的SID=hs4sql

HS_FDS_CONNECT_INFO = ERPSQL     # B中設置的ODBC名稱

HS_FDS_TRACE_LEVEL = 0

// G. 創建DB LINK,以及測試

SQL>  create database link ora2sql connect to "sa" identified by "pass" using 'Lnk2sql';


Database link created


SQL> select ta001,ta002 from acpta@ora2sql where rownum<5;


TA001 TA002

----- -----------

S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

------------

2.通過"透明网關"方式聯接

代码:

// A. 安裝透明网關,在安裝時選擇自定義安裝,安裝TRANSPARENT GATEWAY FOR SQLSERVER 組件,安裝成功後會產生oracle_homeora90\tg4msql目錄!

// B. 配置TNSNAMES.ORA,路徑:ORACLE_HOME\NETWORK\ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!

TG4MSQL =           # tnsName

    (DESCRIPTION =

      (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))     # 网關IP

      )

      (CONNECT_DATA =

        (SID = tg4msql )  #SID,要和監听器裡的SID一致!

      )

    (HS=OK) 

    )

// C. 配置listener.ora,路徑:ORACLE_HOME\NETWORK\ADMIN

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

      )

    )

  )


SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = tg4msql)

      (SID_NAME = tg4msql)

      (ORACLE_HOME = D:oracleora9i)

      (PROGRAM= tg4msql)

    )

  )

// D. 重啟監听器服務

// E. 編輯ORACLE_HOME\TG4MSQL\ADMIN內init.ora，這裡是inittg4msql.ora,因為上面的SID=tg4msql

#HS_FDS_CONNECT_INFO="SERVER=ERPSQL;DATABASE=EK",好多人說用這行可以,我用這行的時候出現了不能打開鏈接的錯誤,改下面一行就沒問題了!

HS_FDS_CONNECT_INFO=ERPSQL.EK

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

// F. 創建DB LINK,以及測試

SQL>  create database link msql2 connect to "sa" identified by "pass" using 'TG4MSQL';


Database link created


SQL> select ta001,ta002 from acpta@msql2 where rownum<5;


TA001 TA002

----- -----------

S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

--------

代码:

-- 不知什么原因,感覺"通用方式"比"透明网關速度快一點"

SQL> set timing on

SQL> select ta001,ta002 from acpta@ora2sql where rownum<10;


TA001 TA002

----- -----------

S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

S710  20020328001

S710  20020329001

S710  20020419001

S710  20020422001

S710  20020425001


9 rows selected


Executed in 0.047 seconds


SQL> select ta001,ta002 from acpta@msql2 where rownum<10;


TA001 TA002

----- -----------

S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

S710  20020328001

S710  20020329001

S710  20020419001

S710  20020422001

S710  20020425001


9 rows selected


Executed in 52.281 seconds

--------

3.SQLSERVER訪問ORACLE
環境:windowsxp + sqlserver2000 + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221

代码:

// A. 添加ODBC,OdbcName=DB,OracleSid=DB

// B. 執行

sp_addlinkedserver 'LIORA', 'Oracle', 'MSDAORA', 'DB'

GO

EXEC sp_addlinkedsrvlogin  @rmtsrvname='LIORA',@useself='false',@locallogin='sa',@rmtuser='SYSTEM',@rmtpassword='MANAGER'


select top 10 topic,info from LIORA..SYSTEM.HELP

topic                                              info                                                                             

-------------------------------------------------- -------------------------------------------------------------------------------- 

@                                                  NULL

@                                                   @ ("at" sign)

@                                                   -------------

@                                                  NULL

@                                                   Runs the SQL*Plus statements in the specified command file. The command

@                                                   file can be called from the local file system or from a web server.

@                                                  NULL

@                                                   @ {uri|file_name[.ext]} [arg...]

@                                                  NULL

@                                                   where uri supports HTTP, FTP and gopher protocols in the form:


(影響 10 個資料列)

  //开始 15位到18位的身份证号转换
   //身份证号码由十七位数字本体码和一位校验码组成，排列顺序从左至右依次为：
   //六位数地址码、八位数字的出生日期码、三位数字的顺序码和一位数字的校验码
    public String change18ID(String ID15){
         String ID18="";
         if(ID15.length()==18){
           ID18=ID15.toUpperCase();
         }

         if(ID15.length()==15){
            int[] w={7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2,1};
            char[] A={'1','0','X','9','8','7','6','5','4','3','2'};
            String ID17=ID15.substring(0,6)+"19"+ID15.substring(6,15);
            int[] ID17Array;
            ID17Array=new int[17];
            for(int i=0;i<17;i++){
                ID17Array[i]=Integer.parseInt(ID17.substring(i,i+1));
            }
            int s=0;
            for(int i=0;i<17;i++){
                 s=s+ID17Array[i]*w[i];
            }
            s=s%11;
            ID18=ID17+A[s];
          }
          return ID18.trim();
      }
 //结束 15位到18位的身份证号转换
 public String change15ID(String ID18){
     String ID15="";
     if(ID18.length()==15) ID15=ID18;
     if(ID18.length()==18){
         ID15=ID18.substring(0,6)+ID18.substring(8,17);
     }
     return ID15.trim();
 }
 

<script language="Javascript">
function KeyDown(){
 //alert(event.keyCode);
  if ((event.keyCode==116)|| //屏蔽 F5 刷新键
(event.ctrlKey && event.keyCode==82)){ //Ctrl + R
event.keyCode=0;
event.returnValue=false;
}

}
document.onkeydown=KeyDown;
</script>

     String HanDigiStr[] = new String[]{"零","壹","贰","叁","肆","伍","陆","柒","捌","玖"};

      String HanDiviStr[] = new String[]{"","拾","佰","仟","万","拾","佰","仟","亿",
          "拾","佰","仟","万","拾","佰","仟","亿",
          "拾","佰","仟","万","拾","佰","仟" };

     /**
      * 输入字符串必须正整数，只允许前导空格(必须右对齐)，不宜有前导零
      * @param NumStr
      * @return
      *  */
      String PositiveIntegerToHanStr(String NumStr)
      {
       String RMBStr = "";
       boolean lastzero = false;
       boolean hasvalue= false;       // 亿、万进位前有数值标记
       int len,n;
       len = NumStr.length();
       if( len > 15 ) return "数值过大!";
       for(int i=len-1;i>=0;i--) {
        if( NumStr.charAt(len-i-1)==' ' ) continue;
        n = NumStr.charAt(len-i-1) - '0';
        if( n<0 || n>9 ) return "输入含非数字字符!";

        if( n!=0 ) {
         if( lastzero ) RMBStr += HanDigiStr[0];  // 若干零后若跟非零值，只显示一个零
                 // 除了亿万前的零不带到后面
         //if( !( n==1 && (i%4)==1 && (lastzero || i==len-1) ) )    // 如十进位前有零也不发壹音用此行
         if( !( n==1 && (i%4)==1 && i==len-1 ) )     // 十进位处于第一位不发壹音
          RMBStr += HanDigiStr[n];
         RMBStr += HanDiviStr[i];    // 非零值后加进位，个位为空
         hasvalue = true;                                    // 置万进位前有值标记

        }else {
         if( (i%8)==0 || ((i%8)==4 && hasvalue) )  // 亿万之间必须有非零值方显示万
          RMBStr += HanDiviStr[i];   // “亿”或“万”
        }
        if( i%8==0 ) hasvalue = false ;      // 万进位前有值标记逢亿复位
        lastzero = (n==0) && (i%4!=0);
        }

       if( RMBStr.length()==0 ) return HanDigiStr[0];         // 输入空字符或"0"，返回"零"
       return RMBStr;
      }

     /**
      *
      * @param val
      * @return
      *  */
     public  String NumToRMBStr(double val)
      {
       String SignStr ="" ;
       String TailStr ="";
       long  fraction, integer;
       int jiao,fen;

       if( val<0 ) {
        val = -val;
        SignStr = "负";
        }
       if(val > 99999999999999.999 || val <-99999999999999.999 ) return "数值位数过大!";
       // 四舍五入到分
       long temp = Math.round(val*100);
       integer = temp/100;
       fraction = temp%100;
       jiao = (int)fraction/10;
       fen = (int)fraction%10;
       if( jiao==0 && fen==0 ) {
        TailStr = "整";
        }
       else {
        TailStr = HanDigiStr[jiao];
        if( jiao!=0 )
         TailStr += "角";
        if( integer==0 && jiao==0 )                // 零元后不写零几分
         TailStr = "";
        if( fen!=0 )
         TailStr += HanDigiStr[fen] + "分";
        }

      // 下一行可用于非正规金融场合，0.03只显示“叁分”而不是“零元叁分”
      //        if( !integer ) return  SignStr+TailStr;

       return SignStr+PositiveIntegerToHanStr(String.valueOf(integer) )+"元"+TailStr;
      }

作者：wldandan　来源：www.matrix.org.cn 　发布时间：2006-02-22 17:55:09.217

最近刚做完一个项目，用Struts1.1做的。从不懂，到熟练使用，都靠参考CSDN的一些文档。但是文章上讲的并不一定适合自己，所以我把我自己做的一些东西拿上来给大家看看，互相交流一下。如果您有跟好的方法，可以和我联系。
MSN:whw_dream (AT) hotmail.com

Struts的文件上传
本文用的是Struts1.1的org.apache.struts.upload.FormFile类。很方便，不用自己写。也不用写一个jsp调用jspsmartupload就可以搞定。

选择上传文件页面：selfile.jsp



--------------------------------------------------------------------------------
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<html:html>
<html:form action="/uploadsAction.do" enctype="multipart/form-data">
<html:file property="theFile"/>
<html:submit/>
</html:form>
</html:html>

--------------------------------------------------------------------------------
UpLoadAction.java
--------------------------------------------------------------------------------
import java.io.*;
import javax.servlet.http.*;
import org.apache.struts.action.*;
import org.apache.struts.upload.FormFile;

/**
* <p>Title:UpLoadAction</p>
* <p>Description: QRRSMMS </p>
* <p>Copyright: Copyright (c) 2004 jiahansoft</p>
* <p>Company: jiahansoft</p>
* @author wanghw
* @version 1.0
*/

public class UpLoadAction extends Action {
  public ActionForward execute(ActionMapping mapping,
                               ActionForm form,
                               HttpServletRequest request,
                               HttpServletResponse response)
      throws Exception {
    if (form instanceof uploadsForm) {//如果form是uploadsForm
        String encoding = request.getCharacterEncoding();
        if ((encoding != null) && (encoding.equalsIgnoreCase("utf-8")))
        {
            response.setContentType("text/html; charset=gb2312");//如果没有指定编码，编码格式为gb2312
        }
        UpLoadForm theForm = (UpLoadForm ) form;
        FormFile file = theForm.getTheFile();//取得上传的文件
        try {
          InputStream stream = file.getInputStream();//把文件读入
          String filePath = request.getRealPath("/");//取当前系统路径
          // filePath = request.getRealPath(request.getRequestURI()); //取当前系统路径
          ByteArrayOutputStream baos = new ByteArrayOutputStream();
          OutputStream bos = new FileOutputStream(filePath + "/" +
                                                  file.getFileName());//建立一个上传文件的输出流
          //System.out.println(filePath+"/"+file.getFileName());
          int bytesRead = 0;
          byte[] buffer = new byte[8192];
          while ( (bytesRead = stream.read(buffer, 0, 8192)) != -1) {
            bos.write(buffer, 0, bytesRead);//将文件写入服务器
          }
          bos.close();
          stream.close();
        }catch(Exception e){
          System.err.print(e);
        }
        //request.setAttribute("dat",file.getFileName());
        return mapping.findForward("display");
    }
    return null;
  }
}



--------------------------------------------------------------------------------

UpLoadForm.java

--------------------------------------------------------------------------------


import javax.servlet.http.HttpServletRequest;
import org.apache.struts.action.*;
import org.apache.struts.upload.*;

/**
* <p>Title:UpLoadForm</p>
* <p>Description: QRRSMMS </p>
* <p>Copyright: Copyright (c) 2004 jiahansoft</p>
* <p>Company: jiahansoft</p>
* @author wanghw
* @version 1.0
*/

public class UpLoadForm extends ActionForm {
  public static final String ERROR_PROPERTY_MAX_LENGTH_EXCEEDED = "org.apache.struts.webapp.upload.MaxLengthExceeded";
  protected FormFile theFile;
  public FormFile getTheFile() {
      return theFile;
  }
  public void setTheFile(FormFile theFile) {
      this.theFile = theFile;
  }
  public ActionErrors validate(ActionMapping mapping, HttpServletRequest request)
  {
      ActionErrors errors = null;
      //has the maximum length been exceeded?
      Boolean maxLengthExceeded = (Boolean)
              request.getAttribute(MultipartRequestHandler.ATTRIBUTE_MAX_LENGTH_EXCEEDED);
      if ((maxLengthExceeded != null) && (maxLengthExceeded.booleanValue()))
      {
          errors = new ActionErrors();
          errors.add(ERROR_PROPERTY_MAX_LENGTH_EXCEEDED, new ActionError("maxLengthExceeded"));
      }
      return errors;

  }
}
//这是相对应的form，还有其他属性可以设置，具体可以参考struts的上传例子。




--------------------------------------------------------------------------------

struts-config.xml

--------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.1//EN" "http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">
<struts-config>
  <form-beans>
    <form-bean name="uploadsForm" type="UpLoadForm" />
  </form-beans>
  <action-mappings>
    <action name="uploadsForm" type="UpLoadAction" path="/uploadsAction">
      <forward name="display" path="/display.jsp" />
    </action>
  </action-mappings>
</struts-config>
<!--display.jsp就是随便写一个成功页-->

 //=================公司类方法===================//
public String getDrop(String compid,String dataid) throws Exception{
      if(str.IsEmpty(dataid) )
        dataid="AAA";
      Compid  com[]=this.readAll(compid,dataid);
      StringBuffer bf = new StringBuffer();
      if(com.length > 0){
           for(int i=0;i<com.length ;i++){
             if(dataid.equals(com[i].getBh()) )
               bf.append("<option value=" + com[i].getID()  + " selected >" + com[i].getName() + "</option>") ;
             else
               bf.append("<option value=" + com[i].getID()  + ">" + com[i].getName() + "</option>") ;
           }
         }
    return bf.toString();
    }
 //=================部门类方法同上===================//

//====================页面调用=====================//

   Compid com = new Compid();
   Deptid dep = new Deptid();
   Deptid [] depAll=bm.readAll("","");

          <select name="deptid" style="width:120px">
           <option value="">所有公司
            <%=com.getDrop(compid,deptid)%>
          </select>

          <select name="deptid" style="width:120px">
           <option value="">所有部门
            <%=dep.getDrop(compid,deptid)%>
          </select>




<script language=javascript>

 var bm= new Array();
 <%if(depAll.length>0){
     for(int i=0;i<depAll.length;i++){%>
     bm[<%=i%>] = new Array("<%=depAll[i].getCom()%>","<%=depAll[i].getID()%>","<%=depAll[i].getName()%>");
  <%}}%>
 function setBm(){
  document.form_com_dep.deptid.length = 0;
  document.form_com_dep.deptid.options[document.form_com_dep.deptid.length] = new Option("所有部门","");
  var comp = document.form_com_dep.compid.value ;
  var i=0;
  for (i=0;i < <%=depAll.length%> ; i++)  {
      if (bm[i][0] == comp ) {
         document.form_com_dep.deptid.options[document.form_com_dep.deptid.length] = new Option(bm[i][2], bm[i][1]);
             }       
     }
 }
</script>