SFTP for EDI

The SFTP service is intended for lightweight electronic data interchange (EDI) with trading partners. You can upload any generated text (e.g. an X12 file) or any document, image or signature from your database to a trading partner. You can also download files.

You access the SFTP service by making an API call as shown below.

Example - SFTP Upload

Simply call parasql_http() as shown below. Replace YOUR_API_KEY with a key you create via Tools > API Keys.

CALL parasql_http(
	'POST', 
	'https://www.appsynergy.com/api?action=SFTP_UPLOAD&apiKey=YOUR_API_KEY',
	JSON_OBJECT(
		'host', 'sftp.yourhost.com', 
		'port', 22, 
		'user', 'MyUserName', 
		'password', 'MyPass', 
		'uploadAsFilename', '/IN/test1.txt', 
		'contentType', 'UTF8',  -- enum: UTF8, BASE64, BLOB_FIELD
		'content', 'Hello World!'
	),
	NULL,
	NULL,
	NULL
);

If contentType is BLOB_FIELD then content can be the value of any document, image or signature field in your database.

Example - SFTP Download

Simply call parasql_http() as shown below.

CALL parasql_http(
	'POST', 
	'https://www.appsynergy.com/api?action=SFTP_DOWNLOAD&apiKey=YOUR_API_KEY',
	JSON_OBJECT(
		'host', 'sftp.yourhost.com', 
		'port', 22, 
		'user', 'MyUserName', 
		'password', 'MyPass', 
		'path', '/IN', 
		'filename', 'test1.txt', -- file.ext or * or *.ext or abc* or abc*.ext
		'command', 'GET', -- enum: LIST, GET, DELETE, GET_AND_DELETE
		'downloadType', 'UTF8' -- enum: UTF8, BASE64, DOCUMENT_FIELD
	),
	MyDownload_CALLBACK,  -- specify a callback procedure to process the response
	NULL,
	NULL
);

If downloadType is DOCUMENT_FIELD then the value returned as the file contents will be a document field; this value can be saved to any document field in your database.

The Response

Your callback procedure must take the following parameters:

respCode INTEGER, respBody JSON, respHeaders JSON, optMetadata JSON

The respBody will look like this:

{
  "status": "OK",
  "errorMessage": "",
  "errorCode": "",
  "data": {
    "files": [
      {
        "filename": "test1.txt",
        "folder": false,
        "size": 12,
        "timestamp": "2024-04-15 00:13:20",
        "content": "Hello World!"
      }
    ]
  }
}

You can get the file contents of the first file like this:

JSON_VALUE(respBody, '$.data.files[0].content')

You can also transform the list of files into a SQL table so it's easier to work with:

SELECT * FROM JSON_TABLE(respBody, '$.data.files[*]' COLUMNS(
   filename VARCHAR(255) path '$.filename', 
   folder BOOLEAN path '$.folder',
   size INTEGER path '$.size',
   timestamp DATETIME path '$.timestamp', 
   content MEDIUMTEXT path '$.content'
  )
) AS files 

Which gives you this as a query result:

filename	folder	size	timestamp	content
test1.txt	0	12	2024-04-15 00:13:20	Hello World!