RE: [Openinteract-help] RE: proxying to mssql server

[Chris M. <Chr...@te...>]

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.