MySQL

Some of our users have faced the situation when they need to change the connection details of the existing Database Diagram file.

Let’s assume the situation when you have several databases with identical schemas which are only located on different MySQL servers and you have created a database model that was saved in a Database Diagram file. Now, you want to avoid redesigning the database model from scratch and only reassign the existing database model to another MySQL server.

Here are some steps you can follow to achieve this:

1. Create a database diagram.

2. Arrange objects on the diagram so you can easily read them (create containers, notes, etc).

3. Save the diagram and close dbForge Studio for MySQL.

4. Open the Database Diagram file using a third-party text editor, for instance, notepad.

Open Database Diagram as XML

5. Find the <Connection></Connection> tag that may look as follows:

...
<Connection>
    <Name>MyConnectionToDB</Name>
    <ConnectionString>User Id=root;Host=db;Port=3310;Database=sakila;Ping Interval=120;Character Set=utf8;</ConnectionString>
</Connection>
...

and make the following changes:

...
<Connection>
    <Name>new_connection_name</Name>
    <ConnectionString>User Id=new_user_id;Host=new_host;Port=new_port;Database=new_db_name;Ping Interval=120;Character Set=utf8;</ConnectionString>
</Connection>
...

i.e. modify the <Name></Name> tag (change it to something different) and the <ConnectionString></ConnectionString> tag (connection details for another server).

6. Find all the <DbOid></DbOid> tags that may look as follows:

...
<DbOid>
    <Path>sakila.actor</Path>
    <TypeName>Table</TypeName>
</DbOid>
...

and change the database name to the new one:

...
<DbOid>
    <Path>new_db_name.actor</Path>
    <TypeName>Table</TypeName>
</DbOid>
...

Make sure you specify the new database name in the same letter case as it exists on the server.
7. Save the modified file with the new name.

Now, you have a new Database Diagram file adjusted to another server.

NOTE: The article applies to dbForge Studio for MySQL, dbForge Studio for SQL Server, and dbForge Studio for Oracle.

Some of our users have encountered problems with establishing a connection over the tunnel.php script despite the fact that the tunnel.php script is installed correctly. The following error message occurs:

‘Can’t connect to MySQL server on ‘your.sitename.com’ (10061): Authentication failed.’

The problem appears to be with cached proxy servers and will be fixed in one of the next builds of our product.

Currently it can be fixed by replacing the dbforgemysql.exe.config file, that can be found in dbForge Studio for MySQL installation folder, with the attached one.

Since one of our users has encountered a problem when modifying data via Data Editor, we decided to bring such a situation to our users’ attention.

Let’s assume you have the following table:

CREATE TABLE dept(
  DeptNo INT(10),
  DName VARCHAR(14),
  loc VARCHAR(13),
  Salsum FLOAT
);

Let’s execute the following query in the SQL document:

SELECT DName, loc FROM dept WHERE DeptNo = 20;

Now if you change any of the values of the first record in Data Editor, the changes will be applied to all records of the original table that have ‘DName’ = ‘Research’ and ‘loc’ = ‘Dallas’.

It happens because when dbForge Studio for MySQL generates the internal UPDATE statement, only the fields returned by the previous query are included in the WHERE clause of the UPDATE statement. In our example DeptNo = 20 that was stated in the WHERE clause of the SELECT statement was not included in the internal UPDATE statement as there was no the ‘DeptNo’ field in the grid when modifying data.

So that if you’re planning to modify data that will be returned as a result of the SELECT statement in the grid, we suggest you to make sure that all the fields that are specified in the WHERE clause of the SELECT statement are also specified in the SELECT clause of the statement.

In our example, you have to specify the ‘DeptNo’ field in the SELECT clause of the statement.

SELECT DeptNo, DName, loc FROM dept WHERE DeptNo = 20;

Now if you modify any of the values of the records in Data Editor, the above actions will exclude accidental changes in the records that are not displayed in the grid.

Finally, to avoid such problems that may occur when modifying data that was returned as a result of the ‘SELECT’ statement inside of which the ‘WHERE’ clause is used, make sure you’re specifying the ‘*’ in the ‘SELECT’ clause or at least all the columns listed in the ‘WHERE’ clause of the statement.

Merry Christmas and Happy New Year!

As Christmas holidays are drawing closer, we offer you to get 20% off any product license order.

Don’t think twice, just save 20%. And what more, you get free access to all future releases of the respective products for one year.

Hurry up, this offer is available only from December 01, 2011 till January 10, 2012. Decide and order right now.

We wish you all the best in the coming year, let joy and happiness be your constant partners and friends!

Best Christmas Wishes,
Devart Team

Introduction
Debugging SQL code is a very important task these days. Unfortunately not all database servers support this feature. And MySQL is one of such servers. In spite of its wide feature set, debugging is not implemented in it. So the problem is that MySQL provides no API functionalities for code debugging. But the necessity to debug SQL code does not disappear and becomes more and more important each year. Some companies that develop tools to access data of MySQL server databases create their own debuggers that solve this problem to some extent.

Types of Debuggers
Now we will consider the types of debuggers that can be implemented for MySQL server and principles of their work.
(more…)

Despite the fact that dbForge products were developed only for Windows platforms, our active users (thanks to Tsvetkov) have found a way to use some features of dbForge Studio for MySql on Linux family operating systems.

Tests were run under .Net Framework 2.0 on Wine emulator.

The following command line functionality works with no visible issues:

• /backup – backups a database
• /restore – restores a database
• /datacompare – launches a data comparison
• /datacompare /sync – launches a database synchronization
• /schemacompare – launches a schema comparison
• /schemacompare /sync – launches a database synchronization
• /dataexport (starting from version 5.0) – exports data
• /dataimport (starting from version 5.0) – imports data
• /execute – executes a script

Testing of the following GUI functionality completed with full success:

• Import Wizard
• Export Wizard
• Backup Wizard
• Restore Wizard
• SQL Formatter Wizard

Data comparison. We were able to perform data comparison operation with some limitations:

• there is no possibility to view differences between the records of the relevant objects
• Data Synchronization Wizard doesn’t work at all

Schema comparison. Testing of the schema comparison functionality finished with the following results:

• Schema Comparison Wizard works properly
• Schema Synchronization Wizard is supported excepting the possibility to view synchronization script for the object. However, you can save documents and use them as a command line argument

Stored procedures debugger. Works properly if you disregard the artifacts.

The rest of the functionality is associated with tool-windows or documents and mostly blocked because of problems with rendering. To avoid the issues associated with tool-windows, change the window status from Docked to Floating and vice versa.

If you are interested in using dbForge Studio for MySql on Linux, you can use the following installation recommendations:

1. Build wine with the following options:

app-emulation/wine-1.3.3 USE="X alsa dbus gecko jpeg ncurses opengl perl png ssl threads
truetype win32 xinerama xml (-capi) -cups -custom-cflags (-esd) -fontconfig -gnutls
(-gphoto2) -gsm (-hal) -jack -lcms -ldap -mp3 -nas -openal -oss -pulseaudio -samba (-scanner)
 -test -win64 -xcomposite"

2. Install to a new folder .wine files (optionally, but preferably)

3. Run:

- sh winetricks gdiplus

Note: Do not use gdiplus from wine (dbforge crashes with an error when starting)

4. Install dotnet20 (in Fedora13 the installation interrupts with an error):

- sh winetricks dotnet20

5. Run:

wine ./dbforgemysqlru.exe (installs with no visible issues)

Our company has been working on its own MySQL GUI client development for over 6 years. Many features were implemented in this product in accordance with our users’ requests, that acted as task originators for us. We are glad that the development process of our MySQL Front-End goes on and on, and we were able to release version 5.0 of the product. For the release of version 5.0, we decided to make a special video telling about the product history. You can see the result.

Funny video about history of our MySQL GUI tool

We did not intend to have a professional video made by clip-makers. The video was shot frame-by-frame using a common digital camera. After that jpg files were rendered to the mp4 video format. Shooting took almost 7 hours. All models were made by hand from plasticine by one of our employees, whose hobby is design and model-making. All of us want to thank him for this!

A comma-separated values (CSV) file is a simple file format that is widely supported, so it is often used to move tabular data between different computer programs that support the format. CSV file is a text format for a database table. Each record in the table is one line of the text file. Each field value of a record is separated from the next with a comma. For example, a CSV file might be used to transfer information from a database to a spreadsheet or another database. Of course, there are more advanced formats to store data, for example, XML, but CSV does have one advantage over XML. CSV has much lower overhead, thereby using much less bandwidth and storage than XML.

Now let’s analyse how one can save data from a MySQL table to a CSV file.

(more…)

In the new version we made a lot of improvements and added a whole pack of new features in almost all dbForge Studio’s GUI tools. And the most remarkable thing about this release is that nearly all novelties were suggested by our users. So we hope they will enjoy working with the new version and feel that they have contributed a lot to make it as it is.

Cutting-Edge MySQL GUI Client: dbForge Studio for MySQL

With the help of our users we could work on the product on the new level, and as a result of such successful tight cooperation its standard of quality grew. This new standard includes taking into account actual needs of people using our product and implementing their exact requirements except studying the market in general and implementing features that are theoretically demanded.

When you open the application to get acquainted with the new version, you will be surely stunned and pleasantly surprised by its new look. When you start using it, we hope you will be thrilled by the inner improvements and make the best use of them:
(more…)