Requester cannot establish the connection. Jetty, Lift /Scala, iSeries DB2/400

Question

I’m working my way through the Lift Application Development Cookbook by Gilberto T. Garcia Jr and have run up against a problem I can’t seem to resolve. I’ve copied the source code Chap06-map-table and I’m trying to modify it to work with my IBM i (iSeries, AS/400, i5) database. I was able to make it work with the first type of connection using Squeryl Record. However, I can’t seem to figure how to get this to work using a JNDI Datasource. I’ve spent a couple of days searching the internet for examples of setting this up and have not found a good example involving a DB/400 database connection. Below is the error I get when I attempt to start the container and the code I’ve modified in an effort to make it work. Any help would be appreciated. There seems to be some choices for the data source class from jt4oo.jar (jtOpen) and I’m not sure which would be the best to use or perhaps there’s another. I’ve been trying this with each of the three and am assuming the first is the correct one.

com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource
com.ibm.as400.access.AS400JDBCConnectionPoolDataSource
com.ibm.as400.access.AS400JDBCDataSource

Thanks. Bob

This is the start of the error:

> container:start
[info] jetty-8.0.4.v20111024
[info] No Transaction manager found - if your webapp requires one, please config
ure one.
[info] NO JSP Support for /, did not find org.apache.jasper.servlet.JspServlet
[info] started o.e.j.w.WebAppContext{/,[file:/C:/Users/Bob/Lift26Projects/scala_
210/chap06-map-table/src/main/webapp/]}
[info] started o.e.j.w.WebAppContext{/,[file:/C:/Users/Bob/Lift26Projects/scala_
210/chap06-map-table/src/main/webapp/]}
18:21:47.062 [pool-7-thread-1] ERROR n.liftweb.http.provider.HTTPProvider - Fail
ed to Boot! Your application may not run properly
java.sql.SQLException: The application requester cannot establish the connection
. ("jdbc:as400://www.busapp.com;libraries=PLAY2TEST";naming=system;errors=full;)
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:524) ~[jt
400-6.7.jar:JTOpen 6.7]
        at com.ibm.as400.access.AS400JDBCConnection.setProperties(AS400JDBCConne
ction.java:3142) ~[jt400-6.7.jar:JTOpen 6.7]
        at com.ibm.as400.access.AS400JDBCManagedDataSource.createPhysicalConnect...

My Build.sbt File:

name := "Lift 2.5 starter template"
version := "0.0.1"
organization := "net.liftweb"
scalaVersion := "2.10.0"
resolvers ++= Seq("snapshots" at "http://oss.sonatype.org/content/repositories/snapshots",
          "staging"         at "http://oss.sonatype.org/content/repositories/staging",
          "releases"        at "http://oss.sonatype.org/content/repositories/releases"
            )
seq(com.github.siasia.WebPlugin.webSettings :_*)
unmanagedResourceDirectories in Test <+= (baseDirectory) { _ / "src/main/webapp" }
scalacOptions ++= Seq("-deprecation", "-unchecked")
env in Compile := Some(file("./src/main/webapp/WEB-INF/jetty-env.xml") asFile)
libraryDependencies ++= {
  val liftVersion = "2.5"
  Seq(
    "net.liftweb"       %% "lift-webkit"        % liftVersion        % "compile",
    "net.liftmodules"   %% "lift-jquery-module_2.5" % "2.3",
    "org.eclipse.jetty"     % "jetty-webapp"       % "8.0.4.v20111024"  % "container",
    "org.eclipse.jetty"     % "jetty-plus"         % "8.0.4.v20111024"  % "container",
    "ch.qos.logback"    % "logback-classic"     % "1.0.6",
    "org.specs2"        %% "specs2"             % "1.14"           % "test",
    "net.liftweb"       %% "lift-squeryl-record" % liftVersion % "compile",
    "net.sf.jt400"    % "jt400"       % "6.7",
    "org.liquibase"    %  "liquibase-maven-plugin" % "3.0.2"
  )
}

This is my boot.scala file:

package bootstrap.liftweb

    import _root_.liquibase.database.DatabaseFactory
    import _root_.liquibase.database.jvm.JdbcConnection
    import _root_.liquibase.exception.DatabaseException
    import _root_.liquibase.Liquibase
    import _root_.liquibase.resource.FileSystemResourceAccessor
    import net.liftweb._
    import util._
    import Helpers._
    import common._
    import http._
    import sitemap._
    import Loc._
    import net.liftmodules.JQueryModule
    import net.liftweb.http.js.jquery._
    import net.liftweb.squerylrecord.SquerylRecord
    import org.squeryl.Session
    import java.sql.{SQLException, DriverManager}
    import org.squeryl.adapters.DB2Adapter
    import javax.naming.InitialContext
    import javax.sql.DataSource
    import code.model.LiftBookSchema
    /**
    * A class that's instantiated early and run.  It allows the application
    * to modify lift's environment
    */
    class Boot {
     def runChangeLog(ds: DataSource) {
     val connection = ds.getConnection
       try {
        val database = DatabaseFactory.getInstance().
         findCorrectDatabaseImplementation(new JdbcConnection(connection))
        val liquibase = new Liquibase(
        "database/changelog/db.changelog-master.xml",
        new FileSystemResourceAccessor(),
        database
        )

      liquibase.update(null)
      } catch {
        case e: SQLException => {
         connection.rollback()
         throw new DatabaseException(e)
       }
     }
    }
    def boot {

    // where to search snippet
    LiftRules.addToPackages("code")

    prepareDb()


    // Build SiteMap
    val entries = List(
      Menu.i("Home") / "index", // the simple way to declare a menu

      // more complex because this menu allows anything in the
      // /static path to be visible
      Menu(Loc("Static", Link(List("static"), true, "/static/index"),
        "Static Content")))

    // set the sitemap.  Note if you don't want access control for
    // each page, just comment this line out.
    LiftRules.setSiteMap(SiteMap(entries: _*))

    //Show the spinny image when an Ajax call starts
    LiftRules.ajaxStart =
      Full(() => LiftRules.jsArtifacts.show("ajax-loader").cmd)

    // Make the spinny image go away when it ends
    LiftRules.ajaxEnd =
      Full(() => LiftRules.jsArtifacts.hide("ajax-loader").cmd)

    // Force the request to be UTF-8
    LiftRules.early.append(_.setCharacterEncoding("UTF-8"))

    // Use HTML5 for rendering
    LiftRules.htmlProperties.default.set((r: Req) =>
      new Html5Properties(r.userAgent))

    //Init the jQuery module, see http://liftweb.net/jquery for more information.
    LiftRules.jsArtifacts = JQueryArtifacts
    JQueryModule.InitParam.JQuery = JQueryModule.JQuery172
    JQueryModule.init()

  }

  def prepareDb() {

    Class.forName("com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource")

    val ds = new InitialContext().lookup("java:/comp/env/jdbc/dsliftbook").asInstanceOf[DataSource]

    runChangeLog(ds)

    SquerylRecord.initWithSquerylSession(
      Session.create(
        ds.getConnection,
        new DB2Adapter)
    )
  }


}

This is my jetty-env-xml File

<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure class="org.eclipse.jetty.webapp.WebAppContext">
    <New id="dsliftbook" class="org.eclipse.jetty.plus.jndi.Resource">
   <Arg></Arg>
   <Arg>jdbc/dsliftbook</Arg>
   <Arg>
      <New class="com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource">
         <Set name="serverName">"jdbc:as400://www.[server].com;libraries=PLAY2TEST";naming=system;errors=full;</Set>
         <Set name="user">[user]</Set>
         <Set name="password">[password]</Set>
       </New>
   </Arg>
    </New>
</Configure>

Answer 1

Okay, I've managed to get connected. One problem was the quotation marks in the jetty-env-xml file. And the user name/password I was using apparently did not the authority required to make this work I'm not sure why since this is the same id/password I use for all my iSeries development. So for now, I'm another user profile with security officer authority until I can figure out what's happening or what authorities are required.

Once I got signed on, I was not able to set a library list for the user and this was causing the SQL to fail. It was looking for a library name that was the same as the user ID. For the time being, I've gotten around this issue by creating a new library named the same as the user id.

One other problem here is that even though I'm supplying both the ID and Password, I'm getting prompted to enter the ID/Password before it will connect. The ID and url are filled in but the password always has to be re-keyed.

I've included the current source for the jetty-env-xml file and the boot.scala file. Hopefully this may help others.

Thanks to Dave and James for their help!

Bob

boot.scala:

package bootstrap.liftweb

// import _root_.liquibase.database.DatabaseFactory
// import _root_.liquibase.database.jvm.JdbcConnection
// import _root_.liquibase.exception.DatabaseException
// import _root_.liquibase.Liquibase
// import _root_.liquibase.resource.FileSystemResourceAccessor
import net.liftweb._
import util._
import Helpers._

import common._
import http._
import sitemap._
import Loc._
import net.liftmodules.JQueryModule
import net.liftweb.http.js.jquery._
import net.liftweb.squerylrecord.SquerylRecord
import org.squeryl.Session
import java.sql.{SQLException, DriverManager}
import org.squeryl.adapters.DB2Adapter
import javax.naming.InitialContext
import javax.sql.DataSource
import code.model.LiftBookSchema
import com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource



/**
 * A class that's instantiated early and run.  It allows the application
 * to modify lift's environment
 */
class Boot {
  // def runChangeLog(ds: DataSource) {
  //   val connection = ds.getConnection

  //   try {
  //     val database = DatabaseFactory.getInstance().
  //       findCorrectDatabaseImplementation(new JdbcConnection(connection))

  //     val liquibase = new Liquibase(
  //       "database/changelog/db.changelog-master.xml",
  //       new FileSystemResourceAccessor(),
  //       database
  //     )

  //     liquibase.update(null)
  //   } catch {
  //     case e: SQLException => {
  //       connection.rollback()
  //       throw new DatabaseException(e)
  //     }
  //   }
  // }

  def boot {

    // where to search snippet
    LiftRules.addToPackages("code")

    prepareDb()


    // Build SiteMap
    val entries = List(
      Menu.i("Home") / "index", // the simple way to declare a menu

      // more complex because this menu allows anything in the
      // /static path to be visible
      Menu(Loc("Static", Link(List("static"), true, "/static/index"),
        "Static Content")))

    // set the sitemap.  Note if you don't want access control for
    // each page, just comment this line out.
    LiftRules.setSiteMap(SiteMap(entries: _*))

    //Show the spinny image when an Ajax call starts
    LiftRules.ajaxStart =
      Full(() => LiftRules.jsArtifacts.show("ajax-loader").cmd)

    // Make the spinny image go away when it ends
    LiftRules.ajaxEnd =
      Full(() => LiftRules.jsArtifacts.hide("ajax-loader").cmd)

    // Force the request to be UTF-8
    LiftRules.early.append(_.setCharacterEncoding("UTF-8"))

    // Use HTML5 for rendering
    LiftRules.htmlProperties.default.set((r: Req) =>
      new Html5Properties(r.userAgent))

    //Init the jQuery module, see http://liftweb.net/jquery for more information.
    LiftRules.jsArtifacts = JQueryArtifacts
    JQueryModule.InitParam.JQuery = JQueryModule.JQuery172
    JQueryModule.init()

  }

  def prepareDb() {

    Class.forName("com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource")

    val ds = new InitialContext().lookup("java:/comp/env/jdbc/dsliftbook").asInstanceOf[DataSource]

    // runChangeLog(ds)

    SquerylRecord.initWithSquerylSession(Session.create(ds.getConnection, new DB2Adapter)
    )
  }
}

jetty-env-xml

<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure class="org.eclipse.jetty.webapp.WebAppContext">
    <New id="dsliftbook" class="org.eclipse.jetty.plus.jndi.Resource">
   <Arg></Arg>
   <Arg>jdbc/dsliftbook</Arg>
   <Arg>
      <New class="com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource">
         <Set name="serverName">www.[server].com</Set>
         <Set name="user">DBUSER</Set>
         <Set name="password">DBUSER</Set>
      </New>
   </Arg>
    </New>
</Configure>