DBI fetchrow

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

Hi

I am using Perl DBI to connect to SQl server database, take the contents of a table and dump them into a spreadsheet. I am using fetchrow_array method to read every row of the eable and then write the contents into a spreadsheet. Here's the hitch - when i have few rows in the table, the script works just fine and the output spreadsheet gets created like a chram! But when I have say thousands of rows, the script just creates an empty spreadsheet! Any idea what might be the reason?

I was thinking on lines of the fetch size? Any idea how i can set the fetch size? Is there a limit on how many records can be iterated thru using fetchrow_array?

Thanks

Comment on DBI fetchrow_array

Replies are listed 'Best First'.
Re: DBI fetchrow_array by JavaFan (Canon) on Oct 29, 2010 at 17:22 UTC
Been there, done that. My bet is that you're looking in the wrong direction. Perl DBI can fetch any number of rows, and SQL server is happy to return them. Look at your spreadsheet and/or your spreadsheet writer. Make sure it doesn't have a limit. Spreadsheet::WriteExcel has a limit of 65536 rows (and, IIRC, also a limit on filesize). If you're using Spreadsheet::WriteExcel, you may want to use Spreadsheet::WriteExcel::Big instead.	[reply]
Re: DBI fetchrow_array by halfcountplus (Hermit) on Oct 29, 2010 at 17:01 UTC
Probably a good idea if you include the code you are using to do this. That would -- for example -- allow someone to point out you are missing some appropriate error handling, etc.	[reply]
Re: DBI fetchrow_array by roboticus (Chancellor) on Oct 29, 2010 at 17:13 UTC
biswanath_c: I routinely generate large (a dozen sheets, 10,000+ lines per sheet) spreadsheets with DBI and Spreadsheet::WriteExcel, and haven't found any problems with the modules. I've had the symptoms you describe when something else in the program causes the program to die, though. It hasn't happened in a while, but ISTR that adding an explicit `close()` to the workbook will work around the problem until I discover what the problem actually is. Update: I forgot to answer one of your questions: I don't know of any limit on rows from DBI, but I've successfully pulled down over 100,000 before. (I've probably done a million, but I'm not certain.) ...roboticus	[reply] [d/l]