C3 AI Documentation Home

Connect Application to Microsoft SQL Databases

The C3 Agentic AI Platform has a built-in 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 table.
  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.

The following sections include detailed instructions for configuring the connection.

For more information, see the MSSQL documentation.

Model the source system

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

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

JSON
{
    "name": "MSSQLSourceSystem"
}

Configure the credential used to authorize the JDBC connection

Create a JdbcCredentials Type instance to configure the connection to the external MSSQL table, 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
  • username — The account with authorization to access the MSSQL database table
  • password — The password for this account

For example, run the following from console to configure the JdbcCredentials:

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

JdbcStore.forName("MSSQLSourceSystem").setCredentials(creds, ConfigOverride.APP);
JdbcStore.forName("MSSQLSourceSystem").setExternal(ConfigOverride.APP);

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, you can add the following MSSQLTable.json to the \metadata\SqlSourceCollection directory of your package:

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

Model the table schema

To model the schema of the MSSQL table in your application, create an External Entity Type with a schema name that matches the name of the table in the MSSQL table, as it is case-sensitive.

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

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

You can use the inferSourceType() method to access the C3 AI data types of the table, which the C3 Agentic AI Platform infers from the source data types.

JavaScript
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;
    }

Output

MSSQL data types are mapped to C3 AI data types according to the following table. See also PrimitiveType.

MSSQL Data TypesC3 AI Data Types
bigintbigint
intint, int32
smallint, tinyintint16
numericdecimal
realfloat
floatdouble
char, text, varchar, nchar, ntext, nvarcharstring
binary, varbinarybinary
date, smalldatetime, datetime, datetime2, timedatetime

You can also access the source data types to validate the type inference:

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

Output

Complete the External Entity Type definition:

Type
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"
}

Note that the id field is required. If your table does not have a column called id, you can change the schema name for the corresponding id field with the following annotation:

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

Read data from the table

After completing the External Entity Type definition, you can validate the configuration by fetching the External Type data from the MSSQL table:

JavaScript
c3Grid(InsuranceDataMSSQL.fetch());

Output

See also

Was this page helpful?