0xDBE Parameter Handling...

Hi,

I don't know about many of you but I have a list of sql files broken down into mutations and queries that I use for various day-to-day sundries. One tool I've used in the past is DbVisualizer. This tool is actually quite good. One thing I love about DbVisualizer is the parameter handling. Here is a small snippet of their handling:

${name || value || type || options}$
  • name
    Required. This is the name that appear in the substitution dialog. If multiple variables in a script have the same name, the substitution dialog shows only one and the entered value will be applied to all variables of that name.
  • value
    The default value for the variable
  • type
    The type of variable: String, Boolean, Integer, Float, Long, Double, BigDecimal, Date, Time and Timestamp. In addition DbVisualizer defines: BinaryData and TextData (for CLOB). This is used to determine how the data should be passed between DbVisualizer and the database server. If no type is specified, it is treated as an Integer.
  • options
    The options part is used to express certain conditions:
    • pk
      Indicates that the variable is part of the primary key in the final SQL. Represented with a key icon
    • where
      Defines that the variable is part of the WHERE clause. The green star icon further illustrate this condition
    • noshow
      This option define that the variable should not appear in the substitution dialog. A proper value must be set when using this option, unless it is an output variable (see dir below)
    • nobind
      Specifies that the value should be replaced as text in the final statement instead of being replaced as a parameter marker
    • dir=in | out | inout
      The direction for a variable used with the @call command (it is ignored for other uses). A variable assigned the return value for a function must be declared as dir=out, and a variable used for a procedure parameter must use a dir type matching the procedure parameter direction declaration. in is the default.


I love this. Many of my queries have the following format:

SELECT aName, bAge
FROM aTable
WHERE aStringIndexFields = ${stringIndexFIeld||ABCD||String||where}$;

I will be prompted for the above parameter with ABCD as the default value. Additionally, since this value is marked as a String, I do not have to have the single quotes in the value; DbVisualizer does this for me! Even more cool is that they allow me to pull from previous values that I've used. Other nice UX features include:

  • I dont have to click into the field to edit the value, focus is on the value field.
  • Hitting <enter> will submit the query
  • Hitting <tab> will change to the next parameter (not the next header/label - which is somewhat counter-intuitive)

Jetbrains,

Please use DbVisualizer's handling of parameters. I love 0xDBE because it is based off IntelliJ idea. Darcula is just that awesome.

Everyone else,

What do you think?
3 comments
Comment actions Permalink

Hello Chris,

Thank you for your suggestion. Have you tried using File | Settings | Database | Parameter Patterns? Basically you can define your own pattern for named parameter for your quieries. On the statement execution IDE will show the popup with named parameters:
2015-09-30_15-38-19.png
The parameters should be remembered for the statement and for consequent executions of the statement.

0
Comment actions Permalink

Thank you for the reply!

Yes I use the patterns. In fact, I had to change/remove one of the default ones that contains % because for some of the anonymous stored procedures, i use <cursor>%NOTFOUND and those were resolving to a parameter :)

While the parameter popup in 0xDBE does maintain state between calls, I think it can be improved upon. While I prefere 0xDBE (as a platform) over DBVisualizer, I do sorely miss the strong parameter handling that DBVisualizer has.

With the new licensing coming in November, I will be purchasing the total package license. This will allow me to drop DBVisualizer. In order to do so, I would love get as much feature parity that I can between 0xDBE and DBVisualizer.

0
Comment actions Permalink

Thanks, created request in YouTrack https://youtrack.jetbrains.com/issue/DBE-1611 for the developers to review. Feel free to vote and follow for updates.

0

Please sign in to leave a comment.