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
4.10. Command line parameters
5. JDBC Drivers
5.1. Configuring JDBC drivers
5.2. Connecting through ODBC
5.3. Specifying a library directory
5.4. Popular JDBC drivers
6. Connecting to the database
6.1. Connection profiles
6.2. Managing profile groups
6.3. JDBC related profile settings
6.4. PostgreSQL connections
6.5. Extended properties for the JDBC driver
6.6. SQL Workbench/J specific settings
6.7. Connect to Oracle with SYSDBA privilege
7. Editing SQL Statements
7.1. Editing files
7.2. Code completion
7.3. Show hints for INSERT statements
7.4. Customizing keyword highlighting
7.5. Reformat SQL
7.6. Create SQL value lists
7.7. Programming related editor functions
8. Working with bookmarks
8.1. Defining bookmarks
8.2. Jumping to a bookmark
8.3. Configuring the display of the bookmark list
9. Creating stored procedures and triggers
9.1. PostgreSQL
9.2. Oracle PL/SQL
9.3. Other DBMS
10. Using SQL Workbench/J
10.1. Displaying help
10.2. Resizing windows
10.3. Executing SQL statements
10.4. Displaying results
10.5. Dealing with BLOB and CLOB columns
10.6. Performance tuning when executing SQL
10.7. Using workspaces
10.8. Saving and loading SQL scripts
10.9. Displaying the structure of tables
10.10. Viewing server messages
10.11. Editing data
10.12. Deleting rows from the result
10.13. Sorting the result
10.14. Filtering the result
10.15. Running stored procedures
10.16. Export result data
10.17. Copy data to the clipboard
10.18. Import data into the result set
11. Using SQL Workbench/J specific annotations in SQL comments
11.1. Naming result tabs
11.2. Adding macros to the result's context menu
11.3. Re-using an existing named result tab
11.4. Scrolling the result
11.5. Appending a results
11.6. Suppressing empty results
11.7. Automatic refresh of the result
12. Using macros and text clips
12.1. Loading and saving macro sets
12.2. Defining Macros
12.3. Executable macros
12.4. Expandable macros
13. Working with foreign keys
13.1. Navigating referenced rows
13.2. Generating JOIN conditions
13.3. Selecting foreign key values in referencing tables
13.4. Deleting rows with foreign keys
14. DBMS specific features
14.1. PostgreSQL specific features
14.2. Oracle specific features
15. Variable substitution in SQL statements
15.1. Defining variables
15.2. Populate a variable from a SELECT statement
15.3. Populate a variable from a file
15.4. Editing variables
15.5. Using variables in SQL statements
15.6. Prompting for values during execution
15.7. Controlling the order of variables during prompting
16. Using SQL Workbench/J in batch files
16.1. Specifying the connection
16.2. Specifying the script file(s)
16.3. Specifying a SQL command directly
16.4. Specifying a delimiter
16.5. Specifying an encoding for the file(s)
16.6. Specifying a logfile
16.7. Handling errors
16.8. Specify a script to be executed on successful completion
16.9. Specify a script to be executed after an error
16.10. Ignoring errors from DROP statements
16.11. Changing the connection
16.12. Controlling console output during batch execution
16.13. Running batch scripts interactively
16.14. Defining variables
16.15. Setting configuration properties
16.16. Examples
17. Using SQL Workbench/J in console mode
17.1. Entering statements
17.2. Exiting console mode
17.3. Setting or changing the connection
17.4. Displaying result sets
17.5. Running SQL scripts that produce a result
17.6. Controlling the number of rows displayed
17.7. Controlling the query timeout
17.8. Managing connection profiles
17.9. PostgreSQL psql commands
18. Export data using WbExport
18.1. Memory usage and WbExport
18.2. Exporting Excel files
18.3. General WbExport parameters
18.4. Parameters for text export
18.5. Parameters for XML export
18.6. Parameters for type SQLUPDATE, SQLINSERT or SQLDELETEINSERT
18.7. Parameters for Spreadsheet types (ods, xslm, xls, xlsx)
18.8. Parameters for HTML export
18.9. Parameters for JSON export
18.10. Compressing export files
18.11. Examples
19. Import data using WbImport
19.1. Importing spreadsheet files
19.2. General parameters
19.3. Parameters for the type TEXT
19.4. Text Import Examples
19.5. Parameters for the type XML
19.6. Parameters for spreadsheet import
19.7. Update mode
20. Copy data across databases
20.1. General parameters for the WbCopy command.
20.2. Copying data from one or more tables
20.3. Copying data based on a SQL query
20.4. Update mode
20.5. Synchronizing tables
20.6. Examples
21. Comparing databases
21.1. Compare two database schemas - WbSchemaDiff
21.2. Compare data across databases - WbDataDiff
22. Other SQL Workbench/J specific commands
22.1. Create a report of the database objects - WbSchemaReport
22.2. Search source of database objects - WbGrepSource
22.3. Search data in multiple tables - WbGrepData
22.4. Define a script variable - WbVarDef
22.5. Delete a script variable - WbVarDelete
22.6. Show defined script variables - WbVarList
22.7. Confirm script execution - WbConfirm
22.8. Run a stored procedure with OUT parameters - WbCall
22.9. Execute a SQL script - WbInclude (@)
22.10. Extract and run SQL from a Liquibase ChangeLog - WbRunLB
22.11. Handling tables or updateable views without primary keys
22.12. Change the default fetch size - WbFetchSize
22.13. Run statements as a single batch - WbStartBatch, WbEndBatch
22.14. Extracting BLOB content - WbSelectBlob
22.15. Control feedback messages - WbFeedback
22.16. Setting connection properties - SET
22.17. Changing Oracle session behaviour - SET
22.18. Changing read only mode - WbMode
22.19. Generate DROP statement with dependencies - WbGenerateDrop
22.20. Generate DELETE statements with dependencies - WbGenerateDelete
22.21. Generate SQL script for database objects - WbGenerateScript
22.22. Show table structure - DESCRIBE
22.23. List tables - WbList
22.24. List indexes - WbListIndexes
22.25. List stored procedures - WbListProcs
22.26. List triggers - WbListTriggers
22.27. List catalogs - WbListCat
22.28. List schemas - WbListSchemas
22.29. Show the source of a table - WbTableSource
22.30. Show the source of a view - WbViewSource
22.31. Show the source of a stored procedures - WbProcSource
22.32. Count rows for all tables - WbRowcCount
22.33. Change the connection for a script - WbConnect
22.34. Show the history of SQL statements - WbHistory
22.35. Run an XSLT transformation - WbXslt
22.36. Running operating system commands - WbSysExec
22.37. Opening a file with the default application - WbSysOpen
22.38. Using Oracle's DBMS_OUTPUT package
22.39. Define a new macro - WbDefineMacro
22.40. List available macros - WbListMacros
22.41. Show a text to the user - WbEcho
22.42. Conditional execution
22.43. Change an internal configuration paramter - WbSetConfig
23. DataPumper
23.1. Overview
23.2. Selecting source and target connection
23.3. Copying a complete table
23.4. Advanced copy tasks
24. Database Object Explorer
24.1. Objects tab
24.2. Table details
24.3. Modifying the definition of database objects
24.4. Table data
24.5. Changing the display order of table columns
24.6. Customize data retrieval
24.7. Customizing the generation of the table source
24.8. View details
24.9. Procedure tab
24.10. Search table data
25. Working with the Database Object tree
25.1. Filtering the elements in the tree
25.2. Drag and drop support
25.3. Finding elements in the tree
25.4. Features available through the context menu
26. Common problems
26.1. The driver class was not found
26.2. Syntax error when creating stored procedures
26.3. Timestamps with timezone information are not displayed correctly
26.4. Some of the dialogs are too small
26.5. Excel export not available
26.6. Out of memory errors
26.7. High CPU usage when executing statements
27. Common DBMS problems
27.1. Oracle
27.2. MySQL
27.3. Microsoft SQL Server
27.4. IBM DB2
27.5. PostgreSQL
27.6. Sybase SQL Anywhere
28. Options dialog
28.1. General options
28.2. Editor options
28.3. Macro options
28.4. Bookmark options
28.5. Editor colors
28.6. Font settings
28.7. Auto-completion options
28.8. Workspace options
28.9. Options for displaying data
28.10. Options for formatting data
28.11. Data display colors
28.12. Options for data editing
28.13. DbExplorer options
28.14. Window Title
28.15. SQL Formatting
28.16. External SQL Formatter
28.17. SQL Generation
28.18. External tools
28.19. Look and Feel
29. Configuring keyboard shortcuts
29.1. Assign a shortcut to an action
29.2. Removing a shortcut from an action
29.3. Reset to defaults
30. Advanced configuration options
30.1. DBID
30.2. GUI related settings
30.3. Editor related settings
30.4. Controlling code generation for code snippets
30.5. DbExplorer Settings
30.6. General configuration settings
30.7. Database related settings
30.8. Configuring the check for the update table
30.9. DBMS specific settings
30.10. SQL Execution related settings
30.11. Default settings for Export/Import
30.12. Controlling the log file
30.13. Configure Log4J logging
30.14. Configuring the logfile viewer
30.15. Settings related to SQL statement generation
30.16. Customize table source retrieval
30.17. Filter settings
Index