From Access to MySql

any directions available?

[txbakers]

I've downloaded mySql and MySql-front (very nice GUI for the database) and myODBC.

I'm ready to start converting from Access to mySql, but would like to find some how-to pages.

Do any exist?

Thanks.

[william_dw]

Hi there!,
When you say converting i assume you mean converting the data and not the actual CGI/ASP/* code which you are using with the database & SQL.

The SQL should work without changes,
as SQL is pretty DB independant for simply statments

To actually convert the data and get it into the MySQL database, there are several converters, including:
[cynergi.net ] (free, but you have to compile it yourself in visual basic)
[mdb2mysql.de ]
[webattack.com ] (shareware, $39.95)

and finally
[dbtools.com.br ], which is insofar as i'm aware: free, easy & small. just click downloads and follow the instructions & links.

HTH,
William

[william_dw]

Ahem,
although the last link I put in seems to be the best from the reviews I've seen,as I'm getting into mySql I clicked the link myself and had to battle a whole bunch of 'access forbidden' intermittent errors, if you have problems there's a mirror at [dbtools-uk-download.hubnut.net...]

Good luck,
Dw

[txbakers]

Thanks William - just what I was looking for!

[lorax]

Hello,
I may be a little late here but if you can, I would recommend you don't use ODBC for your connection to the MySQL database. Call the database directly. No need to use an intermediate layer.

[mole]

you probably don't need to 'convert' the access database to MySQL. Just configure the MyODBC driver to connect your Access front end to the MySQL back end then do File>Save As/Export on each of your tables. Chose file type 'ODBC' and it'll write your data straight into the MySQL database.

[txbakers]

even better - thanks for the help.

I just installed JRun for a J2EE app server so I can start learning JSP and EJB.

Lots to learn. I like the mySQL so far.

Does anyone have comments in relation to this DB vs. others such as MSSQL or Oracle Personal?

[lorax]

Hello,
With regards to MS SQL - MySQL has a nice price tag. I also like the fact that it's so well supported by more than one entity as opposed to MS products. You can find all kinds of help from those who've been there before us. My 0.02¢.

[ppg]

I've only used SQL server 7 with asp and mysql with jsp & servlets, but the main thing that immpresses me about mysql is the speed, especially considering its pretty much free. A couple of times I've come up against things which mysql doesn't support, like I wanted row level locking to pevent concurrent updates to a row, but in the end I got round it by synchronizing the relevant bit of java code, which I suppose is one of the nice things about coding in java - you seem to have more access to the nuts and bolts of whats going on than with asp.

heres a couple of links in case you don't have them already:

[archives.java.sun.com...]

theres a link at the top to join, its a pretty good list but quite high traffic.

[servlets.com...]

a good book on servlets

[oreilly.com...]

a good book on jsp

[java.sun.com...]

the jsp spec.

I havn't used EJB yet, as far as I can see unless you have a big site that needs load balancing its a bit overkill, interesting stuff tho ;)

oops, forgot: popular open source jdbc driver for mysql:

[mmmysql.sourceforge.net...]

[lorax]

Hello ppg.
You are correct that plain-jane MySQL does not support transactional processing very well. That being said, Nusphere has produced a version of enhanced MySQL that does support transactional processing. No, it's not a freebie but if you're looking for the speed of MySQL without the workarounds, you should check them out. www.nusphere.com

[ppg]

thanks for the tip lorax.

Are you talking about the 'Gemini' table type that nusphere provides? Do you know if this provides better performance/more features than mysql-max with the innoDB tables?

[lorax]

Hello ppg,
Yes, I forgot to tell you to look for the Gemini table. I don't know how well their product compares with the others you noted. I do know that I've heard good things about their product but it's all hearsay. I work with their PHP development tool and I can say that it's pretty good for a recent entry into the market. Nusphere seems to have a solid foundation and understanding of what's needed and seem willing to adapt to their user's needs. For what it's worth. HTH.

[txbakers]

What is wrong with transactional processing with mySQL? By this do you mean scalar functions in the Query?

If there is an issue with a serious function I might want to look elsewhere.

[lorax]

Hello txbakers,
The free version of MySQL doesn't do row level locking on a database table which is important for very busy web sites. MySQL is a great product but it does have some limitations. That being said, it is in use on thousands of web sites including those which process credit cards.

The lack of row level locking means that you can't have 2 people editing a table at the same time - IE: placing an order at the exact same time which would require a write to the db. The chances of this occurring increase with the number of transactions being executed. Without row level locking, 1 user will have to wait while the other's transaction is processed and recorded. Not a big deal on smaller web sites but can be a major headache if you do a lot of sales with your web site. How you handle this as a programmer is up to you. Some folks let the user wait while others notify them the db is busy. Don't let this minor issue keep you from using MySQL. It is a very quick and reliable database not to mention its free and well supported.

[txbakers]

I'm using a DB with many tables that could very well be updated at the same time. It's not for ecommerce, rather for teachers to keep track of their information. There are all sorts of records.

What would happen if 3 people logged in at the same time and tried to hit the save button?

Would there be an error message, or would one just wait?

I recently did a demo with 40 people on the same account with Access and there wasn't a problem at all.

This concerns me now.

[lorax]

Hello txbakers,
We're talking about milliseconds to open a table, edit/insert/delete data, and then close the table. Many factors play into the performance equation. Not the least of which is the server and how well it's setup/optimized. Also, how much data you're sending along in the query. I highly doubt that even if you sat 40 people down and told them all to hit the submit button at the exact same time, that you'd notice the delay. If, however, you had a thousand - mmm yeah.

Will MySQL keep trying until it's successful or times out - I'm not sure.

MySQL will return an error code and an error number if it can't complete the query which you can capture and use: for the error in a readable text format use mysql_error() and for the code use mysql_errno(). I don't know if will return an error for the table being busy (it must!) nor what the code number is but if/when you find it you could use it in your code to tell the user to wait or to try the submission again in 50 milliseconds. HTH

[ppg]

I did a bit of checking up, it looks like theres no reason why you can't use mysql.

what you need for transactions is mysql-max, which is an extended version which supports transactions through use of the innoDB tables (http://www.innodb.com/ -erm- its alright to post URLs like this right?). The good news is you can get a binary of the whole lot together at the mysql site. Theres documentation on it there too, also more documentation at the innoDB site.

you can use transactions in your beans through JDBC, which you use to connect to the db. As far as I'm aware the mm.mysql driver supports transactions.

[sugarkane]

> its alright to post URLs like this right?

Sure, links to anything available under an open source licence are fine :)

[lorax]

Hello sugarkane,
That'd be 'license' I believe. :0

[sugarkane]

Aha lorax, that would indeed be 'license' if I was talking about a specific licence that originated in the USA (eg GPL), but as I was referring to licences in a non-specific and general manner I used the spelling of my mother tongue, The Queen's English

:)

[txbakers]

Ah, this colourful humour. Time for my bangers and eggs. I need to take the car in and have a peek under the bonnet.

[mole]

lorax and sugarkane - shut up about the spelling and stick with the matter in hand?

[mole]

txbakers - you too please?

[sugarkane]

mole - yes, you are quite correct. Let's try to stay on topic. Apologies all.

[txbakers]

yes massa. Please don't beat me no mo, I'll be good.