Connecting to SQL Server with JDBC
All you need is 3 jar files in your classpath
• Msbase.jar
• Msutil.jar
• Mssqlserver.jar
When you install the Microsoft drivers, by default, they go to the following folder:
c:\program files\Microsoft SQL Server 2000 Driver for JDBC
So, going by this, you can set your CLASSPATH the following way:
CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver
Code to connect SQL Server
import java.*;
public class Connect
{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";
// Constructor
public Connect(){}
private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
/*
Display the driver properties, database details
*/
public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}
private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}
Output
If this code is successful, the output is similar to the following:
Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022
Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )
Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb
This is an excerpt from microsoft article. Here is the full article:
http://support.microsoft.com/kb/313100
Backup / Restore OIM Oracle DB
I am showing you the statements to backup and import an OIM Database. Most of the statements are on a linux box. Windows should be same or nearly same. I'll also provide with a few useful OIM / Oracle statements (besides Backup/Restore) that will be handy all the time.
OIM Backup
I always recommended that whenever you reach a logical step in implementing OIM, you should take a backup. By logical step, I mean when your first clean install is completed take one backup. Once your connectors are loaded, take another backup. So on and so forth. Coming straight to the point of backup, here are the statements:
Assuming you have a linux machine, connect with the user that has the privileges to do an export of oracle. Usually user is oracle, but if you have a different one - go with that user.
[oracle@idm ~]$mkdir exports
[oracle@idm ~]$cd exports
[oracle@idm exports]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Here is the syntax we are going to use to export the database:
exp <schemaOwner>/<schemaOwnerPassword>@<DatabaseSID> file=<filename>.dmp log=<logname>.log full=y
In this example assume:
Schema owner is xladm
Password is xladmpwd
databaseSID is IDM
filename is base_clean_install.dmp
logname is base_clean_install_log.log
So, issue the command like this:
[oracle@aelidm1 exports]$ exp xladm/xladmpwd@IDM file=base_clean_install.dmp log=base_clean_install_log.log full=y
If you need to, you may set your environment variables as follows:
export ORACLE_HOME=/ora/oim_infra
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=IDM
or in windows as follows:
set ORACLE_HOME=C:\ora\oim_infra
set PATH=%PATH%;%ORACLE_HOME%;
set ORACLE_SID=IDM
Note: Please change values according to your environment.
And your backup is done.
OIM Restore
Step 1
Firstly, delete user/schema owner & associated datafiles from Enterprise Manager. You may also use the following scripts to achieve it:
sqlplus sys/password@IDM as sysdba
SQL>drop user xladm cascade;
SQL>drop tablespace oim_dev including datafiles and contents;
SQL>drop tablespace oim_dev including contents;
SQL>quit
Note, we are logging in as sys user (with sysdba privilege) to delete the xladm schema owner / user and drop oim_dev tablespace completely.
Step 2
====================================
To Re-Prepare the OIM database:
====================================
log in as oracle (or login root and su - oracle)
cd /oim903/OIM903/installServer/Xellerate/db/oracle
sh ./prepare_xl_db.sh IDM /oracle/10_2 xladm xladmpwd OIM_DEV /oracle/OIM OIM_DEV_D01 TEMP sys_user_password
Here is the description of the parameters you need send to the script:
# Arguments : $1 --> ORACLE_SID
# $2 --> ORACLE_HOME
# $3 --> Oracle Identity Manager User Name
# $4 --> Password for the Oracle Identity Manager user
# $5 --> Name of the Tablespace to be created
# $6 --> Directory to store the datafile for the tablespace
# $7 --> Name of the datafile
# $8 --> Temporary tablespace for Oracle Identity Manager User
# $9 --> Password of the SYS user
Step 3
cd ~/exports
imp xladm/xladmpwd@IDM
Specify the filename from where the dump has to be imported. This will be the .dmp file name you specified when you took the backup. Also, specify xladm as the user when asked and press enter on all the other options presented.
Step 4
Next, we will recompile all invalid objects.
To recompile as user, save this file and run it from sqlplus:
Go to the folder that holds recompile_as_user.sql file. If you do not have this file, copy the text from below and save it one of the folder. Please note, you might have to run this twice to make sure all invalid objects are compiled.
sqlplus xladm/xladmpwd@IDM
SQL>@recompile_as_user.sql;
SQL>@recompile_as_user.sql;
SQL>quit
Then you can quit from sqlplus. If you are one of those who are intrigued to know which objects are rendered invalid, here is the sql you can use to find out this.
SQL>select object_name from user_objects where status='INVALID';
Here is the file:
----------------------------------------------------------
-- recompile_as_user.sql
-- connected as the SCHEMA_OWNER
----------------------------------------------------------
set feedback off;
set heading off;
set linesize 100;
set pagesize 1000;
spool recompile_as_user.lst;
prompt spool recompile_as_user.log;
select 'alter '||
DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||
object_name||' compile '||
DECODE(object_type,'PACKAGE BODY','BODY','PACKAGE','PACKAGE',' ')||';',
'show errors;'
from user_objects
where status = 'INVALID'
order by created,
DECODE(object_type, 'PACKAGE BODY', 'AAA', 'PACKAGE', 'AAB', substr(object_type, 1, 3)) DESC,
object_name;
prompt spool off;
spool off;
@@recompile_as_user.lst;
----------------------------------------------------------
Step 5
All set with the import. Restart your application server and you should be all set.
cd /opt/oracle/xellerate/bin
./xlStartServer.sh &
or ./xlStartServer.sh | tee /tmp/oim.log | less
or however you start your application server.
If you need to kill your application server, just do a ps -ef | grep java
and then you can kill the process.
Solving Oracle Issues if it is not up
===========================================
Check all the services are up (started) in Control Panel ->services
C:\oracle\product\10.2.0\db_1\BIN>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 27 16:01:09 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 188746604 bytes
Database Buffers 419430400 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL>quit
----------------------------------
Other DB Related Operations
----------------------------------
To ping TNS - tnsping idm
To start listener - lnsrctrl start
To start enterprise manager - emctl start dbconsole
To login to enterprise manager webconsole - http://server:1158/em
To startup database in sqlplus - startup
To shutdown in sqlplus - shutdown
To reach sqlplus address - http://server:5560/isqlplus
To initiate sqlplus - sqlplus
Another Backup Variant:
exp system/systempwd file=C:\DB_backup.dmp owner=xladm
