Connecting to Magento via SQL

You can use SQL to connect to Magento in the Data Virtuality Studio or the Code Editor in the Web UI.

Pre-requisites

You need the endpoint and the Consumer Key, the Consumer Secret, the Access Token and the Access Token Secret for gaining access to the API.

How to Connect

Before you start retrieving data, you need to run the connector script for Magento in the Data Virtuality Studio or the Web UI and input your credentials in the following line:

SQL

CALL SYSADMIN.createConnection (
    name => 'magento_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'EndPoint=https://{domain}/rest/V1,SecurityType=None', encryptedProperties => 'AccessToken=<AccessToken>' );;
 
CALL SYSADMIN.createDataSource (
    name => 'magento_src'
    ,translator => 'magento'
    ,modelProperties => 'ConsumerKey=<ConsumerKey>,ConsumerSecret=<ConsumerSecret>,AccessTokenSecret=<AccessTokenSecret>'
);;
 
CREATE VIEW magento.Credentials
AS
SELECT
        '<label_1>' AS label,
        '<EndPoint_1>' AS EndPoint,
        '<ConsumerKey_1>' AS ConsumerKey,
        '<ConsumerSecret_1>' AS ConsumerSecret,
        '<AccessToken_1>' AS AccessToken,
        '<AccessTokenSecret_1>' AS AccessTokenSecret
UNION ALL
SELECT
        '<label_2>' AS label,
        '<EndPoint_2>' AS EndPoint,
        '<ConsumerKey_2>' AS ConsumerKey,
        '<ConsumerSecret_2>' AS ConsumerSecret,
        '<AccessToken_2>' AS AccessToken,
        '<AccessTokenSecret_2>' AS AccessTokenSecret
-- You may add additional sets using 'UNION ALL', making sure labels are unique
;;

You may optionally add a multi-tenancy table if you wish to access data from multiple accounts in the API.

Keep in mind that you have to mnually pass a label parameter to the API procedures which will identify which credentials should be used in the request. If you are not using multi-tenancy, remove the multitenancy_tablename parameter from the model properties, as the default value is not intended to be present in production environments.