Switch to standard view 
  Sybase logo
 
 
 



 

June 2011 Edition

Retrieving Web Data Using SQL Anywhere

SQL Anywhere contains many built-in database features to both host and consume web services. Below is an example that shows how to use a simple stored procedure to access Yahoo's Financial web services and download the resulting stock data into a database table.

In order to retrieve the HTML data from Yahoo's web server, we need to first specify that the 'RESULT' type of the stored procedure will be sourced from an external 'URL'and will use the HTTP request method HTTP:GET' to retrieve the data:

E.g.

CREATE PROCEDURE "DBA"."sp_GetQuoteYahoo"()
result( Field_Type varchar(60),Field_Value long varchar )
url 'http://finance.yahoo.com/d/quotes.csv?s=HPQ+F+TOYOF
+ADBE+AAPL+AMZN+GOOG+MSFT+ORCL+YHOO+
MFC&f=sl1d1t1c1ohgvj1pp2wer2n' type
'HTTP:GET';

The returned data set from a web service is returned in an HTML format (in general). This particular Yahoo! web service returns a Comma Separated Values (CSV) set of data, which can be directly viewed in Interactive SQL:

Specific data fields from the stock CSV data can be retrieved (via the 'Body' field) by using an ‘OPENSTRING’ expression on the result:

CREATE PROCEDURE "DBA"."sp_YahooStockRetrieval"()
BEGIN

DECLARE ts timestamp;
DECLARE xmlResult long varchar;
DECLARE starttime datetime;
DECLARE numstocks int;
DECLARE statdescription varchar(60);
DECLARE sqlcd varchar(5);
DECLARE sqlst varchar(5);

SET TEMPORARY OPTION "blocking" = 'off';
SET TEMPORARY OPTION date_order='mdy';
SET ts = NOW();
SET numstocks =0;

-- retrieve stock information
SET xmlResult = (SELECT C2 From sp_GetQuoteYahoo() with (C1 long varchar,C2 long varchar) WHERE C1 = 'Body');

SELECT SQLCODE,SQLSTATE INTO sqlcd,sqlst from DUMMY;

IF sqlst <> '00000' THEN
MESSAGE 'YAHOO STOCK PRICE UPDATE - FAILED... WITH SQLCODE= ', sqlcd , ', SQLSTATE= ',sqlst,' ,ERRORMSG= ',ERRORMSG(sqlst);
RETURN;
END IF;

INSERT INTO STOCKDATA
(
"TS", "STOCKID","STOCKPRICE", "STOCKDATE", "STOCKTIME", "STOCKCHANGE", "STOCKOPEN", "STOCKHIGH", "STOCKLOW",
"STOCKVOLUME", "MKTCAP", "PREVIOUS_CLOSE", "PERCENT_CHANGE", "ANNUAL_RANGE", "EPS", "PE", stockdescription
)
SELECT
ts, n.stockid,n.price,
n."date", n."time",n."change", n."open", n."high",n."low", n.volume,
n.mktcap,n.Previous_Close, n.Percentage_Change,n.annual_Range, n.eps,n.pe, n."stockdescription"
FROM OPENSTRING(VALUE xmlresult)
WITH (stockID varchar(30) ,
price decimal(18,2) ,
"date" varchar(30) ,
"time" varchar(30) ,
change varchar(30) ,
"open" varchar(30) ,
"high" varchar(30) ,
"low" varchar(30) ,
volume varchar(30) ,
mktcap varchar(30) ,
previous_Close varchar(30) ,
percentage_Change varchar(30) ,
annual_Range varchar(30) ,
eps varchar(30) ,
pe varchar(30),
stockdescription varchar(128)) as n
WHERE
STOCKID not in
(select STOCKID FROM OPENSTRING(VALUE xmlresult)
WITH (stockID varchar(30) ,
price decimal(18,2) ,
"date" varchar(30) ,
"time" varchar(30) ,
change varchar(30) ,
"open" varchar(30) ,
"high" varchar(30) ,
"low" varchar(30) ,
volume varchar(30) ,
mktcap varchar(30) ,
previous_Close varchar(30) ,
percentage_Change varchar(30) ,
annual_Range varchar(30) ,
eps varchar(30) ,
pe varchar(30),
stockdescription varchar(128)) as g2
WHERE
"date" = 'N/A'
OR "time" = 'N/A'
OR "price" = '0.00');

SELECT SQLCODE,SQLSTATE,@@ROWCOUNT INTO sqlcd,sqlst,numstocks from DUMMY;
--
IF sqlst <> '00000' THEN
MESSAGE 'ERROR IN sp_YahooStockRetrieval, SQLCODE = ', SQLCODE , ', SQLSTATE= ',SQLSTATE,' ,ERRORMSG= ',ERRORMSG(SQLSTATE);
RETURN;
END IF;

MESSAGE 'YAHOO STOCK PRICE UPDATE - FINISHED...' ,numstocks, ' STOCKS WERE UPDATED. IN : ', datediff(second,starttime,NOW()), ' secs.';
RETURN;

END;

 

This stored procedure generates a record for each stock, which then can be inserted into a base table that is defined with the following schema:

 

CREATE TABLE "DBA"."STOCKDATA" (
"TS" TIMESTAMP NOT NULL,
"STOCKID" VARCHAR(30) NOT NULL,
"STOCKPRICE" DECIMAL(18,2) NOT NULL,
"STOCKDATE" VARCHAR(30) NOT NULL,
"STOCKTIME" VARCHAR(30) NOT NULL,
"STOCKCHANGE" VARCHAR(30) NULL,
"STOCKOPEN" VARCHAR(30) NULL,
"STOCKHIGH" VARCHAR(30) NULL,
"STOCKLOW" VARCHAR(30) NULL,
"STOCKVOLUME" VARCHAR(30) NULL,
"MKTCAP" VARCHAR(30) NULL,
"PREVIOUS_CLOSE" VARCHAR(30) NULL,
"PERCENT_CHANGE" VARCHAR(30) NULL,
"ANNUAL_RANGE" VARCHAR(30) NULL,
"EPS" VARCHAR(30) NULL,
"PE" VARCHAR(30) NULL,
"STOCKDESCRIPTION" VARCHAR(128) NULL,
PRIMARY KEY ( "TS" ASC, "STOCKID" ASC )
) IN "system";

 

 

The collected data can then be analyzed using standard SQL queries:

 

 



Back to Top
© Copyright 2010, Sybase Inc.