import java.sql.*;
public class Query{
// Variablen für die DB Connections
static String url;
static String dsn1, dsn2, dsn3;
static String user1, user2, user3;
static String password1, password2, password3;
static String Driver;
static String sql1, sql2, sql3;
static Connection con1, con2, con3;
static Statement stmt1, stmt2, stmt3;
static ResultSet result1, result2;
static int messer, vorschub;
static int zaehler, zudruckenstk, uebrigestk, Druckstatus;
static int xpos, ypos, serstart, winkel;
static String PfadzurVorlage, kurzmzz, commentar;
static String aoz, slo, iti, kiv, mzz, t01, t02, t03, t04, t05, t06, t07;
static String t08, t09, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19;
static String t20, st1, st2, st3, st4, st5, trd;
public static void main(String arguments[]){
// Abfragen der Verbindungsparameter
System.out.println("\n\n\n\n\n");
System.out.println("Datenbankzugriff mit JDBC");
System.out.println("================================\n\n");
// Adresse der zu verbindenden Datenbank
url = "jdbc:odbc:";
dsn1= "RofinDB2";
user1 = "USERNAME";
password1 = "GEHEIM";
dsn2 = "RofinACC";
user2 = ""; // wird nicht benoetigt
password2 = ""; // wird nicht benoetigt
dsn3 = "BacktoDB2"; // ist der selbe Treiber wie DSN1
user3 = "USERNAME";
password3 = "GEHEIM";
try{
// JDBC over Odbc Treiber laden
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Verbindung mit der DB2 - Datenbank aufnehmen - noch nicht gelesene Datensätze suchen (Select nach st1=1)
con1 = DriverManager.getConnection(url+dsn1, user1, password1);
System.out.println("Verbinden_1 ...... ");
// Verbindung zur Access - Datenbank aufnehmen - Datensatz schreiben (Insert)
con2 = DriverManager.getConnection(url+dsn2, user2, password2);
System.out.println("Verbinden_2 ...... ");
// Verbindung mit der DB2 - Datenbank aufnehmen - wie CON1 nur hier Datensatz als gelesen markieren (Update)
con3 = DriverManager.getConnection(url+dsn3, user3, password3);
System.out.println("Verbinden_3 fuer UPDATE ...... ");
}
catch (SQLException e){
System.out.println("Fehler beim Verbindungsaufbau!");
System.exit(0);
}
catch (ClassNotFoundException e){
System.out.println("JDBC over ODBC nicht gefunden!");
System.exit(0);
}
System.out.println("Verbindungsaufbau erfolgreich\n");
// Ausgabe der Datensätze
try{
// SQL1-Select-Befehl
sql1 = "SELECT * FROM DBTLIB.VRROFP00 WHERE ST1ROF = '1' ORDER BY AOZROF,KIVROF";
// Statement erstellen um SQL1-Befehl auszuführen
stmt1 = con1.createStatement();
// SQL1-Befehl ausführen
result1 = stmt1.executeQuery(sql1);
while (result1.next())
{
// Schreibe Datensatz in Access-DB
aoz = result1.getString("AOZROF");
slo = result1.getString("SLOROF");
iti = result1.getString("ITIROF");
kiv = result1.getString("KIVROF");
mzz = result1.getString("MZZROF");
t01 = result1.getString("T01ROF");
t02 = result1.getString("T02ROF");
t03 = result1.getString("T03ROF");
t04 = result1.getString("T04ROF");
t05 = result1.getString("T05ROF");
t06 = result1.getString("T06ROF");
t07 = result1.getString("T07ROF");
t08 = result1.getString("T08ROF");
t09 = result1.getString("T09ROF");
t10 = result1.getString("T10ROF");
t11 = result1.getString("T11ROF");
t12 = result1.getString("T12ROF");
t13 = result1.getString("T13ROF");
t14 = result1.getString("T14ROF");
t15 = result1.getString("T15ROF");
t16 = result1.getString("T16ROF");
t17 = result1.getString("T17ROF");
t18 = result1.getString("T18ROF");
t19 = result1.getString("T19ROF");
t20 = result1.getString("T20ROF");
st1 = result1.getString("ST1ROF");
st2 = result1.getString("ST2ROF");
st3 = result1.getString("ST3ROF");
st4 = result1.getString("ST4ROF");
st5 = result1.getString("ST5ROF");
trd = result1.getString("TRDROF");
kurzmzz = mzz.trim();
PfadzurVorlage ="C:\\Ordner\\Unterordner\\"+kurzmzz+".vlm";
zudruckenstk = 1;
uebrigestk = 0;
Druckstatus = 1;
messer = 0;
vorschub = 1;
commentar = "";
xpos = 0;
ypos = 0;
serstart = 0;
winkel = 0;
st2 = "0";
st3 = "0";
st4 = "0";
st5 = "0";
sql2 = "INSERT INTO JobList (Layout, PieceCount, Remaining, "
+"Status, Cut, Move, Comment, X_Offset, Y_Offset, "
+"SernoStart, Angle, Text1, Text2, Text3, Text4, Text5, "
+"Text6, Text7, Text8, Text9, Text10, Text11, Text12, "
+"Text13, Text14, Text15, Text16, Text17, Text18, Text19, "
+"Text20, ST1ROF, ST2ROF, ST3ROF, ST4ROF, ST5ROF, AOZROF"
+") VALUES ('"
+PfadzurVorlage+"', "+zudruckenstk+", "+uebrigestk
+", "+Druckstatus+", "+messer+", "+vorschub+", '"+commentar
+"', "+xpos+", "+ypos+", "+serstart+", "+winkel+", '"+t01
+"', '"+t02+"', '"+t03+"', '"+t04+"', '"+t05+"', '"+t06+"', '"+t07
+"', '"+t08+"', '"+t09+"', '"+t10+"', '"+t11+"', '"+t12+"', '"+t13
+"', '"+t14+"', '"+t15+"', '"+t16+"', '"+t17+"', '"+t18+"', '"+t19
+"', '"+t20+"', "+st1+", "+st2+", "+st3+", "+st4+", "+st5
+", '"+aoz
+"')";
// Kontrollausgabe
System.out.println(sql2);
stmt2 = con2.createStatement();
stmt2.executeUpdate(sql2);
// *********************************************************************
// Beginn Update der gelesenen Datensätze in der DB2
sql3 = "UPDATE DBTLIB.VRROFP00 SET ST1ROF = '2' WHERE "
+"AOZROF = '"+aoz+"' AND KIVROF = '"+kiv+"'";
// Kontroll Ausgabe
System.out.println(sql3);
stmt3 = con3.createStatement();
stmt3.executeUpdate(sql3);
// Verbindung_3 wieder schliessen
stmt3.close();
con3.close();
// Ende Update der gelesen Datensätze in der DB2
//**********************************************************************
// Verbindung_2 wieder schliessen
stmt2.close();
con2.close();
}
// Verbindung_1 schliessen
result1.close();
stmt1.close();
con1.close();
}
catch(Exception e) {
System.out.println("Fehler: "+e);
}
}
}
