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. Increasing the memory available to the application
4.9. 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. Extended properties for the JDBC driver
6.5. SQL Workbench/J specific settings
6.6. Connect to Oracle with SYSDBA privilege
6.7. ODBC connections without a data source
7. Editing SQL Statements
7.1. Editing files
7.2. Working with bookmarks
7.3. Code completion
7.4. Show hints for INSERT statements
7.5. Customizing keyword highlighting
7.6. Reformat SQL
7.7. Create SQL value lists
7.8. Programming related editor functions
8. Creating stored procedures and triggers
9. Using SQL Workbench/J
9.1. Displaying help
9.2. Resizing windows
9.3. Executing SQL statements
9.4. Displaying results
9.5. Dealing with BLOB and CLOB columns
9.6. Performance tuning when executing SQL
9.7. Using workspaces
9.8. Saving and loading SQL scripts
9.9. Displaying the structure of tables
9.10. Viewing server messages
9.11. Editing data
9.12. Deleting rows from the result
9.13. Sorting the result
9.14. Filtering the result
9.15. Running stored procedures
9.16. Export result data
9.17. Copy data to the clipboard
9.18. Import data into the result set
10. Using macros and text clips
10.1. Loading and saving macro sets
10.2. Defining Macros
10.3. Executable macros
10.4. Expandable macros
11. Working with foreign keys
11.1. Navigating referenced rows
11.2. Generating JOIN conditions
11.3. Selecting foreign key values in referencing tables
11.4. Deleting rows with foreign keys
12. DBMS specific features
12.1. PostgreSQL specific features
12.2. Oracle specific features
13. Variable substitution in SQL statements
13.1. Defining variables
13.2. Populate a variable from a SELECT statement
13.3. Populate a variable from a file
13.4. Editing variables
13.5. Using variables in SQL statements
13.6. Prompting for values during execution
14. Using SQL Workbench/J in batch files
14.1. Specifying the connection
14.2. Specifying the script file(s)
14.3. Specifying a SQL command directly
14.4. Specifying a delimiter
14.5. Specifying an encoding for the file(s)
14.6. Specifying a logfile
14.7. Handling errors
14.8. Specify a script to be executed on successful completion
14.9. Specify a script to be executed after an error
14.10. Ignoring errors from DROP statements
14.11. Changing the connection
14.12. Controlling console output during batch execution
14.13. Running batch scripts interactively
14.14. Defining variables
14.15. Setting configuration properties
14.16. Examples
15. Using SQL Workbench/J in console mode
15.1. Entering statements
15.2. Exiting console mode
15.3. Setting or changing the connection
15.4. Displaying result sets
15.5. Running SQL scripts that produce a result
15.6. Controlling the number of rows displayed
15.7. Controlling the query timeout
15.8. Managing connection profiles
15.9. PostgreSQL psql commands
16. Export data using WbExport
16.1. Memory usage and WbExport
16.2. Exporting Excel files
16.3. General WbExport parameters
16.4. Parameters for text export
16.5. Parameters for XML export
16.6. Parameters for type SQLUPDATE, SQLINSERT or SQLDELETEINSERT
16.7. Parameters for Spreadsheet types (ods, xslm, xls, xlsx)
16.8. Parameters for HTML export
16.9. Parameters for JSON export
16.10. Compressing export files
16.11. Examples
17. Import data using WbImport
17.1. Importing spreadsheet files
17.2. General parameters
17.3. Parameters for the type TEXT
17.4. Text Import Examples
17.5. Parameters for the type XML
17.6. Parameters for spreadsheet import
17.7. Update mode
18. Copy data across databases
18.1. General parameters for the WbCopy command.
18.2. Copying data from one or more tables
18.3. Copying data based on a SQL query
18.4. Update mode
18.5. Synchronizing tables
18.6. Examples
19. Comparing databases
19.1. Compare two database schemas - WbSchemaDiff
19.2. Compare data across databases - WbDataDiff
20. Other SQL Workbench/J specific commands
20.1. Create a report of the database objects - WbSchemaReport
20.2. Search source of database objects - WbGrepSource
20.3. Search data in multiple tables - WbGrepData
20.4. Define a script variable - WbVarDef
20.5. Delete a script variable - WbVarDelete
20.6. Show defined script variables - WbVarList
20.7. Confirm script execution - WbConfirm
20.8. Run a stored procedure with OUT parameters - WbCall
20.9. Execute a SQL script - WbInclude (@)
20.10. Extract and run SQL from a Liquibase ChangeLog - WbRunLB
20.11. Handling tables or updateable views without primary keys
20.12. Change the default fetch size - WbFetchSize
20.13. Run statements as a single batch - WbStartBatch, WbEndBatch
20.14. Extracting BLOB content - WbSelectBlob
20.15. Control feedback messages - WbFeedback
20.16. Setting connection properties - SET
20.17. Changing Oracle session behaviour - SET
20.18. Changing read only mode - WbMode
20.19. Generate DROP statement with dependencies - WbGenerateDrop
20.20. Generate DELETE statements with dependencies - WbGenerateDelete
20.21. Generate SQL script for database objects - WbGenerateScript
20.22. Show table structure - DESCRIBE
20.23. List tables - WbList
20.24. List indexes - WbListIndexes
20.25. List stored procedures - WbListProcs
20.26. List triggers - WbListTriggers
20.27. List catalogs - WbListCat
20.28. List schemas - WbListSchemas
20.29. Show the source of a table - WbTableSource
20.30. Show the source of a view - WbViewSource
20.31. Show the source of a stored procedures - WbProcSource
20.32. Count rows for all tables - WbRowcCount
20.33. Change the connection for a script - WbConnect
20.34. Show the history of SQL statements - WbHistory
20.35. Run an XSLT transformation - WbXslt
20.36. Running operating system commands - WbSysExec
20.37. Opening a file with the default application - WbSysOpen
20.38. Using Oracle's DBMS_OUTPUT package
20.39. Define a new macro - WbDefineMacro
20.40. List available macros - WbListMacros
20.41. Show a text to the user - WbEcho
20.42. Change an internal configuration paramter - WbSetConfig
20.43. Conditional execution
21. DataPumper
21.1. Overview
21.2. Selecting source and target connection
21.3. Copying a complete table
21.4. Advanced copy tasks
22. Database Object Explorer
22.1. Objects tab
22.2. Table details
22.3. Modifying the definition of database objects
22.4. Table data
22.5. Changing the display order of table columns
22.6. Customize data retrieval
22.7. Customizing the generation of the table source
22.8. View details
22.9. Procedure tab
22.10. Search table data
23. Common problems
23.1. The driver class was not found
23.2. Syntax error when creating stored procedures
23.3. Timestamps with timezone information are not displayed correctly
23.4. Some of the dialogs are too small
23.5. Excel export not available
23.6. Out of memory errors
23.7. High CPU usage when executing statements
23.8. General DBMS problems
23.9. Oracle Problems
23.10. MySQL Problems
23.11. Microsoft SQL Server Problems
23.12. DB2 Problems
23.13. PostgreSQL Problems
23.14. Sybase SQL Anywhere Problems
24. Options dialog
24.1. General options
24.2. Editor options
24.3. Editor colors
24.4. Font settings
24.5. Auto-completion options
24.6. Workspace options
24.7. Options for displaying data
24.8. Options for formatting data
24.9. Data display colors
24.10. Options for data editing
24.11. DbExplorer options
24.12. Window Title
24.13. SQL Formatting
24.14. SQL Generation
24.15. External tools
24.16. Look and Feel
25. Configuring keyboard shortcuts
25.1. Assign a shortcut to an action
25.2. Removing a shortcut from an action
25.3. Reset to defaults
26. Advanced configuration options
26.1. Database Identifier
26.2. DBID
26.3. GUI related settings
26.4. Editor related settings
26.5. DbExplorer Settings
26.6. General configuration settings
26.7. Database related settings
26.8. DBMS specific settings
26.9. SQL Execution related settings
26.10. Default settings for Export/Import
26.11. Controlling the log file
26.12. Configure Log4J logging
26.13. Settings related to SQL statement generation
26.14. Customize table source retrieval
26.15. Filter settings
Index