SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Call a URL and pass a parameter in the URL (http post)


Call a URL and pass a parameter in the URL (http post)

Author
Message
mooneer3112
mooneer3112
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 8
Hello ,

Can somebody please assist me, i need to call this URL http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=XXXX where XXXX is a number which we obtain from a table by a simple select Query.

create schedule Job which execute every 1 minute and also to save the reponse we obtain from the URL to a database.

Any idea ?

Thanks
McSQL
McSQL
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 299
I may be overcomplicating things, and there may be a much more simple way to do this, but for the first part, you're looking at creating an agent job that fires up a select, using the table output as a variable;

i.e.
@URL is your URL path
@nocall = your selected record result

set @nocall = select <field> from <table> where <whichever criteria you're using to choose the number>

set @URL = 'http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall='@nocall


You can then use a cmdexec command to fire up Internet Explorer with the URL:

(Create a text document with the following entry:

@start "" /b "C:\Program Files\Internet Explorer\iexplore.exe" %*


Rename the file AutoURL.bat)


You could then call this from the command line;

*exec AutoURL www.google.com for example

You would have to pass the variable output to the command (i.e. AutoURL @URL)

Personally, I would consider using a VB Script for the whole job, rather than anything SQL offers on its own.

The second part may be more difficult, as it will depend on the structure of the website and what you're actually able to read from it.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62731 Visits: 17959
I would not do this in sql. This sounds like you need a simple program to do this. If I had this assignment I would generate a .net windows service for this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71956 Visits: 40942
I'd do exactly what Sean is recommending...I'd have a service slam the web site and store the results in a table, all outside of TSQL.

Now, having said that, just to be stubborn, i got out my "gotta do it in TSQL" hat, and created a CLR to return a web page, or in this case a web request.

It works via TSQL, and takes about 6 seconds or so to get the results...so it's not all that quick, but we are talking about the web, here.

the call:


declare @url varchar(1000)
declare @int int
SET @int = 42
SET @url='http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=' + CONVERT(varchar,@int)

declare @results varchar(max)

SELECT @results = dbo.CLR_WebQuery(@url)
print @results

/*
OK
#0 appels effectifs / #42 / #0
*/




and the CLR definition:


'requires
'Imports System.Net
<Microsoft.SqlServer.Server.SqlFunction()>
Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' varchar(8000) = SqlString, SqlChars=varchar(max)
Dim request As WebRequest = HttpWebRequest.Create(URL)
Using response As WebResponse = request.GetResponse()
Using dataStream As Stream = response.GetResponseStream()
Using reader As New StreamReader(dataStream)
Dim responseFromServer As String = reader.ReadToEnd()
Return New SqlChars(New SqlString(responseFromServer))
End Using
End Using
End Using
request = Nothing
End Function



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62731 Visits: 17959
LOL Lowell you really pulled out all the stops with your T-SQL hammer on this one!!! Talk about brute force. I don't remember the link to the hammer on your site but this conjured up memories of that one. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71956 Visits: 40942
oh yeah!!!



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71956 Visits: 40942
Received an pm asking for more details on the programming portion of this, as CLR can be a bit scary the first time around.

hopefully, this can get someone started, but there's a lot of tutorials out there; I'm better with specific questions rather than overall processes.

1. I believe you'll need to set your database to TRUSTWORTHY.
so assuming you create a brand new database named "Test" so you can be sure this works, you need to run this command:


ALTER DATABASE Test SET TRUSTWORTHY ON



2. it's a lot easier to modify something that already exists, than it is to start fresh.
So I slapped together a Visual Studio Database project real quick.
grab this zip file containing a Visual Studio 2010 and open it in in visual studio.

CLRWeb.zip

3. go to the project properties and change the database connection shown below:


4. Use the Build>>Build CLRWeb command as shown in the screenshot above.

5. Use the Build>>Deploy CLRWeb command as shown in the screenshot above.
If the build fails, Control+alt+O takes you to the output window....review the error.

6. Once deployed, use the same test method I pasted before to test it in TSQL

Use Test;
GO
declare @url varchar(1000)
declare @int int
SET @int = 59
SET @url='http://ccv.viatelecom.com/wcbr/wsRealTimeMode.php?adm=10764&sid=3112&nocall=' + CONVERT(varchar,@int)

declare @results varchar(max)

SELECT @results = dbo.CLR_WebQuery(@url)
print @results




If it works, you can now start customizing it by changing the project name or whatever else you need to do, but it would be good to go as is.
you might want to consider using a certificate instead of setting Trustworthy, but that should get you started.

if you were going to use it as is, you would repeat the same steps above so you point at the final target database.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search