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
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')
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.
Got it!!!
I have to change URL to be
jdbc:sqlserver://localhost:1433;databaseName=AEONDW;
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(); } } } }