#SQL

Earlier in this series, we've presented four different demo applications for iKnow, illustrating how its unique bottom-up approach allows users to explore the concepts and context of their unstructured data and then leverage these insights to implement real-world use cases. We started small and simple with core exploration through the Knowledge Portal, then organized our records according to content with the Set Analysis Demo, organized our domain knowledge using the Dictionary Builder Demo and finally build complex rules to extract nontrivial patterns from text with the Rules Builder Demo.

This time, we'll dive into a different area of the iKnow feature set: iFind. Where iKnow's core APIs are all about exploration and leveraging those results programmatically in applications and analytics, iFind is focused specifically on search scenarios in a pure SQL context. We'll be presenting a simple search portal implemented in Zen that showcases iFind's main features.

Introduction

The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.

Hello,

We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.

In the attached file there are 4 sample Ensemble Operations that demonstrate four different types of transactions with an external SQL DB:

Presenter: Andreas Dieckow
Task: Apply SQL security to multiple servers in a distributed system
Approach: Provide code samples for using new API calls to apply SQL security statements to multiple instances of our products

Description: The requirement that started at all. See examples on how to use this new feature and integrate it into your application by discuss code examples.

Problem: SQL Security is local to the instance and most of time driven by customer application code. That it is only local to the instance and is not automatically going to other instances requires a solution.

Solution: With application code use new API calls to issue SQL security statements that is applied to multiple instances.

Content related to this session, including slides, video and additional learning content can be found here.

Presenter: Kyle Baxter
Task: Upgrade to a new version of Caché without concerns about slowing existing SQL queries
Approach: Use the new Frozen Plans feature in Caché

Content related to this session, including slides, video and additional learning content can be found here.

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
 
USER>s sc=rs.Execute("c:\s\","*.txt")
 
USER>w sc
1
USER>while rs.%Next() {w !,rs.Data("Name")}

...

then the new way is:

What do you do if you want to have the ID field have a meaningful name for your application?

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data. Moreover, sometimes you want to set this value directly. Caché fully supports this functionality and it works Suppose you have a class Test.Kyle. The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

Customers who switch to Caché from relational databases report that their average performance is up to 20 time faster, running on the same hardware, with no changes to the application. What is it about Caché that lets applications run so fast?