DAC Team

ODAC Integration With dbForge Studio for Oracle

Posted by on November 6th, 2014

Database application development is usually divided into two stages: database design and application writing. Oracle Data Access Components for Delphi (ODAC) allow implementation of work with Oracle server, and dbForge Studio for Oracle – database design and administration. Now, for convenient development, integration with dbForge Studio for Oracle is implemented in ODAC.

To enable integration to dbForge Studio for Oracle, in the Delphi main menu select ODAC → Database Tools → dbForge Studio for Oracle Integration.

*In order for integration to be available, dbForge Studio for Oracle is required to be installed on the PC.

Due to integration, all the configured connections saved in dbForge Studio for Oracle are now available in the OraSession editor, and when selecting a needed connection – all the required OraSession parameters are filled in automatically.

/

Formerly, in ODAC, as well as in the other Data Access Components, editing of SQL queries, scripts, etc. was performed in the TMemo standard component. That complicated reading of code with large scripts. Now, ODAC has intellectual syntax highlighting, that makes query writing and analysis more convenient and obvious.

In addition, you can now switch from OraQuery, OraStoredProc, OraSQL, etc. editors directly to writing a query, a table, a procedure in dbForge Studio for Oracle – and get full stack of advantages of using the dbForge Studio editor: autocomplete, alignment, and others).

After SQL text is saved, all the changes will be automatically saved in an ODAC component. Also, in the integration mode, data edits are applied in dbForge Studio. One of the dbForge Studio for Oracle features – the visual query editor — is now available in ODAC: query text generated by the designer is automatically passed to ODAC.

To find an object binded with an ODAC component, it is enough to call the needed component context menu and select «dbForge Studio For Oracle → Find in Database Explorer».

Current table editing is available from the TOraTable component – the menu item «dbForge Studio For Oracle → Edit Object».

For convenient work with stored procedures, there are procedure compilation and editing capabilities in ODAC, including Debug info, and step-by-step procedure execution in dbForge Studio.

What is LINQ for Delphi in EntityDAC?

Posted by on October 15th, 2014

The main idea of EntityDAC, as well as any ORM for RAD Studio, is to free the programmer from having to implement a business-logic, and the logic of database interaction separately in the application source code. When writing code, programmer abstracts himself from specifics of a DBMS and operates the database entities using structures of his “native” programming language.

I.e., if in the classic approach a similar code is used for updating a database record:

Query1.SQL.Text := 'UPDATE Master ' +
                   'SET Name = ''New Name'' ' +
                   'WHERE MasterId = 10';
Query1.Execute;

then when using EntityDAC, a similar operation is performed in a more “natural” way:

var
  Master: TMaster;
begin
  // retrieve a TMaster instance
  
  Master.Name := 'New Name';
  Master.Save;
end;

In the above sample, a code fragment is commented deliberately, that logically leads us to the following task: retrieving an entity (or entity collection) by required criteria in the context of ORM ideology.

In the classical approach, SQL is used:

Query1.SQL.Text := 'SELECT * FROM Master WHERE MasterId = 10';
Query1.Open;

And in EntityDAC, to retrieve entities or their collections, a special language is used— LINQ.

What is LINQ?

LINQ – is a language specialized on writing queries similar to SQL-queries serving for retrieving data from the database and mapping them to entities or entity collections.

What are the advantages of using LINQ?

Firstly: a programmer gets a universal syntax for writing queries, that doesn’t depend on the specifics of the used DBMS.

For instance: the task is to retrieve first 5 records from a table. In MySQL and InterBase the corresponding queries will look differently, that must be taken into account when writing an application:

MySQL

SELECT Name
FROM Master
LIMIT 5; 

InterBase

SELECT FIRST 5 Name
FROM Master; 

When using LINQ, it will be enough to write one query in the application source code, that will be executed correctly not depending on the used DBMS:

Query := From(Master).Select.Take(5);

Secondly: using the “native” Delphi syntax when writing queries gives the programmer all the advantages of the built-in code editor:

Code Editor

And finally, syntax mistakes and typos are fully avoided when writing queries.

Sample:

Query1.SQL.Text := 'SELECT Name FROM Master WHER MasterId = 10'; 

or

Query1.SQL.Text := 'SELECT Nam FROM Master WHERE MasterId = 10';

In the classical use of SQL such errors would appear only in application run time. Using LINQ minimizes probability of such situations, since LINQ is validated at the stage of application compilation.

LINQ architecture in EntityDAC

The LINQ syntax is built on the principle of “method chaining”, i.e. each subsequent query operator is a method of execution result of the previous operator. This approach eliminates the use of intermediate variables and makes the query code more readable. For example, compare the code:

Query := From(Master).Where(Master.Id = 10).Select;

with such a structure:

a := From(Master);
b := a.Where(Master.Id = 10)
c := b.Select;

Moreover, the principle of «method chaining» defines the overall structure of a LINQ query, that differs from the usual query structure in SQL. Building a LINQ query is based on the following two provisions:

  • each query operator returns a set of entities;
  • each subsequent operator applies a specific action to this set and returns the resulting subset.

Let’s consider an example query:

Query := From(Master).
         Where(Master.Id > 10).
         OrderBy(Master.Name).
         Select(Master.Name);

The From(Master) operator returns the whole instance set of the Master entity. The following operator Where(Master.Id > 10) applies a filter by the Id field to this set and «passes» the resulting subset to the following operator. The OrderBy(Master.Name) operator sorts the resulting subset by the Master.Name field and passes it further. Finally, the Select(Master.Name) operator generates a new subset of entities, that contain the only field Master.Name. The generated query is saved to the Query variable for further execution and result materializing.

The detailed description of the LINQ syntax, implemented in EntityDAC, is available in the corresponding section of the EntityDAC documentation.

Query result materialization means retrieving query results as entities or entity collections using the GetEntity and GetEntities methods.

Query := From(Emp).
         Where(Emp.DeptNo = 10).
         Select;

Emps := Context.GetEntities<TEmp>(Query);

Some peculiarities LINQ implementation in EntityDAC

In all the code samples in this article the generated LINQ query is assigned to the Query variable. A variable for storing the generated LINQ query must be declared in the following way:

var
  Query: ILinqQueryable;

This approach frees the programmer from having to monitor the destruction of the variable.

Any LINQ query begins from specifying the source of data, i.e. the “From” operator. There is a global function defined in the EntityDAC.Linq module of EntityDAC:

function From(const Source: TExpression): ILinqFrom;

that is the «starting point» for building a LINQ query.

Support for RAD Studio XE7 is added to all the Devart components for Delphi!

Posted by on October 2nd, 2014

Dear users,
DAC Team claims its products to be up-to-date and feature-rich solutions for Delphi developers. We are always trying our best to remain at the edge of programming technologies and cross-platform application development. Support for RAD Studiio XE7 was one of those high-priority tasks… And now we are happy to announce the new versions of already beloved products for Delphi programmers with support for the cutting-edge IDE:

Devart is grateful for your choice and won’t short-sell you.

Thank you for staying with us!

Best wishes!

DAC Team

How to develop Database Applications with UniDAC for iOS and Android in C++Builder XE6

Posted by on July 3rd, 2014

In RAD Studio XE6 there appeared an opportunity to develop applications for iOS and Android on C++. Let’s consider the peculiarities of database application development on C++ using UniDAC.

UniDAC is Universal Data Access Components that allow to connect to SQLite, Oracle, MySQL, PostgreSQL, and InterBase (since version XE3) from iOS and Android (you can use UniDAC for connection from Windows to almost all the known databases, but this is beyond the scope of this blog).

Connection to database

Work with databases on iOS and Android in general is no different from the one on Windows, but there are some nuances when setting connection and deploying files to a mobile device if you work with a local DB. We will consider here how to establish connection to each supported database.

Design-time

Let’s create a new Android application on C++ working with a MySQL server. For this, in the File|New menu select FireMonkey Mobile Application – C++ Builder.

In the appeared dialog select Blank Application.

Then place the TUniConnection and TMySQLUniProvider components onto the form, which will be named as UniConnection1 and MySQLUniProvider1 respectively. Set up the UniConnection1 component by setting the ProviderName property to MySQL and filling in the required properties: UserName, Password, Server, Port.

If the listed properties are set correctly, then you can select a value for the Database property from the combobox. Now you can test connection by setting the Connected property to True. Take into account, that this test connection is performed on Win32 for XE6, not for the mobile application you are developing.

Compilation

Add the libmyproveder200.a file to the project, as it is shown below:

See the correspondence of object module names to database servers in the table below:

Database server Object module name
SQLite libliteproveder200.a
MySQL libmyproveder200.a
Oracle liboraproveder200.a
PostgreSQL libpgproveder200.a
Interbase libibproveder200.a

If you don’t add this file, you will get an error message during compilation:

“[ldandroid Error] C:\Users\Public\Documents\Embarcadero\Studio\14.0\PlatformSDKs\android-ndk-r9c\toolchains\arm-linux-androideabi-4.6\prebuilt\windows\bin\arm-linux-androideabi-ld.exe: .\Android\Debug\Unit1.o: in function _ZTX6TForm1:.\Android\Debug\Unit1.o.ll(.data.rel.ro._ZTX6TForm1+0×6): error: undefined reference to ‘vtable for Mysqluniprovider::TMySQLUniProvider’”

Compile the project.

Run-Time

You should place those providers onto the form, which you will use, and add object modules of these providers (as it was shown in the design-time sample). But take into account that for Android and iOS these modules have the same names, but are located in different folders:

“C:\Program Files\Devart\UniDAC for RAD Studio XE6\Lib\Android\”
“C:\Program Files\Devart\UniDAC for RAD Studio XE6\Lib\iOSDevice\”

You should either place the TUniConnection component onto the form or add the following lines to the header file:

#include "DBAccess.hpp"
#include "Uni.hpp"

and the following – to the cpp file:

#pragma link "DBAccess"
#pragma link "Uni"

If you are going to open access to a local database on a mobile device, then add the line to the header file:

#include <System.IOUtils.hpp>

in order to get access to IOUtils namespace.

SQLite

If you don’t deploy the database file to a mobile device, you should set:

SpecificOptions->Values["ForceCreateDatabase"] = "True"

In this case, on the first application launch, a database file will be created automatically.

Sample

  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "SQLite";
	Connection->SpecificOptions->Values["ForceCreateDatabase"] = "True";
	Connection->Database = System::Sysutils::IncludeTrailingPathDelimiter(
	  System::Ioutils::TPath::GetDocumentsPath()) + "db.sqlite3";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }

Oracle

It is impossible to install Oracle client to a mobile device, because Oracle client for Android and iOS simply doesn’t exist. Therefore the only way to establish connection to Oracle from a mobile device is to connect directly via TCP/IP. For this, the Direct option should be set to True:

SpecificOptions->Values["Direct"] = "True";

In addition, the server name must be set correctly, since, if we have no client – we have no tnsnames.ora file with the server list as well. Therefore, to establish connection from Android and iOS, we need to know the server Host and Port, as well as its SID or Service Name.

To connect via the SID, the server should be set in the following way:

Server = "Host:Port:sid=SID";

or a simplified way:

Server = "Host:Port:SID";

To connect via the Service Name – as follows:

Server = "Host:Port:sn=ServiceName";

In other words, the ‘sid=’ prefix of the third parameter indicates that connection is established via the SID, and the ‘sn=’ prefix indicates that connection is established via the Service Name. If no prefix is specified, then, by default, it is considered, that we want to establish connection via the SID.

The majority of Oracle servers have the same SID and Service Name, so you, most likely, won’t have to go into such nuances, since you can learn more about this in the Oracle documentation.

Sample

  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "Oracle";
	Connection->SpecificOptions->Values["Direct"] = "True";
	Connection->Server = "server:1521:orcl";
	Connection->Username = "user_name";
	Connection->Password = "password";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }

MySQL

MySQL client software for Android and iOS also doesn’t exist, therefore connection to MySQL server will also be established directly via TCP/IP. For this, let’s set the corresponding option:

SpecificOptions->Values["Direct"] = "True";

Sample

  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "MySQL";
	Connection->SpecificOptions->Values["Direct"] = "True";
	Connection->Server = "server";
	Connection->Port = 3306;
	Connection->Username = "user_name";
	Connection->Password = "password";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }

PostgreSQL

With PostgreSQL, everything is more simple. Since UniDAC only allow establish direct connection via TCP/IP, it is enough for us to specify the Server and Port and perform Connect.

Sample

  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "PostgreSQL";
	Connection->Server = "server";
	Connection->Port = 5432;
	Connection->Database = "database_name";
	Connection->SpecificOptions->Values["Schema"] = "schema_name";
	Connection->Username = "user_name";
	Connection->Password = "password";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }

InterBase

Using InterBase ToGo, you can connect to both local or remote DB.

To connect to a local db, just the path to the local DB on the device should be set:

Database = System::Sysutils::IncludeTrailingPathDelimiter(
	     System::Ioutils::TPath::GetDocumentsPath()) + "db.gdb";

If you need to establish connection to a remote server, you should specify not only the database, but the server as well:

UniConnection.Server = "server";
UniConnection.Database = "C:\db.gdb";

Please note that the System::Sysutils::IncludeTrailingPathDelimiter (System::Ioutils::TPath:: GetDocumentsPath) prefix should be specified when connecting to a local DB, and it is not needed for connection to a remote database.

Local database sample
  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "InterBase";
	Connection->Database = System::Sysutils::IncludeTrailingPathDelimiter(
	  System::Ioutils::TPath::GetDocumentsPath()) + "db.gdb";
	Connection->Username = "user_name";
	Connection->Password = "password";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }
Remote database sample
  TUniConnection * Connection;

  Connection = new TUniConnection(Form1);

  try {
	Connection->ProviderName = "InterBase";
	Connection->Server = "server";
	Connection->Database = "C:\db.gdb";
	Connection->Username = "user_name";
	Connection->Password = "password";
	Connection->Connect();
	ShowMessage("Connected successfully");
  }
  __finally {
	Connection->Free;
  }

Deployment to mobile device

The deployment to mobile device process is described in the How to Develop Android Database Applications in RAD Studio XE5″ article.

Difference between Android and iOS application deployment

Please note that the deployment path is different for Android and iOS. If you want to deploy your application to both platforms, then make sure the deployment paths are specified correctly for both of them.

NOTE: Don’t forget to change the Remote Path default value “.” with one of the described above.

  Deployment Path Destination on Device
TPath::GetDocumentsPath .\assets\internal /data/data/com.embarcadero.MyProjects/files
TPath::GetSharedDocumentsPath .\assets /mnt/sdcard/Android/data/com.embarcadero.MyProjects/files

In addition, thus the providers for Android and iOS have similar names, they differ from each other. The providers are supplied with UniDAC, but each of them is located in a folder corresponding to a platform.

Debug

We won’t focus on the debug process in this article, since it was described in details in the previous article: Remote Debug of Android Applications in RAD Studio XE5 via Wi-Fi

Conclusion

RAD Studio XE5 release allowed creating applications with Devart Data Access Components in Delphi for work with SQLite, Oracle, MySQL, PostgreSQL and InterBase from both mobile platforms: Android and iOS. RAD Studio XE6 release made it possible not for Delphi only, but for C++Builder as well. But we are not going to stop. Many users ask us to support connection to SQL Server from Android and iOS – and we will make every effort to give them such a capability.

How to Develop Android Database Applications in RAD Studio XE5

Posted by on October 8th, 2013

Not so long ago, in RAD Studio XE5 there was introduced support for Android application development. On the Internet there can be found a lot of information about Android application development in Delphi XE5, but when it comes to development of database applications, it becomes much more difficult. The point is that the components and drivers included in the RAD Studio XE5 distribution (for example, FireDAC and dbExpress drivers) have severe limitations: on Android and iOS platforms, they support just 2 databases — SQLite and InterBase. And what to do when you develop a serious business-application, that requires remote connection to Oracle, MySQL or PostgreSQL from Android? We have come across such questions on various forums dedicated to database application development for Android and decided to reveal the topic as detailed as possible. (more…)

How to Debug Android Apps via Wi-Fi in RAD Studio XE5

Posted by on October 2nd, 2013

The top news of the Embarcadero company in 2013, as well as the direct reason for RAD Studio XE5 release, was certainly support for Android. Android is now of rather great interest for programmers, therefore its support in the Embarcadero’s product in a certain way increased activity in the Delphi community.

With the release of ХЕ5, in a variety of specialized blogs, there appeared publications concerning development of “my first application” for the new platform, in which it is described in details how to create, run, and debug a project. Due to the specifics of the products produced by our company, while preparing for the recent release, we were most interested in the debug process, some peculiarities of which we want to discuss here. (more…)

How to Develop iOS Applications in Delphi XE4 Using Devart Data Access Components

Posted by on May 30th, 2013

Half a year ago we published an article describing the process of iOS application development in Delphi XE2. We received quite a lot of positive feedback on that article, but the main thing is that the article helped many our users create their first applications for iPhone, iPad and iPod. Since then, been a long time – and the new RAD Studio XE4 first saw the light of day, in which the process of iOS application development greatly changed. But, fortunately, all the changes were oriented to make the development simple and easier to understand.

iOS development limitations are not for us!

The matter is that iOS has a quite serious limitation on application deployment to iOS devices: you cannot deploy any libraries (*.dylib) along with your application. If you have developed applications for work with databases earlier, you should know that to work with any databases, you need either a client to be installed or a library allowing connection to a certain database. This can be a very serious brick wall when attempting to create a business-application that must work with databases on iOS. (more…)

Using DAC products in multi-tier DB application development

Posted by on April 30th, 2013

DataSnap technology overview

The DataSnap technology (previously known as MIDAS) allows development of client–server applications, specifically applications using databases, that work via Internet, local network, or on a local PC. For connection, DataSnap allows using the TCP/IP protocol, as well as HTTP and HTTPS. For backward compatibility, COM/DCOM support also remains.

Besides, DataSnap allows development of applications for both PC and mobile devices based on iOS, Android and Windows Phone.

In addition, DataSnap supports secure connection capabilities using JSON, as well as use of filters for data encryption and compression for enhanced security. (more…)

How to Develop iOS Applications in Delphi XE2 Using Devart Data Access Components

Posted by on September 14th, 2012

Delphi XE2 allows to develop iOS applications as well as applications for Win 32-bit, 64-bit, and Mac OS. In this article, we will try to explain how to develop iPhone apps in Delphi XE2. However, everything below can be used in application development for iPad and iPod, since they support iOS mobile platform as well.

iOS application development is a little bit different from development of common desktop applications, and it consists of two main stages:

  1. Application development in Delphi XE2
  2. Application compilation and building in Xcode on Mac OS (more…)

Data Type Mapping in Data Access Components for Delphi

Posted by on July 5th, 2012

Data Type Mapping is a flexible and easily customizable gear, which allows mapping between DB types and Delphi field types.

In this article there are several examples, which can be used when working with all supported DBs. In order to clearly display the universality of the Data Type Mapping gear, a separate DB will be used for each example.

Data Type Mapping Rules

In versions where Data Type Mapping was not supported, the DAC products automatically set correspondence between the DB data types and Delphi field types. In versions with Data Type Mapping support the correspondence between the DB data types and Delphi field types can be set manually. (more…)


Devart Blog