Re: Feature Request: SQL parameters

On 3/19/2011 1:52 PM, Pavel Stehule wrote:
> 2011/3/19 Eric Schwarzenbach<subscriber(at)blackbrook(dot)org>:
>> 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.
> There isn't only JDBC - libpq support it.
>
> Pavel
>
Ok, but the use-case I'm talking about is debugging applications using
SQL, in my case an application using JDBC. Maybe I'm misunderstanding
the point of your post. I'm taking it as suggesting I switch to using $n
notation. This isn't an option.

Perhaps you are requesting that if the feature I requested be
implemented it support $n notation also? If so, this sounds like a good
idea. It should support all common SQL parameters notations.

Eric