Discussion :

[jdflaugergues]

Bonjour,

Je suis confronté à une barrière technique en T-SQL sous SQL Server 2008 que je n'arrive pas à surmonter.
Le problème est le suivant :

J'ai un script SQL (pas encore transformé en procédure stockée) qui a pour rôle d'appeler un web service et de récupérer un objet JSON.

Le script fonctionne bien jusqu'à l'envoi et la réception du status de la requête HTTP, par contre le problème qu'il se pose c'est au niveau de la récupération du responseText. La réponse étant du JSON, je ne sais pas dans qu'elle type de variable la stocker, si il faut la convertir avant de la récupérer,et... et j'obtiens une erreur lorsque je tente de la récupérer dans une variable de type nvarchar(max)
Erreur : 0xC00CE56E
Source : msxml3.dll
Description : Erreur système*: -1072896658.

Ma question est donc de savoir si il est possible de récupérer du JSON en SQL et si oui de quel manière.
Merci pour votre aide.

Ci-dessous le script en question :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
 
DECLARE @URI varchar(2000) = 'http://.../...php'      
DECLARE @methodName varchar(50) = 'POST'
DECLARE @requestBody varchar(8000) =  'type=6&module=2&id=101&title=Titre  Notif&message=Message Notif&externalRef=8'
DECLARE @UserName nvarchar(100) = 'UserName '
DECLARE @Password nvarchar(100) = 'Password '
DECLARE @responseText nvarchar(max) = ''
 
SET NOCOUNT ON
 
SET @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 
 
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'Create failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
 
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'Open failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
 
-- set request headers - Content-Type
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'SetRequestHeader failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
-- set request headers - Authorization
DECLARE @CrendentialEncoded VARCHAR(500) = @UserName + ':' + @Password
 
SELECT @CrendentialEncoded = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)')
FROM ( SELECT CAST(@CrendentialEncoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp;
 
SET @CrendentialEncoded = 'Basic ' + @CrendentialEncoded
PRINT @CrendentialEncoded
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Authorization', @CrendentialEncoded
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'SetRequestHeader failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
DECLARE @returnval nvarchar(max)
-- send the request 
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
IF    @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'Send failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
 
-- Get status text 
declare @statusText varchar(1000), @status varchar(1000) 
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status as Status, @statusText as StatusText, @methodName as MethodName
 
 
-- Get response text 
EXEC @hResult = sp_OAGetProperty @objectID, 'responseText',@responseText OUTPUT
--Exec @hResult = sp_OAMethod @objectID, 'responseText', @responseText OUTPUT
 
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT    hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'ResponseText failed', 
                MedthodName = @methodName 
      goto destroy 
      return
END
Select @ResponseText [ResponseText]
 
destroy: 
      exec sp_OADestroy @objectID 
SET NOCOUNT OFF
 
GO

[SQLpro]

le JSON est une forme dérivée du XML. SQL Server permettant de retournée du XML an lieu et place du dataset, il suffit de savoir faire la transformation....
Exemple : http://www.codeproject.com/Articles/...L-Data-to-JSON

A +

[jdflaugergues]

Le lien que tu me donnes explique comment transformer du JSON en xml en javaScript.
En fait, c'est pas trop ca que je souhaite faire.

Ce que je veux, c'est récupérer mon objet JSON sous SQL Server, ensuite je n'aurais pas de problème pour le parser.

Pour récupérer mon JSON sous sql server, je dois passer une variable en output dans l'appel de la procédure sp_OAMethod. Mais cette procédure foire, certainement car j'ai un type incompatible entre ma variable @responseText et ce que me retourne mon webservice.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Exec @hResult = sp_OAMethod @objectID, 'responseText', @responseText OUTPUT

Je pense plutôt que la solution se trouve dans l'appel des méthodes d'accès au webservice (sp_OACreate,sp_OAGetProperty,sp_OAMethod), il doit bien exister un moyen pour faire comprendre au système de convertir l'objet JSON en nvarchar ou xml ou autre pour que je puisse le récupérer sans problème lors de l'appel de sp_OAMethod @objectID, 'responseText'