jdbc table create DB1

Post Reply
magnus
Posts: 3
Joined: 15 Oct 2009, 21:04

Hi, Im trying to convert a file repo to use DB2 database.
When Protege is creating the table the SQL doesn't conform with DB2 (probably not oracle either). When VARCHAR is use a length need to be specified.
Where in the Code is the SQL for creating the schema(tables)?

This is the SQL that the GUI produce:
'CREATE TABLE essentialbaseline (frame VARCHAR NOT NULL, frame_type SMALLINT NOT NULL, slot VARCHAR NOT NULL, facet VARCHAR NOT NULL, is_template SMALLINT NOT NULL, value_index INTEGER NOT NULL, value_type SMALLINT NOT NULL, short_value VARCHAR, long_value LONG VARCHAR)'

DB2 need VARCHAR(20) NOT NULL, Also how long does the Column need to be?
magnus
Posts: 3
Joined: 15 Oct 2009, 21:04

This is very easy to fix as mySQL sets default length if VARCHAR(length) not specified as it's in Essential code.

Can I have the source of the create table?

This is the DB Dictionary from mySQL:

Field Type Null Default Comments
frame var(500) No
frame_type smallint(6) No
slot var(500) No
facet var(500) No
is_template bit(1) No
value_index int(11) No
value_type smallint(6) No
short_value varchar(500) Yes NULL
long_value mediumtext Yes NULL
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Hi Magnus,

Thanks for your post. I'm going to have to take this up with the Protege team, so can you give me a copy of the exception message that you're getting, please?

In Protege 3.4, the database backend has been re-implemented completely (for the better) but maybe somethings have changed - I've certainly read of people using DB2 as the database backend for Protege.
Can you confirm which version of Protege that you are using?

In the meantime, it's worth trying Oracle if you have that available, as your post suggested. Failing that, we know MySQL works, as does MS SQLServer.

For some more background, it's the Convert Project to Database Backend process that creates the table and builds all the correct indices and at least there did not used to be a way to set the database table up manually and then get it to use it. I had a similar problem with MS SQLServer2005 with Protege 3.3.1 that I could not resolve without making a custom version of Protege.

I've just been looking through the new code (Protege 3.4) for this and in the new implementation, Protege asks the database (via the JDBC) driver for the correct type to use and asks the database for the MaxCharSize to put into the brackets, e.g. if MaxCharSize for DB2 is 20, the statement should be VARCHAR(20) NOT NULL. It's only the last column that the code doesn't do this for. I'm not clear why the statements for things like the frame column don't have the (20) after the VARCHAR - it's there in the code.
Check that you are using the correct version of the JDBC driver for your database and assuming that you have the correct driver, please post the exact exception message that you're getting and I will investigate with the Protege team.

Thanks

Jonathan
Essential Project Team
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

I think our posts crossed in the ether!

Here is the source - BUT as I say, the Protege code drops any existing table before creating a new one to store your repository. You can create the table, but Protege will zap it before trying to create it again.

As you show - Protege is creating the maximum varchar size parameters for MySQL - the code is the same for all databases. This is what makes me think it's worth looking at your DB2 driver (and possibly any configuration on the DB2 side that might be interfering).

Code: Select all

private void createTable() throws SQLException {
        String createTableString = "CREATE TABLE " + _table + " (";
        createTableString += FRAME_COLUMN + " " + getFrameDataType() + " NOT NULL, ";
        createTableString += FRAME_TYPE_COLUMN + " " + getFrameTypeDataType() + " NOT NULL, ";
        createTableString += SLOT_COLUMN + " " + getFrameDataType() + " NOT NULL, ";
        createTableString += FACET_COLUMN + " " + getFrameDataType() + " NOT NULL, ";
        createTableString += IS_TEMPLATE_COLUMN + " " + getIsTemplateDataType() + " NOT NULL, ";
        createTableString += VALUE_INDEX_COLUMN + " " + getValueIndexDataType() + " NOT NULL, ";
        createTableString += VALUE_TYPE_COLUMN + " " + getFrameTypeDataType() + " NOT NULL, ";
        createTableString += SHORT_VALUE_COLUMN + " " + getShortValueDataType() + ", ";
        createTableString += LONG_VALUE_COLUMN + " " + getLongValueDataType();
        createTableString += ")";
        if (checkMySQLBug()) {
          createTableString += " ENGINE = INNODB";
        }
        try {
            executeUpdate(createTableString);
            if (log.isLoggable(Level.FINE)) {
                log.fine("Created table with command '" + createTableString + "'");
            }
        } catch (SQLException e) {
            StringBuffer buffer = new StringBuffer();
            buffer.append("Failed to create table on database ");
            buffer.append(getCurrentConnection().getDatabaseProductName());
            buffer.append(" with command '");
            buffer.append(createTableString);
            buffer.append("' :");
            buffer.append(e.getMessage());
            Log.getLogger().warning(buffer.toString());
            throw e;
        }
    }
And for reference the getFrameDataType() method:

Code: Select all

private String getFrameDataType() throws SQLException {
      return getCurrentConnection().getVarcharTypeName() + "(" + getCurrentConnection().getMaxVarcharSize() + ")";
    }
I can't see any reason (at the moment) in the code why this wouldn't create the column parameters in DB2 but it does for MySQL or MS SQLServer.

As I say, the full SQL Exception that you're getting from DB2 will help when I take this up with the Protege team.

Jonathan
Essential Project Team
magnus
Posts: 3
Joined: 15 Oct 2009, 21:04

We are using Protege 3.4.1
"DB2 v8.1.0.128", "s061108", "U810099", and FixPak
"14"

Here is the exception:

WARNING: Failed to create table on database DB2/SUN with command 'CREATE TABLE essentialbaseline (frame VARCHAR NOT NULL, frame_type SMALLINT NOT NULL, slot VARCHAR NOT NULL, facet VARCHAR NOT NULL, is_template SMALLINT NOT NULL, value_index INTEGER NOT NULL, value_type SMALLINT NOT NULL, short_value VARCHAR, long_value LONG VARCHAR)' :DB2 SQL error: SQLCODE: -604, SQLSTATE: 42611, SQLERRMC: FRAME -- DefaultDatabaseFrameDb.createTable()
WARNING: Errors at copying knowledgebase jdbc:db2://zddogdb1:50000/DBEASD01 -- com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -604, SQLSTATE: 42611, SQLERRMC: FRAME
at com.ibm.db2.jcc.b.id.e(id.java:1640)
at com.ibm.db2.jcc.b.id.b(id.java:1197)
at com.ibm.db2.jcc.c.fb.i(fb.java:234)
at com.ibm.db2.jcc.c.fb.b(fb.java:50)
at com.ibm.db2.jcc.c.s.b(s.java:36)
at com.ibm.db2.jcc.c.wb.f(wb.java:141)
at com.ibm.db2.jcc.b.id.m(id.java:1194)
at com.ibm.db2.jcc.b.id.a(id.java:1922)
at com.ibm.db2.jcc.b.id.c(id.java:560)
at com.ibm.db2.jcc.b.id.executeUpdate(id.java:545)
at edu.stanford.smi.protege.storage.database.AbstractDatabaseFrameDb.executeUpdate(Unknown Source)
at edu.stanford.smi.protege.storage.database.DefaultDatabaseFrameDb.createTable(Unknown Source)
at edu.stanford.smi.protege.storage.database.DefaultDatabaseFrameDb.ensureEmptyTableExists(Unknown Source)
at edu.stanford.smi.protege.storage.database.DefaultDatabaseFrameDb.overwriteKB(Unknown Source)
at edu.stanford.smi.protege.storage.database.DatabaseKnowledgeBaseFactory.copyKnowledgeBase(Unknown Source)
at edu.stanford.smi.protege.storage.database.DatabaseKnowledgeBaseFactory.copyKnowledgeBase(Unknown Source)
at edu.stanford.smi.protege.storage.database.DatabaseKnowledgeBaseFactory.saveKnowledgeBase(Unknown Source)
at edu.stanford.smi.protege.model.Project.saveDomainKB(Unknown Source)
at edu.stanford.smi.protege.model.Project.save(Unknown Source)
at edu.stanford.smi.protege.ui.ProjectManager.save(Unknown Source)
at edu.stanford.smi.protege.ui.ProjectManager.saveProjectRequest(Unknown Source)
at edu.stanford.smi.protege.action.SaveProject.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231)
at java.awt.Component.processMouseEvent(Component.java:5517)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
at java.awt.Component.processEvent(Component.java:5282)
at java.awt.Container.processEvent(Container.java:1966)
at java.awt.Component.dispatchEventImpl(Component.java:3984)
at java.awt.Container.dispatchEventImpl(Container.java:2024)
at java.awt.Component.dispatchEvent(Component.java:3819)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
at java.awt.Container.dispatchEventImpl(Container.java:2010)
at java.awt.Window.dispatchEventImpl(Window.java:1791)
at java.awt.Component.dispatchEvent(Component.java:3819)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

I will look into the JDBC drivers, let you know what I find.
Thanks.
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Thanks.

I've already raised a request for some feedback on the use of DB2. This will help a lot.

Let me know how you get on with the drivers and I'll keep you posted on what I can find out about this exception

Jonathan
Essential Project Team
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Hi Magnus,

I've had a very good reply from the Protege team about the DB2 issue.

The first point they make is that, actually, DB2 is not supported BUT you can custom-configure Protege to work with DB2, via some rarely used protege properties. These are set using the Protege properties panel which you will find from the File->Preferences menu. Then select the Properties tab.

This is what Timothy from the Protege team says:
For example, if your driver name is

com.ibm.db2.Driver

(I invented that) then setting the property

Database.typename.frame.name.type.com.ibm.db2.Driver=DB2VARCHAR(128)

will set the type for the first column of the database. I took the declarations of these property names from RobustConnection.java and included them below. I think that the important ones are

Database.typename.frame.name.type -- the type used for frame names

Database.typename.bit - a boolean type

Database.typename.short.value.type -- a type for relatively small object

Database.typename.longvarchar - a type for bigger objects

Database.type.varchar.maxsize -- the line between small and bigger objects

The frame name type must be case sensitive (or you will have the expected but possibly manageable problems). The short value type must be able to take a character array of length varchar.maxsize.

The best way to modify the protege properties is with the protege properties tab. When modifying the file directly, the fact that Protege updates the protege.properties file on exit/save can become annoying. If you get good settings I can include them in the default Protege distribution.

-Timothy
I'm not sure what the fully-qualified name of your DB2 JDBC driver class is, but you get the idea from what Timothy says.
You can set the property for each of the problem columns, e.g. the 'frame' and the other 'frame-type' columns to use whatever the correct type should be in DB2.

I'm happy to help if any of this is not clear.

Let me know how you get on

Jonathan
Essential Project Team
Post Reply