How to connect external database (Netezza)?

0
Hi, I’m a newbie of Mendix and I have only a basic in web programming (ASP.NET). My first project is to create a Data Dictionary to describe/explain Tables, Fields Definition. My database is Netezza, MSSQL, MySQL. And I want to select structures information (including TableName, FieldName, FieldLength, Type, ...). And download this information into Internal MenDix Database then I will use MenDix web page to add more field definitions, example, … So, could someone guide me how  to Import external data into Internal Mendix database by Batch Job?
asked
5 answers
1

Have a look at the database connector module from the appstore:

https://appstore.home.mendix.com/link/app/2888/Mendix/Database-Connector

This allows you to execute queries on a remote database, see documentation here: 

https://docs.mendix.com/appstore/connectors/database-connector

Getting table and column information for MSSQL could be done like below:

SELECT
  	TABLE_NAME,
    COLUMN_NAME
FROM
  	INFORMATION_SCHEMA.COLUMNS

 

answered
0

I following guide to : https://www.youtube.com/watch?v=eJVFyDONU9Y

but still found error of “Failed to get driver instance for jdbc”

 

com.mendix.webui.WebUIException: Exception while retrieving data for a widget 'MyFirstModule.Course_Overview.listView5' on page 'MyFirstModule.Course_Overview'
    at com.mendix.webui.actions.client.RetrieveAction.$anonfun$apply$3(RetrieveAction.scala:69)

Caused by: com.mendix.modules.microflowengine.MicroflowException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.RuntimeException: Failed to get driver instance for jdbcUrl=jdbc:sqlserver//localhost;instanceName=MSSQLSERVER;database=AEONDW;
    at MyFirstModule.Course_Show (JavaAction : 'Execute query')

 

answered
0

Now I already tested by creating a simple Java program. It can retrieve data from MSSQL2014 properly.

But still don’t know what is the step that I missing in Mendix.

  1. Copy .jar file to userlib directory of Mendix project. (I have 3 versions of jar files What version should be support with Datbase Connector of Mendix? mssql-jdbc-8.2.2.jre8.jar,mssql-jdbc-8.2.2.jre11.jar, mssql-jdbc-8.2.2.jre13.jar

 

answered
0

Got it!!!

I have to change URL to be

jdbc:sqlserver://localhost:1433;databaseName=AEONDW;
answered
0

Now I stuck at Netezza JDBC. 

I know how to connect Netezza JDBC using Java. But I don’t know how to connect using Mendix?

Mendix is more difficult to use due to lack of example information.

Does Mendix support to specify Class.forName("org.netezza.Driver") or not?

package com.taninu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectNetezza {
    public static void main(String[] args) {
        String server = "192.168.85.128";
        String port = "5480";
        String dbName = "AEONDW";
        String url = "jdbc:netezza://" + server + "/" + dbName;
        String user = "admin";
        String pwd = "password";
        String schema = "";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("org.netezza.Driver");
            System.out.println(" Connecting ... ");
            conn = DriverManager.getConnection(url, user, pwd);
            System.out.println(" Connected " + conn);
            String sql = "select * from D_CUSTOMER";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            System.out.println("Printing result...");
            int i = 0;
            while (rs.next()) {
                String strCUST_ID = rs.getString("CUST_ID");
                String strCUST_NAME = rs.getString("CUST_NAME");
                System.out.println("CUST_ID: " + strCUST_ID +
                        ", CUST_NAME: " + strCUST_NAME);
                i++;
            }
            if (i == 0) {
                System.out.println(" No data found");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (st != null)
                    st.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }
}
answered