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

[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

[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

[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]

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

[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

[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]

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

[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

[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.

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.

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]

another interessting read is here.

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

[_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

[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?

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.

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: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.

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

[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

[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.

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.

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 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.

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: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?

[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?

[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.

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.

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

[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.

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.

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.

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)=

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.

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.