SQL Workbench/J User's Manual


Table of Contents

1. General Information
1.1. Program version
1.2. Feedback and support
1.3. Credits and thanks
1.4. Third party components
2. Software license
2.1. Definitions
2.2. Grant of Copyright License
2.3. Restrictions (deviation of the Apache License)
2.4. Grant of Patent License
2.5. Redistribution
2.6. Submission of Contributions
2.7. Trademarks
2.8. Disclaimer of Warranty.
2.9. Limitation of Liability
2.10. Accepting Warranty or Additional Liability
3. Change log
4. Installing and starting SQL Workbench/J
4.1. Pre-requisites
4.2. First time installation
4.3. Upgrade installation
4.4. Starting the program from the commandline
4.5. Starting the program using the shell script
4.6. Starting the program using the Windows® launcher
4.7. Configuration directory
4.8. Copying an installation
4.9. Increasing the memory available to the application
5. Command line parameters
5.1. Specify the directory for configuration settings
5.2. Specify a base directory for JDBC driver libraries
5.3. Specify the file containing connection profiles
5.4. Defining variables
5.5. Prevent updating the .settings file
5.6. Connect using a pre-defined connection profile
5.7. Connect without a profile
6. JDBC Drivers
6.1. Configuring JDBC drivers
6.2. Specifying a library directory
6.3. Popular JDBC drivers
7. Connecting to the database
7.1. Connection profiles
7.2. Managing profile groups
7.3. JDBC related profile settings
7.4. PostgreSQL connections
7.5. Extended properties for the JDBC driver
7.6. SQL Workbench/J specific settings
7.7. Connect to Oracle with SYSDBA privilege
7.8. Using the quick filter
8. Using workspaces
8.1. Overview
8.2. Creating a copy of the current workspace
8.3. Load a different workspace
8.4. Workspace and external files
8.5. Workspace variables
9. Editing SQL Statements
9.1. Editing files
9.2. Code completion
9.3. Show hints for INSERT statements
9.4. Customizing keyword highlighting
9.5. Reformat SQL
9.6. Create SQL value lists
9.7. Programming related editor functions
10. Working with bookmarks
10.1. Defining bookmarks
10.2. Jumping to a bookmark
10.3. Configuring the display of the bookmark list
11. Creating stored procedures and triggers
11.1. PostgreSQL
11.2. Oracle PL/SQL
11.3. Other DBMS
12. Using SQL Workbench/J
12.1. Displaying help
12.2. Resizing windows
12.3. Executing SQL statements
12.4. Displaying results
12.5. Dealing with BLOB and CLOB columns
12.6. Performance tuning when executing SQL
12.7. Using workspaces
12.8. Saving and loading SQL scripts
12.9. Displaying the structure of tables
12.10. Viewing server messages
12.11. Editing data
12.12. Deleting rows from the result
12.13. Sorting the result
12.14. Filtering the result
12.15. Running stored procedures
12.16. Export result data
12.17. Copy data to the clipboard
12.18. Import data into the result set
13. Using SQL Workbench/J specific annotations in SQL comments
13.1. Naming result tabs
13.2. Adding macros to the result's context menu
13.3. Re-using an existing named result tab
13.4. Scrolling the result
13.5. Appending a results
13.6. Suppressing empty results
13.7. Automatic refresh of the result
14. Using macros and text clips
14.1. Loading and saving macro sets
14.2. Defining Macros
14.3. Executable macros
14.4. Expandable macros
15. Working with foreign keys
15.1. Navigating referenced rows
15.2. Generating JOIN conditions
15.3. Selecting foreign key values in referencing tables
15.4. Deleting rows with foreign keys
16. DBMS specific features
16.1. PostgreSQL specific features
16.2. Oracle specific features
17. Variable substitution in SQL statements
17.1. Defining variables
17.2. Populate a variable from a SELECT statement
17.3. Populate a variable from a file
17.4. Editing variables
17.5. Using variables in SQL statements
17.6. Prompting for values during execution
17.7. Controlling the order of variables during prompting
18. Using SQL Workbench/J in batch files
18.1. Specifying the connection
18.2. Specifying the script file(s)
18.3. Specifying a SQL command directly
18.4. Specifying a delimiter
18.5. Specifying an encoding for the file(s)
18.6. Specifying a logfile
18.7. Handling errors
18.8. Specify a script to be executed on successful completion
18.9. Specify a script to be executed after an error
18.10. Ignoring errors from DROP statements
18.11. Changing the connection
18.12. Controlling console output during batch execution
18.13. Running batch scripts interactively
18.14. Defining variables
18.15. Setting configuration properties
18.16. Examples
19. Using SQL Workbench/J in console mode
19.1. Entering statements
19.2. Exiting console mode
19.3. Setting or changing the connection
19.4. Displaying result sets
19.5. Running SQL scripts that produce a result
19.6. Controlling the number of rows displayed
19.7. Controlling the query timeout
19.8. Managing connection profiles
19.9. PostgreSQL psql commands
20. Export data using WbExport
20.1. Memory usage and WbExport
20.2. Exporting Excel files
20.3. General WbExport parameters
20.4. Parameters for text export
20.5. Parameters for XML export
20.6. Parameters for type SQLUPDATE, SQLINSERT or SQLDELETEINSERT
20.7. Parameters for Spreadsheet types (ods, xslm, xls, xlsx)
20.8. Parameters for HTML export
20.9. Parameters for JSON export
20.10. Compressing export files
20.11. Examples
21. Import data using WbImport
21.1. Importing spreadsheet files
21.2. General parameters
21.3. Parameters for the type TEXT
21.4. Text Import Examples
21.5. Parameters for the type XML
21.6. Parameters for spreadsheet import
21.7. Update mode
21.8. Native UPSERT mode
21.9. Native insertIgnore mode
22. Copy data across databases
22.1. General parameters for the WbCopy command.
22.2. Copying data from one or more tables
22.3. Copying data based on a SQL query
22.4. Update mode
22.5. Synchronizing tables
22.6. Examples
23. Comparing databases
23.1. Compare two database schemas - WbSchemaDiff
23.2. Compare data across databases - WbDataDiff
24. Search data and code in the database
24.1. Search source of database objects - WbGrepSource
24.2. Search data in multiple tables - WbGrepData
25. SQL Workbench/J to generate DDL commands
25.1. Generate DROP statement with dependencies - WbGenerateDrop
25.2. Generate SQL script for database objects - WbGenerateScript
25.3. Generate SQL script for foreign key constraints - WbGenerateFKScript
25.4. Generate a table definition from an import file - WbGenerateImpTable
25.5. Show the source of a table - WbTableSource
25.6. Show the source of a view - WbViewSource
25.7. Show the source of a stored procedures - WbProcSource
25.8. Show the source of a trigger - WbTriggerSource
25.9. Generate DELETE statements with dependencies - WbGenerateDelete
26. Show information about database objects
26.1. Create a report of the database objects - WbSchemaReport
26.2. Show table structure - DESCRIBE
26.3. List tables - WbList
26.4. List indexes - WbListIndexes
26.5. List stored procedures - WbListProcs
26.6. List triggers - WbListTriggers
26.7. List catalogs - WbListCat
26.8. List schemas - WbListSchemas
27. Manage macros with SQL Workbench/J command
27.1. Define a new macro - WbDefineMacro
27.2. Delete a macro - WbDeleteMacro
27.3. List available macros - WbListMacros
28. Manage variables with SQL Workbench/J
28.1. Define a script variable - WbVarDef
28.2. Delete a script variable - WbVarDelete
28.3. Show defined script variables - WbVarList
29. Other SQL Workbench/J specific commands
29.1. Confirm script execution - WbConfirm
29.2. Display a message box - WbMessage
29.3. Print a text - WbEcho
29.4. Run a stored procedure with OUT parameters - WbCall
29.5. Execute a SQL script - WbInclude (@)
29.6. Conditional execution
29.7. Extract and run SQL from a Liquibase ChangeLog - WbRunLB
29.8. Handling tables or updateable views without primary keys
29.9. Change the default fetch size - WbFetchSize
29.10. Run statements as a single batch - WbStartBatch, WbEndBatch
29.11. Extracting BLOB content - WbSelectBlob
29.12. Control feedback messages - WbFeedback
29.13. Setting connection properties - SET
29.14. Changing Oracle session behavior - SET
29.15. Changing read only mode - WbMode
29.16. Count rows for all tables - WbRowcCount
29.17. Change the connection for a script - WbConnect
29.18. Show the history of SQL statements - WbHistory
29.19. Run an XSLT transformation - WbXslt
29.20. Running operating system commands - WbSysExec
29.21. Opening a file with the default application - WbSysOpen
29.22. Change an internal configuration parameter - WbSetConfig
30. DataPumper
30.1. Overview
30.2. Selecting source and target connection
30.3. Copying a complete table
30.4. Advanced copy tasks
31. Database Object Explorer
31.1. Objects tab
31.2. Table details
31.3. Modifying the definition of database objects
31.4. Table data
31.5. Changing the display order of table columns
31.6. Customize data retrieval
31.7. Customizing the generation of the table source
31.8. View details
31.9. Procedure tab
31.10. Search table data
32. Working with the Database Object tree
32.1. Filtering the elements in the tree
32.2. Drag and drop support
32.3. Finding elements in the tree
32.4. Features available through the context menu
33. Common problems
33.1. The driver class was not found
33.2. Syntax error when creating stored procedures
33.3. The SQL source code for tables or indexes is incorrect
33.4. Timestamps with timezone information are not displayed correctly
33.5. Some of the dialogs are too small
33.6. Excel export not available
33.7. Out of memory errors
33.8. High CPU usage when executing statements
33.9. The GUI freezes when displaying menus or context menus
34. Common DBMS problems
34.1. Oracle
34.2. MySQL
34.3. Microsoft SQL Server
34.4. IBM DB2
34.5. PostgreSQL
34.6. Sybase SQL Anywhere
35. Options dialog
35.1. General options
35.2. Editor options
35.3. SQL Excecution options
35.4. Macro options
35.5. Bookmark options
35.6. Editor colors
35.7. Font settings
35.8. Auto-completion options
35.9. Workspace options
35.10. Options for displaying data
35.11. Options for formatting data
35.12. Data display colors
35.13. Options for data editing
35.14. DbExplorer options
35.15. Window Title
35.16. SQL Formatting
35.17. External SQL Formatter
35.18. SQL Generation
35.19. External tools
35.20. Look and Feel
36. Configuring keyboard shortcuts
36.1. Assign a shortcut to an action
36.2. Removing a shortcut from an action
36.3. Reset to defaults
37. Advanced configuration options
37.1. DBID
37.2. GUI related settings
37.3. Editor related settings
37.4. Controlling code generation for code snippets
37.5. DbExplorer Settings
37.6. General configuration settings
37.7. Database related settings
37.8. Configuring the check for the update table
37.9. DBMS specific settings
37.10. SQL Execution related settings
37.11. Default settings for Export/Import
37.12. Controlling the log file
37.13. Configure Log4J logging
37.14. Configuring the logfile viewer
37.15. Settings related to SQL statement generation
37.16. Customize table source retrieval
37.17. Customize index source retrieval
37.18. Filter settings
38. Simple properties based profile storage
Index