Creating Oracle to SQL Server Database Link Programmatically(32-Bit Win)

This article describes how to create a heterogeneos connection between Oracle to SQLServer programmatically using Java.

Please read the Article Creating Oracle to SQL Server Database Link to create this connection manually.

You can download the source code for this tutorial from here

Step 1: Create Windows Script (vbs) File to Create Data Source (ODBC)

Create a file of type .vbs (for Example odbcScript.vbs) with the code below.

This script expects six input parameters that are represent by WScript.Arguments.Item(i) in the script. Below is the list of six parameters you need to supply.

DataSourceName: The Name of the Datasource Connection (ODBC) you want create.

DatabaseName: Name of the SQL Server Database

LastUser: SQL Server User Name

Password: Password of the SQL Server User

Server: Name of the Server Computer that runs the SQL Server

sComputer = Name of the Host machine where you run this code

The Script:

Option Explicit

'Constants

Const HKEY_CLASSES_ROOT = &H80000000

Const HKEY_CURRENT_USER = &H80000001

Const HKEY_LOCAL_MACHINE = &H80000002

Const HKEY_USERS = &H80000003

Const HKEY_CURRENT_CONFIG = &H80000005



'Variables

On Error resume next

Dim DataSourceName

Dim DatabaseName

Dim Description

Dim DriverPath

Dim LastUser

Dim Password

Dim Server

Dim Trusted_connection

Dim DriverName

Dim InputFile

Dim iFSO

Dim ifile

Dim sComputer

Dim sPath



'Value assignment



DataSourceName = WScript.Arguments.Item(0)

DatabaseName = WScript.Arguments.Item(1)

DriverPath = "C:\WINNT\System32\sqlsrv32.dll"

LastUser = WScript.Arguments.Item(2)

Password = WScript.Arguments.Item(3)

Server = WScript.Arguments.Item(4)

Description="ODBC DSN for the Database:" & DatabaseName

DriverName="SQL Server"

'InputFile="c:\pclist.txt"

'Set iFSO = CreateObject("Scripting.FilesyStemObject")

'Set ifile = iFSO.OpenTextFile(inputfile)

sPath = "SOFTWARE\ODBC\ODBC.INI\" & DataSourceName



'Read and loop through the input file

'Do until ifile.AtEndOfLine

'sComputer = ifile.ReadLine

sComputer = WScript.Arguments.Item(5)

If (0 = CreateRegKey(sComputer, HKEY_LOCAL_MACHINE, sPath)) Then

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Database", DatabaseName

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Description", Description

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Driver", DriverPath

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "LastUser",LastUser

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Password",Password

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Server",Server

'Else

'Exit Do

End If

'Write in "ODBC Data Sources" Key to allow ODBC Manager list & manage the new DSN

SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", DataSourceName , DriverName

'Loop

'ifile.Close

Set ifile = Nothing

Set iFSO = Nothing



'Create RegKey Function



Function CreateRegKey (sComputer, hTree, sKey)

Dim oRegistry

Dim lResult

Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")

lResult = oRegistry.CreateKey(hTree, sPath)

If (lResult = 0) And (Err.Number = 0) Then

CreateRegKey = 0

Else

CreateRegKey = 1

msgbox("Create Key " & sKey & " Failed")

End If

Set oRegistry = Nothing

End Function



'set RegKey Function



Function SetRegKeyStrValue (sComputer, hTree, sKey, sValueName, sValue)

Dim oRegistry

Dim lResult

Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")

lResult = oRegistry.SetStringValue(hTree, sKey, sValueName, sValue)

If (lResult = 0) And (Err.Number = 0) Then

SetRegKeyStrValue = 0

Else

SetRegKeyStrValue = 1

msgbox("Set Value for " & sKey & " Failed")

End If

Set oRegistry = Nothing

End Function


- You can run this script with java code below.


public void doODBCStep()
throws IOException

{

String absolutePath = "c:\\odbcScript.vbs";
String dataSourceName = "ibexLink"; // Database Source (ODBC) Name
String databaseName = "ibex"; // SQL Server Database Name
String user = "sa"; // SQL Server User
String password = "123456"; // SQL Server Password
String server = "denizli"; // SQL Server
String host = "192.168.1.112"; // Host where you run this code
Runtime load = Runtime.getRuntime();
load.exec("wscript.exe " + "\""+ absolutePath +"\"" + " " + "\""+ dataSourceName+"\"" + " " + "\""+ databaseName+"\"" + " " + "\""+ user+"\""
+ " " + "\""+ password +"\"" + " " + "\""+ server +"\"" + " " + "\""+ host +"\"");

}


Step 2: Edit Oracle Files

Step 2.1: Edit tnsnames.ora file


public void setTnsnames(File file)
throws FileNotFoundException, IOException

{

String oracleHost = "192.168.1.112"; // Oracle Host
String oraclePort = "1521"; //Oracle Port
String newEntry = "ibexLink.world = \n" +
" (DESCRIPTION = \n" +
" (ADDRESS = (PROTOCOL = TCP)(HOST = " + oracleHost + ")(PORT = "+ oraclePort +")) \n" +
" (CONNECT_DATA = \n" +
" (SID = " + "ibexLink) \n" +
" ) \n" +
" (HS = OK) \n" +
" )";

this.setContents(file,getContents(file)+ newEntry);

}


Step 2.2: Edit listener.ora file


public void setListener(File file)
throws FileNotFoundException, IOException
{
String oracleHome = "C:\\oraclexe\\app\\oracle\\product\\10.2.0\\server"; // Oracle Home
String newEntry = " (SID_DESC =\n" +
" (SID_NAME = " + "ibexLink) \n" +
" (ORACLE_HOME = " +oracleHome + ") \n " +
" (PROGRAM = hsodbc)\n" +
" ) ";

String currentContent = this.getContents(file);
int equationStringIndex = currentContent.indexOf("=");
int indexofSIDLIST = currentContent.substring(equationStringIndex+1).indexOf("=");
int copyIndex = indexofSIDLIST + equationStringIndex + 1;
String parseF = currentContent.substring(0,copyIndex+1);
String parseL = currentContent.substring(copyIndex +1);
this.setContents(file,parseF+newEntry+parseL);
}


Step 2.3: Create new inithsodbc.ora file


public void setInitHsOdbc(File fromFile, File toFile)
throws FileNotFoundException, IOException
{
String newEntry1 = " HS_FDS_CONNECT_INFO = " + "ibexLink"+ " ";
String newEntry2 = " HS_FDS_TRACE_LEVEL = off ";
this.copy(fromFile,toFile);

try {
BufferedWriter out = new BufferedWriter(new FileWriter(toFile));
out.write(newEntry1);
out.newLine();
out.write(newEntry2);
out.close();
}
catch(Exception ex)
{
System.out.println("EX: " + ex);
}
}


Step 2.4: getContents, setContents and copy Methods
Below is the code of the utility files used in the Article.

public String getContents(File aFile) {

StringBuilder contents = new StringBuilder();
try {
BufferedReader input = new BufferedReader(new FileReader(aFile));
try {
String line = null; //not declared within while loop
while (( line = input.readLine()) != null){
contents.append(line);
contents.append(System.getProperty("line.separator"));
}
}
finally {
input.close();
}
}
catch (IOException ex){
ex.printStackTrace();
}
return contents.toString();
}

public void setContents(File aFile, String aContents)
throws FileNotFoundException, IOException {
try {
BufferedWriter out = new BufferedWriter(new FileWriter(aFile));
out.write(aContents);
out.close();
}
catch(Exception ex)
{
System.out.println("EX: " + ex);
}
}

public void copy(File fromFile, File toFile)
throws IOException {
FileInputStream from = null;
FileOutputStream to = null;
try {
from = new FileInputStream(fromFile);
to = new FileOutputStream(toFile);
byte[] buffer = new byte[4096];
int bytesRead;
while ((bytesRead = from.read(buffer)) != -1)
to.write(buffer, 0, bytesRead); // write
} finally {
if (from != null)
try {
from.close();
} catch (IOException e) {
;
}
if (to != null)
try {
to.close();
} catch (IOException e) {
System.out.println(e);
}
}
}


Step 3: Create Link
The Java code below creates an Oracle Database link to the SQL Server Database.

private void createHSLink()
throws ClassNotFoundException, SQLException
{
String OracleHost = "192.168.1.112";
String OraclePort = "1521";
String OracleSID = "XE";
String user = "system";
String password = "sys";

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection cc = DriverManager.getConnection("jdbc:oracle:thin:@"+OracleHost+":"+OraclePort+":"+OracleSID, user, password);
Statement oracleStmt = cc.createStatement();

String SQLUser = "sa";
String SQLUserPassword = "123456";

try
{
String sql = "begin execute immediate('create public database link ibexLink connect to " + SQLUser + " identified by " + SQLUserPassword + " using ''ibexLink.world" + "'''); end;";
System.out.println(sql);
oracleStmt.executeUpdate(sql);
}
catch (SQLException e)
{
System.out.println("oracle connection error : " + e);
}
}


Step 4: Restart Service Oracle Listener


public void cmdListener(String oraHome)
throws IOException, InterruptedException
{
Runtime load = Runtime.getRuntime();
Process pro = load.exec(oraHome + "\\bin\\LSNRCTL.EXE" + " " + "reload");
ProcessOutput po = new ProcessOutput(pro.getInputStream());
po.start();
int i;
i = pro.waitFor();
}



H. Kutay Çilingiroğlu

Comments

Popular posts from this blog

Monitoring Oracle Database with Zabbix