Alan Humphrey (alan.hum...@comcast.net)

All -

I'm running an program that updates multiple rows in one or more tables.
The target database is MSSQL.  DBIC version is .08010.

The program hums along nicely doing many selects and many updates.  After
about 50 updates it falls over with the following errors (obtained with
DBIC_TRACE on).

Any ideas what's going on?  Suggestions for what I can be looking at?

Thanks!

- Alan

DBI
connect('server=seattleaudubon;database=seattleaudubondb','SASAdmin',...)
failed: (no error string) at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 839
Use of uninitialized value in concatenation (.) or string at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 856.
DBI Connection failed:  at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Schema.pm line 954

DBIx::Class::Schema::throw_exception('BirdWeb::BirdWebSchema=HASH(0x83b8e54)
', 'DBI Connection failed: ') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage.pm line 122

DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Sybase=HAS
H(0x842118c)', 'DBI Connection failed: ') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 856

DBIx::Class::Storage::DBI::_connect('DBIx::Class::Storage::DBI::Sybase=HASH(
0x842118c)', 'DBI:Sybase:server=seattleaudubon;database=seattleaudubondb',
'xxxxx', 'xxxx') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 769

DBIx::Class::Storage::DBI::_populate_dbh('DBIx::Class::Storage::DBI::Sybase=
HASH(0x842118c)') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 611

DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Sybase=HASH(0x
842118c)', 'CODE(0x8a2e54c)', 'update', 'ARRAY(0x8cf72dc)',
'DBIx::Class::ResultSource::Table=HASH(0x885757c)', 'undef',
'HASH(0x825b3bc)', 'HASH(0x8cf7b58)') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 1013

DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::Sybase=HASH(
0x842118c)', 'update', 'ARRAY(0x8cf72dc)',
'DBIx::Class::ResultSource::Table=HASH(0x885757c)', 'undef',
'HASH(0x825b3bc)', 'HASH(0x8cf7b58)') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Storage/DBI.pm line 1085

DBIx::Class::Storage::DBI::update('DBIx::Class::Storage::DBI::Sybase=HASH(0x
842118c)', 'DBIx::Class::ResultSource::Table=HASH(0x885757c)',
'HASH(0x825b3bc)', 'HASH(0x8cf7b58)') called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Row.pm line 298

DBIx::Class::Row::update('BirdWeb::BirdWebSchema::Surveys=HASH(0x8cf7c90)')
called at
/usr/pkg/lib/perl5/site_perl/5.8.0/DBIx/Class/Relationship/CascadeActions.pm
line 30

DBIx::Class::Relationship::CascadeActions::update('BirdWeb::BirdWebSchema::S
urveys=HASH(0x8cf7c90)') called at remove_new.pl line 35

All -

I'm not sure if this is a DBIx issue or a Catalyst issue.  I'm starting with
DBIx based on the error message.  At this point I'm looking for pointers as
to what to go to figure out this problem.

Scenario:  A Catalyst application using DBIx to access a MSSQL database via
FreeTDS.  The database lives on a different server than the web app and
there's a relatively slow connection between the two.  The web app is very
lightly used.  On occasion the error message below will be returned.  It
doesn't always happen at the same place and isn't always in InflateColumn.

I've gone back to the application code and put in more prefetches and this
has made a big difference.  At this point I can't duplicate the problem.
However, I feel that I've just kicked the can down the road and eventually
this will come back.

Any ideas about what I should be looking for?

Many Thanks!

- Alan

DBIx::Class::InflateColumn::get_inflated_column(): DBI Exception:
DBD::Sybase::st execute failed: OpenClient message: LAYER = (0) ORIGIN = (0)
SEVERITY = (78) NUMBER = (51) Server seattleaudubon, database Message
String: Attempt to initiate a new SQL Server operation with results pending.
[for Statement "SELECT me.id, me.map_id, me.default_ecoregion_image_id,
me.ecoregion_name, me.area, me.physiography, me.climate, me.habitats,
me.human_impact FROM ecoregions me WHERE ( ( ( me.id = '1' ) ) )"] at (eval
356) line 6

DBIx::Class::InflateColumn::get_inflated_column(): DBI Exception:
DBD::Sybase::st execute failed: OpenClient message: LAYER = (0) ORIGIN = (0)
SEVERITY = (78) NUMBER = (51) Server seattleaudubon, database Message
String: Attempt to initiate a new SQL Server operation with results pending.
[for Statement "SELECT me.id, me.habitat_description FROM habitat_codes me
WHERE ( ( ( me.id = '4' ) ) )"] at (eval 368) line 6

DBIx::Class::InflateColumn::get_inflated_column(): DBI Exception:
DBD::Sybase::st execute failed: OpenClient message: LAYER = (0) ORIGIN = (0)
SEVERITY = (78) NUMBER = (51) Server seattleaudubon, database Message
String: Attempt to initiate a new SQL Server operation with results pending.
[for Statement "SELECT me.id, me.ISBN, me.title, me.url_id FROM sources me
WHERE ( ( ( me.id = '381' ) ) )"] at (eval 391) line 6

Consider this a testimonial.

Yesterday I had to move a DotNetNuke application from one directory to
another for a client.  Changing directories immediately broke all the
internal links in the app.  Since the links are all stored in the database I
thought I could run a simple SQL update and get them all adjusted.

No such luck.  It turns out that the MSSQL REPLACE function doesn't work
with the data type used by the column in question.  What to do?  Turn to
DBIx::Class of course!

I'm no DBIx guru but within 90 minutes of starting I had a throw away
utility to fix up the DNN application.  Proving I'm no guru, most of that 90
minutes was spent chasing my own errors and looking things up in the docs.
Here are some notes:

- tried to use Loader at first, thinking it would be the fastest way to a
schema definition.  Something in the way the MSSQL tables are defined kept
Loader from finding a primary key on the table, even though it looks like
there is one on the table.  Spun my wheels on that one for a while before
giving up.

- decided to define the table definition by hand.  Went to CPAN and opened
up the Manual.  Found the appropriate section and basically copied the
sample code.

- tried to run the utility.  Weird error.  Back to the docs, this time the
troubleshooting section.  Sure enough, there's my error message.  Oops, I
didn't define my module correctly.  Fixed that.

- At this point the connection to the database is working and I can pull out
data at will.  A couple rounds of testing my change logic before committing
the changes to the database.  Seeing the generated SQL gives me added
confidence that my set up is correct.

- put in an 'update' line, ran the utility, and the database is fixed.


In the end there were three files totaling 27 lines, including blank lines.
The main program is all of 8 lines to connect to the database, extract the
right records, modify them and update.  The supporting files are just
definitions.

This is the second time in a month that I've found DBIx::Class coming in
handy for a simple, throw away, utility.  It certainly makes this kind of
stuff easy.

Solid software and excellent documentation that makes my life easier.
That's why I use DBIx::Class.


- Alan

I'll second that motion.  Jonathan's book is a good intro, but requires too
much reading, then groking the demo application for a
reference/how-to-solve-a-problem book.

I'll second that motion.  Jonathan's book is a good intro, but requires too
much reading, then groking the demo application for a
reference/how-to-solve-a-problem book.