FAQ
I am a novice Perl DBI programmer currently having trouble referencing
the same
database handle (in Main) from within REPEATED calls to a perl
subroutine I wrote as an engine
to process multiple SQL statements (one call per SQL statement).

(I'm on Solaris 2.6.???, Oracle 8i, Perl5????)

In other words, I get a "db prepare" error when I send the 2nd SQL
statement from Main into
the handle in the subroutine. The first statement executes fine and I
get the results I want
back from the database. It's just that 2nd pass to the same handle that
kills the program.

I was able to fix the problem by passing the 2nd SQL statement through a
separate database
handle (I pass both the database and statement handles to the
subroutine). I have been
playing with the object "finish" but haven't had any luck with it
resolving the problem.

But I thought Oracle DBI database handles were able to process multiple
statement handles??????? I'd really like to use only one database
handle for all
statement handles because I've got a lot of SQL statements to process.
I think it is
ridiculous that I would have to create so many database handles to
access the same database.

Any suggestions would be GREATLY appreciated. I'm sorry I didn't supply
more
detail, but I'm not at my UNIX machine right now and am in a tight pinch
to get this done).

THANKS A LOT for any help. Please reply to both my addresses:
wrendave@earthlink.net
and david.wren@ubs.com

-Dave-

Search Discussions

  • Jeff Urlwin at Feb 2, 2002 at 8:42 pm
    David,

    It would be helpful if you attached a small script which demonstrates the
    error.

    I have had numerous instances using Oracle to have multiple sql statements
    executed and, in fact some executing concurrently. All using the same
    connection.

    Regards,

    Jeff
    I am a novice Perl DBI programmer currently having trouble referencing
    the same
    database handle (in Main) from within REPEATED calls to a perl
    subroutine I wrote as an engine
    to process multiple SQL statements (one call per SQL statement).

    (I'm on Solaris 2.6.???, Oracle 8i, Perl5????)

    In other words, I get a "db prepare" error when I send the 2nd SQL
    statement from Main into
    the handle in the subroutine. The first statement executes fine and I
    get the results I want
    back from the database. It's just that 2nd pass to the same handle that
    kills the program.

    I was able to fix the problem by passing the 2nd SQL statement through a
    separate database
    handle (I pass both the database and statement handles to the
    subroutine). I have been
    playing with the object "finish" but haven't had any luck with it
    resolving the problem.

    But I thought Oracle DBI database handles were able to process multiple
    statement handles??????? I'd really like to use only one database
    handle for all
    statement handles because I've got a lot of SQL statements to process.
    I think it is
    ridiculous that I would have to create so many database handles to
    access the same database.

    Any suggestions would be GREATLY appreciated. I'm sorry I didn't supply
    more
    detail, but I'm not at my UNIX machine right now and am in a tight pinch
    to get this done).

    THANKS A LOT for any help. Please reply to both my addresses:
    wrendave@earthlink.net
    and david.wren@ubs.com

    -Dave-
  • David Wren at Feb 3, 2002 at 6:17 pm
    Jeff,

    Thanks for your reply.

    First of all, the versions of software I'm running are:
    Oracle8i Enterprise Edition Release 8.1.7.2.0
    perl5 (5.0 patchlevel 4 subversion 4)
    Solaris 2.6

    The sample of code which I'm using to test is attached (test.pl), and
    the output
    I get (including an echo of the first SQL, a result status, an echo of
    the
    2nd SQL, and then the error) is as follows:

    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1
    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    DBD::Oracle::db prepare failed: (DBD INVALID_HANDLE:
    OCIStmtPrepare) at test.pl line 86.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 86.

    If I reverse the order of the two SQL statements, to prove that the 2nd
    works, then
    the output is as follows:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    DBD::Oracle::db do failed: (DBD INVALID_HANDLE: OCIStmtPrepare)
    at test.pl line 109.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 109.

    Finally, if I use a completely separate database handle for the 2nd SQL
    statement,
    (attached is the code used for this, called test2.pl) it works:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1

    Thanks for taking a look!

    -Dave-



    -----Original Message-----
    From: jurlwin
    Sent: Saturday, February 02, 2002 3:42 PM
    To: wrendave; dbi-users
    Cc: jurlwin; Tim.Bunce; Wren, David
    Subject: RE: DBI Oracle Database Handle trouble


    David,

    It would be helpful if you attached a small script which demonstrates
    the
    error.

    I have had numerous instances using Oracle to have multiple sql
    statements
    executed and, in fact some executing concurrently. All using the same
    connection.

    Regards,

    Jeff
    I am a novice Perl DBI programmer currently having trouble referencing
    the same
    database handle (in Main) from within REPEATED calls to a perl
    subroutine I wrote as an engine
    to process multiple SQL statements (one call per SQL statement).

    (I'm on Solaris 2.6.???, Oracle 8i, Perl5????)

    In other words, I get a "db prepare" error when I send the 2nd SQL
    statement from Main into
    the handle in the subroutine. The first statement executes fine and I
    get the results I want
    back from the database. It's just that 2nd pass to the same handle that
    kills the program.

    I was able to fix the problem by passing the 2nd SQL statement through a
    separate database
    handle (I pass both the database and statement handles to the
    subroutine). I have been
    playing with the object "finish" but haven't had any luck with it
    resolving the problem.

    But I thought Oracle DBI database handles were able to process multiple
    statement handles??????? I'd really like to use only one database
    handle for all
    statement handles because I've got a lot of SQL statements to process.
    I think it is
    ridiculous that I would have to create so many database handles to
    access the same database.

    Any suggestions would be GREATLY appreciated. I'm sorry I didn't supply
    more
    detail, but I'm not at my UNIX machine right now and am in a tight pinch
    to get this done).

    THANKS A LOT for any help. Please reply to both my addresses:
    wrendave@earthlink.net
    and david.wren@ubs.com

    -Dave-
  • Michael A Chase at Feb 3, 2002 at 7:21 pm
    You are disconnecting from the database handle at the bottom of execsql().
    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
    ----- Original Message -----
    From: <David.Wren@ubsw.com>
    To: <dbi-users@perl.org>; <jurlwin@bellatlantic.net>
    Cc: <Tim.Bunce@pobox.com>
    Sent: Sunday, February 03, 2002 10:13
    Subject: RE: DBI Oracle Database Handle trouble

    First of all, the versions of software I'm running are:
    Oracle8i Enterprise Edition Release 8.1.7.2.0
    perl5 (5.0 patchlevel 4 subversion 4)
    Solaris 2.6

    The sample of code which I'm using to test is attached (test.pl), and
    the output
    I get (including an echo of the first SQL, a result status, an echo of
    the
    2nd SQL, and then the error) is as follows:

    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1
    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    DBD::Oracle::db prepare failed: (DBD INVALID_HANDLE:
    OCIStmtPrepare) at test.pl line 86.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 86.

    If I reverse the order of the two SQL statements, to prove that the 2nd
    works, then
    the output is as follows:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    DBD::Oracle::db do failed: (DBD INVALID_HANDLE: OCIStmtPrepare)
    at test.pl line 109.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 109.

    Finally, if I use a completely separate database handle for the 2nd SQL
    statement,
    (attached is the code used for this, called test2.pl) it works:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1
  • Jeff Urlwin at Feb 3, 2002 at 9:01 pm
    David,

    It turns out to be relatively trivial. Take the disconnect out of the
    execsql subroutine. Pull the disconnect to the main subroutine and I bet it
    will work.

    Jeff
    -----Original Message-----
    From: David.Wren@ubsw.com
    Sent: Sunday, February 03, 2002 1:13 PM
    To: dbi-users@perl.org; jurlwin@bellatlantic.net
    Cc: Tim.Bunce@pobox.com
    Subject: RE: DBI Oracle Database Handle trouble


    Jeff,

    Thanks for your reply.

    First of all, the versions of software I'm running are:
    Oracle8i Enterprise Edition Release 8.1.7.2.0
    perl5 (5.0 patchlevel 4 subversion 4)
    Solaris 2.6

    The sample of code which I'm using to test is attached (test.pl), and
    the output
    I get (including an echo of the first SQL, a result status, an echo of
    the
    2nd SQL, and then the error) is as follows:

    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1
    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    DBD::Oracle::db prepare failed: (DBD INVALID_HANDLE:
    OCIStmtPrepare) at test.pl line 86.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 86.

    If I reverse the order of the two SQL statements, to prove that the 2nd
    works, then
    the output is as follows:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    DBD::Oracle::db do failed: (DBD INVALID_HANDLE: OCIStmtPrepare)
    at test.pl line 109.
    (DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 109.

    Finally, if I use a completely separate database handle for the 2nd SQL
    statement,
    (attached is the code used for this, called test2.pl) it works:

    select trade_date, host_cust_id from cm_trade_fact where trade_id
    = 210960459
    Return Status: 1
    15-MAY-01, WA181862
    BEGIN sp_exec_imm('drop index CMBUS1'); END;
    Return Status: 1

    Thanks for taking a look!

    -Dave-



    -----Original Message-----
    From: jurlwin
    Sent: Saturday, February 02, 2002 3:42 PM
    To: wrendave; dbi-users
    Cc: jurlwin; Tim.Bunce; Wren, David
    Subject: RE: DBI Oracle Database Handle trouble


    David,

    It would be helpful if you attached a small script which demonstrates
    the
    error.

    I have had numerous instances using Oracle to have multiple sql
    statements
    executed and, in fact some executing concurrently. All using the same
    connection.

    Regards,

    Jeff
    I am a novice Perl DBI programmer currently having trouble referencing
    the same
    database handle (in Main) from within REPEATED calls to a perl
    subroutine I wrote as an engine
    to process multiple SQL statements (one call per SQL statement).

    (I'm on Solaris 2.6.???, Oracle 8i, Perl5????)

    In other words, I get a "db prepare" error when I send the 2nd SQL
    statement from Main into
    the handle in the subroutine. The first statement executes fine and I
    get the results I want
    back from the database. It's just that 2nd pass to the same handle that
    kills the program.

    I was able to fix the problem by passing the 2nd SQL statement through a
    separate database
    handle (I pass both the database and statement handles to the
    subroutine). I have been
    playing with the object "finish" but haven't had any luck with it
    resolving the problem.

    But I thought Oracle DBI database handles were able to process multiple
    statement handles??????? I'd really like to use only one database
    handle for all
    statement handles because I've got a lot of SQL statements to process.
    I think it is
    ridiculous that I would have to create so many database handles to
    access the same database.

    Any suggestions would be GREATLY appreciated. I'm sorry I didn't supply
    more
    detail, but I'm not at my UNIX machine right now and am in a tight pinch
    to get this done).

    THANKS A LOT for any help. Please reply to both my addresses:
    wrendave@earthlink.net
    and david.wren@ubs.com

    -Dave-

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedFeb 2, '02 at 7:31p
activeFeb 3, '02 at 9:01p
posts5
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase