Dimensional Data

A customer recently asked me if FireDAC can be used to connect to a database through an SSH tunnel. The short answer is yes, of course. But let’s understand better what is an SSH tunnel, and how this goal can be easily achieved when using Delphi and FireDAC. SSH Tunnels Secure Shell (ssh) is a standard tool included on most “network” operating systems i.e. Linux, UNIX, MacOS, and now also on Windows (natively speaking). If you had used PuTTY sometime in your life to connect to a remote command line interface, in general a Linux system, SSH was the protocol used for that. So the key thing here to use FireDAC via an SSH secure interface is to understand that FireDAC (or any other database access framework) has nothing to do with it. What you need is to establish a permanent SSH tunnel and then setup your database connection properly. We’ll see two ways to establish an SSH tunnel: one using an external tool (PuTTY) and the second one directly coded in your application. SSH Servers The aim of this article isn’t to teach how to create an SSH Server, but I had to create one to test my solution, so here is what I’m using. Basically I have a Linux VM running a PostgreSQL database (this is the database the customer would like to connect, but the solution is database agnostic), and over it I’ve installed and started an SSH Server following this tutorial: https://docs.oracle.com/cd/E37670_01/E41138/html/ch25s03.html This is for Oracle Linux (this is what I’m running due another proof of concept I had to do past week) but you’ll find similar tutorials for your preferred Linux flavor. Testing the Connection Before trying the connection from FireDAC, it’s a good idea to prove the connection using just command line tools that are integral part of the operation systems, so you have an idea if things are working or not. So, having both the SSH Server and the database instance (PostgreSQL in my case) open and running on Linux, we can try a first connection from Windows using any SSH client terminal. Windows 10 has a native client since the 1809 “October Update”, but I’ll stick with PuTTY as this is a dominant utility for system admins. These are my Putty configurations. In the Session page you need to specify the server IP and the SSH port (22 is the default): And in the SSH/Tunnels configuration page you specify how the tunnel will be created: What this configuration is saying? Basically to create an SSH tunnel you have to specify the Source port (any available/not used port number you have in your system) and the Destination IP/Port (in this case the Linux server IP address and the Port where the PostgreSQL instance is listening – the 5432 is the default PostgreSQL port). Of course, the Source IP address will be always “localhost”, meaning your database connection will point to localhost/63333 and the tunnel will do the magic, sending the commands to the server and receiving back the results. Finally, opening the PuTTY connection and providing a valid Linux user and password (it’s recommended to create an specific user only for the SSH usage), you are going to see something similar to this below. Ugly, I know. You can test some Linux commands […]

Since 1998, Ziad Allaghi has been developing programs with Delphi. Ziad’s (System of Student Registration) application is a showcase entry for the Delphi 26th Showcase Challenge and we talked to him about his knowledge creating programs with Delphi. You can find more information about the application over at System of Student Registration. When did you start using RAD Studio/Delphi and have long have you been using it? I started programming in the Delphi language in 1998 the third edition and then version 6, and I have become better in my programs and increase my productivity because Delphi provides an environment for generating ideas easily. What was it like building software before you had RAD Studio/Delphi? The Delphi editor provides an excellent programming environment because it depends on the Pascal language, which is a clear language with a clear code that allows you to develop more than one program at the same time with minimal code description instructions, which allows you to easily understand It with less time and effort … The latest version is very amazing at all levels Delphi 10 4. 2 How did RAD Studio/Delphi help you create your showcase application? Rapid development in this language with the availability of many resources that help in the implementation of applications on adequate operating systems and increases the demand of users and your customers for your programs Thank you Embarcadero What made RAD Studio/Delphi stand out from other options? It provides easy-to-use and effective components such as dealing with various databases and communications .. Pascal language with the Delphi editor 10.4.2 gives the programmer the complete departure in writing his programs with creativity What made you happiest about working with RAD Studio/Delphi? It gives you the possibility that you can produce a program entirely without resorting to assistant programs, such as designing branding screens, databases, and visual components What have you been able to achieve through using RAD Studio/Delphi to create your showcase application? Sharing experiences is very important and taking notes in developing a program while developing programming style and ease of implementation with the new version of Delphi 10.4.2. What are some future plans for your showcase application? Program development, especially with the new components only available in Delphi version 10.4 ControlList and compiler speed Thank you, Ziad! Click through the link below for the showcase entry of System of Student Registration. Showcase Thanks for the interview Ziad!

What can the Google Audio Transcription API do for our applications? Google’s Audio Transcription API allows your program to use all the power of Google’s huge computing resources to accurately transcribe speech found in audio files into text. As Google’s documentation says: Transcribe your content in real time or from stored files Deliver a better user experience in products through voice commands Gain insights from customer interactions to improve your service How can we use the Audio Transcription API with Delphi? Google cloud services have been positioned as a must have computing service solution today. They allow us to easily use their perfectly-designed AI solutions to use in our applications. Not only that, prices are also reasonable and you can start with zero payment by just adding your credit card. But how about getting those amazing functions to our Delphi application? Some people would think that Delphi is not the ideal language to work with those popular cloud computing APIs. It’s not true. With the help of the huge community for Delphi, it’s simpler than you think. Google Speech to Text and Text to speech are two of those cloud computing functions that could vital for some business applications. With the help of few repositories by grijjy in GitHub, we can easily get those functionalities to our Delphi application. Actually the repository is dated from 2017 but surprisingly it works perfectly with the newer versions of Delphi without many changes to the code. How to setup Google cloud services to work with Delphi? To use Google cloud services in our Delphi project, we need credentials to allow our project to use our google cloud API account. To do that, please visit this link and create an account. https://cloud.google.com Then create a new project. Then go to API and services dashboard and create new credentials. Make sure it’s service account. Then go to the new service account and create new key in Keys tab. Please make sure the key type is P12. Now you need to convert this P12 key to PEM to use with our Delphi components. To do that, please run this command in a folder with OpenSSL binaries. Make sure to edit file names: openssl pkcs12 -in path.p12 -out newfile.key.pem -nocerts -nodes em>openssl pkcs12 –in path.p12 –out newfile.key.pem –nocerts –nodes/em> Keep that generated pem file to use with our Speech to Text Delphi project. What are the prerequisites for creating a Delphi Audio Transcription project? Download the GrijjyFoundation repository. https://github.com/grijjy/GrijjyFoundation Download the “Nghttp2.pas” from this link and copy it to the GrijjyFoundation folder. https://github.com/grijjy/DelphiRemotePushSender/blob/master/Nghttp2.pas Download the “Google.API.pas” form this link and include it in your project folder. https://github.com/grijjy/DelphiGoogleAPI/blob/master/Google.API.pas How to setup new Delphi project to demonstrate Audio Transcription? Create a new Delphi project and add components like this. Alternatively, you can download the demo project form this link. Demo Project GrijjyFoundation has many units to support many Google Cloud services. But in this project we only need few of them. Please include only these units to your Delphi project. How to code the Delphi Audio Transcription project? Now let’s move to the coding part. We use an instance of “TgoGoogle” class to post data and get the response form the Speech to Text API. We need to set some parameters of “TgoGoogle”. TgoGoogle.OAuthScope – OAuth Scope of the […]

DigitalOcean offers a set of APIs that we can access to manage their products from our application.  You can create applications that act like DigitalOcean’s Droplets Control Panel. You can access DigitalOcean’s API documentation here https://developers.digitalocean.com/documentation/v2/. One reason to utilize the DigitalOcean API is to automate your cloud server management and deployment process. If you are building out separate machines for customers (using RAD Server for example) this would be a great way to automate that process. Or maybe you have a CI pipeline you’re setting up that involves automated deployment and testing of an installation process this would be a great way to automate the process through Delphi and the DigitalOcean API. How do I set up a Windows app with DigitalOcean’s API? This article is only an example of the initial steps in creating an automation application, so I only show how to set up the DigitalOcean’s Droplet. In order to create a droplet (virtual private server) the /v2/droplets endpoint exists. It can be called to create a new server for you to use. Here are the two request headers needed which include authorization. Content-Type: application/json Authorization: Bearer b7d03a6947b217efb6f3ec3bd3504582 Content–Type: application/json Authorization: Bearer b7d03a6947b217efb6f3ec3bd3504582 Here are the list of request parameters that can be set when creating a droplet. The parameters are sent via JSON in the request body. You will see these parameters used in the Object Pascal code below. Name Type name String region String size String image integer (if using an image ID), or String (if using a public image slug) ssh_keys Array backups Boolean ipv6 Boolean private_networking Boolean vpc_uuid string user_data String monitoring Boolean volumes Array tags Array And here is some example JSON of a request body: { “name”: “example.com”, “region”: “nyc3”, “size”: “s-1vcpu-1gb”, “image”: “ubuntu-16-04-x64”, “ssh_keys”: [ 107149 ], “backups”: false, “ipv6”: true, “user_data”: null, “private_networking”: null, “volumes”: null, “tags”: [ “web” ] } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 {   “name”: “example.com”,   “region”: “nyc3”,   “size”: “s-1vcpu-1gb”,   “image”: “ubuntu-16-04-x64”,   “ssh_keys”: [     107149   ],   “backups”: false,   “ipv6”: true,   “user_data”: null,   “private_networking”: null,   “volumes”: null,   “tags”: [     “web”   ] } What Does the Delphi API code look like to access DigitalOcean? We are going to use the TRESTClient TRESTRequest TRESTResponse pipeline that is built into the Delphi RTL to make it easy to handle the REST request. The library is available for all platforms that are supported by Object Pascal. The REST Library framework focuses on JSON as the representation format. REST.Client.TRESTClient is the component that actually executes a request to a service. TRESTClient manages the HTTP connection to the service, deals with HTTP headers and proxy servers, and receives the response data. Authenticators (described later) can be attached to the client for a convenient way of adding authentication to a request. The request (REST.Client.TRESTRequest) holds all parameters and settings that form the actual HTTP request to the service. When connected to a client-component, it can be executed (even at design time). One of the most important properties of the request is the ‘Resource’. This value forms the previously mentioned ‘second half’ of the full request-url and defines the actual action that is executed on the service. The request also defines the HTTP methods (‘get’, ‘post’, ‘put’, ‘delete’) that are used for its execution. The response […]