Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Tips > SQL Anywhere INSIDER June 2011  
RSS Feed
 
 
 

 

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:

SQL Anywhere

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";

 

 

SQL Anywhere

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

SQL Anywhere

 

 


 

DOCUMENT ATTRIBUTES
Last Revised: Jun 14, 2011
Product: SQL Anywhere
Technical Topics: SQL Anywhere
  
Business or Technical: Technical
Content Id: 1093467
Infotype: Tips
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics