creating database tables

[HarryDeBoer]

Hi

is there a way to create database tables/schema's in the embedded Derby database from within the XiDesigner ? Also for other databases (MS SQL)?

[Jon]

Harry,

There is a Create Database Table icon on the toolbar within the Database Resource editor. You can use this to create a table from the Database Resource (if the Database Resource represents a single table), or you can use it to just run your own SQL.

If you're doing very much of this, you are probably better of converting to use networked Derby instead of embedded, then you can attach a client to it, set up your own schema, and do anything you want. There should be a new forum post on this in the next couple of days in section Technical How To Documents, but it's a relatively easy thing to do.

Regards
Jon

[Steve]

Hi Harry,

You can create the tables from the Database Resource, create database table.

Create a database resource.
Select the database you wish to create the table on from the list.
Add a field to the resource fields.
Write something in both the select and table boxes.
Press the 'create database table' on the toolbar at the top of the frame.

This will open a box filled in with create table....
You can type your own SQL into here also and click 'Submit SQL' to execute the SQL.

Kind regards

Steve

[HarryDeBoer]

Ok,

-I created a database (embedded derby -copied ebase_samples to harry_samples)
-created a resource: first added two fileds (resource fields editor) pressed create table and added some more fileds in the create table sql. Table succesfully created.

Then I noticed that in my form I only saw the two fields (of course correct because in the resource there were only two). I then wanted to recreate the resource (to fetch all fields) with the db schema wizard, but I can't see my table. Probably because of a lack of a schema.

Can I create schema's or am I missing something/did something wrong?

[Jon]

The schema seems to be taken from the connection user name. For example if I create a table using the schema wizard from the EBASE_SAMPLES database connection, it creates it in the EBASE_SAMPLES schema. If you look at the connection definition in tomcat\conf\Catalina\localhost.ufs.xml, you'll see that the user name is ebase_samples - I think this is being used as the schema name. Same should apply for your database connection.

[HarryDeBoer]

Hi Jon,

Alas I'm not seeing a schema with the same name (harry_samples). In what directory could I find the database, tables, etc ?

[Jon]

The database should be in UfsServer/DB/harry_samples but I don't think that will help. Have you set up a new Database Connection for harry_samples? If yes, what is the user name that you are connecting with - this should be the schema name. Also, when you go into the schema wizard, which schema names do you see displayed?

[HarryDeBoer]

Hi,

completely forgot about this post. Sorry for that. However, I cannot (don't know how) create a new database for Derby embedded (or network) on my Windows machine. I cannot find the technical document that should appear in the Technical Howto documents (see Jon's post) too. Could you provide that?

[Steve]

Hi Harry,

You can create a new database just simply by appending create=true to the JDBC url.

You will need to modify the ufs.xml file inside the directory:

<Ebase_Install_Dir>/UfsServer/tomcat/conf/Catalina/localhost

You will need to add a new resource:

e.g
To create a new database called using Derby embedded HARRY_TEST

Code: Select all

<Resource name="jdbc/HARRY_TEST" auth="Container"
              type="javax.sql.DataSource"
              username="harry" password="harry"
              driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
              url="jdbc&#58;derby&#58;HARRY_TEST;create=true"
              max="30" min="5" 
              factory="com.ebase.jndi.DataSourceFactory"
	          validationQuery="SELECT 1 FROM SYSIBM.SYSDUMMY1" checkLevel="1" preparedStatementCache="0"
	      />

For Derby server:

Code: Select all

   <Resource name="jdbc/HARRY_TEST" auth="Container"
              type="javax.sql.DataSource"
              username="harry" password="harry"
              driverClassName="org.apache.derby.jdbc.ClientDriver"
              url="jdbc&#58;derby&#58;//localhost&#58;1527/HARRY_TEST;create=true;"
              max="30" min="0"
              factory="com.ebase.jndi.DataSourceFactory"
	          validationQuery="VALUES 1" checkLevel="2" preparedStatementCache="0"
	      />

Note that the database is created only if it does not exist.

I hope this helps.

Steve

[HarryDeBoer]

Thanks Steve. Works fine!

[Hovik]

Harry,

I have added a new document which you my find useful.

http://forum.ebasetech.com/forum/viewto ... =2345#2345

Regards,
Hovik

[Dave]

...or you could always do this sort of thing:

Code: Select all

var samplesDB = "EBASE_SAMPLES";

// DDL statements to be executed
//
var tableName = "ES120913_COUNTRIES";
if (!dbExists(tableName)) {
	// table doesn't exist so push your DDL statements onto the array and build the     
   // database
   var ddl = [];
   ddl.push("create table ES120913_COUNTRIES(id int not null, country_name varchar(100))");
   ddl.push("insert into ES120913_COUNTRIES (id, country_name) values (1, 'Argentina')");
   ddl.push("insert into ES120913_COUNTRIES (id, country_name) values (2, 'Japan')");
   ddl.push("insert into ES120913_COUNTRIES (id, country_name) values (3, 'Iran')");
   
   execute(ddl);
}

var tableName = "ES120913_CITIES";
if (!dbExists(tableName)) {
	// table doesn't exist so push your DDL statements onto the array and build the     
   // database
   var ddl = [];
   ddl.push("create table ES120913_CITIES(id int not null, city_name varchar(100), country_id int)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (1, 'Buenos Aires', 1)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (2, 'Mendoza', 1)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (3, 'Rosario', 1)");

   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (4, 'Osaka', 2)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (5, 'Kobe', 2)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (6, 'Kyoto', 2)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (7, 'Nagoya', 2)");

   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (8, 'Tehran', 3)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (9, 'Yadz', 3)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (10, 'Esfahan', 3)");
   ddl.push("insert into ES120913_CITIES (id, city_name, country_id) values (11, 'Shiraz', 3)");

   execute(ddl);
}

// function to test if a database table exists, returns true if it does
//
function dbExists(tableName) {
   var con, rs, stmt, metaData;
   try {
      con = system.getDatabaseConnection(samplesDB);
      metaData = con.getMetaData();
      rs = metaData.getTables(null, null, tableName.toUpperCase(), null);
      if (rs.next()) {return true;}
      else {return false;}
   }
   catch (error) {
   	  log(error);
   }
   finally {
	    if (rs) rs.close;
      if (stmt) stmt.close;
		  if (con) con.close;
   }	
}

// function to execute an array of DDL statements
//
function execute(ddl) {
   var con, stmt;
   try {
     con = system.getDatabaseConnection(samplesDB);
	  stmt = con.createStatement();
     ddl.forEach(function(s) { 
         try {
	        stmt.execute(s);
	        system.transactionManager.commitAndRestartTransaction();
         }
         catch (error) {
            log (error);
         }
      });
   }
   catch (error) {
   	  log(error);
   }
   finally {
	    if (stmt) stmt.close();
        if (con) con.close();
   }
}

which is how the samples set up local database tables.

Dave