|Don't ask to ask, just ask|
Re: Perl Excel and ODBCby davies (Vicar)
|on Oct 07, 2011 at 15:41 UTC||Need Help??|
Congratulations. You have successfully identified one of the most horrible rat's nests of complexity that Microsoft has ever inflicted on this miserable world. You are likely to catch some flak for not showing us what you have tried, though. In general, it's a good idea to record a macro in Excel and then to try to translate that into decent Perl, bearing in mind that the VBA generated by the macro recorder is not decent VBA.
Getting back to what you want, however, I think it will look like this:
The first two blocks in the code are pretty standard stuff needed to set up a clean instance of Excel. I'm not sure whether Corion's approach will actually do what you want. If you need to embed a query in the Excel spreadsheet so that a user will be able to refresh it without having Perl on his machine, I can't immediately see how it can be done that way. The code above will embed the SQL query so that it can be refreshed provided that the ODBC setup is the same on the target machine. This is not something I know how to control from Perl!
You won't want to embed the user name (UID) and password in the code, but these are the defaults for BugZilla, and if anyone wants to see what I have on my machine, they're welcome as it's currently meaningless.
The real nonsense comes with the CommandText option. Excel requires this to be an array. Passing it as a reference seems to be essential, as if you leave out the backslash, you get nothing unless you use diagnostics, in which case you get a SQL error. Passing the array without the backslash and printing the contents of the CommandText property returns the number of elements.
Nor does the nonsense stop there. While the SQL is effectively unlimited in length, each array element is limited to something close to 240 characters. It's not 255 characters or anything sensible. You might want to write a little loop to check the array element lengths before committing to the query, as with the tail of a few lines knocked off in the wrong places, you might get something seriously unpatriotic happening to your server.
Update: fixed two minor typos