Frequently Asked Questions

1. Licensing

1.1 Are ALL these products FREE?

All other products but Data Xtractor are free, provided as-they-are, with no support. Data Xtractor combines the functionality of all other modules in a single executable. After a trial period, you can no longer run queries as usual for some supported databases, unless you buy the product.

1.2 May I have more than one Xtractor application running in the same time?

No, not on the same computer. If you already launched let’s say Model Xtractor, you cannot launch a second Model Xtractor application, or Data Xtractor application, in the same time. If you plan to use features from multiple applications, we strongly recommend installing and using only Data Xtractor, because this app includes the features of all other applications combined, and more.

2. Configuration

2.1 Is there a way to import connection strings from a text file?

File-Batch Connection Loader opens a custom ConnectionStrings.txt user file from your AppData\Local\Xtractor folder.

If you frequently connect and disconnect from databases and you’re not concerned with exposing those connection strings in a plain text file, create such file and fill it with your own data. Each line must start with a {dbms_type}; prefix and a valid ASP.NET connection string to your database, followed by a TAB and an optional description of your entry. Prefix with – the whole line if for some reason the database became unavailable.

2.2 Where can I find the password I entered when I connected to a database?

Nowhere. This is actually an important security measure we took: we never show in clear the password someone entered to connect to a database. In connection strings, the password always appears hidden, as ********.

If the connectivity has been established on your behalf by a database administrator, make sure the Save Password field is checked. This way, your connection parameters are persisted on our side, but you never need to know or enter the password.

Uncheck Save Password when you rather know the credentials and want to protect the database access better. This way, each time you launch the application, you are required again to enter the password.

2.3 Where is my user data collected?

By default, all your user data is collected into the SQLite file Xtractor.db, located under your AppData\Local\Xtractor user folder. If you remove an application and reinstall it, the user database is not removed and you may automatically reuse it again.

All our Xtractor applications will use and share by default this system database. If you use two different Xtractor applications – such as Model Xtractor and Data Xtractor – they will both use and share the same data. After you create some new models in Model Xtractor, the next time you launch Data Xtractor you’ll see those models automatically appear there as well.

2.4 Do I need administrative privileges to install the app?

It depends where you install it. If you install it in its default Program Files folder, all applications installed there require administrative privileges on setup. If you install it in a less restrictive folder – such as your AppData\Local folder, you may not have any restrictions at all. It is still possible some related database drivers may require admin privileges, because they are registered in the Registry.

2.5 Why import on some databases may fail with “Importing tables failed”?

There is a collation database settings we recently became aware of and it could be the origin of some failed imports. It will be fixed asap, stay tuned.

3. Data Model

3.1 When to use minified shapes?

Use minified table, view or query shapes when you want to see the connected path between two (expanded) shapes and you’re not concerned through which other shapes it passes through. Unlike the collapsed shapes, minified shapes take less space and completely hide the underlying object name.

One typical example is when you have a model with many irrelevant intersection tables, with just the foreign keys of the related tables propagated as primary keys. In most cases, intersection tables are just a technical implementation detail of the relational many-to-many relationship.

For instance, take the many-to-many relationship between Playlist and Track in the picture above, where PlaylistTrack is their intersection table. Most data modeling tools will show you only the first image, with all shapes expanded. But if you want to focus just on the main Playlist and Track tables and their n-n relationship, you can either collapse or minify the PlaylistTrack shape. Or remove it completely from the diagram and rather expand the Xtractor-specific derived chain relationship between the main tables.

3.2 Can I create new database tables and views with Model Xtractor?

No. None of the Xtractor applications can modify remote database data or metadata! We did this on purpose, to offer your database administrators a safe and secure interface through which users can query data and metadata, but never alter them.

What you can do is expand your database models with artifacts and extensions transparently saved in your local user database. You can create custom joins and aliases, chain relationships, hide table columns etc. None of these transformations will alter anything in the database itself. They will all come on top of the remote database model, as safe client side extensions.

3.3 How can I create a custom join?

In a model or query diagram, you start by dragging and dropping a table column over another table column. If a custom join is possible, a popup will open to help you eventually add more then one pair of columns for this join.

The join should make sense. For instance, you cannot pair columns with fundamentally different data types, such as one integer and the other one binary. You can however pair a tiny int column with a long int column. Examples of typical custom joins include on-the-fly relationships on First Name and Last Name columns. Unlike the database built-in foreign key joins, you can also remove the custom joins.

3.4 How can I print a diagram?

There is no Print command, but you have several options:

• Save it as a PDF file (with File – Save PDF). Then use a free reader like Acrobat Reader or Sumatra PDF, that have full Print and Print Preview support. You may send it to a printer, fax it, email it etc. PDF page format and margins can be adjusted from the toolbar at the bottom of your screen. Large diagrams are cut into separate pages.
• If you want just one large diagram, with no pages, to send to a plotter, save it rather as an image (with File – Save Image) and use one of the many free image tools to print it as it is.
• You can also copy it in the clipboard (with Edit – Copy) and paste it into another document, such as Word, then use that tool’s print support.

4. Query Design

4.1 What is the purpose of the Selection check boxes in a query design grid?

When checked, the expression you have in that column will also appear in the top list of a SELECT SQL query. When unchecked, it doesn’t. But you may use that expression as filter, in a WHERE or HAVING clause, or for the ORDER BY.

Hide the whole Selection row if you want all column expressions to appear in the SELECT SQL query.

4.2 Can I create SQL queries that alter data with Query Xtractor?

No. None of the Xtractor applications can modify remote database data or metadata! We did this on purpose, to offer your database administrators a safe and secure interface through which users can query data and metadata, but never alter them.

SELECT is the only kind of SQL query automatically generated by our designer. That’s actually a main strength of our application, that should give most database administrators the peace of mind, when they allow novice users connecting directly to a database.

4.3 How can I add DISTINCT to a query?

Simply click on the Show Distinct small toggle toolbar button while in design mode of a query. Generated query will switch between SELECT and SELECT DISTINCT.

4.4 Why Rows is sometimes zero for tables with data?

After you connect to Postgres or another database type, you may see Rows 0 in the Objects tab, for tables which are not actually empty.

This is because both Rows and Size columns from the Objects browser are initially just estimates collected from your database on the first import. Some platforms give us the exact row count, others (like PostgreSQL) provide an just approximate number. For 1-2 rows in the table, collected Rows could be 0.

Rows value is never automatically updated when rows are added or removed. But, whenever you need a refresh, you can select one or more tables, and execute the contextual Update Row Count command, as in the image below. A SELECT COUNT(*) will be executed right away for each selected table, and Row value updated. Be careful with this command, because the query may be slow on very big tables.

4.5 Is there a way to turn off the drill-down feature?

Not at this point, but we may add a switch option in a future version.

Drill-down is automatically determined by the PK-FK custom or built-in relationships. One way to avoid it is to exclude participating keys from the current query. For instance, assume you have an Order with a ProductID foreign key. If you exclude the ProductID from your query, there will be no drill-down to the related Product when you run it.

4.6 What are the unsupported SQL features?

Here is a list of SQL features that our models and query builder do not directly supports at this time, that we will unlikely support in the future and the reasons why. We fully encourage your feedback! Give us some constructive reasons why we should reconsider.

• BETWEEN … AND … – You can always take the >= … AND < = … approach. Between requires two parameters and it’s harder to accommodate this Boolean predicate in our builder.
• [NOT] EXISTS – We may generate some inline queries with EXISTS, but we found no use case where EXISTS couldn’t be successfully expressed with rather a different operator.
• INNER, FULL and LEFT/RIGHT JOIN – We always translate our inclusive and exclusive joins into one of these SQL joins. What users may find confusing is the notion of LEFT and RIGHT in the visual builder. What if you have both shapes one below the other? Which is right and which is left?
• GROUPING_ID – We support GROUPING and we use it to generate Grouping Flag and Grouping Fields, which both return better information than GROUPING_ID, are automatic and do not require manual handling of group columns.

4.7 Why BETWEEN should be avoided as SQL operator?

We did not find an east way to design it as an intuitive operation in our query builder. But there is always the more reliable syntax a >= 1 and a <= 10, for a BETWEEN 1 AND 10.

According to SQL All-in-One For Dummies (2011):

The BETWEEN keyword may be confusing because it doesn’t tell you explicitly whether the clause includes the end points.

Is 10 BETWEEN 10 AND 100 true? It is. Is 10 NOT BETWEEN 10 AND 100 true? It is not. The clause include the end points, but …still unclear.

If you use BETWEEN, you must be able to guarantee that the first term in your comparison is always equal to or less than the second term.

And this is an “element of surprise” we try to avoid. People may expect 20 BETWEEN 100 and 10 to be a legal statement and return true, but in fact Microsoft Access was our only supported database to return true. All other databases returned false and did not complain at all that this was not a valid statement. If we disallow this in our application, we;ll have to explain why.

4.8 How to sort by an expression that’s not in SELECT?

Assume you have a date of birth and you want to order by the month of birth. However, you do not want the month of birth to appear in the SELECT list.

Sort does not refer to the single column you may check for the top Field, but to the whole eventual expression you may add with Formula, based on your top field.

You may hide an expression from the SELECT list with an uncheck in Selection. That’s the typical way to create a “hidden” expression for another clause, such as ORDER BY, GROUP BY, or as a query expression reused by some other SELECT list item.

For this date of birth example:

1. 1. Select date of birth in your Field.
   2. Show Formula and add ToMonths function.
   3. Show Sort and select the order by type.
   4. Show Select and uncheck selection of this whole expression.

In the generated SQL, this whole expression will now appear only in the ORDER BY clause.

See also the Sort by Expression chapter in the Sorting Data article of the Knowledge Base.

4.9 How do you create a new folder for queries?

Select your queries, right click and choose Move to New Folder. A new custom subfolder is created, your queries are moved here and you can rename your folder now. Whenever you move the last child object out of a custom folder, the folder is automatically deleted.

We call them custom folders to separate them from the special top folders under your database node: Tables, Views, Models, Queries, Bookmarks. You cannot move, delete or rename these special folders . Any object can be moved to another custom folder, but within the same branch: you cannot move a query under Models, table under Queries and so on. To move objects and folders under a different existing folder, select them, then drag and drop them on that folder.