DMXzone Database Connector Reference

A more detailed overview of the features

In this article you can explore the DMXzone Database Connector functionality. The very same options and features are available for the PHP, ASP and ASP.NET version of the extension. We covered everything from a simple database connection, through the database query builder to table joins and pagination.

Features

Full integration within DMXzone Server Connect

You can use the DMXzone Database Connector's built-in connection wizard directly in DMXzone Server Connect to define and manage your connections to all well-known databases.

Database Connections

Built-in connection wizard

The built-in connection wizard allows you to connect to your database easily and get your data the Ajax way. It comes with full support for wide range of well-known databases and uses specific adapters for each type database so that it can use the native power of the database without bothering you with weird SQL syntax!

Support for standard PHP, ASP and ASP.NET server types database

The extension comes in PHP, ASP and ASP.NET versions. If you have both installed in your DMXzone Sever Connect you can choose between PHP, ASP and ASP.NET server technology otherwise the field will only display the installed one.

ASP.NET database types

Custom connection strings for PHP and ASP

In order to connect to your database you can always enter your custom connection string, which follows the syntax for PDO, if you're using PHP, or ADO if you're using ASP.

Database type properties

Depending on the chosen database, the connection wizard pops up different options to build your connection string automatically so you don't have to worry about weird connection string syntax.

Test your database connection

Once the connection to your database is established, a confirmation window pops up. If it's not connected properly an error message appears on the popup window.

Connection wide options

You can set the maximum allowed returned records from this connection as well as to enable the debugging info option that will bring you additional information in the browser and help you in your development process.

Database Source Query Builder

Full database schema reading support – The Database Connector connects to your database and reads its full schema like tables, columns and data types so that everything is available as pickup options in Dreamweaver for its visual Query Builder tool!
Fully automatic SQL generator – When using the Database Connector, you never need to bother any more with writing SQL queries! Actually you don’t need to write any SQL as everything is fully generated for you! As the extension ‘ knows’ the different SQL dialects for each database it uses the power features of this specific database, automatically!
Automatic data types conversion – The extension automatically knows your database column types and does the right conversions of your data types.
Full Data Type Validation and Enforcement - when various data types are being used as input, for example as filter parameters, those are first validated against their type. This makes your pages extra secure against various SQL injections attacks
Easy tables and column pickup - you can quickly add tables to be listed in the columns pickup view. From there you can populate your selects, filters and sort orders. When adding more then one table, a special Table Joins Manager dialog will appear. Using the Database Source Query Builder is a breeze, just select the connection, add a table to the column picker tree, add columns from it to the selects, filters and or sort orders and you are done!

Add and remove tables

From the table manager you may add or remove tables to pickup columns from.

Table column aliases

To improve the readability of your result tables you can assign column aliases.

Individual column filtering conditions

The conditions node lists conditions which must be met before the filter can be run. There are basic conditions like =, as well as advanced, such as contains, begins with, ends with and between. Contains and begins/ends with are very powerful string searching operators that incorporate the native database search options.

Multiple filters conditions

You can add as many filters you want! On each sequential filter you can choose the logical operator (and/or) to be applied with the previous one.

Default sort option

In the Database Source Query Builder you can add default sorting order for you database table columns.

Table Joins

Table Joins

SQL joins are used to combine rows from two or more tables. The Database Connector features inner, left and right joins where you can add more than one table to the Database Source Query Builder.

Add, edit and remove tables

You can add as many tables as you want to and also edit them any time by selecting the table from the columns picker tree in the Database Source Query Builder. Any table can be easily removed from the delete button.

Inner Join

The inner join is the most common and useful type of query. Most of the time you are interested in the records that match in the two tables and this is what the inner join gives you. An inner join return all rows from multiple tables where the join condition is met.
Example: An inner join of Customers and Orders would return details of all the customers who have placed orders together with the details of the orders that they have placed.

Left join

Returns all rows from the left table, and the matched rows from the right table. The result is NULL in the right side when there is no match.

Right join

A right join is the opposite of the left join and it returns all rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match.

Join conditions

The conditions node lists conditions which must be met before the tables to be joined.

Multiple joins conditions

You can add as many joins you want! On each sequential join you can choose the logical operator (and/or) to be applied with the previous one.

Pagination

Powerful built-in pagination

The DMXzone Database Connector has a native pagination built-in! No need to do any weird, database specific SQL magic here, the Database Connector covers your back. It knows all the various SQL Databases and uses a specific paging technique specific to that database. So you always get the most powerful solution without the need of any SQL programming.

Lubov has been with DMXzone for 8 years now, contributing to the Content and Sales departments. She is bringing high quality content in the form of daily blog updates, reviews, tutorials, news, newsletters,update emails and extensions' manuals. If you have a product that needs publicity or any other questions about the entire DMXzone community, she is the one you can contact.