v8.0

Connect to Custom Provider

Wyn Enterprise enables you to integrate a wide range of data sources, including NoSQL databases, by connecting to custom native data sources. Using this you can pull data from various data sources using native queries. You can also create your own custom native data provider by following the instructions listed in the Guide for Provider Developer section of this article.

The Custom Native-Query Data provider allows you to query the data directly from specific data sources. This custom provider can be used in the following scenarios:

With Direct Dataset: The dataset can be directly queried and used in:
1. Dashboards
2. Reports
3. Referencing by cached models
4. Referencing other cached datasets
5. Referencing other direct datasets
With Cached Dataset: The dataset can be pre-processed and stored for repeated use in:
1. Dashboards
2. Reports
3. Referencing by cached models
4. Referencing other cached datasets
5. Referencing other direct datasets
With Native Dataset: This dataset can be used in its native format within the provider.

Note: Direct Query Models are not supported by Custom Native-Query providers.

Guide for Admin

In this guide, you will find the instructions to connect to the JDBC Native Data Source in Wyn Enterprise. Note that the following demo custom data provider is for learning purposes only.

Build Artifact

Ensure that you have installed .NET 8 SDK.
Download the provider source code -
NativeJdbcProvider.zip.
Extract the downloaded Zip file.
Navigate to the directory, and run the following command in the command line,
```
dotnet publish
```
After running the above command, the artifacts (compiled files) will be saved at the following location: bin/release/.net8/publish.

Prepare JDBC Drivers

Visit the websites of third-party database providers to download the JDBC drivers that your end users will access. Below are the links to some of the popular databases,
1. MySQL: MySQL :: Download Connector/J
2. SQL Server: Download - JDBC Driver for SQL Server
3. PostgreSQL: Download | pgJDBC
Copy the JDBC Driver to the artifact directory.

Configure Wyn

Copy and Paste the entire artifact directory to the machine where Wyn is installed, placing it in any location you prefer.
Stop the Wyn service and locate the Wyn.conf file generally available at the following location: Wyn Enterprise\Monitor\conf.

Modify the Wyn.conf file by adding the CustomNativeQueryDataProviderAssemblies section and updating the sample path location to the location of the NativeJdbcProvider.dll of your artifact.

<Services>
  <Server>
    <DataProviders>
      <CustomNativeQueryDataProviderAssemblies>                    
        <sys:string>/filePath/customdatasource.dll</sys:string>
      </CustomNativeQueryDataProviderAssemblies>
    </DataProviders>
  </Server>
</Services>

Restart the Wyn service.

Now, you can open the Create Data Source page on the Wyn Enterprise Resource portal. In the Custom section, you’ll find the Native JDBC data source.

User Guide

This user guide provides instructions for configuring and using a custom JDBC-based native data provider in Wyn Enterprise, including connection strings, limitations, and supported platforms.

Introduction

This data provider can connect to the database via a JDBC driver.

In Wyn Enterprise, this provider is treated as a Native data provider and can only be used in the following scenarios:

* Native dataset designer

* CustomSQLTable in direct/cache dataset designer

JDBC Drivers

JDBC drivers are not included with this data provider. You must specify the path to the JDBC driver to initiate the connection.

See the Connection String Pattern section for more details.

Connection String Pattern

The connection string format is as follows:

DriverClass=???;DriverClass=???;JdbcUrl=???

Sample:

	DriverPath = "mysql-connector-j-8.2.0.jar";
	DriverClass = "com.mysql.cj.jdbc.Driver";
	JdbcUrl = "jdbc:mysql://10.32.5.241:3306/stg_GEF11977?user=root&password=unknown";

The connection string is a combination of three parts:

DriverPath: The relative path to the JDBC driver JAR file. The path should be relative to the location of the primary library for this data provider. In the docker environment, you can mount the jar file to the container and specify the path here.
DriverClass: The class name of the JDBC driver which is required to load the driver.
JdbcUrl: The JDBC connection string, which the driver will use to connect to the database.

Like any other built-in data providers, you can use @{user_context_name} and/or #{organization_context_name} in the connection string to refer to context variables.

Command text pattern

The command text must follow the SQL syntax of the database you are connecting to.

Limitations

Data Type Mapping
This data provider uses gRpc and Protobuf to communicate with the Java runtime. Some unconventional data types may not be supported.
If your database contains columns with unsupported data types, you may need to carefully write your command text to avoid referencing these columns.
Row Limit Option
Sometimes, Wyn needs to retrieve the result schema of a given command. However, because this provider does not know the underlying database, it cannot automatically open a TOP N or LIMIT clause to the command text.
As a workaround, the provider will attempt to append a LIMIT ? clause to the command text if needed. But, this may not work always. If the attempt fails, the provider will execute the raw command and return only the specified number of rows.

This known limitation may impact the performance when:

Creating CustomSQLTable in direct/cache dataset designer.
Editing an existing direct/cache dataset using this provider in the dataset designer.
It is recommended to use this provider in scenarios where the command does not return a large number of rows.

Data Source Preview
Like the other built-in native data providers, this provider does not support data source preview.
Platform Support
This data provider works with Wyn service deployed on Windows(x64) and Linux(x64).

Disclaimer

The source code of this data provider is provided as-is. You are free to modify and redistribute it without any restrictions.
This data provider is a demo provider created by the Wyn team to demonstrate how to build a custom data provider. It is not guaranteed to work in all scenarios. For any questions, please refer to the Wyn forum.

Note: The JDBC driver path depends on where the admin has placed the driver on the server machine. Please ask your Wyn administrator for details.

Guide for Provider Developer

The following instructions include the steps to create your custom data provider,

Prepare Development Environment

Ensure that you have installed .NET8 SDK.
Prepare an IDE (Integrated Development Environment) like Visual Studio or Visual Studio Code.

Build Project

Create a library project using the IDE.
Reference the following assembly version 1.80.6-dev in your library project:
Gcef.CustomProvider.Native.dll
Note: The older version of the referenced library will continue to function properly in the latest version of Wyn Enterprise.
Create an interface to implement a class namely, Gcef.CustomProvider.Native.INativeQueryDataProvider
Build your project and publish the artifact (compiled assembly).
Configure the Wyn server to add and use the new custom provider.

Gcef.CustomProvider.Native.INativeQueryDataProvider

Interface description

public interface INativeQueryDataProvider
{
    // The name of the provider should be unique globally.
    static abstract string ProviderName { get; }
    
    // Creates an instance of the native-query data provider statically.
    static abstract INativeQueryDataProvider CreateInstance();
    
    // Configures the features of the provider.
    // Parameters:
    // features:
    static abstract void Configure(IFeatureCollection features);
    
    // Executes a native query and returns the result as a data reader.    
    // Each time this method is called, a new instance of the current class is created.
    
    // When implementing this method, ensure that all unmanaged resources are closed properly after the reader finishes work.
    // Parameters:
    // nativeQuery:
    // readerConsumer:
    Task ExecuteAsync(INativeQuery nativeQuery, Action<IDataReader> readerConsumer);
    
    // Tests the connection string. The implementation can just check the connection string or connect to the database.
    // As a convention, this method will throw exceptions if the test fails.
    // Parameters:
    // connectionString:
    Task TestConnectionAsync(string connectionString);
}