Re: connect to external DB ( sqlserver ) via JDBC
From: mhoys <matthias.hoys_at_gmail.com>
Date: Wed, 23 Jun 2010 06:38:45 -0700 (PDT)
Message-ID: <826baf70-01a9-42d1-85fd-021b28daa173_at_5g2000yqz.googlegroups.com>
On Jun 22, 9:49 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 22 Jun 2010 08:27:26 -0700, amoroder wrote:
> > Hello,
>
> > I remember that we tested this years ago( DB on windows with ODBC ) ,
> > but don't know where to search for dcumentation today.
>
> > Is it possible to create a link to a non oracle db ( sqlserver or other
> > ) from our oracle 11 DB ?
> > Is the access to the external tables transparent ?
>
> > Thanks
> > Andreas
>
> It is possible. I tested it on Red Hat 5.2 (the current version is 5.5)
> with unixODBC and freeTDS, I was able to connect to SQL Server and select
> data. Mixed transactions are not supported. That, in particular, means
> that you can not push data from Oracle. You can do insert as select, you
> can even create a view, but no mixed transactions.
>
> --http://mgogala.byethost5.com
Date: Wed, 23 Jun 2010 06:38:45 -0700 (PDT)
Message-ID: <826baf70-01a9-42d1-85fd-021b28daa173_at_5g2000yqz.googlegroups.com>
On Jun 22, 9:49 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 22 Jun 2010 08:27:26 -0700, amoroder wrote:
> > Hello,
>
> > I remember that we tested this years ago( DB on windows with ODBC ) ,
> > but don't know where to search for dcumentation today.
>
> > Is it possible to create a link to a non oracle db ( sqlserver or other
> > ) from our oracle 11 DB ?
> > Is the access to the external tables transparent ?
>
> > Thanks
> > Andreas
>
> It is possible. I tested it on Red Hat 5.2 (the current version is 5.5)
> with unixODBC and freeTDS, I was able to connect to SQL Server and select
> data. Mixed transactions are not supported. That, in particular, means
> that you can not push data from Oracle. You can do insert as select, you
> can even create a view, but no mixed transactions.
>
> --http://mgogala.byethost5.com
What do you mean with "you can not push data from Oracle"?
The following works fine in my test setup (executed from Oracle
sqlplus):
SQL> insert into test001_at_mssql01 values(1,'test');
SQL> commit;
This is Oracle 10g on CentOS connected to SQL Server 2008R2 on Windows XP using unixODBC, freeTDS and heterogeneous services. The only "problem" is that as long as you don't commit or rollback in your Oracle session, the SQL Server table apparently remains locked for other users. Don't know if this is "normal" behavior or not.
Matthias Hoys Received on Wed Jun 23 2010 - 08:38:45 CDT