Database Tour Documentation Contents Index

Typical Tasks Solutions

 

Notes

This topic is intended mainly for newbies.

Here are described only a few examples of how Database Tour can be used.

In the examples, table and field names are hypothetical; use the names from your own database instead.

In the bigger part of the examples it is assumed that you have already connected the corresponding database.

1. Set all the data of the field Name to the same value "Ronald":

Note that the operation applied only for table, where there is no unique key on field Name.

Solution 1

Open the table and go to Data page. If Allow Edit button is not down, then click it. Right-click the corresponding column and choose Batch Change Field Values. Type the value and click OK.

Solution 2

Create new SQL window, go to Edit page and type the SQL text

UPDATE clients SET name = 'Ronald'

Click Execute Query button or press F9.

See also

 Batch Changing Field Values

 Working with SQL Queries

2. The same as previous task but set only the data in those records, where value in field Debt greater than 20:

Solution 1

Open the table and go to Data page. If Allow Edit button is not down, then click it. Type the filter condition (above the table grid)

Debt>20

Press ENTER or click Apply Filter button. Then do the same things as in the previous example.

Solution 2

Create new SQL window, go to Edit page and type the SQL text

UPDATE clients SET name = 'Ronald'

WHERE debt>20

Click Execute Query button or press F9.

See also

 Batch Changing Field Values

 Working with SQL Queries

 Filtering Data

3. Delete those records, where value of field Debt less than 10.

Solution 1

Open the table and go to Data page. If Allow Edit button is not down, then click it. Click Select Records button, select records to delete and click Delete Record button.

Solution 2

Open the table and go to Data page. If Allow Edit button is not down, then click it. Type the filter condition (right above the table grid)

Debt<10

Press ENTER or click Apply Filter button. Click Select Records button, select all records and click Delete Record button.

Solution 3

Create new SQL window, go to Edit page and type the SQL text

DELETE FROM clients

WHERE debt<10

Click Execute Query button or press F9.

See also

 Working with Tables

 Working with SQL Queries

 Filtering Data

4. Export data from DBF (dBase) table to a new DB (Paradox) table.

Open the DBF table, go to Data page and click Export Data button. Go to Database tab. Click Select a Database button and select destination folder for the new table; from driver list, select Paradox; click OK. Specify table name for the new table and other desired export options; for export mode, choose Replace+Insert. Click Next. Specify field mappings between old and new tables. Click Export. A new table will be created.

See also

 Exporting Data

5. Create a new table, which must consist of fields Name and Street from table Friends.

Open Friends table. If it consists of two foregoing fields only, then all you should do is to export data as in the previous example. Otherwise (if the table consists of three or more fields) create new SQL window, go to Edit page and type the SQL text

SELECT name, street FROM friends

Click Execute Query button or press F9. The result data will be shown on Result page. Export it to the new table as in the previous example.

See also

 Exporting Data

 Working with SQL Queries

6. Print a table or query.

Open a table or query. For table, go to Data page; for query, go to Result page.

Solution 1

Choose Tools | Print Table. Select needed options and click OK. The table will be printed as it appears on the screen. Note, that hidden columns will not be printed.

Solution 2 (Database Tour Pro only)

Click Report Builder button. Report template will be shown. Customize it if you wish. Click Preview. In Preview window, click Print, select needed options and click OK.

See also

 Printing Data

 Reports Overview

7. Print the first and the third column from a table.

Open a table or query. For table, go to Data page; for query, go to Result page.

Solution 1

Hide all columns, except first and third columns (use right mouse button and floating menu to do this). Choose Tools | Print Table menu, then click OK.

Solution 2

Click Report Builder button. In report template, select and delete unwanted column objects. Customize (if necessary) other objects. Click Preview. In Preview window, click Print, select needed options and click OK.

See also

 Printing Data

 Reports Overview

8. Print a table with totals for all numeric fields.

Open a table or query. For table, go to Data page; for query, go to Result page. Click Report Builder button. Mark with check Totals band in Report Tree window to make it visible. Place Expression objects on it under first numeric column. Call expression editor (button '...' in Object Inspector window). In the editor, select SUM from the function list and desired field from the field list and click Add button. Then click OK. Repeat this procedure for all columns, to which you wish to add expressions.

Click Preview. In Preview window, click Print and then click OK.

See also

 Report Expressions

 Reports Overview

9. Find the "Bill" word in field Name.

Open the table and go to Data page. Right-click the Name column and choose Find. Select desired options and click OK.

See also

 Searching and Replacing Text

10. Replace all the "Cat" words in the fields Animal and Description with the "Mouse" word.

Solution 1

Open the table and go to Data page. If Allow Edit button is not down, then click it. Right-click the Name column and choose Replace. Select desired options and click OK. Repeat this for another column.

Solution 2

Open the table and go to Data page. If Allow Edit button is not down, then click it. Right-click each unnecessary column and choose Hide Column. Choose Search | Replace. Type Cat in the upper edit window and Mouse in the lower one. Select All in the Fields area. Select (if you wish) other options and click OK.

See also

 Searching and Replacing Text

11. Change all first symbols in all text fields of Animals table to upper case.

Open the table and go to Data page. If Allow Edit button is not down, then click it. Choose Tools | Batch Operations with Text Fields.... Check All in the Fields area and First to UPPER Case in the Functions area. Select other desired options and click OK.

See also

 Batch Operations with Text Fields

12. Get sum of numeric field Price from Goods table.

Solution 1

Open the table and go to Data page. Choose Tools | Arithmetic Functions.... Select the function, and then select the field (column) from dropdown list.

Solution 2

Create new SQL window, go to Edit page and type the SQL text

SELECT sum(price) FROM goods

Click Execute Query button or press F9.

See also

 Arithmetic Functions

 Working with SQL Queries

13. Create a BDE alias.

Choose Options | BDE Options | Aliases.... Click New Alias button. Select alias type and click OK. Set desired alias parameters and click Apply Changes button. If you click Cancel Changes before Apply Changes, then the alias will not be created.

See also

 Creating and Configuring BDE Aliases

14. Modify an existing BDE alias.

Note, that you cannot modify opened alias (i.e. when the corresponding database is opened).

Choose Options | BDE Options | Aliases.... Select an alias from alias list and click Modify Alias button. Set desired alias parameters and click Apply Changes button. To cancel changes you made to the alias, click Cancel Changes button. If you did not make any changes and wish to leave a modifying mode, then click Modify Alias button again to restore its original non-pushed state.

See also

 Creating and Configuring BDE Aliases

15. Add the two first records in fields Capital and Area from Country table into the fields Name and Area accordingly from City table (from the same database).

Open the City table and go to Data page. If Allow Edit button is not down, then click it. Right-click the Country table in the table list and choose Import Data From Country. Export dailog will appear. Make sure the source table specifiied correctly, and click Next. Check Limit the record count to option and type '2' in the corresponding field. Click Next. Match the field Capital from the Country table to the field Name from the City table and the field Area to the Area. Other fields from the Country table must not match to any field from the City table. Click OK.

See also

 Importing Data

16. Rename dBase table City and related files (index and so on), if they exists, to Cities. (BDE connections only)

Note, that sometimes you cannot rename table (for example, if other users work with it). Also, you cannot change the table format by renaming, use export utility instead.

Select City table in the table list and go to Data page. Choose Table | Utilities | Rename.... Type the new name and click OK.

See also

 Exporting Data

 Renaming Tables

17. Maximally automate the following actions:

Open WorkData ODBC data source, City table from this database, load report template from file C:\My templates\city.dtt and show the report preview. (Database Tour Pro only)

Create a text file and give it .bat extension. Open it for editing and type the following line of text (use a real path to Database Tour program instead of "C:\Program Files\Database Tour\dbtour.exe"):

       "C:\Program Files\Database Tour\dbtour.exe"  /OpenDB /DBInterface=ado /DBKind=DSN /DB=WorkData /TableName=City "/ReportTemplateFile=C:\My templates\city.dtt" /ReportPreview

Save the file. Now, if you double-click this file, all the foregoing actions will be performed automatically. It is very useful, for example, when you need to perform daily reports.

See also

 Command Line Usage

18. Encrypt all files from C:\My Templates folder. (Database Tour Pro only)

Create a text file and give it .bat extension. Open it for editing and type the following line of text (use a real path to Database Tour program instead of "C:\Program Files\Database Tour\dbtour.exe"):

"C:\Program Files\Database Tour\dbtour.exe"  /encrypt /silent "/SrcFile=C:\My Templates\city.dtt" "/TrgFile=C:\Encrypted\city1.eee" /Password=MyPassword

Duplicate this line for every file you wish to encrypt, and adapt it accordingly.

Save the file and run it to encrypt all selected files.

See also

 Command Line Usage

 Encrypting / Decrypting Files

19. Create a report with grouping records. (Database Tour Pro only)

Open a table or query. For table, go to Data page; for query, go to Result page. To group records by some field(s), you should sort the table by these field(s).

Click Report Builder button. In Report Tree, select Report to see the report properties. Specify grouping expression.

Check grouping bands in report tree to make them visible. Place desired objects on them.

Click Preview. In Preview window, click Print and click OK.

Note: There are examples of report templates with grouping in the Example folder of the program (for example, groups.dtt).

See also

 Reports Overview

20. Specify custom titles for data grid columns.

Open the table and go to Data page. Right-click the needed column and choose Column Properties | Title.... Specify the title and click OK. Physical field name remains unchanged. There is an ability to change other visual properties of table columns, save the column definitions to file and load them for this table at a later time.

See also

 Data View

21. Specify the column pick list, all records from which must be extracted from a certain database field

Open the table, from which the pick list data should be copied, and go to Data page. Right-click the column, from which the data should be copied and choose Copy | Column.

Then open the table, where the pick list should be created, and go to Data page. Right-click the needed column and choose Column Properties | Pick List.... Click Paste from Clipboard button. Modify the list (if it is needed) and click OK.

See also

 Data View

22. Copy opened table or query to clipboard

Open a table or query. For table, go to Data page; for query, go to Result page.

Then choose Tools | Copy | Full Table or click Copy Data to Clipboard button and choose Full Table from dropdown menu.

See also

 Using Clipboard

 Copying Data to Clipboard

23. Paste data from clipboard into an opened table

Open the table and go to Data page. If Allow Edit button is not down, then click it.

Select a field, from which you wish to paste data, and press CTRL+ALT+V.

See also

 Using Clipboard

24. Export data from a table to JSON

Open the table (for example, customer.dbf from Examples folder) and go to Data page.

Click Export data button. In the Export Data window, go to the Text / CSV page and specify the target filename.

Uncheck With column titles checkbox.

In the Schema box, choose Expression and type the following expression:

'{' +
'"ID":' + to_string(dataset_field_val(1, 'ID')) + ', ' +
'"FIRSTNAME": "' + dataset_field_val(1, 'FIRSTNAME') + '", ' +
'"LASTNAME": "' + dataset_field_val(1, 'LASTNAME') + '", ' +
'"CITY": "' + dataset_field_val(1, 'CITY') + '", ' +
'"COUNTRY": "' + dataset_field_val(1, 'COUNTRY') + '"' +
'}'

Click Export button.

See also

 Exporting Data