Re: pl/perl autonomous transactions question

On Monday 25 September 2006 15:05, Bob wrote:
> I would like to use autonomous transactions for a large batch process and I
> want this all encapsulated within stored procedures. I want to commit after
> say every 15,000 records. The only way I have found to do this is to use
> the perl DBI in my stored procedure to establish a new connection to the
> database.
>
> 1. Is
> there any way to tell the DBI connection to use the current credtials
> just with a new connection?
>

I guess the depends on where you draw the line between credentials and
connection information.

> 2. Is there any way to get the spi call to create a new connection instead
> of using the connection it is called with?
>

no... that is after all the whole point of spi.

> One issue I see with my current DBI  solution is
> I need to hard code or pass in as variables the connection
> information.  I would prefer not to have the password lying around in
> plain site.  Keep in mind this is a batch process not a something I
> that is called manually where a user is
> going to be entering their username and password in.
>

One option is to use dbi-link, which will store the connection information in
it's own table, so you only pass in a reference to the particular connection
inside your function.  You can then control permissions on this table more
granularly.  Taking that a step farther would be creating specific functions
to handle the posting of the autonomous transactions and then calling those
inside your functions.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL