Creating Native Queries For Microsoft SQL Server
For Connectors that support native languages, such as Microsoft SQL Server, use the Native Query Block in a flow to write your own free form query.
When you write a native query, the following rules apply:
TIBCO Cloud™ Integration - Connect uses the query to create a read-only virtual source entity. While you can read this entity from other Blocks:
- The entity does not display in a Lookup Block
- You cannot update the entity
Any field names resulting from the query must be unique. If you join two tables that both have a field named 'Id,' then you must write the query to generate a unique name for each Id field.
For example, the following query calls both Customers.Id and SalesOrders.Id:
Select Customers.CompanyName, Customers.Id, SalesOrders.OrderNumber, SalesOrders.Id
From Customers join SalesOrders on...
To work with TIBCO Cloud™ Integration - Connect, rewrite the query using the AS clause. For example:
Select Customers.CompanyName, Customers.Id as CustomerId, SalesOrders.OrderNumber, SalesOrders.Id as SalesOrderId
From Customers join SalesOrders on...
A Native Query must return exactly one result set; for example, the following query is not allowed:
Select * from Accounts
Select * from Contacts
Metadata fields that display on the source side may show unexpected values for Allow Nulls and Primary Key.
If the source metadata changes, you must retest the query; resetting the metadata does not properly update the metadata.
When testing a Native Query in a flow, if the source datastore does not return any data, TIBCO Cloud™ Integration - Connect cannot build the schema for the underlying metadata and the flow cannot be saved. To allow TIBCO Cloud™ Integration - Connect to build the schema, do the following:
- Create a single temporary record in the source datastore that matches the Native Query.
- Test the Native Query and ensure that it is successful.
- Save the flow.
- Remove the temporary record from the source datastore.