Linux.com

I so agree with you.
The only thing I really miss in PostgreSQL is
a decent way of replication. Other than that,
the oss RDBMS'es are the way to go.

#

I agree too

I work as a consultant, and has for many years, both as developer and dba, using many platforms and RDBMS's. Oracle is so stupidly complex in some places.

Just look at the syntax for sqlnet.ora, or the mess in the oracle home dir. Just finding the log files is a challange for people not working with Oracle ALL THE TIME (like me). Don't even get me started on the installer.

After a while you learn all the stupid stuff and just assimilate them, but you relly shouldnt need to "learn" crap like that, to use an RDBMS efficently.

It is *so* easy to install, configure, use, admin and run PostgreSQL on Debian (my dist of choice).

Every NEW STARTED project (without legacy Oracle stuff) I have worked on could have used postgresql instead, but I am sure none of my firms customers has even heard of it, that is the problem for PostgreSQL, not enough exposure.

Any Oracle DBA could Admin PostgreSQL, if he wanted to (eg someone paying him), so the support issue people bring up often I dont really understand, it is an imagined problem mostly.

#

Umm.. You googled for postgres replication?

I agree it's not out of box but it's there. I didn't use any of them but I am sure they will be at least workable.

For hint, here are two google terms, usogres and HA-postgresql.

Shridhar

#

Or if you want to be able to make changes on more than one replica and have them reconciled, try
http://pgreplicator.sourceforge.net/

#

replication is easily accomplished with a decent script and some thought... Microsofts never really worked correctly anyway. I always ended up doing transactional remote table access stored procedures IE rolling my own. If you make them transactional, you are replicating.

Think shell and a little trial and error for replication. Roll your own, it will work better anyway. Just remember to think A.C.I.D. and you will be ok. psql combined with stdio is very powerful.

I even replicate MySQL this way. Where there is a will, there is a way. Belleedat.

l8,
neil

#

I've been using MySQL for 2-3 years. As a matter of fact, it's the only database I'm comfortable with.

There are a only couple of things that I want for version 4:
- Nested SQL queries. Sometimes I find myself using the programming language (PHP, C) to execute multiple queries to do stuff that could really be done with a single nested query.
- Proper replication. I.e. with features that would make it useful for high-availability and load-balancing out-of-the-box. The current manual says that you must write your own scripts (!) for a MySQL HA setup.

#

You can set up a mutual master-slave configuration and have two servers mirror each other.

http://www.mysql.com/doc/en/Replication_HOWTO.htm<nobr>l<wbr></nobr>

#

Just move to Postgres. MySQL normally rocks for single user application. Add a concurrently load, which is more reflective of real world conditions and MySQL almost always gets seriously stomped. In other words, if you need concurrent queries, PostgreSQL is what you want.

FYI, PostgreSQL 7.3 is currently in beta.

#

Both MySQL and Postgres have limitations in comparison to existing Oracle functionality.

Postgres lacks just in time recovery. The WAL has now been implemented but can’t yet be backed up. This is coming soon.

MySQL has lacked nested SQL statements. This should be in version 4.0.

Both of these are fundamental failings for production databases.

Both databases are much easier to configure and work with than Oracle. Both of these failings are being addressed in the next releases of these products.

#

After all, anybody remember the early release or Oracle? I'm sure pre 4.0 releases had major deficiencies.

#

Yes, PostgreSQL only needs JIT recovery, full replication, and a couple other features to compete directly with the big DBMSs. Most of these are on their way with the next release (7.3).

MySQL lacks a good many more features than just nested queries, though. I would say it is about 50% of the way there, missing subselects, views, triggers, stored procedures, CHECK constraints, and actually even it's foreign key support is not fully ANSI-compliant.

#

Actually, none of those will be in 7.3. 7.3 will have schemas and lots of other cool new features though...

PITR (with I assume is what you're referring to) will likely be in 7.4, and there are several projects that provide various levels of replication functionality, but it's IMHO optimistic to expect a good solution to be integrated into the core server code for 7.4

#

Read this article http://www.oreillynet.com/pub/a/network/2002/04/2<nobr>3<wbr></nobr> /fulljoin.html

#

Perhaps it's new in 9i, but Oracle does support ANSI standards. Please look at:

http://otn.oracle.com/products/oracle9i/daily/jan<nobr>1<wbr></nobr> 4.html

Also, my 2 cents is that Oracle installation is usually painless (of course, always go to custom installation or it will install everything you don't need). It never took me more than an hour to install it properly.

My preference for Oracle is centered in two aspects: the wonderful and robust transaction model and backup/recovery features. Also, it's very stable. I can sleep at night.

#

Oracle installation: painless? I suppose it gets easier with supercomputer hardware and being a full-time Oracle administrator with lots of time on your hands. I've seen lots of issues with Oracle software of late: bloated installations, bizarre errors caused by slight mismatches in client and server versions, bizarre update procedures (to fix those patchlevel differences).

Also, Oracle's requirements seem to be skyrocketing as they seem to be deserting the low-end faster than a third-world regime who've just heard that George W. Bush is in town with 10000 of his best men. As people start to realise that they don't all need a datacentre dedicated to Oracle (probably the size of a small planet, in order to cram in all that hardware), expect to see more of a presence for open source database systems, and the commercial ones with a relatively small footprint (eg. Sybase).

#

What you really mean is

Oracle installation is generally painless if your running it in a windows environment. Because that's the only excuse I can find for it's installation procedures....

We had a parent company project bring in a oracle consultant at the usual rate for oracle people. I was assigned to help him get oracle going on an IBM RS/6000. The first day I was working on other things.. the second day I watched him install and answered basic questions about file systems and how to run commands.... The third day I spent most of it on the phone but they wanted to get some progress so he was working late... while he continued to try and get oracle to run I downloaded and installed DB2 under linux... Management wanted a "commercial database" for that project and as it wasn't our management.

Our data backend continues to be postgres and we have had zero crashes in the last year. We have had zero problems restoring the database from backup. And It took all of about 20 minutes to install (from cd) and create the first database.

Not everything is bad about Oracle but the fact that they can't even get an installer right on a comercial AIX tells me a lot about the attitude of the project.

Although Oracle was finally brought up it never did achieve any degree of reliability needed to actually deploy that project.

#

I don't know anything about the original contributor's background, but Oracle's installers do suck pretty badly on Windows as well, in my experience. With all the stuff Oracle installs, they'll soon require you to install a database system to manage all those libraries and programs before you get to install the actual Oracle database system itself.<nobr> <wbr></nobr>;-)

#

Here's an example on an Oracle system I work with. I'm no expert, but I tried what the author was saying and he seemed to be right.

database version:
-----------------
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

query:
------
SQL> select international.internationalId,wobject.wobjectId from international left outer join wobject on international.namespace=wobject.namespace;

error:
------
ERROR at line 1:
ORA-00933: SQL command not properly ended

Note: The little * in the SQL Plus interface was right under the join syntax. I don't think I did anything wrong.

#

Sorry, but MS SQL has been supporting ANSI join syntax (at least "left [outer] join") since version 7.0, or perhaps even 6.5.

#

I confirm that. I've just used one in a quietly long and awfull select<nobr> <wbr></nobr>:)

#

"Why don't you use a translating abstraction layer like ODBC or JDBC?"

 
Ummm.. maybe because ODBC and JDBC wouldn't fix this so called problem anyways. They are simply connectivity layers. You still have to supply a sql statement in a syntax that will work for your target databases.

#

actually ODBC and JDBC are more than simply connectivity layers and you can use something called an 'escape clause' that the ODBC layer will translate to the proper syntax for the database you are using. Check out <A HREF="http://www.newarchitectmag.com/archives/1997/07/north/">
Interoperable SQL</a newarchitectmag.com>

#

No "LEFT [OUTER] JOIN" ? It seems pretty strange.

5 years ago, I worked with an Oracle 7 server, and I absolutely positive that the "LEFT [OUTER] JOIN" was supported. In fact, there was even a recommandation to not use the older syntax ".. *=<nobr> <wbr></nobr>..". And about the MSSQL, I use on a daily basis the "LEFT [OUTER] JOIN" syntax on MSSQL7 and MSSQL2000 servers. It works (and it is not on the joints that we can critisize MSSQL<nobr> <wbr></nobr>... ).

Then, you must know that there is others free db servers. I use Firebird 1.0, and for little applications, SQLite. They are SQL92 compliant at 98 to 100% and works pretty well.

(sorry for the english, me not good<nobr> <wbr></nobr>;) )

#

In linux i was unable to install Oracle and had to get support because the installer kept barking a java error and did not install correctly. finally i got it to install after a week of playing with it and took foreeever to set up a simple database. Even after oracle training it still takes me forever to get the system up. And this was just a development server do you think we would buy/install this in our live servers? NOOPE. Too expensive, Too tedious.

I use Postgresql to replicate an HP3000 imageSQL multiple databases that have millions of records.
I can really sleep well at night.. it has been like a rock.Once they get backup and replication running i dont see a need for anyone to waste money on such an expensive DB.

#

Yep, PostgreSQL is used in mission critical environments all around the world. A recent example of it's growing acceptance and reputation are the submissions by the ISC, and by the ISOC, for using PostgreSQL as the database behind the management of the<nobr> <wbr></nobr>.org registry.

http://www.icann.org/tlds/org/questions-to-applic<nobr>a<wbr></nobr> nts-13.htm#Response13InternetMulticastingServiceI<nobr>n<wbr></nobr> candInternetSoftwareConsortiumInc

and

http://www.icann.org/tlds/org/questions-to-applic<nobr>a<wbr></nobr> nts-13.htm#Response13TheInternetSocietyISOC

To quote examples for the ISOC proposal:

"... some more detailed case studies of companies that have chosen PostgreSQL for their needs.

BASF: This $29B global chemical company selected PostgreSQL to power a shopping platform for a new initiative in its agricultural products division: the Harvest Partners Network. BASF worked with the Web Commerce Group's (WCG) product, Arkdom Commerce Enterprise Edition, which was developed with PostgreSQL. WCG chose PostgreSQL for 3 reasons: lower total cost of ownership; superior technology; and better maintenance and support.

Vantek K.K: Vantek K.K. is currently implementing a business information system using PostgreSQL, for Tsutaya, Japan's largest rental company that rents more than a billion items a year. Vantek K.K's previous successes include the implementation of the Blockline ISP customer management system, again using PostgreSQL.

The World Company: The World Company operates 10 U.S. newspapers, several online sites, and a cable television, Internet, and telephone company in Lawrence, KS, USA. The World Company has based much of its operations on PostgreSQL."

There are many more. TrustCommerce, a real-time online credit card processing company uses and recommends PostgreSQL. There are 1TB+ installations of PostgreSQL. 100GB+ installations are getting common. And more.

#

I use Postgresql to replicate an HP3000 imageSQL multiple databases that have millions of records.

I need to do this VERY BADLY!!!! Can you give me a brief description of your setup? I'd love to talk to you about this...

#

All you need to do is join the PostgreSQL "General" mailing list and ask for assistance there.

Instructions for joining that mailing list are available in the Users Lounge:

http://www.ca.postgresql.org/users-lounge

The people on the PostgreSQL General mailing list are generally knowledgable, helpful, and friendly.

There are also commercial support places around that can assist you too:

http://techdocs.postgresql.org/techdocs/supportco<nobr>n<wbr></nobr> tracts.php

Good luck.

#

Please correct me if I'm wrong (and please point me in the right direction!) but I have yet to find a way to implement data replication with a load balancing option to Open-Source databases. The project I work on (<A HREF="http://www.mcve.com/">MCVE</a mcve.com>) is a credit card processing engine for Enterprise clients, and the problem we've run into is Open Source databases aren't cutting it for the 2 million + SQL transactions that our higher-level clients run per day (granted, most of these are INSERTs). A means of load-balancing is crucial, as well as fault tolerance as downtime means lost revenues for these clients.

This is where closed-source (commercial) databases shine, especially Oracle and IBM DB2. I cannot state anything for M$ products as we do not support anything but the UNIX's.

Using the generalization that Open-Source databases are "better" doesn't cut it. Perhaps for the small-business this is true, but open-source databases will not be adopted in the Enterprise until this situation has been corrected. (I do know PostgreSQL is working on some datareplication stuff, but one project seems to do this is PERL, which is obviously not going to offer the required performance<nobr> <wbr></nobr>... I'd say we've still got 2-3 years before PostgreSQL shines in this area)

#

If they're using the regular MySQL engine suggest they check out the -MAX or 4.x series for Innodb support.

#

You should ask Justin C about it, he seems to know everyone that does stuff with Postgres.

#

It still amazes me that people that support OSS spew false and inaccurate information to get people to see the merits of open source.

I know for a fact that Microsoft supports the ANSI 92 standard (your example the LEFT OUTER JOIN syntax). From the other posts it appears that Oracle supports it as well (I am not an Oracle guru).

I have use PostgreSQL and MySQL and they are nice databases but you don't need to go making up reasons why people should run them instead of a propritery database solution.

All this does is make me doubt either your motives or your expertise. Either you already know this and are spewing fud or you don't know after all your 10 years of database expertise that MS and Oracle support ANSI SQL syntax.

#

Maybe you're the one spewing fud. On the internet, no one is to be trusted.

#

Its all very well whining about syntax but to think MySQL/PostgreSQL are even in the same league as Oracle is ridiculous.

#

You're getting MySQL and PostgreSQL confused.

_MySQL_ isn't in the same league as Oracles database products.

_PostgreSQL_ is the one in the same league as Oracle's database products.

They're two different databases, with very different sets of capabilities. They both also happen to be Open Source.

Please don't get PostgreSQL's reputation for reliability and quality mixed up with MySQL's reputation.

#

Or even their capabilities. MySQL is not even on the same book, let alone same page as PostgreSQL. Granted, PostgreSQL still has some growing to do, but it is finally starting to touch the lowend of major RDBMS'.

#

I do not claim to be a RDB pro even though I have used databases since Knowledgeman (1984) and Oracle 4 (1986). After that I have used MSSQL, Interbase, MySQL, PostgreSQL and a few others, just to mention. Plus the workstation db's (dBase, Clarion, Clipper etc).

The comparison does very little to tell about the real differences in various DBs and NOTHING about one of the most powerful features a DB backend can have: stored procedures and support for transactions.

MSSQL has a very powerful and good languages, so does Oracle. And Interbase.

How do you write triggers with MySQL?

I admit that a fake frontend to flat file system (like MySQL) can be written to emulate most of the features of a DB which has been stripped from what really makes it a db ENGINE but NOBODY should be fooled to believe that you can do serious enterprise level programming using it.

I am currently writing an Internet "application" using MySQL and getting very frustrated about its functional shortcomings.

#

Postgresql supports triggers, transactions, and stored procedures. You can write your server-side procedures in PL/pgSQL (very similar to Oracle's PL-SQL), PL/Tcl, or PL/Perl.

There is no doubt that Oracle (and DB2) are the choices for large enterprise projects. However, for _most_ non-enterprise applications, I have found mySQL and PostgreSQL to be more than adequate. I've found myself cursing the lack of SP's in mySQL on several occasions, but I've moved those apps over to PostgreSQL and have solved that problem. If I need replication, load balancing, etc., I'd definitely look at Oracle. However, short of that I'll stick with the Open Source offerings...

#

PL/Python!

Granted, it's probably is the minority but it is there nonetheless.

#

Your problem is that you chose the wrong Free Software database. Not that MySQL is a bad choice, but the MySQL developers have typically aimed more for raw select speed and not for advanced features. PostgreSQL has triggers, functions (with several different programming languages supported including C, Perl, Python, Tcl, and SQL), a mutable type system, rules, MVCC, referential integrity, and a whole pile of other goodies. The new beta adds to that schemas, and a whole pile of other goodies.

What PostgreSQL lacks is point in time recovery (you can only do full backups not incremental ones), and the best replication strategy is commercial, but other than that PostgreSQL is a pretty slick deal.

#

Well, I did not choose MySQL - it is the VAST majority of ISPs that only support MySQL and due to this unfortunate fact we will have to live with it. It is almost a de facto now, which I am sorry for. I simply cannot rely on an ISP offering PostgreSQL support. I am running PGL at my office but that does not help my customers.

Things just to go this way just like they did with the highly proprietary mp3 format. There is nothing one can do about it any more.

#

Sure, lots of places support MySQL. But many of them support PostgreSQL too.

There are even PostgreSQL specialist hosting companies of good reputation and charging very cheap rates.

i.e. pgHoster (www.pgHoster.com)

or Command Prompt (www.commandprompt.com) if you want something a bit more customsable and higher end, etc.<nobr> <wbr></nobr>:-)

#

drop mysql and get firebird, it supports stored procs triggers views etc.

#

Had you read my post you could have figured out that I know Interbase/firebird having used IB for several years.

The problem is that there are just a few ISPs that support more db backends. MySQL crap seems to be the only choice we have now.

Sigh

#

You're kidding, right? Or are you trolling?

There is a major disconnect between the sort of company that would need a major database and the sort of company that uses leased space on commercial ISP's. And 'leased space' is the lowest level of access where you would be forced to use "whatever was available". Starting with co-lo, you can install whatever makes your heart race and your wallet flinch.

boink

#

Triggers as the bane of DB existance.
While convienent at first, they always turned around and bite you in the ass. IMHO, triggers should never, ever be used but in the only rarest of situations.

#

AFAIK, you can't using MySQL but you can using PostgresSQL. We (at work) use a PostgresSQL DB with stored proc. to retrieve data from 10 different servers.

#

Quick quiz:

Would you use Access for your database?

No:
Then use PostgreSQL or a commercial database

Yes:
Then MySQL *may* be right for your task depending on what features you may require. Otherwise, you need to look at using PostgreSQL or some other commercial database.

#

You are flat wrong that Oracle doesn't support ANSI outer joings, but frankly, I was DISAPPOINTED when Oracle added support for the outer join syntax. It was a feature that I wanted NOT to have.

Oracle now supports ANSI outer joins, but the ANSI standard does not support anything like the clearly supperior syntax of either MS or Oracle. ANSI should have changed, or at least adopted a consise notation. The ANSI outer join produces very wordy, unreadable hard to format SQL, with no offsetting benefit. I fail to see why I need two words "LEFT JOIN" and a separate clause (ON or USING ) to indicate that I am using a different operator to join by. Please give me one technical advantage of the ANSI syntax.

Similarly, ANSI does not support CONNECT BY syntax, nor analytic funcitions (PARTITION<nobr> <wbr></nobr>... OVER clauses). Open standards are good, but stupid open standards are not. In every case where Oracle has extended the ANSI SQL standard, they have done so for legitimate reasons that basically amount to ANSI not providing reasonable syntax.

Frankly, I would rather see the open source databases dump ANSI SQL and create an Oracle work-alike open standard.

#

Maybe you should read up on SQL:1999. The mere fact that certain functionality has a different name does not mean that that functionality is not present.

#

I agree that the open source databases can fulfill many needs.
I also agree that, when *properly* implemented and programmed, OS RDBMS's are capable of almost the same, if not better, speeds as the commercial alternatives.

Remember, most tests are biased towards one db or another.
Example:
PostGreSQL will beat MySQL handily if both have to do a 3 query transaction, and you utilize server side procedures written in C (on PGSQL) that does the queries, and you have to do three db calls in Mysql. Despite this, Mysql looks faster in some tests because the testers use the same methodologies on both dbs. This is another subject.

The point is that if a test is performed, and the coding is optimized for a given DB platform, OS RDBMS alternatives are quite competitive in terms of power and speed.

However, this is box for box. You can cluster oracle across umpteen processors and boxes. PostGreSQL cannot do this. Have I ever needed this kind of power? Not yet. Will I? Probably not.

Point is, there are some applications where Open Source is not an alternative, despite the fact that you may like it better. I, for one, will never use commercial DB's if I can get away with it: )

I have almost the same insight and experience you do... And have to say, you are 99.9% right. I have worked with Access, MSSQL 6.5, 7, and 2000, Oracle 8i(took cert classes too), DB2, PostGreSQL, and MySQL. I prefer the latter 2.

Yeah, the guis don't work so well, my co-workers are not too fond of Open Source alternatives, but I am a command line freak so just don't care. If you need a GUI, stick to the commercial RDBMS. I will take PFE(when on windows) or GEDIT, and psql/mysql interfaces.

Need any likeminded co-workers?

Happy databasing,
neil

#

I believe that tora http://sf.net/project/tora ha now a very nice gui for PostgreSQL, MySQL and for Oracle too, it's very close to TOAD

#

Hey do not forget the sapdb, which is the database backend for sap it is GPL, and you can get tons og commercial support.
--
Best Regards
Rune Tonnesen

#

Firebird is better than all the open source RDBMSs you mentioned.
Check it out at http://www.ibphoenix.com

Firebird has way more admin tools and accessories than any of the other ones.

#

firebird/interbase is an extremely lightweight database. it blocks dml, does not support online backups, no stored procedures, and doesnt run on unix.

#

Never heard of it before!(haven't done much looking; I found it hard to imagine that PostGreSQL/MySQL could not fulfill all of my needs)

 
thank you for bringing this to my attention.
I will be checking firebird out, adding it to my performance metrics (firebird optimized code for firebird test of course!) collection etc.

thx,
neil

#

Are you high?
Firebird does support online hot backups via gbak, it has stored procs, triggers, views and UDFS, and it runs on LINUX, SCO UNIX, Solaris, Mac OS X and WIN32 and others.
It is in no way a lightweight database and can handle terabytes of data.
Get some info before you speak garbage.

#

hi,

why ppl forget the best DB -

- open source (FireBird)
- ANSI compliant
- small footprint
- easy administration, run&forget
- faster multiversioning engine
- run on much more platforms than any other DB

SIMPLY THE BEST
go try it :
http://firebirdsql.org
http://ibphoenix.com

And if u need support contact FireBird team.

#

"I'm sticking with MySQL and PostgreSQL, and will look at commercial databases as inferior from this day forward.

That's my two cents. What's yours?"

I say that you must be joking<nobr> <wbr></nobr>:) Are you seriously putting important data (most is) in MySQL and Postgresql? Thats just plain nuts.

#

Putting important data in MySQL is plain nuts.

But putting important data in PostgreSQL is standard. PostgreSQL is generally regarded as being _more reliable_ than Oracle.

MySQL _isn't capable_ of truly competing with the commercial RDBMS in many areas, but PostgreSQL does already. Many organisations use PostgreSQL for their most valuable data because they trust _its_ reliability more than Oracle.

#

More reliable than Oracle??? Haven't you noticed the lazy filesystem sync, lack of good replication (no, the forward commands do NOT count) and lack of true point-in-time backup?

These are absolutely MUSTS for databases with important data in it.

This is scary, if DBAs out there thinks like you we are in deep trouble.

#

Lazy file system sync. Learn how to configure it properly.

Good replication exists, only it's commercial solution.

PITR is coming. They were shooting for 7.3 release but decided that they'd rather have it very right so it's been pushed to 7.4.

And yes, PostgreSQL is often regarded as being more stable/reliable than Oracle.

So what was your point again???!?!

#

Where is the LotLin (LotusLinux) with Lotus Approach? I wonder if Lotus thinks the Approach GUI is TOO friendly and likely to cannibalize their Notes/Domino potential sales. Hence, since IBM took over Lotus, we see LITTLE to no meaningful "innovations" in Approach, just bug and maintenance fixes for the most part (Approach could use more granular security, such as that found in FileMaker/Pro). Afterall, we cannot have the troops in their cubicles making their own forms, can we? No, that might either compete with or just make more work for less innovative, browser-crazed developers who occasionally have a no-degree, no-developer diletante throw stumbling blocks into their path or inadvertently embarrass them... I was such an "instigator" or "undesirable". Do note: I am aware that Lotus Approach is not an "enterprise" grade database, but for joins, forms creations, charts, cross-tabbing, and the fact that it will access ove 7 major database table (with propery accounts and passwords know in advance), Approach, if it were available natively in Linux, would be an EXCELLENT data mining tool for the CUBICLE WORKERS, neverminding what the DEVELOPERS want (other than security and data protections, etc...) (Also, I've use ms access, fox pro, FileMaker/Pro, Vantive, Telemagic, and others as a user, not a programmer...I keep coming back to Approach, and it BREAKS my heart that IBM and Lotus let Approach just STAGNATE!)



That said, I am a user who would PAY big bux (up to $300) for Lotus Approach if it would run NATIVELY in Linux. I want IBM to update Approach, keep the simple interface, extend the security model, increase the number of CHARTS (from 20) (for some reason there is a limit of 20 charts, but forms, crosstabs, and others table sheets can go on and on...



Also, there is a thriving support group for Lotus Approach users, and MAYBE their expertise and such can be a BOON. Approach is commercial, but I am not a "Gotta be dollar-free if it's on Linux" purist. (I don't work for or represent any groups below, but see these links:



http://www.iversonsoftware.com/tabularium/program<nobr>m<wbr></nobr> ing/databases/index.asp?area=u&cats=1944



(for some reason the<nobr> <wbr></nobr>.au domain site whacked 3 of my Konqueror windows associated with my google search...)



http://www.alphasoftware.com



http://www-3.ibm.com/software/data/db2/db2lotus/w<nobr>o<wbr></nobr> rkdata/work-06.html




for crossbabs



A link showing just ONE area, this one being tables joining...



http://www-3.ibm.com/software/data/db2/db2lotus/d<nobr>b<wbr></nobr> 2how2.htm



Check out that SQL Assistant



(Maybe if you have windoze users and hate IBM, but want an Approach/Filemaker-like GUI...)




If you want to check out a site devoted to Approach, see:



http://www.xpertss.com



There was another site which was devoted to Approach, but I cannot find its URL in google and I've packed away my printouts...



Now, the gist of my posting...



Where I once werked, we used to use Vantive in Customer Support and the RMA area, and after two years or so of using it we all mostly loved it. (Because I left IT and went to C/S, and because I had much user experience and diletane forms-designing with Lotus Approach, I got lucky and was ble to sit in on some Vantive meetings and I submitted some 30-40+ Customization Requests, with about 40% of mine being implemented. Thanks to my exposure to Lotus Approach, I had some convenience and useability suggestions to offer.) Our new parent company wanted to nix it with a vengeance, or maybe the Oracle and Oracle contractor developers team was bent on purging Vantive (a competing product). I was so horrified by the Oracle contractor (the people representing or misrepresenting Oracle) forms I wanted to jump off a roof. How DARE they campaign to remove our working, in-house developed, sensibly-lain out, feed-back accepting as well as data-entry forms and tabbed sections. The contractors making the interface to the Oracle back end didn't even want to give us a tabbed form for us to enter Must Have and Nice-to-have Customization Request sheet. The wretched bean counters don't really know how the Data Entry or Customer Support Rep keystrokes, tabs, or flips through forms when dealing with a Customer call for an RMA or whatever was needed. IN Vantive, our CSR could hear the phone ring, get the Customer to give their ID & support contract number, and be done with an RMA in under 3.5 minutes (if no yak and such occurred), often just around 2 minutes. Yet, the Oracal contractor's forms cost an extra 3 -5 minutes, and jarring introduction to their "forms" with all the separate access forms made some RMA calls take an average of 6-8 minutes. Yes, I realize that ORACLE didn't make these forms. They mostly make the back ends, I gather. I find fault mostly with our corporate leaders who didn't give us troops the support we needed. I fault the developers who stubbornly and heatedly interacted with us and on occasion got into battles (yep, battles. I missed that exciting meeting, but heard ALL about the table pounding, pen-gripping and scathing profanities, BOY I wish I'd been in on THAT, of all, meetings....)



It was ghastly. If you have ANY in-house databases and front ends that are to be changed, DON'T let ANY contractors make radical departures from your visual approach to your Customer Support, manufacturing, or other departmental use of the information. It was so ghastly that I was somewhat pleased that Oracle had tried (probably from other scathing input before our company was hooked/roped by the developers) that Oracle bought a company that had very-Lotus Approach-like interfacing. I forget the name, but it allowed cross tabs and chart creations. It was not browser-based. It was pleasingly more client/server-like. It was MUCH more intuitive. It was a working tool. It would have been cheaper had we seen or used this thing FIRST. (I went to the training course at Park Center Plaze in San Jose back in 2000, and gave them some suggestions about Lotus Approach. Maybe Oracle sould snatch the languishing Lotus division from IBM... Linux-aimed databases NEED front ends/standalones like LOTUS APPROACH, native to Linux. )



Thus, I have the attitude that more developers need to be forced to acknowledge and disclose ANY known competing tools and justify to the prospective new Customer why the Customer should spend so much money on a more expensive, less intuitive, more time-consume process.



As long as these (hopefully a different type of) consultants for the Ora side don't grab the pocketbooks of the Lin side, this might be a very good team-up. If they grab or get greedy, it'll be held up as a failure, and politics as usual will make Linux look incable of accepting the team-up. If that occurs, then just point to PostgresQL and MySQL (for back ends...)



Anyway, those horrible web-based GUIs that forced our employees to do TWICE as much work compared to using our home-brewed database. Yes, some of it was due to "old habits being hard to break", but a LOT had to do with kludgy, UGLY, "a different page for almost every distinct type of product category entry" forms. In other words, some normalization seemed too security/isolation-bent rather than ease of data entry and retrieval. Wose, they did away with our tabbed sections. Things had to be pulled down my menus. No jump buttons were coming anytime soon, and Finance put a lid on customizations. Wretched bean counters need to at LEAST demand the contractors mimic what we HAD (visually) and map it to the new back end in order to quell the troops inclination do demand sensible mimics or changes that the developers ended up labeling as "customizations", yet the work was taking WAYYYY too much time and MILLIONS of MILLIONS of dollars. Yep, a boondoggle. We could probably have cared less about the back end as long as the front end was useful and sensible. Heck, the leak kept our change requests in a paper jounal, when in Vantive, we could just click a "Customization Requests" tab, enter the info, check up on the progress of other C/Rs and give feed back to those we didn't even submit ourselves. (About a year or less after I left the company, I heard they scrapped or disbanded the Oracle project, probably (who knows) because of costs, increasing contractor head count, the dot-bomb meltdown dragging down more established companies (like mine), and general marked slowdowns on orders, especially with some larger firms having outright (downwrong) cancelling orders in progress...)



Maybe this was ok with manufacturing, but the Customer Support side went through pure HELL hopping around in the slow interface, the too-many-forms interface, and the near total rearrangement of data presentation, much of which seeming to be to avoid looking like they could have done the work cheaper if they'd mimicked what we had reather than chew up our floor space with close to 20-30 contractors.



I'm just saying NO company has the money or the time to lose control of the contractors. The contractors are supposed to make the CUSTOMERS' dreams come true, not MAKE dreams for the Customer. Otherwise, the contractors need to be a provider of a product and not claim to be "flexible".



Also, if your developers cause, egg-on, or instigate (via insensitivity or callousness) any animosities that steal productivity and raise the bill and the developer on-site body count/cubicle displacement, then FIRE them or warn them...



Regards,



David Syes

#

what about access. it's easily programmable with VBA, comes with office, and hey, works nicely with activeX on the web.

okay, i'll flame myself.

#

He, he, he... Really, ¿Do you think Access is a DB? ¿Is it a joke?

#

mysql = access for *nix. both are single user database systems. actually, mysql is not really a relational database system, just a sql frontend for flatfiles.

#

Finally!!! Someone that knows what they are talking about! Short-n-sweet, if you wouldn't use Access for your database, you shouldn't be using MySQL either!!!

One brownie button for the poster!

#

I see a lot of posts here stating that the commercial products have this feature or that feature that the OS products do not. I am using MySQL for a website that I am working on and almost all that is done are INSERT and SELECT statements. From my past experience, a lot of people do the same. Sure sometimes we use the Relational part of RDBMS but most times people are just storing and retrieving data from them. The commercial solutions, ie Oracle, are intended for a large corprate database, heavy load, major Relational queries. Most of us don't use that level of power. I can't afford to suggest Oracle for something that MySQL can do. But if I need big iron level DB, I know where to look.

#

amen. right tool for the project. I am a bird of this feather. BTW though, be careful with locking if lots of users are doing inserts in a production MySQL db. too many inserts on a busy MySQL db, if not a recent version, and locking is not set up correctly, can cause locking issues. You will hit a critical mass, then BAM, wait times through the roof and MySQL starts seg faulting if load does not die down and give it time to catch up.

I usually use MySQL only when there isn't too much inserting happening. Check out a really busy phpNuke site to see this locking effect and occasional crash happening.

Too many concurrent forum posts, and it gets ugly.

Like anything else, it is a good idea to beat the hell out of a MySQL (or any) application with a load test (that includes the inserts!) to see what is up.

l8,
neil

#

Well, you sorta missed the boat on this one. The major reason why PostgreSQL and commercial databases are popular is because they understand A.C.I.D. Data intergrity is often important. For many small businesses, this is even more so that any high-end scalability.

A.C.I.D. is where it's at. Please note that MySQL is yet to understand what it's about.

#

U forgot to mention firebird. Mysql and Postgres are good, but still lack of features Firebird provided. Mysql have no transaction, trigger, store procedures. Well, Postgres have most features of serious database, but it's getting slower when it raise hundred thousand of records with many complicated transactions.


For Instance Postgres doesn't support OUTER JOIN, and can only be solved by SUBSELECT and UNION ALL. UNION is not good idea, isn't it? Firebird? It's ANSI SQL92, and have almost all features of MSSQL.For ORACLE?<nobr> <wbr></nobr>:P Why should I use ORACLE for my cms application or even inventory application? It's too much expensive!huah!!!

#

Exactly what part of "OUTER JOIN" does postgres not support?

I haven't had much problem with LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN.

Jeff Davis

#

For Instance Postgres doesn't support OUTER JOIN

On the contrary, PostgreSQL has supported outer joins since release 7.1 (7.2 is current, 7.3 is in beta -- i.e. it's had the feature for a while).

#

Geeze, being a big fan of pgsql, the title caught my eye, and I read with interest. I have never read such a poorly researched article before in my life. Please at least pretend you actually attempted to install and test Oracle by skimming the manual before you try another "review".

Oracle 9i and MSSQL both support ANSI style joins. Period.

Here is a link for Oracle:
http://otn.oracle.com/products/oracle9i/daily/jan<nobr>1<wbr></nobr> 4.html

Here is a link for MSSQL:
http://msdn.microsoft.com/library/default.asp?url<nobr>=<wbr></nobr> /library/en-us/acdata/ac_8_qd_09_3mk3.asp

#

In spite of one or two errors due to (slighty) out of date data ( it did take Oracle *ages* to support ansi joins), the intent of this article is absolutely right !

In case you ar wondering, I am an Oracle and Db2 Dba... so I do actually know !

Ok - noone is objective, for me - this article really spoke to where I am on the whole enterprise data nonsense that the proprietary vendors would have us buy into. I say use open source and save millions...

regards

mark

#

I'll post the same reply again.

MySQL doesn't even count, its a waste of time.

Regarding Postgreaql. Haven't you noticed the lazy filesystem sync, lack of good replication (no, the forward commands do NOT count) and lack of true point-in-time backup?

These are absolutely MUSTS for databases with important data in it.

This is scary, if DBAs out there thinks like you we are in deep trouble.

#

Hmmm,

Think I could post my previous message as a reply to yours.

Have you noticed the fsync=true setting for Pg?

As for Mysql...many web sites and companies have their "enterprise" data in it. Why? - It meets their needs - end of story.

Not everyone (in fact hardly anyone) needs all the features that Oracle provdies...but you have to pay for them.

Dont believe everything that th big O tells you.

If more DBA's think like me, then yes - proprietary database advocates are in trouble !

regards

Mark

#

You've stated pretty much my nightmare/experience with Oracle installs and config. Viva MySQL and PostgreSQL! Two of the most effective databases on the market, maybe in this economy companies can save more money by utilizing MySQL or PostgreSQL instead of Oracle and Co.

#

Probably PostgreSQL. It does the enterprise stuff.

#

PostgreSQL is used in mission critical environments all around the world. A recent example of this are the submissions by the ISC, and by the ISOC, for using PostgreSQL as the database behind the management of the<nobr> <wbr></nobr>.org registry.

http://www.icann.org/tlds/org/questions-to-applic<nobr>a<wbr></nobr> nts-13.htm#Response13InternetMulticastingServiceI<nobr>n<wbr></nobr> candInternetSoftwareConsortiumInc

and

http://www.icann.org/tlds/org/questions-to-applic<nobr>a<wbr></nobr> nts-13.htm#Response13TheInternetSocietyISOC

To quote examples for the ISOC proposal:

"... some more detailed case studies of companies that have chosen PostgreSQL for their needs.

BASF: This $29B global chemical company selected PostgreSQL to power a shopping platform for a new initiative in its agricultural products division: the Harvest Partners Network. BASF worked with the Web Commerce Group's (WCG) product, Arkdom Commerce Enterprise Edition, which was developed with PostgreSQL. WCG chose PostgreSQL for 3 reasons: lower total cost of ownership; superior technology; and better maintenance and support.

Vantek K.K: Vantek K.K. is currently implementing a business information system using PostgreSQL, for Tsutaya, Japan's largest rental company that