View Full Version : Webservice per SQL abfragen
Hallo,
ich hab gerade einen Webservice erstellt, der mir XML-Daten in UTF-8 zur Verfügung stellt. Diesen Webservice verwende ich nun in einer SQL-Abfrage folgendermaßen:
SELECT subject
FROM XMLTABLE('$doc/campaignListData/campaign'
PASSING
XMLPARSE(DOCUMENT SYSTOOLS.HTTPGETCLOB
('http://192.168.200.79:8080/webservices/emarsys/list_email_campaigns/' concat systools.urlencode('D', 'UTF-8') concat '/' concat systools.urlencode('2018-01-01', 'UTF-8') concat '/' concat systools.urlencode('2018-02-01', 'UTF-8'), ''))
AS "doc"
COLUMNS
name VARCHAR(50) PATH 'name',
status VARCHAR(1) PATH 'status',
contactlist VARCHAR(50) PATH 'contactlist',
subject VARCHAR(50) ccsid 1208 PATH 'subject'
) AS WebServiceResult
Ich hab hier also beim Feld "subject" die CCSID 1208 angegeben, da hier UTF-8 Daten vorkommen. Leider werden hier aber die Sonderzeichen (2-Byte Zeichen) nicht erkannt, sondern das System interpretiert jedes Byte als ein Zeichen. Was muss ich tun, um korrekte Daten zu erhalten? Muss ich evtl. im Nachgang das Ergebnis mit "iconv" bearbeiten?
Viele Grüße,
KM
Hat keiner eine Idee dazu? Das ist aber sehr ungewöhnlich für dieses Forum ;-)
Mir ist jetzt noch eingefallen, dass der Job, mit dem der SQL abgesetzt wird mit einer "Single-Byte-CCSID" läuft. Geht ja nicht anders. Ich kann hier keine Unicode-CCSID angeben. Liegt es evtl. daran?
Gruß,
KM
Rainer Ross
19-02-18, 13:23
Hallo KM,
mir fallen zwei Dinge zum Ausprobieren ein
1) Gib beim HTTPGETCLOB als zweiten Parameter folgenden Header mit
'<httpHeader>
<header name="Content-Type" value="application/xml; charset=utf-8"/>
</httpHeader>'
<httpheader><header name="Content-Type" value="application/json; charset=utf-8">
<httpheader><header name="Content-Type" value="application/json; charset=utf-8">
2) Prüfe ob die System CCSID 273 oder 1141 ist, wenn nicht, dann setze die JOB CCSID auf 273 oder 1141
Viele Grüße
Rainer</header></httpheader></header></httpheader>
Hallo Rainer,
zu 1.) Ich hab das jetzt folgendermaßen eingebaut:
SELECT *
FROM XMLTABLE('$doc/campaignListData/campaign'
PASSING
XMLPARSE(DOCUMENT SYSTOOLS.HTTPGETCLOB
('http://192.168.200.79:8080/webservices/emarsys/list_email_campaigns/' concat systools.urlencode('D', 'UTF-8') concat '/' concat systools.urlencode('2018-01-01', 'UTF-8') concat '/' concat systools.urlencode('2018-03-01', 'UTF-8'),
'<httpHeader><header name="Content-Type" value="application/xml; charset=utf-8"/></httpHeader>'))
AS "doc"
COLUMNS
name VARCHAR(50) PATH 'name',
status VARCHAR(1) PATH 'status',
contactlist VARCHAR(100) PATH 'contactlist',
subject VARCHAR(100) ccsid 1208 PATH 'subject',
fromemail VARCHAR(100) PATH 'fromemail',
fromname VARCHAR(100) PATH 'fromname'
) AS WebServiceResult
Allerdings erhalte ich dann einen "XML-Parsing-Fehler" mit folgendem Text:
1 -- Das externe Programm oder Serviceprogramm hat SQLSTATE 2200M
zurückgegeben. Die vom Programm zurückgegebene Textnachricht ist:
& âThe element type "p" does not match the expected end-tag "</body>"
zu 2.) Der Job steht bereits auf CCSID 1141.
Viele Grüße,
KM
Rainer Ross
19-02-18, 14:10
Hallo KM,
ich habe mir die Emarsys API angeschaut und es gibt auch die Möglichkeit JSON Daten abzufragen,
https://help.emarsys.com/hc/en-us/articles/115004522154-Listing-Email-Campaigns
das sieht dann so aus
ich habe es zunächst mit einer SQL Variable getestet
-- SQL-Variable erstellen
create or replace variable tsto.json_var varchar(10000) ccsid 1208;
values(tsto.json_var);
-- Variable mit JSON-Daten laden
set tsto.json_var =
'{
"replyCode": 0,
"replyText": "OK",
"data": [
{
"id": "12345",
"language": "en",
"created": "2011-08-12 18:12:23",
"deleted": "",
"fromemail_host": "emarsys",
"name": "be_afraid_email",
"status": "3",
"api_status": "2",
"api_error": "0",
"fromemail": "malekith@example.com",
"fromname": "Malekith",
"subject": "convergence",
"email_category": "111111111",
"filter": "222222222",
"contactlist": "0",
"template": "333333333",
"cc_list": 0,
"source": "userlist",
"html_source": "Hello $First Name$...",
"text_source": "Hello $First Name$..."
},
{
"id": "67890",
"root_campaign_id": "67890",
"language": "en",
"created": "2011-08-12 18:20:23",
"deleted": "2011-11-07 08:11:57",
"fromemail_host": "emarsys",
"name": "angry_email",
"status": "3",
"api_status": "2",
"api_error": "0",
"fromemail": "bruce.banner@example.com",
"fromname": "Hulk",
"subject": "A new business",
"email_category": "111111111",
"filter": "222222222",
"contactlist": "0",
"template": "0",
"cc_list": 564365356,
"source": "api",
"html_source": "Hello $First Name$...",
"text_source": "Hello $First Name$...",
"version_name": "A version"
}
]
}';
-- JSON_TABLE aus Variable mit JSON-Daten laden
Select x.*
from JSON_TABLE(
tsto.json_var,
'$'
Columns(
nested '$.data
' columns(
"name" varchar(50) path 'lax $.name',
"Status" varchar(01) path 'lax $.status',
"Contactlist" varchar(50) path 'lax $.contactlist',
"Subject" varchar(50) ccsid 1208 path 'lax $.subject'
)
)
) as x;
JSON Parsing mit HTTPGETCLOB sieht dann so aus
Select x.*
from JSON_TABLE(
SYSTOOLS.HTTPGETCLOB('https://api.emarsys.net/api/v2/email/?fromdate=2013-01-01&todate=2013-02-01',''),
'$'
Columns(
nested '$.data
' columns(
"name" varchar(50) path 'lax $.name',
"Status" varchar(01) path 'lax $.status',
"Contactlist" varchar(50) path 'lax $.contactlist',
"Subject" varchar(50) ccsid 1208 path 'lax $.subject'
)
)
) as x;
<httpheader><httpheader><header name="Content-Type" value="application/json; charset=utf-8"><httpheader><header name="Content-Type" value="application/json; charset=utf-8">
Viele Grüße
Rainer</header></httpheader></header></httpheader></httpheader>
Die Abfrage mit JSON_TABLE hatte ich auch schon versucht. Hat aber am Ergebnis nichts geändert. Auch die Abfrage mit dem geänderten httpHeader hat nichts geändert.
Ich hab jetzt mal eine Tabelle erstellt mit einem Feld erstellt, das die CCSID 1208 hat. Diese Tabelle fülle ich direkt mit folgender SQL-Abfrage:
insert into mai.testjson
select subject
from JSON_TABLE(systools.httpGetClob(
'http://ban:8080/webservices/emarsys/list_email_campaigns/' concat systools.urlencode('D', 'UTF-8') concat '/' concat systools.urlencode('2017-01-01', 'UTF-8') concat '/' concat systools.urlencode('2018-02-15', 'UTF-8'),
'<httpHeader><header name="Content-Type" value="application/json; charset=utf-8"/></httpHeader>'),
'$.campaigns'
columns(
name VARCHAR(50) ccsid 1208 PATH '$.name',
status VARCHAR(1) ccsid 1208 PATH '$.status',
contactlist VARCHAR(100) ccsid 1208 PATH '$.contactlist',
subject VARCHAR(100) ccsid 1208 PATH '$.subject',
fromemail VARCHAR(100) ccsid 1208 PATH '$.fromemail',
fromname VARCHAR(100) ccsid 1208 PATH '$.fromname')
) as x
Das Ergebnis sieht dann so aus:
438
In der zweiten Zeile sollte eigentlich stehen "Grüße und Wünsche...".
Gruß,
KM
Das Problem mit 1208 ist ja, dass es hier keine SQL-Unterstützung für die automatische Umsetzung gibt.
Beim Select kannst du UTF8-Felder z.B. in NCHAR/NVARCHAR umwandeln, was dann in STRSQL in SBCS (der Job-CCSID) umgewandelt wird. Im RPGLE bekommst du dann UCS2.
So liefert dein Select native UTF8-Daten und das sieht wiederum korrekt aus.
Rainer Ross
19-02-18, 15:21
Hallo KM,
teste mal folgenden Webservice der auch Umlaute und russische Zeichen im ersten Datensatz enthält
Select x.*
from JSON_TABLE(
SYSTOOLS.HTTPGETCLOB('http://www.myhofi.com/myapp/websrv11.pgm',''),
'$'
Columns(
nested '$.items
' columns(
"Id" integer path 'lax $.id',
"Name" varchar(40) ccsid 1208 path 'lax $.name'
)
)
) x;
439
Hallo Rainer,
stimmt, hier sind Umlaute vorhanden. Die werden auch richtig angezeigt.
Gruß,
KM
Hallo Fuerchau,
Das Problem mit 1208 ist ja, dass es hier keine SQL-Unterstützung für die automatische Umsetzung gibt.
Das scheint hier auch das Problem zu sein. Ich denke, dass ich hier tatsächlich nachträglich per iconv die Daten von 1208 in 1141 konvertieren muss. Dann sollte es wieder passen. Ist halt umständlich.
Ich finde es nur seltsam, dass man nicht direkt die Daten aus dem Webservice in eine Tabelle mit CCSID 1208 speichern kann. Irgendwo dazwischen scheint wohl noch eine SBCS-Komponente mit im Spiel zu sein.
Gruß,
KM