How I miss ROWNUM sometimes...

Today I needed to calculate the average from a field from a table in Intersystem's Caché. The logic was very simple: I just neede the average of prices of last N purchases, after some date.

Oracle has the pseudo-column ROWNUM, which makes my problem very easy to be solved. I would have something like:

SELECT AVG(PRICE)
FROM PURCHASE
WHERE DATE > '01/01/01' AND ROWNUM <= 20
ORDER BY DATE DESC

In this simple example, we calculate the average of price of last 20 purchases made after day 01/01/01. Quite simple, no?

I searched for something similar in Caché, but I couldn't find it. I found some related stuff, like a variable for the number of lines a query resulted, or the TOP statement, used within SELECT. Unfortunatelly, nothing helped me much, so I had to open a cursor (or I could have used a ResultSet, if I wanted to), and iterate through the results, and calculate the average myself.

So, the query took this form:

SELECT TOP 20 PRICE
FROM PURCHASE
WHERE DATE > '01/01/01'
ORDER BY DATE DESC

See the 'TOP 20'. It that for the query I just want the first 20 records. Unfortunatelly, we can not use it on WHERE statement, so we can not use it for the AVG function.

I will not describe how I iterate through a cursor and calculate the average, since it's quite trivial, and I'm tired now.

Today, I'm done.

Execute code dinamically with Caché

...Or How to evaluate and execute an user input's expression

The scenario: with the application, an user can input some math expressions, using variables previously defined (by himself or another users). These variables might be simple or they might depend on some parameter. An example of expression is below:

(0.50 * ([VAR1(I)] / [VAR1(0)])) + (0.50 * [VAR2])

where:

• VAR1(I) is a variable, which depends on parameter "I".


• VAR1(0) is a variable (in fact, the same variable above), but depends on another parameter, "0".


• VAR2 is a simple variable.

By convention, variables are written between "[]". Most variables are in fact, historical series of values, and those parameters indicate the specific time/period to be considered.

The problem: given a time interval, I need the value the expression evaluates to. Or, with another terms, I need to execute the expression, as if it was part of the program. Some other script languages have the eval function, which takes a text (i.e., an String) and executes it as if it were program code. In this link, an example of JavaScript's eval(). With Caché Object Script, we have the command XECUTE, which does the same thing, it gets the content of an string and executes it, i.e., it does macro substitution.

Well, even with this feature, I still need to identify within the %String, each variable. To make this, I wrote a little function, which I put in a .MAC file:

(A snapshot of the function, the text source code is here.)

For this function, we pass as parameters: the expression, two characters used to delimit what is considered a variable, (in our case, the characters "[" and "]"), a %String to be place where each variable was, and another character to delimit parts of %String (something used a lot with MUMPS-like systems, to create lists - without list functions - see here some discussion.)

Using the expression above in this function, with "?" as the %String to replace the variables, and "~" as delimiting character, we get:

(0.50 * (? / ?)) + (0.50 * ?)~VAR1(I)~VAR1(0)~VAR2

The next step, we get from the system the value of each variable, and replace it within the expression. I will not show these steps, since they are very specific to our problem. After that, our expression String is something like:

(0.50 * (12.3 / 10.88)) + (0.50 * 10)

Note that the expression is mathematically correct, and the precedence of operator is explicit with the use of parenthesis. This REALLY is something to be done, since Caché DOES NOT follow math conventions of operators precedence, as I already posted.

Now, we can finally execute the expression. First, we set the command SET inside a %String, as follows:

SET someVariable = "SET result = " _ stringWithExpression

And then we call XECUTE:

XECUTE someVariable

That's it! The value of the evaluated expression is in variable "result"! Using XECUTE is very handy, but you might pay attention to not abuse it's use, since it can turn your code into a really great mess!

Uploading files with Caché and CSP

Uploading files with Caché and CSP - or... My first CSP

As I already said here (or not), my work is almost all done with Intersystems' Ensemble (and consequently with Caché, since Ensemble is built upon it). Therefore, I am not generally involved with user interfaces and these kind of stuff, but this week I needed to do a web page to upload files to my server. So, lets learn some CSP - or Caché Server Pages. In fact, I didnt reaaaally learned them, I just got the stuff to do what I needed. And I thought it'd be nice to share the experience with you ;)

As CSP Introduction page says, you have two paths if you´re willing to work with CSPs: you can write Html pages and embed them with csp tags, much like JSP (Java Server Pages) or PHP, saving them with .csp extension; or you can write a Caché's class, where you can output the html, the same approach of Java Servlets.

Within examples (in namespace SAMPLES of Caché) there is a .csp file with an example of upload (as you can guess, it is csp/samples/upload.csp). Taking this as base, I adapted the necessary to my needs.



(Upload example page, and the resulting page after an upload)

Well, as I couldn't place the source code here at blogger, in a good way, I will put an image, and let a link to source code text.

(Image with source code)

The logic behind this csp page is very simple: when you open this page, calling from a parent window, you pass the request parameter 'campo', if not, it will show you nothing. Then, the csp code checks if a file was uploaded, if not, it renders an html form with fields for upload (the form's submit points to the same csp); if the file was already uploaded, it sets the file name to an html field from the parent window who called this csp, and shows a link to close this current window.

Lets view with more details some of this.

<CSP:CLASS INCLUDES="SOMEINCFILETOINCLUDE">

Here we point to an .INC file (without the inc extension) that we will use in the page, i.e., we are including this INC file. In Caché, INC files are used to define macros (like C/C++ macros), and are very used to both implement routines and define constants.

/***********************************

<csp:if condition='($data(%request.Data("campo",1)))'>

Here we show an specific tag from csp, which means a condition, the famous 'if'. The condition is the value of attribute 'condition', of 'csp:if' tag, and it may contain COS (Caché Object Script) code, returning true or false. In our code, we are just checking if the http request (%request object) has as one its parameters a field named 'campo'. More info about %request object can be found at %CSP.Request class doc page.

/***********************************

<csp:if condition='($data(%request.MimeData("FileStream",1)))'>

Other if, but a little different. This time we do not check the http request parameter with %request.Data, but with %request.MimeData. The reason is because this time we are checking if a file was sent, using MIME.

/***********************************


File uploaded: <b>#(%request.MimeData("FileStream",1).FileName)#</b><br>
Size: <b>#(%request.MimeData("FileStream",1).Size)#</b><br>
Type: <b>#(%request.MimeData("FileStream",1).ContentType)#</b><br>


When we sent a file, %request.MimeData("PARAMETERNAME",1) return an object %CSP.BinaryStream. The above code shows on screen some info that we can get from this stream object. Note that the values to be rendered on the screen are between '#(' and ')#'.

/***********************************

<script language="Cache" runat="server">

Here we declare a code block of COS to be executed at the server. In this block, if we want something to be shown in the resulting html, we must write at the standard output, using Write command.

/***********************************

set dname = $$$SomeDirectory

Still within the block we opened above, here we create and set a value to the variable 'dname'. This value is a macro, which is indicated by the three $ ($$$SomeDirectory). This macro should be defined in some of .INC files we imported, like commented above.

/***********************************


Set stream=##class(%FileBinaryStream).%New()
Set stream.Filename= dname _ fname

do stream.CopyFrom(%request.MimeData("FileStream",1))
do stream.Flush()
do stream.SaveStream()


Here we create an stream to a file (class %FileBinaryStream), and we set it to variable 'stream'. Then we set the file's name, and use the method CopyFrom, to copy the content of the stream sent through http, to the file.

/***********************************

<csp:else>

Here we finish the 'if' block, and start the 'else' block.

/***********************************

<form enctype="multipart/form-data" method="post" action="este_arquivo_csp.csp">
Arquivo: <input type=file size=30 name=FileStream>
<p>

<script language="Cache" runat="server">
New bytes
Set bytes="<input type=""hidden"" name=""campo"" value=""" _ %request.Data("campo",1) _ """ > "
Write bytes,!
</script>

<p> <ul><input type="submit" value="Upload file"></ul> <p>
</form>

The 'else' block has the definition of an html form, to upload the file to the server. Note that inside de html form definition, we mix some Caché code, to place a hidden field within the form, to keep the value of parameter 'campo', which was passed. This is one solution, another one (maybe more 'elegant') would be use sessions, but as this is very simple case, I didn't check how to manage sessions with CSPs (but of course, you can explore it yourself).

/***********************************

As this post got bigger than I expected, I will finish it here. Within CSP file there are other features (as some javascript, and COS directory apis), but I will not comment them now. Besides, as I simplified the csp a lot, and I removed all stylish stuff. For you to use it, you will have to change some things, like the import of .inc file.

That's all folks.

Some curious stuff from Caché

As Intersystem's Caché Object Script is a "typeless language", sometimes it produces curious results, especially if you, reader, is more used to math notations than "stuff" of IT.

As an example, lets open a Caché's terminal window, and declare two variables*:

USER>set x = "5.10"

USER>set y = "5.1"

USER>write x
5.10
USER>write y
5.1

Remembering that Caché, (as good and old C) treats 1 (one) as TRUE, and 0 (zero) as FALSE*:

USER>write x = y
0
USER>write x > y
0
USER>write x >= y
1




It all means that (x = y) is false, (x > y) is also false, but (x >= y) is true! Or, "x" is not equals to "y", and "x" is not greater than "y", but "x" is greater than or equal to "y", hohohoho 8-)

Lets give some thoughts about it:

• The first statement (x = y) is false because in this case, Caché assumes an iguality test between two Strings, and String "5.1" is different from String "5.10"
  
  
• The second statement (x > y) is false, not because Caché compares String's lengths (x < y also returns false), but because when the operator ">" is used, Caché dynamically casts the variables' values to numeric values, and because "x" IS NOT GREATER THAN "y" ("x"'s value is numerically EQUAL TO "y"'s value, since 5.1 = 5.10), the overall result is false.
  
  
• Well, by now you might already have figured out why (x >= y) returns true, right? When using "<", "<=", ">", ">=" operators, Caché tries to cast the variables to numeric values, and because numerically "5.1" IS EQUALS to "5.10", the expression (x >= y) is true.
  

All this might confuse some people not used to programming, just as Caché's operators precedence, which I already talked about.

* Some tips: the "set" command is used to initialize/assign a value to a variable, and the "write" command (just as it says) writes to the current output device, in our case, the terminal window.