0

Background

Hi,

I work with a Unix-based application that uses an Intersystems Caché database. Since I'm not that familiar with Caché, it wasn't until recently that I found out I could type...

$ cache

...to enter the database. From here, I found out I could access a number of things like the %FREECNT report, the ^DATABASE routine (to view/modify the size and other properties of the database), and $SYSTEM.SQL.Shell().

Since I found the $SYSTEM.SQL.Shell(), I've found a number of things I can use it for to obtain info about the database, specifically running processes using the %SYS.ProcessQuery table.

I'm able to run queries successfully - for example:

USER>ZN "%SYS"
%SYS>D $SYSTEM.SQL.Shell()
SQL Command Line Shell             #Comment - Sql Shell Intro text
--------------------------------
Enter q to quit, ? for help.
%SYS>Select PID As Process_ID, State As Process_Status From %SYS.ProcessQuery

The above query will return results in this format:

Process_ID        Process_State
--------------------------------
528352            READ
2943582           HANG
707023            RUN

3 Rows(s) Affected
--------------------------------

Question

Considering the background identified above, I'm looking for a way to return just the results without the "SQL Command Line Shell" intro text, the column names, or the row count footer. When I write a .ksh script in Unix to connect to Caché and run a query, like above, I return the results, along with the following text that I don't want included:

SQL Command Line Shell
--------------------------------
Enter q to quit, ? for help.

Process_ID        Process_State
--------------------------------

3 Rows(s) Affected
--------------------------------

Additional Info

I realize I could use Unix commands to filter out some of the text using awk and sed, but I'm looking for something a little easier/cleaner way that might be built-in. Maybe something that has a silent or no_column_names flag, like the example in this LINK.

My end game is to have a script run that will obtain info from a query, then use that info to make changes to the database when certain thresholds are met. Ultimately, I want to schedule the script to run at regular intervals, so I need all the processing to occur on the server instead of creating a separate Client app that binds to the database.

Community
  • 1
  • 1
user2063351
  • 503
  • 2
  • 13
  • 31

2 Answers2

0

You want to create a Cache Routine for this. You can do this in Cache Studio. http://docs.intersystems.com/ens20131/csp/docbook/DocBook.UI.Page.cls?KEY=GSTD_Routines

In the Routine, you want to use either Embedded SQL or Dynamic SQL to run the query, and iterate through the results, and print them using WRITE. I would recommend Dynamic SQL, as it will be more flexible in the future.

Introduction to SQL: http://docs.intersystems.com/ens20131/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_intro#GSQL_intro_embeddedsql

Dynamic SQL Information: http://docs.intersystems.com/ens20131/csp/documatic/%CSP.Documatic.cls?APP=1&LIBRARY=%SYS&CLASSNAME=%SQL.Statement

Embedded SQL Information: http://docs.intersystems.com/ens20131/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_esql

Brandon Horst
  • 1,921
  • 16
  • 26
  • Thanks...I've read through some of that documentation. Can I do this without creating a routine? Can I create dynamic SQL and output the results to a text file from the terminal? I know I can perform WRITE statements from the terminal, so I assume I can perform some ObjectScript commands/functions that will allow me query a table and iterate over the result set. I don't have Caché Studio available, as I believe the vendor of the application, which uses Caché as its database, didn't include it in their installation. – user2063351 Sep 12 '13 at 17:47
  • Yes, Dynamic SQL can be used from the Terminal. All of the code in my 3rd link can be run just as easily from the terminal. Embedded SQL cannot be run from the Terminal. – Brandon Horst Sep 12 '13 at 17:49
  • Just for clarification, are routines stored in the database once they are created or can I create them as separate files on the server and refer to them? I haven't been able to find answers to this in the documentation and that's kept me from looking more at the routine option. Also, can you write loop statements, like the ones in your third link, at the terminal to iterate over the result set? Sorry, I know these are different questions, but the answers will ultimately help me with my original question. – user2063351 Sep 12 '13 at 19:17
  • Routines are always stored in the database. They can be exported to the file system with $system.OBJ.Export(), and re-loaded with $system.OBJ.Load() if necessary. You can certainly write loop statements... you can use any Cache ObjectScript functionality in a routine. – Brandon Horst Sep 12 '13 at 20:19
  • What is the syntax for writing, for instance, a while loop at the terminal? When I try to write `While rs.%Next() { WRITE rs.PID " " rs.State } and press 'Enter', it throws a syntax error. Can I create routines, with loops (especially multiline loops), from the terminal? – user2063351 Sep 12 '13 at 20:48
  • You have to use underscore("_") for merging strings or comma in command Write http://docs.intersystems.com/cache20131/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_cwrite – DAiMor Sep 13 '13 at 07:12
0

You can create duplicate class of %SQL.Shell in your own namespace and you can edit it..If you want as Rountine means you can call this method ...%Go() from your routine.

chandru
  • 11
  • 1