How to Quickly Start Using OneWebSQL

Many of you complain that it's difficult to start with OneWebSQL if your system is already running. If you have a DDL file that creates the database, here's what you can do.

Download Oracle Data Modeler

Download and install Oracle Data Modeler. It is a free database modeling tool from Oracle (see license terms). It runs on Windows, Linux, and Mac.

Import DDL File

Oracle Data Modeler can parse DDL files and OneWebSQL can read from Oracle Data Modeler models. Choose File > Import > DDL file...

Select the DDL file(s) you want.

You may skip the Merge Dialog as you are importing into an empty model.

Choose the database you want to use. Oracle Data Modeler supports Oracle, DB2, and MS SQL databases out of the box.

Import the model. Here is a database model from one of our systems. As you can see, Oracle Data Modeler can parse even large database models. (Yes, the Data Modeler can zoom the model in and out.)

Tune the model

Now we have to do some tuning so that the model works with OneWebSQL. First right-click the model name and choose Properties. In the Comments tab, type:

    dbms=DATABASE_TYPE

DATABASE_TYPE can be one of DB2, Oracle, or MSSQL.

If there are views in your model, you will see that the views are not parsed correctly. The column types are missing.

To fix this, double-click the view to open View Properties window. In the window, click the Query button.

The query window will open and the SQL query will be parsed.

Click OK. The view is now parsed correctly.

Unfortunately, I don't know a simpler way to fix views in the model. If you know one, let me know in the comments.

Save the model in the place of your choice. The model is saved in a .dmd file and a folder with the same name. When you specify the location of the model, give the path to the .dmd file but also make sure that the folder is sitting next to it.

Download OneWebSQL

If you haven't already done so, download OneWebSQL for Oracle Data Modeler. Unzip the archive.

    unzip onewebsql-oracle2java-1.0.2.zip

Generate Java files

Now we can generate OneWebSQL files:

    java -jar onewebsql-oracle2java-1.0.2.jar \
          -model_sets <path-to-dmd-file> \
          -java_output_dir generated-sources  \
          -java_default_package com.example \
          -classpath onewebsql-runtime-1.0.2.jar 

If your model has more than 20 tables and views, you'll need OneWebSQL commercial license to generate the code. Add path to license location to code generator options:

    java -jar onewebsql-oracle2java-1.0.2.jar \
          -model_sets <path-to-dmd-file> \
          -java_output_dir generated-sources  \
          -java_default_package com.example \
          -classpath onewebsql-runtime-1.0.2.jar \
          -license_location <path-to-onewebsql-oracle2java-license>

We're done!

You can read more about using Oracle Data Modeler with OneWebSQL in Oracle Data Modeler Guide.

If you use Maven 2 or 3, you might want to try OneWebSQL Maven plugin. If you're still using Maven 1, check out OneWebSQL and Maven 1.x blog post.