MySQL in a Windows environment 2

[Terpsfan]

I have generally used Access, MSDE or SQL Server as my database, with generally a VB or .NET application. I have heard all kinds of great things about MySQL...much faster than Access or SQL Server, handles more concurrent users (not sure if I can trust benchmark tests, because all of them seem to have a bias). My question is this...in order to use MySQL:
1) What should I expect to be the benefits/costs over Access/SQL Server.
2)Can I still use Visual Basic/.NET as the front end application code or would I be better served using C++/Perl, etc.
3) What is the best reference tool (book, manual, etc) for learning various aspects of MySQL.
4) What GUI tools are available for MySQL in a Windows environment. I'm under the impression that MySQL is mostly command line stuff.
5)How does MySQL rate vs. PostGreSQL?

Thanks in advance.

 

[rzs0502]

I've been working with MySQL for about 2 years and it's great because it's powerful, fast, flexible and free.

To answer you questions -
1) It's free unless :
If you distribute MySQL Software with your non open source software,
If you want warranty from MySQL AB for the MySQL software,
If you want to support MySQL development.

2) You can still use VB as your front end using MyODBC (

http://www.mysql.com)

or as used by us, the MyOLEDB Provider.
But you can basically use any programming language for your frontend.

3) There a excellent reference books at

http://www.mysql.com/documentation/index.html.

Get the pdf.
I also have downloaded on of those Teach Yourself MySQL in 24 hours books. (Can Email if needed)

4) I personally use the MySQL Control Centre (download from

http://www.mysql.com)

for all Admininstration (similar to Enterprise Manager). Should serve all your needs from Administration to SQL coding.

5) I've never used PostGreSQL so I cannot comment.

Hope that helps...

 

[QatQat]

PostGreSQL is ACID compliant without any need for external Storage engines such as InnoDB.
It is generally a more complete RDBMS than MySQL due to its longer development however, unless you need Stored procedures, a lot of transactions, views etc.. mysql should serve the purpose brilliantly.


Bye

Qatqat

Life is what happens when you are making other plans.

 

[LittleSmudge]

Like you Omega36, my background is Access based. However, I've recently been working on a number of databases that were originally Access 2000 BackEnd & Front End split. I've converted these to MySQL back end and created new Access front ends to provide the user interface.

It needs NEW Access front ends in orer to take advantage of the Client/Server architecture that MySQL can provide.

You need to set up System DSNs on the user's machines then connect the Front End to them via ODBC.

Use Unbound Forms.
Open RecordSets to retrieve the data
Monitor the controls for changes
Write changes back to the database again using Recordset via ODBC

For read only lookup activities you can use PassThrough queries.


'ope-that'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Terpsfan]

I read recently that one shouldn't fool with a VB/Access front end to MySQL because it's often slow - a lot of run-time libraries in the middle between the db and application. What's your response to that LittleSmudge?

 

[LittleSmudge]

On the local network at headquarters the speed performance has been perfectly adequate - forms open ( and populate ) in the "blink of an eye".

For users on remote sites, the advantages of the client/server structure are soooooo much better than Access's File/Server that the users are seeing great performance enhancements.

Certainly, for my requirements at the moment it presents a working solution.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Terpsfan]

Thanks Smudge that helps to know about Access as a front end. However I am still concerned about the fact that MySQL has no referential integrity or stored procedure support. I have a lot of table relationships and really have a need for cascade updates and deletes...I don't like orphaned records. How big are your databases by the way?

 

[LittleSmudge]

My Databases are BIG by Access terms.

The Back End of one was about 60MByte and the Front End was not much smaller ( But some of that due to legacy image files - logos on forms and reports - stored in an inefficient format )

Yes, I too look forward to version 5 release ( That's due to have SPs )
I've rarely used Cascade Updates and I can just as easily write a bit of code that does cascade deletes.
I do like the data integrity security of having referential integrity ( But it is 'Belt and Braces' ) so I'm prepared to live without it for the moment to get the other advantages that MySQL has to offer.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[rzs0502]

Interesting read...

http://www.mysql.com/press/user_stories/Red One Aviation.html