With a little side of applesauce...

Monday, January 29, 2007

NULL and empty strings in MySQL/Access

Here is an interesting email which details a problem between MySQL and Access 2000:

http://listserv.muohio.edu/scripts/wa.exe?A2=ind0302b&L=archives&D=1&P=8035

This caused many headaches for our folks who used Access frontends to manipulate MySQL backends. I’m not sure if any of the newer versions of Access continue to exhibit this issue or not…

Here is the text:
>Hi. I’m currently running Access for our database which is for pretty
>much a single user. Recently we got a new computer with XP and the latest
>Access. There is a conversion process that can be done with our Access 95
>db’s, but the product is then not readable from the other computers which
>still have Access 95 on them (and we are not upgrading them).
>
>I installed MySQL for Windows on one of the Win 98 computers and can throw
>the database from Access 2002 to MySQL. This can be accessed with Access
>95 or Access 2002 and that’s nice.
>
The only problem you might run into when using Access as a front-end is
caused by the ancient sql standards to which the jet db engine adheres.
Here is an explanation:

/We have identified a problem with using Access as a front-end with the
MySQL server. Thanks to Michael Zedeler ([log in to unmask])
for this article that explains the problem quite thoroughly. :)

———————————————————————————

If you get the following message: “Write Conflict - This record has been
changed by
another user since you started editing it. […]” from Access 2000, its
likely to be a
problem that arises from differing standards between Access and MySQL.

>From old times, according to some SQL standard, an empty space is by
definition
equal to NULL. This disposition has caused many grievances over the
years, so
MySQL rectifies this by deviating from the standard. In MySQL, NULL is
NULL and the empty string is the empty string.

So when Access retrieves a row from MySQL with fields containing the
empty
string, it automatically translates them into NULL values. When you try
updating this
row, the query sent from Access 2000 to MySQL will fail to update the
row.
An example:

You have inserted the following row into table a:

id | name—-+————————- 1 | >- contains the empty
string. Not
NULL.

And then you retrieve the row using Access 2000, subsequently inserting
the value
“Smartypants” in the “name” field.

You would expect Access 2000 to send the following query to MySQL:

UPDATE a SET name = ‘Smartypants’ WHERE id = 1 AND name = ‘’

(The extra “AND name = ‘’” is included to avoid updates in case that
some other
user has already updated the given row.)

But what Access 2000 actually sends is:

UPDATE a SET name = ‘Smartypants’ WHERE id = 1 AND name IS NULL

That query fails because NULL is not being treated as the empty string
by
MySQL.

How to fix the problem

I haven’t found any way around it, but to stop using empty strings in
character fields.

The problem only arises when other systems inserts data into MySQL
tables, so
you’ll have to make those systems start using NULL in stead of the empty
string,
where necessary. When Access 2000 inserts data, it will always translate
the empty
string into NULL, thus maintaining data that are consistent with the
(old?) SQL
standard.
————————————————————————————-

As many of you insert data into the database with empty strings, and not
NULL, this means that you will have to write some code to check to see
if the value in the form is actually an empty string or NULL. Then
write the query accordingly. For examples of inserting NULL values into
a MySQL database, check out:

http://www.mysql.com/doc/P/r/Problems_with_NULL.html/

This not only effects using access with mysql, but also with any “modern” database server, including Microsoft SQL Server. :( (Go figure, they can’t even get on the ball with their own products. ;-( )

>
>I have read that php is the way to go to access the MySQL database.
>
This is one possibility. You have mentioned another, with MS Access.
You can also write applications in Perl, Python, (I’d imagine C++), and
probably almost any other language, as long as you use the ODBC drivers
to access the data. (The only thing about PHP is the incredibly
powerful and mature library that allows you to interface with the
database. I’m sure that Perl DBI mods are pretty mature as is
Pythons… Pick your poison.)

>What
>I’ve read seems to indicate to me that I need a web server to do this.
>
This is true. PHP is a language that is primarily used in conjunction
with a web server. (Apache is the way to go here. It is the most
stable, and configurable, web server on the market. (It’s also free :)
It has mod_php, which might come with apache2, the best version for a
windows installation. It is fairly simple to setup, and you can
probably enable mod_php during the install… (My installation handles
1.5 mil hits on Apache2, coldfusion/php enabled server, on linux, no
problem.) The site is www.apache.org. The documentation is pretty
thorough, and groups.google.com is a great place to find out other
information about running the server. (If you are running NT/2000/XP,
it will install as a service, so that it will start automatically at
bootup.)

>Can you give me a little clarity on this. I don’t want to get a book on
>php and try to learn that if I’m going to have to install a web server to
>use it. I would like to find another way for the average user to query
>the MySQL database other than Access. I would also like to be able to
>input data into it some other way that is similar to the way I can input
>with Access - using a form or datasheet view. Can php do this for me? Is
>the learning curve high on this? It was pretty steep for learning to use

>MySQL and learning SQL to do these tasks is not something I really want to
>do. I don’t mind spending the time creating the forms etc, but I’d like
>it to be fairly easy to use them after they are made. Making the forms in
>Access took me some time to learn and to create, but now they are easy to
>use.
>
>
If you have any previous programming experience with C++ or Perl, PHP
should come fairly naturally. Otherwise, it can be a bit difficult at
first. (As anything new is…) A great place to steal code from, would
be www.phpmyadmin.net. There gui admin is written in PHP, and it is the
most amazing web interface :) They have a lot of great code in their
product. You might also want to search the web for some PHP interfaces
on the net. Try www.hotscripts.com, or search on google.com. You would
be surprised at the applications that are Open-Source and available for
you to modify, instead of starting from scratch.

If you decide to use another language, such as Visual Basic, you will
not need to use a web server. _But_, you will need to install the
program on an application server, or on each individual machine.

HTH, :)

speeves
cws

No comments: