You rock Chris - it's boggling sometimes how helpful you are...
-----Original Message-----
From: Chris Winters [mailto:ch...@cw...]
Sent: December 18, 2002 4:05 PM
To: Chris McDaniel
Cc: ope...@li...
Subject: Re: [Openinteract-help] RE: proxying to mssql server
Chris McDaniel wrote:
> Thanks again - much appreciated! I will look into the JDBC option further
-
> trust perl to have everything!
Just for kicks (and I work with MS SQL a lot) I decided to see
how FreeTDS worked. FYI, I compiled this on Linux and am
connecting to a MS SQL 2000 server:
* Grab the latest CVS sources from FreeTDS (this fixes a
problem when using DBD::Sybase)
* Install FreeTDS with:
./configure --with-tdsver=7.0 --with-ssl \
--prefix=/usr/local/freetds
make
make install
* Download the latest DBD::Sybase (0.95)
* Edit DBD-Sybase-0.95/CONFIG with:
EXTRA_LIBS= -ltds -linsck
(I think for Solaris you need -ltds -ltli)
* set SYBASE=/usr/local/freetds (or whatever the configure
prefix is)
* perl Makefile.PL, make, make install
Now you need to edit /usr/local/freetds/etc/freetds.conf with
your server entry. (They have examples, it's pretty simple.) The
entry name is what you use as the server name in your DBI DSN:
freetds.conf:
[foo]
host=my.server.com
port=1433
DBI connection:
DBI->connect( 'DBI:Sybase:server=foo', ... )
All that works great. But the name/type information doesn't seem
to be retrieved in the same manner ($sth->{NAME}, $sth->{TYPE})
so SPOPS won't work without a little massaging. Namely, you need
to tell SPOPS what the field names and types are. Here's an example:
#!/usr/bin/perl
use strict;
use SPOPS::Initialize;
{
my %config = (
doodad => {
class => 'My::Doodad',
isa => [ 'SPOPS::DBI::Sybase', 'SPOPS::DBI' ],
rules_from => [ 'SPOPS::Tool::DBI::Datasource' ],
field => [ 'doodad_id', 'name', 'action' ],
id_field => 'doodad_id',
increment_field => 'yes',
no_insert => [ 'doodad_id' ],
no_update => [ 'doodad_id' ],
base_table => 'cw_doodad',
dbi_type_info => { doodad_id => 'int',
name => 'char',
action => 'char' },
dbi_config => { dsn =>
'DBI:Sybase:server=foo;database=foo',
username => 'foo',
password => 'foo' },
},
);
SPOPS::Initialize->process({ config => \%config });
My::Doodad->new({ doodad_id => 1,
name => 'foo',
action => 'run' })->save();
My::Doodad->new({ doodad_id => 2,
name => 'bar',
action => 'crawl' })->save();
my $doodads = My::Doodad->fetch_group();
foreach my $doodad ( @{ $doodads } ) {
print Dumper( $doodad );
}
}
The 'dbi_type_info' field is where we need to specify the
name/type mapping since we cannot discover it automatically (yet).
So it would *seem* to work. I haven't tried a full OI install
with this but I'm pretty sure the only sticky point would be
SPOPS, which should be ok with the workaround posted above.
I see that DBD::Sybase supports the DBI column_info method for
retrieving column metadata. I haven't listened in on the DBI
mailing list for a while so I don't know if this is replacing
{NAME}/{TYPE} or what. If so I'll build support into SPOPS.
Have fun!
Chris
--
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.
|
