OS2 World.Com Forum

Subject  :  Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  09 Dec, 2006 on 12:57
Hi,

I've got experience with rexxsql that it is awesome to use together with Sybase SQL Anywhere 9 and ODBC connection (yea, I know, on another OS). The thing is that I've written 3 rexx graphical apps that are useful in every day work¹ and would like to do something similar on eComStation.

I'd therefore like to ask you for some help to find a database engine with a graphical tool to manage the database, tables, write sql statements etc. and that it can be reached through ODBC.


====
Setup: Graphic ( Native, Java or Odin )
it would also be nice if the db would work out of the box too.
If command line only, please do tell me/explain how to do it in eCS-OS/2 and not require me to find a proper version "somewhere on the net" of sh and sed etc.

Client-Server communication: odbc
( so that I can use rexxsql on the client to select, insert, delete records )

====
I tried DB2 Personal Edition with the latest fixpacks but found out that it doesn't allow inbound connections, so the huge download was a waste of time.

I've looked at some interesting alternatives, but need some help then to understand how to use them...

Tried Postgres 7.3.x that had to be tweaked and the scripts rewritten to work, the instuctions @ OS2Voice was helpful to some extent though, but it still complained.
Postgres 8.x doesn't explain how to use it on eCS-OS2, add tables etc. just as MySQL, textmode and "you're on your own"

Mvh / Wkr / MfG
//Jan-Erik


¹Software testing of a range of concurrent versions of a MPS system mean that I have to switch between several versions of an app every day.
I've got one tool to deregister/register settings on the fly and start the MPS app that the other testers can only imagine, another tool to determine problems in the products and verify the problem on each version, and the last one to file reports to the right person. All these apps use odbc and databases to handle both the MPS system with it's database and additional databases to help me.


Subject  :  Re:Looking for >useful< database
Author  :  Fahrvenugen
Date  :  09 Dec, 2006 on 15:46
I was just going to suggest either Postgre or MySQL, both of which have OS/2 ports. But it seems you've already looked at those. Anyways, I've been running MySQL on OS/2 for a while and it works well. As for an administration interface, I've used PHPMyAdmin through a web browser (set up a web browser - Apache or Web/2, along with PHP and you can install PHPMyAdmin). The benefit of either of these is because they are open source and available on a number of systems, in the future if you want to move the database to another server on another platform, all you really have to do is a data export and import.

Anyways, you can grab what you need from Paul Smedley's website at:

http://smedley.info/os2ports/index.html


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  09 Dec, 2006 on 17:50
Thank you for the advice...

The "extras" ("A" and "P" in AMP4eCS http://www.amp4ecs.de ) may very well do it but hope to avoid it as I don't want to be forced to install another webserver on my machines just for that and don't want to force users of the app-to-be, to have to do the same. It's to much M$.

How about real admin apps for MySQL and Postgres?

Mvh / MfG / Wkr
//Jan-Erik

I guess I'll never turn into a textmode l*nix user, I like the OS/2-eCS way and Drag& Drop to much for that.


Subject  :  Re:Looking for >useful< database
Author  :  ecsguy
Date  :  09 Dec, 2006 on 18:39

Jan-Erik (09 Dec, 2006 12:57):
Postgres 8.x doesn't explain how to use it on eCS-OS2, add tables etc. just as MySQL, textmode and "you're on your own"

The main PostgreSQL plastforms have a admin GUI called "pgAdmin". It is coded in C/C++ and also uses WxWidgets. OS/2-eCS has a port of WxWidgets so maybe pgAdmin can be compiled for OS/2-eCS.

I have setup PostgreSQL on eCS before. I have also setup my database on Linux or mswin systems then backed up and restored the database to PostgreSQL running on OS/2-eCS.

You can also admin PostgreSQL using pgAdmin from a virtual machine on the OS/2-eCS computer or from a second computer. Another option you could do without inslalling anything on your hard drive, boot Knoppix Live CD on a second machine then use pgAdmin in Knoppix to admin PostgreSQL on OS/2-eCS.


Subject  :  Re:Looking for >useful< database
Author  :  ecsguy
Date  :  09 Dec, 2006 on 18:57
http://www.pgadmin.org/

pgAdmin III is available for:
Linux
FreeBSD
Mac OSX
Windows 2000, XP and 2003.
Note that the pgInstaller distribution of PostgreSQL for Windows
includes a bundled copy of pgAdmin.

http://www.pgadmin.org/development/

Hopefully someone can compile it for OS/2-eCS


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  09 Dec, 2006 on 19:36
Hmm, I've now (finally) found this one:

PGCup written in JAVA
http://gborg.postgresql.org/project/pgcup/download/download.php

I have now seen the commands in it how to create tables and users etc.

a) A few years old
b) Compact
c) Display commands
d) Easy to use (Took me only minutes to dowload-and-get-up-and-running)
e) Behave/Look like similar tools
f) Seems to handle alot of the aspects related to db-administration
g) Use TCP/IP
h) Generate event dispatch problems at times

( Tested with Postgres 8.1.x and GoldenCode Java 1.4.1 )

Mvh / MfG / Wkr
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  09 Dec, 2006 on 19:54
Thank you for your reply...

It may be possible to port pgAdmin III to OS/2-eCS as well since we've got wxWindows.

Mvh / Wkr / MfG

---
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  Terry tgindy@yahoo.com
Date  :  09 Dec, 2006 on 20:09
While this may or may not be helpful. it doesn't hurt to be reminded of Douglas Clark's in-depth eCS-OS/2 specific 12-part database series titled, "ODBC on OS/2," on OS/2 eZine a few years ago that begins with the May 16, 2001 issue at http://www.os2ezine.com/backissues.html

The ODBC on OS/2 Part 5 article on PostgreSQL (then at v7.1) is at http://www.os2ezine.com/20010916/page_7.html

Here's some of Douglas Clark's part 5 article: "Among the so called 'open source' SQL databases available on OS/2 - PostgreSQL, mySQL, miniSQL - PostgreSQL is feature-wise vastly superior in every way. PostgreSQL also compares very favorably with commercial databases, at least in the feature and design areas."


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  09 Dec, 2006 on 20:59

Jan-Erik (09 Dec, 2006 19:36):
Hmm, I've now (finally) found this one:

PGCup written in JAVA
http://gborg.postgresql.org/project/pgcup/download/download.php

I have now seen the commands in it how to create tables and users etc.

a) A few years old



For a more current pure Java alternative, you may consider IBM's freely downloadable Cloudscape and/or open source Apache Derby. If you know DB2 SQL commands, you will find that you already know how to manage either of the above mentioned alternatives.

Please, see the following previous reply and elaborate if either CloudScape and/or Derby would be something you may want to try:

< http://www.os2world.com/cgi-bin/ultraboard/UltraBoard.cgi?action=Read&BID=12&TID=1071&P=1&SID=yycXNMM8qGmRD13V#ID12 >

This is a snapshot (under WebSphere Application Server Community Edition) of what you are getting:

http://www.chingonazo.com/wasceDerbyView.JPG

This database engine is used in vendor offerings, ranging from Sun Microsystems to Oracle, in some of their Web services software development platforms.

...and yes, there is ODBC support also, see:

< http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0409cline2/#N10209 >


Jan-Erik (09 Dec, 2006 19:36):
b) Compact
c) Display commands
d) Easy to use (Took me only minutes to dowload-and-get-up-and-running)
e) Behave/Look like similar tools
f) Seems to handle alot of the aspects related to db-administration
g) Use TCP/IP
h) Generate event dispatch problems at times

( Tested with Postgres 8.1.x and GoldenCode Java 1.4.1 )

Mvh / MfG / Wkr
//Jan-Erik



Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  11 Dec, 2006 on 19:00
Thank you for the suggestion...

I'll try it out.

I do know some SQL, but want the app to do the work for me as with pgcup. GUI is preferred

Mvh / Wkr / MfG
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  obiwan
Date  :  11 Dec, 2006 on 23:44
Someone help me out here. Why doesn't Lotus Approach satisfy the requirements?

Subject  :  Re:Looking for >useful< database
Author  :  SEV0600 sev0600@verizon.net
Date  :  12 Dec, 2006 on 02:11
Lotus Approach doesn't meet Jan-Erik's last requirement... it cannot be reached using ODBC.

Subject  :  Re:Looking for >useful< database
Author  :  obiwan
Date  :  12 Dec, 2006 on 02:50
Sorry I meant Approach just for the client, with a Postgres or MySQL server backend through ODBC.

Subject  :  Re:Looking for >useful< database
Author  :  Terry tgindy@yahoo.com
Date  :  12 Dec, 2006 on 16:33
Just remembered DbVisualizer, a Java-based universal database tool, now at v5.1, might be useful.

It is listed "eCS-OS/2 compatible" at http://www.os2world.com/java/

Current features include a graphical query builder, graphing, table creation, SQL Commander, and new support for Derby.

http://www.minq.se/products/dbvis/

"DbVisualizer is a feature rich, intuitive and cross platform database tool for developers and DBA's providing a single powerful interface for a variety of databases. DbVisualizer supports simultaneous database connections, it lets you explore and manage database objects, execute SQL queries, visualize information and a lot more."

DbVisualizer's Forum states the coming v5.2 will strictly require Java 1.5 in a user survey. There are older versions available depending upon the underlying Java subsystem.

Visualizer Query might have a fit here except it's been withdrawn from sale & development by the folks who bought it from IBM. Too bad Visualizer Query is not a part of eComStation supplemental applications pack since it is the only database query tool with intentional Workplace Shell integration.

http://www.os2ezine.com/20011116/page_4.html


Subject  :  Re:Looking for >useful< database
Author  :  obiwan
Date  :  12 Dec, 2006 on 21:13
From an an article on OS/2 Ezine:

http://www.os2ezine.com/20010416/ssapproach.html



Database Connectivity
Approach comes with ODBC Administrator which allows you to connect to a variety of databases including Btrieve, DB2, dBase, Informix 5, Oracle 7 and Sybase. I also have a driver for Postgres SQL from the Postgres SQL server installation. Unfortunately there isn't an ODBC driver for MySQL; although this isn't Lotus' problem, it is still frustrating.

Through ODBC, Approach supports entering SQL queries and then processing the results, so you could use Approach for your reporting needs if you run a SQL database such as for managing your website.



Subject  :  Re:Looking for >useful< database
Author  :  Terry tgindy@yahoo.com
Date  :  12 Dec, 2006 on 22:22
[1] Near the bottom of "ODBC on OS/2 - Part 1," Douglas Clark, provides instructions & pictures for connecting Lotus Approach to a SQL database through ODBC.

http://www.os2ezine.com/20010516/odbc1.html

[2] The article also defines Lotus Approach as a "Local" database.

Databases come in two basic flavors:

* Local, or sometimes called "desktop" - designed to be accessed (only) on the machine where the database resides. Examples are dBase, Paradox, and Approach.

* Client/server - where the database resides on one machine and the users of the database are on client machines. The machine the database is on is called the database server, and may or may not be the same machine as the network server. The operating system used on the database server and the client machines may or may not be the same.

(Some databases, like Watcom SQL/Sybase SQL Anywhere, function both ways: as a local database the application talks directly to the database engine, as a client/server the application talks to a client.)


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  13 Dec, 2006 on 13:12
The reason I'd like to use a remote server<->ODBC<->rexx is that I'm quite skilled with rexx, DrDialog and have written stuff that will generate large amounts of data that I need to store and later retrieve and communicate with others, preferrably in the same way across the world and applications.

One would e.g. think that rexx is the scripting language in DBExpert, but the rexx implementation just work as some sort of layer on top of the original scripting language. I need it to interact with the system, other rexx applications and possibly other apps.

I've used Approach and am quite found of the capabilities to create graphs, but LotusScipt make it limited to itself, just as the absence of server capablilites mentioned above.

Mvh / MfG / Wkr
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  Terry tgindy@yahoo.com
Date  :  13 Dec, 2006 on 22:07
I have taken, perhaps a thinking out loud interest, in this "useful database" thread, and here is why...

First of all, eComStation will be the preferred workstation. Period!

As a part of my research, resources have been slowly gathered for a SOHO network with: eCS 1.2R (soon 2.0) with a KVM in some type of coherent system that will talk with each other, and; part of my looking at in virtualization with a preferred eCS host instead of an eCS guest for those win-apps still required for business.

Over the past few years, I've gathered OS/2-native resources for SQL & ODBC databases such as VX-Rexx Client/Server, and; VisualAge Enterprise Editions for Visualage Generator/Smalltalk, Java (for JDBC), and TeamConnection Server (for component-driven access sorted by DB2) for which a DB2/2 Workgroup Edition is targeted for installation. OS/2-native Lotus Go, Domino, and Notes have also been secured for experimentation.

The thought here was to find the latest OS/2-native versions, with available & detailed documentation, that work reasonably well together in the Workplace Shell.

Isn't eBay a great place to garner OS/2 resources when nobody else is looking?

So, there is a method to my madness. Perhaps in a year or so, I'll be in a position to write an article(s) for the OS/2 community.

This was all inspired from the "ODBC on OS/2" series in OS/2 eZine. Where was I thinking earlier without remembering part 12 - ODBC ACCESS IN OS/2? ...at http://www.os2ezine.com/20020416/page_7.html

At the article's beginning we see...

"There are two products for Rexx that provide access to ODBC databases, neither of which is sold anymore: VX-Rexx and VisPro-Rexx. There is currently no method for getting at ODBC data apart from those two products. However the is a library of data access routines called RexxSQL put out by Mark Hessling that provide a common (though not ODBC) interface to DB2, Oracle, mSQL, mySQL, and SQL Anywhere."

"Sibyl is a Delphi clone that works on OS/2. It builds Borland-like Turbo Pascal applications using the same interface and parts as Borland's Delphi 2. It is very compatible with Delphi and supposedly you can take Delphi source (Pascal language, forms, etc.) and recompile with Sibyl on OS/2. When you are finished building your application you end up with a stand-alone executable that can be moved to any Warp machine. Sibyl is very complete. It has interfaces to TCP/IP, the OS/2 Control Program API, Rexx, multimedia and more. You can build very nice applications with Sibyl, as NewView by Aaron Lawrence shows."

GET THIS: "Sibyl can work with any ODBC enabled database."

It also comes with a number of drivers for different databases that bypass ODBC and allow Sibyl to work directly with that database: DB2/2, dBase, mySQL, Oracle 7, SQL Anywhere, and Paradox. I have not tested any of these. But in theory, for instance, you could use Sibyl to write applications for mySQL which does not have an ODBC driver (that works) in OS/2."

"Sibyl addresses databases through a concept it calls a database alias. This database alias is very similar to a data source in ODBC, meaning that an alias is the association of an arbitrary name with a database driver. The driver can be either one of Sibyl's built in drivers or it can be an ODBC data source - if the alias is an ODBC datasource the alias must have the same name as the ODBC data source name."

"The easiest way to create database aliases is with the DBExplorer tool. This little jack-of-all-trades tool allows you to create a database alias, browse the attributes of a database, and to submit SQL statements to a database."

Here's another Sibyl-article at http://www.os2ezine.com/20001216/sibyl.html

There is one other way for ODBC access not mentioned in the article which is VisualAge Generator/Smalltalk. Sibyl is no longer in development, but its successor, WDSibyl is now under Netlabs at http://svn.netlabs.org/wdsibyl/wiki.

Now, the SOHO adventure begins...


Subject  :  Re:Looking for >useful< database
Author  :  oli
Date  :  14 Dec, 2006 on 19:50

Terry (12 Dec, 2006 22:22):
[2] The article also defines Lotus Approach as a "Local" database.

Databases come in two basic flavors:

* Local, or sometimes called "desktop" - designed to be accessed (only) on the machine where the database resides. Examples are dBase, Paradox, and Approach.


That is correct, however it is possible to connect to Approach using ODBC and using it as a multi user remote database, it's not an ideal application but a number of people do this when they need to convert an existing app to multi user and/or remote purposes.

For new applications this would not be recommended since a good SQL would give you transaction control, what people tend to forget is that some of the open source SQL databases like My and M SQL do not offer transaction control and are thus no safer than Approach as a multi user database.


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  17 Dec, 2006 on 17:04

El Vato (09 Dec, 2006 21:37):
For a more current pure Java alternative, you may consider IBM's freely downloadable Cloudscape and/or open source Apache Derby. If you know DB2 SQL commands, you will find that you already know how to manage either of the above mentioned alternatives.

Thank you for the suggestion, but as with the rest of them... no GUI admin tool a very large NoNo when I need to configure/modify/administrate it myself. It may be very handy if it can be handled automatically with a rexx .cmd-file from the app-to-be later on though.

I'll try out the older DBVisualizer 4.0.4 to see if it can make up for that, otherwise I have to ask you for some more suggestions.

Mvh / MfG / Wkr
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  19 Dec, 2006 on 00:20

Jan-Erik (17 Dec, 2006 17:04):

Thank you for the suggestion, but as with the rest of them... no GUI admin tool a very large NoNo when I need to configure/modify/administrate it myself.



Well, there is actually a GUI front end in beta testing (that I know of), although it is not supported under OS/2 due to the fact that it is SWT based, please see: <
http://www-128.ibm.com/developerworks/db2/downloads/csworkbench/#Download >
Notwithstanding, due to the flexibility that CloudScape/Derby provides, I will suggest a couple of options. Quite possibly, even if you do not find either of them appealing, those might provide you with some insight.

You might run the SWT WorkBench front end on Linux (I will never consider WinXX for anything other than trivial stuff but you may have a different take). In this particular example, I executed the networks server on OS/2 but ran the beta SWT on Linux from within Linux:
http://www.chingonazo.com/csLinuxWorkBenchDBs.JPG

And in this other case, again the same scenario but I used the OpenSuSE VNC server application to make a connection wholly from within OS/2 using Seamonkey browser; by medium of the http protocol and through an open port on the Linux firewall this Jvm app presented:
http://www.chingonazo.com/csLinuxWorkBenchVNC.JPG

On the other hand, CloudScape/Derby were designed to be "administrator-free" which means that an user (like a developer, for example) does not have to worry much about managing the database.

Have you explored the embedded bin directory?

Probably if you were to become acquainted overall with CloudScape/Derby features by using the extensive examples and/or information at IBM and Apache.org, you would more conveniently customize the RDBM to your needs --yes, even if text based.

Moreover, for a comprehensive source, you might want to buy the manual referenced in my previous post.


Jan-Erik (17 Dec, 2006 17:04):
It may be very handy if it can be handled automatically with a rexx .cmd-file from the app-to-be later on though.

I'll try out the older DBVisualizer 4.0.4 to see if it can make up for that, otherwise I have to ask you for some more suggestions.

Mvh / MfG / Wkr
//Jan-Erik


If by now I have not confused you enough , let me know if I may obfuscate the issue some more...


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  20 Dec, 2006 on 13:22
The installer fro DBVisualizer 4.0.4 and the earlier version doesn't work properly and just halt suring installation.

So I'm back at square one.

Someone got another suggestion?!

Mvh / MfG / Wkr
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  20 Dec, 2006 on 15:19

Jan-Erik (20 Dec, 2006 13:22):
The installer fro DBVisualizer 4.0.4 and the earlier version doesn't work properly and just halt suring installation.

So I'm back at square one.

Someone got another suggestion?!


Humm, assuming that you have installed the CloudScape/Derby application and have successfully tried some of the sample ksh scripts, here is an GUI for an admin that I had not remembered about.

Place the two *.jar files included in the compressed file in your CLASSPATH for CloudScape/Derby (perhaps at the end of your current CLASSPATH). And place the file myCloudView.cmd in your OS/2 PATH environment --say, /os2/-- so that you will be able to execute from anywhere in your file structure.

After you do so, you may start the GUI admin for IBM CloudScape and/or Apache Derby as follows:

myCloudView.cmd

You should see the following:

http://www.chingonazo.com/CloudView.JPG

Define Connection:
http://www.chingonazo.com/CloudView1.JPG

Properties (yes, it is executing under OS/2):
http://www.chingonazo.com/CloudView2.JPG

Please, note although I am making available those files with the intention of helping you solve your problem, those are provided to you "as is" --that is, they worked for me when I tested them but you download them at your own risk.

Best of luck ;

P.S. Please make sure to add the db2jcc.jar file to your CLASSPATH environment variable, otherwise only your embedded db driver will load.

You can test that it is loading by pressing the appropriate button for the purpose (note the default localhost and port 1527):
http://www.chingonazo.com/CloudView3.JPG

To create (or import) your db simply select from the menu label "file":
http://www.chingonazo.com/CloudView4.JPG

Now you are in full control of CloudScape/Derby with your GUI admin front end.


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  20 Dec, 2006 on 22:12

Jan-Erik (20 Dec, 2006 13:22):
The installer fro DBVisualizer 4.0.4 and the earlier version doesn't work properly and just halt suring installation.

...it is because you are using the WinXX installer --a NoNo if an OS/2er can avoid it. As an example, I was playing a little with the 4.3.6 version DbVisualizer. I downloaded first the generic Unix *gz version and subsequently the WinXX *zip one.

If possible download the dbvis_unix_4_3_6.tar.gz or dbvis_unix_5_0.tar.gz since OS/2ers can use the EMX runtime with the tar and gzip utilities to extract like:

tar -xvpzPf dbvis_unix_4_3_6..tar.gz

Notwithstanding, if you download the WinXX version, I believe the following will also apply. I tried the WinXX file BUT after I had worked with the generic Unix version.

I referenced the long command for Unix/Linux masochists (see the section: 3.2 How do I launch DbVisualizer manually? at < http://www.dbvis.com/products/dbvis/faq.html >)

But I placed all those statements in a file ( myDbVisualizer.cmd ) that I subsequently placed under my OS2 directory --to be accessible from anywhere in my directory structure, see(modified to eliminate the break after the last SET statement in the html presentation):

http://www.chingonazo.com/myDbVisualizer.cmd.html

(In other words, the assignment(s) by the keyword SET is(are) to be ALL in a single line with no newline breaks)

Now simply entering at a command prompt:

myDbVisualizer.cmd

this Java JVM pops up:
http://www.chingonazo.com/dbVis-4_3_6.JPG

The CMD file can be modified to work with the Version of DbVisualizer 5.0. by modifying the DBVIS_HOME environment variable to point to the pertinent directory and adding to the CLASSPATH the extra JAR file: $DBVIS_HOME/lib/dbvisext.jar
that exists only in the version 5.0.

One issue that I noticed is that moving/resizing of DbVisualizer is constrained (at least in my system).

The way around that issue is to manipulate directly the configuration file dbvis.xml file located in the directory pointed by the HOME environment variable see the comments in the file http://www.chingonazo.com/myDbVisualizer.cmd.html
consequently, make sure to set that important variable (WinXX relieves the user from these tasks but we do it the Penguin's way).

Hence you will find a line in your dbvis.xml file of the form: <MainFrameGeometry>1|200|750|600|170|200</MainFrameGeometry>

where the two inner numbers represent the size of the rectangular Java application (I modified the first three numbers separated by bars, above)

In my case I specified the number 1 that makes the app rectangle be close to the edge of my left screen area;

the number 200 makes the app rectangle decrease its height position relative to the screen area, see:
http://www.chingonazo.com/dbVisBottom-4_3_6.JPG

(compare to the value in the first JPG above).

To finalize, if the CloudScape/Derby admin GUI does not fulfill your needs, try experimenting again with DbVisualizer as recommende by Terry: COOL APP, apparently?

Cheers!


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  12 Jan, 2007 on 20:24


Humm, assuming that you have installed the CloudScape/Derby application and have successfully tried some of the sample ksh scripts, here is an GUI for an admin that I had not remembered about.


on a) Yes, derby server seems to work, and the client command-line "ij"-thingie too.
on b) no "ksh"
on c) was a part of CloudScape/Derby 5?!, you forgot anyhow to include something... so I have nothing to try out.


..it is because you are using the WinXX installer --a NoNo if an OS/2er can avoid it.

Nope, I used the Generic Java installer, how can they create platform specific installers when they're using a platform neutral java solution?!!!


I referenced the long command for Unix/Linux masochists (see the section: 3.2 How do I launch DbVisualizer manually? at < http://www.dbvis.com/products/dbvis/faq.html >)

Yes, Thank you. I modified it to be more suitable for OS/2-eCS by making it rexx aware and resolv paths by itself.

I had problems that it complained that it wasn't installed in the default directory and therefore couldn't load something dynamically when it was supposed to connect to the Derby server...
but I managed to silence it with additional parameters compared to the ones suggested.

The problem I've got now is that it (dbvis) just doesn't like the syntax itself produce to connect to the server.


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  13 Jan, 2007 on 07:25

Jan-Erik (12 Jan, 2007 20:24):


Humm, assuming that you have installed the CloudScape/Derby application and have successfully tried some of the sample ksh scripts, here is an GUI for an admin that I had not remembered about.


on a) Yes, derby server seems to work, and the client command-line "ij"-thingie too.
on b) no "ksh"


Please, explain. The post where I suggested how to set it up used the scripts for the public domain Korn Shell (ksh). How are you starting the network server and the ij client, then


Jan-Erik (12 Jan, 2007 20:24):
on c) was a part of CloudScape/Derby 5?!, you forgot anyhow to include something... so I have nothing to try out.


You mean CloudScape/Derby 10.1? That is the version that up to now we have had under discussion. Quite possible you are confusing the database version with that of DBVisualizer.

(appropos what version of DBVisualizer are you using???)

Notwitstanding, and assuming that you are sincere in your question/request and not simply heeding the advice of third parties with other motives, I have sent you another (yes, this is the second one) email
with instructions. Please reread with care the prior and pertinent post afterwards.


Jan-Erik (12 Jan, 2007 20:24):

..it is because you are using the WinXX installer --a NoNo if an OS/2er can avoid it.

Nope, I used the Generic Java installer, how can they create platform specific installers when they're using a platform neutral java solution?!!!


Because WinXX users enjoy chasing icons with with their mice/pointer/etc. Many software business organizations offer executables to relieve the WinXX user from learning to type a simple command with arguments at the prompt line. And there the argument that MS simply has stiffled innovation by removing the creative thinking ability of their users .

Consequently, the zipped version of DBVisualizer provides executables that, among other things, set the CLASSPATH and HOME environment variable for the user (and refuse to work even with the Odin under OS/2).


Jan-Erik (12 Jan, 2007 20:24):

I referenced the long command for Unix/Linux masochists (see the section: 3.2 How do I launch DbVisualizer manually? at < http://www.dbvis.com/products/dbvis/faq.html >)

Yes, Thank you. I modified it to be more suitable for OS/2-eCS by making it rexx aware and resolv paths by itself.

I had problems that it complained that it wasn't installed in the default directory and therefore couldn't load something dynamically when it was supposed to connect to the Derby server...
but I managed to silence it with additional parameters compared to the ones suggested.

The problem I've got now is that it (dbvis) just doesn't like the syntax itself produce to connect to the server.


If you followed the instructions in the DBVisualizer pertinent post, you might make sure about the existence of a HOME environment directory. What is the exact name of the DBVisualizer that you downloaded?

Note that HOME environment is an important variable and will cause problems if not set as suggested in the pertinent post --please reread carefully. The WinXX version executable relieves the user from all those details BUT nonetheless sets a HOME environment variable.

Another thing, using the Unix/Linux equivalent of the DBVisualizer (or as a matter of fact, most any other Unix/Linux program that we use under OS/2) presuposes the existence of the EMX runtime environment --at least for the OS/2 session from where we start the DBVisualizer front end-- because internally those programs may be using front slashes "/" rather than OS/2's default "\".

Appropos, did you start your CloudScape/Derby network server BEFORE attempting to connect and create/manipulate/import/etc. data with the DBVisualizer front end
That is a commonly overlooked issue.

Perhaps Terry, having browsed through some of the DbVisualizer posts at that site, might have observed a related issue by some users of the front end.

Best regards !


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  14 Jan, 2007 on 00:09


Please, explain. The post where I suggested how to set it up used the scripts for the public domain Korn Shell (ksh). How are you starting the network server and the ij client, then

Please see the attached image of the "server"...

Then nothing... as there's just the command line


You mean CloudScape/Derby 10.1? That is the version that up to now we have had under discussion. Quite possible you are confusing the database version with that of DBVisualizer.

(appropos what version of DBVisualizer are you using???)

Notwitstanding, and assuming that you are sincere in your question/request and not simply heeding the advice of third parties with other motives, I have sent you another (yes, this is the second one) email
with instructions. Please reread with care the prior and pertinent post afterwards.


DBVisulizer 4.3.6 (dbvis_unix_4_3_6.tar.GZ) that I unpacked with Archiver

I went back and looked for info where to get something GUI admin like CloudView and saw that it was included in CloudScape/Derby 5 but left out of the package 10.x
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0408anderson2/#table1

Ohh, ok you sent me a mail before, must have been filtered, I'll look to see if I can recover it....gone, could you please resend it.


f you followed the instructions in the DBVisualizer pertinent post, you might make sure about the existence of a HOME environment directory. What is the exact name of the DBVisualizer that you downloaded?

Note that HOME environment is an important variable and will cause problems if not set as suggested in the pertinent post --please reread carefully. The WinXX version executable relieves the user from all those details BUT nonetheless sets a HOME environment variable.

Another thing, using the Unix/Linux equivalent of the DBVisualizer (or as a matter of fact, most any other Unix/Linux program that we use under OS/2) presuposes the existence of the EMX runtime environment --at least for the OS/2 session from where we start the DBVisualizer front end-- because internally those programs may be using front slashes "/" rather than OS/2's default "\".



Yes, I've read all your posts I could find regarding this topic, downloaded the images and files and reread the posts to verify correctness and they are, and therefore work (up to a certain point).

A very good thing with eCS is that it set the Home and Programs folder during install of the system, where dbvis created its settings just as you mentioned

I use this code to run DatabaseVisualizer
===DBVIS.CMD===
/* Start DBVis */
parse source . . this
this = filespec( 'D', this )||strip( filespec( 'P', this ),'T', '\' )
'@SET DBVIS_HOME='||this
'@SET CLASSPATH=%DBVIS_HOME%\resources;%DBVIS_HOME%\lib\alloy.jar;%DBVIS_HOME%\lib\commons-lang.jar;%DBVIS_HOME%\lib\crimson.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\dbvis.jar;%DBVIS_HOME%\lib\forms.jar;%DBVIS_HOME%\lib\ideit-util.jar;%DBVIS_HOME%\lib\jcchart.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\jdom.jar;%DBVIS_HOME%\lib\log4j.jar;%DBVIS_HOME%\lib\nb-editor.jar;%DBVIS_HOME%\lib\ognl.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\openide-util.jar;%DBVIS_HOME%\lib\poi.jar;%DBVIS_HOME%\lib\yFiles.jar;%DBVIS_HOME%/lib/jdom-b9.jar;'
'@echo Starting DB Visualizer, Please wait...Here we go, Metztli !!!'
'@K:\NetWork\java141\bin\java -Xmx256m -Ddbvis.wrapperdir='||translate(this, '/', '\')||'/wrapper/classes -Ddbvis.prefsdir=C:/Home/Default com.onseven.dbvis.DbVisualizer'
===============


Appropos, did you start your CloudScape/Derby network server BEFORE attempting to connect and create/manipulate/import/etc. data with the DBVisualizer front end
That is a commonly overlooked issue.

I ensured that it was started far it in advance before I tried DBVisualizer 4.3.6
The param -Ddbvis.wrapperdir fixed the problem to dynamically load and keep the imported Derby class (db2jcc.jar) in memory during connection operation.

Something is wrong though anyway and I can't figure it out.


======

I was so saddened by the problem that I bought a new harddrive, installed it and downloaded and installed AMPOS2/AMPECS on this computer, I managed to configure it and began add the tables in PHPMyAdmin. I don't know what I have to do to link the tables together, but now I guess I will manage table relations myself in rexx code to get past all this. :-/

Thank you for all your help...

mvh / Wkr / MfG
//Jan-Erik
Running eCS 2.0 Beta 3


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  14 Jan, 2007 on 01:18

Jan-Erik (14 Jan, 2007 00:31):


Please, explain. The post where I suggested how to set it up used the scripts for the public domain Korn Shell (ksh). How are you starting the network server and the ij client, then

Ohh, ok you sent me a mail before, must have been filtered, I'll look to see if I can recover it....gone, could you please resend it.


...mail has been sent again (third time just now) --you must have a really bad misconigured email filtering system.


Jan-Erik (14 Jan, 2007 00:31):

f you followed the instructions in the DBVisualizer pertinent post, you might make sure about the existence of a HOME environment directory. What is the exact name of the DBVisualizer that you downloaded?

Note that HOME environment is an important variable and will cause problems if not set as suggested in the pertinent post --please reread carefully. The WinXX version executable relieves the user from all those details BUT nonetheless sets a HOME environment variable.

Another thing, using the Unix/Linux equivalent of the DBVisualizer (or as a matter of fact, most any other Unix/Linux program that we use under OS/2) presuposes the existence of the EMX runtime environment --at least for the OS/2 session from where we start the DBVisualizer front end-- because internally those programs may be using front slashes "/" rather than OS/2's default "\".



Yes, I've read all your posts I could find regarding this topic, downloaded the images and files and reread the posts to verify correctness and they are, and therefore work (up to a certain point).

A very good thing with eCS is that it set the Home and Programs folder during install of the system, where dbvis created its settings just as you mentioned

I use this code to run DatabaseVisualizer
===DBVIS.CMD===
/* Start DBVis */
parse source . . this
this = filespec( 'D', this )||strip( filespec( 'P', this ),'T', '\' )
'@SET DBVIS_HOME='||this
'@SET CLASSPATH=%DBVIS_HOME%\resources;%DBVIS_HOME%\lib\alloy.jar;%DBVIS_HOME%\lib\commons-lang.jar;%DBVIS_HOME%\lib\crimson.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\dbvis.jar;%DBVIS_HOME%\lib\forms.jar;%DBVIS_HOME%\lib\ideit-util.jar;%DBVIS_HOME%\lib\jcchart.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\jdom.jar;%DBVIS_HOME%\lib\log4j.jar;%DBVIS_HOME%\lib\nb-editor.jar;%DBVIS_HOME%\lib\ognl.jar;'
'@SET CLASSPATH=%CLASSPATH%;%DBVIS_HOME%\lib\openide-util.jar;%DBVIS_HOME%\lib\poi.jar;%DBVIS_HOME%\lib\yFiles.jar;%DBVIS_HOME%/lib/jdom-b9.jar;'
'@echo Starting DB Visualizer, Please wait...Here we go, Metztli !!!'
'@K:\NetWork\java141\bin\java -Xmx256m -Ddbvis.wrapperdir='||translate(this, '/', '\')||'/wrapper/classes -Ddbvis.prefsdir=C:/Home/Default com.onseven.dbvis.DbVisualizer'
===============


I am not versed in REXX. I do most of my work from the Linux shell scripts to roughly equivalents under the OS/2 CMD. Most of the issues arising out of Java programs are due to a CLASSPATH misconfiguration.

If those REXX scripts that you concocted miss one slight detail ( a colon ':' or a space instead of a semicolon ";") --you are the slave of Java for an undetermined length of time.


Jan-Erik (14 Jan, 2007 00:31):

Appropos, did you start your CloudScape/Derby network server BEFORE attempting to connect and create/manipulate/import/etc. data with the DBVisualizer front end
That is a commonly overlooked issue.

I ensured that it was started far it in advance before I tried DBVisualizer 4.3.6
The param -Ddbvis.wrapperdir fixed the problem to dynamically load and keep the imported Derby class (db2jcc.jar) in memory during connection operation.

Something is wrong though anyway and I can't figure it out.


======

I was so saddened by the problem that I bought a new harddrive, installed it and downloaded and installed AMPOS2/AMPECS on this computer, I managed to configure it and began add the tables in PHPMyAdmin. I don't know what I have to do to link the tables together, but now I guess I will manage table relations myself in rexx code to get past all this. :-/

Thank you for all your help...


Hope your filter will allow this third email pass through...

Cheers !


Subject  :  Re:Looking for >useful< database
Author  :  magog
Date  :  16 Jan, 2007 on 01:51
If you are looking for a frontend you can also use Squirrel SQL (Java) for most databases:
http://sourceforge.net/projects/squirrel-sql/

I'm not sure if the latest release from end of last month does still work with Java 1.4.

---
Regards,
Juergen Ulbts (Germany)

*** Java Movie Database - http://www.jmdb.de/ ***
*** New version available (2006-11-17) ***
*** Website has been updated ***


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  16 Jan, 2007 on 22:39
CloudView
I've now tried CloudView with the following observations.

1. I had to include the license file for db2jcc.jar as well in the classpath statement to be able actually connect to the derby server.

2. The Derby server responded and accepted the connection attempt with:
Connection number: 1.
Connection number: 2.
Connection number: 3.
etc.

3. CloudView can't display tables in the sample or tourdb databases.

4. CloudView seem to produce code that doesn't work with version 10.x of Derby:
Database access resulted in SQL exception:
SQLState , Error code -1, Message:
DB2 SQL error: SQLCODE: -1, SQLSTATE: 42X01, SQLERRMC: PROPERTIES42X01
and it's therefore not possible to create tables either.

5. Properties code can't be edited by hand in SQL view as it seem to me.

Thank you for all your suggestions and tips... I'm trying them out when time permit.

=====
Squirrel SQL
squirrel-sql-2.4-install.JAR only work with java 1.5
=====
MySQL
MySQL tools can be made to run under eCS if you copy the right .dlls (why do they require/load opengl32?!!!) but doesn't connect or do anything useful at the moment.
=====
JDBStudio
Ehh, have to get back to you on that one
=====
Fredys
Admin.jar doesn't seem to like Java 1.4.1, may work with java 1.4.2?
=====
iSQL-Viewer
Ehh, how can one enable drivers and connect it to something?
=====


I'll try some more to see if the MySQL path is possible...

mvh / Wkr / MfG
//Jan-Erik


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  17 Jan, 2007 on 14:36

Jan-Erik (12 Jan, 2007 20:24):


Humm, assuming that you have installed the CloudScape/Derby application and have successfully tried some of the sample ksh scripts, here is an GUI for an admin that I had not remembered about.


on a) Yes, derby server seems to work, and the client command-line "ij"-thingie too.
on b) no "ksh"
on c) was a part of CloudScape/Derby 5?!, you forgot anyhow to include something... so I have nothing to try out.


..it is because you are using the WinXX installer --a NoNo if an OS/2er can avoid it.

Nope, I used the Generic Java installer, how can they create platform specific installers when they're using a platform neutral java solution?!!!


I referenced the long command for Unix/Linux masochists (see the section: 3.2 How do I launch DbVisualizer manually? at < http://www.dbvis.com/products/dbvis/faq.html >)

Yes, Thank you. I modified it to be more suitable for OS/2-eCS by making it rexx aware and resolv paths by itself.

I had problems that it complained that it wasn't installed in the default directory and therefore couldn't load something dynamically when it was supposed to connect to the Derby server...


That problem can be resolved by editing the fle myDbVisualizer.cmd
Please note the changes in the newly edited file. The last line contains modifications to make the DBVIS_HOME resolve to the OS/2 and NOT the Unix environment.


Jan-Erik (12 Jan, 2007 20:24):
but I managed to silence it with additional parameters compared to the ones suggested.

You do not have to add any additional parameters to the file referenced above. Consequently, BEFORE you get fancy with your REXX stuff, please try the file referenced above first.

Here is the OS/2 script that I used to start the network server:
myStartCloudNetwork.cmd

And here is the OS/2 script to start the IJ tool:
myStartIJ.cmd


Jan-Erik (12 Jan, 2007 20:24):
The problem I've got now is that it (dbvis) just doesn't like the syntax itself produce to connect to the server.

You are experiencing that problem because the developers of DbVisualizer probably made a mistake in the way that their product attempts to connect to the IBM CloudScape/Apache Derby database ...really! Their default connect expression on DbVisualizer 4.3.6 and 5.0 field is wrong!

Assuming that you have your CloudScape/Derby network server on localhost port 1599 and you want to connect to a non existing database, the default connection on DbVisualizer (4.3.6 and 5.0) is:

jdbc:derby:net://localhost:1599localhost

THAT IS WRONG and CloudScape/Derby will complain about the syntax in BOTH versions of DbVisualizer. CloudScape/Derby network server will not accept the connection due to the unecessary expressions: net: and the localhost following the port number.

On the other hand, if you modify the connect expression to:

jdbc:derby://localhost:1599

then you will connect briefly but you will be bounced back because there is no existing database to connect to. Here is where you use the "ij thingie" --as you called it-- to make an initial database called OS2WORLD (observe the lower left screen area):

connect 'OS2WORLD;create=true';

proceed to connect (as I did in the illustration above) with to the database in your DbVisualizer as:

jdbc:derby://localhost:1599/OS2WORLD

and you are connected!

Hence earlier, with the IJ tool I also created another exmaple database named MARIA which I subsequently connected to with DbVisualizer 4.3.6.

Consequently, your named databases will be created in the root directory of localhost in these examples (see the upper screen window) and will maintain a lock so as not to be rewritten if another attempt at creation is made. Note however that you can connect multiple times to a database since the Derby network server will manage the connecions appropriately.

The references on CloudScape/Derby will reveal more on the subject. As an example, the link below shows the appropriat way to connect to the CloudScape/Derby network server:

http://db.apache.org/derby/docs/10.1/getstart/rgsquck30197.html

One interesting feature of the IBM CloudScape/Apache Derby RDBMs is that the storage created by those is architecture agnostic. As long a you move the complete hierarchy, the databases can be moved, for instance, from Little-endian to Big-endian architectures with no issues. Needless to say, the moving of databases among OS/2, WinXX, Macs, and Linux will be no issue at all.

Hope your problems will be over with this information !

P.S. Assuming (another) OS/2 CloudScape/Derby Network server script with ONLY the DERBY_INSTALL and CLASSPATH OS/2 environment set --that is, with nothing else specified, you can gracefully shutdown your nerwork server running in localhost port 1599 with the command:
javaorg.apache.derby.drda.NetworkServerControlshutdown-hlocalhost -p1599

As referenced in the verbose output of:
java org.apache.derby.drda.NetworkServerControl

Doing so will void connection failures that sometimes occur when the CloudScape/Derby network server process is abruptly killed.


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  17 Jan, 2007 on 22:14
Thank you El Vato!!!

It all works up to the point you've described it, now I can do it with both Derby and MySQL.

But it's very unclear that one shouldn't use the jdbc drivers as described earlier (db2cc.jar), one has to use the "client" jar-file that is obviously not the same thing.

I can now get to the point that you describe, but there's no way to add schemas nor tables, not even to see that the default ones contain anything... can you please suggest something?

Can't edit the MySQL tables, nor link them togheter in DBVis as there's no function for that (e.g. right-click on table or tree view), but it does connect nicely and show the ones I created with PHPAdmin the other day.

mvh / Wkr / MfG
//Jan-Erik
Software tester of ERP System for manufacturing companies.


Q: Why can't developers create intuitive software so we can get on with our work?
A: It would be to easy to use and so many more would try it, that's why.


Subject  :  Re:Looking for >useful< database
Author  :  El Vato
Date  :  18 Jan, 2007 on 18:44

Jan-Erik (17 Jan, 2007 22:14):
Thank you El Vato!!!

It all works up to the point you've described it, now I can do it with both Derby and MySQL.

But it's very unclear that one shouldn't use the jdbc drivers as described earlier (db2cc.jar), one has to use the "client" jar-file that is obviously not the same thing.


db2jcc.jar and db2jcc_license_c.jar are IBM's Universal Database driver licensed to be used on both IBM CloudScape and Apache Derby RDBMs.

Notwithstanding, derbyclient.jar is the open source implementation of the previous proprietary driver that also supports the Distributed Relational Database Architecture (DRDA) standard for client/server comm.

You might connect to Cloudscape/Derby databases even if either of the proprietary or the open source driver were removed from the path. If you were to remove the open source driver ( derbyclient.jar ), for instance, and used the proprietary set, then to connect to a SAMPLE named database on port 1599 of localhost, you would type:

jdbc:derby:net://localhost:1599/SAMPLE

Compare that to the previous post where we were making the connection to CloudScape/Derby without the net: because we were using the open source driver: derbyclient.jar


Jan-Erik (17 Jan, 2007 22:14):
I can now get to the point that you describe, but there's no way to add schemas nor tables, not even to see that the default ones contain anything... can you please suggest something?

Can't edit the MySQL tables, nor link them togheter in DBVis as there's no function for that (e.g. right-click on table or tree view), but it does connect nicely and show the ones I created with PHPAdmin the other day.


Whereas CloudScape, donated to the Apache Foundation by IBM and known in the open source world as Derby are released with no strings attached --as long as the respective licenses are observed-- DbVisualizer is not.

...you need to aquire a (personal) license if you want to manipulate your storage units. See < http://www.minq.se/products/dbvis/info.html >

I would review Java version requirements at the bottom and ask if the license will cover from 4.3.6 and upper DbVisualizer versions.


Jan-Erik (17 Jan, 2007 22:14):
mvh / Wkr / MfG
//Jan-Erik
Software tester of ERP System for manufacturing companies.

Q: Why can't developers create intuitive software so we can get on with our work?
A: It would be to easy to use and so many more would try it, that's why.


Best regards !!!


Subject  :  Re:Looking for >useful< database
Author  :  Jan-Erik jan-erik@os2ug.se
Date  :  18 Jan, 2007 on 20:43
OK, are there any other tools out there that does the job?


mvh / MfG / Wkr
//Jan-Erik


Powered by UltraBoard 2000 <www.ub2k.com>