Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Call a URL and pass a parameter in the URL (http post) Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 8:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 4:57 AM
Points: 1, 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
Post #1382247
Posted Thursday, November 8, 2012 4:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282
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.
Post #1382401
Posted Thursday, November 8, 2012 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
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 Moden's 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)
Post #1382499
Posted Thursday, November 8, 2012 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1382517
Posted Thursday, November 8, 2012 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
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 Moden's 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)
Post #1382524
Posted Thursday, November 8, 2012 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
oh yeah!!!



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1382572
Posted Thursday, November 8, 2012 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1382674
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse