Use the SQL Statement builder to create Prepared or Callable SQL statements that are ready for execution.
This builder manages all your Data Modeling Language (DML) and Data Definition Language (DDL) operations. In this builder, you write SQL-based database instructions. For example, you can create stored procedure calls, inserts, deletes, selects, updates, create tables, and create indices.
The following setting groups are available:
A builder name and SQL statement are required for this builder. The SQL you enter must be valid SQL syntax for the database you are targeting and can include any valid SQL character.Statement Parameters
(optional) If the statement you enter has positional parameters indicated by question mark (?) characters, you can provide values for those characters using these settings. When the statement is prepared for execution, the parameters you enter here are picked, defined, and added to the statement. Input parameters typically specify a value used in a query or stored procedure call. Output parameters are used to support the calling of stored procedures and the retrieval of data from their output parameters.Result Set Custom Data Transforms
(optional) These settings are used when a result set contains complex JDBC column types such as ARRAY, BLOB, and STRUCT or you want to apply some custom processing to a column as part of the result set transformation.Result Set Performance Hints
For example, if a result set column contains all uppercase characters, you might create a method to lowercase the column contents as part of the result set transformation into XML.
(optional) These settings allow the builder to create a result set tuned for a particular usage pattern. For example, you might tune a result set for read-only use or a specific fetch direction.Execution Throttles
(optional) These settings allow you to place limits on a statement execution. For example, you might want to do this to constrain the number of rows returned by a query. Using a constraint such as this can prevent overload of network, database, or application resources.Events, Statistics and Logging
(optional) These settings enable you to collect database-related server statistics and work with database-related events.
Enhance SQL readability
Enter your SQL statements using multiple lines to make SQL easier to read.Use Automatic JDBC Type Cast
When you do not know the type of a positional parameter, select Automatic to allow JDBC to determine the type for you.Avoid Escape Characters
Do not use an escape character in front of quotes in SQL statements. For example, do not use: select count(*) \\"Count\\" from sometableDatabase Syntax Issues
Instead use: select count(*) "Count" from mytable
Parent topic: Builder Reference: wpf7
About using the builder call editor: wpf7
About tracking database events: wpf7
Working with a database: wpf7