Gojko Adzic

Aug 22 2008

DbFit 1.1 released: DB2 and Derby support and native queries

8 responses so far

8 Responses to “DbFit 1.1 released: DB2 and Derby support and native queries”

1. # ACon 25 Aug 2008 at 9:53 am

   Hi.

   I needed a AS400 db environment for a project I’m working on right now, so I created one.
   I will not post it entirely, because it is incomplete (I did not need to implement the getAllProcedureParameters()). Anyway, I think it can be done similarly, using JDBC metadata.

   /**
   * The beginning of an AS400 JDBC connection string.
   */
   private static final String CON_STRING_BEGIN = “jdbc:as400://”;

   /**
   * The AS400 JDBC driver class name.
   */
   private static final String DRIVER_CLASS_NAME = “com.ibm.as400.access.AS400JDBCDriver”;

   /**
   * {@inheritDoc}
   */
   protected String GetConnectionString(final String dataSource) {
   return CON_STRING_BEGIN + dataSource;
   }

   /**
   * {@inheritDoc}
   */
   protected String GetConnectionString(final String dataSource, final String database) {
   return CON_STRING_BEGIN + dataSource + “/” + database;
   }

   /**
   * {@inheritDoc}
   */
   protected String getDriverClassName() {
   return DRIVER_CLASS_NAME;
   }

   /**
   * {@inheritDoc}
   */
   public Map getAllColumns(final String tableOrViewName) throws SQLException {
   String[] qualifiers = NameNormaliser.normaliseName(tableOrViewName).split(“\\.”);

   String schemaName = null;
   String tableName = null;

   if (qualifiers.length == 2) {
   schemaName = qualifiers[0];
   tableName = qualifiers[1];
   } else {
   tableName = qualifiers[0];
   }

   return readIntoParams(schemaName, tableName);
   }

   /**
   * Constructs a map columnName-DbParameterAccessor for the given table.
   *
   * @param schemaName The database schema name. Can be null.
   * @param tableName The table’s name.
   * @return Map (column name – DbParameterAccessor pairs).
   *
   * @throws SQLException When there’s a problem while retrieving the metadata of the given table.
   */
   private Map readIntoParams(final String schemaName, final String tableName) throws SQLException {

   DatabaseMetaData metaData = currentConnection.getMetaData();
   ResultSet columns = null;
   if (schemaName != null) {
   columns = metaData.getColumns(null, schemaName, tableName, “%”);
   } else {
   columns = metaData.getColumns(null, “%”, tableName, “%”);
   }

   Map allParams = new HashMap();
   int position = 0;
   while (columns.next()) {
   String columnName = columns.getString(“COLUMN_NAME”);
   String dataType = columns.getString(“TYPE_NAME”);

   DbParameterAccessor dbp = new DbParameterAccessor(columnName, DbParameterAccessor.INPUT,
   getSqlType(dataType), getJavaClass(dataType), position++);
   allParams.put(NameNormaliser.normaliseName(columnName), dbp);
   }

   columns.close();
   return allParams;
   }

   I also have a few questions for you:

   1. Why isn’t AbstractDbEnvironment a public class, such that anybody can subclass it if needed? I had to create a copy of it in order to be able to use its code. I think it would be a good idea to make it public.

   2. Why don’t you use the JDBC metadata to create some generic way to get the column names of a table, and the procedure parameters? This way, a user should only provide implementation for getConnectionString() and getDriverClassName(). I think you probably have some reasons for not doing this (like drivers not supporting some of these features?) but it would be a good addition. If it works, than anybody with compliant driver could use this kind of generic DbEnvironment very easily.

   Thank you.

2. # gojkoon 25 Aug 2008 at 2:04 pm

   Hi,

   thanks for the AS400 environment, i’ll add it to the source. Can you send me getSqlType and getParameterPattern implementations as well?

   1. AbstractDbEnvironment is now public
   2. I ran into tons of small inconsistencies with generic driver access, and I wanted to provide full support for db-specific features. I guess that implementing a JDBC-generic environment that has some sort of greatest common denominator functions should not be too hard. I’ll try to do it for the next release.

3. # ACon 26 Aug 2008 at 9:14 am

   Well, I’m not quite an AS400 specialist. Initially, I copied them from the DB2Environment, because AS400 uses a version of DB2.

   I’ve been searching a little on the internet today, and I think there are some small differences, so I’ll post here these types. Please be advised that these are not necessarily the correct types… For the time that I used these settings, it seems to work, but I don’t guarantee anything. If they fail somehow, I’ll write a reply here.

   /**
   * String types.
   */
   private static final List STRING_TYPES = Arrays.asList(new String[] {“VARCHAR”, “CHAR”, “CHARACTER”, “GRAPHIC”,”VARGRAPHIC”, “TIME” });

   /**
   * Integer types.
   */
   private static final List INT_TYPES = Arrays.asList(new String[] {“SMALLINT”, “INT”, “INTEGER” });

   /**
   * Long types.
   */
   private static final List LONG_TYPES = Arrays.asList(new String[] {“BIGINT”});

   /**
   * Float types.
   */
   private static final List FLOAT_TYPES = Arrays.asList(new String[] {“FLOAT”, “REAL”});

   /**
   * Double types.
   */
   private static final List DOUBLE_TYPES = Arrays.asList(new String[]{“DOUBLE”});

   /**
   * Decimal types.
   */
   private static final List DECIMAL_TYPES = Arrays.asList(new String[] {“DECIMAL”, “DEC”, “NUMERIC” });

   /**
   * Date types.
   */
   private static final List DATE_TYPES = Arrays.asList(new String[] {“DATE”});

   /**
   * Timestamp types.
   */
   private static final List TIMESTAMP_TYPES = Arrays.asList(new String[]{“TIMESTAMP”});

   /**
   * Ref. cursor types.
   */
   private static final List REF_CURSOR_TYPES = Arrays.asList(new String[]{});

   The parameter regex – I just copied it from DB2Env. As I said, I don’t need to call procedures, just some basic stuff (insert, query etc).

4. # ACon 26 Aug 2008 at 9:37 am

   Another question: do you think there’s something to be done about DatabaseTest not being a DoFixture anymore? Maybe the fitlibrary developers will agree to make interpretTables() a non-final method back again.

   There’s a handy setSystemUnderTest() method in DoFixture which allows practically to hand over the flow to another fixture. Now I can’t use it anymore…

5. # gojkoon 26 Aug 2008 at 10:26 am

   I’ve already logged a complaint to fitnesse developers about interpretTables. if that’s fixed it will be easy to go back to dofixture.

6. # ACon 26 Aug 2008 at 11:32 am

   A suggestion: maybe create a way to initialize the connection from a classpath file besides the current connectUsingFile().

   Because I needed something like that, I’ve already added this in my AS400Environment:

   /**
   * Connects to the database using a file from the classpath.
   *
   * @param filePath The path to the properties file, starting from a location in the classpath.
   *
   * @throws SQLException if connecting fails.
   * @throws IOException if something went wrong while parsing the properties file.
   */
   public void connectUsingClasspathFile(final String filePath)
   throws SQLException, IOException {
   DbConnectionProperties dbp = DbConnectionProperties.CreateFromString(getLinesFromClasspathFile(filePath));
   if (dbp.FullConnectionString != null) {
   connect(dbp.FullConnectionString);
   } else if (dbp.DbName != null) {
   connect(dbp.Service, dbp.Username, dbp.Password, dbp.DbName);
   } else {
   connect(dbp.Service, dbp.Username, dbp.Password);
   }
   }

   /**
   * Gets a list of lines from the properties file located in the classpath at the given location.
   *
   * @param filePath The path to the properties file, starting from a location in the classpath.
   * @return A list of lines from the properties file, to be used by
   * {@link DbConnectionProperties#CreateFromString(List)}
   *
   * @throws IOException if something went wrong while parsing the properties file.
   */
   private List getLinesFromClasspathFile(final String filePath) throws IOException {
   BufferedReader br = new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream(filePath)));
   List lines = new ArrayList();
   String line;
   while ((line = br.readLine()) != null) {
   lines.add(line);
   }
   return lines;
   }

   You can probably move getLinesFromClasspathFile() to DbConnectionProperties.

   Thank you.

7. # easternWahooon 14 Oct 2008 at 8:41 pm

   Hi,

   I’m new to DBfit, and to Fitnesse, so bear with me here… How can I make DbFit use a connection which I supply? I want Spring to configure and supply a “dataSource”, and let DbFit use it to setup and rollback data. The dbfit pdf implies this can be done, but I don’t see how, unless it means creating my own implementation of the DBEnvironment, and giving that to the DbEnvironmentFactory.

   In the DbFit pdf, it states, “In standalone mode, the connection properties are stored in the public DefaultEnvironment singleton field inside dbfit.DbEnvironmentFactory. You can initialise it from your own fixtures if you want to pass an existing database connection (to make sure that your .NET tests are using the same transaction ” as DbFit fixtures).”

   What am I missing?

   Thanks for your help!

8. # gojkoon 14 Oct 2008 at 10:22 pm

   You can instantiate the appropriate type of DbEnvironment and pass your connection into it, then set the DefaultEnvironment to that DbEnvironment. The connection fields should be public/settable from outside. if not for that particular environment, let me know and i’ll modify the source.

Leave a Reply

Name (required)

Mail (hidden) (required)

Website