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 Map to write your own free form query.
When you write a native query, the following rules apply:
- TIBCO Scribe® Online 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 Scribe® Online, 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 Map, if the source datastore does not return any data, TIBCO Scribe® Online cannot build the schema for the underlying metadata and the Map cannot be saved. To allow TIBCO Scribe® Online 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 Map.
- Remove the temporary record from the source datastore.
