MSSQL/Page/Prefetch problem

13 messages

MSSQL/Page/Prefetch problem

Reply Threaded

Hi all -

I just upgraded to version 0.08103 and have encountered a problem using page
in conjunction with prefetch against a MSSQL database.

Given:

my $result = $schema->resultset('Surveys')->search(
{},
{ page => 1,
rows => 25,
order_by => ['survey_site_id', 'survey_date'],
prefetch => 'site'
}
)->first();

Executing the code against MSSQL generates this error:

DBI Exception: DBD::Sybase::st execute failed: Server message number=8156
severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' was
specified multiple times for 'foo'. [for Statement " SELECT * FROM
(
SELECT TOP 25 * FROM
(
SELECT TOP 25 me.id, me.survey_site_id, me.start_time, me.end_time,
me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
me.sea_state, me.tide_movement, me.visibility_distance,
me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment,
me.binocular_magnification, me.scope_magnification, me.walker_count,
me.dog_count, me.power_boat_count, me.unpowered_boat_count,
me.other_activities_name, me.other_activities_count, me.comments,
me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect,
me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id,
me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name,
site.position, site.elevation, site.comments FROM surveys me JOIN
survey_sites site ON site.site_code = me.survey_site_id ORDER BY
survey_site_id ASC, survey_date ASC
) AS foo
ORDER BY survey_site_id DESC, survey_date DESC
) AS bar
ORDER BY survey_site_id, survey_date

This used to work. Any ideas? Thanks!

- Alan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

Alan Humphrey wrote:

> Hi all -
>
> I just upgraded to version 0.08103 and have encountered a problem using page
> in conjunction with prefetch against a MSSQL database.
>
> Given:
>
> my $result = $schema->resultset('Surveys')->search(
> {},
> { page => 1,
> rows => 25,
> order_by => ['survey_site_id', 'survey_date'],
> prefetch => 'site'
> }
> )->first();
>
> Executing the code against MSSQL generates this error:
>
> DBI Exception: DBD::Sybase::st execute failed: Server message number=8156
> severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' was
> specified multiple times for 'foo'. [for Statement " SELECT * FROM
> (
> SELECT TOP 25 * FROM
> (
> SELECT TOP 25 me.id, me.survey_site_id, me.start_time, me.end_time,
> me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
> me.sea_state, me.tide_movement, me.visibility_distance,
> me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment,
> me.binocular_magnification, me.scope_magnification, me.walker_count,
> me.dog_count, me.power_boat_count, me.unpowered_boat_count,
> me.other_activities_name, me.other_activities_count, me.comments,
> me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect,
> me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id,
> me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name,
> site.position, site.elevation, site.comments FROM surveys me JOIN
> survey_sites site ON site.site_code = me.survey_site_id ORDER BY
> survey_site_id ASC, survey_date ASC
> ) AS foo
> ORDER BY survey_site_id DESC, survey_date DESC
> ) AS bar
> ORDER BY survey_site_id, survey_date
>
>
> This used to work. Any ideas? Thanks!
>
> - Alan
>

Can you debug the SQL for us, and figure out why it complains about
a column duplicate when there cleare isn't one?

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Marc Mims

Re: MSSQL/Page/Prefetch problem

Reply Threaded

* Peter Rabbitson <[hidden email]> [090529 15:07]:

> Alan Humphrey wrote:
> > Hi all -
> >
> > I just upgraded to version 0.08103 and have encountered a problem using page
> > in conjunction with prefetch against a MSSQL database.
> >
> > Given:
> >
> > my $result = $schema->resultset('Surveys')->search(
> > {},
> > { page => 1,
> > rows => 25,
> > order_by => ['survey_site_id', 'survey_date'],
> > prefetch => 'site'
> > }
> > )->first();
> >
> > Executing the code against MSSQL generates this error:
> >
> > DBI Exception: DBD::Sybase::st execute failed: Server message number=8156
> > severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' was
> > specified multiple times for 'foo'. [for Statement " SELECT * FROM
> > (
> > SELECT TOP 25 * FROM
> > (
> > SELECT TOP 25 me.id, me.survey_site_id, me.start_time, me.end_time,
> > me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
> > me.sea_state, me.tide_movement, me.visibility_distance,
> > me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment,
> > me.binocular_magnification, me.scope_magnification, me.walker_count,
> > me.dog_count, me.power_boat_count, me.unpowered_boat_count,
> > me.other_activities_name, me.other_activities_count, me.comments,
> > me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect,
> > me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id,
> > me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name,
> > site.position, site.elevation, site.comments FROM surveys me JOIN
> > survey_sites site ON site.site_code = me.survey_site_id ORDER BY
> > survey_site_id ASC, survey_date ASC
> > ) AS foo
> > ORDER BY survey_site_id DESC, survey_date DESC
> > ) AS bar
> > ORDER BY survey_site_id, survey_date
> >
> >
> > This used to work. Any ideas? Thanks!
> >
> > - Alan
> >
>
> Can you debug the SQL for us, and figure out why it complains about
> a column duplicate when there cleare isn't one?

There are indeed 2: me.comments and sites.comments. In the outer
selects, they conflict.

-Marc

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Alan Humphrey

RE: MSSQL/Page/Prefetch problem

Reply Threaded

> -----Original Message-----
> From: Marc Mims [mailto:[hidden email]]
> Sent: Friday, May 29, 2009 4:16 PM
> To: DBIx::Class user and developer list
> Cc: DBIx:
> Subject: Re: [Dbix-class] MSSQL/Page/Prefetch problem
>
> * Peter Rabbitson <[hidden email]> [090529 15:07]:
> > Alan Humphrey wrote:
> > > Hi all -
> > >
> > > I just upgraded to version 0.08103 and have encountered a problem
> using page
> > > in conjunction with prefetch against a MSSQL database.
> > >
> > > Given:
> > >
> > > my $result = $schema->resultset('Surveys')->search(
> > > {},
> > > { page => 1,
> > > rows => 25,
> > > order_by => ['survey_site_id', 'survey_date'],
> > > prefetch => 'site'
> > > }
> > > )->first();
> > >
> > > Executing the code against MSSQL generates this error:
> > >
> > > DBI Exception: DBD::Sybase::st execute failed: Server message
> number=8156
> > > severity=16 state=1 line=1 server=SQLA4 text=The column 'comments'
> was
> > > specified multiple times for 'foo'. [for Statement " SELECT * FROM
> > > (
> > > SELECT TOP 25 * FROM
> > > (
> > > SELECT TOP 25 me.id, me.survey_site_id, me.start_time,
> me.end_time,
> > > me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
> > > me.sea_state, me.tide_movement, me.visibility_distance,
> > > me.poor_visibility_reason, me.poor_visibility_reason_other,
> me.equipment,
> > > me.binocular_magnification, me.scope_magnification,
> me.walker_count,
> > > me.dog_count, me.power_boat_count, me.unpowered_boat_count,
> > > me.other_activities_name, me.other_activities_count, me.comments,
> > > me.is_complete, me.raptor1_bird_id, me.raptor1_count,
> me.raptor1_affect,
> > > me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect,
> me.raptor3_bird_id,
> > > me.raptor3_count, me.raptor3_affect, site.site_code,
> site.site_name,
> > > site.position, site.elevation, site.comments FROM surveys me JOIN
> > > survey_sites site ON site.site_code = me.survey_site_id ORDER BY
> > > survey_site_id ASC, survey_date ASC
> > > ) AS foo
> > > ORDER BY survey_site_id DESC, survey_date DESC
> > > ) AS bar
> > > ORDER BY survey_site_id, survey_date
> > >
> > >
> > > This used to work. Any ideas? Thanks!
> > >
> > > - Alan
> > >
> >
> > Can you debug the SQL for us, and figure out why it complains about
> > a column duplicate when there cleare isn't one?
>
> There are indeed 2: me.comments and sites.comments. In the outer
> selects, they conflict.
>
> -Marc

Right. I don't understand why the outer selects are there. The inner
select works fine on its own.

- Alan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

Alan Humphrey wrote:

>> -----Original Message-----
>> From: Marc Mims [mailto:[hidden email]]
>> Sent: Friday, May 29, 2009 4:16 PM
>> To: DBIx::Class user and developer list
>> Cc: DBIx:
>> Subject: Re: [Dbix-class] MSSQL/Page/Prefetch problem
>>
>> * Peter Rabbitson <[hidden email]> [090529 15:07]:
>>> Alan Humphrey wrote:
>>>> Hi all -
>>>>
>>>> I just upgraded to version 0.08103 and have encountered a problem
>> using page
>>>> in conjunction with prefetch against a MSSQL database.
>>>>
>>>> Given:
>>>>
>>>> my $result = $schema->resultset('Surveys')->search(
>>>> {},
>>>> { page => 1,
>>>> rows => 25,
>>>> order_by => ['survey_site_id', 'survey_date'],
>>>> prefetch => 'site'
>>>> }
>>>> )->first();
>>>>
>>>> Executing the code against MSSQL generates this error:
>>>>
>>>> DBI Exception: DBD::Sybase::st execute failed: Server message
>> number=8156
>>>> severity=16 state=1 line=1 server=SQLA4 text=The column 'comments'
>> was
>>>> specified multiple times for 'foo'. [for Statement " SELECT * FROM
>>>> (
>>>> SELECT TOP 25 * FROM
>>>> (
>>>> SELECT TOP 25 me.id, me.survey_site_id, me.start_time,
>> me.end_time,
>>>> me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
>>>> me.sea_state, me.tide_movement, me.visibility_distance,
>>>> me.poor_visibility_reason, me.poor_visibility_reason_other,
>> me.equipment,
>>>> me.binocular_magnification, me.scope_magnification,
>> me.walker_count,
>>>> me.dog_count, me.power_boat_count, me.unpowered_boat_count,
>>>> me.other_activities_name, me.other_activities_count, me.comments,
>>>> me.is_complete, me.raptor1_bird_id, me.raptor1_count,
>> me.raptor1_affect,
>>>> me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect,
>> me.raptor3_bird_id,
>>>> me.raptor3_count, me.raptor3_affect, site.site_code,
>> site.site_name,
>>>> site.position, site.elevation, site.comments FROM surveys me JOIN
>>>> survey_sites site ON site.site_code = me.survey_site_id ORDER BY
>>>> survey_site_id ASC, survey_date ASC
>>>> ) AS foo
>>>> ORDER BY survey_site_id DESC, survey_date DESC
>>>> ) AS bar
>>>> ORDER BY survey_site_id, survey_date
>>>>
>>>>
>>>> This used to work. Any ideas? Thanks!
>>>>
>>>> - Alan
>>>>
>>> Can you debug the SQL for us, and figure out why it complains about
>>> a column duplicate when there cleare isn't one?
>> There are indeed 2: me.comments and sites.comments. In the outer
>> selects, they conflict.
>>
>> -Marc
>
> Right. I don't understand why the outer selects are there. The inner
> select works fine on its own.
>

And what did a previous version generate as far as SQL goes (the one that
used to work)?

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

In reply to this post by Alan Humphrey

Alan Humphrey wrote:

The problem here is twofold, a solution is being worked on, should
emerge around the weekend. Still please provide us with the SQL statement
that did work on 0.08102

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Alan Humphrey

RE: MSSQL/Page/Prefetch problem

Reply Threaded

In reply to this post by Peter Rabbitson-2

> And what did a previous version generate as far as SQL goes (the one
> that
> used to work)?
>

0.08102 generates:

SELECT me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id, survey_date:

Note: The correct number of rows is returned to the application code.

- Alan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

Alan Humphrey wrote:

>> And what did a previous version generate as far as SQL goes (the one
>> that
>> used to work)?
>>
>
> 0.08102 generates:
>
> SELECT me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id, survey_date:
>
> Note: The correct number of rows is returned to the application code.
>

But this does not apply the page => 1/rows => 25 at all does it?

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Alan Humphrey

RE: MSSQL/Page/Prefetch problem

Reply Threaded

> -----Original Message-----
> From: Peter Rabbitson [mailto:[hidden email]]
> Sent: Monday, June 01, 2009 9:44 AM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] MSSQL/Page/Prefetch problem
>
> Alan Humphrey wrote:
> >> And what did a previous version generate as far as SQL goes (the one
> >> that
> >> used to work)?
> >>
> >
> > 0.08102 generates:
> >
> > SELECT me.id, me.survey_site_id, me.start_time, me.end_time,
> me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
> me.sea_state, me.tide_movement, me.visibility_distance,
> me.poor_visibility_reason, me.poor_visibility_reason_other,
> me.equipment, me.binocular_magnification, me.scope_magnification,
> me.walker_count, me.dog_count, me.power_boat_count,
> me.unpowered_boat_count, me.other_activities_name,
> me.other_activities_count, me.comments, me.is_complete,
> me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect,
> me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect,
> me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect,
> site.site_code, site.site_name, site.position, site.elevation,
> site.comments FROM surveys me JOIN survey_sites site ON site.site_code
> = me.survey_site_id ORDER BY survey_site_id, survey_date:
> >
> > Note: The correct number of rows is returned to the application code.
> >
>
> But this does not apply the page => 1/rows => 25 at all does it?

The generated SQL doesn't do any paging, but the correct data set (page, number of rows) is passed back to the application.

- Alan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

In reply to this post by Alan Humphrey

Alan Humphrey wrote:

This has been fixed in trunk. Please try and report any problems.
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Alan Humphrey

RE: MSSQL/Page/Prefetch problem

Reply Threaded

>
> This has been fixed in trunk. Please try and report any problems.
> http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/
>

Getting the same results, see below. Also, getting a failure to install DateTime::Format::Pg:

[MSG] Extracted 'DateTime::Format::Pg' to '/home/alanh/.cpanplus/5.10.0/build/DateTime-Format-Pg-0.16003'
[ERROR] Could not read /home/alanh/.cpanplus/5.10.0/build/DateTime-Format-Pg-0.16003/META.yml: ''

Key 'prereqs' needs to be of type 'HASH' at /usr/pkg/lib/perl5/5.10.0/CPANPLUS/Module.pm line 812
[ERROR] Unable to satisfy 'configure_requires' for 'DateTime::Format::Pg' -- aborting install

[ERROR] Unable to create a new distribution object for 'DateTime::Format::Pg' -- cannot continue

META.yml is there, just empty.

Program output:

~/birdweb/trunk/BirdWeb-Admin > export DBIC_TRACE=1
~/birdweb/trunk/BirdWeb-Admin > perl test.pl
SELECT * FROM
(
SELECT TOP 25 * FROM
(
SELECT TOP 50 me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id ASC, survey_date ASC
) AS foo
ORDER BY survey_site_id DESC, survey_date DESC
) AS bar
ORDER BY survey_site_id, survey_date

:
DBI Exception: DBD::Sybase::st execute failed: Server message number=8156 severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' was specified multiple times for 'foo'. [for Statement " SELECT * FROM
(
SELECT TOP 25 * FROM
(
SELECT TOP 50 me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id ASC, survey_date ASC
) AS foo
ORDER BY survey_site_id DESC, survey_date DESC
) AS bar
ORDER BY survey_site_id, survey_date

"] at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Schema.pm line 1010
DBIx::Class::Schema::throw_exception('BirdWeb::BirdWebSchema=HASH(0x8666570)', 'DBI Exception: DBD::Sybase::st execute failed: Server message...') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage.pm line 122
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'DBI Exception: DBD::Sybase::st execute failed: Server message...') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 722
DBIx::Class::Storage::DBI::__ANON__('DBD::Sybase::st execute failed: Server message number=8156 se...', 'DBI::st=HASH(0x87f4ea0)', undef) called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 978
DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'DBI::db=HASH(0x87cee70)', 'select', undef, 'ARRAY(0x87c6b70)', 'HASH(0x87f4d60)', 'ARRAY(0x8666630)', undef, 'HASH(0x88c7870)', ...) called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 400
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'CODE(0x8783d40)', 'select', undef, 'ARRAY(0x87c6b70)', 'HASH(0x87f4d60)', 'ARRAY(0x8666630)', undef, 'HASH(0x88c7870)', ...) called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm line 21
DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server::_execute('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'select', undef, 'ARRAY(0x87c6b70)', 'HASH(0x87f4d60)', 'ARRAY(0x8666630)', undef, 'HASH(0x88c7870)', 25, ...) called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1195
DBIx::Class::Storage::DBI::_select('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'ARRAY(0x87c6b70)', 'ARRAY(0x8666630)', undef, 'HASH(0x87c6e30)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI/Cursor.pm line 104
DBIx::Class::Storage::DBI::Cursor::_dbh_next('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'DBI::db=HASH(0x87cee70)', 'DBIx::Class::Storage::DBI::Cursor=HASH(0x87c6f00)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 415
eval {...} called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 408
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server=HASH(...', 'CODE(0x86aaba0)', 'DBIx::Class::Storage::DBI::Cursor=HASH(0x87c6f00)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI/Cursor.pm line 123
DBIx::Class::Storage::DBI::Cursor::next('DBIx::Class::Storage::DBI::Cursor=HASH(0x87c6f00)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/ResultSet.pm line 944
DBIx::Class::ResultSet::next('DBIx::Class::ResultSet=HASH(0x87c6c50)') called at test.pl line 30

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

Alan Humphrey wrote:
>> This has been fixed in trunk. Please try and report any problems.
>> http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/
>>
>
>
> Getting the same results, see below. Also, getting a failure to install DateTime::Format::Pg:
>

Just a heads-up - the problem goes deeper than we originally thought,
work is being done at [1]. The fix will probably be finalized in
a week or two. I'll let you know when it's ready to test.

[1] http://dev.catalyst.perl.org/svnweb/bast/browse/DBIx-Class/0.08/branches/mssql_top_fixes/

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Peter Rabbitson-2

Re: MSSQL/Page/Prefetch problem

Reply Threaded

In reply to this post by Alan Humphrey

Alan Humphrey wrote:

>> This has been fixed in trunk. Please try and report any problems.
>> http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/
>>
>
>
> Getting the same results, see below. Also, getting a failure to install DateTime::Format::Pg:
>
> Program output:
>
> ~/birdweb/trunk/BirdWeb-Admin > export DBIC_TRACE=1
> ~/birdweb/trunk/BirdWeb-Admin > perl test.pl
> SELECT * FROM
> (
> SELECT TOP 25 * FROM
> (
> SELECT TOP 50 me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id ASC, survey_date ASC
> ) AS foo
> ORDER BY survey_site_id DESC, survey_date DESC
> ) AS bar
> ORDER BY survey_site_id, survey_date
>
> :
> DBI Exception: DBD::Sybase::st execute failed: Server message number=8156 severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' was specified multiple times for 'foo'. [for Statement " SELECT * FROM
> (
> SELECT TOP 25 * FROM
> (
> SELECT TOP 50 me.id, me.survey_site_id, me.start_time, me.end_time, me.zero_ref_point, me.survey_date, me.weather, me.precipitation, me.sea_state, me.tide_movement, me.visibility_distance, me.poor_visibility_reason, me.poor_visibility_reason_other, me.equipment, me.binocular_magnification, me.scope_magnification, me.walker_count, me.dog_count, me.power_boat_count, me.unpowered_boat_count, me.other_activities_name, me.other_activities_count, me.comments, me.is_complete, me.raptor1_bird_id, me.raptor1_count, me.raptor1_affect, me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, me.raptor3_bird_id, me.raptor3_count, me.raptor3_affect, site.site_code, site.site_name, site.position, site.elevation, site.comments FROM surveys me JOIN survey_sites site ON site.site_code = me.survey_site_id ORDER BY survey_site_id ASC, survey_date ASC
> ) AS foo
> ORDER BY survey_site_id DESC, survey_date DESC
> ) AS bar
> ORDER BY survey_site_id, survey_date
>
> "] at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Schema.pm line 1010

Please try the branch[1]. It does *not* pass its SQL output tests yet, as if the
concept is not right the tests will have to be rewritten. Nevertheless this code
should be final, let us know if it works for you.

[1] http://dev.catalyst.perl.org/svnweb/bast/browse/DBIx-Class/0.08/branches/mssql_top_fixes/

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...