JSON Verilerini Web Servisleri ve Uygulamalarından SQL Server'a Alma
Minimum SQL Server 2016 olmak zorundadır.
versiyon bilgisini sorgulamak ve güvenlik seviyesini ayarlayabilmek için aşağıdaki kodu SQL Server da çalıştırabilirsiniz.
SELECT @@VERSION
ALTER DATABASE binance SET COMPATIBILITY_LEVEL = 130
Birçok uygulamayı desteklemek için, veritabanının JSON verileriyle çalışması mantıklıdır, çünkü bir JavaScript veya TypeScript uygulamasının nesne verilerini temsil etmesi için yerleşik bir yoldur. Bu daha az ağ trafiği, daha gevşek bağlantı ve uygulama geliştiricisinin veritabanının taban tablolarına tam erişim gerektirmesi için daha az ihtiyaç anlamına gelebilir. Ancak, veritabanının içe aktarılmadan önce çok sayıda kontrol yapması gerektiği anlamına gelir.
Veritabanı geliştiricilerinin veya DBA'ların JSON'u şüphe ile görmeleri için çeşitli nedenler var. Daha yüksek düzeydeki kısıtlamaların korunmasında kullanılırlar ve içe aktarılan verileri kontrol ederler: İçeri aktarmadan önce verilerin meta verilerinin doğru olduğundan emin olmak isterler: Verilerin kendisini de kontrol etmek isterler. En basit seviyede, bir veri kaynağından gelen düzenli bir beslemenin mümkün olduğu kadar güçlü olduğundan emin olmak isterler. Neden bu kadar telaşlı? Basitçe, kötü veriler herhangi bir boyutta bir veritabanına girdiğinde, çıkarmak yorucu ve zaman alıcıdır.
JSON verilerini web tabanlı uygulamalardan, özellikle tek sayfalık olanlardan almanın yanı sıra, birçok veritabanı, para birimlerinin listesi ve dolara karşı geçerli değerleri gibi web hizmetlerinden gelen değişken verilere dayanır. Her seferinde, JSON'un normalde olduğu gibi aynı meta veri veya veri yapısına sahip olduğundan ve verilerin geçerli olduğundan emin olmamız gerekir. Günümüzde, uygulama verileri genellikle bir JSON belgesi olarak gelse de, diğer belge tabanlı veri türleriyle benzer bir sorunumuz var: örneğin, iki şekilde var olan XML: iyi ve erdemli olan ancak iyi yaramaz kız kardeşi, şema-az veya yazılmamış XML. Yazılan XML, bir SQL Server veritabanında izin vermeniz gereken tek tür XML'dir. Sadece kullanımı daha güvenli olmakla kalmıyor, aynı zamanda çok daha verimli bir şekilde saklanıyor. Türlenmemiş XML, JSON ve YAML hepsi kontrol gerektirir.
JSON ile aynı şekilde XML yazdığımız gibi yapmak istiyoruz ve JSON Schema'nın eşeğin çalışmasına izin veriyoruz; Ancak, ne yazık ki, SQL Server şu anda herhangi bir JSON şeması bağlamasını desteklememektedir ve bu nedenle JSON'u 'derlenmiş' bir biçimde verimli bir şekilde depolayamamaktadır.
Baskın JSON Şeması aslında 'JSON Şeması' olarak adlandırılır ve IETF taslak 6'dadır. İnsan tarafından okunabilir JSON'da bir veri formatını tanımlayabilir ve tam bir yapısal doğrulama sağlamak için kullanılabilir. PostgreSQL, JSON'u doğrulamak için postgres-json-şemaya sahiptir, JavaScript'in bunu yapmak için birçok eklentisi vardır. Net programcıların Json.NET Schema ve diğer birçok alternatifi var.
PowerShell, JSON'unuzu JSON.net şeması ile doğrulamak için açık bir yerdir, ancak aslında, JSON verilerinizi kontrol etmek için SQL'de yapabileceğiniz pek çok şey vardır. Bir şeylerin değişip değişmediğini görmek için iki JSON belgesinin meta verilerini kolayca karşılaştırabilir ve JSON Verileri üzerinde kısıtlamalar uygulayabilirsiniz. Bu, yol ifadelerinin kullanımı nedeniyledir. Başlamak için, onları daha iyi tanımlayacağız, çünkü bunlar, JSON'un SQL Server'daki ciddi kullanımı için çok önemlidir.
Yol ifadeleri
SQL Server'daki bir JSON belgesindeki değerleri okumanız veya yazmanız gerektiğinde, JSON yol ifadelerini kullanırsınız. Bunlar nesnelere, dizilere veya değerlere referans verebilir.
İlişkisel tablo kaynağını döndürmek için WITH yantümcesine sahip OPENJSON'u çağırırsanız, JSON_VALUE'yu JSON metninden tek bir değer çıkarmak için veya bu değeri güncellemek veya ona eklemek için JSON_MODIFY'yi çağırırsanız, bu yol ifadeleri gerekir.
Yolun kendisi, içerik öğesini temsil eden bir dolar işareti ($) ile başlar. Yol, 'noktalar' veya tam duraklarla ayrılmış öğelerden oluşur.
Özellik yolu, isteğe bağlı olarak 'alıntılanan' Anahtar adlarından oluşan bir dizi yol adımıdır. (örneğin, $ .info.address.county veya $ .info.address. ”posta kodu” .outward ). Her nokta Lvalue (sol taraf değeri), RValue’nin ebeveyni (sağ taraf değeri) gösterir. Anahtar adı bir dolar işaretiyle başlıyorsa veya boşluklar gibi özel karakterler içeriyorsa, anahtar için çift tırnaklı ayırıcılar kullanmanız gerekir (örn. $ .İnfo.Cost. ”La Posche Hotel” ). Anahtar ismi bir diziye atıfta bulunuyorsa, sıfır tabanlı bir dizine göre dizine eklenir. (örneğin, $ .info.tags [0] veya $ .info.tags [1] )
Yol birden fazla var olan bir nesneye başvuruyorsa, ne JSON_Value ne de JSON_modify ikinci veya daha sonraki değerlere erişemez. Bu durumda, tüm değerlere ulaşmak için OpenJSON kullanmanız gerekir.
SQL Server'daki JSON yolları 'lax' veya 'strict' anahtar kelimesiyle başlayabilir. JSON belgesinde bir yol bulunmazsa, hataları bastırmak için alışılmadık bir gereksinimdir, ancak bu varsayılandır ve bunu 'lax' kullanarak belirtebilirsiniz. 'Katı' mod isteme olasılığınız daha yüksek olur, ancak NULL dönüş değerinde bir şeyin yanlış olduğunu bildiğiniz için bir yol değerinin olup olmadığını test etmek istiyorsanız 'lax' daha iyidir
Yol ifadelerini kolayca kullanabilirsiniz. Muhtemelen ihtiyacınız olan ilk işlev, bu ifadelerin gerçekte herhangi bir JSON belgesi için gerçekte ne olduğunu söyleyen bir yordamdır.
Bir JSON dizesinde veya belgesinde hangi yolların bulunduğunu bulma
İşte bir JSON dizesini alan ve belirttiğiniz JSON için ifadeleri, veri türlerini ve değerlerini içeren bir tablo kaynağı döndüren bir yordam.
IF Object_Id('dbo.JSONPathsAndValues') IS NOT NULL DROP FUNCTION dbo.JSONPathsAndValues;
GO
CREATE FUNCTION dbo.JSONPathsAndValues
/**
Summary: >
This function takes a JSON string and returns
a table containing the JSON paths to the data,
and the data itself. The JSON paths are compatible with OPENjson,
JSON_Value and JSON_Modify.
Author: PhilFactor
Date: 06/10/2017
Version: 2
Database: PhilFactor
Examples:
- Select * from dbo.JSONPathsAndValues(N'{"person":{"info":{"name":"John", "name":"Jack"}}}')
- Select * from MyTableWithJson cross apply dbo.JSONPathsAndValues(MyJSONColumn)
Returns: >
A table listing the paths to all the values in the JSON document
with their type and their order and nesting depth in the document
**/
(@JSONData NVARCHAR(MAX))
RETURNS @TheHierarchyMetadata TABLE
(
-- columns returned by the function
element_id INT NOT NULL,
Depth INT NOT NULL,
Thepath NVARCHAR(2000),
ValueType VARCHAR(10) NOT NULL,
TheValue NVARCHAR(MAX) NOT NULL
)
AS
-- body of the function
BEGIN
DECLARE @ii INT = 1, @rowcount INT = -1;
DECLARE @null INT = 0, @string INT = 1, @int INT = 2, --
@boolean INT = 3, @array INT = 4, @object INT = 5;
DECLARE @TheHierarchy TABLE
(
element_id INT IDENTITY(1, 1) PRIMARY KEY,
Depth INT NOT NULL, /* effectively, the recursion level. =the depth of nesting*/
Thepath NVARCHAR(2000) NOT NULL,
TheName NVARCHAR(2000) NOT NULL,
TheValue NVARCHAR(MAX) NOT NULL,
ValueType VARCHAR(10) NOT NULL
);
INSERT INTO @TheHierarchy
(Depth, Thepath, TheName, TheValue, ValueType)
SELECT @ii, '$', '$', @JSONData, 'object';
WHILE @rowcount <> 0
BEGIN
SELECT @ii = @ii + 1;
INSERT INTO @TheHierarchy
(Depth, Thepath, TheName, TheValue, ValueType)
SELECT @ii,
CASE WHEN [Key] NOT LIKE '%[^0-9]%' THEN Thepath + '[' + [Key] + ']' --nothing but numbers
WHEN [Key] LIKE '%[$ ]%' THEN Thepath + '."' + [Key] + '"' --got a space in it
ELSE Thepath + '.' + [Key] END, [Key], Coalesce(Value,''),
CASE Type WHEN @string THEN 'string'
WHEN @null THEN 'null'
WHEN @int THEN 'int'
WHEN @boolean THEN 'boolean'
WHEN @int THEN 'int'
WHEN @array THEN 'array' ELSE 'object' END
FROM @TheHierarchy AS m
CROSS APPLY OpenJson(TheValue) AS o
WHERE ValueType IN
('array', 'object') AND Depth = @ii - 1;
SELECT @rowcount = @@RowCount;
END;
INSERT INTO @TheHierarchyMetadata
SELECT element_id, Depth, Thepath, ValueType, TheValue
FROM @TheHierarchy
WHERE ValueType NOT IN
('array', 'object');
RETURN;
END;
GO
Bununla, bir json verisinin değerlerinde ne olduğunu görebilirsiniz.
DECLARE @JSONData NVARCHAR(4000) = N'
{
"info": {
"type": 1,
"address": {
"town": "Colchester",
"county": "Essex",
"country": "England"
},
"Hotels": {
"La Posche Hotel": "$400",
"The Salesmans Rest": "$35",
"The Middling Inn": "$100"}
},
"Sights": ["the Castle","The Barracks","the Hythe","St Bartolphs"]
} ';
SELECT * from dbo.JSONPathsAndValues(@JSONData)
İki JSON dizesinin meta verileri arasındaki farklar
Aynı meta verilere sahip olduklarından emin olmak için iki JSON dizesini kontrol etmek ve bulduğu farklılıkları rapor etmek çok kolaydır.
IF Object_Id('dbo.DifferenceBetweenJSONstrings') IS NOT NULL
DROP function dbo.DifferenceBetweenJSONstrings
GO
CREATE FUNCTION dbo.DifferenceBetweenJSONstrings
/**
Summary: >
This checks two JSON strings and returns
a table listing any differences in the schema between them
Author: PhilFactor
Date: 20/10/2017
Database: PhilFactor
Examples:
- Select * from dbo.DifferenceBetweenJSONstrings(@Original,@new)
- Select from MyTable cross apply dbo.DifferenceBetweenJSONstrings(FirstJ)
Returns: >
A table
**/
(
@Original nvarchar (max),-- the original JSON string
@New nvarchar (max) -- the New JSON string
)
RETURNS TABLE
--WITH ENCRYPTION|SCHEMABINDING, ..
AS
RETURN
(
SELECT Coalesce(old.thePath, new.thepath) AS JSONpath,
Coalesce(old.valuetype, '')
+ CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN ' \ ' ELSE '' END
+ Coalesce(new.valuetype, '') AS ValueType,
CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN 'value type changed'
WHEN old.thePath IS NULL THEN 'added or key changed'
WHEN new.thePath IS NULL THEN 'missing' ELSE 'dunno' END AS TheDifference
FROM dbo.JSONPathsAndValues(@New) AS new
FULL OUTER JOIN dbo.JSONPathsAndValues(@original) AS old
ON old.ThePath = new.ThePath --AND old.Valuetype=new.Valuetype
WHERE old.thepath IS NULL OR new.thepath IS NULL OR old.ValueType <> new.ValueType
);
Go
Yapının bir parçası olarak, işlevin beklediğiniz şeyi yapmasını sağlamak için böyle bir kod çalıştırarak test edebilirsiniz.
Select * from dbo.DifferenceBetweenJSONstrings(
'[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')
Select * from dbo.DifferenceBetweenJSONstrings(
'[1,2,3,4,5,6]','[1,"2",3,4,5,6]')
Select * from dbo.DifferenceBetweenJSONstrings(
'{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
'{"id": "004","type": "Coupe","name": "Jaguar","year": "2012"}')
Select * from dbo.DifferenceBetweenJSONstrings(
'{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
'{"id": "001","type": "Coupe","name": "Cougar","start": "2012"}')
DECLARE @TestData TABLE (JSONpath NVARCHAR(MAX),ValueType VARCHAR(20), TheDifference VARCHAR(20))
INSERT INTO @testdata
SELECT JSONpath,ValueType,TheDifference
FROM dbo.DifferenceBetweenJSONstrings(
'[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')
UNION ALL SELECT JSONpath,ValueType,TheDifference
FROM dbo.DifferenceBetweenJSONstrings(
'[1,2,3,4,5,6]','[1,"2",3,4,5,6]')
UNION all Select JSONpath,ValueType,TheDifference
FROM dbo.DifferenceBetweenJSONstrings(
'{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
'{"id": "004","type": "Coupe","name": "Jaguar","year": "2012"}')
UNION all Select JSONpath,ValueType,TheDifference
FROM dbo.DifferenceBetweenJSONstrings(
'{"id": "001","type": "Coupe","name": "Cougar","year": "2012"}',
'{"id": "001","type": "Coupe","name": "Cougar","start": "2012"}')
IF (EXISTS(
SELECT * FROM @testData g
FULL OUTER JOIN
(VALUES('$[6]','int','missing'),
('$[1]','int \ string','value type changed'),
('$.START','string','added or key changed'),
('$.year','string','missing'))f(JSONpath,ValueType,TheDifference)
ON f.JSONpath=g.JSONpath
WHERE f.JSONpath IS NULL OR g.JSONpath IS NULL))
RAISERROR ('the dbo.DifferenceBetweenJSONstrings routine is
giving unexpected results;',16,1)
Şimdi iki JSON dizesinin meta verilerini karşılaştırabiliriz, ancak iki JSON belgesinin aynı verileri temsil ettiğini kontrol etmemiz gerekirse, değerleri de karşılaştırabiliriz.
JSON'un beklediğiniz gibi olup olmadığını kontrol etme
Web Servislerinin çoğu, beklediğiniz veriler yerine, bilgi mesajları veya uyarı mesajları JSON formatında gönderilir. Hizmet kesintisi, abonelik sonlandırmaları veya ulaşılan günlük kredi limitleri gibi çeşitli mesajlar içerebilir. Bunların kaydedilmesi ve ithalat rutini atlamanız gerekir. Muhtemelen JSON'unuzu kontrol etmenin en hızlı yolu, NULL elde edip etmediğinizi görmek için orada olması gereken bir anahtar / değer çiftinde basit bir JSON_VALUE çağrısı yapmaktır. Bu yolu ilk etapta almak için JSONPathsAndValues işlevini kullanabilirsiniz . Ancak, orada olması gereken birkaç anahtar / değer çifti olması mümkündür. İhtiyacınız olan yolların bir listesi veya tablosu varsa, bunu yapmanın birçok yolu vardır. Noktayı göstermek için VALUES tablo kaynağı kullanacağım.
DECLARE @json NVARCHAR(MAX) = N'[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]'
IF EXISTS(
SELECT thepath
FROM
(VALUES
('$[0].AccountNumber'),
('$[0].Order.Number'),
('$[0].Order.Date'),
('$[0].Item.Price'),
('$[0].Item.Quantity'))WhatThereShouldBe(path)
LEFT OUTER JOIN dbo.JSONPathsAndValues( @json )
ON WhatThereShouldBe.path=ThePath
WHERE ThePath IS NULL)
RAISERROR ('an essential key is missing',16,1)
Meta verilerin yanı sıra değerleri karşılaştırma
Anahtarları kontrol etmek için kodda çok küçük bir değişiklik, daha önce yapmanız gerektiğinde değerleri karşılaştırmanıza da izin verecektir. Listelerin sırasını da kontrol ettiğini unutmayın.
SELECT FirstVersion.Thepath, SecondVersion.Thepath, FirstVersion.TheValue, SecondVersion.TheValue
FROM dbo.JSONPathsAndValues('[1,2,3,4,5,7,8]') AS FirstVersion
FULL OUTER JOIN dbo.JSONPathsAndValues('[1,2,3,4,5,6]') AS SecondVersion
ON FirstVersion.Thepath = SecondVersion.Thepath
WHERE FirstVersion.TheValue <> SecondVersion.TheValue
OR FirstVersion.Thepath IS NULL
OR SecondVersion.Thepath IS NULL;
Gördüğünüz gibi, JSON dizeleri arasında iki fark var. Aynı dizi elemanı için farklı değerler var (sonuçtaki satır 1) ve farklı sayıda dizi elemanı var (satır 2).
Geçerli SQL Veri Tipleri denetleniyor.
Olduğu kadarıyla sorun değil, ama verilerin gerçekten SQL Server'a girip girmeyeceğini kontrol etmeye ne dersiniz? Örneğin, JSON'da tarih depolamak için iyi uygulamalar olmasına rağmen, örneğin, JSON standardı yoktur. Kısıtlarımızı biliyorsak, kontrol etmesi çok kolaydır. Belirli bir veri türünde olan bazı JSON'larımız olduğunu hayal edin. Sadece kontrol etmek istediğimiz değerlerin yolunu tanımlıyoruz ve JSON'da ne gerekiyorsa yapalım kontrol ediyoruz. Bu tekniği gösterebiliriz.
SELECT g.ThePath, g.TheValue, CASE Coalesce(f.Datatype,'')
WHEN '' THEN '' --it hasnt had a check specified
WHEN 'int' THEN --need to check that it is a valid int
CASE WHEN Try_Convert(INT,g.TheValue) IS NULL THEN 'Bad int' ELSE 'good' end
WHEN 'DateTime' THEN --need to check that it is a valid DateTime
CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad datetime' ELSE 'good' end
WHEN 'Money' THEN --need to check that it is a valid Money value
CASE WHEN Try_Convert(Money,g.TheValue) IS NULL THEN 'Bad money value' ELSE 'good' END
WHEN 'ISO8601' THEN --need to check that it is a valid ISO8601 datetime
CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad ISO8601 datetime' ELSE 'good' end
ELSE '' end
FROM dbo.JSONPathsAndValues('["1.7","2","23/4/2008","1 Jun 2017","5.6d","$456,000","2017-09-12T18:26:20.000","2017/10/20T18:26:20.000"]')g
LEFT outer JOIN
(VALUES('$[0]','int'),
('$[1]','int'),
('$[2]','DateTime'),
('$[3]','DateTime'),
('$[4]','money'),
('$[5]','money'),
('$[6]','ISO8601'),
('$[7]','ISO8601'))f(Thepath,DataType)
ON f.thepath=g.Thepath
JSON'un yanı sıra, her bir değerin ne tür bir veri olması gerektiğini söylemek için ayrı bir tablo kaynağına ihtiyaç duyacağınızı ve kısıtlamanızı daraltabileceğinizi göstermek için özel bir tarih biçimi yaptım. Bir VALUES tablo kaynağı kullandım, ancak kolayca bir JSON içinden takas edebilirsiniz. Bu tablo kaynağının çalışması için case ifadesiyle senkronize tutulması gerekir. Çalışan bir sistemde, tüm bunları bir işlevde saklamak istersiniz.
Değerlerin sınır kontrollerini geçip geçmeyeceğini kontrol etme
Peki, tüm bu IP adreslerinin geçerli olduğundan emin olmak için bu basit örneği kontrol etme görevini nasıl ele alırsınız? Bu örnek için sadece üç nokta olup olmadığını kontrol edeceğiz. Belli ki adı kontrol etmek istemiyoruz.
{
"name":[
"Philip",
"Mildew",
"Factor"
],
"ipAddress":[
"80.243.543.4",
"45.85.678.68",
"5.8.7.9",
"192.168.0.123",
"34.8.8"
]
}
Bu son IP adresini veritabanında istemezsin. Geçerli değil. Sadece bu IP değerlerini kontrol etmek istiyorsun. Burada, kontrolü çok kolay bir şekilde yapabilirsiniz.
SELECT ThePath, TheValue
FROM dbo.JSONPathsAndValues('{"name":["Philip","Mildew","Factor"],"ipAddress":[ "80.243.543.4","45.85.678.68","5.8.7.9","192.168.0.123","34.8.8"]}')
WHERE ThePath LIKE '$.ipAddress%' AND TheValue NOT LIKE '%.%.%.%'
Ve kötü IP adresini göreceksiniz, ancak yalnızca istediğiniz IP adresleri listesindeki denetimi gerçekleştiriyor.
Yapmanız gereken çok sayıda çek varsa, tablo kaynağını bir tablo değişkeni veya geçici tablo olarak kaydeder ve üzerinde birkaç sorgu çalıştırırsınız.
Bir şeyleri büyütmek
Normalde büyük miktarlarda JSON verisi ile ilgileniyorsunuz, bu nedenle tüm meta veri denetimlerinizi çalıştırmadan önce yalnızca bir kez geçici bir tabloya veya tablo değişkenine ayrıştırmak istiyorsunuz. Bu şekilde yapmanın avantajı, yola iyi bir birincil anahtar yerleştirdiğinizde (yinelenen JSON anahtarlarına dikkat edin: bunlar geçerli JSON - RFC 4627'dir), öncelikle verilerin başarılı bir şekilde zorlanabildiğini kontrol etme süreci Hedef tablonun uygun sütununa girdikten sonra, hiyerarşik JSON verilerini tüm sırayla ilişkisel tablolara doğru sırayla açmadan önce sınırlar dahilinde olduğunu kontrol etmek çok daha kolay ve iyi kontrol edilir.
SQL Server’dan bir JSON Web sunucusu kullanma
Normalde, normal bir üretim veri beslemesi için SSIS veya Powershell kullanırsınız, ancak bunu SQL'de yapmak kesinlikle mümkündür. Dezavantajı ise, SQL Server'ınızın internet erişimine sahip olması, bu da bir güvenlik riskidir ve ayrıca OLE otomasyonuna izin vererek güvenlik yüzey alanınızı açmanız gerekir. Bu, nasıl yaptığınızı söyledi.
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name ='Ole Automation Procedures' AND value=1)
BEGIN
EXECUTE sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
end
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
IF Object_Id('dbo.GetWebService','P') IS NOT NULL
DROP procedure dbo.GetWebService
GO
CREATE PROCEDURE dbo.GetWebService
@TheURL VARCHAR(255),-- the url of the web service
@TheResponse NVARCHAR(4000) OUTPUT --the resulting JSON
AS
BEGIN
DECLARE @obj INT, @hr INT, @status INT, @message VARCHAR(255);
/**
Summary: >
This is intended for using web services that
utilize JavaScript Object Notation (JSON). You pass it the link to
a webservice and it returns the JSON string
Note: >
OLE Automation objects can be used within a Transact-SQL batch, but
SQL Server blocks access to OLE Automation stored procedures because
this component is turned off as part of the security configuration.
Author: PhilFactor
Date: 26/10/2017
Database: PhilFactor
Examples:
- >
DECLARE @response NVARCHAR(MAX)
EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
SELECT @response
Returns: >
nothing
**/
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;
SET @message = 'sp_OAMethod Open failed';
IF @hr = 0 EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @TheURL, false;
SET @message = 'sp_OAMethod setRequestHeader failed';
IF @hr = 0
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded';
SET @message = 'sp_OAMethod Send failed';
IF @hr = 0 EXEC @hr = sp_OAMethod @obj, send, NULL, '';
SET @message = 'sp_OAMethod read status failed';
IF @hr = 0 EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT;
IF @status <> 200 BEGIN
SELECT @message = 'sp_OAMethod http status ' + Str(@status), @hr = -1;
END;
SET @message = 'sp_OAMethod read response failed';
IF @hr = 0
BEGIN
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @Theresponse OUT;
END;
EXEC sp_OADestroy @obj;
IF @hr <> 0 RAISERROR(@message, 16, 1);
END;
GO
Bunu kullanmak basittir. Bazı json test örenkleri ile test edebilirsiniz.
DECLARE @response NVARCHAR(MAX)
EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
SELECT @response
Sonuç?
{
"X-Cloud-Trace-Context": "54e570f5620dc6ef3b087ac6042dca03/10421626717945848480",
"Host": "headers.jsontest.com",
"User-Agent": "Mozilla/4.0 (compatible; Win32; WinHttp.WinHttpRequest.5)",
"Accept": "*/*",
"Content-Type": "application/x-www-form-urlencoded"
}
Şimdi gerçek bir hizmet alma imkanımız var, deneyebiliriz. Bu web servisi verileriniz yerine size mesaj gönderebilir, bu yüzden bunları kontrol edip kaydettiğinizden emin olun.
status": {
"message": "the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.",
"value": 19
}}
Sonraki grupta, bu tür bir mesajı yalnızca hata olarak gösteriyoruz.
Msg 50000, Level 16, State 1, Line 30
The import failed ({"status": {
"message": "the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.",
"value": 19
}})
Bu durumda, demo modundan çok kendi hesabımı kullanmalıydım. URL’deki 'demo ’adı saatte birkaç kez çalışacak olsa da, takılıp kalıyorsanız, kendi kayıtlı adınız için bunu değiştirmeniz gerekecektir.
DECLARE @response NVARCHAR(4000);
--get the data from the provider as JSON
EXECUTE dbo.GetWebService 'http://api.geonames.org/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',
@response OUTPUT;
--now check to see if it is all there.
IF EXISTS
(
SELECT * FROM dbo.JSONPathsAndValues(@response)
WHERE Thepath IN
('$.geonames[0].lng', '$.geonames[0].geonameId', '$.geonames[0].countrycode', '$.geonames[0].name',
'$.geonames[0].fclName', '$.geonames[0].toponymName', '$.geonames[0].fcodeName',
'$.geonames[0].wikipedia', '$.geonames[0].lat', '$.geonames[0].fcl', '$.geonames[0].population',
'$.geonames[0].fcode'
)
)
BEGIN
SELECT CountryCode, name, population, latitude, longitude, id, WikipediaURL
FROM OpenJson(@response) --we have to walk to the level of the array that
--we are interested in. OPENjson doesn't support accessing an array at
--a higher level when using the WITH clause.
OUTER APPLY
OpenJson(Value)
WITH
(CountryCode CHAR(2) '$.countrycode', Latitude NUMERIC(38, 15) '$.lat',
Longitude NUMERIC(38, 15) '$.lng', Name VARCHAR(200) '$.name',
Population BIGINT '$.population', wikipediaURL VARCHAR(200) '$.wikipedia',
id INT '$.geonameId'
);
END;
ELSE RAISERROR('The import failed (%s)', 16, 1, @response);
Tabii ki yapabileceğin daha çok şey var. Örneğin, verilerin tablodaki veri tiplerine uyduğunu kontrol edebilir ve ihtiyacınız olan diğer kısıtlama kontrollerini yapabilirsiniz.
Dso çıktısındakileri kullanarak, OpenJSON'un son parçasının jimnastikçiliğinden vazgeçebilirsin.JSONPathsAndValues
DECLARE @response NVARCHAR(4000);
--get the data from the provider as JSON
EXECUTE dbo.GetWebService 'http://api.geonames.org/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',
@response OUTPUT;
DECLARE @TheData table (
-- columns returned by the function
element_id INT NOT NULL,
Depth INT NOT NULL,
Thepath NVARCHAR(2000),
ValueType VARCHAR(10) NOT NULL,
TheValue NVARCHAR(MAX) NOT NULL
)
INSERT INTO @TheData SELECT * FROM dbo.JSONPathsAndValues(@response)
SELECT
Max(Convert(CHAR(2),CASE WHEN Thepath LIKE '%.countrycode' THEN Thevalue ELSE '' END)) AS countycode,
Max(Convert(NVARCHAR(200),CASE WHEN Thepath LIKE '%.name' THEN Thevalue ELSE '' END)) AS name,
Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lat' THEN Thevalue ELSE '-90' END)) AS latitude,
Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lng' THEN Thevalue ELSE '-180' END)) AS longitude,
Max(Convert(BigInt,CASE WHEN Thepath LIKE '%.population' THEN Thevalue ELSE '-1' END)) AS population,
Max(Convert(VARCHAR(200),CASE WHEN Thepath LIKE '%.wikipedia' THEN Thevalue ELSE '' END)) AS wikipediaURL,
Max(Convert (INT,CASE WHEN Thepath LIKE '%.geonameId' THEN Thevalue ELSE '0' END)) AS ID
FROM @TheData GROUP BY Left(ThePath,CharIndex(']',ThePath+']'))
özet
Bazı JSON'ları SQL Server'a içe aktarmadan önce, her türlü kontrolü nasıl çalıştırabileceğinizi göstermeye karar verdim, örneğin JSON meta verilerinin beklediğiniz gibi olmasını sağlamak, istediğiniz bilgilerin tam yollarını bulmak veya değerler, veri türleri ve aralık içinde geçerlidir. JSON belgesinde ne olduğunu incelemek zorunda kalmadan çözmek kolaydır. Size bir web servisinden basit bir özet akışı göstererek, umarım istediğiniz kadar sağlam hale getirebileceğinizi göstermişimdir. Tabii ki yapabileceğiniz daha çok şey var, ama umarım size JSON formatında veri kabul etme konusunda kendinizden emin olmanız için yeterince şey göstermiştim.
Bu site kuruluş amacının özelliği gereği aslında bir nevi kendime online help desk oluşturmak ve bu deski de herkesle paylaşmak istediğimden bazı makaleler alıntı olabilmektedir. Anlayışınıza sığınarak orjinal emeğe saygımız da sonsuz olduğundan makale linkine tıklayarak ulaşabilirsiniz.
Bilgi paylaştıkça çoğalır ve insanlar güzelleşir.. :-)
Sizde paylaşmayı unutmayın lütfen..
İyi çalışmalar.