Stock Quotes and XMLHttp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stock Quotes and XMLHttp

Author

Topic

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 17:10:32

Hi All!
I'm trying to reinvent the wheel here and write a job to reach out to Yahoo and retrieve our company's stock quote info as well as that of the DJIA.

Usually, I'd do something like this quite easily in Cold Fusion, but I'm trying to expand my horizons and duplicate the functionality from within SQL Server.

My research shows me that I should be able to use XMLHTTP. I'm just unsure of how to use it and pull out the results.

Here's the page I'm trying to parse:
http://finance.yahoo.com/d/quotes.csv?s=^DJI&f=sl1d1t1c1ohgv&e=.csv

Any help would be appreciated.

Thanks,

Bob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-04 : 17:14:07

Why would you do this in SQL Server? Are you going to store the information in a database? Jobs can call programs, so just schedule your program to run inside SQL Agent.

Tara

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 17:24:59

Tara,
Yes, I will be storing the data in a database. From that, I'll be producing graphs (using cold fusion) as well as other files for my client to include in their pages.

I can certainly schedule a job in Cold Fusion to reach out, grab the page, parse the results and insert it to the database. The thing is, we have a cluster of web servers and to do this right, I'd have to schedule the job to run on each of them in case one dies. I really don't want to do that because of other factors, so I'm trying to centralize the operation on the SQL Server.

Thanks,

Bob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-04 : 17:28:49

Since this is a web environment, wouldn't SQL Server be behind a firewall and not be able to get to the web anyway?

Tara

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 17:31:29

Tara,
It's actually for an intranet portal. Although the servers are behind a firewall, they can access the internet through our proxy servers.

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-04 : 18:09:15

This is a square peg, round hole problem.
Cold Fusion would be the better way to get the data, parse it, and insert it into your database. Then use Cold Fusion to render the graphs. You could also write an EXE that is a scheduled tasks to go do the same thing. That might be the best way to make this work.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 18:39:11

Michael,
I realize it's not the best approach, but it also ties in to a post one of my clients posted earlier this week.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35773&SearchTerms=html

We provide shared SQL hosting for our statewide organization. What he's trying to do here is import a text file that exists on another department's web server into his local database on our server. They won't grant him direct access to their db nor will they FTP the data to our server. All they have/will offer is to make the extract available on their web server.

It's basically the same concept as the stock quote example I provided. I know I should be able to reach out and grab the file with XMLHTTP and save it to the file system where I can use BCP to insert the records to the database. If at all possible, I'd like to help him and skip saving the file and somehow loop over the results and insert them directly to the database.

That said, what do yout think?

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-04 : 18:57:12

Hmmmmm
I'm not sure how you would do this without getting Cold Fusion involved to pull the data into SQL server somehow. Once it's in SQL server, you can manipulate that string and do whatever you need with it.

If someone can help get that CSV string from a website into SQL server somehow, I can help ya from there.

http://finance.yahoo.com/d/quotes.csv?s=^DJI&f=sl1d1t1c1ohgv&e=.htm

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 19:05:04

using XMLHTTP I can (in theroy) retrieve the page and return it as a single variable.

Dim xmlHttp As New ServerXMLHTTP30
Dim stockQuote As IXMLDOMNode

xmlHttp.open "GET", "http://finance.yahoo.com/d/quotes.csv?s=^DJI&f=sl1d1t1c1ohgv&e=.csv", False
xmlHttp.send

Set stockQuote = xmlHttp.responseXML

So now I have a variable that has the CSV output from the yahoo stock page or the text file from that other web server I mentioned.

I just need to figure out how to a) seperate the stock values and b) loop over each row of the text file one at a time so I can use substring commands to parse out the data my colleague needs and insert it to the database.

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-04 : 19:58:32

The code you just posted, are you running that in ASP / Cold Fusion?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 20:16:15

Neither, I was going to run it as an ActiveX Script Task in a DTS package.

I haven't run it yet. Going to install MSXML 3.0 over the weekend.

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-04 : 20:34:56

Ahhhh ok!
Yeah, that should work.

Do some SPLIT() 's on the CSV rows and you should be set.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-04 : 20:54:59

Cool!

Is there a command that works with split to tell you how many sections exist?

For that text file out on the web server, it would seem I could do some kind of while loop if I knew how many rows there were using split to seperate the rows. Then I could split it again to seperate the elements within each row.

Sound do-able?

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-04 : 21:01:09

How about something like this:



Dim aryCSV() as String 
Dim nNumElements as integer

'Basically, for each CSV string in your CSV list
For Each oNode in oNodeList
aryCSV = Split(oNode.value, ",")

'Get the number of elements in the array
nNumElements  = UBound(AryCSV)

SELECT CASE nNumElements 
Case 5 'This is a guess
  'Call Your stored proc or set some variables to call it here
  sSymbol = aryCsv(0)
  sPrice = aryCSV(1)

Case 6 
'Maybe sometimes you get different length strings etc.

Case else
   'Bad string, toss it out
End Select

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-07 : 11:37:37

Well, I installed MSXML 3 over the weekend on our SQL box. Tried out the example from MSDN on making the XMLHTTP call and so far it hasn't worked.

Has anyone else tried this?

Thanks,

Bob

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-07 : 13:17:18

I looked in some of the other forums and found some code that proved quite useful. Here's what my ActiveX Script Task looks like now:

Function Main()
dim http, httpresult
set http=createobject("microsoft.xmlhttp")
http.open "get", "http://finance.yahoo.com/d/quotes.csv?s=^DJI&f=sl1d1t1c1ohgv&e=.csv", 0
http.send
set httpresult=http.responseText
set http=nothing
msgbox httpresult
Main = DTSTaskExecResult_Success
End Function

The good news is that based on the error, SQL is reaching out and pulling in csv of stock values. The bad news is, the step is failing. The error is as follows:

Error Code: 0
Error Source= Microsoft VBScript Runtime Error
Error Description: Object required: '[string: """^DJI",10333.38, "6/7"]'

I'm not exactly sure what this means. Any ideas?

Thanks,

Bob

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-07 : 14:13:20

Is that an escape char in the string?

Brett

8-)

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-07 : 14:50:59

It looks like SQL added some quotation marks to the string. Not sure why though.

Here is the contents of the string as viewed through a browser:

"^DJI",10349.77,"6/7/2004","2:48pm",+106.95,10243.31,10350.69,10243.31,111574664

Thanks,

Bob

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-07 : 14:54:30

Does the error occur on the MsgBox line?

Maybe you need this:
msgbox replace(httpresult, """", "")

or maybe

msgbox replace(httpresult, """", "'")

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2004-06-07 : 15:18:13

Michael,
After re-running, it looks like my error description contained the extra quote mark.

The error reads:

Error Code: 0
Error Source= Microsoft VBScript Runtime Error
Error Description: Object required: '[string: ""^DJI",10364.31, "6/7"]'

The message places the error on line 12. Starting at the top and going down through my comments and empty lines, that places the error at the line reading 'http.send'.

Thanks,

Bob

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-08 : 02:32:39

This is a really painful way to debug! Try running this code inside the VB6 IDE first, where you can use the Immediate window and other debugging tools to figure out the problem. Once you are sure it works, you can copy and paste that code inside the VBS file or whatever. We use XML 4.0, and it seems to be less buggier than XML 3.0. The good news is that both versions can exist side by side...give it a shot!

OS

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources