Parameter markers (also referred as bind/host variables or place holders) are commonly used in database applications where the SQL is composed of static text combined with values represented as markers instead of actual values. These markers are processed during the preparation of the SQL statement and values are then bound with the markers. Each database has its recommendations for how and when to use parameter markers so this is not further discussed here.
DbVisualizer supports the most common syntaxes for parameter markers to comply with the supported databases. Parameter markers are categorized as either named or unnamed markers. The following sections explains their respective syntaxes.
The following is a sample SQL executed in the SQL Commander:
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES (:EMPLOYEE_ID, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE_NUMBER, :HIRE_DATE, :JOB_ID, :SALARY, :COMMISSION_PCT, :MANAGER_ID, :DEPARTMENT_ID); |
The prompt window will show the markers with their respective names:
For parameter marker processing to work in the SQL Commander, make sure the SQL Commander->Parameterized SQL main menu option is checked.
To apply the values, close the window and continue with the execution, use key binding Ctrl+Enter (Command+Enter on macOS). |
This is the same SQL as used in the Named Parameter Marker section but here question marks are used as markers:
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); |
Since we're using the unnamed marker, ?, the name of each parameter is displayed as Parameter 1, Parameter 2 and so on:
Due to the use of unnamed markers it is not very intuitive what parameter correspond to which part in the statement. The SQL Preview may be handy to get an idea. The Type field is automatically adjusted based on what data is entered for a value. The data type may be manually set by left-click on the type and choose another type from the drop-down.
To apply the values, close the window and continue with the execution, use key binding Ctrl+Enter (Command+Enter on macOS). |
The processing of named and unnamed parameter markers is managed by DbVisualizer. By default there is no data type detection of the target columns identified by the markers and DbVisualizer will initially present these as String in the prompt window. When changing the value for a parameter in the prompt window, a data analyzer is triggered which will automatically detect the type and update the Type field accordingly.
Some drivers (far from all) have the capability to detect the real data type for the referenced columns in the SQL statement. To enable this processing, select the Get Parameter Types via JDBC action in the SQL Commander menu. DbVisualizer will then show the correct types in the prompt window.
Having Get Parameter Types via JDBC enabled while executing may decrease performance substantially as each SQL statement in the script is then pre-processed with the database before the prompt window is displayed. |
The support for parameter markers may differ between databases. Please consult the documentation for the database to see what syntax it supports.
In some situations the database and DbVisualizer support for named parameters might be incompatible. An example is when using the same parameter name in multiple places in the SQL. When preparing a statement towards such a database, the database may report that the parameter is only used once. In these cases, DBVisualizer will trust the driver and revert to generating the names visible in the form as Parameter 1 , Parameter 2 and so on.