Connect to SFTP Data in JRuby

JRuby is a high-performance, stable, fully threaded Java implementation of the Ruby programming language. The CData JDBC Driver for SFTP makes it easy to integrate connectivity to live SFTP data in JRuby. This article shows how to create a simple JRuby app that connects to SFTP data, executes a query, and displays the results.

Configure a JDBC Connection to SFTP Data

Before creating the app, note the installation location for the JAR file for the JDBC Driver (typically C:\Program Files\CData\CData JDBC Driver for SFTP\lib).

JRuby natively supports JDBC, so you can easily connect to SFTP and execute SQL queries. Initialize the JDBC connection with the getConnection function of the java.sql.DriverManager class.

SFTP can be used to transfer files to and from SFTP servers using the SFTP Protocol. To connect, specify the RemoteHost;. service uses the User and Password and public key authentication (SSHClientCert). Choose an SSHAuthMode and specify connection values based on your selection.

Set the following connection properties to control the relational view of the file system:

• RemotePath: Set this to the current working directory.
• TableDepth: Set this to control the depth of subfolders to report as views.
• FileRetrievalDepth: Set this to retrieve files recursively and list them in the Root table.

Stored Procedures are available to download files, upload files, and send protocol commands. See gdatamodel for more on using SQL to interact with the server.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the SFTP JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.sftp.jar

Fill in the connection properties and copy the connection string to the clipboard.

👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)

Below is a typical JDBC connection string for SFTP:

jdbc:sftp:RemoteHost=MyFTPServer;

Create a JRuby App with Connectivity to SFTP Data

Create a new Ruby file (for example: SFTPSelect.rb) and open it in a text editor. Copy the following code into your file:

require 'java'
require 'rubygems'
require 'C:/Program Files/CData/CData JDBC Driver for SFTP 2018/lib/cdata.jdbc.sftp.jar'

url = "jdbc:sftp:RemoteHost=MyFTPServer;"
conn = java.sql.DriverManager.getConnection(url)
stmt = conn.createStatement
rs = stmt.executeQuery("SELECT Filesize, Filename FROM MyDirectory")
while (rs.next) do
 puts rs.getString(1) + ' ' + rs.getString(2)
end

With the file completed, you are ready to display your SFTP data with JRuby. To do so, simply run your file from the command line:

jruby -S SFTPSelect.rb

Writing SQL-92 queries to SFTP allows you to quickly and easily incorporate SFTP data into your own JRuby applications. Download a free trial today!