Download dynamic url from internet

[Former Member]

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.

[MarkCulp · Accepted solution]

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).

[Former Member · Accepted solution]

Thank you Mark.

[former_SQLA_member1694880 · Accepted solution]

Great, just what I need.

[VolkerBarth · Accepted solution]

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

[former_SQLA_member1694868 · Accepted solution]

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

[VolkerBarth · Accepted solution]

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:)