Call a URL and pass a parameter in the URL (http post)
-
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
-
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 http://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.
-
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
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! -
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. 😀
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
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! -
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.
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply