1

I am using a Perl 5.8.3 script to query Oracle 11g database. The connection is established with ODBC Extension for Win32:

http://search.cpan.org/~gsar/libwin32-0.191/ODBC/ODBC.pm

As you can see in the documentation (link), the buffer size is limited to 10K. This size of the buffer is not sufficient in some cases (i.e. too small).

  1. Excerpt from the link above: “The amount of memory that is allocated to retrieve the field data of a record is dynamic and changes when it need to be larger.” I have tested it and the buffer size is not increasing with larger data. It just errors out. Is there any configuration / setup / initialization / parameters passing I need to do? Am I missing something here?
  2. What is the life span of the allocated buffer? Is it throughout the lifetime of the opened connection?
  3. I can increase the buffer size with SetMaxBuffer size up to 2GB. But to what size? Having it too large might put the server stability at risk. One option is to get the row length (the size in bytes for the actual data for all the columns for a record. Not an average) in order for us to allocate sufficient buffer size dynamically. Is there an easy way to do that or I must tally up the sum of all columns? This option will increase the DB trips but in this specific case, it’s not an issue. Related: Oracle - How do I get the actual size of a specific ROW? http://www.dba-oracle.com/t_get_length_of_row.htm

Thanks in advance!

Community
  • 1
  • 1
Rotem Varon
  • 1,597
  • 1
  • 15
  • 32
  • What is the actual error? – Jim Davis Sep 23 '14 at 23:05
  • $db->Sql("SELECT f1, f2, f3 FROM foo"); $db->FetchRow();--- @JimDavis FetchRow will fail (returns false as if no more records are available in the set) when the buffer is smaller than the row being fetched. So not a real error... – Rotem Varon Sep 24 '14 at 02:41
  • 2
    I'm surprised you are not using DBD::ODBC unless that has something to do with the fact you are running a very old perl. DBD::ODBC allocates buffers for row data dynamically but for specifically large columns you have to say how much you want (that is how DBI works). – bohica Sep 24 '14 at 08:16
  • It is a very old Perl script. @bohica see here: http://stackoverflow.com/q/26022754/895667 – Rotem Varon Sep 24 '14 at 17:15
  • With regards to question 1. See section "Data Transfer Buffers" for more info: http://www.roth.net/perl/odbc/docs/ODBC_Docs.htm. Excerpt: "If the buffer is too small to successfully transfer data then it will be transfered in multiple passes." This is not happening. It just fails... – Rotem Varon Sep 24 '14 at 21:39
  • I would be very surprised if you are not better off with DBD::ODBC (but I am the current maintainer so I would say that). WIN32::ODBC is a lower level API but I'd be surprised if WIN32::ODBC had anything much DBI/DBD::ODBC couldn't do and if it was important to you I would look at it. Can I see the script you are currently using (even privately) so I can assess how difficult it would be to replicate in DBI/DBD::ODBC? – bohica Sep 25 '14 at 07:42
  • @bohica Thanks. I appreciate your comment (regardless if you are the maintainer). I am not sure if I can share the script. I do value your desire to help and to that end, I will try to get a special approval ( I am part of the healthcare industry, specifically medical imaging. It might be hard but I'll try anyways!) – Rotem Varon Sep 26 '14 at 02:53
  • @RotemVaron I can promise I would not share with anyone else if that makes any difference. – bohica Sep 26 '14 at 07:31

0 Answers0