Deploying on OCI with the starter package – 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 shoppers (MySQL Shell, MySQL Shell for VS Code, and Cloud Shell). On this publish, we’ll see how one can join from an software utilizing a connector.

We are going to cowl connections utilizing Python, Java, NodeJS, and PHP.

Conditions

As standard, we should always have a devoted database consumer for any software we write. We are going to then create a schema and a check consumer.

Within the MySQL Consumer of our alternative, 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, on OCI as well.

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

Please word that in case you are utilizing the At all times Free Form VM.Commonplace.E2.1.Micro, it is likely to be difficult to execute dnf.

Let’s attempt to connect with the DB System 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 are going to 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 Take a look at {

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

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

X Protocol

We are able to 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

We alter our code to have TestX.java like this:

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 primary(String[] args) {
        attempt {
            // 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 test it, using connector/J and protobuf:

[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 shouldn’t be accessible for the most recent model of MySQL. However the 8.0.35 is working wonderful because it helps the MySQL X Protocol.

Basic MySQL Protocol

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

[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 /residence/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 operate primary() {  
  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 outcome returned');
    }
  } catch (err) {
    console.error('Question error:', err);
  } lastly {
    await session.shut();
  }
}

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

And as soon as once more, we will 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 primary(String[] args) {
        attempt {
            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 eventually, if you wish to attempt to connect with your MySQL HeatWave occasion utilizing PHP, that is how one can do it utilizing Remi’s repo, as we wish to use PHP 8.4:

[opc@webserver ~]$ dnf set up https://rpms.remirepo.web/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 attempt it:

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

X Protocol

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

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 accessible model:

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

We are able to 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 query.n";
        $session->close();
        exit;
    }

    $version        = $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 attempt 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 completely different programming languages.

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