Day 3: Developing with InterSystems Objects and SQL
I'm participating in the Developing with InterSystems Objects and SQL with Joel Solon. The course is very nice and I will share with you some tips I got during the training. Tips presented in the day 3:
- You can see your class catalog using %Dictionary classes and see your sql objects into INFORMATION_SCHEMA table.
- Is possible use SQL inside your ObjectScript methods using Dynamic SQL or Embedded SQL.
- You can pass parameters into Dynamic SQL string using ? (eg.: where country = ?) and pass parameters to Embedded SQL using colon (ed.: where country = :variable).
- Dynamic SQL Sample (from Intersystems documentation):
SET tStatement = ##class(%SQL.Statement).%New(,"Sample") SET myquery = 3 SET myquery(1) = "SELECT TOP ? Name,DOB,Home_State" SET myquery(2) = "FROM Person" SET myquery(3) = "WHERE Age > 60 AND Age < 65" SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} DO tStatement.%Display() WRITE !,"End of %Prepare display"
- Embedded SQL Sample (from Intersystems documentation):
#SQLCompile Select=Display &sql(SELECT DOB INTO :a FROM Sample.Person) IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT} ELSEIF SQLCODE=100 {WRITE "Query returns no results" QUIT} WRITE "1st date of birth is ",a,! DO $SYSTEM.SQL.Util.SetOption("SelectMode",1) WRITE "changed select mode to: ",$SYSTEM.SQL.Util.GetOption("SelectMode"),! &sql(SELECT DOB INTO :b FROM Sample.Person) WRITE "2nd date of birth is ",b
- Embedded SQL Sample - Insert:
&sql(INSERT INTO Sample.Person (Name, Age, Phone) VALUES (:name, :age, :phone)
- If you need process data in batch use SQL, if you process a single record, use Persistent Object API.
- You can create SQLQuery methods and if you use [SqlProc] in the method, will be created a SQL procedure in the SQL side.
- From terminal is possible go to SQL Shell, a terminal to SQL commands, from terminal, execute do $system.SQL.Shell().
- Persistent classes have a system generated ID, if you need ID controled by you, use IDKEY index with one or more properties. Eg: Index Key on SocialNumber [IdKey, PrimaryKey, Unique].
- There two strategies to control concurrency when two or more process try process the same data at same time: Pessimistic and Optimistic.
- To acquire a pessimistic control, lock the object with %OpenId(ID, 4), where 4 lock the table to exclusive access. After process ran the lock can be released.
- To do optimistic control (indicated to web apps), create in your persistent class Parameter VERSIONPROPERTY = "Version"; Property Version as %Integer [ InitialExpression = 1 ]. IRIS will increment property version in each instance update, allowing coordinate the order of updates, instead lock table.
- When you have methods that update, insert or delete data, use transactions, to keep the data consistency. Example:
Transfer(from,to,amount) // Transfer funds from one account to another { TSTART &SQL(UPDATE A.Account SET A.Account.Balance = A.Account.Balance - :amount WHERE A.Account.AccountNum = :from) If SQLCODE TRollBack Quit "Cannot withdraw, SQLCODE = "_SQLCODE &SQL(UPDATE A.Account SET A.Account.Balance = A.Account.Balance + :amount WHERE A.Account.AccountNum = :to) If SQLCODE TROLLBACK QUIT "Cannot deposit, SQLCODE = "_SQLCODE TCOMMIT QUIT "Transfer succeeded" }
- InterSystems IRIS has an Architecture based in Namespaces (logical groups of databases) and Databases.
- There two types of data to hold in the databases: for data (globals) and for code (source code - procedures).
- You can do horizontal processing scaling to your databases using ECP - Enterprise Cache Protocol, allowing see different databases in several servers in the same namespace.
- You can do horizontal data volume scaling (distributed database partitions) using Sharding (only IRIS), allowing partitioning data into distributed nodes (like MongoDB).
- The maximum size to a database is 32TB.
- To change from a namespace to another do zn "Namespace" or set $namespace = "Namespace".
PS 1: the course show in details how to do transactions control, this is very important.
Tomorrow I will post day 4 resume.