HomeActive Query BuilderHow can I create a xml to describe a database schema, from a db's url and the xsd 's url?

How can I create a xml to describe a database schema, from a db's url and the xsd 's url?


Hello,

I'm trying to develop a program in Java, to generate a xml describing a database's schema, just like the file sakila.xml included in the free sample "EventsDemo".


So my program will take these 4 arguments:
the db's url, like "jdbc:mysql://192.168.80.242:3306/mantis"
the db's credentials, like its login & password;
the xsd's url published on activedbsoft.com:

http://www.activequerybuilder.com/schemas/metadata.xsd


How can I find a program to create the xml that I wanted? Do I ever ignore such a standard procedure?

Thanks.
Rosière Fontaine | email
May 18 2009, 05:53 AM
Rosière,

This xml file is created with our component.
Open the GeneralDemo and look for "Metadata->Save to XML..." menu item. See how it's implemented in the code.
Eugene Starkov [ActiveDBSoft] | email
19 hours, 33 mins since original post
Thanks,

I tried GeneralDemo on a MySQL database, but I cannot read the database schema, and this is my Exception trace:

It seems that a getString(0) was used instead of getString(1), but I cannot find any "getString(0)" in the sample's source code.



20 mai 2009 11:00:38 com.adbs.metadata.MetadataObjectFetcherFromResultSet calcObjectName
GRAVE: null
java.sql.SQLException: Column Index out of range, 0 > 1.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkColumnBounds(ResultSet.java:662)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5477)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5420)
at com.adbs.metadata.MetadataObjectFetcherFromResultSet.calcObjectName(tc:44)
at com.adbs.metadata.BaseMetadataObjectsFetcher.loadMetadata(rc:75)
at com.adbs.metadata.LoadMetadataHelpers.loadMetadataObjectsMySQL(ro:437)
at com.adbs.syntax.MySQLSyntaxProvider.loadMetadataObjects(ne:1290)
at com.adbs.ast.BaseSQLContext.loadMetadataObjects(mv:918)
at com.adbs.ast.MetadataContainer.loadMetadataByFilter(gv:1136)
at com.adbs.querybuilder.QueryBuilder.refreshMetadata(sj:1591)
at generaldemo.MainFrame.jMenuItemMySQLActionPerformed(MainFrame.java:742)
Rosière Fontaine | email
1 day, 22 hours since original post
Please try the latest version.
We have released v1.0.6.1006 yesterday.
Eugene Starkov [ActiveDBSoft] | email
1 day, 23 hours since original post
Hello,

I'm still facing the same JDBC exception, even under the GeneralDemo of the latest trial (released 2009/05/19
Version 1.0.6.1006), by the batch: run_GeneralDemo.bat

My MySQL server's version is "4.1.20 via TCP/IP" (displayed in "Server information" of my MySQL Administrator)

My MySQL's JDBC driver is this :Implementation-Title: MySQL Connector/J
Implementation-Version: 5.0.4
Implementation-Vendor: MySQL AB (supplied by its matifest.mf)

Besides, I also tried to run GeneralDemo, with or without my own CLASSPATH that includes my jar for MySQL driver, but nothing changed:



set AQB_HOME=C:\QueryBuilders\ActiveQB\V1.0.6.1006
set AQB_LIB=%AQB_HOME%\lib\ActiveQueryBuilder.jar
set CLASSPATH=%CLASSPATH%;C:\Program Files\MySQL\MySQL Tools for 5.0\java\lib\mysql-connector-java-5.0.4-bin.jar
set CLASSPATH=%CLASSPATH%;%AQB_LIB%

Rosière Fontaine | email
2 days since original post
Could you please dump the schema of your database (without data)?
You can use the following command line:
mysqldump <database> -d --skip-opt
Eugene Starkov [ActiveDBSoft] | email
2 days, 1 hour since original post
I have no access to the Unix System which hosts my MySQL, so I only use MySQL Query Browser to read 3 tables.

My database "mantis" is a traditional base of Mantis Bug reporting tools, under MySQL.

It defines no foreign key or other constraints, seemly.


--schéma:
CREATE DATABASE `mantis` /*!40100 DEFAULT CHARACTER SET latin1 */;


DROP TABLE IF EXISTS `mantis`.`mantis_user_table`;
CREATE TABLE `mantis`.`mantis_user_table` (
`id` int(7) unsigned NOT NULL auto_increment,
`username` varchar(32) NOT NULL default '',
`email` varchar(64) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
`date_created` datetime NOT NULL default '1970-01-01 00:00:01',
`last_visit` datetime NOT NULL default '1970-01-01 00:00:01',
`enabled` int(1) NOT NULL default '1',
`protected` int(1) NOT NULL default '0',
`access_level` int(2) NOT NULL default '10',
`login_count` int(11) NOT NULL default '0',
`cookie_string` varchar(64) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `cookie_string` (`cookie_string`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;




DROP TABLE IF EXISTS `mantis`.`mantis_project_user_list_table`;
CREATE TABLE `mantis`.`mantis_project_user_list_table` (
`project_id` int(7) unsigned NOT NULL default '0',
`user_id` int(7) unsigned NOT NULL default '0',
`access_level` int(2) NOT NULL default '10',
PRIMARY KEY (`project_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `mantis`.`mantis_project_table`;
CREATE TABLE `mantis`.`mantis_project_table` (
`id` int(7) unsigned NOT NULL auto_increment,
`name` varchar(128) NOT NULL default '',
`status` int(2) NOT NULL default '10',
`enabled` int(1) NOT NULL default '1',
`view_state` int(2) NOT NULL default '10',
`access_min` int(2) NOT NULL default '10',
`file_path` varchar(250) NOT NULL default '',
`description` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Rosière Fontaine | email
2 days, 2 hours since original post
Thanks for the detailed information.
That was MySQL 4.1 specific bug.
Please download version 1.0.6.1007.
Eugene Starkov [ActiveDBSoft] | email
2 days, 22 hours since original post
Thank you Eugene.

I used the new version of Active Query Builder.

My MySQL is still the same.

Now I can create the xml for my schema, but I cannot create necessary elements for foreign keys. In fact I have 2 tables:

mantis_user_profile_table,
mantis_user_table, under InnoDB engine, and profile 's user_id references user's id.

However, In my xml created, I find no relation elements, inside my tables:


<table>
&#8722;
<database>
<item case_sens="0" name="emv"/>
</database>
<name case_sens="0" name="mantis_user_profile_table"/>
<system>0</system>
<visible>1</visible>


So did I forget anything?

Thanks.
Rosière Fontaine | email
6 days, 22 hours since original post
Sorry, previously you wrote your tables have no foreign keys.
Do you mean you want to create them manually in the XML file, not in the database?
Eugene Starkov [ActiveDBSoft] | email
6 days, 23 hours since original post
Sorry for my ambiguity.

In fact, I created manually foreign keys in my MySQL, then I tried to recreate a xml for its schema, but I found no <relation> elements in my new xml.




the table for user profile now references the user table's id:


CREATE TABLE `emv`.`mantis_user_profile_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default '0',
.......
PRIMARY KEY (`id`),
KEY `FK_mantis_user_profile_table__user_id` (`user_id`),
CONSTRAINT `FK_mantis_user_profile_table__user_id` FOREIGN KEY (`user_id`) REFERENCES `mantis_user_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Besides, I would also like to know if it is possible to create composite queries, such as "select ..." UNION "select ...", "select ..." MINUS "select ...", or INTERSECT, or subquery with "select ... where id IN select id from ...", how can I build these queries?

Thanks a lot.
Rosière Fontaine | email
1 week since original post
Do you see this manually created foreign key as the link between tables dropped on the query builder?

There is "Union Navigation Bar" in the top-right corner of the diagram pane (looks like "Q" button). Using the context menu of this bar you can create unions and manipulate them.
Eugene Starkov [ActiveDBSoft] | email
1 week since original post
No, I don't see such a like between the tables user & user_profile, when I drag them into query builder.

Rosière Fontaine | email
1 week since original post
[Conversation moved to the support ticket system]
Eugene Starkov [ActiveDBSoft] | email
1 week since original post
This topic is closed