03- MySQL Connectors API

MySQL Connectors/API provides connectivity to the MySQL database server for client programs in different formats for different operating systems. APIs provide low-level not much secure access to the MySQL protocol and resources, some over the TCP/IP and some over the secure connections like SSH and some using the SSL certificates. Both APIs and the available connectorsallows you to connect and execute MySQL statements from another language or environment, including ODBC, Java (JDBC), Perl, Python, PHP, Ruby, and native C and installed MySQL instances.

MySQL provides connectors with naming convention like Connector/Language_name. And in some language APIs are used to connect the MySQL like C/C++. libmysqlclient and libmysqld are embedded in the C library for the MySQL support and connection.

There are various third party APIs that gives an interface to MySQL database from specific application languages. MySQL doesn’t provide any official support or solutions to these third party APIs and the performance would be low in these third party APIs as the data transfer between the Third party API and native languages. These APIs are developed from two methods usinglibmysqlclient or the native drivers.

Below is the table containing recent updated connector with version:

Connector

Connector version

MySQL Server version

Connector/C

6.1.0 GA

5.6, 5.5, 5.1, 5.0, 4.1

Connector/C++

1.0.5 GA

5.6, 5.5, 5.1

Connector/J

5.1.8

5.6, 5.5, 5.1, 5.0, 4.1

Connector/Net

6.5

5.6, 5.5, 5.1, 5.0

Connector/Net

6.4

5.6, 5.5, 5.1, 5.0

Connector/Net

6.3

5.6, 5.5, 5.1, 5.0

Connector/Net

6.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.1 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.0 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

5.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

1.0 (No longer supported)

5.0, 4.0

Connector/ODBC

5.1

5.6, 5.5, 5.1, 5.0, 4.1.1+

Connector/ODBC

3.51 (Unicode not supported)

5.6, 5.5, 5.1, 5.0, 4.1

Table 3.1 MySQL Connectors.

Example of MySQL C API Implementation:

Basic steps of C/C++Programs that are using MySQL C/C++ API's

  1. All programs must include <mysql/mysql.h>but always be in the lastinclude step.
  2. Declare only one variable of MySQL type. There should not be any other variable of MySQL type.
  3. After declaration initialize that MySQL variable with mysql_init() function.
  4. Use the function mysql_options() to load any other mysql functions required.Don’t call unnecessary, only when needed.Although one can call this function many times when require to load additional options. NOTE- Callthis function always beforecalling mysql_real_connect() function and after mysql_init() function.
  5. Now connect the code by calling mysql_real_connect() function.
  6. Now implement the coding logics and MySQL API's.
  7. At last close the MySQL type variable to save resources.

Actual Coding Part:

#include <mysql.h> or <mysql>
returnTypefunctionName (PARAM)
{
   MYSQL mysqlName;
   mysql_init(&mysql);
   mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);/*call this function when required otherwise don’t to optimize resource utilization*/
   mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"brijskate_gold_minig_tricks”) /* call this function when required otherwise don’t to optimize resource utilization*/
   mysql_real_connect();
   /* At last call other required API’s*/
   mysql_close(&mysql); /* Finally close the connection variable */
}

Manual MySQL Connection for querying the database

This is the sample output while connecting on MySQL server from the command prompt in Linux/Unix based systems:

[root@localhost]#mysql-u root -p
Enter password:******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 102429
Server version: 5.5.27-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help.Type '\c' to clear the current input statement.
mysql>

Now you can run any command on this prompt, like:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+

2 rows in set (0.00 sec)

mysql> create database [databasename];
Query OK, 1 row affected (0.38 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)
mysql> exit
Bye

This is the command line tool or interface for connecting MySQL but there are various GUI tools available in the market both open source or paid. Example of these tools are:

MySQL Workbench (Free provided by Oracle Inc.)

Navicat (Paid tool provided by PremiumSoft)

Sequel Pro (Free provided by Sequel Pro Project under GPL)

phpMyAdmin (Free web based tool provided by phpmyadmin project under GPL)

SqlWave (Paid Provided by Nerocode)

dbForge Studio (Paid provided by devart)

Toad for MySQL (Free Provided by Quest)

MyDB Studio (Free provided by H2LSoft, Inc)

SQLYog (Paid provided by Webyog )

Connecting MySQL using PHP

There is a function in PHP named mysql_connect that allows the connection of PHP language with MySQL database.Mysql_connect() opens a non-persistent connection for MySQL. This function has various parameters defined as:

Parameter

Detail of Parameters

server

This is an optional parameter. It specifiesphp that which mysql server needs to connect to. In some cases you need to specify the port number where your database server i.e MySQL server is not running in the default port 3306. For example if your MySQL server is running on port 3307 then you need to pass “localhost:3307”

user

This is the username for MySQL database to connect to. This user must exist on the MySQL server database to create a successful connection. If you leave bank then there must exists a user with blank name “”.

password

This is the password of the user defined in the user parameter. If leave blank means there is no password set for specified user.

newconnectionlink

If the PHP code tries to create new connection via mysql_connect() then it looks any open connection with that link, if it exists then it will not open another connection rather use the previous one from the connection pool.

clientflagsymbol

This is an optional parameter , it is formed by combining the following parameters:

  1. MYSQL_CLIENT_SSL – If connection use SSL encryption
  2. MYSQL_CLIENT_COMPRESS -  If connection use compression protocol
  3. MYSQL_CLIENT_IGNORE_SPACE –It allow the spaces after every function names in the code.
  4. MYSQL_CLIENT_INTERACTIVE – Itallows the interactive timeout seconds (connection persistent time) before closing the connection automatically.

Its opposite function named mysql_close that closes/end the MySQL connection of the same session.

Example code to connect PHP to MySQL:

<html>
  <head>
    <title>Example of MySQL/PHP connection:</title>
  </head>
  <body>
   <?php
      $dbhostname = '192.168.2.13:3311';
      $dbusername = 'mysq_user123';
      $dbpassword = 'mysql_password123';
            $conn1 = mysql_connect($dbhostname,$dbusername,$dbpassword);
            if (!$con)
              {
            die('Could not connect to MySQL Server, check connection parameters: ' . mysql_error());
              }
            echo ‘Successfully Connected.’ ;
            mysql_close($conn1);
    ?>
  </body>
</html>

There is another function called mysql_pconnect like mysql_connect, the only difference is that it creates persistent connection unlike mysql_connect().

Each Language has its own set of function related to MySQL database for opening and closing the connection.

Like us on Facebook