[筆記]如何用java呼叫oracle store procedure並接收 oracle object的回傳參數

因應和介接系統的API stored procedure修改，將原本的回傳參數型態由oracle cursor
改成oracleobject，機歪老闆又把這項雜事丟給我，要我測試在我們的系統上是否可行。

雖然網路上的參考文獻很多，不過大部分都是傳送或接收陣列型態的參數給stored procedure，
所以花了一些功夫整理了一下關鍵的內容。




conn = this.getConnectionAdapter().getNewConnection();
vendorConn = (OracleConnection)((WLConnection)conn).getVendorConnection();
call = (OracleCallableStatement)vendorConn.prepareCall(" begin twm_pg_ec_test.pc_get_oracle_object_demo(?,?) ; end;"); //引號內是stored procedure
//的名稱，問號是傳給他即接受的對應參數。

call.setString(1, "EC");//設定第一個參數
call.registerOutParameter(2, OracleTypes.STRUCT, "ECUSER2.EMPLOYEETYPE");
//設定第二個回傳的參數，同時指定他在資料庫上的object type(第二個參數)
call.executeQuery();

oracle.sql.STRUCT emp = (oracle.sql.STRUCT)call.getObject(2); //解析回傳的第二個參數
oracle.sql.Datum[] attrs = emp.getOracleAttributes();//取得所有oracle object的屬性

//取得oracle object屬性的值
int id= attrs[0].intValue();
String firstName = attrs[1].stringValue();
String lastName = attrs[2].stringValue();
java.util.Date dob = attrs[3].dateValue();
String phone = attrs[4].stringValue();








[+/-] 深入閱讀

[+/-] 不看了

SVN + Apache 2.2 Authentication Set Up

I have tried twice to start up a simple svn server with either svnserve daemon or
apache module, even both.
But every time when I want to rebuild a server in different machines, I have to
waste my time looking up Internet resource. Therefore, I determined to write a easy document about the whole procedure just in case I should forget once again.


Platform: winxp sp3 & IBM X31

1.Download SVN binary with apache module from here

2.Download apache package and install it.
After installation, enter localhost to see if apache service works.

3.Copy bin/mod_dav_svn.so and bin/mod_authz_svn.so under svn installed directiry to the Apache modules directory.

4.Set system environment variable "path" to svn insatlled directory
subfolder "bin" and reboot so that svn support dll's are visible to
apache service.

5. Edit the Apache configuration file (httpd.conf) and make the following changes:

5a. Uncomment the following two lines:

#LoadModule dav_fs_module modules/mod_dav_fs.so
#LoadModule dav_module modules/mod_dav.so

5b. Add the following two lines to the end of the LoadModule section:

LoadModule dav_svn_module modules/mod_dav_svn.so
LoadModule authz_svn_module modules/mod_authz_svn.so

6. Add the following code snippet in th end of http.conf

# /svn_repos attribute refers to http uri.In
# this example, it should be http://localhost/svn_repos/

#
DAV svn
SVNParentPath D:/svn_repos/
# This indicates the absolute path where your
# respositories reside in your machine
# Apache authentication begins...
AuthType Basic
AuthName "Subversion repository"
AuthUserFile D:/svn_repos/apache_authz
# Generate this "apache_authz" with htpasswd tool under apache installed
# directory "bin" subfolder with following
# command:
# htpasswd -cm path to your file username
# ex. htpasswd -cm D:/svn_repos/apache_authz kinju
# -c option means "create" Next time when you want to
# add a new user, you should omit -c option like that
# htpasswd -m D:/svn_repos/apache_authz kinjuplus
# This program should prompt you to enter password twice.

Require valid-user
### apache auth end

### svn auth begin
# our access control policy
AuthzSVNAccessFile D:/svn_repos/svn_authz
# authentication if necessary
Satisfy Any
### svn auth end

#


7. Edit svn auth policy in configuration file
as you define in http.conf previously

[groups] #define group syntax: groupname = user1,user2,....
test-developers = kinju


[/:] #repository root
*= r
@test-developers = rw
[test:/] # "test repository"
*= r
@test-developers = rw

[test:/test] # A test folder under test repository
@test-developers = rw

[test2:/] #"test2 repository"
*= r
@test-developers = rw



Tips: You must create your repository with svnadmin command line tool like that
"svnadmin create --fs-type fsfs NewRepo."
On the contrary, creating a repository with TortoiseSVN(烏龜), you probably
will encounter a error. This is a bug dued to a compatibility issue in new
version of TortoiseSVN. Refer to here

[+/-] 深入閱讀

[+/-] 不看了

Google Map API

由於期末作業的需要，試了一下Google Map API
寫法並不難，google也提供了許多範例程式可以參考，
主要是include一些java script。
1.先到 Google Map API 以自己的google 帳號以及domain name產生一組註冊碼
2.在html head標籤內以註冊碼引入google api，重點在於key的引用，最簡單的類似像這樣。

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script src="http://maps.google.com/maps?file=api&v=1&key=ABCDEF" type="text/javascript"
></script>
</head>
<body>
   <div id="mymap" style="width: 300px; height: 300px"></div>

   <script type="text/javascript">
      //<![CDATA[
            function load() {
      if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(37.4419, -122.1419), 13);
      }
    }
      //]]>
   </script>
</html>

3.最後在body部分加入 body body onload="load()" onunload="GUnload()"這個屬性
以及"map"這個div元素，即便大功告成。

其他的用法還有很多，有時間再來慢慢發掘吧！

[+/-] 深入閱讀

[+/-] 不看了

Connect MySQL in VS.NET 2005 with ODBC

1. 下載 ODBC connector


2.Install ODBC connector


















3.在C#程式碼裡加入這一段
String mycn = "DRIVER={MySQL ODBC 3.51"+ "Driver};SERVER=localhost;DATABASE=xml;USER=root;"+
"PASSWORD=1234;OPTION=3;stmt=SET NAMES 'big5';";

//stmt=SET NAMES 'big5'是為了顯示資料庫裡的中文欄位
String query = "select * from fine_work";
DataSet ds = new DataSet();
OdbcConnection conn = new OdbcConnection(mycn);
OdbcDataAdapter adapter = new OdbcDataAdapter(query, mycn);
try
{
conn.Open();
adapter.Fill(ds);
conn.Close();
}

參考連結
藍色小舖
不知名的網站

或許下次可以改用MySqlConnection類別，不過這次我沒試出來...囧

[+/-] 深入閱讀

[+/-] 不看了