import java.util.*;

import java.sql.*;

import com.microsoft.sqlserver.jdbc.*;public class defaultStmt

{

Connection tb_conn = null;

private final String testquery = "select c1 from t1";

PreparedStatement pstmt = null;

String url="jdbc:sqlserver://192.168.14.239:1433;databasename=tmax";

String uid="test";

String pwd="tmax123";

ResultSet rset = null;public defaultStmt() {

init();

}

public void init() {

try {

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

}

catch(Exception e) { e.printStackTrace(); }

execCall();

}

public void execCall() {

try {tb_conn = DriverManager.getConnection(url, uid, pwd);

pstmt = tb_conn.prepareStatement(testquery);

rset = pstmt.executeQuery();

while(rset.next()){

System.out.println( rset.getInt(1) );

}

pstmt.close();

tb_conn.close();

}catch(Exception e) { e.printStackTrace();

}finally{ try { pstmt.close(); tb_conn.close(); } catch(Exception ex)

{ ex.printStackTrace(); } }

}public static void main(String[] args) {

new defaultStmt();

}

}

1. sqljdbc_xa.dll 파일을 SQL Server의 Binn 디렉토리에 복사

(해당파일은 Microsoft SQL Server 2005 JDBC Driver 1.2의 sqljdbc_1.2\kor\xa에 플랫폼별

로 있음)2. 첨부한 (xa_install.sql)을 DB 에 관리자 계정으로 로그인 후 실행.

(설치여부 확인 sp_help xp_sqljdbc_xa_init )3. XA Transaction을 사용하기 위한 권한 부여.

ex)

use master

go

exec sp_grantdbaccess 'tibero','tmax'

go

exec sp_addrolemember [SqlJDBCXAUser], 'tibero'

go4. DTC Service 가능토록 설정(XA Transaction을 지원할 수 있도록 설정)

- 제어판-관리도구-구성요소서비스-컴퓨터-내컴퓨터(속성: 오른쪽마우스 클릭)

- MSDTC TAB에서 '보안구성' XA 트랜잭션 사용을 체크 후 MSDTC 및 MSSQL 재구동

import java.net.Inet4Address;

import java.sql.*;

import java.util.Random;

import javax.transaction.xa.*;

import javax.sql.*;

import com.microsoft.sqlserver.jdbc.*;public class testXA {public static void main(String[] args) throws Exception {

// Create a variable for the connection string.

String url="jdbc:sqlserver://192.168.14.239:1433;databasename=tmax";

String uid="test";

String pwd="tmax123";try {

// Establish the connection.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection(url, uid, pwd);// Create a test table.

Statement stmt = con.createStatement();

try {stmt.executeUpdate("DROP TABLE t1"); }catch (Exception e) {}

stmt.executeUpdate("CREATE TABLE t1 (c1 int)");

stmt.close();

con.close();// Create the XA data source and XA ready connection.

SQLServerXADataSource ds = new SQLServerXADataSource();

ds.setUser("hslim");

ds.setPassword("tmax123");

ds.setServerName("192.168.14.239");

ds.setPortNumber(1433);

ds.setDatabaseName("tmax");

XAConnection xaCon = ds.getXAConnection();

con = xaCon.getConnection();// Get a unique Xid object for testing.

XAResource xaRes = null;

Xid xid = null;

xid = XidImpl.getUniqueXid(1);// Get the XAResource object and set the timeout value.

xaRes = xaCon.getXAResource();

xaRes.setTransactionTimeout(0);// Perform the XA transaction.

System.out.println("Write -> xid = " + xid.toString());

xaRes.start(xid,XAResource.TMNOFLAGS);

PreparedStatement pstmt =con.prepareStatement("INSERT INTO t1 (c1) VALUES ()");

pstmt.setInt(1,1);

pstmt.executeUpdate();// Commit the transaction.

xaRes.end(xid,XAResource.TMSUCCESS);

xaRes.commit(xid,true);// Cleanup.

pstmt.close();

con.close();

xaCon.close();// Open a new connection and read back the record to verify that it worked.

con = DriverManager.getConnection(url, uid, pwd);

ResultSet rs = con.createStatement().executeQuery("SELECT * FROM t1");

rs.next();

System.out.println("Read -> 1 rows = " + rs.getInt(1));

rs.close();

con.close();

}// Handle any errors that may have occurred.

catch (Exception e) {

e.printStackTrace();

}

}

}

class XidImpl implements Xid {public int formatId;

public byte[] gtrid;

public byte[] bqual;

public byte[] getGlobalTransactionId() {return gtrid;}

public byte[] getBranchQualifier() {return bqual;}

public int getFormatId() {return formatId;}XidImpl(int formatId, byte[] gtrid, byte[] bqual) {

this.formatId = formatId;

this.gtrid = gtrid;

this.bqual = bqual;

}

public String toString() {

int hexVal;

StringBuffer sb = new StringBuffer(512);

sb.append("formatId=" + formatId);

sb.append(" gtrid(" + gtrid.length + ")={0x");

for (int i=0; i<gtrid.length; i++) {

hexVal = gtrid[i]&0xFF;

if ( hexVal < 0x10 )

sb.append("0" + Integer.toHexString(gtrid[i]&0xFF));

else

sb.append(Integer.toHexString(gtrid[i]&0xFF));}

sb.append("} bqual(" + bqual.length + ")={0x");

for (int i=0; i<bqual.length; i++) {

hexVal = bqual[i]&0xFF;

if ( hexVal < 0x10 )

sb.append("0" + Integer.toHexString(bqual[i]&0xFF));

else

sb.append(Integer.toHexString(bqual[i]&0xFF));

}

sb.append("}");

return sb.toString();

}// Returns a globally unique transaction id.

static byte [] localIP = null;

static int txnUniqueID = 0;

static Xid getUniqueXid(int tid) {Random rnd = new Random(System.currentTimeMillis());

txnUniqueID++;

int txnUID = txnUniqueID;

int tidID = tid;

int randID = rnd.nextInt();

byte[] gtrid = new byte[64];

byte[] bqual = new byte[64];

if ( null == localIP) {

try {

localIP = Inet4Address.getLocalHost().getAddress();

}

catch ( Exception ex ) {

localIP = new byte[] { 0x01,0x02,0x03,0x04 };

}

}

System.arraycopy(localIP,0,gtrid,0,4);

System.arraycopy(localIP,0,bqual,0,4);// Bytes 4 -> 7 - unique transaction id.

// Bytes 8 ->11 - thread id.

// Bytes 12->15 - random number generated by using seed from current time in

milliseconds.

for (int i=0; i<=3; i++) {

gtrid[i+4] = (byte)(txnUID%0x100);

bqual[i+4] = (byte)(txnUID%0x100);

txnUID >>= 8;

gtrid[i+8] = (byte)(tidID%0x100);

bqual[i+8] = (byte)(tidID%0x100);

tidID >>= 8;

gtrid[i+12] = (byte)(randID%0x100);

bqual[i+12] = (byte)(randID%0x100);

randID >>= 8;

}

return new XidImpl(0x1234, gtrid, bqual);

}

}

com.microsoft.sqlserver.jdbc.SQLServerException: 호스트 에 대한 TCP/IP 연결에 실패했습니다.

java.net.ConnectException: Connection refused

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

at java.sql.DriverManager.getConnection(DriverManager.java:512)

at java.sql.DriverManager.getConnection(DriverManager.java:171)

at defaultStmt.execCall(defaultStmt.java:37)

at defaultStmt.init(defaultStmt.java:30)

at defaultStmt.(defaultStmt.java:22)

at defaultStmt.main(defaultStmt.java:52)

java.lang.NullPointerException

at defaultStmt.execCall(defaultStmt.java:48)

at defaultStmt.init(defaultStmt.java:30)

at defaultStmt.(defaultStmt.java:22)

Write -> xid = formatId=4660

gtrid(64)={0x7f00000101000000010000000d5390f1000000000000000000000000

0000000000000000000

00000000000000000000000000000000000000000000000000000}

bqual(64)={0x7f00000101000000010000000d5390f1000000000000000000000000

0000000000000000000

00000000000000000000000000000000000000000000000000000}

javax.transaction.xa.XAException: java.sql.SQLException: XA 컨트롤 연결

을 만들지 못했습니다.

오류: xp_sqljdbc_xa_init failure, status:0 msg:***

SQLJDBC_XA DTC_ERROR Context: xa_init, state=1, StatusCode:0

(0x00000000) ***.

at com.microsoft.sqlserver.jdbc

CREATE DATABASE LINK 링크명

CONNECT TO 유저명 ID IDENTIFIED BY '패스워드' USING 'tns_alias명';

SELECT * FROM 테이블명@링크명;

INSERT INTO 테이블명@링크명 VALUES (col1, col2);

DELETE FROM 테이블명@링크명;

UPDATE 테이블명@링크명 SET col1=1;**PROCEDURE, TRIGGER안에서도 DBLink 사용가능.