Announcement
· Jul 9, 2018

Caché SQL Queries

I have finished my 4th book about Caché and MUMPS. This will probably be my last.

I am deeply grateful and humbled for all the help I have received from this group and the WW Response Center.

You all have something very special going here. 

Unlike most groups like this, you help those who are struggling to get started, that is unique.

Never lose the attitude of graciously helping the beginner. 

=================================================================================

Following is the book cover, available on Amazon or Smile.Amazon,

the description, my special thanks, Robert, Evgeny, Dmitry,  Nikita,

and so many others. I apologize for not listing all your names.

Evgeny's very gracious review, and the Table of Contents.

Mike Kadow - pmkadow@gmail.com


 

 


 

Special Thanks

Editor

Robert Cemper – I am thankful for his excellent knowledge of SQL and queries. He has been of great help, he always understands the various areas that seem to confound me.

InterSystems Corporation - I am indebted to InterSystems Corporation for their excellent documentation, examples, and test data as well as their Worldwide Support Center. It is this attitude for openness from InterSystems that greatly spreads the Caché message and technology.

I am thankful for the help I received from the Developers Community and their excellent staff of moderators John Murray, Dmitry Maslennikov, Eduard Lebedyuk, and Robert Cemper lead by Evgeny Shvarov, I salute you all!

It is imperative for a developer to have an effective, encompassing, and correct grasp of their database, for that reason I express my thanks to Nikita Savchenko – developer of the Caché Class Explorer (Chapter 6), software that opens up ones' understanding of the Caché database.

Dmitry Maslennikov- Dmitry helped me late in the writing process with a new option called "Func". He also corrected a number of errors in my book and was of great help.

Eduard Lebedyuk for his help with SQL and his several references in this book.

William Hazel from the Worldwide Support Center - for his help.

Mike Rizza from the Worldwide Support Center - for his help.

Harry Tong from the Developer Community.

Kyle Baxter for his SQL help and references.

I am grateful to Evgeny Shvarov from the Developer Community – he has helped me on many levels and many occasions as well as reviewing this book.

Jack Snyder – his help in reviewing my book.

TinyURL, LLC. – Tinyurl.com for their website which allowed me to shrink some of the long Uniform Resource Locators (URLs) used in this book.

Dick Martel – My Mentor, although Dick is no longer with us, his influence is still felt.

And Others

There are others, I am sure that deserve my thanks. I cannot take credit for this book as if I am an island. To the many others, I am humbled, and I thank you.

 

Comments from Evgeny Shvarov about Caché SQL Queries:

InterSystems Caché is a multi-model data platform which implements persistence via globals - special data structures also known as sparse key-value arrays. Globals can be managed by SQL in Caché using several different ways. In this book, Mike describes different approaches on how to use SQL in Caché and offers supplementary tools that could help therein. He introduces a consistent view on how to start, what to use and where to find the information about SQL usage in InterSystems Caché. If you are familiar with SQL this book could be a good first step to start work with InterSystems Caché as a database for your solutions. The book could be helpful for beginners in Caché and also for experienced Caché developers who want to refresh their knowledge on Caché SQL and make some interesting findings.

 

 

1              Table of Contents

CHAPTER 1 INTRODUCTION   15

The Potential of Data and SQL Queries  15

Finding the right Query           16

Consider your data 16

A Diamond and Imagination  17

Look ahead for a new generation of queries         17

Imagination and the Box        18

SQL Table Updates 19

Updates are done with ObjectScript      19

Namespaces           20

Queries run in Samples Namespace      20

Current Namespace.              20

All namespaces on the system.              20

Is your Namespace Valid?      21

Change Namespaces              21

 

CHAPTER 2 WHEN YOU NEED HELP       23

InterSystems Worldwide Support Center              23

InterSystems Developer Community      23

 

CHAPTER 3 GENERAL INTRODUCTIONS 25

What is Caché?       25

What are Caché Objects?       25

What is Caché ObjectScript?  26

What is MUMPS?   26

Link to InterSystems Documentation     26

 

CHAPTER 4 INSTALLING CACHÉ              27

Installing Caché       27

 

CHAPTER 5 SUMMARY           29

Structured Query Language   29

InterSystems and Caché SQL  29

Database Structure 29

Caché SQL Evolution               30

Overview of the Book             31

 

CHAPTER 6 CACHÉ CLASS EXPLORER     33

Caché Class Explorer — exploring Caché in the UML notation              33

Introduction to Caché             34

Enter Class Explorer                34

Functionality           34

 

CHAPTER 7 SQL SHELL INTERFACE         37

Every Global Accessible          37

Classes to test with – Samples               37

A partial listing of Classes in Samples    37

Sample.Person Class               38

Database of Classes available to the Developer     39

Copy Samples         39

Invoking the SQL Shell Interface            39

Initial Query into Sample.Person           40

Multi-line example of SQL Shell Interface              41

Go command          42

Input Parameters   43

ObjectScript Commands inside the SQL Query      45

Log          46

Set LOG ON             46

SQL Shell Interface Help         47

SQL Shell Interface Documentation        47

Chapter Summary  48

 

CHAPTER 8 MANAGEMENT PORTAL SQL UTILITY  51

Management Portal SQL Utility Documentation    51

Invoking the SQL Query Utility               52

SQL Query Utility Documentation          53

Switch the Namespace to Samples        54

Query "Select Name from Sample.Person"           56

See all the Properties in Sample.Person 57

See all the data in Sample.Person          58

Drag and drop example          59

Query Builder         60

Global Sample.PersonD          61

Global Sample.PersonI           61

Menu drop-down option        62

Home, About, Help, Logout    63

Home option           63

About option           64

Help option             65

Execute, Show Plan, Show History, Query Builder, Display Mode, Max                66

Wizards, Actions, Open Table, Documentation      68

Catalog Details about Sample.Person    71

Catalog Details of a Class       72

Table Info                73

Fields      74

Map/Indices            74

Triggers   75

Constraints              75

Cached Queries      75

Table SQL Statements            76

All SQL Statements in a Class 77

All SQL Statements in the Sample Classes             79

Tables     80

Views      80

Procedures or Stored Procedures           81

How are Stored Procedures defined?    82

Cached Queries      84

Recommendations 84

 

CHAPTER 9 EMBEDDED SQL   85

Embedded SQL Query Concepts             85

Use .MAC File (in Routines)    85

SQL directive - &sql()              85

Cursor      85

Host Variables        86

Embedded Queries – Routines and Classes           86

Routine ^LastName.Embedded1           86

Routine ^LastName.Embedded1           87

Routine ^LastName.Embedded1 Output               88

^Sample.PersonD Global        89

Class LastName.Emb1Class    89

Class LastName.Emb1Class    90

Class LastName.Emb1Class Output        91

Stored Procedure    92

StartsWith Parameter            92

Class LastName.Emb2Class & Stored Procedure    93

Called from $SYSTEM.SQL.Shell             94

Called from ObjectScript        94

Execute Emb2Class the Management Portal         95

Embedded SQL Query Summation & Recommendations     96

 

10 DYNAMIC SQL QUERY       97

%SQL.Statement Class           97

•              %Prepare() method 97

•              %PrepareClassQuery() method              97

•              %Execute() method 97

•              %ExecDirect() method            97

•              %Display() Method 97

•              %Get() Method       97

•              %GetData(n) Method             97

•              %Print() Method     97

Approach 98

List of Examples:     98

Base Query              100

Array Query             101

Error Checking Query              102

Decompose Status  103

New Query              104

Distinct Query         106

Where and Order By Query    107

Max, Min, and Avg Query       108

%PrepareClassQuery()            109

Message Query       111

Zwrite from ResultSet            112

Parameter Query    113

Multiple Parameters Query    114

Multiple %Execute() Commands Query 115

%ExecDirect() Query               116

%ExecDirect with parameters Query      117

ResultSet - %Get( FieldName ) Query    118

ResultSet %GetData( Number ) Query   119

%Print() Query        120

%Print("^|^") Query               121

 

CHAPTER 11 IN-DEPTH QUERIES            123

Free Space              123

%Freecnt 124

Namespace List      126

Top Global              127

Global Size              128

Directory List          129

Processes List         130

List Pids   131

List Locks 132

^%SS       133

%Display Formatted               134

 

CHAPTER 12 CUSTOM CLASS QUERY     135

Introduction to Custom Class Query      135

Custom Class Query Shell       136

Workings of the Custom Class Query     137

Simple Example of a Custom Class Query              138

Running of the Custom Class Query       139

 

CHAPTER 13 INDICES              141

General Guidelines on Indices                141

Tuned Tables          141

Bitmap Queries       142

Bitslice Indices        143

 

CHAPTER 14 THE SELECT STATEMENT   145

Select Statements in the Management Portal       146

 

CHAPTER 15 ROWSPEC          147

Columns  147

Inside the Columns 148

CONTAINID             149

SQLProc   149

SQLName 149

ROWSPEC Line        149

 

CHAPTER 16 RESULTSET        151

ResultSet & the %Next commands        151

Finer points             151

%Display() – all rows               152

%Print() – 1 row      153

RSet.Name, RSet.%Get("Name"),  &  RSet.%GetData("1")   154

 

CHAPTER 17 STORED PROCEDURE [ SQLPROC ]    155

Stored Procedures in the Management Portal      156

Stored Procedure in the SQL Shell         157

 

CHAPTER 18 CACHED QUERIES              159

 

CHAPTER 19 DYNAMIC DISPATCH          161

 

CHAPTER 20 %SQL.STATEMENT            163

%SelectMode          163

%SchemaPath         164

%Dialect  165

%ObjectSelectMode               167

 

APPENDIX A – RETURNSTATUS              169

Return Status of Success        169

Return Status of Failed           169

 

APPENDIX B – COMMON SQL MISTAKES               171

Build Indices            171

 

APPENDIX C – UNIFIED DATA ARCHITECTURE       173

Unified Data Architecture       173

 

APPENDIX D – LOOKING AT A GLOBAL  175

Looking at a Global - the Management Portal       175

^Sample.PersonD   177

Looking at a Global – from the Terminal               178

^Sample.PersonI     178

 

APPENDIX E – FORMATS FOR EXECUTING CODE   179

 

APPENDIX F – IMPLICIT JOINS 181

Join          181

Implicit Join             181

 

APPENDIX G – SQL QUERY MANAGEMENT           183

 

APPENDIX H – FUNC SUFFIX   185

New Func() Method Added To Query Classes       185

 

APPENDIX I – SQL VIDEOS      187

SQL Things you should know  187

Optimizing SQL Queries          187

 

APPENDIX J – SQL CATALOG QUERIES   189

%Library.SQLCatalog.SQLTables()          189

%Library.SQLCatalog.SQLProcedures()  190

%Library.SQLCatalog.SQLProcedureInfo()              190

%Library.SQLCatalog.SQLRelationships()              191

%Library.SQLCatalog.SQLCachedQueryInfo()         191

%Library.SQLCatalog.SQLCODEList()      192

%Library.SQLCatalog.SQLFields()           192

%Library.SQLCatalog.SQLIndices()         193

 

APPENDIX K – VARIOUS CLASS QUERIES               195

Class Index Query   195

Compiled Class Summary       195

Compiled Properties Query    195

Compiled Methods Query      195

Compiled Parameters Query  196

Compiled Storage Query         196

Compiled Projection Query    196

Compiled Trigger Query          196

Compiled Queries Query        197

Compiled Index Query            197

Compiled Constraints Query   197

Compiled Foreign Key Query  197

Compiled Instance Variables Query       198

 

APPENDIX L – USEFUL CALLS & FUNCTIONS          199

List Namespaces     199

Valid/invalid Namespaces      199

Show all Objects     199

Methods in %SYSTEM.OBJ     199

Methods in %SYSTEM.Version               199

Methods in %SYSTEM.SQL     199

Methods in %SYSTEM.Status 200

Methods in %SYSTEM.Util     200

Methods in %SYSTEM.SYS     200

Methods in %SYSTEM.Config 200

Methods in %SYSTEM.CPU     200

Methods in %SYSTEM.Dictionary           200

 

APPENDIX M – MORE USEFUL CALLS&FUNCTIONS               201

Show Classes          201

Does an ID Exists?  201

Is this an Object?    201

Dumping contents of Object Reference (Oref)      201

Value of an Environmental Variable      201

Operating System Version      202

Show Macros          202

Decompose Status  202

 

APPENDIX N – EXTENT            203

 

APPENDIX O – THE QUOTE CHARACTER 205

 

APPENDIX P – CACHÉ LEXICON               207

Write the word "Caché" with Cut and Paste          207

Write the word "Caché" in Terminal      207

Write the word "Caché" in Microsoft Word           208

 

APPENDIX X – QUERIES IN TEXT             209

Base Query              209

Array Query             210

Error Checking Query              210

New Query              211

Distinct Query         211

Where and Order By Query    212

Max, Min, and Avg Query       212

PrepareClassQuery()               213

Message Query       214

Parameter Query    215

Multiple Parameters              216

Multiple Execute commands  217

%ExecDirect            218

%ExecDirect with Parameters 218

ResultSet Fieldname              219

ResultSet GetData  219

Print()      220

Print("^|^")            220

Free Space              221

FreeCnt   221

NameSpaceList       221

Top Global              222

Global Size              222

Directory List          222

Process List             223

List Pids   223

List Lock   223

^%SS       224

Display Formatted  224

%Display all rows    225

%Display 1 row       225

ResultSet – Name and age     226

Dynamic Dispatch   227

Extent      227

 

GLOSSARY              229

 

INDEX      239

Discussion (1)0
Log in or sign up to continue