ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Table Update Example

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

  • Table Update Example

    Does anyone have an ISeries Table update program?

    I need to do an update on an ISeries (DBU) table via the Web Browser (with validation "login" of course).

    Nothing fancy .. just workable.

    Anyone? Anyone? Bueller?

  • #2
    Re: Table Update Example

    I could work you up a quick little example of one using Servlets, JSP, and sql. Unless you need it in CGI instead...just let me know if you want something. Its not very hard to do

    I would have to sit down tonite and do it...
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

    Comment


    • #3
      Re: Table Update Example

      Don't want you to waste a bunch of time, just want something to play with for the connections and updates. Nothing special. Just need to play with it and see whether or not everything here is setup to use it.

      I'd rather start building it for here if we can use it.

      Comment


      • #4
        Re: Table Update Example

        Do you have an APP server set up that would handle an EAR file? If so I can code up a small example or even just send you some stuff I might already have.

        I've done this stuff in java. I would be updating the table with SQL and using javascript and java to do validation. Is this what you are after?
        Your future President
        Bryce

        ---------------------------------------------
        http://www.bravobryce.com

        Comment


        • #5
          Re: Table Update Example

          Yes, I believe we do. But, due to SOX compliancy, we're locked down tighter than as400pro's butt on Halloween in the slammer.

          I can get the admin to help set stuff up if I don't have it... but, right now, I can't say for sure. That's why I wanted to just "test" it and see what happens.

          Comment


          • #6
            Re: Table Update Example

            Here is a java class that makes a connection to an AS400 DB. Now, you can change this to receive the username and password as parms if you want. That would be very easy.

            Code:
            /*
             * Created on Nov 28, 2007
             *
             * TODO To change the template for this generated file go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            package CollapseTablePackage;
            
            import java.sql.*;
            /**
             * @author bmartin
             *
             * TODO To change the template for this generated type comment go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            public class ConnDB 
            {		
            	
            //	--------------------------------------------------------------------------------------------------------------------------//
            //	- Open an AS400 database connection
            //	--------------------------------------------------------------------------------------------------------------------------//
            
            	    public static java.sql.Connection opnAS400Conn() throws Exception {
            
            	    String url = "myDomain";
            		String libraryName = "*LIBL";
            		String userName = "UserName";
            		String password	= "Password";
            
            		java.sql.Connection cn = null;
            
            	  // Register the driver
            		Class.forName("com.ibm.as400.access.AS400JDBCDriver");
            
            	  //  Create the database connection
            		cn = DriverManager.getConnection(
            		     "jdbc:as400:" + url + "; translate binary=true; naming=system; " +
            		     "extended metadata=true;libraries=" + libraryName + "; date format=iso",
            		     userName,
            		     password);
            
            	  // Return the connection object
            		return cn;
            	  }
            }
            Below I have a Result Set class that uses the above connection class to return a result set. You pass it the sql statement as a string. You can make this not return a result set and just do a nice update This class also does some command line stuff too incase you were wondering how to do that form your web app.

            Code:
            /*
             * Created on Dec 3, 2007
             *
             * TODO To change the template for this generated file go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            package CollapseTablePackage;
            import java.sql.*;
            
            /**
             * @author bmartin
             *
             * TODO To change the template for this generated type comment go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            public class RS {
            	
            	public static ResultSet myRS(String sqlRS, Connection myConn) throws Exception
            	{
            		ResultSet rs = null;
            		
            		try
            		{
            			Statement chgqaqqini = myConn.createStatement();
            			chgqaqqini.executeUpdate("CALL QSYS/QCMDEXC('CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QUSRSYS) OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES) CST(*YES) TRG(*YES)',0000000098.00000)");
            			chgqaqqini.executeUpdate("UPDATE QTEMP/QAQQINI SET QQVAL='*YES' WHERE QQPARM='IGNORE_DERIVED_INDEX' OR QQPARM='MESSAGES_DEBUG'");
            			chgqaqqini.executeUpdate("CALL QSYS/QCMDEXC('CHGQRYA QRYOPTLIB(QTEMP)', 0000000024.00000)");
            	        java.sql.Statement stm = myConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            	        rs = stm.executeQuery(sqlRS);
            		}
            		catch(Exception e)
            		{
            			e.printStackTrace();
            		}
            
            		return rs;
            	}
            
            }
            And finally, brining it all together....

            Code:
            /*
             * Created on Jan 2, 2008
             *
             * TODO To change the template for this generated file go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            package CollapseTablePackage;
            import java.sql.*;
            import java.util.*;
            
            /**
             * @author bmartin
             *
             * TODO To change the template for this generated type comment go to
             * Window - Preferences - Java - Code Style - Code Templates
             */
            public class RetrieveInfo {
            	
            	public static List getOrders(String ord1, String ord2, String ord3)
            	{
            		List OrderBeanList = new ArrayList();
            		StringBuffer sql = new StringBuffer(100);
            		ResultSet ECH = null;
            		String order = "";
            		String lines = "";
            		String po = "";
            		String custnum = "";
            		
            		sql.append("select hord, hlins, hcpo, hcust from bpcsffg/ech where hord=");
            		sql.append(ord1);
            		sql.append(" or hord=");
            		sql.append(ord2);
            		sql.append(" or hord=");
            		sql.append(ord3);
            		sql.append(" order by hord");
            		try
            		{
            			Connection opnConn = ConnDB.opnAS400Conn();
            			ECH = RS.myRS(sql.toString(), opnConn);
            			while(ECH.next())
            			{
            				OrderBean row = new OrderBean();
            				row.setOrder(order = ECH.getString("hord"));
            				row.setLineCount(ECH.getString("hlins"));
            				row.setPurchaseOrder(ECH.getString("hcpo"));
            				row.setCustomerNumber(ECH.getString("hcust"));
            				
            				OrderBeanList.add(row);
            			}
            		}
            		catch(Exception e)
            		{
            			e.printStackTrace();
            		}
            		return OrderBeanList;
            	}
            	
            	public static List getLines(String order)
            	{
            		List LineBeanList = new ArrayList();
            		
            		return LineBeanList;
            	}
            
            }

            This will return a list of beans, but the important part is the creation of the sql statment, and the execution inside a try/catch.

            Its probably a little bit more than you need, but its what I've got. If you need an example of a servlet used to implement this stuff just let me know, I have one of those laying around too
            Your future President
            Bryce

            ---------------------------------------------
            http://www.bravobryce.com

            Comment


            • #7
              Re: Table Update Example

              Thanks Bryce! Mucho' Grasssy Assss!

              I'll look it over and see what I can do. What did you use for the URL Call and where do I need to place the classes? Anyplace specific?

              Comment


              • #8
                Re: Table Update Example

                I don't know how much you know about writing java web apps. But if you use a servlet, which you should, you would set up the deployment descriptor to handle the url. What you would do is basically this...

                1. Create a JSP with a form to enter a username and a password.
                2. OnSubmit of the form you would call the Validate servlet or whatever you want to call it.
                3. The servlet's doPost (use post and not get since you are passing sensitive data) you would call the method in the RetrieveInfo class which calls the connection class and the RS class as well.
                4. Then, when you want to run it you would just run the JSP. As long as everything is set up correctly it should fall right into place.

                The jsp has to be in the WEB-INF folder. The java classes go inside a package. You can name the package whatever you want, but you should (for most cases) bundle them in the same package so they can be easily seen.

                I'm at home right now, but tomorrow I could throw together an EAR file and just upload that to here or email it to you at lunch, which would be about 9 or 10am for you. Then you would just have to install the EAR file to the app server.

                If you need anything else, just let me know.
                Your future President
                Bryce

                ---------------------------------------------
                http://www.bravobryce.com

                Comment


                • #9
                  Re: Table Update Example

                  Thanks!

                  I'll start playing with it when I get to work tomorrow. I'm at home now drinking beer and taking shots of Hot @#%@#%@#%@#%! Yea Team!

                  I sure am hoping that Oklahoma gets their asses kicked around tonight. But, that's just me.

                  Comment


                  • #10
                    Re: Table Update Example

                    How are you making out? I whipped up a little example that you can play with. I haven't tested it, but it gives me no compile errors in WDSC. I just pulled apart some code I have. If you have any questions you can just ask away.

                    Its an EAR file. I can try to upload it. It would probably be easier if I could email it to you though. Just let me know.
                    Your future President
                    Bryce

                    ---------------------------------------------
                    http://www.bravobryce.com

                    Comment


                    • #11
                      Re: Table Update Example

                      FaStone - he's gonna want your vote in 20 yrs for this you realise !!!

                      Greg Craill: "Life's hard - Get a helmet !!"

                      Comment


                      • #12
                        Re: Table Update Example

                        Yea, I know. But, I'll expect a Cabinet position for it!

                        Comment

                        Working...
                        X