SQL Workbench/J User's Manual SQLWorkbench

SQLWorkbench-Manual

User Manual:

Open the PDF directly: View PDF .
Page Count: 248 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Scroll down to view the document on your mobile browser.

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.5.1. Specifying the Java runtime for the shell script
- 4.6. Starting the program using the Windows® launcher
  - 4.6.1. Parameters for the Windows® launcher
    - Specifying the Java location
    - Defining the memory for the application
- 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. Connecting through a SSH tunnel
  - 7.7.1. Configure the remote login
  - 7.7.2. Configure the tunnel
- 7.8. Connect to Oracle with SYSDBA privilege
- 7.9. 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.3.1. Control the statement to be executed
    - Statement history
- 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.14.1. Defining a filter manually
  - 12.14.2. Defining a filter from the selection
- 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
  - 12.18.1. Import a file into the current result set
  - 12.18.2. Import the clipboard into the current result
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
- 13.8. Create crosstab (pivot) from a 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.3.1. Editing foreign key values
  - 15.3.2. Selecting FK values when editing DML statements
- 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
  - 24.2.1. Examples
25. SQL Workbench/J to generate DDL commands
- 25.1. Generate SQL script for database objects - WbGenerateScript
- 25.2. Show the source of a table - WbTableSource
- 25.3. Show the source of a view - WbViewSource
- 25.4. Show the source of a stored procedures - WbProcSource
- 25.5. Show the source of a trigger - WbTriggerSource
- 25.6. Generate DROP statement with dependencies - WbGenerateDrop
- 25.7. Generate SQL script for foreign key constraints - WbGenerateFKScript
- 25.8. Generate a table definition from an import file - WbGenerateImpTable
- 25.9. Generate DELETE statements with dependencies - WbGenerateDelete
  - 25.9.1.
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
- 26.9. Show dependency information - WbListDependencies
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.5.1. Examples
- 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.8.1. Define primary key columns - WbDefinePK
  - 29.8.2. List defined primary key columns - WbListPKDef
  - 29.8.3. Load primary key mappings - WbLoadPKMap
  - 29.8.4. Save primary key mappings - WbSavePKMap
- 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.13.1. FEEDBACK
  - 29.13.2. AUTOCOMMIT
  - 29.13.3. MAXROWS
- 29.14. Changing Oracle session behavior - SET
  - 29.14.1. SERVEROUTPUT
  - 29.14.2. AUTOTRACE
- 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.17.1. By specifying a profile
  - 29.17.2. By specifying a simple connection string
  - 29.17.3. By specifying all connection attributes
- 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.3.1. Changing the table definition
  - 31.3.2. Renaming 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
  - 31.10.1. Server side search
  - 31.10.2. Client side search
32. Working with the Database Object tree
- 32.1. Filtering the elements in the tree
- 32.2. Drag and drop support
  - 32.2.1. Dropping elements into the SQL editor
  - 32.2.2. Displaying a table's data
- 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. Amazon Redshift
  - 34.5.1. "ERROR: type "e" does not exist" when using the DbExplorer
- 34.6. PostgreSQL
  - 34.6.1. WbExport or WbCopy using a lot of memory
- 34.7. Sybase SQL Anywhere
  - 34.7.1. Columns with type nvarchar are not displayed properly
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. Backup options
- 35.11. Options for displaying data
  - 35.11.1. Column width settings
  - 35.11.2. Row height settings
- 35.12. Options for formatting data
- 35.13. Data display colors
- 35.14. Options for data editing
- 35.15. DbExplorer options
  - 35.15.1. General options
  - 35.15.2. Object list options
  - 35.15.3. SQL Generation options
  - 35.15.4. Data display options
- 35.16. Window Title
- 35.17. SQL Formatting
- 35.18. External SQL Formatter
- 35.19. SQL Generation
- 35.20. External tools
- 35.21. 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.9.1. Oracle specific settings
  - 37.9.2. Microsoft SQL Server specific settings
- 37.10. SQL Execution related settings
- 37.11. Default settings for Export/Import
- 37.12. Controlling the log file
  - 37.12.1. Configure internal logging
- 37.13. Configure Log4J logging
  - 37.13.1. Turn on 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

SQL Workbench/J User's Manual SQLWorkbench

SQLWorkbench-Manual

SQLWorkbench-Manual

SQLWorkbench-Manual

Navigation menu

Versions of this User Manual:

Views

Navigation