Deploying on OCI with the starter equipment – half 5 (connecting to the database II)

In half 4 of our sequence on the OCI Hackathon Starter Equipment, we noticed how to connect with the deployed MySQL HeatWave occasion from our purchasers (MySQL Shell, MySQL Shell for VS Code, and Cloud Shell). On this put up, we’ll see tips on how to join from an utility utilizing a connector.eibccbnkjudbffhigvdnjiudbhertlevjevtuddruvhd

We’ll cowl connections utilizing Python, Java, NodeJS, and PHP.

Conditions

As traditional, we must always have a devoted database consumer for any utility we write. We’ll then create a schema and a check consumer.

Within the MySQL Consumer of our selection, we do:

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

SQL> create consumer starterkit_user recognized 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 set up mysql-connector-python3

Please be aware that if you’re utilizing the At all times Free Form VM.Customary.E2.1.Micro, it is likely to be difficult to execute dnf.

Let’s strive to connect with the DB Systrem HeatWave Occasion utilizing the basic protocol with Python:

import mysql.connector

cnx = mysql.connector.join(consumer="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 check.py
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

MySQL HeatWave on OCI is the one MySQL-as-a-Service providing that additionally offers entry to the X Protocol.

To make use of the X Protocol in Python, we additionally want to put in the connector:

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

As we don’t have knowledge but, we received’t play with collections and paperwork; we’ll simply run a SQL assertion utilizing the X session:

import mysqlx

session = mysqlx.get_session(consumer="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 set up mysql-connector-j

We’ll run the identical instance as we did for Python.

Basic MySQL Protocol

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

public class Check {

    public static void most important(String[] args) {
        Connection conn = null;
        Assertion stmt = null;
        ResultSet rs = null;

        strive {
            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 Check.java
[opc@webserver]$ java -cp .:/usr/share/java/mysql-connector-java.jar Check 
MySQL Enterprise - Cloud 9.5.1-cloud

X Protocol

We will additionally use the X Protocol with Java. We additionally want to put in protobuf-java to make use of 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 most important(String[] args) {
        strive {
            // 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

Sadly, the Node.js connector is just not out there for the most recent model of MySQL. However the 8.0.35 is working effective and it additionally helps the MySQL X Protocol.

Basic MySQL Protocol

For the basic MySQL Protocol, we advocate utilizing mysql2 module for Node.js. Let’s put together our minimal surroundings:

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

And that is the code of check.js:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: '10.0.1.19',
  consumer: '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 = outcomes[0]['@@version_comment'];
    console.log(`${versionComment} ${model}`);
    
    connection.finish();
  });
});

Let’s check it:

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

X Protocol

Let’s set up the MySQL Connector/Node.js, and check it:

[opc@webserver nodejs]$ npm set up @mysql/xdevapi
[opc@webserver nodejs]$ npm listing
nodejs@1.0.0 /dwelling/opc/nodejs
├── @mysql/xdevapi@8.0.35
└── mysql2@3.15.3

Now, that is the code utilizing the X Protocol:

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

async perform most important() {  
  const config = {
    host: '10.0.1.19',
    port: 33060,         // default X-Protocol port
    consumer: '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 consequence returned');
    }
  } catch (err) {
    console.error('Question error:', err);
  } lastly {
    await session.shut();
  }
}

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

And as soon as once more, we are able to confirm that it’s working as anticipated and utilizing the X Protocol with the MySQL HeatWave DB Occasion in OCI:

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

And our code to make use of 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 most important(String[] args) {
        strive {
            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 at last, if you wish to strive to connect with your MySQL HeatWave occasion utilizing PHP, that is tips on how to do it utilizing Remi’s repo, as we wish to use PHP 8.4:

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

Basic MySQL Protocol

We begin with the basic MySQL protocol, that is the code of check.php:

Let’s strive it:

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

X Protocol

And, as with the opposite languages, let’s now additionally use the X DevAPI.

Sadly, Remi’s repo doesn’t present the pecl-mysql-xdevapi bundle for PHP 8.4 but, because it appears to fail constructing it:

[opc@webserver php]$ sudo dnf search xdevapi
Final metadata expiration examine: 0:10:30 in the past on Mon 01 Dec 2025 06:49:40 PM GMT.
================================== Identify Matched: xdevapi ==================================
php74-php-pecl-mysql-xdevapi.aarch64 : MySQL database entry capabilities
php80-php-pecl-mysql-xdevapi.aarch64 : MySQL database entry capabilities
php81-php-pecl-mysql-xdevapi.aarch64 : MySQL database entry capabilities
php82-php-pecl-mysql-xdevapi.aarch64 : MySQL database entry capabilities
php83-php-pecl-mysql-xdevapi.aarch64 : MySQL database entry capabilities

So let’s set up the most recent out there model:

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

We will check that our earlier code, utilizing the basic protocol, continues to be working with PHP 8.3:

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

It’s time to jot down our script:

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

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

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

    echo $versionComment . " " . $model . "n";

    $session->shut();

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

And now let’s strive our final code utilizing PHP 8.3 and the X DevAPI:

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

Conclusion

We noticed on this article how to connect with and use our MySQL HeatWave DB Occasion on OCI from the deployed compute occasion utilizing totally different programming languages.

Within the following article, we’ll begin utilizing GenAI in OCI.