Skip to Content.
Sympa Menu

freetds - RE: [freetds] Oracle heterogeneous services

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Зверев Виталий Борисович <VZverev AT genesis.spb.ru>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Oracle heterogeneous services
  • Date: Mon, 26 Apr 2004 00:44:56 +0400

Thanks Gregg.

If I understand right, the "Business Objects" is something like integration
paroduct from company with same name (Nasdaq: BOBJ).
This product has metamodel in your Oracle RDBMS and proactive use required
online access to TDS datasource.
Alternative solution (in contrast with HS) is:
1) map each required TDS object to PL/Sql object type (CREATE TYPE
Ttds_type1 AS OBJECT ( Id NUMBER..... , ))
2) create traversal function, returning required container type (CREATE TYPE
Ttds_type1TABLE IS TABLE OF Ttds_type1)
3) build object view ( create view as select blablabla from table( cast (
TraversalFuncTds_type1() as Ttds_type1TABLE ) ) )
4) use "instead of" trigger for transparent DML
5) use *JDBC* for data access in traversal function (transparently for
"Business Objects" )
6) use NJC (2..5 speed up)
7) forgive me my terrible English
8) be happy :)

BTW, all this steps (except 7&8 ) could be automated...

Sincerely yours.

------------------------------------------------------------
Vitaly Zverev, Oracle Applications DBA,
Genesis Ltd., S.Petersburg, Russia


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Gregg Anderson
Sent: Saturday, April 24, 2004 12:02 AM
To: FreeTDS Development Group
Subject: RE: [freetds] Oracle heterogeneous services


Vitaly,

We are using a reporting application (Business Objects) on Solaris, and up
until this point all of our data has been in Oracle on Solaris. Now we have
a requirement to report on live data on SQL Server as well. Since we don't
have access to the inner workings of Business Objects database connectivity,
and we don't want to have to buy Windows hardware to add Business Objects
instances that can talk to SQL Server, we are trying this option.

We can call stored procedures with Business Objects, but we loose a lot of
functionality if we do that. By the way, Business Objects doesn't support
JDBC connections, and they won't support ODBC on Unix until later this year.

Did you notice a big performance gain going NCJ?

--Gregg

-----Original Message-----
From: VZverev AT genesis.spb.ru [mailto:VZverev AT genesis.spb.ru]
Sent: Friday, April 23, 2004 12:24 PM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] Oracle heterogeneous services


HI Gregg,
BTW, could you please explain why your choice is FreeTDS + HS cross pure
JDBC (to say nothing about Oracle Natively Compiling Java)?
Our choice was Oracle NCJ two years ago (due to performance at first and
native distributed transactions support).

Sincerely yours.

------------------------------------------------------------
Vitaly Zverev, Oracle Applications DBA,
Genesis Ltd., S.Petersburg, Russia


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Gregg Anderson
Sent: Friday, April 23, 2004 9:23 PM
To: FreeTDS Development Group
Subject: RE: [freetds] Oracle heterogeneous services


Hi Vitaly,

Yes you were right on both counts... We were getting those errors because
we hadn't specified a database in the odbc.ini, and we hadn't put double
quotes around the objects.

Thanks again,

Gregg

-----Original Message-----
From: VZverev AT genesis.spb.ru [mailto:VZverev AT genesis.spb.ru]
Sent: Friday, April 23, 2004 10:43 AM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] Oracle heterogeneous services


Hello Gregg.

The "DBMS_HS_PASSTHROUGH" is buildin HS package (additional info and samples
availables in "Heterogeneous Connectivity Administrator's Guide" for 9i
suite or "Distributed Database Systems 8i", chapter 9 "Developing
Applications with Heterogeneous Services"). If DSN's record (from .odbc.ini)
not configured properly we could connect to wrong db. Using
DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@....('use this_db') we set up
required 'this_db' just before the query. I don't know another suitable
method in context of Oracle HS session. Your references about 'table or view
does not exist' give me a clue in that direction.

Sincerely yours.

------------------------------------------------------------
Vitaly Zverev, Oracle Applications DBA,
Genesis Ltd., S.Petersburg, Russia


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Gregg Anderson
Sent: Friday, April 23, 2004 7:15 PM
To: FreeTDS Development Group
Subject: RE: [freetds] Oracle heterogeneous services


Thanks Vitaly. Could you please explain the need for "c:=
DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsodbc('use tempdb');"... We did get
it working without this by using version 0.63 and turning on the development
flag.

--Gregg Anderson

-----Original Message-----
From: VZverev AT genesis.spb.ru [mailto:VZverev AT genesis.spb.ru]
Sent: Friday, April 23, 2004 2:38 AM
To: freetds AT lists.ibiblio.org
Subject: RE: [freetds] Oracle heterogeneous services


Hello Gregg.
Try to use quoted identifiers ("table_name" instead table_name) and set
valid DB with DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE before query:

DECLARE
c INTEGER;
v varchar2(30);
begin
c:= DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsodbc('use tempdb');
select foo into v from "table_name"@hsodbc ;
end;

Unfortunately, generic heterogeneous connectivity service does not support
distributed transactions :(.

Sincerely yours.

------------------------------------------------------------
Vitaly Zverev, Oracle Applications DBA,
Genesis Ltd., S.Petersburg, Russia


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Gregg Anderson
Sent: Thursday, April 22, 2004 7:47 PM
To: freetds AT lists.ibiblio.org
Subject: [freetds] Oracle heterogeneous services


We are trying to connect our Solaris 8 Oracle 9i database to FreeTDS through
unixODBC, and are able to connect to SQLServer2000. However, all our
queries through SQLPlus error out with "table or view does not exist. Both
tsql and isql queries run fine, so we are baffled.

Here is our configuration string... Any help would be greatly appreciated.

locale is "C"
locale charset is "646"
Compile-time settings (established with the "configure" script):
Version: freetds v0.62.3
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: no
iconv library: yes
TDS version: 7.0
iODBC: no
unixodbc: yes

Thanks,

Gregg Anderson
Senior Software Developer

Global Healthcare Exchange
10385 Westmoor Drive, Suite 100
Westminster, CO 80021
720.887.7294
720.887.7200 fax
ganderson AT ghx.com

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page