Wednesday, February 20, 2008

Why to use MYSQL and MYSQL API'S?

Why MySQL?

The list of relational database management systems (RDBMSs) those are full-featured enough and robust enough to bet our company on.
MYSQL is the only one on the list which is affordable by all simple company and available option to download free.
MYSQL is best for performance, versatility, cost and robustness.
Before MYSQL 4, some scoffed. No query nesting, no stored procedures, no views, no triggers.
Correlated sub queries came in with version 4.1.
In MYSQL 5.0 we have enough options like stored procedures and functions, views, triggers, XA transactions, a cluster engine, and a full-fledged information schema. This version is stable in production at present.
Still lot of new features and other functionality was on the way in MYSQL 5.1, which is in beta at present.

MySQL APIs

A major strength of MySQL is its Application Program Interface (API) system.
In principle it makes MySQL databases available to virtually any programming language.
MySQL publishes connectors for PHP, Java, .NET and ODBC, and a C/C++ API. Connector/NET opens MySQL to any .NET language including Visual Basic and C#. Connector/ODBC opens MySQL to Microsoft Access and any other language that can talk to ODBC.

Third parties have published connectors for Perl, Python, Ruby, Delphi, Kylix, Eiffel and more.

A MySQL API is a collection of recipes for executing MySQL commands in a particular programming language or environment. In most APIs, you will find all the DDL and DML functionality you need—functions to create, drop and use databases, tables and so on, plus functions to select, insert, update and delete.

We return to the importance of the specification and its Use Cases. The more you design the solution at the Use Case level, the less crucial the particular programming language you are using. Much of the database programming part of the job becomes syntax lookup.

A MySQL-enabled application tends to do four basic things again and again:

• connect,

• issue DML or occasionally DDL to retrieve and/or store data,

• update the user interface (UI) accordingly,

• disconnect.

The application should encapsulate Use Cases, and partition the presentation layer from the data management layer. Development environments like Java and .NET encourage such structure. Scripting languages like Perl and PHP do not; you are solely responsible for encapsulating code in routines for each Use Case, then breaking out smaller units of work into connect, query, UI and disconnect sub modules.

Why these APIs?

No book has enough pages to cover all language interfaces to MYSQL.

Judging by the fairly steady rate at which new questions are posted to MySQL help forums, the PHP API is used three times as much as .NET or ODBC, four times as much as Java, six times as much as the C API and 16 times as much as Perl.

Java

Java is everywhere. The fact that IBM and Sun champion Java ensures Java's continuing penetration into enterprise computing, most of which currently runs on Windows. Part of IBM’s pitch is the move from Windows to Linux, but from our perspective that is almost irrelevant. Whether or not an organization converts to Linux, Java will be there. In the emerging world of web services, Java is a major player. The MYSQL Bridge to this world is Connector/J.

ODBC

Connector/ODBC (formerly MYODBC) enables any ODBC-compliant language to connect to MYSQL. A single API works for Access, Visual Basic and C++, Delphi, PowerBuilder – virtually any Windows development environment.

From the Linux perspective, understanding Connector/ODBC will enable you to address databases trapped in SQL Server or Access or Approach or QuickBooks, and to deal with their data.

Perl and PHP

The majority of MYSQL installations are part of a LAMP (Linux-Apache-MYSQL-Perl/PHP) setup.

LAMP is light enough on its feet to float like a butterfly and sting like a bee. A website with database smarts and user validation can go from idea to deployment in hours rather than weeks, months or years.

There is a database interface plugin (DBI), and a MYSQL driver plugin (DBD-mysql). Connector/PHP connects PHP with MySQL.

No comments: