String Enum Representation in Entity Developer

Posted by on November 17th, 2014

This article explains and demonstrates mapping a string representation of enum to string fields in a database in Entity Developer. The sample, demonstrated in this post, is included into our NHibernate Mapping Samples application, described in the corresponding NHibernate Mapping Samples blog article. It is named ‘Enum Type Property’; however, other samples from this application probably prove to become useful for NHibernate users both beginners and professionals, so we recommend downloading it for everyone who develop NHibernate applications.

The problem with mapping enum types in NHibernate is that NHibernate can map enum type values only to the numeric columns in database without additional manipulations. However, when enum values are stored in database as strings, NHibernate requires the user to perform some additional actions.

Our sample uses the following database table:

CREATE TABLE Employee (
  EmployeeID INTEGER PRIMARY KEY,
  EmployeeType INTEGER NOT NULL,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Sex VARCHAR(20) NOT NULL
);

The EmployeeType and Sex fields of the corresponding Employee class, must be mapped to the corresponding enum types:

    public enum EmployeeType : int
    {
        Manager = 1,    
        Clerk = 2,    
        Courier = 3
    }

    public enum SexType : int
    {
        Male,    
        Female
    }

EmployeeType values are stored in the database in the numeric format, so it’s easy to map this field. It is defined in the XML file as following:

    <property name="EmployeeType" type="EmployeeType">
      <column name="EmployeeType" not-null="true" sql-type="INTEGER" />
    </property>

The Sex field stores its enum values in the database as strings, and standard mapping cannot be used in this case. You need to define an additional class, which inherits from the Nhibernate.Type.EnumStringType class from the NHibernate.dll assembly, for each of such enums:

    public class SexTypeEnumStringType : NHibernate.Type.EnumStringType
    {
        public SexTypeEnumStringType() : base(typeof(SexType))
        {
        }
    }

And then you must use this class for mapping this property in the XML file:

    <property name="Sex" type="SexTypeEnumStringType">
      <column name="Sex" not-null="true" length="20" sql-type="VARCHAR" />
    </property>

Thus, storing enum values as strings in the database requires an additional class for each of such enums, and redefining the type in mapping of each property of such enum type. This means additional time costs and inconveniences.

With Entity Developer for NHibernate, you can easily get rid of all these inconveniences. In our example, it’s enough to select the Sex field of the Employee class and, in the Properties window, switch its Enum Storage Mode property to String. After this Entity Developer will generate all the necessary classes and specify them in the generated mapping automatically.

Default Enum Storage Property

If you always store enum values in the database as strings, you can set Enum Storage Mode for all the entity properties in the model instead of manually setting it for each property manually. For this, open the Model Settings dialog box (you can do it by right-clicking the root model node in Model Explorer or empty space on a diagram and selecting Model Settings in the shortcut menu), and on the General page set the required value for Default Enum Storage. This changes the default value of the Default Enum Storage property for all entity properties, for which the value of this setting was not changed manually.

Default Enum Storage in Model Settings

If you want to use an enum value mapping strategy different from the model default one for some property, you can always specify it by setting Enum Storage Mode for this property to the corresponding value.

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.

Devart Attended the Second Meeting of Lviv SQL Server User Group as a Sponsor

Posted by on October 28th, 2014

Devart gladly participated as a sponsor on the second meeting of the Lviv SQL Server User Group that took place on October 25, 2014.

About Lviv

Lviv is a cultural and economic center of West Ukraine. Rich history and a lot of old buildings make Lviv one of the most interesting and exciting places in Ukraine.

About Lviv SQL Server User Group Meeting

Lviv SQL Server User Group has a status of PASS Local Chapter (Professional Association for SQL Server) and is a part of PASS’s Global Growth for supporting of MS SQL Server professionals and connecting the community around the world. This meeting was devoted to such topics as Database Version Control and Microsoft Business Intelligence.

In the course of the event, Devart presented the product line of dbForge Tools for SQL Server.

Devart Raffle

We also held the raffle for the participants of the meeting. The winners were awarded with the exclusive prizes, including 1 FREE license of dbForge Studio for SQL Server and 1 FREE license of SQL Complete.

We would like to thank the organizers and the speakers of the conference for the highly informative and productive event. We do hope to attend it next time!

DSC06388 DSC06392 DSC06403 DSC06412 DSC06411

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

Devart at SQLSaturday #290 – Kiev 2014

Posted by on September 24th, 2014

Devart sponsored SQLSaturday #290, that took place on September 20, 2014 in Kiev, Ukraine.

About Kiev

The Ukrainian capital is a city that is more than 15 centuries old, but yet stays remarkably young. Being one of the most historically and culturally significant centers of Eastern Europe, Kiev surprises by its ancient beauty and unforgettable atmosphere.

About SQLSaturday #290

With informative reports and attendance of about 200 participants, this edition of SQLSaturday turned into a perfect platform  for getting new knowledge about SQL Server and related technologies, meeting database experts and communicating with top SQL Server specialists from all over the world.

In the course of the event, Devart presented the updated product line of dbForge Tools for SQL Server.

Devart Raffle

We also held the raffle for the participants of SQLSaturday. The winners were awarded with the exclusive prizes, including 1 FREE license of dbForge Studio for SQL Server, 1 FREE license of SQL Complete and 2 T-shirts.

We would like to thank the organizers and the speakers of the conference for the highly informative and productive event. We do hope to attend it next time!

sql-saturday-290-kyiv-1 sql-saturday-290-kyiv-2 sql-saturday-290-kyiv-3 sql-saturday-290-kyiv-4

How To: Automatically Email Comparison Report

Posted by on September 9th, 2014

dbForge Studio for MySQL allows you to automate report creation process by using command line utility. There is a simple way to send comparison reports automatically via email.

To accomplish this task we will use the Collaboration Data Objects (CDO), previously known as OLE Messaging or Active Messaging. CDO is an application programming interface included with Microsoft Windows.

Below is the simple BAT file that automatically saves a comparison report in the HTML format on the “D:\\” drive and runs the send.vbs script file.

call “C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com” /datacompare /compfile:d:\project.dcomp /reportformat:HTML /report:d:\report.html
start send.vbs

Read the rest of this entry »

Special Aspects of Sphinx SE Support

Posted by on August 28th, 2014

At the moment, dbForge Studio for MySQL allows you to create a connection to Sphinx Search Engine. Due to technical restrictions, a warning message appears in some cases. The reason the message appears is because, there is no possibility to define that the connection happens to  Sphinx SE. We requested the Sphinx SE developers team to fix this problem.

 

When the warning message appears?

Case 1: When you create a connection to Sphinx Search Engine, the message appears for the first time.

Case 2: The same message appears when you create a new SQL document. It appears only once and doesn’t repeat for other new SQL documents.

Case 3: The message appears when you click a Sphinx SE connection in Database Explorer.

Warning message

Warning message

In any of these cases, just click OK, and continue your work.

Read the rest of this entry »

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.

WPF: Smooth Scrolling With UI Virtualization

Posted by on May 27th, 2014

During the development of Review Assistant, we encountered a significant performance problem with displaying 100+ comments in one list, as creation of each comment takes a considerable amount of time. At first sight, it seemed that enabling virtualization would fix the problem, but we faced the following obstacles:

  1. When scrolling, the elements jump form one to another without any smoothness. In addition, the thumb height begins to change in size, what looks just weird.
  2. When selecting IsPixelBased in true, the elements are scrolled smoothly, but a lot of them begin to lag. And the worst thing is that sometimes the application crashes with StackOverflowException. The crash is caused by the code in the VirtualizingStackPanel.MeasureOverrideImpl method, where the tail call is used, and the call depth is not limited by any means.

In addition, we wanted to display elements of different types (reviews and comments) in the list. It can be solved by the usage of TreeView instead of ItemsControl and by specifying several HierarchicalDataTemplates. But the above-mentioned problems still persist.

Read the rest of this entry »


Devart Blog