ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Question about JDBC?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Re: Question about JDBC?

    Are you testing out the example on your pc or your I-Series.

    You are receiving that message because you did not set your classpath. Your program does not know where to access the class.
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

    Comment


    • #17
      Re: Question about JDBC?

      Hi,

      I tested out on the Client Access AS400 Navigator by browse to the IFS and right click "Run the java program". I put the 'sqljdbc.jar' at Root and my test program in a folder called mytest, it contain two files connectURL.class, connectURL.java


      When I run it, I special the classpath '/sqljdbc.jar and the error come up like:

      RUNJVA CLASS('connectURL') PARM(*NONE) CLASSPATH('/sqljdbc.jar') CHKPATH(*WARN) OPTIMIZE(10) INTERPRET(*OPTIMIZE) PROP(*NONE) GCHINL(2048) GCHMAX(*NOMAX) OPTION(*NONE)

      java.lang.NoClassDefFoundError: connectURL
      java/lang/Throwable.<init>(Ljava/lang/StringV+4 (Throwable.java:85)
      java/lang/Error.<init>(Ljava/lang/StringV+1 (Error.java:41)
      java/lang/NoClassDefFoundError.<init>(Ljava/lang/StringV+1 (NoClassDefFoundError.java:38)
      Java program completed with exit code 1





      The connectURL is like below:

      import java.sql.*;

      public class connectURL {

      public static void main(String[] args) {

      // Create a variable for the connection string.
      String connectionUrl = "jdbc:microsoft:sqlserver://localhost:1433;" +
      "databaseName=daybook;user=TEST;password=test" ;

      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {
      // Establish the connection.
      Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
      con = DriverManager.getConnection(connectionUrl);

      // Create and execute an SQL statement that returns some data.
      String SQL = "SELECT * FROM dayh01";
      stmt = con.createStatement();
      rs = stmt.executeQuery(SQL);

      // Iterate through the data in the result set and display it.
      while (rs.next()) {
      System.out.println(rs.getString(4) + " " + rs.getString(6));
      }
      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
      e.printStackTrace();
      }
      finally {
      if (rs != null) try { rs.close(); } catch(Exception e) {}
      if (stmt != null) try { stmt.close(); } catch(Exception e) {}
      if (con != null) try { con.close(); } catch(Exception e) {}
      }
      }
      }

      Comment


      • #18
        Re: Question about JDBC?

        Ok, that code is not going to run on your I-Series for a whole bunch of reasons.

        Lets take this a step at a time shall we?

        1.) Install the jdk 5.0 onto your PC.
        2.) Install a good text editor onto your PC.
        3.) Set the path and classpath for jdk 5.0.
        4.) Create a simple program ON PC to see if we can connect to the SQL Server.

        Once we have the program running on your PC environment we will move it over to the I-Series.
        Predictions are usually difficult, especially about the future. ~Yogi Berra

        Vertical Software Systems
        VSS.biz

        Comment


        • #19
          Re: Question about JDBC?

          I got the jdk and texpad installed on PC, how to edit the classpath for jdk? and what is the next? how can I run it on PC?

          Comment


          • #20
            Re: Question about JDBC?

            just check on my PC command prompt by typing JAVA -VERSION and it shows

            1.5.0_07 runtime Environment Standard edition.

            Is that OK?

            Comment


            • #21
              Re: Question about JDBC?

              Yes, check your PATH settings in your envirnoment variables (Located under System Properties --> Advanced) and make sure that it points to the bin directory of where you loaded the jdk 5.0.

              It should contain a line similiar to the following:
              .;%SystemRoot%;C:\Program Files\Java\jdk1.5.0_07\bin

              Be sure to add this variable to the end of whatever already exists in your path settings. Seperate each path variable with a ;

              If the environment variable CLASSPATH does not exist create it.

              You will want to include the location of any jar file that you plan on using to compile your programs. I.e.

              .;C:\Documents and Settings\kpmac\Desktop\biz\ext\mssqlserver.jar;C:\ Documents and Settings\kpmac\Desktop\biz\ext\msutil.jar;C:\Docum ents and Settings\kpmac\Desktop\biz\ext\msbase.jar;

              as well as any folder that you use to store you java programs.
              Last edited by kpmac; June 28, 2006, 02:52 PM.
              Predictions are usually difficult, especially about the future. ~Yogi Berra

              Vertical Software Systems
              VSS.biz

              Comment


              • #22
                Re: Question about JDBC?

                OK, the first thing we want to do after downloading our jar files and the jdk environment is to write a class that establishes a connection to our MS SQL Server database.

                I have posted a connections convenience class that I use as a central app to connect to my SQL Server databases as well as establishing a Sql connection to my I-Series.

                Download the following programs and test them out in your environment. Once you have them working we will proceed to creating the statements to execute our SQL code. Let me know if you have any problems.

                Note: You will need to have the jt400.jar file in addition to the SqlServer jars added in your class path to get this to compile.

                You also have to change the AS400_HOST,SQL_HOST,AS400_USER. . . to mimic your current environment.

                PHP Code:

                /**
                * Convenience class that returns various AS400 connections as well as
                * connections to AS400 JDBC and SQL Server JDBC.
                **/


                import java.sql.Connection;
                import java.sql.DriverManager;
                import java.sql.SQLException;
                import com.ibm.as400.access.AS400;
                import java.util.Properties;

                public class 
                MyConnections {

                    public static final 
                String AS400_HOST "myas400.ip.com";

                    private static final 
                String AS400_USER "myAs400User";

                    private static final 
                String AS400_PWD "MyAs400Password";

                    public static final 
                String SQL_HOST "mySqlServer.ip.com";

                    private static final 
                String SQL_USER "mySqlServerUser";

                    private static final 
                String SQL_PWD "mySqlServerPassword";

                    public static final 
                String SQL_JDBC_DRIVER "com.microsoft.jdbc.sqlserver.SQLServerDriver";

                    public static final 
                String TOOLBOX_JDBC_DRIVER "com.ibm.as400.access.AS400JDBCDriver";

                    private static final 
                String PROP_DRIVER_KWD "driver";

                    private static final 
                String PROP_DRIVER_VAL "native";

                    public 
                MyConnections() {
                    }

                    
                /**
                     * Returns AS400 connection
                     */

                    
                public static AS400 getAs400() {
                        
                AS400 as400 = new AS400(AS400_HOSTAS400_USERAS400_PWD);
                        return 
                as400;
                    }

                    
                /**
                     * Returns AS400 connection
                     */

                    
                public static AS400 getAs400(String userString password) {
                        
                AS400 as400 = new AS400(AS400_HOSTuserpassword);
                        return 
                as400;
                    }

                    
                /**
                     * Returns AS400 connection
                     */

                    
                public static AS400 getAs400(String hostString userString password) {
                        
                AS400 as400 = new AS400(hostuserpassword);
                        return 
                as400;
                    }

                    
                /**
                     * Returns SQL Connection to an AS400 Database Uses default parms.
                     */
                    
                public static Connection getAs400SqlConnection() throws SQLException,
                            
                ClassNotFoundException {
                        return 
                getAs400SqlConnection(AS400_HOSTAS400_USERAS400_PWD);
                    }

                    
                /**
                     * Returns SQL Connection to an AS400 Database Using parms passed in.
                     */

                    
                public static Connection getAs400SqlConnection(String hostString user,
                            
                String passthrows SQLExceptionClassNotFoundException {
                        
                Connection conn null;
                        
                Properties jdbcProperties = new Properties();
                        Class.
                forName(TOOLBOX_JDBC_DRIVER);
                        
                jdbcProperties.put(PROP_DRIVER_KWDPROP_DRIVER_VAL);
                        
                jdbcProperties.put("user"user);
                        
                jdbcProperties.put("password"pass);
                        
                conn DriverManager.getConnection("jdbc:as400://" host,
                                
                jdbcProperties);

                        return 
                conn;

                    }

                    
                /**
                     * Returns SQL Connection to MS SqlServer Database
                     */
                    
                public static Connection getSqlConnection() throws SQLException,
                            
                ClassNotFoundException {
                        return 
                getSqlConnection(SQL_HOSTSQL_USERSQL_PWD);
                    }

                    
                /**
                     * Returns SQL Connection to MS SqlServer Database
                     */
                    
                public static Connection getSqlConnection(String hostString user,
                            
                String passthrows SQLExceptionClassNotFoundException {
                        
                Connection conn null;
                        Class.
                forName(SQL_JDBC_DRIVER);
                        
                conn DriverManager.getConnection(
                                
                "jdbc:microsoft:sqlserver://" hostuserpass);
                        return 
                conn;
                    }


                Use this class to Test the above code.
                PHP Code:
                /**
                * Simple class to test the SQL Connections returned
                * From the MyConnections.class
                **/

                import java.sql.Connection;
                import java.sql.SQLException;


                public class 
                TestMyConnections {

                    public static 
                void main(String[] args) {

                        try {
                            
                Connection conn MyConnections.getAs400SqlConnection();
                            
                System.out.println("The System catalog is: " conn.getCatalog());
                            
                conn.close();
                            
                conn null;

                            
                conn MyConnections.getSqlConnection();
                            
                System.out.println("The System catalog is: " conn.getCatalog());
                            
                conn.close();
                            
                conn null;

                        } catch (
                SQLException sqle) {
                            
                sqle.printStackTrace();
                            
                System.exit(1);
                        } catch (
                ClassNotFoundException cnf) {
                            
                cnf.printStackTrace();
                            
                System.exit(1);
                        }

                    }

                Attached Files
                Last edited by kpmac; June 29, 2006, 11:57 AM.
                Predictions are usually difficult, especially about the future. ~Yogi Berra

                Vertical Software Systems
                VSS.biz

                Comment

                Working...
                X