Data … as usual

All things about data by Laurent Leturgez

Monthly Archives: May 2019

Configure SQLDeveloper for Postgres, MySQL/Mariadb, SQL Server, and teradata

In my job, I usually work with other rdbms like postgres, sql server, teradata etc. But I usually use SQL Developer as an IDE.

In this post I will explain how to configure SQL Developer if you want to connect and use it with other rdbms than Oracle:

sqldeveloper

 

Step 1 … Download and Install SQL Developer

SQL Developer is available for download at this URL: https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Download the version you want and install it (I don’t explain how to proceed here … I’m sure you’re smart enough to do that alone ! 😉 )

Once downloaded, installed, then executed, you can add new connection but only to Oracle databases:

 

Step 2 … Download libraries

To be configured for other rdbms, SQL Developer need extra libraries, and more precisely extra jdbc libraries for the required rdbms.

Depending on the target rdbms, you will have to download these libraries :

Postgres

Download the jdbc driver enclosed in a jar file for postgres. Jar files are available at this URL : https://jdbc.postgresql.org/download.html

I recommend to download the 42.2.5 JDBC 42 driver, but download the driver you need depending on your PG Server version, your JDBC driver version etc.

MySQL / MariaDB

Configuring SQL Developer to be able to connect to mysql or mariadb server need mysql Connector / J available at this URL: https://dev.mysql.com/downloads/connector/j/

Download the “Platform Independant” connector, and extract the jar file included in the archive/zip file. When writing this blog post the last version was mysql-connector-java-8.0.16, and the required jar file was mysql-connector-java-8.0.16.jar.

SQL Server / Sybase

Download the jTDS jar file at this URL: https://sourceforge.net/projects/jtds/files/, and extract the jar file included in the zip file. (jTDS is described here: http://jtds.sourceforge.net/).

Note: the jTDS is a bit older, and I didn’t use it to connect to recent version of MSSQL (including in the Azure Cloud). It will required some tests.

TeraData

To be able to connect a teradata database with SQL Developer, you need to download the jdbc driver for teradata. These drivers are available at this URL: https://downloads.teradata.com/download/connectivity/jdbc-driver. (It’s free but you will need an account to download it.).

Once downloaded, you will need both jars included in the zipfile. (terajdbc4.jar and tdgssconfig.jar).

Step 3 … Configure SQL Developer to use these third-party JDBC Drivers

First of all, you will have to put all the needed jars in a folder (somewhere in the sqldeveloper subfolders). In my case, I put them in the $SQLDEV_HOME/jdbc/lib directory.

Then, you’ll have to add the needed third party librairies in SQL Developer.

To do that, Menu “Tools”/”Preferences”, and select “Database” and then click on the “Third Party JDBC Drivers”.

Now, you have just to add new entries by selecting the needed jar files :

sqldeveloper

 

Step 4 … Create new connections from these third-party drivers

Now, when we create a new connection, the “Database Type” Menu contains more entries:

sqldeveloper

Once selected, the GUI changes a bit with the required fields to connect to the database type you chose.

 

That’s it for today 😉

creating a VM instance on the OCI with Python (prework)

In a previous post, I explained how to deal with python and the OCI (Oracle Cloud Infrastructure). In this previous post, I wrote a basic script to put and get some files stored into an object storage bucket.

But, with Python and OCI, you can also create some VM instances (and much more funny stuff).

In this article I will present the basics to do before creating a VM Instance (The VM instance creation process will be described in a next blog post).

Principles

As previously written, creating a resource in OCI with Python is almost always the same :


$ cat ~/.oci/config
[DEFAULT]
user=ocid1.user.oc1..aaaaaaaamcel7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
fingerprint=35:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
region=eu-frankfurt-1

  • Load it into your python program
import oci
config=oci.config.from_file()
  • Use the correct OCI Python SDK Client

In the Python SDK, you have various clients you will be able to use to deal with specific task. Each client is part of a service (Core, Compute, Database etc.)

For example:

In addition, in each service, you have some models that are used to configure your client or the resource you will create through the use this client. For example, when creating a subnet, we will use the VirtualNetworkClient object and specially the create_subnet function. But as parameters, we will give a model object for this subnet (this object will be configured with all the details of the subnet : CIDR, availability domain, tags etc.) … If it’s more understandable for you to read python code … we’ll see this is the next sections.

  • Ids Ids Ids … you will have to know all the required IDs (or OCID) for each container that will own the resource you will create.

The main OCIDs to get are:

    • the compartment OCID where you will create your VCN
    • The VCN OCID you will get once created

Of course, all the OCIDs required to configure your OCI client have to be known.. but already used in the first step.

Virtual Cloud Network (VCN) creation

To create a VCN, you will need various information. The main ones are :

  • The compartment OCID where your VCN will be created
  • A display name
  • and a CIDR block for the VCN

Once you got this, you can execute the VCN Creation (you can notice that we used a model to describe our VCN):

import oci
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
vcn_name = 'PREMISEO-VCN'
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
cidr_block='10.10.10.0/24'

result = virtual_network.create_vcn(
        oci.core.models.CreateVcnDetails(
            cidr_block=cidr_block,
            display_name=vcn_name,
            compartment_id=my_compartment_id
        )
    )

Once the command is executed, the process of creating the VCN is executed asynchronously.
So, if you want to be acknowledged when the process is terminated, it’s better to use the wait_until function from the oci package:

v_response = oci.wait_until(
        virtual_network,
        virtual_network.get_vcn(result.data.id),
        'lifecycle_state',
        'AVAILABLE'
    )
print('The VCN has been created with ID: ',v_response.data.id)

If you are curious, you can print the v_response.data associative array content and get all the details of your VCN.

Finally, note the returned OCID that identifies your VCN (You can get it from the OCI web console). It’s the only identifier for your VCN, if your rerun the same block code, you will create another VCN with the same properties (name, CIDR etc). The only difference will be the OCID.

Subnet Creation

Once you created a VCN, you can now create a subnet inside this VCN.

It’s the same principle as creating a VCN, the only difference is that you will need to give the VCN OCID and the compartment OCID, because these components host the subnet you will created.

Subnet creation is done through the VirtualNetworkClient object, like this:


config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
my_vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"
sub_cidr_block='10.10.10.0/24'
my_availability_domain='EUUz:EU-FRANKFURT-1-AD-1'
subnet_name='PREMISEO-VCN-subnet1'

result = virtual_network.create_subnet(
        oci.core.models.CreateSubnetDetails(
            compartment_id=my_compartment_id,
            availability_domain=my_availability_domain,
            display_name=subnet_name,
            vcn_id=my_vcn_id,
            cidr_block=sub_cidr_block
        )
    )
# Same thing here, we are waiting for the response property "lifecycle_state" to be set on "AVAILABLE". 
# This will keep us informed the resource is now available. 
s_response = oci.wait_until(
    virtual_network,
    virtual_network.get_subnet(result.data.id),
    'lifecycle_state',
    'AVAILABLE'
)
    print("The subnet has been created with ID: ", s_response.data.id)

Internet Gateway (IG) Creation

The last step to do before creating new VM instance is optional but needed when you want to connect your VM on the internet. It’s to create an Internet Gateway on your VCN and add a network rule to target this gateway.

Gateway creation

The IG creation follows the same process as previously used for VCN and subnet creation.

To create it, you will need some OCIDs like compartment OCID and VCN OCID, it’s done though the VirtualNetworkClient object, and you will a display name to configure it with the use of IntenetGateway model.


internet_gateway_name='premiseo_GW'
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"

result = virtual_network.create_internet_gateway(
        oci.core.models.CreateInternetGatewayDetails(
            display_name=internet_gateway_name,
            compartment_id=my_compartment_id,
            is_enabled=True,
            vcn_id=vcn.id
        )
    )
IG_response = oci.wait_until(
    virtual_network,
    virtual_network.get_internet_gateway(result.data.id),
    'lifecycle_state',
    'AVAILABLE'
)
print("Created internet gateway: ", IG_response.data.id)

Network rule creation and association with IG

Create a network rule is done through a model called RouteRule made of two properties :

  • the CIDR Block associated with this rule
  • the Object OCID associated with this rule (Here the Internet Gateway OCID)

After creating the RouteRule model, we need to get the route table and append the new rule to it, and then update the new route table.

This is done by the following code block:


internet_gateway_name='premiseo_GW'
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"

# #### Routing rules retrieving
# First, we get the VCN Object from the vcn OCID
vcn=virtual_network_client.get_vcn(vcn_id=vcn_id)
# Then the route table (as response Object)
route_table_resp= virtual_network_client.get_route_table(vcn.data.default_route_table_id)
# And Finally the route rules
route_rules = route_table_resp.data.route_rules

# Another way to proceed ... a one line command but a bit more complex to read 😉
route_rules = virtual_network_client.get_route_table(virtual_network_client.get_vcn(vcn_id=vcn_id).data.default_route_table_id).data.route_rules

# #### Now we append the new network rule to the previously route_rules object
route_rules.append(
        oci.core.models.RouteRule(
            cidr_block='0.0.0.0/0',
            network_entity_id=IG_response.data.id
        )
    )

# #### Finally we update the VCN with the new routing table
virtual_network.update_route_table(
        vcn.default_route_table_id,
        oci.core.models.UpdateRouteTableDetails(route_rules=route_rules)
    )

get_route_table_response = oci.wait_until(
    virtual_network,
    virtual_network.get_route_table(vcn.default_route_table_id),
    'lifecycle_state',
    'AVAILABLE'
)

Ok, now we have an environment ready to host our VM instance. In a next post, I will describe how to create a VM instance on the OCI by using Python SDK.

That’s it for today 🙂