Support > Database usage guide (R7)

Wagby can use various databases. This guide explains notes and troubleshooting when using each database.

1. Deploying the JDBC driver

Save the database driver file provided by each database vendor in the following folder.

$(DEVHOME)/customize/tomcat/lib

For example, when using Oracle, the file Wagby - 7.xx/customize/tomcat/lib/ojdbc7.jar must be copied.

In the built-in database (HSQLDB), since the database driver is already installed, it is not necessary to arrange it.Please do this when using external database.

2. Setting up the database in WagbyDesigner

The "database URL" item is supplemented by the database to be used.
Change the character string portion of "[host name]" to the host name or IP address of the database server and the character string portion of "[DB name]" to the name of the database (prepared in advance).
When installing the Web server and database server on the same machine, specify "hosthost" part as "localhost".

The method of describing the connection URL to each database when the host name is "localhost" and the database name prepared on the database side is "sampledb" is as follows.

Database URL
HSQLDB jdbc:hsqldb:sampledb
PostgreSQL jdbc:postgresql://localhost/sampledb
MySQL jdbc:mysql://localhost/sampledb?useUnicode=true&relaxautoCommit=true
Oracle 9i/10g/11g/12c jdbc:oracle:thin:@localhost:1521:sampledb
SQLServer 2005/2008/2012/2014 jdbc:sqlserver://localhost;databasename=sampledb
DB2 jdbc:db2://localhost:50000/sampledb
DB2 AS/400 jdbc:as400://localhost/wagbydb
DBMaster jdbc:dbmaster:sampledb

3. Full Build

When switching databases, full build is required.
(Full Build is needed only once for the first time you switch databases.After switching databases, you can use ordinary differential builds.)

Therefore, after building the system using the database built in the Wagby trial kit/development kit, when switching to the external database, after setting the external database,Please be sure to perform "Full Build".

4. Connection test

You can verify that you can connect correctly to the external database using the check_db script.

Other work

For users of R7.0 to R7.3.1 only: Renaming JDBC driver file names

If you are using R7.0 to R7.3.1, please change the file name according to the following naming convention after placing the database driver.

Database Database driver file name (after renaming)
PostgreSQL postgresql.jar
MySQL mysql-connector-java.jar
Oracle 9i/10g/11g/12c Ojdbc14.jar (Please rename it to ojdbc14.jar instead of ojdbc5.jar/ojdbc6.jar/ojdbc7.jar)
SQLServer 2005/2008/2012/2014 Sqljdbc.jar (Please rename * sqljdbc4.jar to sqljdbc.jar and use it)
DB2 Db2jcc.jar (Please rename db2jcc4.jar to db2jcc.jar and use it)
Db2jcc_license_cu.jar (when using DB2 Connect, such as connecting to a host)
DB2 AS/400 jt400.jar

In Wagby after R7.3.2, the "rename file name" procedure described here is unnecessary.

When using an external database, the table is created (create table) at the time of executing the init_db script.In Full Build/Differential Build, tables are not created.

Because of this mechanism, it is not necessary to run the init_db script after build (unless there is any change with table definition, more precisely, without adding models or model items).

Depending on the database you use, the conversion rules for the types are different.There are also database specific restrictions.For details, please read the following contents.

  1. Built-in database (HSQLDB)
  2. MySQL 4/5/6
  3. PostgreSQL 8/9
  4. Oracle (9i/10g/11g)
  5. SQLServer 2005/2008/2012/2014
  6. DB2 (Version 8.1)
  7. DBMaster (JDBC driver file and Dialect file for Hibernate are required separately.For details, please contact DBMaker Japan.)

The built application uses the connection pooling mechanism provided by Tomcat.

It is lib/tomcat-dbcp.jar.Its entity is the DBCP of Apache Commons.The version that is shipped depends on the version of Tomcat itself.

For Commons DBCP, this site is helpful.
http://www.limy.org/program/java/commons_dbcp.html

Change to Tomcat JDBC Pool

You can change the connection pooling to Tomcat JDBC Pool.In this case, the value described in the customized and saved configuration file (server.xml) is valid, not the value set in the Wagby repository.

References "What is Tomcat JDBC Pool over Commons DBCP"
http://www.atmarkit.co.jp/ait/articles/1111/07/news212_2.html

Related pages

Recovery method when table creation or deletion script processing fails in the middle

If the init_db script or the import_db script fails in the middle, it is possible that the DDL instruction was executed halfway.

After that, even if deleting the table with the drop_db script does not have the necessary tables and constraints, the deletion also fails.As a result, some tables can not be deleted, resulting in an incorrect state.

In this case, please delete the table manually by using the tool of each database.For details, please read how to use the tools provided by each database.

Impact of command execution order (Application swapping)

For application swapping, it is necessary to execute export_db and drop_db in the old version and import_db in the new version after swapping.

For example, if there was a model that existed in an older version but not in a new version, you can not delete a table unless you use old-version drop_db.

Impact of service activation order

Start Wagby applicationBeforePlease make sure that the database is running.If this order is reversed, the Wagby application can not connect to the database.[Reference database environment> Enable database automatic reconnection]

Fault isolation

If an error occurs when using an external database, please follow the procedure below.

  1. Is the check_db script running?If an error occurs here, you can not connect to the external database.
  2. In general, if it operates, but there is a malfunction in part, check the consistency of the version of the database and the version of the JDBC driver.If the JDBC driver is outdated, it may not work properly.
  3. If it is not the above problem, please send a log file containing an error message to Jasmine Software Support.

How is "DDL" generated by Wagby decided?

Wagby automatically generates DDL such as create table statement and drop table statement from model definition.This uses Hibernate's function.Specifically, it is generated from the Hibernate mapping file (.hbm.xml).

The order of items in DDL is not the item order of the model definition.It will be in the order of Hibernate mapping file.

Where is "DDL" generated by Wagby stored?

The automatically generated DDL file is saved in the following folder.
wagbydesigner/webapps/wagbydesigner/WEB-INF/env/work/dbschema/createddl/

How is "constraint name" generated by Wagby decided?

Hibernate automatically generates the part of "drop constraint FKXXXX" which corresponds to FKXXXXX.Wagby does not control it in particular.

Can I change the output destination of the log file during script execution?

Is possible.For details, please read "Wagby Developer Network (R7)> Setting file> Log handling Other tool logs> Log of database operation script".