Caché SQL Queries

Announcement

Mike Kadow · Jul 9, 2018

#SQL #Caché

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

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