Use ODBC to develop applications - Lindorm - Alibaba Cloud Documentation Center

This topic describes how to use the Open Database Connectivity (ODBC) driver for MySQL to develop C or C++ applications.

Prerequisites

The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.
The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.

Procedure

Download and install the ODBC driver for MySQL from the MySQL official website.
Install the connection component unixODBC. The following command provides an example on how to install unixODBC in Linux:
```
yum install unixODBC-devel
```

Modify the MySQL dependency in the odbcinst.ini configuration file.

[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc8a.so
Setup		= /usr/lib/libmyodbc8w.so
Driver64	= /usr/lib64/libmyodbc8a.so
Setup64		= /usr/lib64/libmyodbc8w.so
FileUsage	= 1

The following table describes the parameters that you can configure in the file.

Parameter	Description
Description	The description of the MySQL dependency. You can specify a custom description.
Driver	The ODBC driver. Set this parameter to the path in which the ODBC driver is stored.
Setup	The installer of the ODBC driver. Set this parameter to the path in which the installer of the ODBC driver is stored.
Driver64	The 64-bit ODBC driver. Set this parameter to the path in which the 64-bit ODBC driver is stored.
Setup64	The installer of the 64-bit ODBC driver. Set this parameter to the path in which the installer of the 64-bit ODBC driver is stored.
FileUsage	The default value of this parameter is 1. Do not modify this value.

Configure connection parameters.

ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)"DRIVER={MySQL};SERVER=ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com;PORT=33060;DATABASE=default;USER=user;PASSWORD=test", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

Parameters

Parameter	Description
DRIVER	The name of the ODBC driver for MySQL. Set this parameter to the MySQL dependency name configured in the odbcinst.ini file.
SERVER	The LindormTable endpoint for MySQL. For more information about how to obtain the endpoint, see View endpoints. Important If your application is deployed on an ECS instance, we recommend that you use a VPC to connect to the Lindorm instance to ensure higher security and lower network latency. If your application is deployed on a local server and needs to connect to the Lindorm instance over the Internet, you can perform the following steps to enable the Internet endpoint for the instance in the Lindorm console: In the left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. If you use a VPC to access the Lindorm instance, specify the LindormTable VPC endpoint for MySQL in the value of SERVER. If you use a VPC to access the Lindorm instance, specify the LindormTable Internet endpoint for MySQL in the value of SERVER.
PORT	The port used to access LindormTable by using MySQL. The value of this parameter is fixed to 33060.
DATABASE	The name of the database to which you want to connect. By default, your client is connected to a database named default.
USER	If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
PASSWORD

Establish a connection and use LindormTable SQL to perform operations in LindormTable. The following code block provides an example on how to use LindormTable SQL to query all databases:

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        printf("Connection established. \n");

        // Perform the query.
        SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
        SQLExecDirect(stmt, (SQLCHAR*)"show databases", SQL_NTS);

        // Obtain query results.
        SQLCHAR result[50];
        while (SQLFetch(stmt) == SQL_SUCCESS) {
            SQLGetData(stmt, 1, SQL_C_CHAR, result, sizeof(result), NULL);
            printf("database: %s\n", result);
        } 
        // Release resources.
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        SQLDisconnect(dbc);
    }
    else {
        SQLCHAR sqlState[6];
        SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH];
        SQLINTEGER nativeError;
        SQLSMALLINT actualMsgLen;

        SQLGetDiagRec(SQL_HANDLE_DBC, dbc, 1, sqlState, &nativeError, msg, sizeof(msg), &actualMsgLen);
        printf("Connection failed: %s\n", msg);
    }

    // Release resources.
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;

Sample code

The following code provides a complete example on how to connect to and use LindormTable by using the ODBC driver:

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>

int main() {
    // Define connection information.
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret;

    // Initialize the environment.
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

    // Establish a connection.
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    SQLSetConnectAttr(dbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, SQL_IS_INTEGER);
    // Set lindorm_mysql_url to the LindormTable endpoint for MySQL, database to the database that you want to connect, lindorm_user to the username used to connect to LindormTable, and lindorm_password to the password used to connect to LindormTable. 
    ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)"DRIVER={MySQL};SERVER=ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com;PORT=33060;DATABASE=default;USER=user;PASSWORD=test", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        printf("Connection established. \n");

        // Perform the query.
        SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
        SQLExecDirect(stmt, (SQLCHAR*)"show databases", SQL_NTS);

        // Obtain query results.
        SQLCHAR result[50];
        while (SQLFetch(stmt) == SQL_SUCCESS) {
            SQLGetData(stmt, 1, SQL_C_CHAR, result, sizeof(result), NULL);
            printf("database: %s\n", result);
        } 
        // Release resources.
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        SQLDisconnect(dbc);
    }
    else {
        SQLCHAR sqlState[6];
        SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH];
        SQLINTEGER nativeError;
        SQLSMALLINT actualMsgLen;

        SQLGetDiagRec(SQL_HANDLE_DBC, dbc, 1, sqlState, &nativeError, msg, sizeof(msg), &actualMsgLen);
        printf("Connection failed: %s\n", msg);
    }

    // Release resources.
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}

If the current instance contains only a database named default, the following result is returned:

Connection established.
database: default
database: information_schema