Favorite Database Manager (Schema Model Development)

[Paul_22]

One aspect of application design that is not included in the Xi platform is database creation and management. Even though it's easy to import and map almost any external data source, I still need a good database management tool.

For the time being an old copy of DbSchema, but I notice the support forum has almost no activity, (Not a good sign.)

Any suggestions on where I might look next? What are you using?

[Jon]

At Ebase we use a variety of database tools. Squirrel is quite popular http://squirrel-sql.sourceforge.net/.

[Paul_22]

I got Squirrel installed okay, but no matter what I do I can't seem to get it to connect.
I suspect it's not finding the right JDBC driver. (see that littler red X.)



Actually when it comes to finding a database manager that supports both the built in Derby db and Postgres there isn't a whole lot to choose from. Squirrel looks good, but so far no luck.

DBeaver is another open source db manager that works for both. It knew right away that the Derby driver was missing and quickly downloaded it from the web. Got connected right away. http://dbeaver.jkiss.org/screenshots/

DBeaver is good, but so far, I'm liking my original dbSchema best.
http://www.dbschema.com/

I'm definitely liking this new breed of db managers that work with almost every db out there. All it takes is a set of Java JDBC drivers. As compared to the old school db managers like Navicat.com, SQLmanager.net and SQLmaestro.com which use native drivers instead of the newer JDBC drivers.

Another one that caught my eye was dbVisualizer, but for some strange reason, I couldn't even get it to rename a field. I guess doing so, is some kind of corporate taboo. http://www.dbvis.com/features/

[Segi]

Paul,

I use a database front end called HeidiSQL (http://www.heidisql.com/)

It supports MySQL, SQL Server, PostGRE and a few other databases and will let you create,modify tables and views as well as work with existing data in the tables.

[Jon]

The red x on the squirrel screen means the JDBC driver isn't configured, almost certainly it needs the jar file added - you can do this by clicking the Extra class path tab in the driver properties and adding ..UfsServer/tomcat/lib/derby.jar.

[Paul_22]

Making progress... I followed your instructions.
I named the driver 'eBase' and picked the driver from the eBase directory.
But I had to add a 2nd driver to make it work.

..UfsServer/tomcat/lib/derbyclient.jar



(Still configuring the SQuirrel db Manager.)
But I'm not there yet. Created an Alias called 'testDB'
And I'm still having trouble with the URL field.

jdbc:derby:testDB;create=true



I did checked the connection string sample page, like you suggested.
http://db.apache.org/derby/docs/10.7/de ... vdvlp22102

For now, I myself am okay with dbSchema, but other new eBase Xi users may want use Squirrel, which is a good Open Source db Manager. So I'm hoping this post helps them. Plus I'm learning some things too.

http://www.squirrelsql.org/

[Paul_22]

I use a database front end called HeidiSQL (http://www.heidisql.com/)

It supports MySQL, SQL Server, PostGRE and a few other databases and will let you create,modify tables and views as well as work with existing data in the tables.

This looks like another FREE Open Source option. I downloaded the Portable installer, which is just a zip file. When I set that up on my hard drive, my I did a scan with Kaspersky, and all was okay. But than when I went to run the exe, I got a another Kaspersky warning. So I decided not to do an evaluation.

Though... I love that HeidiSQL Fourm drop down dialog off their home page.

[Jon]

Derby comes in two flavors: embedded mode and client mode; there are different jar files and URLs for each. I think you might have these crossed over.

• o embedded mode is used by Ebase as delivered out of the box: it uses derby.jar and URLs like jdbc:derby:voters;create=true
  
  o client mode (also called networked or server) is used when you start derby as a separate process - like most other database systems - it has the advantage that any number of clients can connect. It uses the derbyclient.jar and URLs like jdbc:derby://localhost:1527/voters;create=true

From a usability point of view, the client mode is better. But you have to do a bit of work to set it up. All the derby documentation is online - this is a good starting point https://db.apache.org/derby/docs/10.7/g ... index.html

[Segi]

Paul_22,

What is the warning that you're getting with Kaspersky ? I have been using Heidi SQL for some time now with different anti-viruses and haven't had any kind of false positive warnings and consider it completely safe to use.

[LKirby]

I use Kaspersky on 2 computers at home and it never gets flagged. Maybe it was a download bundled with some malware?

[Paul_22]

It wasn't a serious warning. I was given a choice:
1- I trust this program.
2- Run in restricted mode.
3- Block program entirely.

I probably should have gone with the restricted mode.
I'm sure that would have been okay. But I was in a rush.

In the past I think this kind of warning shows up if the program includes a shell statement, as Kaspersky cannot know what batch commands might be run there. Then too, I was trying out the portable version. Maybe that's why I got that warning message.

Also... looking over the HeidiSQL screenshot page, I didn't see the one screen I always look for, so doing the eval wasn't a high priority.
http://www.heidisql.com/screenshots.php

Now you're probably wondering what that one screen I always look for is. Well, I've did quite a few evaluations recently and I've noticed that SQL Managers differ greatly. Basically some emphasize SQL more than that others. They all produce SQL statements for every operation. But some, like dbVisualizer actually make you run an SQL statement for every operation, even when simply adding a field.

I like to be able to edit a table freely: adding, renaming, and moving fields without having to run an SQL statement each time. I like the fact that all the SQL happens in the background. When I need an SQL statement to create a table from scratch, or to create a Query, I ask for it then. Or if I want to see the SQL on a simple operation, I can just look over at the log window.

BUT the thing I look for MOST in an SQL Manager is a screen where I can not only visualize and print all my table relationships, but literally create foreign key relationships by simply dragging and dropping one field to another. Then the real bonus is when I can create a data browse on the fly, AND have the db manager further create perfectly linked child browse windows with just another click or two.

I'm all for open source software. I often check Portable Apps, as they have one of the best lists. http://portableapps.com/apps But if I can't find exactly what I need, I don't mind paying for a license. My dbSchema license only cost $127. So far that's the best one I've found.

Here's the screen shots of what I was just describing:

[kotinkarwak]

Paul_22,
Did you manage to get this to work.
I am stuck at the same point you indicated where the drivers are registered ok but making a connection fails.

Ebase team, please put together a quick note on how to achieve this.
I am using V5 and running the server using the test ebase i.e.

..\EbaseXi_5_0_beta\UfsClient\IntegratedServer\ebasetest


image: http://screencast.com/t/VGanos2Eok


How do we define the URL?

[Paul_22]

KotinKarwak,

No, I never did get Squirrel working.
I decided to stick with dbSchema which I already paid for.

Though... if I see you get it working, I may try again.

[Jon]

Here are some notes and examples of configuring Squirrel to connect to a Derby database. These can also be applied to any other tool:

Firstly, Derby comes in two flavors: embedded or client (see note above). As distributed, Ebase Xi contains connections to the the repository database and a samples database, these are both embedded.

Embedded mode

Most important: only one concurrent connection is allowed with embedded mode, so you must first shut down the Ebase Designer (Ebase V5.x) and or Ebase Server (Ebase V4.x or Ebase V5.x)

URL format: jdbc:derby:<database>[;create=true]
Jar file: derby.jar

Jar file is configured using the Extra Class Path tab in the Squirrel driver properties dialog. Squirrel will show the driver with a green tick when the correct jar is configured.

Driver configuration example:


Connection example:


Client mode

To use client mode you have to start an external derby server process and change any existing JDBC connection URLs. An example of bat and sh files to start the server are provided in the DB/derby_tools folder. The derby server can also be run as a Windows service - see the apache derby documentation for how to do this.

URL format: jdbc:derby://<server>[:<port>]/<databaseName>[;<URL attribute>=<value>]
Jar file: derbyclient.jar

Jar file is configured using the Extra Class Path tab in the Squirrel driver properties dialog. Squirrel will show the driver with a green tick when the correct jar is configured.

Driver configuration example:


Connection example: