Alex Le’s Blog

I’m back from a period of hibernation. Lots of exciting stuff and new things, just barely have enough time to sleep and catch up with all the pending projects that I’ve been crazily taking on. Anyway, today I’d like to share something that maybe useful for someone out there: how to retrieve live stock quote data using Yahoo quote service with ColdFusion.

I was working on a project which originally utilizes the WebServiceX.net’s [[http://www.webservicex.net/stockquote.asmx/GetQuote|StockQuote web service]]. Things were working fine until WebServicex.net is down, as of today, March 22nd, 2007, with a big fat “Service Unavailable”

“Crap!” was what I said to myself. Luckily my work buddy forwarded a [[http://aspalliance.com/articleViewer.aspx?aId=112&pId=#Page2|link]] to an article on how to access the Yahoo Stock Quote service using ASP.NET (C#). Bingo! This was exactly what I needed. Based on the article, I was able to hack out the code using ColdFusion to retrieve stock info in just a few lines of code (or markup since I don’t really consider ColdFusion a real “language”)

===== Yahoo Stock quote CSV service =====
First of all, I didn’t even know if this service ever exists. I don’t really know where the official Yahoo documentations for the Stock Quote CSV service is (anybody knows the link please let me know). More ironically, I used Google to search for tutorials on how to use the Yahoo Stock Quote API. Anyway, this is the URL to request a CSV (comma-separated values) file containing stock information for a particular symbol, in this case, YHOO (I think I do have to give Yahoo some credits for the free service)http://quote.yahoo.com/d/quotes.csv?s=YHOO&f=l1c1v
with
* **s=SYMBOL**: with SYMBOL is the actual stock symbol
* **f=REQUESTED_FORMAT_STRING** where the REQUESTED_FORMAT_STRING is a string of letters representing what fields to be requested. The oder of the letters will be used as the order of the fields in the returned CSV.\\ I messed around and found out about the meaning of the letters used in the **f** parameter. If you want the full information about the symbol, you can use **f=sl1d1t1c1ohgvj1pp2wern** instead.http://quote.yahoo.com/d/quotes.csv?s=YHOO&f=sl1d1t1c1ohgvj1pp2wern Don’t sweat, here comes the explanation for each letter (or letter-digit pair)
* s: the symbol name
* l: last value (or current price). If you use l alone, you will get back a string similar to Mar 22 - 31.26
* l1: (letter l and the number 1) to just get the last value of the symbol
* d: d alone will give you 0, while d1 will return the current date (e.g. 3/22/2007)
* t: t by itself will request the yahoo-generated chart. However, you will get back the chart image with a whole bunch of other HTML garbage, e.g. src=http://chart.yahoo.com/c//y/yhoo.gif" alt="Chart">


* t1: the time of last update, for example 4:00pm.
* c: the change amount. Can either be used as c or c1.
* o: opening value
* h: high value
* g: low value
* v: volume
* j: 52-week low.
* j1: the market cap. This is string like “42.405B” for $42 billion. Man… that can buy **a lot** of hamburger or bowls of phở
* p: after hour price (?)
* p1: (?)
* p2: change percentage e.g. “-0.10%”
* w: 52-week range
* e: EPS (Earning per share)
* r: P/E (Prince/Earning) ratio
* n: Company name

(This list is hand-compiled by me based on my own interpretation. If you find any explanation being inaccurate, please let me know)

===== The Code =====
The code is pretty simple: we will read from the result the Yahoo CSV service using the tag into a variable. This variable will then contain the comma-separated values list of what we put in for the f parameter. Back to our example, the URL we are requesting is http://quote.yahoo.com/d/quotes.csv?s=YHOO&f=l1c1v, which will return to us a string 31.26,-0.03,13249801. Since the response is in CSV format, we can utilize the ColdFusion List functions like ListLen(), ListGetAt() to access the values.

Here is the CFML code snippet





#stockInfo#






* First, we construct the Yahoo Stock Quote service URL. We are requesting for last price, change amount, and the stock volume.
* Next we use the cfhttp tag to request the url, then grab the content into the cfhttp.FileContent variable and assign to the stockInfo variable. stockInfo will contain something similar to 31.26,-0.03,13249801
* To get the different values in the list, we use ListGetAt() with the appropriate index. And remember, indices of Array and List in ColdFusion start at 1, not 0.

That’s it!

===== Final Remarks =====
I hope you will find this useful. Thanks to Yahoo for providing a free stock quote service. I know I can **abuse** this CSV service as much as possible before getting banned because it’s Yahoo. Any services other than those of Yahoo, Google, Microsoft, I will probably have some guilty thoughts before doing anthing. But it’s Yahoo so I really don’t have no guilt abusing it. Just kidding though. I do appreciate the free (as in free beer) stock quote service.

===== References =====
* Bromberg, Peter, PhD. [[http://www.eggheadcafe.com/articles/20010404.asp|Build a C# Stock Quote WebService and Client using the WebService Behavior HTC (Part I)]]
* Perry, Jason. [[http://aspalliance.com/articleViewer.aspx?aId=112&pId=#Page2|Building a Yahoo stock quote ticker]]

At work, I had to run some updates on the Oracle Database so I took the chance to learn some PL/SQL. It turns out to be very handy. I didn’t have to write any .NET application just to be able to access Oracle, instead I can write a small Stored Procedure and run it to get the result I want.

My PL/SQL knowledge was 0 when I started yesterday, so I borrowed the Oracle 9i PL/SQL Programming book from a work buddy and started digging in. After half a day, I was able to get my little stored procedure running using cursors and reference cursors. And before I get a chance to forget the PL/SQL syntax, I am writing this summary about PL/SQL. It is not too detailed but enough to allow anybody who doesn’t know anything about Oracle PL/SQL get started in half an hour. So say if you know some sql from working with MySQL, you can use this guide to get started fairly quickly. Enjoy!

===== Getting started =====
* Each line in PL/SQL ends with semi-colon “;”
* PL/SQL is case-insensitive
* There are keywords like SELECT, CURSOR, TYPE, etc., so my convention is to CAPITALIZE those KEYWORDS. This way your eyes can pick out the keywords easier when you are debugging the code.
* Here are the naming convention, which is optional but will help you distinguish the variables better
* Local built-in type variables within a stored procedure are prefixed with v_. \\ e.g. v_inspectionId
* Cursors have prefix “c_”\\ e.g. c_Inspections
* Custom type variables have prefix “t_” \\ e.g. t_InspectionRecord
* A type for a dynamic cursor (REF CURSOR) has the Ref suffix \\ e.g. t_ViolationRef
* A record variable has the “Record” suffix \\ e.g. v_InspectionRecord
* Single-line comment follows a double-dash (- -), while the favorite C-style comment style is used for a block. Unfortunately, nested C-style commenting is not allowed.
-- This is a comment
SELECT * FROM someTable;
/*
This is a block comment
Hello! still in a comment
*/
SELECT * FROM someOtherTable;

===== Creating a stored procedure =====
First, let’s start with creating a new stored procedure. A procedure in PL/SQL has this overall structure:

CREATE OR REPLACE PROCEDURE procedure_name IS
... variable declarations
BEGIN
... your PL/SQL statements go here
END procedure_name;


===== Variables =====
==== Primitive-type Variables ====
Before we use any variable, we need to declare it first. We put the variable declarations after the CREATE OR REPLACE and before the BEGIN block. Variable name has to be less than 30 characters. We can declare the variables like this


v_number NUMBER;
v_description VARCHAR2;


In case we’d like the variable to describe a data row, we can declare


v_someVariable someTable.someColumn%ROWTYPE;


**%ROWTYPE** is the special property of the column to describe its data type.

==== CURSORS ====
Cursors can be used as a pointer to the result set returned by a SELECT. We can loop through a cursor and do calculations and so on. There are 2 types of cursor: Static Cursor and Dynamic (Ref) Cursor.

=== Static Cursors ===
Static cursor is declared in the declaration block with a specific SELECT statement. Something like this


-- declaration block
CURSOR c_Individuals IS
SELECT * FROM Individuals;

-- .... the rest of the procedure


What we just did is declare a new Cursor called “c_Individuals”, which is a pointer to the result set of the “SELECT * FROM Individuals” statement. Next, we declare a variable called v_individualRecord, whose type will be the return row of the c_Individuals cursor. Then later on in the actual body of the stored procedure, we open the CURSOR (e.g. execute the SELECT and have the Cursor pointed at the first returned row), then have a loop to fetch the Cursor into the v_individualRecord variable. However, if we don’t have no rows left in the result set, we will bail out from the Loop.

=== Reference (dynamic) Cursors ===
A Reference Cursor is a cursor that can be used to to fetch a SELECT statement within the body of the stored procedure. In contrast, for a static cursor, we can only specify which SELECT the static cursor is pointing to in the declaration area of the stored procedure.

This is the general usage for Reference cursor.


-- declaration block
TYPE t_Individuals IS REF CURSOR RETURN Individuals%ROWTYPE;
c_Individuals t_Individuals;
c_IndividualRecord c_Individuals%ROWTYPE;

-- the rest of the stored procedure

First, we need to “invent” a new data type for the reference cursor with the TYPE declaration. We indicate that this t_Individuals type is a Reference cursor and this particular reference cursor will return a row containing all columns from the Individuals table. Next, we declare a cursor using the new t_Individuals type. Now c_Individuals is officially a dynamic cursor. Later on in the body block, we can open the c_Individuals cursor using a SELECT * statement.

Many of you will ask already, “what if I only need a few columns, or the SELECT of the cursor is a complicated JOIN statement, then what is the return type of t_Individuals?” In this case, our declaration for the reference cursor is a little more involved.

Say we need the cursor to return the result of a JOIN between the Individuals and the Departments tables to find out what department an individual belongs to.


-- declaration block
/* t_IndividualRecord defines the data returned by the SELECT & JOIN between Individuals and Departments */
TYPE t_IndividualRecord IS RECORD (
name Individuals.Name%TYPE,
department_id Individuals.Department_id%TYPE,
department_name Departments.Department_Name%TYPE,
);
TYPE t_IndividualRef IS REF CURSOR RETURN t_IndividualRecord;
c_Individuals t_ViolationRef;
v_IndividualRecord c_Individuals%ROWTYPE;

So first, we declare a new type called “t_IndividualRecord”, which is a Record. A record is a data row with predefined columns. We only need 3 columns for this particular record. Next, we define a reference cursor t_IndividualRef which returns a t_IndividualRecord. Then c_Individuals is our cursor and v_IndividualRecord is the variable to hold our information. Yup, that’s it to use dynamic cursors!

First, make a new user-defined record, then declare a reference type cursor, then the cursor itself, and lastly the variable to hold the returned value of the cursor.

===== Control Structures & Loops =====
PL/SQL supports most basic control structures.
* IF-THEN-ELSE
IF boolean_expression1 THEN
-- ...
[ELSEIF boolean_expression2 THEN
-- ... ]
[ELSE
-- ... ]
END IF;

* CASE
CASE v_IndividualRecord.Department_id
WHEN 1 THEN
-- ...
WHEN 2 THEN
-- ...
WHEN 3 THEN
-- ...
[ELSE
-- ...]
END CASE;

* CASE with no default test expression
CASE
WHEN v_IndividualRecord.Department_id = 1 THEN
-- ...
WHEN v_IndividualRecord.Department_id = 2 THEN
-- ...
WHEN v_IndividualRecord.Department_id = 3 THEN
-- ...
[ELSE
-- ...]
END CASE;


* LOOP
LOOP
-- ...
EXIT [WHEN condition];
END LOOP;

* WHILE
WHILE condition LOOP
-- ...
END LOOP;

* FOR
FOR counter IN [REVERSE] start_index .. end_index LOOP
-- ...
END LOOP;
Example
FOR v_Counter IN 1..100 LOOP
-- ...
END LOOP;
The v_Counter index variable doesn’t have to be declared ahead of time. It’s implicitly declared as BINARY_INTEGER by PL/SQL. If the REVERSE keyword is used, our loop will run backward.

* GOTO \\ I haven’t heard any good things about Goto. The overall opinions on GOTO is that using GOTO extensively is bad practice. Nonetheless, GOTO can be used for error-handling.

===== Debugging and Outputting to log =====
Sometimes we need to debug the stored-procedure to see what’s going on. PL/SQL has very nice line debugging capabilities. I am using the PL/SQL Developer version 7.0.3 by AllaroundAutomations and find that it’s pretty easy to use. However, I wish that the shortcuts for the line debugging are customizable, or made consistent with VisualStudio, so that I don’t have to mentally switch between 2 sets of shortcuts.

To write out to the DBMS output log, you will have to add DBMS_OUTPUT.ENABLE(1000000); before writing out the first log entry, otherwise Oracle will throw you an error. After setting DMBS_OUTPU.ENABLE(1000000), you can write an entry by using DBMS_OUTPUT.PUT_LINE( 'Hello World!' );. A variable can also be used inside the .PUT_LINE() like thisDBMS_OUTPUT.PUT_LINE('Individual: ' || v_IndividualRecord.Name || ' is in department ' || v_IndividualRecord.Department_Name);

However, if you are in a debugging session in PL/SQL Developer, you won’t see the entry in the DBMS Output window until after you are done with the debugging session. Maybe I haven’t found a “flush” method for DBMS_Output yet. Nonetheless, the Watch window always comes handy during debugging.

===== Views =====
Views are named queries, e.g. a view represents a derived table from a SELECT statement. End users can consider a view as a table. As views are treated like tables, we can also run query against the view such as SELECT and UPDATE, making our life a bit easier on complex queries. In comparison with an actual table, a view doesn’t contain data thus we cannot create index on a such view.

To create a view, we use

CREATE VIEW view_name AS
SELECT sub-query

[WITH CHECK OPTION];


The With CHECK OPTION means we cannot update the view if the select sub-query doesn’t retrieve the updated rows.

And to drop a view, we just do

DROP VIEW view_name;


Later on within our query, stored procedure body, or function body, we can refer the view directly by calling out its name.

===== Some “post-it-notes” for SQL syntax in Oracle =====
Here are some tips for SQL syntax in Oracle.
* Select a first few rows
SELECT * FROM Individuals WHERE ROWNUM <= 50;ROWNUM is the special keyword to limit how many rows to be returned, similar to the TOP keyword in SQL Server.
* Columns with space between words can be put in double quote like “COLUMN NAME”. The equivalent in SQL Server is the square brackets.
* Keyword for Column alias is “**AS**”: \\ e.g. SELECT (1+1) AS one_and_one;
* For table aliasing, it’s “TableName New_Alias” \\ e.g. SELECT new_alias_for_this_table.* FROM this_table new_alias_for_this_table
* SELECT with conditional logic: In case you’d like to select a column value and do some logic with it, here is the sample syntax SELECT (CASE WHEN t.name = 'Alex' THEN 'Alex Le' ELSE t.name END ) AS name from table t
* to be continued

===== Final Remarks =====
I hope that you’ll find this quick primer guide helpful. Sometimes it’s the syntax that bogs you down when you start to work in a new environment, and this is where the guide comes in. I put this together so that everyone who has to work in PL/SQL can quickly pick up the syntax and get started right away in a minimal amount of time.

Comments, suggestions, and corrections are very welcome.

===== Reference =====
* Urman, Scott. “**Oracle9i PL/SQL Programming**”. McGraw-Hill/Osborne, Oracle Press. 2002.
* Upadhye, Suhas. “**Oracle Views: What can they do for you?**”. October 1999. Retrieved from [[http://www.cdoug.org/docs/views-1099.pdf]] on Feb 13, 2007

I was working on some javascript stuff for a project and ran into this problem with setInterval(): for IE 6, calling setInterval() to an object’s method results in the wrong scope. In the object’s method, the alfamous **this** keyword now refers to **window**, instead of the conventional “this” as in “**this object**”. So let me give you some more details on this and a free, amazingly simple solution to this head-ache.

**SPOILER:**\\ the solution uses eval(). if you are allergic to eval(), don’t take my advice.\\ **END SPOILER**

===== The code =====
Let’s look at some code and we’ll have a little discusssion. Here’s the psuedo-code for a fictional TimeTicker object, which is supposed to automatically update a “clock” on the screen every second.


/* A. Constructor */
function TimeTicker()
{
/* A.1 - populate internal variable here, like hour, minute, second, etc. */
...

/* B.2 - now update the screen */
...
}

===== The problem =====
The above psuedo code will sadly **FAIL** in both IE and FF (not because it’s only psuedo-code!), as the ‘’setInterval()” invocation has **totally changed the scope** of the polled function. First of all, let’s look at ”**A.2**” from above. Calling ”this” within the ‘’setInterval()” will change the reference of ”this” to some other object (which I think is ”this” now refers to ”window”.) Hence calling ‘’setInterval( this.updateTime, 999 )” fails in both Firefox and IE 6, as it means that the ‘’setInterval()” will poll the ”window.updateTime()” function every 999 ms. That’s not what we want, and ”window” object could care less about our ”updateTime()” since we suddenly found ourselves in the wrong scope.

===== The Firefox’s Solution =====
For firefox, the fix is a small rewrite of how we would call the polled-function, updateTime(). The setInterval() function in Firefox is improved and we can now pass an extra parameter to it, and that’s exactly what we will do. We pass in the current object’s reference as ”this” to the a proxy function, which in turn makes a call to the updateTime(). I found this solution through [[http://www.klevo.sk/javascript/javascripts-settimeout-and-how-to-use-it-with-your-methods/|Klevo’s blog]], a web developer from Slovakia.

We will have to rewrite ”**A.2**” as follows:


/* A.2 */
this.timer = setInterval ( function( that ) { that.updateTime(); }, 999, this );


What we just did is we create a proxy function, whose only parameter is ”that”, a pointer to another object. Within this proxy function, we can make invoke all of “that”’s methods! Basically we migrate the scope of ”this” as a reference to the current ”TimerTicker” object into the polled proxy function via a pass-by-reference ”that” parameter. Notice the extra 3rd parameter of ‘’setInterval()”: we are passing ”this”, in this case, a reference to the current ”TimeTicker” object, as the only parameter of the newly created proxy function. Since now ”that” is equivalent to ”this”, we can call all methods of TimeTicker object! Beautiful!

===== The Internet Explorer’s Problem =====
While setInterval() in Firefox provide us a way to pass in extra parameters, IE’s version of ‘’setInterval()” is less flexible (and it also did drive me nuts!) Anyway, let’s talk about the mechanism of IE’s ‘’setInterval()”. According to [[http://msdn.microsoft.com/workshop/author/dhtml/reference/methods/setinterval.asp|MSDN’s manual on setInterval()]], the syntax of setInterval() is

”iTimerID” = ”window”.**setInterval(** ”vCode”, ”iMilliSeconds” [, sLanguage])

with ”vCode” is either a reference pointer or a “string that indicates the code to be executed when the specified interval has elapsed”. Since there’s no way we can pass an extra parameter to the polled-function, we cannot pass ”this” as a 3rd parameter of setInterval() as what we just did for Firefox.

If we rewrite the code as this.timer = setInterval ( this.updateTime(), 999);then 2 things are gonna happen: first,” this.updateTime()” is executed right away. It’s equivalent to a direct call to the ”updateTime()” function of the current object, ”TimeTicker”. As we are still in the scope of the current function, hence, ”this” refers to the current ”TimeTicker” object. Secondly, after the 999ms has elapsed, ‘’setInterval()”" will invoke ”this.updateTime()”, and now ”this” refers to ”window” as we are now out of the scope of the ”TimeTicker” object. And of course, ”window.updateTime()” is wrong. So what you see is that your script will work the first time (when this.updateTime() is executed in the correct scope), and then everything fails (when this.updateTime() is excuted by setInterval() in the wrong scope).

And If we write this.timer = setInterval ( this.updateTime, 999);(Notice the missing parenthesis). This will also fails as when this.updateTime is invoked, we are already in the wrong scope, the scope of ”window” object!

On the other hand, if we writethis.timer = setInterval ("this.updateTime()", 999);we are still in the wrong scope when setInterval() invoke the updateTime() method. ”this” still refers to ”window” when the updateTime() function is executed. Notice that we can include the parenthesis “( )” (and potentially other pass-by-value parameters since the method is refered to as a string and it’s not executed right away, but only when being invoiked by ‘’setInterval()”). However, we can’t pass a pointer to the current object in a string!

We’d like to pass a pass-by-reference pointer object to point to the current ”TimeTicker” object but no matter what we do, we cannot seem to do so. But be hold! Our salvation comes from the use of a glorifying global variable, and of course, a dash of ”eval()”, just enough to tame Internet Explorer.

===== The Internet Explorer’s Solution =====
I’d like to include the code for IE here, just read over and we will discuss later.


/* C. global scope object */
var globalScope = new Array();

/* A.3 - setInterval Fix */
/* A.3.1 - for IE */
if( document.all )
{
/* A.3.1.1 - make a reference to the current object and saved in the global scope array
* to use later to correct the scope.
*/
globalScope[ this.uniqueId ] = this;
setInterval( 'ieIntervalHandler("' + this.uniqueId + '","updateTime")', 999 );
}
else
{
/* A.3.2 - Mozilla */
this.timer = setInterval ( function( that ) { that.updateTime(); }, 999, this );
}
}

And we add an extra function, ”ieIntervalHandler( **id**, **strFunc** )” to the script.

/* D. - Special IE setInterval handler function with scope corrected */
function ieIntervalHandler( id, strFunc )
{
/* D.1 - correct the scope then make the call */
var scope = globalScope[id];
eval( "scope." + strFunc + "()" );
}


All set? Here comes the explanations: First of all, we added a global associative array named globalScope (”**C**”). This associative array is used to store reference to a particle object based on that object’s unique identifier. Confused? Not a problem, just keep on reading.

Let’s read A.0, this is where we set the unique identifier for this ”TimeTicker” object. For this technique to work, each object **must** have a **unique identifier** since the unique identifier is used as the **key** of the global associative array.

Look at ”**A.3.1**”. Here we do a little browser-intelligence. If the browser doesn’t know ”document.all”, which is IE-specific, we use the improved version of ‘’setInterval()” as mentioned in the above Firefox’s solution section. If the browser is indeed IE, at ”**A.3.1.1**”, we store the reference to this ”TimeTicker” object as an element in the globalScope associative array. Then we cheerfully and confidently ‘’setInterval()” the ”ieIntervalHandler()” function (see ”**D.1**”). We do a pass-by-value the unique ID of the current ”TimeTicker” object as the first parameter of ”ieIntervalHandler()”, and ”updateTime” as the name of the method of the same ”TimeTicker” object.

Look at ”**D.1**”. Here we have a 2-line function. First, we get the reference to the particular TimeTicker object, which we already stored in the globalScope associative array back in ”**A.3.1.1**”. Since we are now in the scope of ieIntervalHandler(), our ‘’scope” variable is pointing to the ”TimeTicker” object. With the correct reference to the correct object, now we can make the call to the correct method with a quick, painless ”eval()”eval( "scope." + strFunc + "()" );

What did we just do? We use a global array to store the references so that later on we can get to the right object, even when ieIntervalHandler is invoked by ‘’setInterval()” and the scope now is all over the place — we could care less! Hence we now can solve the wrong scope issue within IE. And guess what, now we can have any object to poll a method of itself! How awesome is that?

===== Memory Issue =====
Since we need to keep polling a function, we need to be careful of not creating more and more objects or functions that the garbage collect will never be able to purge because of a hidden reference somewhere. If you do create a lot of objects that do self-polling using this global associative array technique, when destroying the object, you will probably have to set the element of the globalScope to ”**null**” also, so that there is no further reference to the destroyed object. Also, in ”**A.3.2**”, we do create a new function everytime we’d like to run the timer, this may be a potential memory leaking point on Firefox.

I’d like to leave the quest of battling memory-leak monster to somebody else, but just remeber to watch out for potential weak point where memory leak can happen, especially when your page will be opened for a prolonged preriod of time.

===== Keywords spam =====
This setInterval() and the scope problem is a pretty abstract and hard to describe problem. I just include some potential search keywords here so that other people can by chance find this page. Call it keyword spam, call it shameless-self advertising, I could care less, but if somebody find this helpful, then I call it success

setInterval scope loosing, setTimeout, this setInterval wrong scope, javascript setInterval scope, global associative array scope correction, object oriented setInterval, self polling, this polling scope error, internet explorer setInterval fail, firefox setInterval fail, setInterval object.

===== Final remarks =====
Thank you for reading! I hope this helps. And I’d like to wish you a …

Merry Christmas and Happy New Year!

4:13AM, Dec 23, 2006.