Connect Application to Microsoft SQL Databases

The C3 Agentic AI Platform has a built-in Java Database Connectivity (JDBC) connector for integrating with Microsoft SQL (MSSQL) databases.

To connect to MSSQL from your application:

1. Add a SqlSourceSystem modeling the MSSQL source system to your package.
2. Configure the JdbcCredentials authorizing the connection to the external MSSQL database.
3. Add a SqlSourceCollection modeling the target MSSQL table to your package.
4. Create an External Type modeling the schema of the external MSSQL table.
5. Fetch data from the MSSQL table to validate the configuration.

For more information on MSSQL connection URLs, see the MSSQL documentation.

Copy link to this section

Model the source system

Create a SqlSourceSystem and set the name field as the identifier for the external database system.

For example, add the following MSSQLSourceSystem.json to the \metadata\SqlSourceSystem directory of your package:

{
    "name": "MSSQLSourceSystem"
}

Copy link to this section

Configure the credential used to authorize the JDBC connection

Create a JdbcCredentials Type instance to configure the connection to the external MSSQL database, passing the following fields to the JdbcCredentials.fromServerEndpoint() method:

• serverEndpoint: MSSQL server hostname
• port: MSSQL server port
• datastoreType: Specifies that the JdbcCredentials authorizes a connection to MSSQL
• database: The name of the MSSQL database that you are connecting to
• schemaName: The schema to use, or null to use the default schema
• username: The account with authorization to access the MSSQL database table
• password: The password for this account

For example, run the following code in the console to configure the JdbcCredentials:

var creds = JdbcCredentials.fromServerEndpoint("HOST",
                                                PORT,
                                                DatastoreType.MSSQL,
                                                "SOME_DB",
                                                "SCHEMANAME",
                                                "some_username",
                                                "some_password");

SqlSourceSystem.forName("MSSQLSourceSystem").setCredentials(creds);

Copy link to this section

Model the table containing the data

To model the external MSSQL table in your application, create a SqlSourceCollection and set the following fields:

• name: Identifier for the MSSQL table
• source: Name of the External Type that models the schema of the external MSSQL table
• sourceSystem: Name of the MSSQL SqlSourceSystem

For example, to model a table called insurance_risk, add the following MSSQLTable.json to the \metadata\SqlSourceCollection directory of your package:

{
    "name" : "InsuranceDataMSSQL",
    "source" : "InsuranceDataMSSQL",
    "sourceSystem" : {
      "type" : "SqlSourceSystem",
      "name" : "MSSQLSourceSystem"
    }
}

Copy link to this section

Model the table schema

To model the schema of the MSSQL table in your application, create an External entity Type. Give the entity Type a schema name that matches the name of the MSSQL table exactly. The schema name is case sensitive.

Start by adding the following InsuranceDataMSSQL.c3typ file to the \src directory of your package:

entity type InsuranceDataMSSQL mixes External, NoSystemCols schema name 'insurance_risk'

Use the inferSourceType() method to see the C3 AI data types that the C3 Agentic AI Platform infers from the source data types of the table:

var schema = SqlSourceCollection.forName("InsuranceDataMSSQL").inferSourceType().declaredFieldTypes;

var myObject = {};
for (let i = 0; i < schema.length; i++) {
        schemaName = schema[i].schemaName;
        myObject[schemaName] = schema[i].valueType.name;
    }

The C3 Agentic AI Platform maps MSSQL data types to C3 AI data types according to the following table. For the MSSQL types, see MSSQL data types. For the C3 AI types, see PrimitiveType.

To validate the type inference, view the source data types:

SqlSourceCollection.forName("InsuranceDataMSSQL").connect().columns;

Complete the External entity Type definition:

entity type InsuranceDataMSSQL mixes External, NoSystemCols schema name "insurance_risk" {
  
  id: ~ schema name "Id"
  
  firstName: string schema name "First_Name"

  lastName: string schema name "Last_Name"

  age: int schema name "Age"

  income: int schema name "Income"

  annualMileage: int schema name "Annual_Mileage"

  creditScore: int schema name "Credit_Score"

  debtToAssetRatio: double schema name "Debt_to_Asset_Ratio"

  riskScore: int schema name "Risk_Score"
}

The id field is required. If your table has no column called id, change the schema name for the corresponding id field with the following annotation:

@db(dataTypeOverride="ID_FIELD_DATA_TYPE")
id: ~ schema name "ID_FIELD"

Copy link to this section

Read data from the table

After you complete the External entity Type definition, validate the configuration. Fetch the External Type data from the MSSQL table:

c3Grid(InsuranceDataMSSQL.fetch());

Copy link to this section

See also

• Configure SQL Sources
• Data Integration Overview
• Integrate Data Programmatically