Re: [Lift] Connecting to iSeries (AS400) DB2 using jt400.jar

[AGYNAMIX Torsten Uhlmann]

Bob,

You should create a lib/ folder below the root of your project, but not in web app.

So if your project directory is "as400_app", create a folder "as400_app/lib".

sbt should pick up the jar file from there and add it to the resulting WAR file or to the internal Jetty execution.

Let me know if that helps,

Torsten.

Am 19.02.2013 um 05:46 schrieb b...@busapp.com:

I'm trying to do a proof of concept to determine if using Lift is a viable option to improve our web client interface for users. I've read a lot of the scala and lift tutorials but am by no stretch of any imagination any kind of an expert. I have been able to create some running lift applications using the lift-basic archetype and I've been able to modify it to include many of the examples from Simply Lift. One of the biggest issues of using non IBM based tools in our iSeries environment is connecting the data base and being able to CRUD.

I'm now trying to create a connection to our iSeries host by modifying the default boot.scala file and adding some information to the defaults.prop file in the lift-basic archetype. I'm getting an error and cannot figure what I need to do. Since there seems to be a vacuum of information related to Lift and iSeries DB2 connections on the net, I'm hoping someone will take this as a challenge and help me out.

I've modified the default.props file to the folling:

db.url=jdbc:as400://www.domain.com; database name=bntemp
db.user=username
db.password=password
db.driver=com.ibm.as400.access.AS400JDBCDriver

and I've placed the jt400.jar file containing the JDBC driver in a new folder: c:/(projectPath)\src\main\webapp\WEB-INF\lib

This is all that I thought I would have to do. But, I'm getting the following error that in the past with other tools indicates that the driver file (jt400.jar) is not in the path where it can be found. If this is the case here and/or if there my be other things I'm missing or not understanding.

Appreciate any help.

Thanks

Bob

.
.
.

19:08:54.618 [pool-6-thread-1] ERROR net.liftweb.db.StandardDBVendor - Unable to

 get database connection. url=jdbc:as400://www.busapp.com; database name=bntemp,

 user=bob

java.sql.SQLException: No suitable driver found for jdbc:as400://www.busapp.com;

 database name=bntemp

        at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.7.0]

        at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.7.0]

        at net.liftweb.db.StandardDBVendor$$anonfun$createOne$4.apply(DB.scala:1

143) ~[lift-db_2.9.1-2.5-M4.jar:2.5-M4]

.
.
.

--
--
Lift, the simply functional web framework: http://liftweb.net
Code: http://github.com/lift
Discussion: http://groups.google.com/group/liftweb
Stuck? Help us help you: https://www.assembla.com/wiki/show/liftweb/Posting_example_code
 
---
You received this message because you are subscribed to the Google Groups "Lift" group.
To unsubscribe from this group and stop receiving emails from it, send an email to liftweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[b...@busapp.com]

Hi, Torsten

Thanks for the reply.

Your suggestion seems to have helped me get a little farther along but it is not working yet. I seem to be able to get connected but am now getting what appears to be a strange error (see below).  I appears to be looking for a file named "BOB" in library QSYS when I believe it should be looking for a file named "USER" in library BNTEMP. The file (USER) for this app does not exist in the library but I'm assuming that Mapper should create it.

Maybe you have another suggestion.

Thanks

Bob

> container:start

[info] jetty-8.1.7.v20120910

[info] NO JSP Support for /, did not find org.apache.jasper.servlet.JspServlet

[info] started o.e.j.w.WebAppContext{/,[file:/C:/Users/Bob/Lift25M4Projects/lift

_basic/src/main/webapp/]}

[info] started o.e.j.w.WebAppContext{/,[file:/C:/Users/Bob/Lift25M4Projects/lift

_basic/src/main/webapp/]}

22:53:26.922 [pool-20-thread-6] ERROR n.liftweb.http.provider.HTTPProvider - Fai

led to Boot! Your application may not run properly

java.sql.SQLException: [SQL0204] BOB in QSYS type *LIB not found.

        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:703) ~[jt

400.jar:JTOpen 7.7]

        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:669) ~[jt

400.jar:JTOpen 7.7]

        at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatem

ent.java:1586) ~[jt400.jar:JTOpen 7.7]

        at com.ibm.as400.access.AS400JDBCStatement.execute(AS400JDBCStatement.ja

va:1967) ~[jt400.jar:JTOpen 7.7]

        at net.liftweb.mapper.Schemifier$.net$liftweb$mapper$Schemifier$$maybeWr

ite(Schemifier.scala:208) ~[lift-mapper_2.9.1-2.5-M4.jar:2.5-M4]

.

.

.

        at java.lang.Thread.run(Unknown Source) [na:1.6.0_39]

[info] Started SelectChann...@0.0.0.0:8080

[success] Total time: 20 s, completed Feb 19, 2013 10:53:26 PM

>

 

[AGYNAMIX Torsten Uhlmann]

Bob,

I wasn't sure you wanna use Mapper. Mapper does not have build in support for a AS400 database- I take it's different from DB2?

You may wanna use a persistence mechanism specific to your database, which you totally can in Lift. Lift doesn't build on Mapper or Record (the other persistence mechanism), you can use plain JDBC or JPA, if you like. Here's a (a bit aged) description on how to integrate JPA with Lift: http://exploring.liftweb.net/master/index-12.html

I know there are a couple threads in the mailing list. I have never used JPA with Lift myself but others do successfully.

Torsten.

[b...@busapp.com]

Okay, thanks. I was expecting to hear something like that. I'll take a look at your link.  Coming from mid-range IBM, I'm not much into JAVA. I have done a little in the past.  Hopefully, I can figure it out. From what I've seen, I like Lift.

[AGYNAMIX Torsten Uhlmann]

Glad I could help.

May I suggest you start with a simple stand alone Java application that connects to your AS400 DB. After you figured this out in a simple environment you could port the code to Scala and then integrate it into Lift…

Torsten.

[Marcin Mielżyński]

I was struggling with an app that works with AS400/DB2 using Mapper some years ago, I think I can help a bit here.

First. the schema that seems to work (it also works with schemifier)

object DB2AS400DriverType extends DriverType("DB2 UDB for AS/400") {
  def binaryColumnType = "LONG VARCHAR FOR BIT DATA"
  def booleanColumnType = "SMALLINT"
  def clobColumnType = "LONG VARCHAR"
  def dateTimeColumnType = "TIMESTAMP"
  def dateColumnType = "DATE"
  def timeColumnType = "TIME"
  def integerColumnType = "INTEGER"
  def integerIndexColumnType = "INTEGER NOT NULL GENERATED BY DEFAULT AS IDENITY"
  def enumColumnType = "BIGINT"
  def longForeignKeyColumnType = "BIGINT"
  def longIndexColumnType = "BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY"
  def enumListColumnType = "BIGINT"
  def longColumnType = "BIGINT"
  def doubleColumnType = "DOUBLE"

  override def brokenLimit_? : Boolean = true
  // override def pkDefinedByIndexColumn_? = true
  override def defaultSchemaName : Box[String] = Full("SOMESCHEMA")
}

Then it boot:

DriverType.calcDriver = conn => {
   val meta = conn.getMetaData
   (meta.getDatabaseProductName, meta.getDatabaseMajorVersion, meta.getDatabaseMinorVersion) match {
      // case (DB2ZOSDriverType.name, _, _) => DB2ZOSDriverType
      case (DB2AS400DriverType.name, _, _) => DB2AS400DriverType
   }
}

It's also possible to add automatic pagination (in your custom MetaMapper type):

    private def _addOrdering(params: List[QueryParam[A]]): String = {
        params.flatMap{
            case OrderBy(field, order, nullOrder) =>
            Nil
            case OrderBySql(sql, _) => List(sql)
            case _ => Nil
        } match {
            case Nil => ""
            case xs => " ORDER BY " + xs.mkString(" , ")
        }
    }

    override def addEndStuffs(in: String, params: List[QueryParam[A]], conn: SuperConnection): (String, Box[Long], Box[Long]) =
        conn.driverType.name match {
            case "DB2 UDB for AS/400" =>
                    val ord = _addOrdering(params)
                    val max = params.foldRight(Empty.asInstanceOf[Box[Long]]){(a,b) => a match {case MaxRows(n) => Full(n); case _ => b}}
                    val start = params.foldRight(Empty.asInstanceOf[Box[Long]]){(a,b) => a match {case StartAt(n) => Full(n); case _ => b}}
                    val ret = (max, start) match {
                        case (Full(max), Full(start)) => startMaxDB2(in, ord, start) + " AND B.internal$rownum <= " + (start + max)
                        case (Full(1), _) => in + ord + " FETCH FIRST ROW ONLY"
                        case (Full(max), _) => in + ord + " FETCH FIRST " + max + " ROWS ONLY"
                        case (_, Full(start)) => startMaxDB2(in, ord, start)
                        case _ => in + ord
                    }
                    (ret, Empty, Empty)

            case _ => super.addEndStuffs(in, params, conn)
        }

    private def startMaxDB2(sql: String, ord: String, start: Long) =
        "SELECT B.* FROM (SELECT A.*, row_number() over (" + ord + ") AS internal$rownum FROM (" + sql + ") A ) B WHERE B.internal$rownum > " + start