|
midrange.com
Hi Ashish,
>Q1 Where can i find the JDBC driver for oracle,
I've used the Oracle JDBC driver in classes12.zip.
Search (Google) for classes12.zip
>Q2 Has anyone tested trying to get data from oracle
>database with a java program running on AS400.
Yes, I've also used java to call an oracle function.
>Q3 Does anyone have a sample java program to create
>connectio to oracle database
This program reads (SQL) an Oracle db every 30 sec and writes
the result to an iSeries dtaq:
import java.io.*;
import java.sql.*;
import java.math.*;
import java.util.*;
import java.text.*;
import java.net.*;
import java.lang.*;
import java.lang.Error;
import java.security.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
import com.ibm.as400.access.*;
public class Get_tCAOut extends Object
{
public static void main(String[] parameters)
{
System.out.println( " " );
// if a dataq was not specified, display help text and
exit.
if (parameters.length >= 3)
{
try
{
// Parameter 1: the archive server name.
String ArcSrv = parameters[0];
// Parameter 2: AS/400 dtaq name.
String DtaQ = parameters[1];
// Parameter 3: AS/400 dtaq library.
String DtaQLib = parameters[2];
// Parameter 4: Archive user.
String ArcUsr = parameters[3];
// Parameter : Archive user pwd.
String ArcPwd = parameters[4];
// Parameter : Change status Y/N.
String ChgSta = parameters[5];
// Create an AS400 object for the AS/400 that
has the data queue.
AS400 as400 = new AS400
("localhost", "*CURRENT", "*CURRENT");
// Build a record format for the format of the
data queue entry.
// First create the base data types
CharacterFieldDescription Blanke1 =
new CharacterFieldDescription(new AS400Text
(20, as400), "BLANKE1");
CharacterFieldDescription MesnKeyD =
new CharacterFieldDescription(new AS400Text
(10, as400), "MESNKEY");
CharacterFieldDescription OrgDocIdD =
new CharacterFieldDescription(new AS400Text
(10, as400), "ORGDOCID");
CharacterFieldDescription OrgCatD =
new CharacterFieldDescription(new AS400Text
(50, as400), "ORGCAT");
CharacterFieldDescription NewDocIdD =
new CharacterFieldDescription(new AS400Text
(10, as400), "NEWDOCID");
CharacterFieldDescription NewCatD =
new CharacterFieldDescription(new AS400Text
(50, as400), "NEWCAT");
CharacterFieldDescription AttributesD =
new CharacterFieldDescription(new AS400Text
(3000, as400), "ATTRIBUTES");
CharacterFieldDescription Blanke2 =
new CharacterFieldDescription(new AS400Text
(850, as400), "BLANKE2");
// Build a record format and fill it with the
base data types.
RecordFormat dataFormat = new RecordFormat();
dataFormat.addFieldDescription(Blanke1);
dataFormat.addFieldDescription(MesnKeyD);
dataFormat.addFieldDescription(OrgDocIdD);
dataFormat.addFieldDescription(OrgCatD);
dataFormat.addFieldDescription(NewDocIdD);
dataFormat.addFieldDescription(NewCatD);
dataFormat.addFieldDescription(AttributesD);
dataFormat.addFieldDescription(Blanke2);
// Create the data queue object that represents
the data queue on
// the AS/400.
DataQueue dq = new DataQueue(as400, "/QSYS.LIB/"
+ DtaQLib + ".LIB/" + DtaQ + ".DTAQ");
boolean Continue = true;
// Connect to Oracle db
try{
DriverManager.registerDriver( (Driver)
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance
());
System.out.println("ok 1");
}
catch(Exception e)
{
System.out.println(e);
}
String url = ("jdbc:oracle:thin:@" + ArcSrv);
System.out.println(url + ArcUsr + ArcPwd);
Connection con = DriverManager.getConnection
(url, ArcUsr, ArcPwd);
System.out.println("OK Connect to " + url);
System.out.println(" ");
while (Continue)
{
Statement stmt = con.createStatement();
System.out.println("Set working status to
records in tCAOut");
String query1 = "update tCAOut set
cao_sta_nKey=2 where cao_sta_nKey=1";
ResultSet rsArchive1 = stmt.executeQuery
(query1);
System.out.println(" ");
System.out.println("Select records in tCAOut");
String query2 = "select cao_nKey, cao_mes_nkey,
cao_OrgDocID_doc_nKey,"
+ " cao_OrgCat_sName,"
+ " cao_NewDocId_doc_nKey, cao_NewCat_sName,
cao_attributes"
+ " from tCAOut where cao_sta_nKey=2";
ResultSet rsArchive2 = stmt.executeQuery
(query2);
System.out.println(" ");
System.out.println("Write tCAOut records to
dtaq " + DtaQLib + "/"+ DtaQ);
while (rsArchive2.next()) {
int caonkeyI = rsArchive2.getInt (1);
int mesnkeyI = rsArchive2.getInt
(2);
int orgdocidI = rsArchive2.getInt
(3);
String orgcat = rsArchive2.getString (4);
int newdocidI = rsArchive2.getInt
(5);
String newcat = rsArchive2.getString (6);
String attributes = rsArchive2.getString
(7);
Record data1 = new Record(dataFormat);
// Set the values we received from the user into
the record.
String caonkeyC = String.valueOf
(caonkeyI);
System.out.println("DtaQ data: caonkeyC= " +
caonkeyC);
data1.setField("BLANKE1", " ");
String mesnkeyC = String.valueOf
(mesnkeyI);
data1.setField("MESNKEY", mesnkeyC);
System.out.println("DtaQ data: mesnkeyC= " +
mesnkeyC);
String orgdocidC = String.valueOf
(orgdocidI);
data1.setField("ORGDOCID", orgdocidC);
System.out.println("DtaQ data: orgdocidC= " +
orgdocidC);
data1.setField("ORGCAT", orgcat);
System.out.println("DtaQ data: orgcat= " +
orgcat);
String newdocidC = String.valueOf
(newdocidI);
data1.setField("NEWDOCID", newdocidC);
System.out.println("DtaQ data: newdocidC= " +
newdocidC);
data1.setField("NEWCAT",
newcat);
System.out.println("DtaQ data: newcat= " +
newcat);
data1.setField("ATTRIBUTES",
attributes);
data1.setField("BLANKE2", " ");
System.out.println("DtaQ data: attributes= "
+ attributes);
// Convert the record into a byte array. The
byte array is what
// is actually put to the data queue.
byte [] byteData = data1.getContents();
System.out.println("Bytedata = " + byteData);
// Write the record to the data queue.
dq.write(byteData);
} //While rsArchive2.next
String query3 = "update tCAOut set
cao_sta_nKey=3,cao_dteLastUpdate=sysdate where
cao_sta_nKey=2";
String query4 = "update tCAOut set
cao_sta_nKey=1,cao_dteLastUpdate=sysdate where
cao_sta_nKey=2";
if (ChgSta.equals("Y")) {
System.out.println(" ");
System.out.println("Set status 3 to records
with status 2 in tCAOut");
ResultSet rsArchive5 = stmt.executeQuery
(query3);
}
else {
System.out.println(" ");
System.out.println("Set status 1 to records
with status 2 in tCAOut");
ResultSet rsArchive3 = stmt.executeQuery
(query4);
}
} //for (int i=1
stmt.close();
//waits 30 sec
try {
System.out.println("Wait 30 sec."); //MKO1
Thread.sleep(30000); //MKO1
}
catch (Exception e)
{
System.out.println(e);
}
} // while (Continue)
}
catch (Exception e)
{
// If any of the above operations failed say the
data queue operation
// failed and output the exception.
System.out.println("Data Queue operation
failed");
System.out.println(e);
}
}
// Display help text when parameters are incorrect.
else
{
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("Parameters are not correct.
Command syntax is:");
System.out.println("");
System.out.println(" Get_tCAOut <Srv_Name>
<iSeries_DQ> <DQ_LIB> <Archive_User> <Archive_Pwd>
<Change_status> ");
System.out.println("");
}
System.exit(0);
}
}
Date: Thu, 7 Jul 2005 11:04:25 -0700 (PDT)
From: Ashish Kulkarni <kulkarni_ash1312@xxxxxxxxx> Add To
Address Book
Subject: JDBC driver to get data from Oracle database
To: java400-l@xxxxxxxxxxxx
Hello
We have a project in which we need to get some data
from Oracle database, we are planning to use java and
JDBC to connect to oracle to get this data.
Q1 Where can i find the JDBC driver for oracle,
Q2 Has anyone tested trying to get data from oracle
database with a java program running on AS400.
Q3 Does anyone have a sample java program to create
connectio to oracle database
Ashish
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
