Webservice per SQL abfragen

KM · 13-02-18, 17:21

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:

Code:

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

KM · 19-02-18, 11:47

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

HTML-Code:

'<httpHeader>
    <header name="Content-Type" value="application/xml; charset=utf-8"/>
 </httpHeader>'

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

KM · 19-02-18, 14:07

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'),
'

'))
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 ""

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/ar...mail-Campaigns
das sieht dann so aus

ich habe es zunächst mit einer SQL Variable getestet

Code:

-- 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

HTML-Code:

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;

Viele Grüße
Rainer

KM · 19-02-18, 14:41

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:

HTML-Code:

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:

Click image for larger version.

Name: Testjson.jpg
Views: 10
Size: 57,8 KB
ID: 438

In der zweiten Zeile sollte eigentlich stehen "Grüße und Wünsche...".

Gruß,
KM

**Fuerchau** · 19-02-18, 15:15

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

Code:

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;

Click image for larger version.

Name: capture20180219162739441.png
Views: 12
Size: 28,3 KB
ID: 439

KM · 19-02-18, 15:30

Hallo Rainer,

stimmt, hier sind Umlaute vorhanden. Die werden auch richtig angezeigt.

Gruß,
KM

KM · 19-02-18, 15:38

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

**Fuerchau** · 19-02-18, 17:08

Warum kannst du das Ergebnis des "Select .. from Json..." nicht direkt mit
insert into MyTable
Select ... from Json...
speichern und dabei das Zielfeld der Tabelle als NVARCHAR oder gleich die Json-Tabelle mit
"Name" nvarchar(40)

laden?

KM · 20-02-18, 12:06

Ich hab das Problem nun gefunden. Und zwar hatte ich in meinem REST-Webservice, den ich mit der Java-Referenzimplementierung "Jersey" erstellt hatte, bei der "Produces"-Annotation folgendes angegeben:

Code:

@Produces({MediaType.APPLICATION_JSON , MediaType.APPLICATION_XML})

Ich bin hier davon ausgegangen, dass die Daten standardmäßig in UTF-8 zur Verfügung gestellt werden. In den Log-Files, die ich davor geschrieben hatte, waren die Daten zumindest noch UTF-8. Jetzt hab ich herausgefunden, dass man bei dieser Annotation das Charset explizit angeben muss. Das sieht dann so aus:

Code:

@Produces({MediaType.APPLICATION_JSON + "; charset=UTF-8", MediaType.APPLICATION_XML + "; charset=UTF-8"})

Damit passt nun alles und die Daten werden wie gewünscht in UTF-8 geliefert.

Trotzdem Danke für Eure Bemühungen!!!

Gruß,
KM

Thema: Webservice per SQL abfragen

Thread Tools

Bewerten Sie diesen Thema

Display