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
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
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. Common problems
25.1. The driver class was not found
25.2. Syntax error when creating stored procedures
25.3. Timestamps with timezone information are not displayed correctly
25.4. Some of the dialogs are too small
25.5. Excel export not available
25.6. Out of memory errors
25.7. High CPU usage when executing statements
25.8. General DBMS problems
25.9. Oracle Problems
25.10. MySQL Problems
25.11. Microsoft SQL Server Problems
25.12. DB2 Problems
25.13. PostgreSQL Problems
25.14. Sybase SQL Anywhere Problems
26. Options dialog
26.1. General options
26.2. Editor options
26.3. Macro options
26.4. Bookmark options
26.5. Editor colors
26.6. Font settings
26.7. Auto-completion options
26.8. Workspace options
26.9. Options for displaying data
26.10. Options for formatting data
26.11. Data display colors
26.12. Options for data editing
26.13. DbExplorer options
26.14. Window Title
26.15. SQL Formatting
26.16. SQL Generation
26.17. External tools
26.18. Look and Feel
27. Configuring keyboard shortcuts
27.1. Assign a shortcut to an action
27.2. Removing a shortcut from an action
27.3. Reset to defaults
28. Advanced configuration options
28.1. DBID
28.2. GUI related settings
28.3. Editor related settings
28.4. DbExplorer Settings
28.5. General configuration settings
28.6. Database related settings
28.7. Configuring the check for the update table
28.8. DBMS specific settings
28.9. SQL Execution related settings
28.10. Default settings for Export/Import
28.11. Controlling the log file
28.12. Configure Log4J logging
28.13. Settings related to SQL statement generation
28.14. Customize table source retrieval
28.15. Filter settings
Index