I need to download some internet content. I know I can create a stored function to download data, but I need to pass some params to that url. How can I do that?

As a work around, I'm thinking about create a "proxy" stored function that create and drop a function to consume the url. But I don't know how SA will handle with massive create and drop of stored functions.

asked 23 Mar '12, 12:46

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%


Try this (or a variation):

CREATE OR REPLACE PROCEDURE web_get( in url long varchar )
RESULT( attr varchar(128), value long varchar )
URL '!url'
TYPE 'HTTP:GET';

and then call using:

select *
  into #temp
  from web_get( @url );

The #temp temporary table will contain a number of rows - the row with attr='body' will contain the page that you retrieved from the remote web server.

If you need to pass parameters you can either add the parameters directly to the URL (using ?p1=v1&p2=v2 etc) or add additional parameters to the web_get procedure - any parameter to the procedure which is not consumed by !parameter within the procedure declaration will automatically be encoded and added to the URL (for a HTTP:GET request) or the BODY (for a HTTP:POST request).

Example:

CREATE OR REPLACE PROCEDURE web_get_1( in url long varchar, in parm1 long varchar )
RESULT( attr varchar(128), value long varchar )
URL '!url'
TYPE 'HTTP:GET';

The web_get_1 procedure is like web_get except it will send the value of 'parm1' to the remote web server. In a similar way you can add as many parameters as you need. The parameter name(s) must be the name of the parameter that the remote web server is expecting.

Example:

CREATE OR REPLACE PROCEDURE web_get_xyz(
                      in url  long varchar,
                      in x    long varchar,
                      in y    long varchar,
                      in z    long varchar
                   )
RESULT( attr varchar(128), value long varchar )
URL '!url'
TYPE 'HTTP:GET';

The get_web_xyz procedure will encode the 'x', 'y', and 'z' parameters in the remote web server request. E.g. The statement:

select * into #temp from get_web_xyz( 'http://remote.com', 10, 20, 40 )

will result in the HTTP GET request to the URL http://remote.com?x=10&y=20&z=40

More information in the documentation on creating web service procedures.


FYI: The procedure that I keep in my toolbox (of commonly used procedures) is

CREATE OR REPLACE PROCEDURE sp_web_get (
                      in  url    long varchar,
                      in  cert   long varchar  default NULL,
                      in  proxy  long varchar  default NULL,
                      in  clport varchar(10)   default NULL
                  )
RESULT( attr varchar(128), value long varchar )
URL         '!url'
TYPE        'HTTP:GET'
CERTIFICATE '!cert'
CLIENTPORT  '!clport'
PROXY       '!proxy'
;

This procedure is the generalization of the web_get procedure show above - it allows specification of a certificate (for use with HTTPS calls), a client port (if needed to get through firewalls), and an HTTP proxy (if the site is configured to use one).

permanent link

answered 23 Mar '12, 13:00

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265
accept rate: 40%

edited 23 Mar '12, 15:01

Thank you Mark.

(26 Mar '12, 07:50) Zote

Great, just what I need.

(26 Mar '12, 08:41) Dmitri

...and now we're really interested whether you might reveal more treasures of your great toolbox...:)

(26 Mar '12, 08:48) Volker Barth
2

Mark could start a weekly blog just posting tools and I would subscribe.

(26 Mar '12, 11:27) Siger Matt
Replies hidden

We would appreciate a "not weekly" blog as well...

Sometimes I wonder if a "developer blog" (i.e. one that is not bound to one single person) would lead to more frequent blog article's on the SQL Anywhere blog roll... apparently, not everyone can (and need to!) produce as continuously and brave as Breck:)

(26 Mar '12, 14:13) Volker Barth
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:

×137
×8
×7
×1

question asked: 23 Mar '12, 12:46

question was seen: 1,036 times

last updated: 26 Mar '12, 14:17