Giving CAI a Database: Three Routes to SQL, and How to Choose in Informatica

Informatica Cloud Application Integration (CAI) is great at orchestrating processes, but it has one gap that surprises almost every new developer: there is no SQL step. Almost every non-trivial process still needs to read or write a database, so teams work around the gap. Very often the workaround is a bespoke REST service, and that quietly turns into a component the team maintains forever.

This post looks at three routes for giving CAI a database, explains why the obvious connection fails inside a Service step, and walks through the five undocumented edges we found on the route that won.

In this blog we’ll:

• Unpack why CAI has no SQL step and why teams default to a REST service
• Show why a plain JDBC App Connection is rejected in a Service step
• Compare three routes: a bespoke REST service, the DAS connector, and a CDI mapping task
• Cover the five undocumented edges on the DAS connector route
• Close with the results of the migration and a pre-deployment checklist

The Missing SQL Step

Picture a standard CAI process. It receives a request, looks up a reference value, decides what to do, persists a result, and checks a state table before finishing. Every one of those steps touches a database.

In most low-code tools, this is a first-class step: drag in a SQL step, write a query, move on. CAI’s step palette has no SQL step and offers no obvious built-in way to reach a database. That gap is where most teams make the same decision, and it is worth understanding both of the common reactions.

The Default Reaction: Write a REST Service

Left without guidance, teams write a small REST service that sits in front of the database, and the CAI process calls it. It is fast and familiar, and it works on day one.

The catch is what happens afterwards: the service becomes a component the team owns and maintains forever. It needs hosting, deployments, monitoring, security reviews, and a developer who remembers how it works. Every new query means another change to code that lives outside the platform.

The Connection That Looks Right but Is Not

The second reaction is to create a plain JDBC App Connection and use it inside a Service step. JDBC is the standard way to talk to a database, so it looks like it should work. It does not, and CAI says so clearly:

This is the moment many teams give up on native options and go back to a REST service. That is the wrong lesson, because a native route does exist. It is just not the one that looks obvious.

What We Actually Needed

Before comparing options, we wrote down the requirement. Any route had to satisfy four things:

• Read and write a database from a process. Look up a value, persist a result, or check a state table.
• Be native and supported. Reach the database through the platform’s own connectors instead of bespoke code.
• Fit the unit of work. Choose by whether the job is one row inside a Service step or a whole set of rows.
• Have no surprises in production. Know the undocumented edges of the chosen route before deploying it.

Three Routes, Weighed Against the Same Requirement

We evaluated all three routes against that requirement. The table below summarizes the trade-offs.

The Bespoke REST Service

This is the path of least resistance, and for a one-off it can be reasonable. The problem is that it scales badly. Each new database interaction means another endpoint, another deployment, and another thing that can break outside CAI’s own monitoring. You have traded a platform limitation for a permanent maintenance obligation.

The DAS Connector

The DAS connector is the native answer. It supports both SELECT and INSERT / UPDATE / DELETE, and, critically, it is allowed in a Service step. It reaches the same database that the rejected JDBC connection would have reached. It is simply a different connection object, one the Service step accepts.

The CDI Mapping Task

CDI (Cloud Data Integration) is built for data movement, not single lookups. When you need to compare or load a whole file or table, a CDI mapping using components like Joiner, Aggregator and Router, triggered from a scheduled taskflow, is the right tool. Using it to fetch one row for a running process is the wrong tool, because you would be launching a heavyweight job for a lightweight question.

The Whole Trick: Two Connection Objects, One Database

Same database, two different connection objects, and only one works. The decision is not “JDBC or something else”; it is “which connection object will the Service step accept?”

How the Call Actually Reaches the Database

There are two ways in, depending on the size of the job:

The rule of thumb is short: use CAI when you need one row inside a running process, and use CDI when the unit of work is a set.

Five Undocumented Edges on the DAS Route

The DAS connector is the right route, but it has behaviours that are not obvious from the documentation. We hit each of these while building and fixed them. Here they are, so you do not have to.

1. Driver Placement

The JDBC driver jar must go on the shared library path, not in the ext folder. The ext folder is on no classloader, so a driver placed there is simply never found.

The jar must also be Java-8 compatible, meaning its class files cannot be compiled for a newer Java version than the runtime understands. We checked two driver jars for class-file compatibility before deploying them.

2. Parameter Syntax

Parameters are written as {$field}, and this is textual substitution, not a bound parameter. The value is pasted into the SQL text, which means untrusted input can change the meaning of the query. The SQL-injection setting is therefore not optional whenever any part of the input comes from outside your control.

-- Illustrative example
SELECT status
FROM request_state
WHERE request_id = {$requestId}

Sample: a parameterised SELECT. {$requestId} is pasted into the SQL as text.

3. Column Case

The Column Case dropdown silently lowercases every output column name. If downstream logic expects STATUS or RequestId, it will not find it. Either expect lowercase names everywhere downstream, or set the option deliberately rather than leaving it at its default.

4. Output Expressions

Output expressions must be wrapped in braces. Without them, CAI treats the value as literal text instead of evaluating it, so you get the expression back rather than the result. There is no error, just wrong data.

5. SELECT vs. DML

The connector does not work out from your SQL whether a statement returns rows. SELECT and DML (INSERT / UPDATE / DELETE) are distinguished only by the “Has Result Set” setting, so set it per statement. A SELECT with the flag off returns nothing useful, and a DML statement with the flag on can fail because there is no result set to read.

What We Measured Across the Migration

After moving off the bespoke service and onto the native connector, this is where we landed:

The most important number is the last one. Every component we no longer own is a component we no longer patch, monitor or explain to a new teammate.

These results were verified against the connector’s own rejection message for JDBC in a Service step, its parameter-syntax documentation, and class-file version checks on two driver jars.

Making This Practical

A few concrete habits cover most of this ground:

• Use the DAS service connector, not a plain JDBC App Connection, for any database call inside a Service step.
• Put the driver on the shared library path and confirm it is Java-8 compatible before deployment.
• Keep the SQL-injection setting on for every parameter that can carry untrusted input.
• Expect lowercase column names downstream, and wrap every output expression in braces.
• Set “Has Result Set” per statement: on for SELECT, off for DML.
• Choose by the unit of work. If the job is a set, not a row, build it as a CDI mapping instead.

How Ksolves Helps You Get More from Informatica

Ksolves is one of the trusted IT companies that provide Informatica consulting and support services that help organizations get more value from their data platforms, from architecture decisions to day-to-day production operations. Our team works across Informatica Cloud and IDMC, including CAI, CDI, and PowerCenter, so we know where the platform shines and where its edges are.

The approach in this blog reflects how we work: prefer native capabilities over custom code, test undocumented behaviour before go-live, and reduce the components our clients maintain. Our Informatica services cover strategy and architecture, implementation and platform setup, migration and modernization from PowerCenter or bespoke code, data quality and governance, and 24/7 Informatica support with monitoring and tuning.

If your team is building on Informatica CAI or CDI, struggling with database connectivity, or replacing custom integration code with native capabilities, reach out to our Informatica experts to discuss your use case.

Wrapping Up

CAI having no SQL step is not a dead end. It only means the door is not where you first look for it. A bespoke REST service solves the problem quickly but leaves you owning it forever, and a plain JDBC App Connection looks right but is rejected in a Service step. The DAS connector reaches the same database natively, provided you respect its five edges. For whole files or tables, CDI is the better tool.