WEB SERVICE DATA RECEPTION

Question

Post reply

WEB SERVICE DATA RECEPTION

Vedat ÖZER

SSC Enthusiast

Points: 103

August 10, 2021 at 7:55 pm

#3918533

Hello,

I am able to get data from web service with Excel VBA

Code :

Sub LOGIN()
Dim Movie As Object
Dim scriptControl As Object
Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"
With CreateObject("MSXML2.XMLHTTP")
.Open "POST", "https:....../json", False
sEnv = "{""login"" : "
sEnv = sEnv & vbLf & " { ""username"" : ".....", "
sEnv = sEnv & vbLf & " ""password"" : "..", "
sEnv = sEnv & vbLf & " ""disconnect_same_user"" : ""True"", "
sEnv = sEnv & vbLf & " ""lang"" : ""tr"", "
sEnv = sEnv & vbLf & " ""params"" : {""apikey"":".................."} "
sEnv = sEnv & vbLf & " } "
sEnv = sEnv & vbLf & " }"
.send (sEnv)
Set Movie = scriptControl.Eval("(" + .responseText + ")")
.abort
With Sheets("Ayar")
.Cells(5, 2).Value = Movie.msg
End With
End With
End Sub

I'm trying to import it with mssql but it's not working.

       Declare @url as varchar(8000)
        Set @url =  'https:...../json'
     
       declare @OBJ AS INT
       declare @RESULT AS INT
       EXEC @RESULT = SP_OACREATE 'MSXML2.XMLHTTP', @OBJ OUT
       EXEC @RESULT = SP_OAMethod @OBJ , 'open' , null , 'GET', @url, false
   EXEC @RESULT = SP_OAMethod @OBJ, 'username', '....'
   EXEC @RESULT = SP_OAMethod @OBJ, 'password', '....'
   EXEC @RESULT = SP_OAMethod @OBJ, 'disconnect_same_user', 'True'
   EXEC @RESULT = SP_OAMethod @OBJ, 'lang', 'tr'
   EXEC @RESULT = SP_OAMethod @OBJ, 'params', 'apikey','....'
       EXEC @RESULT = SP_OAMethod @OBJ, send, NULL,''
    
        If OBJECT_ID('tempdb..#XML') IS NOT Null DROP TABLE #XML

       Create table #XML ( STRXML varchar(max))
       Insert INTO #XML(STRXML) EXEC @RESULT = SP_OAGetProperty @OBJ, 'responseXML.xml'
       
       DECLARE @XML AS XML
       SELECT @XML = STRXML FROM #XML
       DROP TABLE #XML
       DECLARE @HDOC AS INT
       EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT , @XML

How can we make Mssql running?

Thank you from now.

Allah bize yeter, O ne güzel vekildir.

vedatoozer@gmail.com

August 18, 2021 at 7:58 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30905 More actions · Answer 1

Well for starters, your VBA request seems to be a post with a JSON request body so you would need to update your SQL Request to do the same which seems to just be a get request.

This reply was modified 2 years, 10 months ago by ZZartin.

Vedat ÖZER SSC Enthusiast Points: 103 More actions · Answer 2

The code below helped.

Thanks everyone from Turkey 😉

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/updateemployee';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "employeeId": 1,
   "firstName": "Nancy",
   "lastName": "Davolio",
   "title": "Sales Representative",
   "birthDate": "2020-08-18T00:00:00.000",
   "hireDate": "2020-08-18T00:00:00.000",
   "address": "507 - 20th Ave. E. Apt. 2A",
   "city": "Seattle",
   "region": "WA",
   "postalCode": "98122",
   "country": "USA",
   "homePhone": "(206) 555-9857"
}'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                 @URL,
                 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
 SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
 SELECT @ResponseText As 'Employee Details'
END
EXEC sp_OADestroy @Object

Allah bize yeter, O ne güzel vekildir.

vedatoozer@gmail.com