Zendesk API connect with SQL Server database-CodePudding

In my project I could connect Zendesk API with our intern SQL Server database and I became some tickets in JSON format in the database. The problem is that I have to work with pagination or use the Unix time as start to have all records (tickets) from the Zendesk system. If I use the Unix time variant, I can reach only 1000 tickets, but I think I could export all of the tickets with pagination. I saw the documentation about the pagination, but I need some advice, how can I use it.

https://example.zendesk.com/api/v2/tickets.json?page[size]=100

is the link for the first 100 tickets. As next I have to say, that I would like to have the next page.

https://example.zendesk.com/api/v2/tickets.json?page[after]=100

I tried to combine the to queries, but it doesn't work.

https://example.zendesk.com/api/v2/tickets.json?page[size]=100&page[after]=100

Is anybody here who could give me some advice please?

CodePudding user response：

It is recommended to use Cursor-based incremental exports

Use the following path for the initial request: https://example.zendesk.com/api/v2/incremental/tickets/cursor.json?start_time={unix_time}

So, to get tickets from the start of the instance, you can simply set unix_time to 0. Your JSON response should be as follows:

{
  "tickets": [...],
  "after_url": "https://example.zendesk.com/api/v2/incremental/tickets/cursor.json?cursor=MTU4MDc1Mzc5OC4wfHw0MzJ8",
  "after_cursor": "MTU4MDc1Mzc5OC4wfHw0MzJ8",
  ...
  "end_of_stream": false
}

Then simply call after_url for the next page if end_of_stream is false

CodePudding user response：

Den Code habe ich in Visual Studio als Klassenbibliothek .NET Framework (dll)geschrieben.

namespace ClassLibrary1
{
    public class Class1
    {
        [SqlFunction()]
        public static SqlString GetResponce(String url,String username, String password)
        {

            
            ServicePointManager.Expect100Continue = true;
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

            String svcCredentials=Convert.ToBase64String(Encoding.ASCII.GetBytes((username   ":"   password)));
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Headers.Add("Authorization", "Basic "   svcCredentials);
                request.Method = "GET";

                HttpWebResponse resp = (HttpWebResponse)request.GetResponse();
                StreamReader reader = new StreamReader(resp.GetResponseStream());
                SqlString result = new SqlString(reader.ReadToEnd());
           
                Console.WriteLine(result);
                Console.ReadLine();
                return result;

Das wäre der Code, wodurch ich die Zendesk API erreiche. Anhand der Code habe ich ein Assembly in der SSMS erstellt, praktisch den Code in der DB verwenden zu können. Mithilfe einer Funktion greife ich der Assembly zu:

CREATE FUNCTION [dbo].[Ticketrueckgabe](@url [nvarchar](max), @username [nvarchar](max), @password [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [ClassLibrary1].[ClassLibrary1.Class1].[GetResponce]
GO

Nachdem habe ich eine SQL Abfrage(um die Funktion aufzurufen) geschrieben um die Daten anzuzeigen:

declare @json nvarchar(max);
set @json=(select dbo.Ticketrueckgabe('https://*****.zendesk.com/api/v2/tickets.json?[size]=100&page[after]=100','*benutzername*','*passwort*'));
select @json;

SELECT * FROM OpenJson(@json, '$.tickets');

Die * habe ich verwendet, um vertrauliche Daten nicht weiterzugeben.

Mein Problem ist, dass ich maximum 100 Tickets abrufen kann und die Paginierung bekomme ich nicht hin. Bei der SQL Abfrage bekomme ich eine Fehlermeldung(ich vermute es handelt sich um eine Syntax Fehler).