A Caché of Tips: SQL

[Emily Haggstrom]

This tip will cover the basics of SQL, which can be a very useful tool for reporting in Cache. Many Zen display components, like the TablePane, use SQL statements to define the data they will present.

 

Most of you are probably already familiar with CMQL (Caché MultiValue Query Language).  These are statements used to fetch data like:

 

LIST MYFILE FIELD1 FIELD2 WITH @ID = ‘12345’

 

SQL is a query language that uses a similar structure, but different keywords. The analogous SQL query to the above would be:

 

SELECT FIELD1, FIELD2 FROM MVFILE.MYFILE WHERE ID = ‘12345’

 

Note that SQL statements will work with the Cache class/table projection of your files, so make sure you have run PROTOCLASS on any file you want to query.

 

There are several types of SQL statements, including SELECT, INSERT, UPDATE, and DELETE. For reporting purposes, however, you’re only going to need SELECT statements, so that’s what I’ll focus on today.

 

The basic structure of a SELECT statement is this:

 

SELECT <fieldList> FROM <tableName> WHERE <someCondition>

 

<fieldList> is a comma separated list of the data fields you want to retrieve. Enter * to fetch all of the fields. A basic SQL statement to retrieve all of the data from a table looks like this:

 

SELECT * FROM MVFILE.MYFILE

 

<tableName> can either be a single table name or a comma-separated list of tables. If you use multiple tables, make sure the field names are unique to each table, or fully qualified. For instance:

 

SELECT MVFILE.MYFILE.FIELD1, MVFILE.MYFILE2.FIELD1 FROM MVFILE.MYFILE1, MVFILE.MYFILE2

 

This can get cumbersome if you have many fields, so you can assign aliases after each table name to use with the fields names.

 

SELECT t1.FIELD1, t2.FIELD1 FROM MVFILE.MYFILE1 t1, MVFILE.MYFILE2 t2

 

A note about table names: they are usually, but not always the same as the Cache package and class name. For instance “USER” is a SQL reserved word, so if your class is “User.Class”, the table name will become “SQLUSER.CLASS”. There is always a period between the package name and the classname, but additional periods in the package structure, they will be replaced with underscores, so “Classes.MyPkg.MyClass” becomes “CLASSES_MYPKG_MYCLASS”. (Note, table names are usually represented in all caps, but SQL is a case-insensitive language.)

 

<someCondition> can be any boolean statement involving the fields in the table. It will be applied to each record in the table, and if true the record is added to the set of returned data. Accepted operators include =, <> (not equal), <, >, <=, >=, AND, OR, etc. There are also a few special functions such as %STARTSWITH and %CONTAINS. For example:

 

SELECT NAME FROM MVFILE.MYFILE WHERE NAME %STARTSWITH ‘A’

 

MultiValued fields are treated as lists in SQL. To select records based on the contents of a list field, you use the FOR EACH clause. You use the %ELEMENT keyword to refer to the items in the list and the %VALUE to compare the individual values in the list. For instance, to find a list of cat owners, you could run:

 

SELECT NAME FROM MVFILE.PERSON WHERE FOR SOME %ELEMENT(PETS) (%VALUE = “Cat”)

 

You can also look for records that contain one of several values. For instance, to get a list of cat and dog owners, you could run:

 

SELECT NAME FROM MVFILE.PERSON WHERE FOR SOME %ELEMENT(PETS) (%VALUE IN (“Cat”,”Dog”))

 

With these rules, you should be able to run most reports you would need to run on your data. More complex SQL functions and predicates can be found in the Cache SQL Guide.

 

One more tip for this edition. If you’re starting to work with SQL and you’re interested to see quickly what results your query will return (or if it will run!) you can go to [SQL] > [Execute SQL Query] in the Management Portal. You will also find a basic query builder on this page that can help you get started.

 

For further reading:

MultiValue & SQL QuickStart Tutorial:

http://docs.intersystems.com/cache20111/csp/docbook/DocBook.UI.Page.cls?KEY=TVQS_SQLAndODBC

Cache SQL Reference:

http://docs.intersystems.com/cache20111/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL

[Roland]

I'm migrating from PICK D3 and was wondering the best way of using SQL on an external data source from within an MVBASIC program.  With D3 & OPENDB you can configure the HOSTS file so that you can read and write to and from the remote SQL data source like it was a native MV file.  I don't really need that functionality.  But I set up an SQL Gateway with a DSN.  The name of it is KSRR.  KSRR represents a remote relational database.  What is the best way to update or select from a table in KSRR?

Let's say I wanted to change all names from Joe to Joseph in a remote table KSRR.  UPDATE KSRR.dbo.tablename SET FN = 'Joseph' WHERE FN = 'Joe'

I'm not sure how you would us KSRR in the phrase or what the best way to incorporate into an MVBASIC program.

Thanks.

[Jason Warner]

Roland,

Look at this documentation for connecting to ODBC through Cache.
http://docs.intersystems.com/cache20122/csp/docbook/DocBook.UI.Page.cls?KEY=BGOD_gateway
It is for Cache 2012.2, but you can find the same documentation for all
versions of Cache that allow you to use the ODBC gateway.

Jason

> --
> You received this message because you are subscribed to the Google
> Groups "InterSystems: MV Community" group.
> To post to this group, send email to Cac...@googlegroups.com
> To unsubscribe from this group, send email to
> CacheMV-u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/CacheMV?hl=en

[Roland]

I had read that documentation and read it again but I don't seem to find what I am asking about.  Maybe I'm misreading something or I didn't explain well enough.  The docs seem to show how to make a persistent SQL gateway where Cache can access external ODBC databases.  It also shows how to access Cache from external sources.  Then it shows how to us dynamic SQL gateway where Cache can access external data sources.  I made the persistent SQL gateway and named it KSRR but I didn't see anywhere how you can use that in programming.  I only see dynamic SQL gateway with username and password.  How do I do a connect with the KSRR name that already has username and password?

Like this?

 Set conn=##class(%SQLGatewayConnection).%New()
    Set sc=conn.Connect("KSRR","","")

from the documentation it would seem like KSRR is a DNS and there is no username and password.  But KSRR is the persistent SQL gateway which already includes username and password.  I hope it's clear.

[Jason Warner]

From reading the documentation, it looks like the DSN you entered when
creating the gateway. It probably looks up the gateway you created
based on the DSN that it connects to. I'll bet if you enter your DSN
with no user name and password it will look it up in your gateway you
created and use the username and password you provided. I haven't
actually gone to ODBC from Cache, so I'm not 100% sure, but the
documentation seems to point that way.

Hopefully someone with experience can help out. If not, you could
probably take this to the general Cache board where I'll bet someone
has experience. A lot of the really smart Cache guys don't make it to
this board very often.

Jason

> <javascript:>

> > To unsubscribe from this group, send email to

> > CacheMV-u...@googlegroups.com <javascript:>

> > For more options, visit this group at
> > http://groups.google.com/group/CacheMV?hl=en

> <http://groups.google.com/group/CacheMV?hl=en>

[Roland]

Ahhh, it finally dawned on me.  After creating the SQL gateway I needed to create a Linked Table from the management portal.  Then I just use that Linked Table name as my table name.  Now I just have to figure out the most elegant way of running an SQL command in MVBasic.

[Rich Taylor]

Well, you can use the %SQL.Statement api to create dynamic SQL for one.  Alternately, once you have the linked table setup it becomes a proxy object in Cache.  Therefore you could interact with the remote tables through object syntax rather than SQL.  SQL tends to perform better, but if what you are doing is working with single records at a time then an object syntax might be more in-line with the programming you need to do.  You work with this proxy class in MV as you would with any other Caché object.

 

Richard S Taylor
Sales Engineer
InterSystems Corporation
Office: 443-340-8614
FAX: 440-815-5805

--

[Michael Cohen]

I am working on an example… hopefully later today

 

From: intersy...@googlegroups.com [mailto:intersy...@googlegroups.com] On Behalf Of Roland
Sent: Wednesday, September 19, 2012 11:45 AM
To: InterSy...@googlegroups.com
Cc: intersy...@googlegroups.com
Subject: Re: [InterSystems-MV] Re: A Caché of Tips: SQL

 

Ahhh, it finally dawned on me.  After creating the SQL gateway I needed to create a Linked Table from the management portal.  Then I just use that Linked Table name as my table name.  Now I just have to figure out the most elegant way of running an SQL command in MVBasic.

--

[Michael Cohen]

Caché can connect to an arbitrary ODBC DSN if you supply the user id and password, as well as via an SQL Gateway where the id and password are stored in Caché.  I created one called 'samples'

 

The difference is:

DSN:

CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()

DSN="CACHE samples"

UserId=""

Password=""

ST=CONN.SAMPLES->Connect(DSN,UserId,Password)

 

Gateway:

CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()

Gateway="samples"

ST=CONN.SAMPLES->GetConnection(Gateway)

 

You can perform the normal ODBC calls to access the remote database.  You do not need to create a Linked Table for ODBC, but would need one for objects access.

 

 

There is an overhead associated with ODBC connect and disconnect, similar to the overhead of opening and closing MV file pointers.  Developers typically open their files once and store the file pointers in COMMON to be available as needed.  The following example does the same for my ODBC connection.  There is a ODBC.SETUP routine to open the connection, and a ODBC.TEST routine to use it.  Finally there is MAINTEST to put them all together in a mini app.  Note that ODBC.SETUP includes comments on how to connect to a DSN with id and password, but the live code uses that info stored via the Management Portal in an SQL Gateway Connection.  I threw in a bit of error checking to highlight some of what is available.

 

FUNCTION ODBC.SETUP

#PRAGMA ROUTINENAME=ODBC.SETUP

COMMON /ODBC/ CONN.SAMPLES,HSTMT.SAMPLES

CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()

*DSN="CACHE20122638 samples"

Gateway="samples"

UserId=""

Password=""

*ST=CONN.SAMPLES->Connect(DSN,UserId,Password)

ST=CONN.SAMPLES->GetConnection(Gateway)

IF ST<>1 THEN CRT "GetConnection failed"; "%SYSTEM.Status"->DisplayError(ST); CRT CONN.SAMPLES->sqlcode; ABORT

HSTMT.SAMPLES=""

ST=CONN.SAMPLES->AllocateStatement(HSTMT.SAMPLES)

IF ST<>1 THEN CRT "AllocateStatement failed"; "%SYSTEM.Status"->DisplayError(ST); CRT CONN.SAMPLES->sqlcode; ABORT

RETURN ST

 

 

 

FUNCTION ODBC.TEST(SQLStatement)

#PRAGMA ROUTINENAME=ODBC.TEST

COMMON /ODBC/ CONN.SAMPLES,HSTMT.SAMPLES

ST=CONN.SAMPLES->Prepare(HSTMT.SAMPLES,SQLStatement)

IF ST<>1 THEN "%SYSTEM.Status"->DisplayError(ST); CRT CONN.SAMPLES->sqlcode; RETURN ST

ST=CONN.SAMPLES->Execute(HSTMT.SAMPLES)

IF ST<>1 THEN "%SYSTEM.Status"->DisplayError(ST); CRT CONN.SAMPLES->sqlcode; RETURN ST

RETURN ST

 

 

#PRAGMA ROUTINENAME=MAINTEST

COMMON /ODBC/ CONN.SAMPLES,HSTMT.SAMPLES

DEFFUN ODBC.SETUP

DEFFUN ODBC.TEST(SQLStatement)

* SETUP ONCE

ST=ODBC.SETUP()

* EXECUTE MULTIPLE TIMES WITH SAME CONNECTION IN COMMON

SQLStatement="UPDATE SAMPLE.PERSON SET Home_State = 'xx' WHERE Home_State = 'MA'"

ST=ODBC.TEST(SQLStatement)

SQLStatement="INSERT INTO SAMPLE.PERSON(SSN,Name) VALUES('":FMT(RND(1000000000),"%%%-%%-%%%%"):"','Smith,John')"

ST=ODBC.TEST(SQLStatement)

CRT "END OF TEST"

 

 

Does this provide the sort of MultiValue support for ODBC that you are looking for?  Note that I connected to the Sample.Person table included with Caché using the default ODBC DSN created when I installed Caché, but the same techniques should work for SQL Server and other databases.

 

 

 

 

 

 

 

 

[Jason Warner]

Great example Michael. I had some questions, but you nailed them all.

Jason

On Wednesday, September 19, 2012 1:55:47 PM, Michael Cohen wrote:
> Caché can connect to an arbitrary ODBC DSN if you supply the user id
> and password, as well as via an SQL Gateway where the id and password
> are stored in Caché. I created one called 'samples'
>
> The difference is:
>
> DSN:
>
> CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()
>
> DSN="CACHE samples"
>
> UserId=""
>
> Password=""
>

> ST=CONN.SAMPLES->*Connect*(DSN,UserId,Password)

>
> Gateway:
>
> CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()
>
> Gateway="samples"
>

> ST=CONN.SAMPLES->*GetConnection*(Gateway)

[Michael Cohen]

I'm glad Jason is happy.

But I did leave out other ODBC options, such as statement parameters and prepare/execute.

Are those (or others) of interest to anyone?

-----Original Message-----
From: intersy...@googlegroups.com [mailto:intersy...@googlegroups.com] On Behalf Of Jason Warner
Sent: Wednesday, September 19, 2012 4:19 PM
To: intersy...@googlegroups.com
Subject: Re: [InterSystems-MV] Re: A Caché of Tips: SQL

[Roland]

What Jason said, the examples answered my questions perfectly.  Thanks, Michael!

The difference is:

DSN:

CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()

DSN="CACHE samples"

UserId=""

Password=""

ST=CONN.SAMPLES->Connect(DSN,UserId,Password)

 

Gateway:

CONN.SAMPLES="%Library.SQLGatewayConnection"->%New()

Gateway="samples"

ST=CONN.SAMPLES->GetConnection(Gateway)