Re: Passing a variable from the user interface to PostgreSQL

mladen(dot)gogala(at)vmsinfo(dot)com (Mladen Gogala) writes:
> Jasen Betts wrote:
>> On 2011-01-21, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:
>>
>>> --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_
>>> Content-Type: text/plain; charset="us-ascii"
>>> Content-Transfer-Encoding: quoted-printable
>>>
>>> Using:
>>> Windows 7 64bit
>>> VB.net 2010
>>> The latest devart.data.PostgreSQL (.net data connection)
>>> PostgreSQL 9.0.2
>>>
>>> Hi all,
>>>
>>> Question: Is there a way to pass a variable from an application's user inte=
>>> rface to the database such that it would be visible inside a database trigg=
>>> er and any corresponding function call?
>>>
>>
>>
>> you can embed the extra value in an sql comment and retrieve
>> it with current_query()
>>
>> "-- this is extra value
>> delete from sometable where thiscolumn='thatvalue';"
>>
>> works in 8.4
>>
>>
> Once you have to resort to the parsing of the initial SQL within the
> database trigger, you know that the application design is fubar.
> It's time to think about the new application design.

>;-)

>Stowing it in the table comment is about the worst mechanism I can think
>of... If one is open to going that far astray, it should surely seem
>reasonable to create a table in which to stow this sort of supplementary
>data.

>What *might* be reasonable...

It's always interesting to see how many different ways there are to skin a cat. (Not that I have anything against cats). I appreciate everyone taking the time to respond and offer up ideas. I'm new to dealing with what I call a "real" database like Postgresql. My approach has always been to deal with tasks through programming. Now I have an opportunity to take advantage of the power of a real database and thought that using table triggers to launch processes that wrote to log tables was the correct approach. Perhaps in most cases it is. However, based on the fact that my vision of "easily" getting information from the UI to a trigger spawned database function ended up being just wishful thinking, I believe I'll initiate the log entries out of the UI. I don't believe it was too much of a stretch to think it was a possibility; after all, you can pass parameters through a connection's command object to a database function, why not to a trigger? Heck, if the Seattle Seahawks can win their division with an over-all record of 7-9, then defeat the defending Super Bowl champs, I was ready to believe anything was possible. ;)

Thanks again for your thoughts.

Chris Campbell
Ccampbell(at)Cascadeds(dot)com