Chilkat Examples

ChilkatHOMEAndroid™Classic ASPCC++C#Mono C#.NET Core C#C# UWP/WinRTDataFlexDelphi ActiveXDelphi DLLVisual FoxProJavaLianjaMFCObjective-CPerlPHP ActiveXPHP ExtensionPowerBuilderPowerShellPureBasicCkPythonChilkat2-PythonRubySQL ServerSwift 2Swift 3/4TclUnicode CUnicode C++Visual Basic 6.0VB.NETVB.NET UWP/WinRTVBScriptXojo PluginNode.jsExcelGo

SQL Server Examples

ASN.1
Amazon S3
Amazon S3 (new)
Amazon SES
Amazon SNS
Amazon SQS
Async
Azure Cloud Storage
Azure Service Bus
Base64
Bounced Email
Box
CSR
CSV
Certificates
Compression
DKIM / DomainKey
DSA
Diffie-Hellman
Digital Signatures
Dropbox
Dynamics CRM
ECC
Email Object
Encryption
FTP
Facebook
FileAccess
Firebase
GMail REST API
GeoOp
Google APIs
Google Calendar
Google Cloud Storage
Google Drive
Google Sheets
Gzip
HTML-to-XML/Text
HTTP
HTTP Misc
IMAP
JSON
JSON Web Encryption (JWE)
JSON Web Signatures (JWS)
JSON Web Token (JWT)

Java KeyStore (JKS)
Jira
MHT / HTML Email
MIME
Microsoft Graph
NTLM
OAuth1
OAuth2
OneDrive
OpenSSL
Outlook
PEM
PFX/P12
POP3
PRNG
PayPal
Peoplevox
QuickBooks
REST
REST Misc
RSA Encryption
SCP
SFTP
SMTP
SSH
SSH Key
SSH Tunnel
SharePoint
Shopify
Socket/SSL/TLS
Spider
Stream
Stripe
SugarCRM
Tar Archive
Twitter
Upload
VoiceBase
Walmart
WebSocket
XAdES
XML
XML Digital Signatures
XMP
Xero
Zip
curl
eBay

 

 

 

(SQL Server) Xero API through an SSH Tunnel

This example demonstrates connecting through an SSH Tunnel w/ 2-legged OAuth for a Xero private application.

This example requires Chilkat v9.5.0.64 or later

Chilkat ActiveX Downloads

ActiveX for 32-bit and 64-bit Windows

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    --  This example requires Chilkat v9.5.0.64 or later

    DECLARE @tunnel int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Socket', @tunnel OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @sshHostname nvarchar(4000)
    SELECT @sshHostname = 'www.my-ssh-server.com'
    DECLARE @sshPort int
    SELECT @sshPort = 22

    --  Connect to an SSH server and establish the SSH tunnel:
    DECLARE @success int
    EXEC sp_OAMethod @tunnel, 'SshOpenTunnel', @success OUT, @sshHostname, @sshPort
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    --  Authenticate with the SSH server via a login/password
    --  or with a public key.
    --  This example demonstrates SSH password authentication.
    EXEC sp_OAMethod @tunnel, 'SshAuthenticatePw', @success OUT, 'mySshLogin', 'mySshPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    --   OK, the SSH tunnel is setup.  Now open a channel within the tunnel.
    --   (Any number of channels may be created from the same SSH tunnel.
    --   Multiple channels may coexist at the same time.)

    --  This sample code would typically be placed in a subroutine or function
    --  where the rest object is passed by reference.
    --  It does the OAuth1 setup and makes the initial connection.
    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT

    DECLARE @channel int

    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 7000

    --  This returns a socket object that is a single channel within the SSH tunnel.
    EXEC sp_OAMethod @tunnel, 'SshOpenChannel', @channel OUT, 'api.xero.com', @port, @bTls, @maxWaitMs
    EXEC sp_OAGetProperty @tunnel, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    --  Use the connection.  (This connection is a TLS running on an SSH channel through an SSH tunnel.
    --  In other words, TLS is wrapped within the SSH tunnel.)
    EXEC sp_OAMethod @rest, 'UseConnection', @success OUT, @channel, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @channel

        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    --  OK, we're connected.
    --  -----------------------------------------------------------------
    --  Now setup the OAuth1 authenticator..

    DECLARE @consumerKey nvarchar(4000)
    SELECT @consumerKey = 'XERO_PRIVATE_APP_KEY'
    DECLARE @consumerSecret nvarchar(4000)
    SELECT @consumerSecret = 'XERO_PRIVATE_APP_SECRET'

    --  Let's get our private key from our PFX (password protected), or the PEM (unprotected).
    --  You can decide which to use.  Either is OK, although I would recommend keeping your
    --  private keys in a PFX and not in an unprotected PEM.
    DECLARE @pfx int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Pfx', @pfx OUT

    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/certs/xero_private_app/public_privatekey.pfx', 'PFX_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END
    DECLARE @privKeyFromPfx int
    EXEC sp_OAMethod @pfx, 'GetPrivateKey', @privKeyFromPfx OUT, 0
    EXEC sp_OAGetProperty @pfx, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    --  Or we can load from a PEM..
    DECLARE @privKeyFromPem int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.PrivateKey', @privKeyFromPem OUT

    EXEC sp_OAMethod @privKeyFromPem, 'LoadPemFile', @success OUT, 'qa_data/certs/xero_private_app/privatekey.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKeyFromPem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        RETURN
      END

    --  Note: There are many other means for loading a private key, including
    --  from other formats and directly from memory (i.e. not file-based).

    DECLARE @oauth1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.OAuth1', @oauth1 OUT

    EXEC sp_OASetProperty @oauth1, 'ConsumerKey', @consumerKey
    EXEC sp_OASetProperty @oauth1, 'ConsumerSecret', @consumerSecret
    EXEC sp_OASetProperty @oauth1, 'Token', @consumerKey
    EXEC sp_OASetProperty @oauth1, 'TokenSecret', @consumerSecret
    EXEC sp_OASetProperty @oauth1, 'SignatureMethod', 'RSA-SHA1'
    EXEC sp_OAMethod @oauth1, 'SetRsaKey', @success OUT, @privKeyFromPfx
    EXEC @hr = sp_OADestroy @privKeyFromPfx

    --  Install the OAuth1 authenticator.
    EXEC sp_OAMethod @rest, 'SetAuthOAuth1', @success OUT, @oauth1, 0


    PRINT 'OK, the Xero OAuth1 is initialized and the REST object is ready to make REST API calls..'

    --  -----------------------------------------------------------------
    --  Make a call to verify that OAuth1 through an HTTP proxy works..

    --  Get the full list of contacts.
    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'GET', '/api.xro/2.0/Contacts', @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    --  A 200 response is expected for actual success.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    --  Iterate over the contacts..
    DECLARE @bAutoTrim int
    SELECT @bAutoTrim = 0
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadSb', @success OUT, @sbXml, @bAutoTrim
    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_cache/xero_contacts.xml'

    --  How many records exist?
    DECLARE @recordCount int
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @recordCount OUT, 'Contacts'

    PRINT 'numRecords = ' + @recordCount

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @recordCount
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i

        EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Contacts|Contact[i]|ContactID'
        PRINT 'ContactID: ' + @sTmp0
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @tunnel
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @privKeyFromPem
    EXEC @hr = sp_OADestroy @oauth1
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @xml


END
GO

 

© 2000-2016 Chilkat Software, Inc. All Rights Reserved.