I've started to get requests to use FireDAC with Cloud Databases, like SQL Azure. The question whether or not to put your business data in the cloud is getting easier and easier these days and a lot of that is thanks to Microsoft's investment in Microsoft Azure. Microsoft's strategy to create an infrastructure, like Microsoft Azure SQL Database, that can manage big data (as big as petabyte-scale) and everything on down from that is working and it's working well.
In this post, we’ll see how FireDAC can connect and use a Microsoft SQL Azure database.
For this post, I'll be using this Microsoft Azure SQL database called "mySampleDatabase" following the steps described here:
For the FireDAC connection we also need to know the Microsoft Azure Server name for the database, as we see here is: firedac.database.windows.net
And lastly, we also need the Login and Password to connect to the SQL Azure database:
From the FireDAC docwiki on Connect to Microsoft SQL Server (FireDAC) it says:
The FireDAC native driver supports Microsoft SQL Azure.
And for Windows Client Software:
FireDAC requires one of the Microsoft SQL Server x86 or x64 ODBC drivers to be installed on the workstation:
- SQL Server ODBC driver as the connectivity for SQL Server 2000. Most likely, the ODBC driver is already installed on your workstation. If not, see details.
- SQL Native Client as the connectivity for SQL Server 2000 and 2005. We strongly recommend that you have SQL Native Client installed, if your application has to work with SQL Server 2005. See Microsoft SQL Server Native Client.
- SQL Server Native Client NN.N as the connectivity for SQL Server 2000, 2005, 2008, 2012, and SQL Azure. We strongly recommend that you have SQL Server Native Client NN.N installed, if your application has to work with SQL Server 2008, 2012, or SQL Azure. See Microsoft SQL Server 2008 Native Client.
- SQL Server Native Client 11.0 as the connectivity for LocalDB.
For my example, I'm using the SQL Server Native Client 11.0 for SQL Azure.
The docwiki also tells us for the Driver Linkage:
To link the driver:
- drop a TFDPhysMSSQLDriverLink component from the "FireDAC Links" palette page.
- or include the FireDAC.Phys.MSSQL unit in the uses clause.
And lastly, the docwiki also includes this sample SQL Azure FireDAC connection string:
- Connect to SQL Azure. Note the "@<server>" suffix in User_Name and the "tcp:" prefix in the Server parameters:
DriverID=MSSQL Server=tcp:nasdfert6.database.windows.net Database=Northwind User_Name=addemo@nasdfert6 Password=asd123zxc Encrypt=Yes MetaDefSchema=dbo MetaDefCatalog=Northwind
So now we have all the information we need to create a FireDAC connection to our mySampleDatabase SQL Azure database.
Using 10.2 Tokyo:
1. Create a new Delphi or C++ Builder VCL or Multi-Device Application (Blank Application).
2. Drop a FDConnection and a FDPhysMSSQLDriverLink component onto the form:
3. Double-click the FDConnection component to display the FireDAC Connection Editor:
In my case, the minimum needed parameters for FireDAC to connect to my SQL Azure database are:
DriverID=MSSQL Server=tcp:firedac.database.windows.net Database=mySampleDatabase User_Name=EMBT@firedac Password=FireDAC!
After entering these values in the FireDAC Connection Editor, you can click the TEST button to verify the Connection established successfully:
You can also select the Info tab on the FireDC Connection Editor, and verify the Connection definition parameters, FireDAC Info, Client Info (including showing the ODBC or Native Client driver being used), and Session Information:
4. Next, we can drop a FDQuery component onto the form:
5. Double-click the FDQuery component to display the FireDAC Query Editor and enter this SQL Command tab:
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;
6. Click the Execute button on the FireDAC Query Editor and verify that your data results get returned from the SQL Azure database:
7. To display the data results on our RAD Studio Client application, let's drop a TStringGrid component onto the Form:
8. Using Visual Live Bindings, let's bind the results from the FDQuery to the TStringGrid. Right-click on the Form and select Bind Visually… or on the Form Designer | right-click the TString component | Bind Visually:
9. On the LiveBindings Designer, select the * member of the FDQuery component, and connect it to the * member of the StringGrid component:
10. Using the Object Inspector, if you set the Active property of FDConnection and FDQuery to True, then you should see Design-Time data on the StringGrid, like this:
11. Lastly, let's add a button on the Form, with Text property = "Get data SQL Azure".
12. Double-click the Button, and for the Button1Click event, let's call the Open method of the FDQuery, like this:
procedure TForm2.Button1Click(Sender: TObject);
begin
FDQuery1.Open();
end;
13. Run the application, click on the "Get data SQL Azure" button, and verify FireDAC connects to the SQL Azure database and returns data on the StringGrid, like this:
14. Congratulations! The steps in this post shows how easy it is to use FireDAC with Microsoft’s SQL Azure database! We saw how easy it is to create a FireDAC connection to a SQL Azure database, connect to the SQL Azure database, and return data from the remote SQL Azure database to our RAD Studio multi-device or VCL clients.
[DownloadButton Product=’RAD’ Caption=’To try FireDAC with Cloud Databases or any other supported database, please download RAD Studio and try it yourself’]