MySQL: Building User Interfaces

Craig Maloney writes "If you are a Windows programmer looking to create or move your stand-alone database applications away from Microsoft-specific tools such as Visual Basic, Visual C++, Access or SQL Server, MySQL: Building User Interfaces is written just for you." Read on for the rest of Craig's review. MySQL: Building User Interfaces author Matthew Stucky pages 632 publisher New Riders rating 4/10 reviewer Craig Maloney ISBN 073571049X summary MySQL and GTK+ are used to create cross-platform applications, with copious code listings.

What's in the book? The first chapter guides the reader through the basics of MySQL and how it compares to Access 2000 and SQL Server 97. Next, a code listing demonstrates the basics of connecting to MySQL via C using the MySQL C API. the book gives an all-too-brief whirlwind tour to the basics of MySQL. The next four chapters are a tutorial on how to use GTK+ and GLADE, focusing on how these toolkits are similar and different from their Visual Basic counterparts. GTK+ was chosen in this book because of its cross-platform compatibility with both Windows and Linux / UNIX operating environments. The second part of the book takes what was learned about MySQL and GTK+ with GLADE and uses it to create a stand-alone application (a real-world order-entry application). What's Good? Throughout MySQL: Building User Interfaces, Stuckey describes exactly what he is doing and why he is doing it that way. The introduction to GTK+ in the first part of the book describes just about every GTK+ widget available (menus, buttons, sliders, status bars, etc.), and creates a monster busy-box application (not to be confused with the busy-box application by Bruce Perens) demonstrating those widgets by themselves. Later in the book Stuckey uses Glade to put applications together, but not using Glade early on gives the reader a chance to see what is happening under Glade's abstraction. During the building of the order-entry application, Stuckey explains the design decisions behind the widgets. Each window of the application is introduced first with a diagram describing where the widgets will be followed by the code for each widget. The design looks like a Visual Basic application designed by a a programmer, with an eye toward the functional rather than the aesthetics of user interface design, but as an introduction to GTK+ programming it works well. What's Bad? If there was ever a book that required a CD-ROM to accompany it, this book gets my nomination. Authors have to walk a fine line between presenting code snippets that don't make sense by themselves, or risk boring readers with page after page of code that might confuse readers who aren't yet ready to view full code listings. MySQL: Building User Interfaces chose to include the full code listing for everything. This is both a blessing and a curse: readers have the code right in front of them and don't have to worry about being in front of a computer while reading the book, but the flow of the book is interrupted every time something is introduced.

The descriptions also suffer, because those code listings are expected to explain in more detail what is going on. In the GTK+ introduction, widgets are introduced with short paragraph introductions. The real-world application, which should be the focus of the book, reads like an assembly line: A screen is introduced, the widgets are placed, and the code is listed. Worse, files which make little sense without a computer (such as files generated by glade) are presented along with the code listings. This makes reading this book a chore. Thankfully, there is an FTP site with the code ready to use, but future versions of this book would be best served to include it on disc.

Perhaps a balance can be struck in a future edition where important code concepts are highlighted without sacrificing seeing the code in a meaningful context.

So, what's in it for me? Windows programmers who need a hand in getting their applications to Linux or UNIX may find this book helpful (but overwhelming) as they learn. This book stands out as a bridge for Windows programmers to make their transition to Linux and UNIX smoother, but the emphasis and amount of code listings in this book may make Windows programmers choose a different route.

You can purchase MySQL: Building User Interfaces from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Comments

Try SQLite

[hendridm]

MS Access has the advantage (on Windows machines anyway) of being a stand-alone database without needing a service to be installed. For a app where you don't want to have to install MySQL server and have it waste resources, you might consider SQLite. Unlike MySQL embedded, it's release under the public domain. MySQL Embedded is licensed under the GPL and has the following requirement:

... any program which includes, by linking with libmysqld, the MySQL source code must be released as free software (under a license compatible with the GPL).

Although, depending on what you want to do with the software, this may not be a problem. I'm all for free and open source software, but a brother's gotta eat too.

Re:Try SQLite

[Anonymous Coward]

Can someone expand on the difference between a stand-alone db and one that needs a service to be installed? I just don't understand what the differences are between Access and other databases such as mySQL...

Re:Try SQLite

[man_of_mr_e]

Access is simply an application. When you open the database, it's just like opening a file in a word processor.

MySQL is a service that runs seperately from the application and must be started and stopped seperately. In most cases, the service is started at boot time and runs all the time, wasting resources unless you manually stop it.

Re:Try SQLite

[cscx]

An Access database (usually a .mdb file) is just a binary file. Your application uses the Access DB driver to read/write to this file.

MySQL on the other hand (which may consist of multiple binary files) has to run a daemon (i.e., server) to be accessed. Your application uses the MySQL driver to talk to the MySQL daemon, either through TCP/IP or a local socket.

This is why MySQL will *never* be a replacement for Access, despite what I often see on slashdot. You can fit a small Access database on a floppy. Conversely, you may need a full fledged DBA to run MySQL.

Re:Try SQLite

[Mod Me God Too]

I do a little DB development. The basic difference is a DB system like MySQL, Oracle or SyBase is that the 'DB' is the back-end (indeed it is a server-type process) which is interrogated by SQL statements (the front end may be built in C++, VBA, whatever), whereas something like Access is an all-in-one solution with the back-end and the front-end in the same app mish-mashed together (it may purport to be SQL-like, but only from within the app, not as a server) - I think MS still provide Foxpro as Access simple can't hack really big work. So an SQL-type database is the classic client-server, Access and Paradox don't seperate front and back ends (they say they can be split, but that is a bit of a bodge job).

Wikipedia has a more thorough discussion here (for SQL) and here (databases in general but quite a read)

Re:Try SQLite

[gandy909]

I suppose the app could easily be written to start the server when it starts and shut the server down when it ends, thus not wasting resources all the time...couldn't you? Not that mysqld is a resource hog anyway...

Re:Try SQLite

[Anonymous Coward]

MySQL is a service that runs seperately from the application and must be started and stopped seperately. In most cases, the service is started at boot time and runs all the time, wasting resources unless you manually stop it.

I'd remove the "wasting resources" part from that description, because it's behaving like any server process does in that regard. Your SMTP server and HTTP server are also "wasting resources" while they're sitting there waiting for inbound connections.

A typical use for a database such as mySQL or postgres is as the back end for some CGIs, such as a message board or shopping cart, and the way to do that is to have them running as server processes. Of course, most message boards and shopping carts are a complete waste of resources, so maybe that's an accurate description after all.

Re:Try SQLite

[ninejaguar]

I'm not familiar with MySQL. Is it possible to start and stop the service from your application, as your application loads and unloads? I mean, even if there's a delay in starting MySQL (MS SQL starts and stops very quickly), it should be possible to do it in the background as your application loads without much (if any) delay from the user's perception.

I've been thinking of trying that with FireBird.

= 9J =

Re:Try SQLite

[hendridm]

> I suppose the app could easily be written to start the server when it starts and shut the server down when it ends

True, but besides being sort of hokey, the requirement of having MySQL installed might not be ideal if your target audience isn't savvy enough to install it, or perhaps don't want extra, normally unnecessary, stuff on their computer. I guess it all depends on your needs and target audience.

Re:Try SQLite

[jcoleman]

I'm all for free and open source software, but a brother's gotta eat too.

Hope you like Indian food!

Coleman

Re:Try SQLite

[laird]

"I'd remove the "wasting resources" part from that description, because it's behaving like any server process does in that regard. Your SMTP server and HTTP server are also "wasting resources" while they're sitting there waiting for inbound connections."

Good point. I'll elaborate in case someone doesn't get the point -- under UNIX (or Linux) there's essentially zero system cost to having a server running if it's not active -- it just gets swapped to disk by the OS' virtual memory system, and doesn't take up any CPU time unless it's called, so all CPU and RAM resources are available to other applications. So unless you _really_ care about a few MB of disk space, or a second or two of boot time, you'd never notice that MySQL is running.

Re:Try SQLite

[Frymaster]

he service is started at boot time and runs all the time, wasting resources unless you manually stop it.

right. think "daemon".

microsoft just uses the word "service" because sco owns the word "daemon".

Re:Try SQLite

[AndroidCat]

Hmm. Access is an application which uses the underlying MSJET engine. Anything that can create an ADO object (or OLEDB or DAO...) can use that engine without Access running. The engine also handles reference counts so that if more than one program is using it, it won't load multiple copies and it won't unload until the last one closes it.

Re:Try SQLite

[Anonymous Coward]

Ah, Wikipedia, ever trusty. Surprised you refer to Paradox though, havn't seen that in years!

Re:Try SQLite

[laird]

"This is why MySQL will *never* be a replacement for Access, despite what I often see on slashdot."

That's true -- it would be more accurate to describe DBM as a replacment for Access, and MySQL or PostgreSQL as a replacement for SQL Server.

Re:Try SQLite

[cscx]

Actually, I think you may have been mislead if I read you correctly. Another person in the thread was correct when they said that Access is a front-end to the actual Access database. You can run SQL queries against an Access database... the functionality is built into the driver.

Re:Try SQLite

[cscx]

Hmm, does DBM allow you to run SQL queries against it?

Often I've seen Linux users suggest using PHP + MySQL as a drop-in replacment to Access. That is really way off base.

Does DBM have a GTK/Qt interface, or is it only through a Perl CGI?

Access' main advantage is that the interface can run as a standalone app.

Re:Try SQLite

[P-Nuts]

An Access database (usually a .mdb file) is just a binary file. ... You can fit a small Access database on a floppy. ...

Are you aware that the data in a MySQL database can be ripped out to a file with the mysqldump command? Okay, perhaps passing it through bzip2 or similar will improve the odds of fitting it on a floppy.

And an Access database in an MDB file is hardly standalone, you still need a machine running Access to get much use out of it.

Re:Try SQLite

[Mod Me God Too]

Yes, you are quite correct, infact I've been using ADO ad-hoc in Excel VBA for a couple of months (it was not a pleasant experience, but VBA never is).

[slaps wrist]careless![/ceases slapping wrist]

Re:Try SQLite

[cscx]

Are you aware that the data in a MySQL database can be ripped out to a file with the mysqldump command? Okay, perhaps passing it through bzip2 or similar will improve the odds of fitting it on a floppy.

Well obviously, I too can do a text export of Access and gzip it as well! But that doesn't, uh, help the situation.

And an Access database in an MDB file is hardly standalone, you still need a machine running Access to get much use out of it.

Wrong. You don't need Access to create/update/use an Access database. The functionality it built into Windows (actually part of a bigger module called Jet).

Re:Try SQLite

[cscx]

And an Access database in an MDB file is hardly standalone, you still need a machine running Access to get much use out of it.

Actually let me expound on this a bit further... you can write a VBScript in your favorite text editor to do SQL operations on an Access database... just use an ADO object...

Re:Try SQLite

[AndroidCat]

Do they actually include Jet as part of Windows now? It used to be that you needed an app that installed the engine. (Like Office, duh! :)

Well, that would make my installation much easier if I just use it as the default db... (he said as he slides further into Microsoft's trap...)

Re:Try SQLite

[w00t_sargasso]

So... You are telling me that Microsoft Access, or at least that part of it _necessary_ to read mdb's will fit on the floppy as well?

I like MySQL because it _is_ a server -- You can create any application in (almost) any language on any platform (think php) to interface with a MySQL database. Try telling me that you can do that with access.

And you _can_ also fit a SQL db on a floppy.

To sum:
-You cant fit M$ Access on a floppy, but you can a db.
-You cant fit MySQL on a floppy, but you can a db

-MySQL is free (and all applications do remain PROPRIETARY provided they dont modify the MySQL source in any way -- Read the licence properly dudes.
-M$ Access is NOT Free in ANY interpretation of the law (to test, try telling Microsoft that it's free...)

Re:Try SQLite

[torok]

MySQL will *never* be a replacement for Access

I am in complete agreement.

I write small DB apps for small businesses. My clients all have MS Access installed. I would love to be able to use all open-source tools to develop these apps, but so far nobody can show me something that's as simple for my computer-illiterate clients to install as "Here, copy this .mdb file to your hard drive and double-click on it". Installing one more piece of software wouldn't be hard either, but if I have to say "Here, install OpenOffice first, then install MySQL, make sure it's running, then run this script to put the right data in MySQL, then make sure you have the right MySQL drivers installed, THEN double-click...." You get the idea.

Most people have a hard enough time with "Copy the .mdb file to c:\dbprogram". Requiring them to install a service with appropriate drivers on their old PentiumII running Access2k is a nightmare and suicidal for a small software company.

Re:Try SQLite

[ttfkam]

It's not like you can just drop an *.mdb file onto a box and use it. Access still needs to be installed. This puts it on about equal footing with MS Access in that even though MS Access is more common, MySQL is a free download if the system you're on doesn't have it. Of course, that assumes your office is running MS Windows. (Not a bad assumption, but good to keep in mind.)

Along those lines, you shouldn't be coding directly to MS Access anyway. Writing your stuff to an ODBC datasource is a much better choice. Luckily, every popular database in existance has an ODBC driver. With ODBC (or ADO) and a client-server database, you can have the database local to you for initial development and then move it off to a dedicated server without changing a single line of code. With a MS Access database, you can have a dedicated fileserver, but locking issues between clients get really hairy after more than a couple of people connect and work at the same time.

SQLite is great if memory constraints are very tight and you probably won't move to a client-server model in the future. How much is a 256MB DIMM these days? 256MB is enough for most relational databases to have a field day with the datasets that MS Access is used to. And memory's only getting cheaper. Make sure that you have a real need to constrain yourself to the in-process model before jumping in.

Also note that if you use an ODBC datasource, you can use the comparatively wonderful Access frontend and have a real data store in the background doing the heavy lifting.

But then, if you really wanted to do it right, you'd use something better than MySQL on that backend like MaxDB, Firebird, or PostgreSQL; Same cost, more features, scale better with concurrent writes, and safer for your data (actually tells you when crap data is trying to find its way in) than MySQL. Remember, if you're talking about replacing MS Access, you're probably not talking about a web environment anymore. Chances are that it's not 99% reads.

Re:Try SQLite

[spasm]

"You can fit a small Access database on a floppy. Conversely, you may need a full fledged DBA to run MySQL."

On the other hand, my current fieldwork database (at the point we were switching from access to mysql) was a 50mb Access file, but a 200kb mysql dumpfile.

I could refresh remote copies of the mysql version over a 56k dialup (or by floppy) no problem, but duplicating the access db (don't even talk to me about 'sync' - I lost count of how many rollbacks to backup I had to do after using sync. one of the reasons we migrated) was impossible without boradband or cd burning.

Re:Try SQLite

[Anonymous Coward]

I don't, but once in awhile I pass around the peace pipe for good measure...

Re:Try SQLite

[delus10n0]

Actually, all you need is the Microsoft Data Access Components installed ( http://msdn.microsoft.com/data/ ) if you wish to just read/write to the Access database.

And as a side note, if you're doing any sort of development in Access, your database shouldn't even be in the Access file. You're much better off using a real database, like MS SQL or MySQL/etc. to handle the data storage, and link to that through ODBC or ADO (I prefer ADO, but whatever.) Also, your program's logic (especially when it's complex) should be contained server-side, in the form of a stored procedure in the database. Anything else is asking for trouble.

Re:Try SQLite

[cscx]

Try telling me that you can do that with access.

In a word, yes.

Re:Try SQLite

[ncc74656]

MySQL is free (and all applications do remain PROPRIETARY provided they dont modify the MySQL source in any way -- Read the licence properly dudes.

Your app only remains proprietary until you decide to distribute it. If you distribute an app that uses MySQL APIs, either (1) you have to GPL your app or (2) you have to pay MySQL if you don't want to GPL your app. It doesn't matter if your app is to be BSD-licensed, closed-source, or whatever...if it's not GPL'd, you have to pay MySQL if you distribute your app.

Re:Try SQLite

[millahtime]

wait, who still uses a floppy. The only time I have used one of those in the last 4 years is an ftp install of reebsd. other then that, what use is there?

Re:Try SQLite

[man_of_mr_e]

Resources does not simply mean CPU time. It also means memory. Further, it's another process in the process table, and unless you ahve the O(1) scheduler running, it increases the amount of time the scheduler takes to run new processes.

Now, all of those things take only a fraction of a microsecond to accomplish, but it's still "wasted" if you're not using it.

Re:Try SQLite

[man_of_mr_e]

Everything that you said is true, but MSJET doesn't run as a service. It's just DLL's that get loaded into the applications process space and execute in the context of the application.

Re:Try SQLite

[man_of_mr_e]

Not exactly true. While it's true that the service won't use many (if any) CPU cycles while waiting, it will use memory.

You're incorrect that this memory will get swapped to disk, since that will only happen if memory becomes low.

The problem with this is that Linux (as do many OS's) has a system of buffers and caches which use unallocated memory to speed up the OS. Having a bunch of memory allocated (but not enough to swap them out to disk) lowers the number of buffers and caches available to the OS, thus decreasing performance.

Finally, whether or not the memory is swapped out to disk, it's still resources, and it's still wasting them if the service isn't being used.

Re:Try SQLite

[joggle]

Do they actually include Jet as part of Windows now?

I'm pretty sure it comes standard with Windows. I know it has been a free download from microsoft.com for quite some time (at least several years).

Re:Try SQLite

[ttfkam]

You're much better off using a real database, like MS SQL or MySQL/etc. to handle the data storage...

...

Also, your program's logic (especially when it's complex) should be contained server-side, in the form of a stored procedure in the database.

As MySQL doesn't support stored procedures, these statements are incompatible. But then you knew that didn't you, you cheeky bastard. ;-)

Re:Try SQLite

[Narcissus]

MySQL Connector/C++ (the C++ API from MySQL) is LGPL.

So long as you link it correctly, you do not need to release the source code to your application: no matter how confusing the licencing on the MySQL website is...

Re:Try SQLite

[dolmen.fr]

Do they actually include Jet as part of Windows now?

Yes, they do. Jet was even used in Windows services in Windows NT 4.0 Server, such as DHCP or Winbind even if the the database didn't had a .mdb extension. I don't know if it is still the case in Win2k.

The lastest version MSJet can be installed with Microsoft Data Access Components : http://www.microsoft.com/data/

Re:Try SQLite

[stephanruby]

This is why MySQL will *never* be a replacement for Access, despite what I often see on slashdot.

It's all a matter of perspective. To a programmer, Access is the means of a cheap no-frills datasource that can be served as a service on the internet or an intranet. So in that sense, MS Access is already being replaced in that area. And to some extent, even SQL Server 2000 is starting to be replaced by MySql in some instances. And no, I'm not just trolling. And yes, I do realize that SQL Server is still much easier to use, especially now that the .NET studio does most of the work for you. But just to give you an example, my favorite host CrystalTech, which used to be very windows-centric, is now pricing MySql way below SQL Server and suggesting MySql for some jobs that SQL Server can't handle.

Re:Try SQLite

[dolmen.fr]

Yes. Microsoft Access stores the application code, forms and all in the .mdb file with the data.

Re:Try SQLite

[ttfkam]

Even with an O(n) scheduler, an idle daemon is nothing. While some others may be impressed with your use of Big-O notation, many of us are not. Even an O(n^2) algorithm isn't that bad if your dataset is sufficiently small. The O(1) scheduler is a glorious thing only because the number of processes (forks, threads, etc.) reaches very high amounts on a large, loaded system. At hundreds of thousands of processes/threads, the choice of scheduler makes a big difference in overhead. And for that matter, if the number of processes without an idle RDBMS running is 100,000, and the RDBMS pushes it to 100,005, the scheduler isn't going to care. It'll be highly unlikely that you could reliably measure a difference.

At smaller numbers (one or two hundred), who cares? Your server isn't using 100% of your resources (CPU or otherwise). They are just going to waste if there's no RDBMS loaded. ;-)

This argument is old. It's like the assembly vs. C debates in the seventies. ...and just as clear cut. "Premature optimization is the root of all evil." Works for system administration as well as software engineering. The real question isn't "which is more efficient?" The real question is "which gets the job done faster?" If I can start a RDBMS and walk away, that constitutes "faster" to me.

Re:Try SQLite

[smchris]

I'm all for free and open source software, but a brother's gotta eat too.

Nonsequitor.

MySQL and PostgreSQL are Mozilla-license.

Write great server code, write a great Windows client for it and sell the package as you desire.

Re:Try SQLite

[YrWrstNtmr]

An Access .mdb can hold both the 'back end' tables/views, and the 'front end' GUI, code, SQL statements, procedures, reports, etc.

Although, a better way to deploy an application in Access is two mdb's. One containing the backend table structure & data, and one containing the front end GUI. That way, when you want to implement new features in the GUI, you don't have to screw with the data. Simply delete the prev version front end, ad drop in the new one. Via code, this can be automatic & seamless to the user.

Re:Try SQLite

[YrWrstNtmr]

n the other hand, my current fieldwork database (at the point we were switching from access to mysql) was a 50mb Access file, but a 200kb mysql dumpfile.

You are talking about two different things. How large would the Access mdb be if it were only the table structure and data? Your 50mb Access file presumably contained all the front end junk as well. Which can be quite large. Especially if you don't Compact once in a while.

Re:Try SQLite

[drdreff]

This is why MySQL will *never* be a replacement for Access

The moment you want to share a database between multiple users simultaneously you need to replace Access. In one deployment I have taken the half step of using Access as the front end for MySQL using ODBC.

We don't lose data and the DB doesn't crash any more. The front end is still a pain to administer, but what the customer wants...

You can fit a small Access database on a floppy.

Is that a tremendous floppy or a uselessly small DB? In my experience, as soon as an Access DB becomes useful, it becomes unstable.

Re:Try SQLite

[laird]

"Does DBM have a GTK/Qt interface, or is it only through a Perl CGI?"

DBM is a C library, so it's trivial to use from C and C++, and has API's for Java, Perl, Python, PHP and Tcl. It doesn't have a GUI, unless you consider an IDE a GUI :-). So you could write GTK/Qt code to talk to DBM, but it's not tied to any particular GUI. So it's not a drop-in replacement for Access -- it's 100x smaller and 100x faster (YMMV), but requires you to write code. The parallel with Access would be that it's an embedded filesystem-based database, as opposed to a stand-alone database server (a la SQL Server, MySQL, etc.).

"Hmm, does DBM allow you to run SQL queries against it?"

Nope, and they'd call that an advantage -- dbm is an extremely small and efficient C library -- making your app generate SQL, then pass the SQL to the database to parse out your meaning would just add complexity and slow things down. Of course, there are times when SQL is important, but for straightforward applications where efficiency is critical (i.e. when you need a database embedded in an application, not for use as a general database) dbm is a nice way to go.

If you want to use SQL, you can uselibmysqld. It's not as efficient as DBM, of course, but it does give you full SQL, which has a lot of value, too.

Re:Try SQLite

[IANAAC]

microsoft just uses the word "service" because sco owns the word "daemon".

Apparently SCO scared RedHat too :-)

/sbin/service mysqld stop

Re:Try SQLite

[sparkane]

Access is a funny thing: it's actually four programs in one.

a) Jet database/sql engine.
b) forms engine.
c) reports engine.
d) visual basic scripting (only differs from normal VB in that it doesn't use the same forms engine and can't compile standalone code).

I don't count macros. Never have. :)_

Re:Try SQLite

[Anonymous Coward]

I thought that was "Dameon"

Re:Try SQLite

[Saint Stephen]

Thanks, I'll give it a try. However I prefer rolling my own with libXML and gnomes GtkTreeViewModel -- use libXML for serializing the data, and keep the entire database in-memory in a GtkTreeViewModel.

It's perfect for single-user, small, personal databases. No binary interfaces to fail. Transactional consistency is a snap -- you commit all changes in one fell swoop.

It's good enough for 1mb databases. If I need more, I'll use a "real SQL".

Re:Try SQLite

[mabhatter654]

The bigger draw for Access is that MS allows program makers to redistribute the access core program for their front-end to use. the advantage is that "headless" Access can be included with whatever app you're setting up for your users. It still requires having the same resources available that you'd need to run the full program...and the MS office version compatibility problems!

the problem with MySQL is that you HAVE to get the USER to install the full verson of MySQL on their station if you want to use it...you can't use embedded without paying seperately for it. It's not any better or worse than MS, but the loophole in using MySQL is to create your program completely independant outside MySQL [that's how PHP handles things] and simply let the User get and agree to the license seperately. unfortunately that's really disruptive to IT oranizations to install 2 seperate programs...as well as training users in somewhat advanced [starting services rather than clicking icons] computer operations! Compariblity to Access is an issue MySQL needs to address publicly...requiring people to GPL everything just to have an app USE MySQL is not a valid answer...they need some "guidelines" similar to the "arrangement" with the PHP guys because it's the same issue. Or, adjust their program to be more "invisible" to regular users...although on windows the installer is pretty good...but programs have to be able to start and stop MySQL services independantly...without violating GPL.

Re:Try SQLite

[mabhatter654]

Not really, while some apps use the .mdb file type what's really happening is that MS has just bundled the Access "program" executable with your program when you compiled it and created the package. If you can see the access file type under the create new menu, you've got a "headless" version of access installed..complete with Office extensions and exes!

Certian apps can "connect" to an access file type independantly...just like reading a file. What MySOL should do is create a Mini-MySQL file type and give it access-like properties...while still keeping it a seperate program...rather than embedded. Their GPL-only stratagy is shooting them in the foot here frankly. Or rather, they're not getting good info & samples out there about how to get MySQL to be more Access-like.

or perhaps...somebody else needs to go after access...Unfortunately there's not a large market. If your using Linux, you can get pre-installed MySQL in most distros now...you just have to use it! so there's no need for Access on linux. If you have an app to run on windows, you can just set up full MySQL [again for free] and it will work like on linux. The only real problem is in creating apps for OTHER people..particularly on Linux for windows users. Even then, MySQL is fairly small and the installer quite good...if there was just a more "legal" way to bundle it with your program and not violate GPL..while setting it up automatically from inside your app..or a properly GPL'd helper app.

Re:Try SQLite

[cookd]

A bit over-simplified, but one way of looking at it is whether the database engine runs in the same process as the application that uses the database.

A metaphor may be useful here. You could consider it the difference between grabbing a meal in your own kitchen or getting take-out. Making a meal in your own home doesn't really take any coordination from anybody, and you can just take care of it yourself -- you just have to have your own food and your own kitchen appliances. If you want to order out, you have to look up the restaurant's phone number, call, make an order, pick it up, and pay for it, but you don't have to have your own food or your own appliances.

In a standalone database, the database engine runs in the same process as the application (i.e. you eat in your own house). You "start" the database engine by running the app (the kitchen is only producing food when you want to eat). This is simpler because there is just one program to manage, the data storage acts just like any other file you want the application to open, and the database engine is just another module the application uses.

In a service (or client/server) architecture, the database runs as a separate process (the restaurant is a separate establishment). The database engine is one program that can start and stop by itself (the restaurant opens at 9:00am and closes at 10:00pm), and the application is another program that can start and stop independently. This is a bit more complicated because the application has to make sure the engine is running, connect to it, and authenticate to it.

The advantage of the standalone database is simplicity and efficiency in the single-user or single-computer scenario. If you only have one client accessing the database, there is no need for authentication, interprocess communication, locking, etc. The database is just another file to be opened, edited and closed.

If a standalone database is used by multiple clients at the same time, things start to break down (too many cooks in the kitchen at once). The advantage of the standalone database was the simplicity of having only one program to worry about, but if there are multiple clients, that is no longer the case. The different client programs now have to coordinate their access to the database. This is not necessarily hard to do, but it isn't very efficient or scalable. Your kitchen just isn't designed to allow 400 individuals to make their own meals every day. People are going to fight over who gets to use the oven next, somebody is invariably going to leave a mess and not clean it up, and you're probably going to run out of food (who ate my cheese?).

When there are many clients, it is better to just have one database engine servicing all of them, so you create a service that starts and stops independently of the clients. A single process is in charge of providing the data (i.e. a single establishment is dedicated to the task of providing food). This would be wasteful if only one person were to eat there each day, but it is much more efficient if many eat there every day.

Re:Try SQLite

[w00t_sargasso]

So we have established the need then for MySQL to clean their licence up a bit?? I mean, with so many interpretations bouncing around, their bound to lose money...

Re:Try SQLite

[man_of_mr_e]

Funny, but I don't consider shutting off a service you're not using to be "premature optimization". Not only is it pointless to run the process, a waste of resources (no matter how insignificant you might think they are), but it's also a security risk.

Frankly, I can't believe any Linux enthusiest would advocate running services without need. That's stupid.

Re:Try SQLite

[man_of_mr_e]

Actually, the biggest draw to access is that everything (outside of the runtime) is encapsulated in one single mdb file. All your forms, queries, data, etc... Just click and go. Various frontend tools for MySQL (or any client/server database for that matter) mean seperating your data from your application, which is more sound, but nowhere near as convenient. As we know, convience rules the retail market.

Re:Try SQLite

[AVee]

So we have established the need then for MySQL to clean their licence up a bit??

True. I've heard so many versions about when you need a license and when not that it makes one thing perfectly clear, it unclear.

I mean, with so many interpretations bouncing around, their bound to lose money...

Well, i doubt that. A lot of bussinesses might you buy a license, just to be sure. Clearing the matter up might just be not in the interest of MySql A.B.

The server is licensed GPL, so anyone can download and install that. As soon as you modify the server you will need to release that changes, but you often would you want to have to maintain your own version af a database server... The client libs are where it get's interesting. These are mainly GPL, wich means you can't link closed-source software against them. Yet some of them are LGPL in wich case you can link your closed-source software against it.

Re:Try SQLite

[dave420-2]

Wasting resources? I've got a windows box here serving a floor of sales consultants, letting them mix-up packages in real-time for customers, and it's currently using 15megs of memory, and about 0.1% cpu time. That's hardly wasting resources, seeing as it's instantly accessible.

Re:Try SQLite

[Geccie]

"... any program which includes, by linking with libmysqld, the MySQL source code must be released as free software (under a license compatible with the GPL)."
This does not appear to be a problem. It says that you must release the MySQL source code, not the code that links with it.... Aside from having to maintain and offer a copy of the MySQL code, I dont see a problem.... Rebut? - Geccie

Re:Try SQLite

[Fulcrum of Evil]

So long as you link it correctly, you do not need to release the source code to your application: no matter how confusing the licencing on the MySQL website is...

Personally, I'd prefer to avoid the issue entirely and use something that is more explicit about not claiming my code. After all, who's to say that the MySQL guys interpret their license the same way you do?

more sellers

[tedtimmons]

amazon link

isbn.nu link

half.com link

No affiliate tags are used above. But here's the amazon link with my tag, if you feel generous.

My opinion

[W32.Klez.A]

I have read this book, and used it to my advantage. However, they don't properly explain some parts about what you're doing, and why you're building the interface. I was hard pressed to find the exact reasoning they used for some of the things they did, but other than that, it's a great resource for mysql developers such as myself.

Re:My opinion

[sentanta]

Dude - the picture at your sig is really foul. I'll need a few more beers to wipe that from my memory, just like that girl in . . . never mind.

MySQL Feature Set

[Saeed al-Sahaf]

Before all the naysayers start in on what a flaky database MySQL is, how it doesn't do this or that, that it just a toy, please visit their web site and see what its current feature set is.

Re:MySQL Feature Set

[Anonymous Coward]

Nah, it still sucks... Use Postgres

Re:MySQL Feature Set

[Anonymous Coward]

another interessting read is here.

Re:MySQL Feature Set

[Anonymous Coward]

Hey, this is /.. Knocking MySQL's features are like complaining that Debian is out of date and ships with an old new kernel. It's all part of being an arrogant geek - what I do is obviously better than what you do!

Re:MySQL Feature Set

[w00t_sargasso]

Damn straight!

MySQL -> More stable than Access More accessible than Access (Think TCP\IP interface) More extensible than access


And it's open source! (Although, if you modify the source, any apps you create that interface with it become open source too...)

Access == Microsoft
:therefore Access != Cheap
:ALSO therefore Access != Stable

Re:MySQL Feature Set

[Mitleid]

Question...

What if I use Access to build a crude front-end to a MySQL server (which I am painfully drudging through right now...) Do I need to liscence MySQL, or am I just commiting a cardinal sin, asking for the universe to collapse in all around me? ...As far as GPLing my code, hell, I don't care who looks at my MS Access VB. It's a mess. Ha. But seriously, if I'm going to deploy this setup in a small bidness, what do I need to consider...?

I've been wondering all this for a while, but it came up here so I thought I might as well ask. Hope I'm not OT.

Re:MySQL Feature Set

[w00t_sargasso]

So long as you don't modify MySQL, I BELIEVE you should be fine. Try checking the licence on MySQLs Website

Re:MySQL Feature Set

[Geccie]

Ha. But seriously, if I'm going to deploy this setup in a small bidness, what do I need to consider...?

Spelling :)

Make sure you use MySQL version = 4.0

[spun]

Before that, the dang thing doesn't support nested subqueries, or any kind of subqueries on inserts and deletes. Found that out the hard way recently (the docs don't make it very clear) and had to rewrite a bunch of elegant SQL as less elegant Perl loops and wacky joins.

Still, I love MySQL. Postgres is more full featured, but for the smallish web-based applications I write, it's overkill.

Re:Make sure you use MySQL version = 4.0

[blackrat]

Exactly, MySQL is lean and mean and usually competent enough to solve the DB stuff at least in my applications/web sites. But as you say, the pre-4 versions do not support nested sub-queries -- also had to make some kludgy PHP constructs and quite a few not too nice join statements recently just due to this annoying fact. Unfortunately, upgrading to v4 was not option. :-/

Re:Make sure you use MySQL version = 4.0

[_fuzz_]

Actually, subqueries are only supported in 4.1, which is not considered production level. So there still isn't a production-quality MySQL server that supports subqueries.

Re:Make sure you use MySQL version = 4.0

[ttfkam]

And it provides so little in the way of data integrity, that you'll have to spend that much more time adding the necessary functionality into your app layer.

Example? Okay, whip out your MySQL client and type with me...

CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
num1 INT,
num2 INT NOT NULL,
num3 INT
price NUMERIC(4,2),
code VARCHAR(8),
);

INSERT INTO test VALUES (
0,
99999999999999,
NULL,
'A quick brown dolphin...',
21474.83,
'ABCDEFGHIJK'
);

...now after this, it should have given you a message like:
"Query OK, 1 row affected"

Seems to me that the data was a collection of square pegs to round holes, but MySQL never even dropped a warning.

So let's check out that table again:

SELECT * FROM test;

1 row with the following is returned.

id: 1
num1: 2147483647
num2: 0
num3: 0
price: 999.99
code: ABCDEFGH

Now stop. Take another look at the table definition. Take another look at the inserted data. Take another look at the output. Take another look at what has been stored in your database. Let's sum up: your primary key (your lookup key!) is not what you expected, the numbers are all different from what you entered, the price does not conform to your data schema (should be a maximum of 99.99), and your character string has been truncated...ALL WITHOUT AN ERROR OR WARNING.

More examples at MySQL Gotchas. All of this in MySQL v4.0 and above. None of this crap happens in Firebird 1.5, PostgreSQL 7.x, or any popular closed source relational database.

...and it is crap. You have crap data in your database, and MySQL never let you know. For those of you out there who never ever write code with bugs, there's no problem; Go on ahead using MySQL. (Then again, you're in high demand for senior, very well paying jobs using Oracle or DB2 in Fortune 100 companies if that's the case.) For all the other folks out there (which includes everyone out there doing web development with dynamically typed languages like Perl and PHP), MySQL will happily insert crap data.

And yes, there's a PostgreSQL Gotchas page. Tables are lowercase by default, and it requires the "AS" keyword for column aliases. Look at that list for PostgreSQL. Look at that above example. Can someone say with a straight face that these lists are comparable? Can someone say with a straight face that they want to explain to the computer-phobe why they got 2147483647 in their data? Can someone say with a straight face to their boss that these errors are acceptable in a production environment?

Re:Make sure you use MySQL version = 4.0

[Delirium Tremens]

Thanks a bizillion times for those links!
You have succesfully scared me away from MySQL...

Next time I start a new project that requires persistence, I am dicthing MySQL.

Re:Make sure you use MySQL version = 4.0

[skiflyer]

I have enough gripes with MySQL... but subqueries are not one of them. They're highly overrated, and nine times out of ten the correct joins will run circles around them performance wise.

Re:Make sure you use MySQL version = 4.0

[chromatic]

You failed to quote the third sentence of the PostgreSQL gotchas page:

This is work-in-progress; a few more gotchas will follow as time allows.

Use what you like, but comparing a list of twenty gotchas to a page clearly labeled "not finished yet" seems a little bit unfair. (Your claim that "professional" companies only use statically typed languages also seems rather unfounded.)

Re:Make sure you use MySQL version = 4.0

[Anonymous Coward]

Who writes INSERT statements without giving field names? And if you don't know your table structure and what you are putting into it, then you deserve the anquish to come.

I do database work with MySQL and SQL Server 2000. All functions I write check that the data I'm getting or putting to the database matches the data type. It's a little extra legwork, but I don't carry a pager for dumb mistakes that can be avoided either.

Never throw data at your DB like as parent poster describes above.

I'd give it at least an 8

[Anonymous Coward]

I've been working through this book for quite some time, and now that I've extracted all i can I have to declare myself delighted with it. The examples are good, deep enough to convey the lessons but shallow enough that not too much is irrelevant. I now have, as the author promised, several applications that will compile easily under both linux and win32. If i can fault the book at all , my only reservation is that is would have been nice to see all the necessary software included on the cd. Downloading all of the required applications and libraries (particularly for windows) led to a treasure hunt accross the web. That aside I would recommend this book to anyone who is not new to programming and databases, a little prior knowledge will be required as this is definately not a complete beginners book.

Interesting Book

[saberworks]

I read most of this book (maybe an older version?) last year. I found it very interesting because it basically gave me the idea to let the staff members of my web site update the site through a windowed interface as opposed to a web interface. MySQL doesn't have to be running on the same computer as the program is running on, so this would be simple. That way you could provide a rich client (for staff members, not end-users) to update a web site based on MySQL.

I know, it's not an original idea, but it's interesting because the book was a nice, step by step guide to doing just this. For what it's worth, I enjoyed it.

On the other hand, I ended up spending time with XUL instead.

Re:Interesting Book

[gbrayut]

Just wondering why you wanted to get away from the web interface? If done correctly, isn't a web interface much better then a client/app, since it provides a single interface that works on all operating systems and from all locations?

Re:Interesting Book

[saberworks]

There are a few problems. There are tree structures that are hard to reproduce on a web interface (think of a directory tree in Windows Explorer with collapsable nodes) [note: I said hard not impossible]. There are also other widgets (like select boxes that also allow you to type values, and rich text editing widgets) that are only reproducable using javascript/dhtml. Most of the 3rd party widgets out there require certain version of Internet Explorer, and while the visitors to my site are mostly all Windows users, most of the staffers don't use Windows, and if they do, certainly don't use IE.

On top of that, I wanted to do some experimenting (just for fun). We're not really a business or anything like that, so there was no real business decision made for it.

Re:Interesting Book

[EastCoastSurfer]

Web interfaces work great for read-only content. Complex UI for data entry is a PITA on the web though.

If your app has a ton of content that is read-only(ie. a reporting app of some sort) and only a few people doing the updating then it makes sense to have a fat client for data entry and a web interface for everyone else.

Re:Interesting Book

[Stinking Pig]

probably because that "if done correctly" part can be really hard.

Re:Interesting Book

[gbrayut]

It is actually pretty easy to create tree strucures using the CSS Display property and simple javascript commands. It also allows for more complex trees that contain text,radio or other user input. We use them all the time in PHP and ASPX, and they are a great way to create compact interfaces.

I have always thought that while web applications are harder to implement they usually pay off in the long run, since they are easier to update and (for the most part) side-step any OS compatability issues.

Of course I'm a web programmer, so my opinion might be bias :-)

Re:Interesting Book

[gbrayut]

Yeah, but the ease of updating and ablility to scale makes the initial investment worth while. When working at a large company it can be quite a hassel to update a client/application on every computer.

Re:Interesting Book

[Stinking Pig]

of course. I was just trying to answer your question, not defend the answer :-)

Re:Interesting Book

[saberworks]

Notice I said "hard to reproduce" not impossible. We have a solution that does just that, with javascript, and works on virtually all recent browsers. That doesn't solve the rest of the problems though (like a rich text editor or a combo box that can be typed in).

I'm also a web programmer and I do my best to stay away from javascript. The reason is that browsers implementations are so different that it's a huge pain to get it working correctly and similarly in all browsers. For back-end stuff (that only staffers can access), it's fine, but for the rest of our visitors, I stay as far away from javascript as I possibly can. Hell, it's abused across so many web sites that it's completely turned off on my browser!

Kexi is also worth checking out.

[anonymous coword]

It is an open source program similar to Microsoft Access. It works with MySQL too. It even runs on Windows.

Re:Kexi is also worth checking out.

[DukeyToo]

And its version 0.1 beta 2, which makes it practically vaporware in my book.

Thats a little harsh of course... I am sure it will be available eventually (the screenshots look very promising) but at this stage it is only an interesting sidenote.

Re:Kexi is also worth checking out.

[Anonymous Coward]

For `typical' users this may look like vaporware indeed, so we've added the info in the announcement:

Kexi Beta 2 is a Preview Release, mostly for developers, to show that there is active development.

--
regards & see you later on /.

Jaroslaw Staniek
Kexi Team

Re:Kexi is also worth checking out.

[Anonymous Coward]

Even in its .1 Beta 2 (The windows binaries are installed as 1.0 b2) version its not vapor but its not real usefull yet either.

If the goal is to make a Access/Pardox Like App it needs to come with designable forms and it needs to install (at least the Windows install) with Kugar for reports.

Also the Installer for WinBloat does not work right at least on 9X machines. It places half the Dlls in the wrong directory. Only a somewhat determained and advanced WindBlows user would be able to figure that out and fix it. The Helpfiles need some real work also... They aren't real helpfull.

Re:Kexi is also worth checking out.

[Anonymous Coward]

Please report me (js at iidea.pl) what is your win32 config: Is the drive C: acts as a system partition or not? Where is that wrong place the files are copied?

The installer already works in _most_ configurations; more fixing is dependent on users feedback.

--
regards,
js
Kexi Team

Greater than or equal to, I mean

[spun]

The > in the title disappeared.

Silly comment

[Rosco P. Coltrane]

MySQL: Building User Interfaces chose to include the full code listing for everything. This is both a blessing and a curse: readers have the code right in front of them and don't have to worry about being in front of a computer while reading the book

Since when do people read computer books on the throne? I would think anybody who's interested in this book (yes, you two hiding at the back!) would be reading it in front of a computer, trying out the sample code to learn what's going on.

Re:Silly comment

[Anonymous Coward]

It depends upon how you treat the book. For a professional who specializes in migrating from Windows to Linux it may be something that you read at a computer and try everything. I personally read computer books differently. I read them with the thought that they are a reference. If a concept is explained well, I can always look up the details of the implementation later.

This is especially true when the activity is merely a sideline aspect of a larger, more complex job. For example, I do not claim to be a Windows expert, but I am versed well-enough with Windows to be able to do the basics (like user management), some more advanced things that are specific to my job (like write and compile crappy little C++ hacks), and lookup rare but complex topics in books if needed (like configuring a distributed Active Directory hierarchy).

Re:Silly comment

[Anonymous Coward]

"Since when do people read computer books on the throne?

Shouldn't the question be: when do people stop reading computer books on the throne?

And the answer is: since the doctor diagnosed them with haemorrhoids.

Re:Silly comment

[saberworks]

On the contrary, I read most of my books through on the throne, in bed, on the ferry, etc., before I ever go to the computer. Especially tutorial type books. I like to read them through first and then try out the stuff later, using the book then as more of a reference.

Re:Silly comment

[kfg]

Since when do people read computer books on the throne?

Since the last time I went to the throne? I also read them in the park, while in waiting rooms of various kinds and in bed.

Sometimes it's ok just to think about the content.

KFG

Re:Silly comment

[Anonymous Coward]

Since when do people read computer books on the throne?

I grab something from my bookshelf every time I pay a visit. You think people sit in front of computers _reading_ books? Sure I reference the occasional book but you think anybody with any computing ability actually sits repeating exercises from a text book unless they are struggling with a specific problem? GTK/MySQL & glade reference manuals and sample code would be much faster for 90% of what you need create your first app.

Re:Silly comment

[dj_cel]

I do some of my best manual reading while dominating the throne, its somehow relaxing and contemplative. :)

Re:Silly comment

[glitch23]

throne + laptop + book = hours in the bathroom away from family

Re:Silly comment

[harikiri]

I read tech books all the time on the loo.

Who doesnt? /boggle

Re:Silly comment

[BoringNickName]

You know I just don't get the whole idea of reading *anything* in the john. If I need to 'make a donation' I want to get it over with as quickly as possible. Get in then get out. Dunno, I just can't get into any kind of book while I'm breathing the healthy aroma of a fresh dump.

And I sure as hell don't have computer books on my mind while I've got my drawers around my ankles.

Re:Suprised that the Review didn't mention this

[Negative Response]

From the story:

Thankfully, there is an FTP site with the code ready to use, but future versions of this book would be best served to include it on disc.

Did you read the second half of the sentence, but failed to notice the first half?

I read this book

[Anonymous Coward]

Recently I had to write a mysql demo for a tradshow. I had
never worked with mySQL before. I got this book and was
able to complete a full fledged demo in a matter of three
days, working part time. The book is very well organized,
easy to read and understand and best of all the examples
work without modifications. I highly recommend this book
for beginners. I would have done so for all levels but
I'm not an advanced user of mySQL.

If you're thinking of getting started on mySQL this is the
book.

Good book

[TechnologyX]

I have this book on my desk currently, its definitely a good resource on using MySQL for programs. The only gripe I had with it was that it didn't come with the source code, like was said in the review. I like how it focuses on converting the VB programmer over to GTK conventions.

Maybe someone should send Mike Rowe a copy after he gets out of Redmond-Training.

Yes but...

[anonymous coword]

Would you *reallY* like users installing MySql on their machines with the easiest to use options (read:insecure options) by default? GUIs are much worse. Best to stick with raw SQL and text files, that way you *have* to learn to secure the system.

Re:Yes but...

[Anonymous Coward]

God, if there was a "-1 Nazi" mod option, I'd used it by now.

Then again, now that I posted to this discussion, so I can't mod it anymore. Um, have you ever thought you can get through the can't-mod-discussion-you-participate-in just logging out to post as AC?

Re:Yes but...

[kiddygrinder]

That's crap, a well designed gui would allow a newbie to secure it more easily, your logic makes no sense. How does making something harder to configure make it easier to configure correctly? I still have no idea how to set a root password on our dev mysql database through code, if I didn't have control center, it'd still be the default

Locking Problem

[stoolpigeon]

from the sql lite page it looks like multiple users can select silmultaneously but only write to the database one at a time. Locking for writes is not record level, page level or even table level - it is the whole database.

Too bad- because MySQL really does not replace Access but neither does this- unless it will only run internally to one user.

Re:Locking Problem

[hendridm]

Umm, wasn't the book/review about creating stand-alone applications? I'd dare to say that most of them only have one user connecting to them at once anyway, right? If you have multiple users connecting, it makes sense to use the full-blown MySQL!

Re:Locking Problem

[ahdeoz]

I believe Access locks the entire database for writes too. Just like any other regular file.

Re:Locking Problem

[stoolpigeon]

I just got real interested when it was presented as a replacement for Access- because MySQL is not. If I'm going to go to run and actual database server I'll use postgres. But I'm looking for something that is just like Access but will run on linux.

So I'm not really responding so much to the book review as to the parent post. that's all.

Re:Locking Problem

[stoolpigeon]

No. You can have multiple concurrent users doing all kinds of crap and the locking granularity can be down to the record level if you write your code correctly.

Now the one thing that is bad with access is that if a user drops their connection in the middle of something it has a tendency to blow up. If you have a lot of people using an Access file over a network- you are going to need to compact & repair every so often. But I've seen stuff go strong for a while with 30-70 users hitting the same .mdb for 10 hours a day and it worked pretty well.

Re:Locking Problem

[Anonymous Coward]

Access isn't really designed for multiple users either. Sure, it will work, but can easy be overworked.

Re:Locking Problem

[LuxFX]

from the sql lite page it looks like multiple users can select silmultaneously but only write to the database one at a time.

I don't see this as a problem, because at least I see SQLite as more of a portable pre-packaged data structure, rather than a traditional multi-user database. I haven't had a chance to use it yet myself, but I look forward to using it in stand-along applications where I need good data structure and the ability to filter and search for specific data. I'm a fair hand with MySQL, so having all of this wrapped up into a SQL-compatible package is exactly what I need.

It means I get to concentrate on what I find more exciting about programming -- rather then spend a load of my time on file structure and data processing.

Re:Locking Problem

[Anonymous Coward]

Not true -- PC systems like Windows, Novell, and Linux are designed to support record-level file locking specifically for multi-user suppport for file databases like Access.

Depends on perspective

[Moth7]

But do we really want the book to only scratch the surface in many languages rather than give it thoroughly indepth with a single language? I often find it frustrating when chapter upon chapter is dedicated to a language I'll never use. If however you want lots of other languages covered, the MySQL Cookbook by O'Reilly has good scenario based discussions of using MySQL with PHP, JSP (as you wanted) as well as Python and Perl.

Java + JDBC + GUI builder?

[Kunta Kinte]

I'm sure the GTK and Glade solution works fine. But wouldn't Java, JDBC, plus a GUI builder ( even a freely downloadable Borland JBuilder Personal or now Eclipse.org has a GUI Builder ) be a lot more crossplatform and mature solution?

There are also quite a few over the over-the-shelf Java JDBC components and tools that you can buy as well in case you're building a commercial application.

To each his own I guess.

The PostGres Fanboys/Trolls should read this too.

[anonymous coword]

Look at your own gotchas before laughing at others!

Re:windows developers

[Anonymous Coward]

Plleeeaassseee... BBeee moooreee ssseennnsssiiiittttiiivveee.....

Iii aaaammm aaaa hhaannnddiicaapppedd mmaaannn.

Where's the delphi love?

[Anonymous Coward]

Right out of the box you can connect to mysql, nothing else required.

Re:windows developers

[BinaryCodedDecimal]

if you are a windows programmer then you must be retarded

I know many retarded people that would be offended by that statement.

Seriously though, making sweeping statements like that just makes it obvious that you were never good enough for the high school debating team...

A possible alternative - Delphi?

[dpeters]

As a self-employed database developer (for 10 years) I've been using Borland's Delphi to talk to MySQL.

Although Delphi is not open source, I prefer it to the typical MS Access / VB answer for Windows platforms, and Delphi has a very mature IDE (developer interface) which is most important when building lots of applications.

Re:A possible alternative - Delphi?

[Anonymous Coward]

You forgot to mention that you have to know Pascal!!!!

Re:A possible alternative - Delphi?

[Anonymous Coward]

There are quite a few databases that are tailored to Delphi. One I know of (www.advantagedatabase.com) has free local (or non client-server) access and then if you want a database server configuration, you can purchase one later ($2000ish for 100 users)

Re:A possible alternative - Delphi?

[name773]

Delphi has a very mature IDE (developer interface)

you mean a text editor? Linux has a lot of mature "IDE's" like vi or emacs or joe

Re:A possible alternative - Delphi?

[cruachan]

Borland's implementation of Pascal in Delphi is so close to C that it should only take a competant C coder about a week to be up and running to a good standard.

Actually calling Borland's implementation of Pascal in Delphi Pascal is a bit of a streatch IMHO. The language is now so far removed from classic pascal that apart from the bare bones there's not a lot in common.

Re:A possible alternative - Delphi?

[certsoft]

You forgot to mention that you have to know Pascal!!!!

Lot's of people know Pascal, especially Europeans, where it is far more popular than in the states.

Re:A possible alternative - Delphi?

[TimTheFoolMan]

Hear, hear!

Shy of Multiple Inheritance (which can be a virtue or vice, depending on your perspective), there isn't much that Delphi lacks in comparison to C++. However, it leaps past C++ in terms of production cycles centered around Test Driven Development or XP, and it gives up nothing to VB in any category except marketing muscle and unwashed hordes of users.

Yes, Delphi is Pascal, but it is so much more than that.

Tim

Re:A possible alternative - Delphi?

[fiftyfly]

Plus a component system that makes activeX objects look, well, let's just say I can't think of an apropriate adjective for polite company. Object pascal is very clean OO/procedural language that remains extreamly self consistant and is small enough to learn quickly. If I were building windows GUIs or DB frontends or pretty much anything focused largely on an event handler model my first choice would be delphi.

Re:A possible alternative - Delphi?

[gringo_l_amigo]

As a guy who uses Delphi for 2 years, and who recently had to transfer a database from access to mySQL, I would like to know, which component do you use to connect to MySQL? I use Zeos which is open and going pretty well, but maybe there is a better (faster) alternative.

A bit easier than programming GTK directly...

[tcopeland]

...might be to use a higher level language wrapper like Ruby/GTK.

Prototyping an app will probably take less time if you don't have the compile/link cycle... worth a try, anyhow.

Re:A bit easier than programming GTK directly...

[Anonymous Coward]

Prototyping an app will probably take less time if you don't have the compile/link cycle... worth a try, anyhow.

Actually, it's even easier if you just use fucking libglade. That way, the .glade file is kept as a separate xml UI definition file, and you don't need to compile the whole program when you need to change some widget properties.

Code generation wigh Glade is stupid. libglade + glade is the way to go, and it's the preferred method used in gnome development. So please, avoid glade code generation like plague (code generation won't be available in glade 3 anyway).

Access 2k? SQL Server 97?

[lawpoop]

Couldn't the author have made a little more recent comparison to database technologies? AFAIK, SQL server is at version 2k, and Access at version 2k3.

Re:Access 2k? SQL Server 97?

[geoffspear]

That would be easier if the reviewer wasn't reviewing a book that was published 3 years ago.

Re:Access 2k? SQL Server 97?

[pballsim]

Access and SQL are completely different as databases are concerned. Access uses the Microsoft Jet database (free), while MS-SQL is a very powerful database tool. MS-SQL blows away MySQL in terms of speed, design, transactional logs, backup/restore, XML support, etc. Of course MS-SQL is $5000.

I've worked on MySQL, Oracle, Access, MS-SQL and some other databases and I prefer MS-SQL.

Re:Access 2k? SQL Server 97?

[iffycompe]

Not only that, but there is no MS-SQL Server 97. There's 6.5, 7.0, and 2000. I'll just assume whoever first wrote it meant 7.0. There's also MSDE (Sql Server Desktop Edition) which is free. Sure it doesn't support some of the enterprise features of it's bigger brothers, but all the stuff you would expect from vanilla MS-SQL Server is there, only in connection limited form.

Re:Access 2k? SQL Server 97?

[Anonymous Coward]

Actually there is no such thing as SQL Server 97(the original writer must be on crack), and the MSDE is free. MSDE is SQL Server throttled down to five connections and limited to 2GB databases. mySQL is a very nice personal/workgroup solution for databases. very fast.

Re:windows developers

[Carl T]

Don't confuse stupidity with lack of political engagement / ethics / knowledge / possibilities. I know quite a few people who are more or less stuck with Windows for various reasons (the main one being their employers' decision to - willingly or not - support Microsoft), but who would like to learn more and be able to break free. If a book like this can help even a few people gain some of the skills they need to migrate, it's a good thing.

Why does OSS love MySQL?

[t_pet422]

I don't get why so many people are stuck on MySQL. It's lacks some very, very basic features: Views, Triggers, Stored procedures, nested selects. Sure, they promise support for all this in future version, but PostgreSQL supports it NOW. If you want a full-featured open source database, don't use MySQL, use Postgres.

Re:Why does OSS love MySQL?

[SomeOtherGuy]

MySQL to Postgres is akin to the whole MP3 to OGG OR VHS to Beta.

Sometimes technically better does not win out over market hold and saturation. One could (I am sure) publish a whole book on how MySQL got entrenched over a more feature laden database.

Re:Why does OSS love MySQL?

[Tassach]

I too wonder about the inexplicable popularity of MySQL. It fills the microscopic niche between a lightweight ISAM database and a real full-featured relational database. I've yet to see MySQL used for something that couldn't have be done better with another tool.

Postgres is pretty good, but still (IMHO) not yet up to the level of a commercial RDBMS. If you want a full-featured free-as-in-beer datacenter grade database, use Sybase 11.0.3.3. It doesn't come with source, but come on now, how many MySQL zealots have ever even compiled from source, let alone modified something?

Re:Why does OSS love MySQL?

[w00t_sargasso]

Err... I have compiled from source.

Most people I know compile from source...

Ah well, to each their own.

Re:Why does OSS love MySQL?

[JanneM]

Acutally, I have yet to need a "full-featured database". From what I've seen of various apps, neither do most other people. By and large, most apps needing a form of database storage usually get by with DBM or equivalent functionality. 'Real', big database applications that need the kind of stuff you mention are few and far between - and in those cases, there are plenty of alternatives anyhow, like you say.

For me, MySQL is not a deficient implementation of the Likes of Oracle, but a full-featured, modern way of accomplishing what DBM did.

Re:Why does OSS love MySQL?

[MCZapf]

I think it's because many people who use MySQL use it for simple data storage. They are quite thrilled that they don't have to write code to read and parse a bunch of text or data files; they just do a SELECT query from MySQL. They haven't yet moved on to the more advanced aspects of these kinds of databases.

Re:Why does OSS love MySQL?

[Anonymous Coward]

I'm not a MySQL zealot if such a thing exists, but I compile from source link against a hacked glibc on most our production systems. You want to know why MySQL is so popular? Simplicity and speed of basic SELECT, pretty much all you need for 80% of a website or intranet app or it could just be that cool dolphin logo :-o

I use Berkeley db for speed in more simple applications so your "other tool" observation appears to be bullshit.

Re:Why does OSS love MySQL?

[t_pet422]

So you're saying people who don't know any better use MySQL? I recently implemented a very small blog app. The query on my search page uses four nested SELECTs containing an INNER JOIN, an OUTER JOIN and a UNION. To me (someone who writes database driven apps for a living), that is a fairly basic query. Yes, I could have done it with temp tables in MySQL, but it wouldn't have been nearly as clean as my Postgres implementation. It would really stink to be a newbie, pick MySQL, then, as you learn more about the power of SQL, realize that your choice of database is limiting you.

Re:Why does OSS love MySQL?

[Khomar]

At least for my company, the primary reason for using MySQL is the native support for Windows. These other solutions are only currently supported in Linux (Postgres supports Windows only through Cwgwin).

Re:Why does OSS love MySQL?

[Zaiff Urgulbunger]

Same here. I think MySQL is easier to install on Windows, and I think thats it! But at this point, MySQL has so much momentum, its difficult to see anything else getting a look in.

Its a shame though -- not knocking MySQL, but isn't a replacement for anything even vaguely serious (e.g. MS SQL Server), but unfortunatly there are a lot of users who make noise about how great it is... which makes them look kind of like they don't know what they're talking about. Sigh!

Re:Why does OSS love MySQL?

[skiflyer]

I second this... though Postgres has a team working on it, however slowly.

MySQL wins again and again for 3 reasons...

• It's good enough
• It's cross platform
• It works right out of the box

Re:Why does OSS love MySQL?

[vandan]

MySQL is very fast and very easy to use.
The support community is also very large and helpful.

I've found Postgres to be very ... strange. It has too many idiosyncrasies ( yeah I can't spell it, I know ) and requires too much pissing around to get anything useful done. It also has some MAJOR bugs and missing features. For example, last time I checked, it couldn't change field types or add / delete fields once a table is created. That's backward.

Postgres also isn't that fast. Nowhere near as fast as MySQL. Having triggers and stored procedures is good, but if it means taking a 50% performance hit, I'd rather stick with MySQL, especially since version 5.0.x is out which supports stored procedures now. By the way, MySQL-4.1.x supports nested selects too.

The support community for Postgres also isn't as big as MySQL's. MySQL is just more popular. If you don't agree with the majority of OSS users on this one, then fine, use Postgres. I've used both and I'm not touching Postgres unless I have to. MySQL is just too good!

Re:Why does OSS love MySQL?

[timmarhy]

err yes thats why yahoo use it for their accounting system. there are other big ones i can't think of off the top of my head. speed is where mysql wins. it's plenty good enough for 99% of database needs, and it's fast & stable. postgrsql might be very feature rich, but from what i've heard it's not the fastest tool in the shed so to speak.

Re:Why does OSS love MySQL?

[Space_Soldier]

MySQL uses cygwin.dll too.

Re:Why does OSS love MySQL?

[tree_frog]

Absolutely. At my work I do a lot of heavy duty simulation. Basically a whole bunch of writes to a database for a couple of days, then some queries. For the extra overhead of writing to a dtabase rather than flat files, we gain so much flexibility in looking at our data.

Why MySQL? Because it is fast, and in our game, that's what matters.

regards,

treefrog

Re:Why does OSS love MySQL?

[Zaiff Urgulbunger]

err yes thats why yahoo use it for their accounting system. there are other big ones i can't think of off the top of my head.

LOL! Yeah, I know that people use it and I didn't intend to imply that it wasn't useful. The thing about MySQL is that it gives up a lot of useful database features in the name of speed. Thats fine, but it makes it a niche product -- in the sense that it is specialised. So its good for areas where reads vastly out number writes.

My point (and I think I worded it badly) was meant to be that its suprising and unfortunate that MySQL is as popular as it is. Why? Because as I say, MySQL is specialised for speed. Whilst I accept PostGre, etc are slower, how much does that affect 99.9% of MySQL users? Many of these people would've otherwise used Access -- and that ain't fast! But Access is fine for many peoples use (e.g. a cheap database usable by a few concurrent users with reasonable data storage).

And going back to the "missing features", I know you can add a bunch of stuff to MySQL to make it more like other DB's, but it would be better if these features existed by default and you had the option to "optomise" for speed by disbling them [IMHO anyway!].

Re:Why does OSS love MySQL?

[Anonymous Coward]

The one argument i came back to for postgresql over mysql is reliability. Pure and simple reliablity. I use both mysql and postgresql. 80% of my database, and all the bigger ones run on postgres. I have never had data courption of a postgresql database ever. Even on computeres with dodgy harddrives and mulitple cold power downs. But i have had myql databases frag themselves and tables vanish on a regular basis. Sure the features on postgresql are nice to but thats not why i use postgresql. I use postgresql because i can trust my data on a postgresql database and i know it will still be there. I can't say the same to mysql. I dont know what makes mysql so unstable but it doesn't sit well with me knowing that my mysql databases could die at any moment.

Re:Why does OSS love MySQL?

[Tassach]

I'd disagree with on the key point: it's not good enough: it's either too good or not good enough.

Everything that can be done with MySQL can be done better using something else. If you need a real multiuser ACID-compliant relational database, MySQL doesn't cut it. If you need a lightweight high-performance data store, MySQL is overkill. All MySQL is good for is teaching people bad habits.

One minor correction

[The Bungi]

There's no such thing as "SQL Server 97". There's SQL Server 6.5, 7.0 and 2000. Perhaps he's referring to 7.0? Well perhaps he should have referred to the newest version instead.

Not that I'd be interested in a comparison between MySQL and SQL Server (midrange C/S replacement for Access/FoxPro - OK. Enterprise RDBMS? No)... but still.

Um... there are 2 gotchas there...

[Anonymous Coward]

MySQL is listed with OVER 20.

It's not about being a postgres fanboy/troll.. reality is that MySQL DOES NOT have features comparable to Oracle/PostgreSQL/SQL Server. When are you going to understand this??

As a DBD (db developer) I CAN'T use MySQL - it's feature set is too WEAK. MySQL is starting to get some of these features now and good for them but the argument was always about the feature set.. fuck some of you are dimwits...

And yes I've just fed a troll but fuck it, now im all pissed off and want another coffee..

Move?

[ClosedSource]

"If you are a Windows programmer looking to create or move your stand-alone database applications away from Microsoft-specific tools such as Visual Basic, Visual C++, Access or SQL Server, MySQL: Building User Interfaces is written just for you."

Very few Windows applications are written in 'C' these days so converting an existing application to use MySQL would be pretty painful. You should have a better reason to port your code then simply avoiding MS tools.

Perhaps the book is better suited for developers who wish to switch to Linux for future development.

Re:Move?

[Anonymous Coward]

You should have a better reason to port your code then simply avoiding MS tools.

Since buiding a simple database app in GTK and C will take about 100x longer than doing it in VB, the reason is obviously Job Security.

Re:The PostGres Fanboys/Trolls should read this to

[Anonymous Coward]

Yes, and then compare the sheer volume of MySQL gotchas to Postgres gotchas.

Both are replacements for MS SQL Server

[ttfkam]

...if you don't use transactions, stored procedures, triggers, views, sequences (read: real ones), or if you actually want your database to be typesafe and have your insert/update to throw an error (or at least a warning!) on invalid data. Otherwise MySQL is most certainly not a viable replacement, although the codebase which used to be called SAPDB is. Firebird also qualifies for a replacement.

Also, if you need advanced XML querying tools for your database, none of the open source RDBMSs can currently act as a reasonable drop-in replacement for MS SQL Server.

If you don't need any of those features (and especially haven't actually used any of them in existing code), then MySQL could be added to the list of open source RDBMSs that fit the requirements for your project.

Re:Both are replacements for MS SQL Server

[EastCoastSurfer]

Also, if you need advanced XML querying tools for your database, none of the open source RDBMSs can currently act as a reasonable drop-in replacement for MS SQL Server.

I have to agree here. I was using MSSQL the other day and was able to open an XML document inside a stored proc and query it like it was a table. All I could say was "Damn, that's pretty cool!"

I had been using the FOR XML EXPLICIT quite a bit for output, but didn't (until recently) need to read XML back into the database.

Re:Both are replacements for MS SQL Server

[Valar]

MySQL has transactions. It has for QUITE awhile. In fact, I write code that uses MySQL transactions everyday. Stored procedures are availible (will be availible?) in version 5.

Re:Both are replacements for MS SQL Server

[damiena]

Firebird? It can do databases, too?

That browser can do everything!

Re:Both are replacements for MS SQL Server

[porter235]

check Berkeley DB XML http://www.sleepycat.com/products/xml.shtml if you want to do real XML work.

Re:Both are replacements for MS SQL Server

[skiflyer]

PostgreSQL was one of the two, and it has stored procedures, triggers, and views. I believe it has it's own version of sequences, just as MySQL does. (Which I personally prefer to MS SQL's)

Re:Both are replacements for MS SQL Server

[ttfkam]

For varying definitions of "quite a while." Other than that I would agree with you except for the fact that every table involved in the transaction must be an InnoDB table. Have a half-and-half setup? When that transaction rolls back, the InnoDB tables will remove the changes, but the MyISAM tables will still be changed. ...not that MySQL will tell you this. No. Once again, it will do it all silently without even a warning to let you know that you need to convert one more of your MyISAM tables to InnoDB.

Which doesn't even cover the fact that if you are being hosted by an ISP, using their copy of MySQL, and you type in:

CREATE TABLE foo (
...
) type=innodb;

and they are either (a) running an older version of MySQL or (b) disabling support for InnoDB tables because of their noticeably higher overhead than MyISAM (yes, both cases happen quite regularly), you will be none the wiser.

MySQL will not issue a warning, error, or valentine's day card for your troubles. So here you are, happily SQLing away without a care because you have InnoDB tables until you notice some strange data...because...you aren't running InnoDB tables after all.

Be honest, how often do you run the following?

SHOW CREATE TABLE foo;

Everytime you create a table? No?

How about the next statement? Here we attempt to give some users on Slashdot 10% more of a clue.

UPDATE slashdot_users SET (clue = (clue * 1.1)) WHERE preferred_db = 'MySQL';

and (oops!) something went wrong halfway through for some reason. Maybe someone killed the process. Maybe someone kicked a cord. Maybe you ran out of memory on the box and the process crashed. The world is an imperfect place. Back to work though: how many Slashdot users had their clues upped? If they were updating a MyISAM table that "didn't need transactions," we have no idea.

Transactions aren't just BEGIN and END statements. They aren't just commit() and rollback() functions. They are intrinsic to a valid datastore. They are the Atomicity in ACID. That UPDATE statement was a transaction in all contexts used to describe databases.

MySQL and transactions are like assembly language and for-loops. They can be made to go together, but they were not originally designed for such a task. In assembly, it's a set of well-placed jump (goto) statements. In MySQL, it's a set of table=innodb flags. Some people do it the hard way for no good reason even when easier, less error prone methods exist. For the rest of us, we use a higher-level language or -- in this case -- a higher-level database. If and only if we see a need to optimize do we drop down to assembly (MySQL). You don't start with assembly and then try to find rationales for a higher-level language.

Re:Both are replacements for MS SQL Server

[dodobh]

How hard woiuld it be to do this in PostgreSQL with pl/perlu and the Perl XML modules?

Re:Both are replacements for MS SQL Server

[dodobh]

Ack! I need to preview before I post.

The word is "would". (and perlu stands for perl untrusted).

Re:Both are replacements for MS SQL Server

[EastCoastSurfer]

No idea as I don't have much experience with PGSQL and perl. Check the MSSQL books online under OPENXML to see how simple it is though. I tried to post a snippet, but /. is telling me to use fewer 'junk' characters.

I am a Windows programmer

[Anonymous Coward]

and I contemplated about my tardiness all the way to the bank

Access Convert

[w00t_sargasso]

I once used access. Even tried to make a VB front end. Couldnt get the damn thing to work. So I stopped using dbs for a time. Then someone showed me the pure beauty of MySQL and PHP (Thanks Andrew!!!!!!!!!) and since, I have never looked back. I tried Postgre, and didnt like it. Feature overkill...

Meh.
"In technology, there will always be 2 people with 200 opinions"

Hey, I just made a quote!

Who mods this shit up? Check poster's history

[Anonymous Coward]

The parent is a content-free comment

Re:The PostGres Fanboys/Trolls should read this to

[gmhowell]

Good point: mysql has 25 listed gotchas, whereas PostGreSQL has 2.

(FWIW, I'm using mysql, but I suck.)

Of course...

[iSwitched]

...You are absolutely correct. As a matter of fact, I am currently engaged in doing exactly that.

JDBC is an incredibly complete data access solution that is almost like using DAO or ADO or whatever Access guys use these days. Swing, as done in Java 1.4 is plenty quick for DB GUI's and JBuilder Foundation's GUI Builder is about what an access of VB form developer would expect. I'm using all of these with no complaints and my apps run without change or recompile on Windows, Linux, and OS X.

In fact, in the time it'll take most to read this book and adapt the first tentative hello world project, I will have completed another third of my real project.

But, since this is Slashdot: "Ooh how I hate Java!". (secretly returns to work making $$$ doing Java apps)

Rekall

[bstadil]

Rekall is another Access like Frontend that works with MySQL and a few others.

They got shafted by TheKompany and need some help so download it a send a few bucks their way if you like it. Runs on Linux and Windows.

Re:Rekall

[Tracy Reed]

I have tried like heck to get Rekall working to no avail. With much effort I have twice gotten it to completely compile (once on debian and once on a RedHat system) and both times it comes up with an empty palatte and a Help menu pulldown at the top and nothing else. I'm not sure what went wrong but I would sure love to get this thing working because I could really use it. If anyone has any tips please email me or reply here.

Access can be a front end to MySQL

[HermanZA]

MS Access can be usedeither as a standalone DB app or as a front end to a larger SQL DB. Obviously the advantage of a SQL DB is its ability to serve multiple users at the same time, which Access on its own, can't do. So, use the right tool for the right problem...

Re:Access can be a front end to MySQL

[son_of_asdf]

That's not entirely true: you can use the Database Splitter in Access to dump your backend to a seperate file, which can be stored on a server that multiple front-end clients connect to. This isn't a viable way of doing things for more than 10-20 odd users at once, but for a small office situation where you can (more or less) trust your users, it works just fine.

Re:Access can be a front end to MySQL

[YrWrstNtmr]

No. A single Access mdb can support mutliple users. More than about 10 or so concurrent users, though, and it starts to bog down.

So, deploy multiple front ends, all talking to the central back end DB.

I suppose this is flamebait or a troll but...

[kahei]

...wouldn't the effort be better spent in actually bringing mySQL up to the point where it *can* replace SQL Server?

MySQL seems to occupy a rather subtle and narrow niche, perhaps the 'single smallish website' niche. I can't really imagine wanting to use it when most applications are liable to grow beyond that niche.

PostgresSQL looks a lot more encouraging, feature-wise, although it doesn't seem to offer many concrete benefits over SQL Server. Still, to me, a book on migrating to PostgresSQL or another full-featured RDB would be more useful.

Re:I suppose this is flamebait or a troll but...

[xutopia]

you do have a good point, however have you ever tried to install PG on Windows? It's a bloody nightmare!

Re:I suppose this is flamebait or a troll but...

[puppetman]

What are you talking about?

MySQL and Postgres are both very good database technologies (and I'm an Oracle DBA, managing 3 high-availability Oracle installations).

"MySQL seems to occupy a rather subtle and narrow niche"

The website that you posted this comment to, that offered the review of this book, runs MySQL. You know - the website with it's own effect named after it, the one that handles the load of tens of thousands of geeks while the site they reference in a front-page-story crashes and burns when all /.'s readers go visit it.

Oh yah, Yahoo runs on MySQL. I wouldn't say Yahoo is a small niche; it's a large one, all by itself.
With the InnoDB database engine, you have the same row-locking, ACID trasactions that you get with the big databases. Both Slashdot and Yahoo use the InnoDB database engine in MySQL.

You don't have stored procedures, triggers, or views, but as an Oracle DBA who has 12,000 lines of proprietary Oracle-PL/SQL on just one of my three databases, I can say that they cause more problems than they are worth. A nightmare to write, debug, and maintain.

But never the less, views, stored procedures, and all those other features are due out in 4.1 and 5.0.

As for SQL Server, I do know it runs only on Windows (at least Oracle supports Linux, Solaris and Windows as first-tier platforms). MySQL runs well on all the *NIXs, and has had AMD-64 support for several months now via several production-ready versions of 64-bit-Linux. Microsoft has just managed to shlub out some half-assed public beta that won't be ready for general public consumption for 4-8 more months.

Re:I suppose this is flamebait or a troll but...

[bucknuggets]

> You don't have stored procedures, triggers, or views, but as an Oracle DBA who has 12,000 lines
> of proprietary Oracle-PL/SQL on just one of my three databases, I can say that they cause more
> problems than they are worth. A nightmare to write, debug, and maintain.

Sure stored procedures can get complicated, then again so can anything if you're fool. Most that I see written today are extremely simple. But anyone who's having nightmares from the complexity of views needs to step away from the keyboard slowly.

> But never the less, views, stored procedures, and all those other features are due out in 4.1 and 5.0.

Yep, and the core functionality *almost* works reliably. Really, views, etc are essential in a database - but they need to first clean up the silent errors in their core functionality.

Don't take my word for us - read their manual (especially the "design issues" section), then apply all that invaluable Oracle dba experience to a few tests. You might be amazed at how often mysql corrupts your data. You just never realized it before because every time it hits something difficult, complex, confusing, etc it simply takes some weird default action. Isn't quite so bad for read-only applications, but anyone who writes valuable data to mysql without really understanding the product is a fool.

Oh yeah, and the best part - it isn't free either.

Re:I suppose this is flamebait or a troll but...

[puppetman]

"Sure stored procedures can get complicated, then again so can anything if you're fool. Most that I see written today are extremely simple. But anyone who's having nightmares from the complexity of views needs to step away from the keyboard slowly."

We inherited our PL/SQL codebase from a consulting company that wrote it. They take Java objects, pass each member in as a parameter, and then almost each and every method passes back a generic-IO cursor. The dumbest thing I ever saw.

Stored procedures are hidden logic. They are difficult to test. They are difficult to debug. If you change a table that a package relies on, you can invalidate the package, and not find out till exceptions start showing up in your logs.

"Don't take my word for us - read their manual (especially the "design issues" section), then apply all that invaluable Oracle dba experience to a few tests. You might be amazed at how often mysql corrupts your data."

Are you talking about "7.1.1 MySQL Design Limitations/Tradeoffs" or "7.4.1 Design Choices"? Both sections are MyISAM-specific. InnoDB is the only true database engine in the MySQL product. I've been subscribing to the MySQL mailing lists for about 18 months now, and the ten to twenty data-corruption issues I've seen have been with MyISAM (often running 3.23), when the server loses power with MySQL running. InnoDB has binary logs, redo capabilities, and transactions. There have been a few bugs that could potentially cause data-issues under rare circumstances with InnoDB, but we've had issues with Oracle as well (corrupt or invalid indexes, or rows that were no longer accessible).

"You just never realized it before because every time it hits something difficult, complex, confusing, etc it simply takes some weird default action. Isn't quite so bad for read-only applications, but anyone who writes valuable data to mysql without really understanding the product is a fool."

I've loaded one of our databases (about 4 gig) into MySQL (it was about a 2 hour effort to conver the data, and 2 hours to bulk-load it, including the 100 or so indexes and foreign keys). I've run some of the tougher queries that we use against it (10 table joines, 5 of them outer-joins, returning 2 million rows) and it worked fine. I've also loaded the key data from one of our other databases into MySQL, and run our nastiest queries against it, in a threaded environment, where each thread does multiple selects, updates and inserts very large rows with LOBS and the CPU stays pegged at 100% for 20 minutes. It finished in half the time of Oracle, and not a single hiccup.

I've also loaded 40 meg of data via insert statements, including LOB fields, large numbers, and dates. No problems at all.

Re:I suppose this is flamebait or a troll but...

[Unordained]

Tried Firebird at all? Feature set is pretty much like Postgres (minor variations in emphasis,) easy to install (both under windows and linux, particularly with the RPM, even under slackware) and it handles our load beautifully.

I've got a few C++ programmers, writing a rather fat client (400 distinct screens, hundred or so reports beyond that) using Firebird on the server. Handles our forty or so users just fine, and unlike some other database servers (MySQL), it keeps our data safe.

Also, as it is relevant to this story, Firebird has an embedded version. Same capabilities as the full server (SQL and all,) except that it's in a DLL you bundle with your application, a sort of single-user mode that doesn't require a service running in the background. That's a big advantage. You don't have to change any code for your app to run on the single-user version (but if you start there without planning for multi-user access, you might be in trouble scaling up!)

Oh, and using Firebird doesn't require you to make your app open-source or free, ever. Even using the embedded server, shipping pretty much all of Firebird with your app, it's still free to use.

It's based on Borland's Interbase, the fork occured at Interbase 6-open-source, and has been moving forward steadily. We're all waiting for Firebird 1.5 final (it's in late-stage release candidates, but they're very careful with it) for improved performance and features. But I've been using 1.0 in production environment for what feels like forever, and it's quite stable itself, and quite powerful. Check it out!

Or... Revolution

[Anonymous Coward]

Check it out: www.runrev.com.
Windows, Linux, MacOS 9/X.
Looks much nicer than GTK or Java.
Much easier to script.
MySQL, Postgres, OpenBase, Oracle, ODBC, etc.
Also works with Valentina- a pretty darn fast Mac & Windows embedded database engine.

It's not for everything, but if you want cross-platform RAD, it should be in your bag of tricks.

Re:Or... Revolution

[Anonymous Coward]

Looks like Hypercard

Paradox

[Pop69]

Anybody know if it's still on the go ? I remember thinking how great the windows versions were for programming compared to the early versions of access which were more likely to crash and corrupt your data than actually do anything useful.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Use Access as a GUI for mySQL!

[Mr. Cancelled]

For a content management system I've developed, I prototyped all of the basic functionality by using Access as a GUI for mySQL. And I loved it! I actually considered doing the whole thing this way for a bit as it offered so many of Access's strengths (quick and powerful GUI building, familiar environment, everyone has it, etc.), but was combined with mySQL's stability, scalability (Well... For the needs of my target audience at least), and versatility. All's you need to tinker with this is the free MyODBC package, and suddenly your mySQL database is seen as a standard ODBC one. You can build table relationships within Access using mySQL based source data. You can locate the mySQL server anywhere you want (local or remote), point Access at it, and there ya go. Additionally you can now use VBA within Access to take advantage of many Active-X components, as well as a wealth of freely available VB code. There were several reasons I went back to my original plan of just prototyping in Access, and deploying via a web-based setup:

1. Obviously not everyone has Access. Many do, or have access to it via work or school (access to Access?!?)
2. There were certain things that were easier to implement in an HTML-based setting (conversely, there's things I still can't equal that were accomplished easily using VB & Active-X. Things such as a customized file browser with a built in image viewer)
3. While some of my VB code and Active-X'ing would have been transferable to the web, it would have required requiring that the user of my system use IE to administer it. Not gonna happen!
4. I just don't like Microsoft enough to make my product only available to users of their software. The above IE example would have required my admins to IE; Access would require users to have Access; Having Access implies that the user must also run Windows (Wine just isn't there for Access usage I'm afraid. At least not for very advanced usage)

But for other projects I have, who's target audience is Windows users, Access is a great way to quickly build some impressive mySQL-based applications. IMHO, of course. 8)=

Open-Source Solution for Java/MySQL

[Yoda2]

SwingSet is an open source toolkit for writing database-aware/enabled Swing applications.

Almost right

[ttfkam]

So there still isn't a production-quality MySQL server.

SQL is outdated

[Tablizer]

SQL is a crappy relational language. The industry should focus on finding a replacement rather then be stuck with the Cobol of relational languages.

Re:SQL is outdated

[Anonymous Coward]

Sounds GREAT! why don't you write it. SQL has survived so long because it works. and it works well. Maybe you should try learning more than SELECT or a couple of DDL statements.

Re:SQL is outdated

[timmarhy]

and just how does it suck? the reason it's stayed popular for so long is the basic's are so easy and logical. i mean i couldn't possibly think of an easier way to extract data

Re:SQL is outdated

[Tablizer]

and just how does it suck? the reason it's stayed popular for so long is the basic's are so easy and logical.

I think SQL stinks.

I would personally like to see a more functional (FP) style that allows one to optionally name sections of a query rather than have one huge run-on sentence like SQL often becomes.

BS-12 was an early example. And then there is Date's Tutorial-D language which appears more rational than SQL.

Re:SQL is outdated

[Jeddawg]

... i couldn't possibly think of an easier way to extract data ...

Hmmm, how about opening the table directly (*gasp*) and applying a filter? Or setting an index order to active to sort the data? Or setting a scope to filter out data that's outside of the range?

Yes, there are specific needs for which SQL presents itself as a solution; but not everything can be solved via SQL.

Re:SQL is outdated

[timmarhy]

i never stated sql was a solution to EVERYTHING. and the examples you gave at NOT easier then SQL in my opinion. same shit different bucket

Re:SQL is outdated

[Tablizer]

Hmmm, how about opening the table directly (*gasp*) and applying a filter?

A view can do that, although currently it is something that the DBA has to do. User-defined views would be nice, but that is orthogonal to query language it seems.

Re:SQL is outdated

[Jeddawg]

User-defined views would be nice, but that is orthogonal to query language it seems.

I don't believe that is completely orthogonal to query languages. I think the navigational-level access is very complimentary to a query language interface.

It comes down to a matter of using the right tool for the job. Some jobs are just easier with SQL; others are just easier with direct access to the table. (And, some jobs are best accomplished via an SQL query to generate the results, then the ability to apply a filter/index to that query result.

Granted, I realize MS SQL doesn't support this; but other engines (like Advantage Database Server) can support both access methods while still maintaining security and data integrity.

Not quite

[ttfkam]

In most modern operating systems, inactive processes can be swapped out in favor of disk buffers and the like as well. Besides that, the memory used by an inactive MySQL process is closer to "rounding error" than "waste of memory."

But if you're still worried about it, an extra 256MB is hovering around $30 on Pricewatch.com. Hmmm... $30 and never having to worry about it or any other inactive daemons on my system again or...

I don't know about you, but my time is worth more than that.

Re:Not quite

[man_of_mr_e]

It's not an issue with MySQL in particular. It adds up when you have lots of services you're not using. I don't care if I've got 10TB of memory in my system, it's still wasted if i'm not using it.

Besides, having services you don't use in your startup takes longer to boot, and for most people, booting happens every day as they shut off their computers when they're not using them.

Remember, we're not talking about a server in this thread.

postgresql syntax conventions? LOL

[kpharmer]

ok, so postgresql has two issues with syntax, and mysql has dozens of situations in which it will silently fail.

And that's comparable?

If you want to read something amazing, go ahead and read the MySQL manuals, look for design deficiencies: http://www.mysql.com/documentation/mysql/bychapter /manual_Introduction.html#Bugs

You'll see more than just minor syntax issues - you'll see that mysql spends most of its time in silent error mode.

Re:test

[Zaiff Urgulbunger]

Good point. All new users must be encouraged to test all their code before they deploy it!

But isn't mysql a commercial database?

[bucknuggets]

I've seen the licensing fees - and if I remember right, they charge by concurrent user. So, if you want to support 1000 concurrent users @ $500/user on a mysql-based web portal, wouldn't that be something like $500,000? Sounds commercial to me anyhow.

Re:But isn't mysql a commercial database?

[Anonymous Coward]

"The MySQL Server license is per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one database server."

from http://www.mysql.com/products/pricing.html

Where did you get $500 per user!!???

Re:But isn't mysql a commercial database?

[lostchicken]

MySQL is GPL. You can use it just as you would Linux, or any other GPLed application. It's free. You can use it on a commercial site all you want.

You can even modify it and distribute it in your own application. However, you've got to play by the rules of the GPL (you've got to release your code). If you don't want to play by the rules, you can buy a license, and pay to distribute whatever you want, without ever releasing code. It's really a great system.

Re:But isn't mysql a commercial database?

[jred]

I think this is an aspect of GPL (& other OSS?) that should be emphasized more often. Especially given some companies' reluctance to use it, in fear that they'll be required to release *their* source, too. If they're worried, they can just buy a license that specifies that they have no legal obligation to do that.

I'm all for OSS, but the fact is that most companies aren't going to open the source for products they are actively selling. They are still contributing to OSS, it's just a monetary contribution instead of a codetary :) contrib.

(After typing all this up I realized I'm only assuming OSS stands for open source software. If it doesn't, just pretend it does)

and they mostly work

[bucknuggets]

you just need to know where to be careful, since when they fail they won't reliably tell you the way most other databases will. What they'll do, and what most of mysql will do is to do whatever will allow the operation to complete. So, as long as you know what & when to test - you get can good transaction support out of mysql. Not a big deal if you know what to look for and don't mind writing that code. Personally, I wish they'd get their shit together and fix this crap since I'm getting very tired of writing this kind of code.

Thank you

[YrWrstNtmr]

Finally..some rational thought in here.

I've used Access and/or VB to talk to a backend Oracle or SQLServer DB. Different office has needs for different views/functionality? Deploy them a different MDB, talking to the same tableset.

MySQL doesn't have sequences

[ttfkam]

At least not ones that conform to the ANSI SQL definition of sequences.

Sending a NULL to an AUTO_INCREMENT field is not the same as an SQL sequence.

Can you skip numbers easily in MySQL (eg. tell the DB to continue from some arbitrary number next)? Can you put explicit values into the column without the AUTO_INCREMENT overriding you silently? (My favorite part: MySQL changing the values you enter without even telling you.) Can you tell a MySQL sequence to have a maximum value lower than the maximum value of the underlying datatype or a starting value of higher than 1? (No, multiple inserts and then deleting them after doesn't count.)

The syntax may be different between all three, but when you pull back the curtain, the one in MySQL are a very different and limited beast.

Re:MySQL doesn't have sequences

[skiflyer]

I'm not saying MySQL's auto-increments are perfect... but at least get your facts straight. You can easily skip numbers, you can put explicit values in a column without being overriden and you can certainlly have a starting value higher than 1. I don't know if it can have a maximum value lower than the maximum value of the underlying datatype can't say I've tried that one... but all the rest I've just implemented within the last week so it's very doable.

Re:windows developers

[skiflyer]

And don't forget those who are oh I dunno, concerned about selling their product. You don't go to a customer site and say, no no, you need to reformat and install Linux, you'll love it honest... your other programs? What no, you don't need those. Not a good way to make a sale.

A lesson to an errant moderator

[ttfkam]

...who modded me flamebait. Since I don't feel like reposting all of it, here's another one of my comments that covers this:

An example of MySQL suckage

Production quality doesn't just mean "doesn't crash." Production quality for a DB means "keeps my data safe."

Once again, there still isn't a production-quality MySQL server.

Re:windows developers

[Green Light]

What's worse, his/her product "Jahshaka" runs on Windows...

Go figure!

Ease of Use

[Ween]

I hardly consider GTK+ + MySQL to even be in the same league as Access when it comes to ease of use and interface design. Sure, Access isnt that great of a program, but it is extremely easy to create a simple database+frontend in. Another nonfree alternative is Filemaker Pro. This is a very nice application.

MySQL SUCKS

[Space_Soldier]

MySQL is just a package of different database engines. Now they have added SAP DB (Adabas) under MaxDB by MySQL, which is now marketed as the open source enterprise database. Before that MySQL was marketed as ready for the enterprise. Sometimes I wonder if the people behind MySQL ever write a line of code. They need to get rid of all the other stuff, and write their own database engine, if they want to be taken seriously. If I wanted an open source database, I would go for Postgresql or Firebird. Data corruption with MySQL is very common. As for those that talk about features, do you care more about the data or about one more feature on how to extract it? Now for desktop applications, you have to be a bloody idiot to use MySQL for that, Access is much better, or BerkeleyDB from SleepyCat. SQLite is a piece of crap. What kind of a moron thinks that the only type in database should be STRING? It is very slow on INSERT, but fast on SELECT. Access (Jet Database) is dying, it hasn't been updated in a while (2000). Longhorn and it's sql server file system over NTFS killed access. MySQL is a joke and it should be treated as such. If those PHP zealots would know a thing about databases it wouldn't be the "most popular open source database". There is a reason why IBM, Microsoft, and Oracle are the 3 standards.

MS SQL vs MySQL?

[James Lewis]

Could someone explain to me the difference between the two? (I've had a database course, so it's OK to get technical) There was that top 10 database article a while back, and while I saw MS SQL in there a few times I NEVER saw MySQL. What is MySQL better than MS SQL at, and vice versa?

Re:MS SQL vs MySQL?

[Anonymous Coward]

MySQL is REALLY fast, weak on security, with no enterprise funtionality. Primarily built for website content presentation.

SQL Server is quick, strong on security, high amount of enterprise functionality. Primarily for companies needing high end solutions with the money to pay for it.

SQL Server has the capacity to cluster multiple servers into one virtual server for redundancy. you walk up, pull the power plug, and the data movement doesn't stop. I'm a DBA on SQL Server as a profession, so I understand how to implement SQL Server. The trick to determining which is "better", is to understand the needs of the solution. SQL Server is a very mature product. reliable; secure;and fast, but unfortuanally it follows Microsoft's thought to only be availiable for Microsoft platforms. I wish they would think more like our Linux comrades. MySQL can run on any flavor of Unix and with Cywin, it can run on windows.
MySQL is a very good database program I have used it for a while. SQL Server is a good solution also, but since it is a enterprise class RDBMS, there will be a learning curve. You will definetly understand MySQL faster. Neither product is "better" than the other. Just different. Pick the right tool for the job.

I'd like to run postgres on windows...

[Phil John]

...without downloading cygwin...native win32 support was meant to be in 7.4 but didn't make it. I deploy to FreeBSD but develop on windows (because some apps I rely on still don't run in Xover office and are too damn slow in vmware under linux). Don't get me wrong, it works fine in cygwin, but it doesn't sit well with me having to do that. Let's be honest here, PostgreSQL doesn't have an important feature of Oracle, the HAL (or whatever oracle call it), PostgreSQL has to be ported manually and it's taking a long time. True, it's a lovely system, but they need to sort this out before they start making even greater inroads. Also, visiting the PostgreSQL site doesn't sit well with PHB's. mysql.com looks very businesslike with licensing options and online stores, plus loads of documentation. Bring up postgresql's site and look at this message in the top right hand corner: "Tech Support requests to this address will be ignored! Please use the appropriate mailing list."

That wouldn't fill me with enough confidence to ok deployment of a mission critial app if I didn't know what you and me both know, PostgreSQL kicks ass.

Re:Suprised that the Review didn't mention this

[Anonymous Coward]

Dumbest. Post. Ever. If you're going to make a half ass attempt at cut and paste trolling, at least use something that isn't part of the freaking article! If you were making a serious post, you have the worst case of ADD I've ever seen.

Now that's funny

[ttfkam]

Your claim that "professional" companies only use statically typed languages also seems rather unfounded.

I'm looking at my previous post, but I can't seem to find that statement. Where did you see me write that? Nice misquote. You missed not only the spirit, but the letter of what I said. I only brought up Perl and PHP because those are examples of languages where many people are just starting out -- Perl was one of my first as well. I also brought those up because, unlike statically typed languages, errors can go through easily because there's no catch to say "this is supposed to be a number between 0 and 255" for example. Everything else you inferred was from your own defensiveness I think.

As for the PostgreSQL gotchas page, as a user of PostgreSQL, I can safely say that I have not come across anything like what was found on the MySQL gotchas page; There was nothing even close with regard to scope. And in the event that some weird behavior comes up, I'm pretty sure that PostgreSQL will at least warn me about it.

If the MySQL page were written with the rigor of the PostgreSQL page, there would be far more "gotchas" for MySQL. For example, the two PostgreSQL issues were differences with the official SQL spec -- not data consistency issues. If you were to include all of the aspects of MySQL that did not conform to the ANSI SQL standards (any of the ones over the years not to mention the most recent ones), the gotchas page would be about as long as the main MySQL documentation. Yes, it's that bad.

Now then, as for the failure to quote the third sentence, I'd be very surprised to find that "a few more gotchas to follow" would come to equal the twenty seven gotchas that MySQL has. There are twice as many categories of gotchas in MySQL than there are total gotchas for PostgreSQL.

But fair's fair. I'll add a few for PostgreSQL.

"MySQL with MyISAM tables is faster for selects and mostly read-only databases." Then again, I haven't accurately benched it myself. When I make a database schema in PostgreSQL, it cannot be mapped to MySQL easily. The only way to make a comparison is to strip my PostgreSQL database of many of its advantages both in performance and maintainability like triggers, views, rules, stored procedures, foreign keys that actually prevent you from dropping related tables, domains, schemas, real SQL sequences, etc.

Oh wait! MVCC (Multiversion Concurrency Control) doesn't apply when trying to check for duplicates with a SELECT followed by an INSERT. In other words, short of locking the table, you can't guarantee that if a SELECT comes up empty, that another client doesn't INSERT something before you get there. This of course is followed by an error as opposed to silent do-whatevers of MySQL coupled with the fact that MySQL can't do any better with this problem and frequently does worse.

Here's one: PostgreSQL cannot restrict access to just a column. The only workaround is to make a view without that column and set the rights appropriately. Finally, a real issue. I should submit that one to the PostgreSQL gotcha page maintainer.

Is PostgreSQL the end-all be-all? No, of course not. I never said it was. I only assert that MySQL is only fitting to a project with a very loose set of requirements -- requirements that don't include safety of your information, the raison d'etre of a RDBMS.

So if you want to store data, be able to retrieve it quickly, don't do too many inserts or updates, only access from a limited amount of simultaneous clients, don't care too much about crap accumulating in the tables, and have a good reason to eshew a custom data storage structure tailored to your specific needs, then by all means, MySQL is the database for you!

Use what you like, but choosing a toy (Yes! I'm calling it a toy!) to a real RDBMS seems a little bit unfair.

Ahh, now I see

[ttfkam]

...where you got that I thought "professional == statically typed." It was the comment about Fortune 100 companies wasn't it? Hmmm...

No, that was not my intent. Truth be told, I doubt they are using Perl or PHP to any large extent. Or even C++ for that matter. Chances are that the workhorses for those companies predate the creation of Perl and PHP...probably even C++ in its CFront days. Those companies have tons of COBOL that, while it works, they will never ever mess with. If their infrastructure were written in Eiffel, it wouldn't be going anywhere. If it were written in BASIC, as long as it has worked for twenty years and continues to work, no one is touching it.

Once again, I brought up those languages because of the legions of 31337 coders whose claim to greatness is a 500 character long, single line Perl script. They're not dumb or inferior. They just don't know any better yet.

Your ID

[Azghoul]

No contribution to the story at large here, I just wanted to comment on your slashdot id. Excellent beer. :) Nice to see someone else knows what it is.

Umm, unless you're really creative and that was the name of your last D&D character or something. :-P

Consider other alternatives

[Jeddawg]

There are other alternative servers that allow access from other development platforms. The world does not boil down to M$ SQL and MySQL (and Postgres, etc.) as most of you seem to think.

Take for instance, Advantage Database Server. Advantage supports development in Borland Delphi (and now Kylix), Borland C++Builder, as well as Java through the new Type 4 JDBC driver, php, Perl, and, yes, all Microsoft development environments, including .NET. In addition, the ODBC driver and OLE DB Provider open up tons of other development environments. (And, they have php, perl, and ODBC clients that work on Linux, so you can develop Windows or Linux clients.)

In addition, the server supports Windows systems, Linux, and NetWare. So, there are a variety of development platforms, as well as server platforms. And, while it's not open-source (sorry), it comes at a fraction of the cost of Oracle or M$ SQL.

Please remember that other options exist! (And, if you want a no-cost way to store data, Advantage has a Local Server dll [or .so] that can be used free of charge!)

Why not use a web interface instead of GTK-LAMP

[acomj]

I've just built a database prototype for a client that uses
LAMP (Linux-Apache-Mysql-Php). Oreily calls it the open source web platform and has a webpage about it. Why would you use GTK and limit your platform?

Because its a server application the program doesn't have to be installed on different machines (except the web browser), which makes it easier to install. Programming some stuff in a web interface is tough, but not too too bad.

I actually did the demo on an ibook (ran the database/webserver, client browser on one machine. . used fink to install MySql and a php package from entropy.net. So easy to install I was amazed. although if I deploy I'll compile myself to set the options exactly as I want.

Python?

[streak]

People are talking about high-level Languages to interface to MySQL and other DBs yet no one has mentioned Python.

It has connectors to MySQL, Postgres, Oracle, DB2, MSSQL (only if its hosted through IE I think :\)

Re:Python?

[Anonymous Coward]

Py_what???
Nobody, but geekest geek, use this language... Its a hell just try to find a (serious) isp that provide reliable hosting in python

Yep. - it took all of six steps.

[Slashamatic]

1. browse to Cygwin d/l page
2. CYGWIN SETUP
3. Select install from Internet
4. Open databases tree
5. Select Postgress
6. Install

It doesn't get much easier.

The DT's.

[mypalmike]

I'm pretty sure the beer was named after the medical condition.

Re:The DT's.

[Azghoul]

Hey, whaddya know? :-D

I am in awe

[ttfkam]

I've just pointed out that the salad has roaches and rat feces in it and someone steps up to chastise me for using the wrong fork.

INSERT INTO test
(id, num1, num2, num3, price, code)
VALUES (
0,
99999999999999,
NULL,
'A quick brown dolphin...',
21474.83,
'ABCDEFGHIJK'
);

Now the syntax should be kosher. ...and MySQL still acts like a brain dead cat on crack.

All functions I write check that the data I'm getting or putting to the database matches the data type.

And I applaud you for that. It's good to know that you never make mistakes, are always fully rested, are never up against a hard deadline, etc. I'm not questioning your coding skills or your intelligence. On the contrary, I think that extra checks are always worth it. What I am saying is that the database, the final repository for your data, should be making at least the same amount of effort.

When the string 'A quick brown dolphin...' is passed to an integer field because I'm overly tired, a collegue hasn't been as rigorous as I usually am, some crack-smoking intern had a brain fart, or whatever, the fact remains that MySQL detected that it wasn't an integer. It didn't try to throw it in as the first 32-bits of the binary string. The table needed a number, MySQL had no idea what number you meant, and it decided to put in the default value for the column according to its datatype. In other words, it placed a value that I didn't explicitly specify through the client or schema and did not warn the client that it had done so.

Imagine if all of those POSIX networking functions didn't return an error code. Why not? I'm sure there are folks out there who will say, "But my network is always up, the cables are of highest quality, and in all the time I've worked there, I have never had a socket connection fail; so why do I need the error code back?" No matter the preparation, no matter the training, no matter how good the conditions are, when the rubber hits the road, I would like to be able to see out of the windshield to see if I've spun out.

This is the salient point that people (including the pager-adorned gentleman in the parent post) appear to blithely ignore. It's the giant pink elephant in the corner that everyone notices but no one wants to talk about.

By all means, perform the extra checks. Perform a check at compile time, at deploy time, and at runtime. Why are MySQL folks so averse to having a check at insert/update time as well? Why is it such an affront to want your database to do its own data integrity checks? Why is it so horrible to expect a relational database to do its job!? I honestly want to know. I would very much like some insight into this rather than picking on my choice of salad fork.

Open Source Access library

[nut]

I'm going to take this opportunity to plug Haccess, A set of fast, cross-platform, thread-safe C++ libraries for accessing Microsoft Access database files.
So far written entirely by my friend Oliver, it badly needs some dedicated C++ programmers to help out. I believe it has already made its way into OpenOffice as a filter.

You might think...

[Hecatonchires]

but you'd be wrong. I often read huge swathes of text just before bed. I let it sink in. Then I try to implement, using teh book as a reference, but the initial thought process happened away from the screen.

Since no one else asked

[fataugie]

WTF is SQL Server 97?
Do you mean SQL Server 7?

Re:windows developers

[dave420-2]

Idiot.

Embedded apps

[Stone316]

Isn't sql lite for embedded apps? That would imply (to me) that its for PDA style applications. Which would mean that there would only be one users and database level locking wouldn't be an issue.

Incorrect

[Ryosen]

Access is not a stand-alone database. It is a stand-alone database *file*. Too often, developers make the mistake of confusing Access files with an actual production-capable database. It's great for prototyping and for use as an embedded database, but that's about it. Some important things to understand.

First, the term "Access" is a misnomer. Access is an application, part of the Microsoft Office suite. It is simply a front-end GUI to the JET database library.

Second, interaction with a JET database is conducted on a pure file-base level. In a multiuser environment, each user accessing the database file must do so as if they were reading a simple file across a network. If you execute a query against that file, you must copy that file down into your local memory for manipulation. This is because there is no JET "Server" to marshall calls to the database file from multiple clients. If you want this functionality, you must develop a server process that listens for requests. If you're going this extra step, you might as well use a real database server (e.g. SqlServer, mySQL, Postgres, Oracle, etc). If you're not in the business of writing a database server, it's much better to let someone else do it.

Third, JET files use a very primitive method of data locking. I might be a little behind in the current implementation, but as of Jet 3.5, you could not do row locking. Data in JET is managed in pages. IIRC, the page size is 4k. So, if you need to lock a row of data, you actually end up locking several rows, depending on the size of the rows involved. This creates greater data contention and impacts performance. Again, this might have changed recently, but it is still inefficient.

Fourth, JET is very easy to corrupt. Since each client accesses the database file simultaneously, if one of those clients crashes, they can end up hosing the database. Contrast this with a server where if the client crashes, the database server can manage the crash, as it is an independant process.

Five, JET does not have any ability to maintain itself. It cannot rebuild its own indexes, purge deleted rows or compress itself without manual intervention. Again, this is because there is no server. A maintenance scheme must be developed whereby these functions are performed on a periodic basis. For a database of appreciable size and use, this could be as frequent as hourly and as minimal as daily. In order to pack/compress the database, the maintenance program must have an exclusive lock on the entire database file. If you maintain a 24/7 system, this can have a severe impact. Plus, there is the issue of forcing client applications out of the database programmatically. Easy enough to set up, but still a pain in the ass.

Full database servers perform these functions on their own, in real-time, without excluding the clients.

There are still other reasons not to use JET for anything but the most trivial programs, but I think I've kicked it enough.

So that I am not misunderstood, JET does have its uses. It's suitable for stand-alone applications where the maintenance processes can be integrated within the app itself. It's ubiquity on the Windows platform (since Win2000) allows you to minimize your installation package size and, of course, it's free. However, just like everything else, it's important to know what is appropriate and what should be left for more robust and capable solutions.

Standard disclaimers apply, IMHO, YYMV, yadda yadda.

Re:Incorrect

[scottj]

Not trying to give M$ any props here or anything, but Access isn't just a frontend for JET. It can also, via ODBC, be a frontend for just about any other database, including MySQL. Sure, it's slow and feature-limited when using linked tables via ODBC, but it sure beats JET over a network.

Re:Incorrect

[Ryosen]

Yes, this is true, it can be used as a front-end for just about any ODBC datasource. However, the context in which it is being used here is its own "native" database. The database was once singular to Access itself, but with its inclusion with Visual Basic 3.0 in (approx) 1993, it quickly because associated with other applications. With Windows 2000 (possibly 98), it became included with the operating system. At that point, Access the program became detached from Access the database.

Management doesn't like either

[Stone316]

Cause they are cheap. Seriously, i've been a DBA for 8 years and just the mention of mysql or postgresql can cause involuntary flinching in management.

Sad fact is a significant number of applications out there that can require a database could perform just as well using postgresql. (After reading the mysql gotcha's list and experimenting with it over the past week I don't think i'll be recommending that DB for awhile. Unless its for a website backend or something.) But for some reason management things they need to spend hundreds of thousands of dollars on a commercial DB.

I try to stay away from licensing but yesterday I priced Oracle for a server were going to put in.. I almost fainted. With these up and coming OS databases the big boys are going to have to make some serious cuts to their pricing scheme or start releasing stripped down versions.

PL/SQL is like any other language

[Stone316]

If your a decent programmer you won't have any problems. Have you ever tried to debug a stored procedure in mysql? Last time I checked every line was stored in the DB as a single line. So an error on line 100 would return as error on line 1. That is a nightmare to maintain.

Most real DBA's that I know of (and I know quite a few being one myself) don't understand why mySql is so popular. As mentioned numerous times above this thread, it allows you to insert crap into your database.

Sure, for simple websites mysql or small startups without capital its fine but for the job i'm paid to do, I won't risk it on mysql.

MySQL sucks, hands down

[Anonymous Coward]

Look let's get it straight once and for all. MySQL sucks, hands down, it is crap. MS Access sucks, hands down it is crap.

If you want to Mickey Mouse your careers and use MySQL or MS Access to develope applications, then be my guess. A month after your done, your client will be hiring me to rewrite their application using MSSQL because your app can't handle then load.

The bottom line is this, if you want to develope enterprise level applications, you have no choice but to use MSSQL, Oracle or Sysbase. Using MySQL shows how unexperienced you are as a developer.

And don't get me started with the morons out there saying that they don't need triggers, stored procedure or views. Let me ask you this, morons: When you develope a database application, how do you start? Do you just start coding your application and build you database as you go along? Or do you actually sit down and design the database first, putting in all the referential integity and business rules in? If you take the first road, then idoit, answer me this. What happens when someone goes to enter data in directly into the database and not from you application, and this will and does happen. Your database is going to get corrupt because the person now can by pass all the your business rules that you, the wondeerful programmer that you are, coded into your PHP application instead of putting them into the database.

I personally cannot stand people who think MySQL should even belong in the same product line as MSSQL or the other commercial, real, databases out that.

We have an old saying in the windows world: "Friends don't let friends use Access." Which means Access is great for a personal, jerk off app, but if it will ever be commercial, you need to use MSSQL. I think you OSS people out there need a similar saying.

IIRC

[Phil John]

MSSql, Oracle and IBM's DB2 all come in a cut down version for around $5000 per processor. Ok, so you don't get some of the nice features like advanced clustering and replication, but if you absolutely have to have oracle (i.e., running something like SAP/3 for a small shop) then that's the way to go. The problem with Oracle and DB2 is that even once you've bought the products, you need to pay a DB guru to keep them ticking over, they can't just be setup and left, like PostgreSQL and MySQL. We use MySQL for a lot of simple websites. My companies flagship product however uses a database abstraction layer, so you can use it with any database that has the base requirements.

My mistake

[ttfkam]

I stand corrected

UNION

[jjga]

Unions are not supported either in MySQL 3.x. It is easy to replace it with several queries, but not convenient when you have a lot of SQL code that relies on unions.