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
On my table:
Thank you, MIke!