#539 SQLParam - default values

[Etienne Tasse]

Please add the ability to insert default values and quote mode for parameterized query (SQLParam plugin)

SELECT * FROM EMPLOYEE 
WHERE NAME = :Surname;Smith;quote

[sfst]

Interesting idea.
If I have to do repeated test runs, I normally rewrite the command.

If you shed more light on the reason for the request I might upvote it ;-)

Multiple parameters will need multiple parameter/quote pairs in the correct order:

SELECT * FROM employee
where full_name = :name and hire_date>= :hiredate;Joe Smith;quote;01/jan/2000;quote;

If only one default is given but the SQL command contains 2 parameters, it should ask for the 2nd parameter only?

SELECT * FROM employee
where full_name = :name and hire_date>= :hiredate;Joe Smith;quote;

If the same parameter name is used multiple times, SQuirreL only asks once, so when giving the default values it also only has to be specified once?

SELECT * FROM employee
where full_name = :name or alternate_full_name = :name;Joe Smith;quote;

I wonder whether the modified parsing beyond the semicolon would break things or at least has unintended side-effects.

If the parameters are not used (or partially not used) it will try to execute the extra stuff:

SELECT * FROM employee
where full_name = :name;Joe Smith;quote;01/jan/2000;quote;

Obligatory link: http://xkcd.com/327/ :-)

Maybe hiding the new functionality in a comment might be safer?

--SQUIRREL_META_COMMAND=PARAMETER_VALUE(:name,'Joe Smith',QUOTE);
--SQUIRREL_META_COMMAND=PARAMETER_VALUE(:hire_date,'01/jan/2000',QUOTE);
SELECT * FROM employee
where full_name = :name and hire_date>= :hiredate;

But that method has other potential problems:

The scope only would be for the next SQL command, or should it be defined until it encounters a redefinition?

--SQUIRREL_META_COMMAND=PARAMETER_VALUE(:name,'Joe Smith',QUOTE)
--SQUIRREL_META_COMMAND=PARAMETER_VALUE(:hire_date,'01/jan/2000',QUOTE)
SELECT * FROM employee
where full_name = :name and hire_date>= :hiredate;
DELETE FROM employee
where full_name = :name and hire_date>= :hiredate;

So far I see a lot of potential problems with only very little gain IMO

[Etienne Tasse]

I also use DBVisualizer, which incorporates such a feature. See http://confluence.dbvis.com/display/UG91/Using+DbVisualizer+Variables