Re: Feature Request: SQL parameters

2011/3/19 Eric Schwarzenbach <subscriber(at)blackbrook(dot)org>:
> 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.

Probably you can replace $n by ? and back. The advantage of $n is
support by internal SQL parser.

Pavel

>
> Eric
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>