perlsen has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I have developed a perl script, connecting a sybase database with perl dbi.
and preparing the query to excute the stored procedure based on some values,
after preparation and execution, fetching the result of the stored proc output and processing for some other purpose.
this is working pefectly for nearly first 150 iteartion, after 151 , it is showing the preparation failed for the query.
I had checked the query of the 151 th iteration, the query itself fine while excuting individualy in the sybase db ,
but i could not the get right idea, why it is failing after some 150th iteartion,
Is there any limitation like these many query preparation only allowed in a single db connection?.
can anybody please suggest me an idea for this failing reason?.


Thanks in advance,
perlsen
  • Comment on DBD::Sybase::db prepare failed after nth iteration

Replies are listed 'Best First'.
Re: DBD::Sybase::db prepare failed after nth iteration
by erroneousBollock (Curate) on Nov 05, 2007 at 09:01 UTC
    Is there any limitation like these many query preparation only allowed in a single db connection?
    I doubt DBI has any such restriction.

    can anybody please suggest me an idea for this failing reason?.
    The DBD driver is buggy? The server has a pre-defined chunk of memory available per connection which is exceeded on the 151st run of the stored proc? The server is configured with a hard limit of transactions per connection (as opposed to DBI)? The phase of the moon?

    I'm sure I can't help, but someone perhaps will be able to help if you post your code, and the error message retrieved from DBI.

    Please don't forget to follow the Writeup Formatting Tips.

    -David

[reply]
Re: DBD::Sybase::db prepare failed after nth iteration
by mpeppler (Vicar) on Nov 06, 2007 at 07:30 UTC
    Please post the actual error message that you get.

    My guess at this point is that you aren't processing the results from the proc execution correctly, so something stays allocated and prevents the 151st execution from functioning correctly.

    Michael

[reply]
      Hi Michael,

      Thanks For your above information. Below is the error, what i am getting, while executing the perl script.
      "DBD::Sybase::db prepare failed: OpenClient message: LAYER = (6) ORIGI
+N = (8) SEVERITY = (5) NUMBER = (1) 
Message String: ct_connect(): directory service layer: internal direct
+ory control layer error: There was an error encountered while binding
+ to the directory service. 
Couldn't prepare the query for procedure execution : OpenClient messag
+e: LAYER = (6) ORIGIN = (8) SEVERITY = (5) NUMBER = (1) 
Message String: ct_connect(): directory service layer: internal direct
+ory control layer error: There was an error encountered while binding
+ to the directory service."

      [download]

      And also, this is happened nearly after 150 th excution on one day. the same script when i ran it for next day. it was throwing the same type of error after running thru some 200 th execution.

      then, I have introduced 2 more dbh connection with the existing dbh connection. Now it is not throwing any error and the script works faster compared to the Above one.

      I am not Sure whether these Approach is fine for that problem.

      Please suggest me, if this is the right approach to fix the issue? or Is Anything i need to modify my Script.

      Thanks and Regards
      perlsen
[reply]
[d/l]
        OK - so that means that DBD::Sybase is trying to open a new connection. Is that done on purpose?

        If that's NOT the case then you should make sure that any $sth really go out of scope or are cleaned up before executing the next call. Maybe consider using the syb_no_child_con option to make sure that DBD::Sybase doesn't attempt to open a second connection if there is already an "active" statement handle on the current $dbh.

        Michael

[reply]

Back to Seekers of Perl Wisdom