CL AS400 utilisant SQL et Java
Commande pour lancer un traîtement SQL
Lance cde SQL (JDBC) (SQLJDBC)
Indiquez vos choix, puis appuyez sur ENTREE.
Commande SQL . . . . . . . . . . ____________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
____________________________________________________________________ ...
Fin
F3=Exit F4=Invite F5=Réafficher F12=Annuler F13=Mode d'emploi invite
F24=Autres touches
Prompt de lancement
0001.00 CMD PROMPT('Lance cde SQL (JDBC) ')
0002.00
0003.00 PARM KWD(CMDSQL) TYPE(*CHAR) LEN(1024) MIN(1) +
0004.00 PROMPT('Commande SQL')
0005.00
0006.00 PARM DTA *CNAME 10 CONSTANT(DGBIBLIO) DTAARA(*YES)
0007.00 PARM WKSTN *PNAME 10 CONSTANT(' ') DTAARA(*YES)
Création de la commande
0164.00 /** LANCE COMMANDE SQL
0165.00 CRTCMD &LIB/SQLJDBC PGM(&LIB/SQLCL ) SRCFILE(&LIB/QCMDSRC) +
0166.00 SRCMBR(SQLCMD) TEXT('LANCE COMMADE SQL-JDBC ')
Le CL reçoit la commande SQL en premier paramètre
0001.00 /* LANCE SQL EN MODE JDBC */
0002.00 /* Parametres : */
0003.00 /* - COMMANDE SQL EN MODE JDBC */
0004.00
0005.00 PGM PARM(&CMDSQL &DTA &WKSTN)
0006.00 DCL &CMDSQL *CHAR 1024
0007.00 DCL &TYPE *CHAR 1
0008.00 DCL &DTA *CHAR 10
0009.00 DCL &WKSTN *CHAR 10
0010.00 DCL &LIB *CHAR 10
0011.00
0012.00 /* INTERACTIF SEULEMENT POUR TESTS */
0013.00 /* GOTO OK */
0014.00
0015.00 /* CE CL SE LANCE LUI-MEME EN BATCH */
0016.00 /* TEST SI BATCH */
0017.00 RTVJOBA TYPE(&TYPE)
0018.00 /* SI OUI : TRAITEMENT */
0019.00 IF COND(&TYPE *EQ '0') THEN(GOTO CMDLBL(OK))
0020.00 /* SI NON, ENVOI EN BATCH */
0021.00 RTVDTAARA DTAARA(&DTA (1 10)) RTNVAR(&LIB)
0022.00 RTVJOBA JOB(&WKSTN)
0023.00 SBMJOB CMD(CALL PGM(&LIB/SQLCL) PARM(&CMDSQL &DTA +
0024.00 &WKSTN)) JOB(JDBC_SQL)
0025.00 RETURN
0026.00 /* -------------------------------------------------- */
0027.00 OK:
0028.00
0029.00 RTVDTAARA DTAARA(&DTA (1 10)) RTNVAR(&LIB)
0030.00
0031.00 CALL PGM(&LIB/SBRSQLCL) PARM(&CMDSQL &LIB &WKSTN)
0032.00
0033.00 IF COND(&TYPE *EQ '0') THEN(DO)
0034.00 CALL &LIB/SBRMSGCL PARM('SQL ' &LIB &WKSTN)
0035.00 MONMSG CPF0000
0036.00 ENDDO
0037.00
0038.00 ENDPGM
Routine SBRSQLCL
0001.00 /* LANCE UNE COMMANDE SQL VIA JDBC */
0002.00 /* */
0003.00 /* Parametres : */
0004.00 /* - COMMANDE SQL EN MODE JDBC */
0005.00 /* - BIBLIOTHEQUE */
0006.00 /* - WKSTN */
0007.00
0008.00 PGM PARM(&CMDSQL &LIB &WKSTN)
0009.00 DCL &CMDSQL *CHAR 1024
0010.00 DCL &LIB *CHAR 10
0011.00 DCL &CLASSES *CHAR 256
0012.00 DCL &SYSTEM *CHAR 10
0013.00 DCL &WKSTN *CHAR 10
0014.00
0015.00 ADDLIBLE GUEBEY *LAST
0016.00 MONMSG CPF0000
0017.00
0018.00 RTVNETA SYSNAME(&SYSTEM)
0019.00
0020.00 /* UTILISATION D'UNE CLASSE JAVA */
0021.00 /* ----------------------------- */
0022.00 RTVDTAARA &LIB/DGCLASSES &CLASSES
0023.00 MONMSG CPF0000 EXEC(DO)
0024.00 CHGVAR &CLASSES '/home/guebey/java'
0025.00 ENDDO
0026.00 CHGCURDIR &CLASSES
0027.00 RUNJVA JDBCGeneric PARM(&SYSTEM &CMDSQL) +
0028.00 CLASSPATH('.:/QIBM/PRODDATA/HTTP/PUBLIC/JT400/LIB/JT400.JAR') +
0029.00 OUTPUT(*PRINT)
0030.00 MONMSG JVA0000
0031.00
0032.00 FIN: ENDPGM
La classe Java
/**
Traitement par SQL/Jdbc d'un fichier AS400/iSeries,
@author Dominique Guebey d.guebey@abxlogistics.fr
@param
nom de l'as400,
commande SQL (entre GUILLEMETS)
@version 11 juin 2004
*/
// Utilisation de com.ibm.as400.access de jt400.jar
// cf dans /QIBM/ProdData/HTTP/Public/jt400/lib/
// cf http://codenewbie.com/tutorials/1940.html
// Modifications :
// TODO :
import java.sql.*;
import com.ibm.as400.access.*;
public class JDBCGeneric extends Object
{
public static void main (String[] parameters)
{
// Verifie les parametres :
if (parameters.length < 2) { return; }
if (parameters.length > 2) { return; }
String system = parameters[0];
String sql = parameters[1];
Connection connection = null;
try {
// Definir et ouvrir le fichier en sortie et obtenir la connexion...
AS400 as400 = new AS400(system);
// Load the IBM Toolbox for Java JDBC driver.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
// Get a connection to the database. Since we do not
// provide a user id or password, a prompt will appear.
connection = DriverManager.getConnection ("jdbc:as400://" + system);
//DatabaseMetaData dmd = connection.getMetaData ();
// Query sur le fichier
Statement select = connection.createStatement ();
ResultSet rs = select.executeQuery (sql);
// java.sql.Statement stmt = connection.createStatement();
//ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5)+" "+rs.getString(6)+" "+rs.getString(7)+" "+rs.getString(8)+" "+ rs.getString(9)+" ");
}
}
catch (Exception e) {
// System.out.println ();
// System.out.println ("ERROR: " + e.getMessage());
}
finally {
// Clean up.
try {
if (connection != null)
connection.close ();
}
catch (SQLException e) {
// Ignore.
}
}
System.exit (0);
}
}