Hi
I have started using SQL::Translator on a project where we are using SQL
Server and found that the parser kept falling over on fetching the
schema information from the database handle. I have attached a patch
that fixes this (in lib/SQL/Translator/Parser/DBI/SQLServer.pm). It is
quite straightforward - the original code tried to slurp all columns
with column_info(undef, undef, undef, undef) but the version of SQL
Server we have doesn't like that, so I have rewritten the code to get
all the tables first and call column_info on each table in turn. It now
works for me. If you like I can rework it further so that it tries the
original column_info call in an eval block and then fall back to the new
code if that fails - the original code might be significantly faster if
the version of SQL Server allows it.
Andrew
--
Andrew Ford, Director Pauntley Prints / Ford & Mason Ltd
[EMAIL PROTECTED] South Wing Compton House
pauntley-prints.co.uk Compton Green, Redmarley Tel: +44 1531 829900
ford-mason.co.uk Gloucester GL19 3JB Fax: +44 1531 829901
refcards.com cronolog.org Great Britain Mobile: +44 7785 258278
*** SQLServer.pm.orig 2007-11-15 16:06:54.000000000 +0000
--- SQLServer.pm 2007-11-15 16:44:46.000000000 +0000
***************
*** 64,76 ****
my ($sth, @tables, $columns);
my $stuff;
- ### Columns
! # it is much quicker to slurp back everything all at once rather
! # than make repeated calls
! $sth = $dbh->column_info(undef, undef, undef, undef);
foreach my $c (@{$sth->fetchall_arrayref({})}) {
$columns
--- 64,89 ----
my ($sth, @tables, $columns);
my $stuff;
! ### Tables and columns
! # The original code maintained that it is much quicker to slurp
! # back everything all at once rather than make repeated calls,
! # however some versions of SQL Server object to column_info being
! # called without a table name (the error is "Procedure 'sp_columns' expects
! # parameter '@table_name', which was not supplied.") so this has
! # been recoded to grab the tables first and then iterate over them
! # getting the columns for each table.
+ # Get a list of the tables
+ $sth = $dbh->table_info();
+ @tables = @{$sth->fetchall_arrayref({})};
+
+
+ # and slurp the columns for each table
+
+ foreach my $t (@tables) {
+ $sth = $dbh->column_info($t->{TABLE_CAT}, $t->{TABLE_SCHEM}, $t->{TABLE_NAME}, undef);
foreach my $c (@{$sth->fetchall_arrayref({})}) {
$columns
***************
*** 80,91 ****
->{columns}
->{$c->{COLUMN_NAME}}= $c;
}
! ### Tables and views
! # Get a list of the tables and views.
! $sth = $dbh->table_info();
! @tables = @{$sth->fetchall_arrayref({})};
my $h = $dbh->selectall_arrayref(q{
SELECT o.name, colid,c.text
--- 93,103 ----
->{columns}
->{$c->{COLUMN_NAME}}= $c;
}
+ }
! ### Views
! # Get a list of the views.
my $h = $dbh->selectall_arrayref(q{
SELECT o.name, colid,c.text
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
--
sqlfairy-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
