(SQL Server) SQL Server JCC Payment Systems - Get a User's Credential IDs for CSC

See more JCC Cyprus Examples

Demonstrates how to find the credential ids for a given user to be used with remote signing via the CSC (Cloud Signature Consortium) API.

Note: This example requires Chilkat v10.0.2 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Provide information about the CSC service and our user ID and client credentials.
    DECLARE @jsonCsc int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonCsc OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'service', 'CSC'
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'baseUrl', 'https://ras-test.jcc.com.cy/adss/service/ras/csc/v1/'

    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'userId', 'YOUR_USER_ID'
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'clientId', 'YOUR_CLIENT_ID'
    EXEC sp_OAMethod @jsonCsc, 'UpdateString', @success OUT, 'clientSecret', 'YOUR_CLIENT_SECRET'

    -- The call to SetCloudSigner will do the following internally:
    -- 1) Calls the "info" endpoint to get information about the remote service and the list of the API methods it supports.
    -- 2) Calls the "oauth2/token" endpoint to get the OAuth2 authorization token via client credentials (using the clientId and clientSecret).
    -- 3) Calls the "credentials/list" endpoint to get the list of credentials associated with the userId.
    -- 4) Calls the "credentials/info" endpoint to retrieve the credential and return the main identity information 
    --    and the public key certificate or the certificate chain associated to it.
    --    The Chilkat certificate object is loaded with the retrieved certificate.

    -- The jsonCsc is updated with the information returned from each of the above calls.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonCsc
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonCsc
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    EXEC sp_OASetProperty @jsonCsc, 'EmitCompact', 0

    EXEC sp_OAMethod @jsonCsc, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- We can do the following to get the credential IDs
    DECLARE @i int
    SELECT @i = 0
    DECLARE @numIds int
    EXEC sp_OAMethod @jsonCsc, 'SizeOfArray', @numIds OUT, 'credentials_list.credentialIDs'
    WHILE @i < @numIds
      BEGIN
        EXEC sp_OASetProperty @jsonCsc, 'I', @i
        DECLARE @cred_id nvarchar(4000)
        EXEC sp_OAMethod @jsonCsc, 'StringOf', @cred_id OUT, 'credentials_list.credentialIDs[i]'

        PRINT 'Credential ID: ' + @cred_id
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @jsonCsc
    EXEC @hr = sp_OADestroy @cert


END
GO