Re: Feature Request: SQL parameters

On 3/19/2011 12:21 PM, Pavel Stehule wrote:
> 2011/3/19 Eric Schwarzenbach<subscriber(at)blackbrook(dot)org>:
>> On , Sat, 05 Mar 2011 20:39:53 +0100, Guillaume wrote
>>> Le 03/03/2011 00:03, Eric Schwarzenbach a écrit :
>>>> Some years ago I worked with another DB tool, for another DB that
>>>> offered support for SQL parameters in a way that was extremely handy.
>>>> I've missed this feature in pgAdmin ever since. By SQL parameters, I
>>>> mean the replaceable bits one puts as ? in a prepared statement.
>>>>
>>>> When testing / debugging sql from application code, it is a major
>>>> annoyance to have to constantly find and replace these ?'s. I forget
>>>> exactly how the feature in the other tool worked...it may have actually
>>>> detected the ?'s and prompted you for values the moment you tried to
>>>> execute it. But the important thing was that it allowed you to leave
>>>> the
>>>> ?'s in the query, and gave you a place to fill in the parameter values
>>>> to be used when you execute it. This may not sound like much on the
>>>> surface, but makes an enormous difference. It:
>>>>
>>>> * avoids having to hunt down the ? in a large complex query
>>>> * keeps your query looking more as it appears in your source code (and
>>>> you can more easily paste it back in after you've modified it in
>>>> pgadmin)
>>>> * keeps you from losing track of where the ?s were before you replaced
>>>> them
>>>> * is handier for testing out different values for the parameters.
>>>>
>>> I'm not sure I understand what you would like to have. That pgAdmin asks
>>> for values anytime it encounters a question mark in a query?
>> Sorry if I was a bit vague. I figured there might be a number of UI
>> approaches to handling this, some of which might be easier or harder given
>> pgadmin facilities, and didn't want to my request to get bogged down in Hows
>> rather than Whats, but maybe they are necessary to convey the idea.
>>
>> I'll sketch out a few ways I can see it working:
>>
>> The main feature I'm looking for is a way to let the user supply a list of
>> values to be used to replace the ?'s when executing the query. Now if
>> pgAdmin could detect the ?'s and prompt the user to use this facility, all
>> the better, but this isn't strictly necessary. The user, knowing he has ?'s
>> in his query could manually open the dialog or panel or whatever for
>> entering parameter values (for the sake of argument I'll suppose it is a
>> panel, the "parameter panel". The important thing is being able to enter the
>> values in some more central location rather than hunting through what may be
>> many lines of SQL. The effect should be that the SQL in the editor pane
>> remains unchanged, with the ?'s in place, but when you execute your query
>> PgAdmin replaces them with the values you have entered in the parameter
>> panel.
>>
>> The How of the interface for entering them is probably important for how
>> easy this is in the use case I have in mind (debugging application code
>> SQL), but may be dependent on the tools the user is using. Perhaps several
>> options could be provided. For my own case, debugging Java in Eclipse., I
>> can generally copy the parameters out of the list in the debugger in the
>> form of a comma separated list. So the ability to simply paste in this list
>> would be ideal for me. I don't know how ideal this would be for other users
>> using other debuggers, but perhaps others will give input here.
>>
>> A small feature that would be really sweet, as part of this, would be is
>> pgAdmin were able to highlight a given ? in the SQL when you select a given
>> parameter in the parameter panel.
>>
> using $n notation can be more faster and simpler for implementation -
> and practic, because programmer can check a prepared statements or
> parametrised queries in pgAdmin.
>
> Regards
>
> Pavel Stehule
>
I don't believe this $n notation is supported in JDBC prepared statements.

Eric