Auto Draft

In part 4 of our series on the OCI Hackathon Starter Kit, we saw how to connect to the deployed MySQL HeatWave instance from our clients (MySQL Shell, MySQL Shell for VS Code, and Cloud Shell). In this post, we will see how to connect from an application using a connector.eibccbnkjudbffhigvdnjiudbhertlevjevtuddruvhd

We will cover connections using Python, Java, NodeJS, and PHP.

Prerequisites

As usual, we should have a dedicated database user for any application we write. We will then create a schema and a test user.

In the MySQL Client of our choice, we do:

SQL> create database starterkit;
Query OK, 1 row affected (0.0054 sec)

SQL> create user starterkit_user identified by 'St4rt3rK4t[[';
Query OK, 0 rows affected (0.0118 sec)

SQL> grant all privileges on starterkit.* to starterkit_user;
Query OK, 0 rows affected (0.0035 sec)

Python

We saw that Python 3.9 is installed by default. But for the MySQL Connector/Python, we need to use Python 3.12.

Classic MySQL Protocol

The classic MySQL protocol is the most widely used. It listens on port 3306 by default and, of course, also on OCI.

[opc@webserver ~]$ sudo dnf install mysql-connector-python3

Please note that if you are using the Always Free Shape VM.Standard.E2.1.Micro, it might be complicated to execute dnf.

Let’s try to connect to the DB Systrem HeatWave Instance using the classic protocol with Python:

import mysql.connector

cnx = mysql.connector.connect(user='starterkit_user',
                              password='St4rt3rK4t[[',
                              host='10.0.1.19',
                              database='starterkit')

query = "select version(), @@version_comment"
cursor = cnx.cursor()
cursor.execute(query)
for (ver, ver_comm) in cursor:
    print("{} {}".format(ver_comm, ver))

cursor.close()
cnx.close()

And we can verify it:

[opc@webserver ~]$ python3.12 test.py
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

MySQL HeatWave on OCI is the only MySQL-as-a-Service offering that also provides access to the X Protocol.

To use the X Protocol in Python, we also need to install the connector:

[opc@webserver ~]$ sudo dnf install mysqlx-connector-python

As we don’t have data yet, we won’t play with collections and documents; we will just run a SQL statement using the X session:

import mysqlx

session = mysqlx.get_session(user='starterkit_user',
                             password='St4rt3rK4t[[',
                             host='10.0.1.19',
                             port=33060)

query = "select version(), @@version_comment"
res = session.sql(query).execute()

rows = res.fetch_all()
for (ver, ver_comm) in rows:
    print("{} {}".format(ver_comm, ver))

session.close()

Java

For Java, we need to install MySQL Connector/J:

[opc@webserver java]$ sudo dnf install mysql-connector-j

We will run the same example as we did for Python.

Classic MySQL Protocol

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class Test {

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(
                "jdbc:mysql://10.0.1.19:3306/starterkit",
                "starterkit_user",
                "St4rt3rK4t[["
            );

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select version(), @@version_comment");

            while (rs.next()) {
                String version = rs.getString(1);
                String versionComment = rs.getString(2);
                System.out.println(versionComment + " " + version);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {            
            try {
                if (rs != null) rs.close();
            } catch (Exception ignore) {}
            try {
                if (stmt != null) stmt.close();
            } catch (Exception ignore) {}
            try {
                if (conn != null) conn.close();
            } catch (Exception ignore) {}
        }
    }
}

Let’s compile it and execute it:

[opc@webserver]$ javac -cp /usr/share/java/mysql-connector-java.jar Test.java
[opc@webserver]$ java -cp .:/usr/share/java/mysql-connector-java.jar Test 
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

We can also use the X Protocol with Java. We also need to install protobuf-java to use the X DevAPI.

[opc@webserver java]$ wget \
https://repo1.maven.org/maven2/com/google/protobuf/protobuf-java/4.33.1/protobuf-java-4.33.1.ja

Let’s code TestX.java:

import com.mysql.cj.xdevapi.Session;
import com.mysql.cj.xdevapi.SessionFactory;
import com.mysql.cj.xdevapi.SqlResult;
import com.mysql.cj.xdevapi.Row;

public class TestX {

    public static void main(String[] args) {
        try {
            // Create a session (X Protocol)
            SessionFactory sf = new SessionFactory();
            Session session = sf.getSession(
                "mysqlx://starterkit_user:St4rt3rK4t[[@10.0.1.19:33060/starterkit"
            );

            // Execute SQL using X DevAPI
            SqlResult result = session.sql("SELECT VERSION(), @@version_comment").execute();
            Row row = result.fetchOne();

            if (row != null) {
                String version = row.getString(0);
                String versionComment = row.getString(1);
                System.out.println(versionComment + " " + version);
            } else {
                System.out.println("No results returned");
            }

            session.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

And we can now test it:

[opc@webserver java]$ java -cp .:/usr/share/java/mysql-connector-java.jar:./protobuf-java-4.33.1.jar TestX 
MySQL Enterprise - Cloud 9.5.1-cloud

NodeJS

Unfortunately, the Node.js connector is not available for the latest version of MySQL. But the 8.0.35 is working fine and it also supports the MySQL X Protocol.

Classic MySQL Protocol

For the classic MySQL Protocol, we recommend using mysql2 module for Node.js. Let’s prepare our minimal environment:

[opc@webserver ~]$ mkdir nodejs && cd nodejs
[opc@webserver nodejs]$ npm init -y
[opc@webserver nodejs]$ npm install mysql2

And this is the code of test.js:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: '10.0.1.19',
  user: 'starterkit_user',
  password: 'St4rt3rK4t[[',
  database: 'starterkit'
});

connection.connect((err) => {
  if (err) {
    console.error('Connection error:', err.stack);
    return;
  }

  connection.query('SELECT VERSION(), @@version_comment',
    (err, results) => {
    if (err) {
      console.error('Query error:', err);
      return;
    }

    const version = results[0]['VERSION()'];
    const versionComment = results[0]['@@version_comment'];
    console.log(`${versionComment} ${version}`);
    
    connection.end();
  });
});

Let’s test it:

[opc@webserver nodejs]$ node test.js 
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

Let’s install the MySQL Connector/Node.js, and test it:

[opc@webserver nodejs]$ npm install @mysql/xdevapi
[opc@webserver nodejs]$ npm list
nodejs@1.0.0 /home/opc/nodejs
├── @mysql/xdevapi@8.0.35
└── mysql2@3.15.3

Now, this is the code using the X Protocol:

const mysqlx = require('@mysql/xdevapi');

async function main() {  
  const config = {
    host: '10.0.1.19',
    port: 33060,         // default X-Protocol port
    user: 'starterkit_user',
    password: 'St4rt3rK4t[[',
    schema: 'starterkit'
  };

  const session = await mysqlx.getSession(config);

  try {
    const result = await session
      .sql('SELECT VERSION(), @@version_comment')
      .execute();

    const row = result.fetchOne();
    if (row) {
      console.log(row[1] + ' ' + row[0]);  
    } else {
      console.log('No result returned');
    }
  } catch (err) {
    console.error('Query error:', err);
  } finally {
    await session.close();
  }
}

main().catch(err => console.error('Connection error:', err));

And once again, we can verify that it’s running as expected and using the X Protocol with the MySQL HeatWave DB Instance in OCI:

[opc@webserver nodejs]$ node testx.js 
MySQL Enterprise - Cloud 9.5.1-cloud

And our code to use the X DevAPI:

import com.mysql.cj.xdevapi.Session;
import com.mysql.cj.xdevapi.SessionFactory;
import com.mysql.cj.xdevapi.SqlResult;
import com.mysql.cj.xdevapi.Row;

public class TestX {

    public static void main(String[] args) {
        try {
            SessionFactory sf = new SessionFactory();
            Session session = sf.getSession(
                "mysqlx://starterkit_user:St4rt3rK4t[[@10.0.1.19:33060/starterkit"
            );

            SqlResult result = session.sql("SELECT VERSION(), @@version_comment").execute();
            Row row = result.fetchOne();

            if (row != null) {
                String version = row.getString(0);
                String versionComment = row.getString(1);
                System.out.println(versionComment + " " + version);
            } else {
                System.out.println("No results returned");
            }
            session.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Let’s compile it and test it:

[opc@webserver java]$ javac -cp /usr/share/java/mysql-connector-java.jar TestX.java 
[opc@webserver java]$ java -cp \
.:/usr/share/java/mysql-connector-java.jar:./protobuf-java-4.33.1.jar \
TestX 
MySQL Enterprise - Cloud 9.5.1-cloud

PHP

And finally, if you want to try to connect to your MySQL HeatWave instance using PHP, this is how to do it using Remi’s repo, as we want to use PHP 8.4:

[opc@webserver ~]$ dnf install https://rpms.remirepo.net/enterprise/remi-release-9.rpm
[opc@webserver ~]$ sudo dnf module install php:remi-8.4
[opc@webserver ~]$ sudo dnf install php-mysqlnd
[opc@webserver ~]$ mkdir php && cd php

Classic MySQL Protocol

We start with the classic MySQL protocol, this is the code of test.php:

<?php

$host = "10.0.1.19";
$user = "starterkit_user";
$password = "St4rt3rK4t[[";
$database = "starterkit";

$conn = mysqli_connect($host, $user, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = "SELECT VERSION(), @@version_comment";
$result = mysqli_query($conn, $query);

if ($row = mysqli_fetch_row($result)) {
    $version = $row[0];
    $versionComment = $row[1];
    echo $versionComment . " " . $version . "\n";
} else {
    echo "No result returned\n";
}

mysqli_free_result($result);
mysqli_close($conn);

Let’s try it:

[opc@webserver php]$ php test.php 
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

And, as with the other languages, let’s now also use the X DevAPI.

Unfortunately, Remi’s repo doesn’t provide the pecl-mysql-xdevapi package for PHP 8.4 yet, as it seems to fail building it:

[opc@webserver php]$ sudo dnf search xdevapi
Last metadata expiration check: 0:10:30 ago on Mon 01 Dec 2025 06:49:40 PM GMT.
================================== Name Matched: xdevapi ==================================
php74-php-pecl-mysql-xdevapi.aarch64 : MySQL database access functions
php80-php-pecl-mysql-xdevapi.aarch64 : MySQL database access functions
php81-php-pecl-mysql-xdevapi.aarch64 : MySQL database access functions
php82-php-pecl-mysql-xdevapi.aarch64 : MySQL database access functions
php83-php-pecl-mysql-xdevapi.aarch64 : MySQL database access functions

So let’s install the latest available version:

[opc@webserver ~]$ sudo dnf install -y php83 php83-php-pecl-mysql-xdevapi

We can test that our previous code, using the classic protocol, is still working with PHP 8.3:

[opc@webserver php]$ php83 test.php 
MySQL Enterprise - Cloud 9.5.1-cloud

It’s time to write our script:

<?php

try {
    $session = mysql_xdevapi\getSession(
        "mysqlx://starterkit_user:St4rt3rK4t[[@10.0.1.19:33060/starterkit"
    );

    $sql = "SELECT VERSION() AS ver, @@version_comment AS ver_comment";

    $result = $session->sql($sql)->execute();
    $row = $result->fetchOne();

    if (!$row) {
        echo "No row returned from query.\n";
        $session->close();
        exit;
    }

    $version        = $row['ver']         ?? null;
    $versionComment = $row['ver_comment'] ?? null;

    echo $versionComment . " " . $version . "\n";

    $session->close();

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

And now let’s try our last code using PHP 8.3 and the X DevAPI:

[opc@webserver php]$ php83 testx.php 
MySQL Enterprise - Cloud 9.5.1-cloud

Conclusion

We saw in this article how to connect to and use our MySQL HeatWave DB Instance on OCI from the deployed compute instance using different programming languages.

In the following article, we will start using GenAI in OCI.

Deploying on OCI with the starter kit – part 5 (connecting to the database II)

Prerequisites

Python

Classic MySQL Protocol

X Protocol

Java

Classic MySQL Protocol

X Protocol

NodeJS

Classic MySQL Protocol

X Protocol

PHP

Classic MySQL Protocol

X Protocol

Conclusion

Frederic Descamps

MySQL Community Manager

MySQL & MySQL HeatWave: A Successful Journey Through Global AI Events in 2025

Deploying on OCI with the starter kit – part 5 (connecting to the database II)

Prerequisites

Python

Classic MySQL Protocol

X Protocol

Java

Classic MySQL Protocol

X Protocol

NodeJS

Classic MySQL Protocol

X Protocol

PHP

Classic MySQL Protocol

X Protocol

Conclusion

Authors

Frederic Descamps

MySQL Community Manager

MySQL & MySQL HeatWave: A Successful Journey Through Global AI Events in 2025