I need to download UN Blacklist XML file periodically. Is there any way to automate this using sql ONLY?

asked 28 Dec '09, 05:37

Kumar's gravatar image

Kumar
262479
accept rate: 25%

edited 28 Dec '09, 08:05


The following function will return XML as a string:

CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'http://www.un.org/sc/committees/1267/consolidatedlist.xml' type 'HTTP:GET' ;

To extract stuff from it, use select ... from OpenXML().

permanent link

answered 28 Dec '09, 10:28

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

@Dmitri, thank you. Any comment on the usage of the "proxy" keyword? Is it possible to pass the network username/password authentication?

(28 Dec '09, 10:46) Kumar

Never tried it before, but adding

proxy 'http://192.168.0.1:3128'

seems to work. For the authentication, try adding user:password before the URL, e.g.:

CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'http://proxyuser:proxypwd@www.un.org/sc/committees/1267/consolidatedlist.xml' type 'HTTP:GET' proxy 'http://proxyserver:proxyport' ;

(28 Dec '09, 11:31) Dmitri

On a second thought, 1) How to pass website authentication info? 2) How to pass network proxy authentication info?

I think the following format will work, e.g: CREATE FUNCTION "DBA"."UNBlacklist"() returns long varchar url 'forumuser:forumpwd@www.someforum.com' type 'HTTP:GET' proxy 'networkuser:networkpwd@proxyserver:proxyport';

I dont know whether this will work. No need of this in the sample case above. I will try to download something which requires a web-login.

(28 Dec '09, 12:16) Kumar

Just as a quick start:

You should be able to download any web document with SA's web client procedures/functions.

I haven't done that myself so I can't give exact advice but you may have a look at the topics "SQL Anywhere web services" and "Web service clients", e.g. the following topic.

Some ideas:

  • You could code the URI in a web client function
  • The function could return the according XML document
  • Or you could use SA's XML support to extract the needed information from the document directly
  • Calling the web client function regularly could be done be an event or the like.

Just my thoughts

permanent link

answered 28 Dec '09, 08:58

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

1

@Kumar: In addition to what Volker said, Eric Farrar might be able to help... he's the Go To Guy for a lot of SQL Anywhere topics including everything web-related. His blog is at http://iablog.sybase.com/efarrar/ ...alas, I am having a senior moment, can't find the perfect link for your needs.

(28 Dec '09, 10:11) Breck Carter
1

@Kumar: Another resource is Deanne Chance who has written some articles on the subject for (yuk) sys-on.com. See "Consuming Amazon Web Services Using PowerBuilder and SQL Anywhere 10.0.1" (PowerBuilder only plays a small part) at http://dchance.sys-con.com/node/480658/mobile

(28 Dec '09, 10:23) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×90
×35
×28

question asked: 28 Dec '09, 05:37

question was seen: 1,663 times

last updated: 28 Dec '09, 10:28