Use of PL/SQL %TYPE and HS - Heterogeneous Connectivity

I have been unable to get a PL/SQL package specification to compile that uses %TYPE against a table that is referenced via a HS link and physically resides in MSSQL 7.
The documentation I looked at (Oracle Transparent Gateway A82868-01 for 8.1.6 and A88789-01 for 9.0.1) does not specifically state if this functionality is supported or not. My database level is 8.1.7.1.5.
I assume that if this is supported then Oracle does some translation of data types at compile time. If it is not suppported then I can remove the use of %TYPE and type the variables using the Raw data type.
My line of code in the package spec is:
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN "rfq"."rfqnum"%TYPE);
I have also tried:
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN rfq.rfqnum%TYPE);
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN "dbo"."rfq"."rfqnum"%TYPE);
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN rfq."rfqnum"%TYPE);
The error I get is:
(1): PLS-00201: identifier 'rfq.rfqnum' must be declared
The rfq reference is actually a Oracle VIEW built on a SELECT * FROM table#HSODBC. I can successfully select data from this table using SQL*Plus.
I am also able to reference this table in my package BODY without a problem.
Any insight much appreciated.
Regards, Charles.

Charles,
Are you using Generic Connectivity or Transparent gateway for SQL Server? 

I am using Generic Connectivity. 

Charles,
We tried it here with Generic Connectivity using Oracle v9.0.1 and it works. It also works with the SQL Server gateway v9.0.1.
Although we have not tested with 8.1.7, I think it will not work.
Will update you once we have tested it. 

Charles,
We tried it here with Generic Connectivity using Oracle v9.0.1 and it works. It also works with the SQL Server gateway v9.0.1.
Although we have not tested with 8.1.7, I think it will not work.
Will update you once we have tested it. 

Charles,
We have tried this with Oracle 8.1.7 and it works. Take a look at the trace file for more information on why it is failing in your environment.

The compilation of the package does not create any trace output.
My trace settings in hs\admin\inithsodbc.ora are:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=mssql
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = hsodbc.trc
I can however successfully run a SELECT against the same VIEW that I am trying to use for %TYPE in my package. This SELECT renders the following trace:
TUESDAY MAY 14 2002 15:33:12.031
(0) hoagprd(2); Entered.
(0) [Generic Connectivity Using ODBC] version: 2.0.4.0.0010
(0) connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=4;PARSER_-
(0) DEPTH=2000;EXEC_FLAGS = 131080;defTdpName=hsodbc;binding=(hsodbc,ODBC,"mssql");
(0) ORACLE GENERIC GATEWAY Log File Started at 14-May-02 15:33:12
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = maximo.
(0) Created new ODBC connection (27792120)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid =BATT.d2ea68f8.3.1.11,
(0) hoagtbid = , tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "RFQ"
(0) odbc_rec: select * from "RFQ"
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."buyercompany" AS c00039, "T0000"."printdate" AS c00038, "T0000"."ldkey" AS c00037, "T0000"."rfq10" AS c00036, "T0000"."rfq9" AS c00035, "T0000"."rfq8" AS c00034, "T0000"."rfq7" AS c00033, "T0000"."rfq6" AS c00032, "T0000"."rfq5" AS c00031, "T0000"."rfq4" AS c00030, "T0000"."rfq3" AS c00029, "T0000"."rfq2" AS c00028, "T0000"."rfq1" AS c00027, "T0000"."historyflag" AS c00026, "T0000"."priority" AS c00025, "T0000"."changedate" AS c00024, "T0000"."changeby" AS c00023, "T0000"."paymentterms" AS c00022, "T0000"."shipvia" AS c00021, "T0000"."freightterms" AS c00020, "T0000"."fob" AS c00019, "T0000"."replytoattn" AS c00018, "T0000"."replyto" AS c00017, "T0000"."billtoattn" AS c00016, "T0000"."billto" AS c00015, "T0000"."shiptoattn" AS c00014, "T0000"."shipto" AS c00013, "T0000"."requestedby" AS c00012, "T0000"."requireddate" AS c00011, "T0000"."rfqtype" AS c00010, "T0000"."purchaseagent" AS c0009, "T0000"."closeondate" AS c0008, "T0000"."replydate" AS c0007, "T0000"."enterby" AS c0006, "T0000"."enterdate" AS c0005, "T0000"."statusdate" AS c0004, "T0000"."status" AS c0003, "T0000"."description" AS c0002, "T0000"."rfqnum" AS c0001, "T0000"."rowstamp" AS c0000 FROM "RFQ" T0000
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 932):
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "RFQ"
(0)
(0)
(0) Accessing Database "hsodbc" with SQL:
(0) SELECT "T0000"."buyercompany" AS c00039, "T0000"."printdate" AS c00038, "T0000"."ldkey" AS c00037, "T0000"."rfq10" AS c00036, "T0000"."rfq9" AS c00035, "T0000"."rfq8" AS c00034, "T0000"."rfq7" AS c00033, "T0000"."rfq6" AS c00032, "T0000"."rfq5" AS c00031, "T0000"."rfq4" AS c00030, "T0000"."rfq3" AS c00029, "T0000"."rfq2" AS c00028, "T0000"."rfq1" AS c00027, "T0000"."historyflag" AS c00026, "T0000"."priority" AS c00025, "T0000"."changedate" AS c00024, "T0000"."changeby" AS c00023, "T0000"."paymentterms" AS c00022, "T0000"."shipvia" AS c00021, "T0000"."freightterms" AS c00020, "T0000"."fob" AS c00019, "T0000"."replytoattn" AS c00018, "T0000"."replyto" AS c00017, "T0000"."billtoattn" AS c00016, "T0000"."billto" AS c00015, "T0000"."shiptoattn" AS c00014, "T0000"."shipto" AS c00013, "T0000"."requestedby" AS c00012, "T0000"."requireddate" AS c00011, "T0000"."rfqtype" AS c00010, "T0000"."purchaseagent" AS c0009, "T0000"."closeondate" AS c0008, "T0000"."replydate" AS c0007, "T0000"."enterby" AS c0006, "T0000"."enterdate" AS c0005, "T0000"."statusdate" AS c0004, "T0000"."status" AS c0003, "T0000"."description" AS c0002, "T0000"."rfqnum" AS c0001, "T0000"."rowstamp" AS c0000 FROM "RFQ" T0000
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt(195); Array fetch size is: 1.
(0) ------ hoadscr() -------:
(0) hoadamsz: 40, hoadasiz: 40, hoadambr: 1, hoadabrc: 1
(0) row 0 - hoadambl: 0, hoadadty: 0, hoadaprc: 0, hoadacst: 0
(0) row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 8, hoadanam: rowstamp, hoadabfl:
(0) 0, hoadamod: 0
(0) row 1 - hoadambl: 8, hoadadty: 108, hoadaprc: 8, hoadacst: 0
(0) row 1 - hoadascl: 0, hoadanul: 0, hoadanml: 6, hoadanam: rfqnum, hoadabfl: 8,
(0) hoadamod: 0
(0) row 2 - hoadambl: 50, hoadadty: 108, hoadaprc: 50, hoadacst: 0
(0) row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: description,
(0) hoadabfl: 50, hoadamod: 0
(0) row 3 - hoadambl: 6, hoadadty: 108, hoadaprc: 6, hoadacst: 0
(0) row 3 - hoadascl: 0, hoadanul: 0, hoadanml: 6, hoadanam: status, hoadabfl: 6,
(0) hoadamod: 0
(0) row 4 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 4 - hoadascl: 0, hoadanul: 0, hoadanml: 10, hoadanam: statusdate,
(0) hoadabfl: 7, hoadamod: 0
(0) row 5 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: enterdate, hoadabfl:
(0) apiutil; row 39 - cbDataOffset: 839, dwBinding: 1, width: 18, scale: 0
(0) hoaftch(21); Exited with retcode = 1403, hoadabrc = 0.
(0) hoaclse(22); Entered. id = 1.
(0) hoaclse(22); Exited with retcode = 0.
(0) hoadafr(23); Entered. id = 1.
(0) hoadafr(23); Exited with retcode = 0.
(0) hoacomm(11); Entered. keepinfo = FALSE, tflag = 1.
(0) hoacomm(11); Exited with retcode = 0.
Regards, Charles.

Related

Record count not matching when using HS while querying.

Hello,
We are using 10gr2.
I created a Database link connecting to lotus notes using HETROGENEOUS SERVICES and ODBC.
When we query the table in Lotus Notes using database link oracle is showing less records.
For example.
Lotus Notes database have a table which contains 873 records.
When we query this table from oracle we are getting the count of 722 records.
Any ideas why this is happening.
Thank you for the help.
Regards
Seshadri Thope 
Make sure you have the latest patchset applied (10.2.0.4) and what about a select count(*) from <table>#<db link>?
Does this select report the correct amount of data? 
We are on Latest Patch.
No. Select COUNT(*) is also not returning correct number. 
Enable gateway tracing by setting in your int<hsodbc>.ora file HS_FDS_TRACE_LEVEL=DEBUG
Then open a new SQL*Plus session and run the select count(*) from table#db link.
Afterwards open the trace and look for the select statement being parsed and copy/paste the relevant trace file info into the forum.
(Do not forget to disable tracing afterwards). 
Oracle Corporation --- THURSDAY JAN 29 2009 10:34:23.786
Heterogeneous Agent Release
10.2.0.4.0
(0) hoagprd (2): ; hoagprd Entered.
(0) HOACONN.C (270): ; [Generic Connectivity Using ODBC] version: 10.2.0.0.0080
(0) HOACONN.C (323): ; Class version: 300
(0) hoagprd (2): ; hoagprd Exited with retcode = 0.
(0) hoainit (3): ; hoainit Entered.
(0) (0): ; connect string is: defTdpName=DATABASEA;SYNTAX=(ORACLE8_HOA, BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="", CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasources><datasource name='DATABASEA' type='ODBC' connect='socrates'><driverProperties disableExtendedFetch='true'/></datasource></datasources><remoteMachines/><environment><optimizer noFlattener='true'/><misc year2000Policy='-1' consumerApi='1' codepage='WE8ISO8859P15' sessionBehavior='4'/><queryProcessor parserDepth='2000' tokenSize='1000' noInsertParameterization='true'
noThreadedReadAhead='true' noCommandReuse='true'/><debug generalTrace='true'/></environment></binding></navobj>
(0) ORACLE GENERIC GATEWAY Log File Started at 2009-01-29T10:34:24
(0)
(0) hoainit (3): ; hoainit Exited with retcode = 0.
(0) hoalgon (7): ; hoalgon Entered. name = .
(0) Created new ODBC connection (32056720)
(0) hoalgon (7): ; hoalgon Exited with retcode = 0.
(0) hoaulcp (4): ; hoaulcp Entered.
(0) hoaulcp (4): ; hoaulcp Exited with retcode = 0.
(0) hoauldt (5): ; hoauldt Entered.
(0) hoauldt (5): ; hoauldt Exited with retcode = 0.
(0) hoabegn (9): ; hoabegn Entered. formatID = 306206, hoagttln = 38, hoagttid = DATABASEA.e53cf895.2.35.27286, hoagtbln = 10, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn (9): ; hoabegn Exited with retcode = 0.
(0) hoadtab (26): ; hoadtab Entered. count = 1
(0) hoadtab (26): ; schema_name = , table_name = TABLEA
(0) odbc_rec: select * from "TABLEA"
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('DATABASEA', '%', 'TABLEA', 1, 0, 0) order by 3, 1, 2, 4
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'DATABASEA' , '%' , 'TABLEA' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
(0)
(0)
(0) Accessing saved query spec SP_STATISTICS()
(0) from NAV_PROC DB
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) HOAUTIL.C (1284): ; ------ hoadtab (hoat) -------:
(0) HOAUTIL.C (1287): ; hoatnam: TABLEA, hoatnml: 20, hoatnrw: 711, hoatarl: 110000
(0) HOAUTIL.C (1298): ; ------ hoadtab (hoai) -------:
(0) HOAUTIL.C (1299): ; n_index_stat: 0
(0) HOAUTIL.C (1249): ; -------- hoadtab for table TABLEA---------:
(0) HOAUTIL.C (1250): ; hoadamsz: 22, hoadasiz: 22, hoadambr: 1, hoadabrc: 0, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: Subject, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 1 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: DateCreated, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 2 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: CustomerFacingUnit, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 3 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 12, hoadanam: OFFERNUMBERS, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 4 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 13, hoadanam: textedPricing, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 5 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 22, hoadanam: textedShortDescription, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 6 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 21, hoadanam: textedLongDescription, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 7 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 7 - hoadascl: 0, hoadanul: 1, hoadanml: 14, hoadanam: textedBenefits, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 8 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 8 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: textedAboutAuthors, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 9 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 9 - hoadascl: 0, hoadanul: 1, hoadanml: 13, hoadanam: textedContent, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 10 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 10 - hoadascl: 0, hoadanul: 1, hoadanml: 23, hoadanam: textedDiffByMedia_Print, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 11 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 11 - hoadascl: 0, hoadanul: 1, hoadanml: 20, hoadanam: textedDiffByMedia_CD, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 12 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 12 - hoadascl: 0, hoadanul: 1, hoadanml: 26, hoadanam: textedDiffByMedia_Internet, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 13 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 13 - hoadascl: 0, hoadanul: 1, hoadanml: 23, hoadanam: textedDiffByMedia_Other, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 14 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 14 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: BookStatus, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 15 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 15 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: Published, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 16 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 16 - hoadascl: 0, hoadanul: 1, hoadanml: 4, hoadanam: ISBN, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 17 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 17 - hoadascl: 0, hoadanul: 1, hoadanml: 5, hoadanam: Pages, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 18 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 18 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: Binding, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 19 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 19 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: ProductSize, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 20 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 20 - hoadascl: 0, hoadanul: 1, hoadanml: 12, hoadanam: DateModified, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 21 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 21 - hoadascl: 0, hoadanul: 1, hoadanml: 15, hoadanam: ProductItemCode, hoadabfl: 4000, hoadamod: 0
(0) hoadtab (26): ; hoadtab Exited with retcode = 0.
(0) hoadafr (23): ; hoadafr Entered. id = 0.
(0) hoadafr (23): ; hoadafr Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): SELECT COUNT(*) FROM "TABLEA" A1
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT COUNT ( * ) FROM "TABLEA" A1
(0)
(0)
(0) Accessing Database 'DATABASEA' with SQL:
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars (15): ; hoapars Exited with retcode = 0.
(0) hoaopen (19): ; hoaopen Entered. id = 1.
(0) hoaopen (19): ; hoaopen Exited with retcode = 0.
(0) hoadscr (16): ; hoadscr Entered. id = 1.
(0) hoastmt.c (304): ; Array fetch size is: 1.
(0) HOAUTIL.C (1249): ; ------ hoadscr() -------:
(0) HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: $$CALC_1, hoadabfl: 4, hoadamod: 0
(0) hoadscr (16): ; hoadscr Exited with retcode = 0.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) apiutil.c (0): ; hoaftch()::
(0) apiutil.c (0): ; Column 0 - pszName: $$CALC_1, size: 4, id: 8, width: 0, scale: 0, flags: 12.
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
(0) hoaclse (22): ; hoaclse Entered. id = 1.
(0) hoaclse (22): ; hoaclse Exited with retcode = 0.
(0) hoadafr (23): ; hoadafr Entered. id = 1.
(0) hoadafr (23): ; hoadafr Exited with retcode = 0.
(0) hoacomm (11): ; hoacomm Entered. keepinfo = FALSE, tflag = 1.
(0) hoacomm (11): ; hoacomm Exited with retcode = 0.
(0) hoalgof (8): ; hoalgof Entered. tflag = 1.
(0) hoalgof (8): ; hoalgof Exited with retcode = 0.
(0) hoaexit (6): ; hoaexit Entered.
(0) hoaexit (6): ; hoaexit Exited with retcode = 0.
(0) (0): ; Closing log file at THU JAN 29 10:38:55 2009. 
The trace clearly shows:
SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
So HSODBC sends this command to the foreign database and the foreign database is processing the result.
Now refering back to your update:
"Re: Record count not matching when using HS while querying.
Posted: 28.01.2009 08:36 in response to: kgronau Reply
...
No. Select COUNT(*) is also not returning correct number.
"
So the problem is related to your foreign database as HSODBC sends a count(*) to your foreign database
it retrieves one record:
0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
(1403 is always raised when no more data needs to be fetched)
and a wrong result is coming back.
SIDE NOTE:
I've seen problems that a select count(*) is returning wrong results as the foreign database was choosing a column containing nulls and it didn't count the nulls as a record. Once specifying a coulm name with a primary key in the count function it was returning the correct result. 
We tried this option too.
We created a view which contains only those columns that contains data for all records. When we query the view record count is 711 but view contains 892 records.
Here is the trace file. Any help is appreciated.
Oracle Corporation --- MONDAY FEB 02 2009 16:05:17.054
Heterogeneous Agent Release
10.2.0.4.0
(0) hoagprd (2): ; hoagprd Entered.
(0) HOACONN.C (270): ; [Generic Connectivity Using ODBC] version: 10.2.0.0.0080
(0) HOACONN.C (323): ; Class version: 300
(0) hoagprd (2): ; hoagprd Exited with retcode = 0.
(0) hoainit (3): ; hoainit Entered.
(0) (0): ; connect string is: defTdpName=DATABASEA;SYNTAX=(ORACLE8_HOA, BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="", CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasources><datasource name='DATABASEA' type='ODBC' connect='socrates'><driverProperties/></datasource></datasources><remoteMachines/><environment><optimizer noFlattener='true'/><misc year2000Policy='-1' consumerApi='1' codepage='WE8ISO8859P15' sessionBehavior='4'/><queryProcessor parserDepth='2000' tokenSize='1000' noInsertParameterization='true'
noThreadedReadAhead='true' noCommandReuse='true'/><debug generalTrace='true'/></environment></binding></navobj>
(0) ORACLE GENERIC GATEWAY Log File Started at 2009-02-02T16:05:17
(0)
(0) hoainit (3): ; hoainit Exited with retcode = 0.
(0) hoalgon (7): ; hoalgon Entered. name = .
(0) Created new ODBC connection (32056720)
(0) hoalgon (7): ; hoalgon Exited with retcode = 0.
(0) hoaulcp (4): ; hoaulcp Entered.
(0) hoaulcp (4): ; hoaulcp Exited with retcode = 0.
(0) hoauldt (5): ; hoauldt Entered.
(0) hoauldt (5): ; hoauldt Exited with retcode = 0.
(0) hoabegn (9): ; hoabegn Entered. formatID = 306206, hoagttln = 38, hoagttid = DATABASEB .e53cf895.11.19.7964, hoagtbln = 10, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn (9): ; hoabegn Exited with retcode = 0.
(0) hoadtab (26): ; hoadtab Entered. count = 1
(0) hoadtab (26): ; schema_name = , table_name = PRODUCTDATA_DATABASEA
(0) odbc_rec: select * from "PRODUCTDATA_DATABASEA"
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('DATABASEA', '%', 'PRODUCTDATA_DATABASEA', 1, 0, 0) order by 3, 1, 2, 4
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'DATABASEA' , '%' , 'PRODUCTDATA_DATABASEA' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
(0)
(0)
(0) Accessing saved query spec SP_STATISTICS()
(0) from NAV_PROC DB
(0)
Execution Strategy End
(0) HOAUTIL.C (1284): ; ------ hoadtab (hoat) -------:
(0) HOAUTIL.C (1287): ; hoatnam: PRODUCTDATA_DATABASEA, hoatnml: 20, hoatnrw: 711, hoatarl: 15360
(0) HOAUTIL.C (1298): ; ------ hoadtab (hoai) -------:
(0) HOAUTIL.C (1299): ; n_index_stat: 0
(0) HOAUTIL.C (1249): ; -------- hoadtab for table PRODUCTDATA_DATABASEA---------:
(0) HOAUTIL.C (1250): ; hoadamsz: 2, hoadasiz: 2, hoadambr: 1, hoadabrc: 0, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: Subject, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 1 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: DateCreated, hoadabfl: 4000, hoadamod: 0
(0) hoadtab (26): ; hoadtab Exited with retcode = 0.
(0) hoadafr (23): ; hoadafr Entered. id = 0.
(0) hoadafr (23): ; hoadafr Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): SELECT COUNT(*) FROM "PRODUCTDATA_DATABASEA" A1
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) SELECT COUNT(*) AS c000 FROM "PRODUCTDATA_DATABASEA" "A1"
(0)
(0) Execution Strategy Begin
(0) Original SQL:
(0) SELECT COUNT ( * ) FROM "PRODUCTDATA_DATABASEA" A1
(0)
(0)
(0) Accessing Database 'DATABASEA' with SQL:
(0) SELECT COUNT(*) AS c000 FROM "PRODUCTDATA_DATABASEA" "A1"
(0)
(0)
Execution Strategy End
(0) hoapars (15): ; hoapars Exited with retcode = 0.
(0) hoaopen (19): ; hoaopen Entered. id = 1.
(0) hoaopen (19): ; hoaopen Exited with retcode = 0.
(0) hoadscr (16): ; hoadscr Entered. id = 1.
(0) hoastmt.c (304): ; Array fetch size is: 1.
(0) HOAUTIL.C (1249): ; ------ hoadscr() -------:
(0) HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: $$CALC_1, hoadabfl: 4, hoadamod: 0
(0) hoadscr (16): ; hoadscr Exited with retcode = 0.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) apiutil.c (0): ; hoaftch()::
(0) apiutil.c (0): ; Column 0 - pszName: $$CALC_1, size: 4, id: 8, width: 0, scale: 0, flags: 12.
(0) SELECT COUNT(*) AS c000 FROM "PRODUCTDATA_DATABASEA" "A1"
(0)
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
You can see in the trace a cun(*) is again sent to the remote database and HSODBC only gets back the result.
Are there differences in:
select count(*) from table#<hsodbc>,
select count(<column>) from table#<hsodbc>
and running also both selects in a native ODBC application? 
When we do
Select count(*) from Table and Select count(Column) from table are returning same results.
Trace File information is posted below.
We are using the view which is based on the another table in Lotus Notes.
When we query the table we are getting different record count(714). But in Lotus Notes that table contains around 842 records.
Thank You,
Seshadri
Oracle Corporation --- TUESDAY FEB 03 2009 09:23:33.845
Heterogeneous Agent Release
10.2.0.4.0
(0) hoagprd (2): ; hoagprd Entered.
(0) HOACONN.C (270): ; [Generic Connectivity Using ODBC] version: 10.2.0.0.0080
(0) HOACONN.C (323): ; Class version: 300
(0) hoagprd (2): ; hoagprd Exited with retcode = 0.
(0) hoainit (3): ; hoainit Entered.
(0) (0): ; connect string is: defTdpName=DATABASEB;SYNTAX=(ORACLE8_HOA, BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="", CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasources><datasource name='DATABASEB' type='ODBC' connect='socrates'><driverProperties disableExtendedFetch='true'/></datasource></datasources><remoteMachines/><environment><optimizer noFlattener='true'/><misc year2000Policy='-1' consumerApi='1' codepage='WE8ISO8859P15' sessionBehavior='4'/><queryProcessor parserDepth='2000' tokenSize='1000' noInsertParameterization='true'
noThreadedReadAhead='true' noCommandReuse='true'/><debug generalTrace='true'/></environment></binding></navobj>
(0) ORACLE GENERIC GATEWAY Log File Started at 2009-02-03T09:23:33
(0)
(0) hoainit (3): ; hoainit Exited with retcode = 0.
(0) hoalgon (7): ; hoalgon Entered. name = .
(0) Created new ODBC connection (32056048)
(0) hoalgon (7): ; hoalgon Exited with retcode = 0.
(0) hoaulcp (4): ; hoaulcp Entered.
(0) hoaulcp (4): ; hoaulcp Exited with retcode = 0.
(0) hoauldt (5): ; hoauldt Entered.
(0) hoauldt (5): ; hoauldt Exited with retcode = 0.
(0) hoabegn (9): ; hoabegn Entered. formatID = 306206, hoagttln = 38, hoagttid = DATABASEA.e53cf895.2.40.27492, hoagtbln = 10, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn (9): ; hoabegn Exited with retcode = 0.
(0) hoadtab (26): ; hoadtab Entered. count = 1
(0) hoadtab (26): ; schema_name = , table_name = TABLEA
(0) odbc_rec: select * from "TABLEA"
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('DATABASEB', '%', 'TABLEA', 1, 0, 0) order by 3, 1, 2, 4
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) Execution Strategy Begin
(0) Original SQL:
(0) select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'DATABASEB' , '%' , 'TABLEA' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
(0)
(0)
(0) Accessing saved query spec SP_STATISTICS()
(0) from NAV_PROC DB
(0)
Execution Strategy End
(0) HOAUTIL.C (1284): ; ------ hoadtab (hoat) -------:
(0) HOAUTIL.C (1287): ; hoatnam: TABLEA, hoatnml: 20, hoatnrw: 711, hoatarl: 15360
(0) HOAUTIL.C (1298): ; ------ hoadtab (hoai) -------:
(0) HOAUTIL.C (1299): ; n_index_stat: 0
(0) HOAUTIL.C (1249): ; -------- hoadtab for table TABLEA---------:
(0) HOAUTIL.C (1250): ; hoadamsz: 2, hoadasiz: 2, hoadambr: 1, hoadabrc: 0, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: Subject, hoadabfl: 4000, hoadamod: 0
(0) HOAUTIL.C (1253): ; row 1 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: DateCreated, hoadabfl: 4000, hoadamod: 0
(0) hoadtab (26): ; hoadtab Exited with retcode = 0.
(0) hoadafr (23): ; hoadafr Entered. id = 0.
(0) hoadafr (23): ; hoadafr Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): SELECT COUNT(*) FROM "TABLEA" A1
(0) nvRETURN (D:\Work_Builds\connect_5010\src\qp\qpsynon.c 1171): -1
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0) Execution Strategy Begin
(0) Original SQL:
(0) SELECT COUNT ( * ) FROM "TABLEA" A1
(0)
(0)
(0) Accessing Database 'DATABASEB' with SQL:
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0)
Execution Strategy End
(0) hoapars (15): ; hoapars Exited with retcode = 0.
(0) hoaopen (19): ; hoaopen Entered. id = 1.
(0) hoaopen (19): ; hoaopen Exited with retcode = 0.
(0) hoadscr (16): ; hoadscr Entered. id = 1.
(0) hoastmt.c (304): ; Array fetch size is: 1.
(0) HOAUTIL.C (1249): ; ------ hoadscr() -------:
(0) HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: $$CALC_1, hoadabfl: 4, hoadamod: 0
(0) hoadscr (16): ; hoadscr Exited with retcode = 0.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) apiutil.c (0): ; hoaftch()::
(0) apiutil.c (0): ; Column 0 - pszName: $$CALC_1, size: 4, id: 8, width: 0, scale: 0, flags: 12.
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
(0) hoaclse (22): ; hoaclse Entered. id = 1.
(0) hoaclse (22): ; hoaclse Exited with retcode = 0.
(0) hoadafr (23): ; hoadafr Entered. id = 1.
(0) hoadafr (23): ; hoadafr Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): SELECT "Subject" FROM "TABLEA"
(0) SELECT "T0000"."Subject" AS c000 FROM "TABLEA" "T0000"
(0)
(0) Execution Strategy Begin
(0) Original SQL:
(0) SELECT "Subject" FROM "TABLEA"
(0)
(0)
(0) Accessing Database 'DATABASEB' with SQL:
(0) SELECT "T0000"."Subject" AS c000 FROM "TABLEA" "T0000"
(0)
(0)
Execution Strategy End
(0) hoapars (15): ; hoapars Exited with retcode = 0.
(0) hoaopen (19): ; hoaopen Entered. id = 1.
(0) hoaopen (19): ; hoaopen Exited with retcode = 0.
(0) hoadscr (16): ; hoadscr Entered. id = 1.
(0) hoastmt.c (304): ; Array fetch size is: 1.
(0) HOAUTIL.C (1249): ; ------ hoadscr() -------:
(0) HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: Subject, hoadabfl: 4000, hoadamod: 0
(0) hoadscr (16): ; hoadscr Exited with retcode = 0.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) apiutil.c (0): ; hoaftch()::
(0) apiutil.c (0): ; Column 0 - pszName: Subject, size: 15361, id: 108, width: 15360, scale: 0, flags: 12.
(0) SELECT "T0000"."Subject" AS c000 FROM "TABLEA" "T0000"
(0)
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
(0) hoadtab (26): ; hoadtab Entered. count = 1
(0) hoadtab (26): ; schema_name = , table_name = ProductInformation
(0) odbc_rec: select * from "ProductInformation"
(0) nvOUT (D:\Work_Builds\connect_5010\src\qp\QP_SQTXT.C 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('DATABASEB', '%', 'ProductInformation', 1, 0, 0) order by 3, 1, 2, 4
(0) Execution Strategy Begin
(0) Original SQL:
(0) select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'DATABASEB' , '%' , 'ProductInformation' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
(0)
(0)
(0) Accessing saved query spec SP_STATISTICS()
(0) from NAV_PROC DB
(0)
Execution Strategy End 
The trace shows:
0) apiutil.c (0): ; Column 0 - pszName: $$CALC_1, size: 4, id: 8, width: 0, scale: 0, flags: 12.
(0) SELECT COUNT(*) AS c000 FROM "TABLEA" "A1"
(0)
(0) hoaftch (21): ; hoaftch Exited with retcode = 0, hoadabrc = 1.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) hoaftch (21): ; hoaftch Exited with retcode = 1403, hoadabrc = 0.
So the count(*) is sent again to the FDS. There is nothing HSODBC can do or change.
Unfortunately you didn't post the results for select using count(<column name>) suing an ODBC native tool like odbctest or isql depending on the platform.
It would be really good to see what happens when you vary the column name and test with all the column names from your table.

HS OLEDB Table xxxx has no fields

Hi -
I have written some time back a OLEDB provider, and have used it successfully under many products like excel, spotfire, vb, javascript etc etc, but I have tried to install it under oracle 10 and I have had an amazing amount of problems in getting the Heterogenous Services to tell me what is going on,
So far I have the driver configured reasonably well, and under the hood oracle is issuing the oledbcommand and the command is running ok and returning a result, however this is where oracle stops... and tells me the table has no fields !
I have narrowed it down to the following call that is being made by the hsolesql.exe process and identified where and why the call is failing however I cant see the reason.
The process calls the OleDbCommand.Execute class method with what seems to be an valid pointer value for ppRowset, however when I try to assign a valid rowset to it for the out variable - I get an av error read of address 0000006d, which is bizare as neither of the pprowset or its new value is at that address (obviously)
I include the trc and log files I get to see if anyone out there can help me.
(0) hoadtab (26): ; schema_name = , table_name = dd_functions
(0) RECORDS.C (767): ; Table dd_functions has no fields. Loading failed
(0) DRV_DescribeTable: RECORDS.C (767): ; Table dd_functions has no fields.
(0) Loading failed
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\drv\DRV_PRIV.C 757): -2202
(0) hoadtab (26): ; hoadtab Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\work\nav4100_branch\src\qp\QP_SQTXT.C 54): SELECT * FROM
(0) "dd_functions"
(0) nvRETURN (D:\work\nav4100_branch\src\qp\qpsynon.c 940): -1
(0) RECORDS.C (767): ; Table dd_functions has no fields. Loading failed
(0) DRV_DescribeTable: RECORDS.C (767): ; Table dd_functions has no fields.
(0) Loading failed
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\drv\DRV_PRIV.C 757): -2202
(0) [A00D] Failed to open table k3:dd_functions
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QPBLDBTN.C 502): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QPBLDBTN.C 382): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_SEMAN.C 2832): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_YACC.C 1886): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 504): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 229): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 197): -2202
(0) hoapars (15): ; hoapars Exited with retcode = 942.
(0) hoadtab (26): ; hoadtab Entered. count = 1
(0) hoadtab (26): ; schema_name = , table_name = dd_functions
(0) RECORDS.C (767): ; Table dd_functions has no fields. Loading failed
(0) DRV_DescribeTable: RECORDS.C (767): ; Table dd_functions has no fields.
(0) Loading failed
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\drv\DRV_PRIV.C 757): -2202
(0) hoadtab (26): ; hoadtab Exited with retcode = 0.
(0) hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
(0) nvOUT (D:\work\nav4100_branch\src\qp\QP_SQTXT.C 54): SELECT * FROM
(0) "dd_functions"
(0) nvRETURN (D:\work\nav4100_branch\src\qp\qpsynon.c 940): -1
(0) RECORDS.C (767): ; Table dd_functions has no fields. Loading failed
(0) DRV_DescribeTable: RECORDS.C (767): ; Table dd_functions has no fields.
(0) Loading failed
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\drv\DRV_PRIV.C 757): -2202
(0) [A00D] Failed to open table k3:dd_functions
(0)
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QPBLDBTN.C 502): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QPBLDBTN.C 382): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_SEMAN.C 2832): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_YACC.C 1886): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 504): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 229): -2202
(0) nvRETURN (D:\work\nav4100_branch\src\qp\QP_COMPL.C 197): -2202
(0) hoapars (15): ; hoapars Exited with retcode = 942.
and my log file :
18/04/2006 14:47:06:Starting initializeData
18/04/2006 14:47:06:RowSetData::Create Starting
18/04/2006 14:47:06:JNIClient::parseType Starting with dd_functions;
18/04/2006 14:47:06:JNIClient::ParseType Complete
18/04/2006 14:47:06:RowSetData::Create Complete
18/04/2006 14:47:06:Ending initializeData
18/04/2006 14:47:06:RowSetData::Execute Starting
18/04/2006 14:47:06:JNIClient::parseType Starting with dd_functions;
18/04/2006 14:47:06:JNIClient::ParseType Complete
18/04/2006 14:47:06:RowSetData::QueryDB query was dd_functions;
18/04/2006 14:47:06:RowSetData::QueryDB query complete
18/04/2006 14:47:06:Column name : name of type string
18/04/2006 14:47:06:Column name : description of type string
18/04/2006 14:47:06:Column name : type of type string
18/04/2006 14:47:06:Column name : library of type string
18/04/2006 14:47:06:RowSetData::Execute Complete
18/04/2006 14:47:06:OLEDB Exception raised (0) : at
18/04/2006 14:47:13:Exception occurred copying rowset in InternalOpenRowset, message was : Access violation at address 0000006D. Read of address 0000006D

MYSQL table with Text field issues

Hi all,
my problem is about different data types between Oracle and mySQL and how i can resolve them.
My enviroment is:
- Oracle database 10g(10204)
- mySql (in different machine, mysql db version is 4.0.16)
- using HS with ODBC
In mySql i have a table with TEXT column.
With TEXT fields I receive a "syntax error":
SQL> select "updatetxt" from "updatedb"#sworks;
ERROR:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][unixODBC][MySQL][ODBC 3.51
Driver][mysqld-4.0.16]You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to use near
'"UPDATEDB" WHERE "udid"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
ORA-02063: preceding 2 lines from SWORKS
The "updatetxt" column is declared TEXT in mySql and is seen as LONG from Oracle.
sworks is db link to MYSQL db
Please let me know the cause of it and how to overcome it.
Many Thanks,
Rish 
Please post a debug trace of hsodbc/dg4odbc (HS_FDS_TRACE_LEVEL=DEBUG) 
Hi,
Thanks for the response.trace file look like:
Oracle Corporation --- MONDAY FEB 15 2010 10:05:50.978
Heterogeneous Agent Release
10.2.0.4.0
hoagprd (2): ; hoagprd Entered.
HOACONN.C (270): ; [Generic Connectivity Using ODBC] version: 10.2.0.0.0080
HOACONN.C (323): ; Class version: 300
hoagprd (2): ; hoagprd Exited with retcode = 0.
hoainit (3): ; hoainit Entered.
(0): ; connect string is: defTdpName=sworks;SYNTAX=(ORACLE8_HOA, BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="", CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasources><datasource name='sworks' type='GENERIC_ODBC_FOR_HS' connect='sworks'><driverProperties/></datasource></datasources><remoteMachines/><environment><optimizer noFlattener='true'/><misc year2000Policy='-1' consumerApi='1' codepage='LATIN1' sessionBehavior='4'/><queryProcessor parserDepth='2000' tokenSize='1000' noInsertParameterization='true'
noThreadedReadAhead='true' noCommandReuse='true'/><debug generalTrace='true'/></environment></binding></navobj>
ORACLE GENERIC GATEWAY Log File Started at 2010-02-15T10:05:51
hoainit (3): ; hoainit Exited with retcode = 0.
hoalgon (7): ; hoalgon Entered. name = repuser.
Created new ODBC connection (137295432)
hoalgon (7): ; hoalgon Exited with retcode = 0.
hoaulcp (4): ; hoaulcp Entered.
hoaulcp (4): ; hoaulcp Exited with retcode = 0.
hoauldt (5): ; hoauldt Entered.
hoauldt (5): ; hoauldt Exited with retcode = 0.
hoabegn (9): ; hoabegn Entered. formatID = 306206, hoagttln = 25, hoagttid = DWPR.a09dccd2.11.82.22682, hoagtbln = 10, hoagtbid =
, tflag = 0, initial = 1
hoabegn (9): ; hoabegn Exited with retcode = 0.
hoadtab (26): ; hoadtab Entered. count = 1
hoadtab (26): ; schema_name = , table_name = updatedb
odbc_rec: select * from `updatedb`
nvOUT (./qp_sqtxt.c 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('sworks', '%', 'updatedb', 1, 0, 0) order by 3, 1, 2, 4
nvRETURN (./qpsynon.c 1171): -1
<<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
Original SQL:
select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'sworks' , '%' , 'updatedb' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
Accessing saved query spec SP_STATISTICS()
from NAV_PROC DB
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>HOAUTIL.C (1284): ; ------ hoadtab (hoat) -------:
HOAUTIL.C (1287): ; hoatnam: updatedb, hoatnml: 8, hoatnrw: -1, hoatarl: 1091
HOAUTIL.C (1298): ; ------ hoadtab (hoai) -------:
HOAUTIL.C (1299): ; n_index_stat: 4
HOAUTIL.C (1301): ; index 0 - hoaiuni: 0, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 1
HOAUTIL.C (1301): ; index 1 - hoaiuni: 0, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 9
HOAUTIL.C (1301): ; index 2 - hoaiuni: 1, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 11
HOAUTIL.C (1301): ; index 3 - hoaiuni: 1, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 11
HOAUTIL.C (1249): ; -------- hoadtab for table updatedb---------:
HOAUTIL.C (1250): ; hoadamsz: 20, hoadasiz: 20, hoadambr: 1, hoadabrc: 0, hoadawht: 5
HOAUTIL.C (1253): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 7, hoadanam: callref, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 1 - hoadambl: 20, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: updatetime, hoadabfl: 20, hoadamod: 0
HOAUTIL.C (1253): ; row 2 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: timespent, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 3 - hoadambl: 44, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 5, hoadanam: repid, hoadabfl: 44, hoadamod: 0
HOAUTIL.C (1253): ; row 4 - hoadambl: 160, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: groupid, hoadabfl: 160, hoadamod: 0
HOAUTIL.C (1253): ; row 5 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: udtype, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 6 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: udindex, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 7 - hoadambl: 2147483647, hoadadty: 165, hoadaprc: 2147483647, hoadacst: 0
HOAUTIL.C (1257): ; row 7 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: updatetxt, hoadabfl: 2147483647, hoadamod: 0
HOAUTIL.C (1253): ; row 8 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 8 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: udcode, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 9 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 9 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: udsource, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 10 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 10 - hoadascl: 0, hoadanul: 1, hoadanml: 4, hoadanam: udid, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 11 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 11 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: updatetimex, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 12 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 12 - hoadascl: 0, hoadanul: 1, hoadanml: 3, hoadanam: aid, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 13 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 13 - hoadascl: 0, hoadanul: 1, hoadanml: 4, hoadanam: aaid, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 14 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 14 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_a, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 15 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 15 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_b, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 16 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 16 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_c, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 17 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 17 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_d, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 18 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 18 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_e, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 19 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 19 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_f, hoadabfl: 254, hoadamod: 0
hoadtab (26): ; hoadtab Exited with retcode = 0.
hoadafr (23): ; hoadafr Entered. id = 0.
hoadafr (23): ; hoadafr Exited with retcode = 0.
hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
nvOUT (./qp_sqtxt.c 89): SELECT A1."updatetxt" FROM "updatedb" A1
nvRETURN (./qpsynon.c 1171): -1
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
<<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
Original SQL:
SELECT A1 . "updatetxt" FROM "updatedb" A1
Accessing Database 'sworks' with SQL:
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>hoapars (15): ; hoapars Exited with retcode = 0.
hoaopen (19): ; hoaopen Entered. id = 1.
hoaopen (19): ; hoaopen Exited with retcode = 0.
hoadscr (16): ; hoadscr Entered. id = 1.
hoastmt.c (304): ; Array fetch size is: 1.
HOAUTIL.C (1249): ; ------ hoadscr() -------:
HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
HOAUTIL.C (1253): ; row 0 - hoadambl: 2147483647, hoadadty: 165, hoadaprc: 2147483647, hoadacst: 0
HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 12, hoadanam: A1.updatetxt, hoadabfl: 2147483647, hoadamod: 0
hoadscr (16): ; hoadscr Exited with retcode = 0.
hoaftch (21): ; hoaftch Entered. id = 1.
apiutil.c (0): ; hoaftch()::
apiutil.c (0): ; Column 0 - pszName: A1.updatetxt, size: 5, id: 14, width: 5, scale: 0, flags: 68.
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
hoautil.c (571): ; Requested chunk length: 80
SELECT "updatetxt" FROM "updatedb" WHERE "udid"=1
UPDATE "updatedb" SET "updatetxt" = ? WHERE "udid"=1
errors.h (1526): ; [unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"updatedb" WHERE "udid"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
DRV_BlobRead: errors.h (1526): ; [unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"updatedb" WHERE "udid"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
nvRETURN (./drv_blob.c 250): -1
hoaftch (21): ; hoaftch Exited with retcode = 28500, hoadabrc = 1.
Hope it helps.
Thanks,
Rish 
it seems we send double quotes to Mysql db:
SELECT "updatetxt" FROM "updatedb" WHERE "udid"=1
which Mysql doesn't like when running in none-Ansi mode.
Please try to execute this pl/sql code before executing the select:
DECLARE
ret integer;
c integer;
BEGIN
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR#sworks;
DBMS_HS_PASSTHROUGH.PARSE#sworks(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY#sworks(c);
dbms_output.put_line(ret ||' passthrough output');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR#sworks(c);
END;
/ 
Hi,
Thanks for the response,but following code throws the error:
SQL> declare
2 ret integer;
3 c integer;
4 BEGIN
5 c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR#sworks;
6 DBMS_HS_PASSTHROUGH.PARSE#sworks(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
7 ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY#sworks(c);
8 dbms_output.put_line(ret ||' passthrough output');
9 DBMS_HS_PASSTHROUGH.CLOSE_CURSOR#sworks(c);
10 end;
11 /
declare
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_Execute: errors.h (1683): ;
[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]Unknown system variable
'SQL_MODE' (SQL State: S1000; SQL Code: 1193)
ORA-06512: at line 7
SQL_MODE variable is supported only from MySQL 4.1 version:
http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html
Any solution for my sql version which is 4.0.16?
Cheers,
Rish
Cheers,
Rish
Edited by: user7734492 on Feb 18, 2010 2:31 PM 
Can you temporarily change the setting globally to ANSI when starting mysqld?
Or does the ODBC driver you use support a QuotedId parameter like:QuotedId=Yes ?
Edited by: kgronau on Feb 19, 2010 1:01 PM 
Hi,
I can't change the setting globally to ANSI when starting mysqld even temporarily.
I tried using QuotedId = Yes in /etc/odbc.ini files still it seems to be an issue thought this time error seems to be a bit different than before.
SQL> select "updatetxt" from "updatedb"#sworks;
ERROR:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_BlobRead: errors.h (1526): ;
[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version for the
right syntax to use near '"updatedb" WHERE "udid"=1' at line 1 (SQL State:
37000; SQL Code: 1064)
ORA-02063: preceding 2 lines from SWORKS
Trace file look like:
Oracle Corporation --- MONDAY FEB 22 2010 10:44:15.558
Oracle Corporation --- MONDAY FEB 22 2010 10:44:15.558
Heterogeneous Agent Release
10.2.0.4.0
hoagprd (2): ; hoagprd Entered.
HOACONN.C (270): ; [Generic Connectivity Using ODBC] version: 10.2.0.0.0080
HOACONN.C (323): ; Class version: 300
hoagprd (2): ; hoagprd Exited with retcode = 0.
hoainit (3): ; hoainit Entered.
(0): ; connect string is: defTdpName=sworks;SYNTAX=(ORACLE8_HOA, BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="", CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasources><datasource name='sworks' type='GENERIC_ODBC_FOR_HS' connect='sworks'><driverProperties/></datasource></datasources><remoteMachines/><environment><optimizer noFlattener='true'/><misc year2000Policy='-1' consumerApi='1' codepage='LATIN1' sessionBehavior='4'/><queryProcessor parserDepth='2000' tokenSize='1000' noInsertParameterization='true'
noThreadedReadAhead='true' noCommandReuse='true'/><debug generalTrace='true'/></environment></binding></navobj>
ORACLE GENERIC GATEWAY Log File Started at 2010-02-22T10:44:15
hoainit (3): ; hoainit Exited with retcode = 0.
hoalgon (7): ; hoalgon Entered. name = repuser.
Created new ODBC connection (137295432)
hoalgon (7): ; hoalgon Exited with retcode = 0.
hoaulcp (4): ; hoaulcp Entered.
hoaulcp (4): ; hoaulcp Exited with retcode = 0.
hoauldt (5): ; hoauldt Entered.
hoauldt (5): ; hoauldt Exited with retcode = 0.
hoabegn (9): ; hoabegn Entered. formatID = 306206, hoagttln = 23, hoagttid = DWPR.a09dccd2.7.5.50967, hoagtbln = 10, hoagtbid = ^G, tflag = 0, initial = 1
hoabegn (9): ; hoabegn Exited with retcode = 0.
hoadtab (26): ; hoadtab Entered. count = 1
hoadtab (26): ; schema_name = , table_name = updatedb
odbc_rec: select * from `updatedb`
nvOUT (./qp_sqtxt.c 89): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('sworks', '%', 'updatedb', 1, 0, 0) order by 3, 1, 2, 4
nvRETURN (./qpsynon.c 1171): -1
Execution Strategy Begin
Original SQL:
select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'sworks' , '%' , 'updatedb' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4
Accessing saved query spec SP_STATISTICS()
from NAV_PROC DB
Execution Strategy End
HOAUTIL.C (1284): ; ------ hoadtab (hoat) -------:
HOAUTIL.C (1287): ; hoatnam: updatedb, hoatnml: 8, hoatnrw: -1, hoatarl: 1091
HOAUTIL.C (1298): ; ------ hoadtab (hoai) -------:
HOAUTIL.C (1299): ; n_index_stat: 4
HOAUTIL.C (1301): ; index 0 - hoaiuni: 0, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 1
HOAUTIL.C (1301): ; index 1 - hoaiuni: 0, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 9
HOAUTIL.C (1301): ; index 2 - hoaiuni: 1, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 11
HOAUTIL.C (1301): ; index 3 - hoaiuni: 1, hoainci: 1, hoaiasc: 0
HOAUTIL.C (1304): ; index col 0 - hoaicid: 11
HOAUTIL.C (1249): ; -------- hoadtab for table updatedb---------:
HOAUTIL.C (1250): ; hoadamsz: 20, hoadasiz: 20, hoadambr: 1, hoadabrc: 0, hoadawht: 5
HOAUTIL.C (1253): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 7, hoadanam: callref, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 1 - hoadambl: 20, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: updatetime, hoadabfl: 20, hoadamod: 0
HOAUTIL.C (1253): ; row 2 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: timespent, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 3 - hoadambl: 44, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 5, hoadanam: repid, hoadabfl: 44, hoadamod: 0
HOAUTIL.C (1253): ; row 4 - hoadambl: 160, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: groupid, hoadabfl: 160, hoadamod: 0
HOAUTIL.C (1253): ; row 5 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: udtype, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 6 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: udindex, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 7 - hoadambl: 2147483647, hoadadty: 165, hoadaprc: 2147483647, hoadacst: 0
HOAUTIL.C (1257): ; row 7 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: updatetxt, hoadabfl: 2147483647, hoadamod: 0
HOAUTIL.C (1253): ; row 8 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 8 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: udcode, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 9 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 9 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: udsource, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 10 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 10 - hoadascl: 0, hoadanul: 1, hoadanml: 4, hoadanam: udid, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 11 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1257): ; row 11 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: updatetimex, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1253): ; row 12 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 12 - hoadascl: 0, hoadanul: 1, hoadanml: 3, hoadanam: aid, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 13 - hoadambl: 64, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 13 - hoadascl: 0, hoadanul: 1, hoadanml: 4, hoadanam: aaid, hoadabfl: 64, hoadamod: 0
HOAUTIL.C (1253): ; row 14 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 14 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_a, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 15 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 15 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_b, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 16 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 16 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_c, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 17 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 17 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_d, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 18 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 18 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_e, hoadabfl: 254, hoadamod: 0
HOAUTIL.C (1253): ; row 19 - hoadambl: 254, hoadadty: 108, hoadaprc: 0, hoadacst: 0
HOAUTIL.C (1257): ; row 19 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: custom_f, hoadabfl: 254, hoadamod: 0
hoadtab (26): ; hoadtab Exited with retcode = 0.
hoadafr (23): ; hoadafr Entered. id = 0.
hoadafr (23): ; hoadafr Exited with retcode = 0.
hoapars (15): ; hoapars Entered. stmtType = 0, id = 1.
nvOUT (./qp_sqtxt.c 89): SELECT A1."updatetxt" FROM "updatedb" A1
nvRETURN (./qpsynon.c 1171): -1
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
Execution Strategy Begin
Original SQL:
SELECT A1 . "updatetxt" FROM "updatedb" A1
Accessing Database 'sworks' with SQL:
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
Execution Strategy End
hoapars (15): ; hoapars Exited with retcode = 0.
hoaopen (19): ; hoaopen Entered. id = 1.
hoaopen (19): ; hoaopen Exited with retcode = 0.
hoadscr (16): ; hoadscr Entered. id = 1.
hoastmt.c (304): ; Array fetch size is: 1.
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
Execution Strategy Begin
Original SQL:
SELECT A1 . "updatetxt" FROM "updatedb" A1
Accessing Database 'sworks' with SQL:
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
Execution Strategy End
hoapars (15): ; hoapars Exited with retcode = 0.
hoaopen (19): ; hoaopen Entered. id = 1.
hoaopen (19): ; hoaopen Exited with retcode = 0.
hoadscr (16): ; hoadscr Entered. id = 1.
hoastmt.c (304): ; Array fetch size is: 1.
HOAUTIL.C (1249): ; ------ hoadscr() -------:
HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
HOAUTIL.C (1253): ; row 0 - hoadambl: 2147483647, hoadadty: 165, hoadaprc: 2147483647, hoadacst: 0
HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 12, hoadanam: A1.updatetxt, hoadabfl: 2147483647, hoadamod: 0
hoadscr (16): ; hoadscr Exited with retcode = 0.
hoaftch (21): ; hoaftch Entered. id = 1.
apiutil.c (0): ; hoaftch()::
apiutil.c (0): ; Column 0 - pszName: A1.updatetxt, size: 5, id: 14, width: 5, scale: 0, flags: 68.
SELECT `A1`.`udid` AS c001 FROM `updatedb` `A1`
hoautil.c (571): ; Requested chunk length: 80
SELECT "updatetxt" FROM "updatedb" WHERE "udid"=1
UPDATE "updatedb" SET "updatetxt" = ? WHERE "udid"=1
errors.h (1526): ; [unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"updatedb" WHERE "udid"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
DRV_BlobRead: errors.h (1526): ; [unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"updatedb" WHERE "udid"=1' at line 1 (SQL State: 37000; SQL Code: 1064)
nvRETURN (./drv_blob.c 250): -1
hoaftch (21): ; hoaftch Exited with retcode = 28500, hoadabrc = 1.
hoaclse (22): ; hoaclse Entered. id = 1.
hoaclse (22): ; hoaclse Exited with retcode = 0.
hoadafr (23): ; hoadafr Entered. id = 1.
hoadafr (23): ; hoadafr Exited with retcode = 0.
hoacomm (11): ; hoacomm Entered. keepinfo = FALSE, tflag = 1.
hoacomm (11): ; hoacomm Exited with retcode = 0.
hoalgof (8): ; hoalgof Entered. tflag = 1.
hoalgof (8): ; hoalgof Exited with retcode = 0.
hoaexit (6): ; hoaexit Entered.
hoaexit (6): ; hoaexit Exited with retcode = 0.
(0): ; Closing log file at MON FEB 22 10:45:57 2010.
Many Thanks,
Rish
Edited by: user7734492 on Feb 22, 2010 12:54 PM
Edited by: user7734492 on Feb 22, 2010 12:56 PM 
Try to temporarily change the SQL_MODE globally to ANSI when starting mysqld. 
Hi,
we had a similar problem with the DRDA gateway in Version 10gR2 for DB2.
In DB2 the column is defined as VARCHAR2(32740), but the DRDA gateway shows it as a LONG column in the describe.
Referencing the Oracle-DRDA documentation, this is a normal behaviour.
Normally we select data from DB2 directly into a local table as INSERT ... SELECT. This doesn't works with a long column in the select statement.
I wrote a pipelined table function to get the data from the remote database with the long column and converting it into a CLOB:
FUNCTION get_db2_long_data
    RETURN type_tab
    PIPELINED
IS
    TYPE type_ref_cursor
      IS REF CURSOR;
    v_src_cursor                   type_ref_cursor;
    v_col1                         VARCHAR2(10);
    v_col_tgt_long                 CLOB;
    v_col_src_long                 LONG;
BEGIN
      OPEN v_src_cursor
        FOR 'SELECT col1, col_long FROM tab#db2'
      ;
      -- loop throug cursor and pipe data
      <<loop_src_cursor>>
      LOOP
          FETCH v_src_cursor
              INTO v_col1
                   ,v_col_src_long
              ;
          EXIT WHEN v_src_cursor%NOTFOUND;
          -- converting LONG to CLOB
          v_col_tgt_long := v_col_src_long;
          -- pipe out data
          PIPE ROW(type_row_tab(v_col1
                                ,v_col_tgt_long
                               )
                  );
      END LOOP loop_src_cursor;
      CLOSE v_src_cursor;
END;
/This is only an untested excerpt from the complete function for demonstration purposes.
Additionally, the PL/SQL-LONG variable only allows 32760 bytes. When the text-attribute in the source database is larger, then some data will been lost.
This proceeding is not so performant and the execution time is roundabout three times longer than a direct select on a DB2 table without a long column but we had to find a quick solution for this problem.
Also it is only an interims solution and has fulfilled our requirements.

ORA-28500, but no error returned from postgreSQL

I am running and oracle 8.1.6 on Win2K.
I am attempting to create a db link to a postgreSQL (pgsql) database using ODBC.
I have created a system DSN for the pgsql database and have tested it using MS access.
I then created a system DSN and an oracle db link for the MS Access db.
I have successfully tested the db link from oracle to the Access db, and can even view tables from the pgsql database via the MS Access link, but when I attempt to use the pgsql link created in oracle I get the following:
SQL> create database link LIMS
2 CONNECT TO "user" identified by "pass"
3 using 'TEST';
Database link created.
SQL> SELECT * FROM "tt"#LIMS;
SELECT * FROM "tt"#LIMS
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from LIMS
When I remove the quotes around the table name, I get the following:
SQL> select * from tt#TESTLIMS;
select * from tt#TESTLIMS
*
ERROR at line 1:
ORA-00942: table or view does not exist
[Transparent gateway for ODBC]393330(0,0,1[[]])
ORA-02063: preceding 2 lines from TESTLIMS
So, it appears that the connection has been made. Has anyone else run into a similar situation, with postgreSQL or any other database? Any suggestions would be appreciated.
Thank you
-- dave
David,
Take a look at the trace file generated by generic connectivity as well as the ODBC trace file.
These should provide you a clue of what is happening.
Thank you for the suggestion. Here are my findings:
The HS trace shows the following :
...
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "wt"
(0) odbc_rec: select * from "wt"
(0) SELECT "T0000"."wtdesc", "T0000"."who", "T0000"."modified", "T0000"."entered", "T0000"."welltype" FROM "wt" T0000
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "wt"
(0) Accessing Database "LIMS" with SQL:
(0) SELECT "T0000"."wtdesc", "T0000"."who", "T0000"."modified", "T0000"."entered", "T0000"."welltype" FROM "wt" T0000
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>... some other stuff ...
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT
(0) A1."welltype",A1."entered",A1."modified",A1."who",A1."wtdesc" FROM "wt" A1
(0) odbc_rec: select * from "wt"
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT A1 . "welltype" , A1 . "entered" , A1 . "modified" , A1 . "who" , A1 . "wtdesc" FROM "wt" A1
(0) Accessing Database "LIMS" with SQL:
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>the key thing to notice in the above is that in the select statement "Accessing Database "LIMS" with SQL:" the table alias is quoted when used to define the columns but NOT when the alias itself is defined. This query can not work unless the target database folds all table names to uppercase (which PostGreSQL does not), and does not seem to support the SQL standard.
From the ODBC log:
conn=32075320, query=' SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1'
ERROR from backend during send_query: 'ERROR: Relation 'A1' does not exist'
However, when I ran the following query on the PostgreSQL system: SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" "A1" (note the quotes around A1 when it is defined) it worked fine.
Just to check I then ran the query 'SELECT * FROM wt' through a MS Access to PostgreSQL ODBC link (no quotes around wt because Access will not accept them). If I use wt or WT for the table name the The ODBC log shows:
conn=146305872, query='SELECT "welltype","entered","modified","who","wtdesc" FROM "wt" WHERE "welltype" = 'D ' OR "welltype" = 'G ' OR "welltype" = 'J ' OR "welltype" = 'W ' OR "welltype" = 'X ''
[ fetched 5 rows ]
So, my question is: Is there a way to get HS to put quotes around the alias name when it is defining it, or perhaps not use an alias at all?
Thank you. 
David,
That is correct that we do not generate quotes for the table alias name but since the table name itself is quoted and its case is maintained, Postgres should be able to handle it.
Try putting a table alias in the original SELECT statement with quotes. It might work. 
Perhaps I was not clear in my last post. I am not attempting to use an alias.
The SQL statement that the Transparent Gateway for ODBC (TG) sends to the target database creates an SQL statement that uses an alias.
The SQL statement I typed into SQL+ was:
SELECT * FROM "wt"#TESTLIMS
This is aparently converted in the TG software to:
SELECT "T0000"."wtdesc", "T0000"."who", "T0000"."modified", "T0000"."entered", "T0000"."welltype" FROM "wt" T0000
Note the quotes around the alias name when referring to the column names ("T0000"."wtdesc")
but not when assigning the alias itself (FROM "wt" T0000)
Because the alias is not quoted when it is defined, its case is irelavent as far as SQL is concerned, so it is folded to lower case by PostgreSQL.
But because the alias reference in the column names is quoted, case does matter. The only target database that this select statement can work on is one that defaults to upper case, otherwise it is doomed to fail.
The statement (SELECT "T0000"."wtdesc" FROM "wt" T0000) fails in PostgreSQL.
however the following variations work
SELECT T0000."wtdesc" FROM "wt" T0000
SELECT "T0000"."wtdesc" FROM "wt" "T0000"
The TG software should either always use quotes on alias names or never use quotes on alias names. The practice of sometimes using quotes seems flawed.
Attached please find the complete log files from both the TG and postgresql ODBC.
Thank you
---------------------------------------------------------------------------
trace file in the oracle_home\hs\trace directory:
(0) hoagprd(2); Entered.
(0) [Transparent gateway for ODBC] version: 2.0.2.0.0002
(0) hoautil; connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=7;EXEC_FL-
(0) AGS = 131080;defTdpName=TESTLIMS;binding=(TESTLIMS,ODBC,"testlims");
(0) ORACLE GENERIC GATEWAY Log File (Version 203300) Started at 17-Jul-02 09:43:24
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = user.
(0) Created new ODBC connection (30216952)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid
(0) =LOCAL.US.ORACLE.COM.b0d5bde1.3.68.148, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "wt"
(0) odbc_rec: select * from "wt"
(0) SELECT "T0000"."wtdesc", "T0000"."who", "T0000"."modified", "T0000"."entered", "T0000"."welltype" FROM "wt" T0000
(0)
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "wt"
(0)
(0)
(0) Accessing Database "TESTLIMS" with SQL:
(0) SELECT "T0000"."wtdesc", "T0000"."who", "T0000"."modified", "T0000"."entered", "T0000"."welltype" FROM "wt" T0000
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt; Array fetch size is: 1.
(0) hoautil; ------ hoadscr() -------:
(0) hoautil; hoadamsz: 5, hoadasiz: 5, hoadambr: 1, hoadabrc: 1
(0) hoautil; row 0 - hoadambl: 2, hoadadty: 14, hoadaprc: 2, hoadacst: 0
(0) hoautil; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: welltype,
(0) hoadabfl: 2, hoadamod: 0
(0) hoautil; row 1 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam: entered,
(0) hoadabfl: 7, hoadamod: 0
(0) hoautil; row 2 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam: modified,
(0) hoadabfl: 7, hoadamod: 0
(0) hoautil; row 3 - hoadambl: 8, hoadadty: 14, hoadaprc: 8, hoadacst: 0
(0) hoautil; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 3, hoadanam: who,
(0) hoadabfl: 8, hoadamod: 0
(0) hoautil; row 4 - hoadambl: 50, hoadadty: 14, hoadaprc: 50, hoadacst: 0
(0) hoautil; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: wtdesc,
(0) hoadabfl: 50, hoadamod: 0
(0) hoadscr(16); Exited with retcode = 0.
(0) hoaclse(22); Entered. id = 1.
(0) hoaclse(22); Exited with retcode = 0.
(0) hoadafr(23); Entered. id = 1.
(0) hoadafr(23); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT
(0) A1."welltype",A1."entered",A1."modified",A1."who",A1."wtdesc" FROM "wt" A1
(0) odbc_rec: select * from "wt"
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0)
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT A1 . "welltype" , A1 . "entered" , A1 . "modified" , A1 . "who" , A1 . "wtdesc" FROM "wt" A1
(0)
(0)
(0) Accessing Database "TESTLIMS" with SQL:
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt; Array fetch size is: 1.
(0) hoautil; ------ hoadscr() -------:
(0) hoautil; hoadamsz: 5, hoadasiz: 5, hoadambr: 1, hoadabrc: 1
(0) hoautil; row 0 - hoadambl: 2, hoadadty: 14, hoadaprc: 2, hoadacst: 0
(0) hoautil; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam:
(0) A1.welltype, hoadabfl: 2, hoadamod: 0
(0) hoautil; row 1 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: A1.entered,
(0) hoadabfl: 7, hoadamod: 0
(0) hoautil; row 2 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam:
(0) A1.modified, hoadabfl: 7, hoadamod: 0
(0) hoautil; row 3 - hoadambl: 8, hoadadty: 14, hoadaprc: 8, hoadacst: 0
(0) hoautil; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: A1.who,
(0) hoadabfl: 8, hoadamod: 0
(0) hoautil; row 4 - hoadambl: 50, hoadadty: 14, hoadaprc: 50, hoadacst: 0
(0) hoautil; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: A1.wtdesc,
(0) hoadabfl: 50, hoadamod: 0
(0) hoadscr(16); Exited with retcode = 0.
(0) hoaclse(22); Entered. id = 1.
(0) hoaclse(22); Exited with retcode = 0.
(0) hoadafr(23); Entered. id = 1.
(0) hoadafr(23); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT
(0) A1."welltype",A1."entered",A1."modified",A1."who",A1."wtdesc" FROM "wt" A1
(0) odbc_rec: select * from "wt"
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0)
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT A1 . "welltype" , A1 . "entered" , A1 . "modified" , A1 . "who" , A1 . "wtdesc" FROM "wt" A1
(0)
(0)
(0) Accessing Database "TESTLIMS" with SQL:
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt; Array fetch size is: 1.
(0) hoautil; ------ hoadscr() -------:
(0) hoautil; hoadamsz: 5, hoadasiz: 5, hoadambr: 1, hoadabrc: 1
(0) hoautil; row 0 - hoadambl: 2, hoadadty: 14, hoadaprc: 2, hoadacst: 0
(0) hoautil; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam:
(0) A1.welltype, hoadabfl: 2, hoadamod: 0
(0) hoautil; row 1 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: A1.entered,
(0) hoadabfl: 7, hoadamod: 0
(0) hoautil; row 2 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) hoautil; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam:
(0) A1.modified, hoadabfl: 7, hoadamod: 0
(0) hoautil; row 3 - hoadambl: 8, hoadadty: 14, hoadaprc: 8, hoadacst: 0
(0) hoautil; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam: A1.who,
(0) hoadabfl: 8, hoadamod: 0
(0) hoautil; row 4 - hoadambl: 50, hoadadty: 14, hoadaprc: 50, hoadacst: 0
(0) hoautil; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: A1.wtdesc,
(0) hoadabfl: 50, hoadamod: 0
(0) hoadscr(16); Exited with retcode = 0.
(0) hoaftch(21); Entered. id = 1.
(0) SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1
(0)
(0) apiutil; hoaftch()::
(0) apiutil; row 0 - szColName: A1.welltype, cbMaxLen: 2, dwCoerceType: 14,
(0) cbInfoOffset: 0
(0) apiutil; row 0 - cbDataOffset: 4, dwBinding: 1, width: 2, scale: 0
(0) apiutil; row 1 - szColName: A1.entered, cbMaxLen: 7, dwCoerceType: 167,
(0) cbInfoOffset: 6
(0) apiutil; row 1 - cbDataOffset: 10, dwBinding: 1, width: 0, scale: 0
(0) apiutil; row 2 - szColName: A1.modified, cbMaxLen: 7, dwCoerceType: 167,
(0) cbInfoOffset: 17
(0) apiutil; row 2 - cbDataOffset: 21, dwBinding: 1, width: 0, scale: 0
(0) apiutil; row 3 - szColName: A1.who, cbMaxLen: 8, dwCoerceType: 14,
(0) cbInfoOffset: 28
(0) apiutil; row 3 - cbDataOffset: 32, dwBinding: 1, width: 8, scale: 0
(0) apiutil; row 4 - szColName: A1.wtdesc, cbMaxLen: 52, dwCoerceType: 14,
(0) cbInfoOffset: 40
(0) apiutil; row 4 - cbDataOffset: 44, dwBinding: 1, width: 50, scale: 0
(0) ERROR: Relation 'A1' does not exist (SQL State: S1000; SQL Code: 7)
(0) DRV_GetRows: ERROR: Relation 'A1' does not exist (SQL State: S1000; SQL Code:
(0) 7)
(0)
(0) nvRETURN (P:\Src\DRV\DRV_IROW.C 239): -2204
(0) nvOUT (P:\Src\UTIL\GETERROR.C 327): DRV_GetRows: ERROR: Relation 'A1' does
(0) not exist (SQL State: S1000; SQL Code: 7)
(0) hoaftch(21); Exited with retcode = 28500, hoadabrc = 1.
(0) hoaclse(22); Entered. id = 1.
(0) hoaclse(22); Exited with retcode = 0.
(0) hoadafr(23); Entered. id = 1.
(0) hoadafr(23); Exited with retcode = 0.
(0) hoaroll(12); Entered. tflag = 1.
(0) hoaroll(12); Exited with retcode = 0.
(0) hoalgof(8); Entered. tflag = 1.
(0) nvRETURN (P:\Src\DRV\DRVIUNWN.C 629): -1210
(0) hoalgof(8); Exited with retcode = 0.
(0) hoaexit(6); Entered.
(0) hoaexit(6); Exited with retcode = 0.
(0) hoautil; Closing log file at WED JUL 17 09:48:09 2002.
---------------------------------------------------
contents of postgresql ODBC log file:
---------------------------------------------------
Global Options: Version='07.02.0001', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'
conn=32075320, query=' '
conn=32075320, query='set DateStyle to 'ISO''
conn=32075320, query='set geqo to 'OFF''
conn=32075320, query='set ksqo to 'ON''
conn=32075320, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=32075320, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96' ]
[ PostgreSQL version number = '7.0' ]
conn=32075320, query='Show Client_Encoding'
NOTICE from backend during send_query: 'NOTICE: Current client encoding is SQL_ASCII
'
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=32075320, PGAPI_DriverConnect(out)='DSN=testlims;DATABASE=testlims;SERVER=esslims;PORT=5432;UID=user;PWD=xxxxx;A6=;A7=100;A8=4096;B0=254;B1=8190;C2=dd_;;CX=18951fab'
conn=32075320, query='BEGIN'
conn=32075320, query='select u.usename, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'wt' order by c.relname, attnum'
[ fetched 5 rows ]
PGAPI_Columns: table='wt',field_name='welltype',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='entered',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='modified',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='who',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='wtdesc',type=1042,name='bpchar'
conn=32075320, query='select u.usename, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'wt' order by c.relname, attnum'
[ fetched 5 rows ]
PGAPI_Columns: table='wt',field_name='welltype',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='entered',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='modified',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='who',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='wtdesc',type=1042,name='bpchar'
conn=32075320, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules from pg_index i, pg_class c, pg_class d, pg_am a where d.relname = 'wt' and d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, c.relname'
[ fetched 1 rows ]
conn=32075320, query='select u.usename, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'wt' order by c.relname, attnum'
[ fetched 5 rows ]
PGAPI_Columns: table='wt',field_name='welltype',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='entered',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='modified',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='who',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='wtdesc',type=1042,name='bpchar'
conn=32075320, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules from pg_index i, pg_class c, pg_class d, pg_am a where d.relname = 'wt' and d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, c.relname'
[ fetched 1 rows ]
conn=32075320, query='select u.usename, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'wt' order by c.relname, attnum'
[ fetched 5 rows ]
PGAPI_Columns: table='wt',field_name='welltype',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='entered',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='modified',type=1184,name='timestamp'
PGAPI_Columns: table='wt',field_name='who',type=1042,name='bpchar'
PGAPI_Columns: table='wt',field_name='wtdesc',type=1042,name='bpchar'
conn=32075320, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules from pg_index i, pg_class c, pg_class d, pg_am a where d.relname = 'wt' and d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, c.relname'
[ fetched 1 rows ]
conn=32075320, query=' SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1'
ERROR from backend during send_query: 'ERROR: Relation 'A1' does not exist'
conn=32075320, query='ROLLBACK'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=32075320, stmt=32113440, result=32124240
manual_result=0, prepare=1, internal=0
bindings=32124080, bindings_allocated=5
parameters=0, parameters_allocated=0
statement_type=0, statement=' SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1'
stmt_with_params=' SELECT "A1"."wtdesc", "A1"."who", "A1"."modified", "A1"."entered", "A1"."welltype" FROM "wt" A1'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR01EA0320'
----------------QResult Info -------------------------------
fields=32113024, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
message='ERROR: Relation 'A1' does not exist', command='(NULL)', notice='(NULL)'
status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: Relation 'A1' does not exist'
------------------------------------------------------------
henv=32116576, conn=32075320, status=1, num_stmts=16
sock=32116512, stmts=32116432, lobj_type=-999
---------------- Socket Info -------------------------------
socket=484, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=32086272, buffer_out=32090376
buffer_filled_in=11, buffer_filled_out=0, buffer_read_in=11
conn=32075320, PGAPI_Disconnect
David,
I cannot figure out what is going on here. We have tried some similar queries to Sybase ( I do not have access to postgres) but I am not seeing what you are seeing.
More investigation is required.
I think the best thing to do is to contact Oracle support.

Can't select char type columns using ODBC link to SQL Server 2K

I have set up a db link using hsodbc from 8.1.7 to SQL Server 2000. I can select numeric and date columns from the
SQL Server table with no problem. But any character datatype (nchar or nvchar) column name included in the query
returns an ORA-00904 ("invalid column name"). I know the column names are valid for SQL Server and can access
these columns via EXCEL using the same user id and data source that is used for the link. I don't have a clue as to why ORACLE can't "see" the character type columns. Following is the hs trace file (HS_FDS_TRACE_LEVEL = debug) generated for "select assay_name from bio_assay#sqlsrvl":
FRIDAY SEP 05 2003 10:39:47.674
(0) hoagprd(2); Entered.
(0) [Generic Connectivity Using ODBC] version: 2.0.4.0.0010
(0) connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=4;PARSER_-
(0) DEPTH=2000;EXEC_FLAGS =
(0) 131080;defTdpName=SQLSRVL;binding=(SQLSRVL,ODBC,"LIBERTY");
(0) ORACLE GENERIC GATEWAY Log File Started at 05-Sep-03 10:39:47
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = XXXXXXXXXXX.
(0) Created new ODBC connection (28382608)
(0) Silent DB Function!!
(0) (Last message occurred 4 times)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid
(0) =XXXXXXXXXXXXXXXXXXXXXXXX, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "BIO_ASSAY"
(0) odbc_rec: select * from "BIO_ASSAY"
(0) Silent DB Function!!
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 932):
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "BIO_ASSAY"
(0)
(0)
(0) Accessing Database "SQLSRVL" with SQL:
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt(195); Array fetch size is: 1.
(0) ------ hoadscr() -------:
(0) hoadamsz: 11, hoadasiz: 11, hoadambr: 1, hoadabrc: 1
(0) row 0 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 9, hoadanam: ASSAY_KEY, hoadabfl:
(0) 20, hoadamod: 0
(0) row 1 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 1 - hoadascl: 0, hoadanul: 0, hoadanml: 15, hoadanam: ENVIRONMENT_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 2 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: TARGET_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 3 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 3 - hoadascl: 0, hoadanul: 0, hoadanml: 13, hoadanam: TIMEFRAME_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 4 - hoadambl: 2, hoadadty: 7, hoadaprc: 5, hoadacst: 0
(0) row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 15, hoadanam: ASSAY_STATUS_NO,
(0) hoadabfl: 2, hoadamod: 0
(0) row 5 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: ASSAY_DATE_STARTED,
(0) hoadabfl: 7, hoadamod: 0
(0) row 6 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: ASSAY_DATE_PLANNED,
(0) hoadabfl: 7, hoadamod: 0
(0) row 7 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 7 - hoadascl: 0, hoadanul: 1, hoadanml: 20, hoadanam:
(0) ASSAY_DATE_COMPLETED, hoadabfl: 7, hoadamod: 0
(0) row 8 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 8 - hoadascl: 0, hoadanul: 0, hoadanml: 18, hoadanam: ASSAY_CREATED_TIME,
(0) hoadabfl: 7, hoadamod: 0
(0) row 9 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 9 - hoadascl: 0, hoadanul: 1, hoadanml: 19, hoadanam: ASSAY_MODIFIED_TIME,
(0) hoadabfl: 7, hoadamod: 0
(0) row 10 - hoadambl: 0, hoadadty: 0, hoadaprc: 0, hoadacst: 0
(0) row 10 - hoadascl: 0, hoadanul: 0, hoadanml: 13, hoadanam: WH_ADDED_DATE,
(0) hoadabfl: 0, hoadamod: 0
(0) hoadscr(16); Exited with retcode = 0.
Note, the query I entered doesn't appear and I never entered the query "select * from bio_assay" - it appears that this is
automatically generated by the hsodbc process and raises other questions (like wouldn't this return a lot of unneeded
data ?). It also appears that the "select * from bio_assay" gets translated somehow into a select statement listing each column - however, non of the columns listed are character type columns like assay_name.
Does this make sense to anyone? Any ideas ? HELP ! 
Convert columns of NVARCHAR datatype in SQLServer into VARCHAR 
How do I convert NVARCHAR to VARCHAR ? I can't (not allowed) modify the SQL Server database. THANKS for the reply. 
Use remote view instead of remote table when accessing from Oracle. View must contain conversion function in SELECT clause (I don't remember the syntax for such function) 
Remote view gives the same error. I think the problem has to do with nchar and nvchar fields. It seems that the hsodbc agent issues a "select * from bio_assay" at the beginning of the session (see above trace).
0) odbc_rec: select * from "BIO_ASSAY"
(0) Silent DB Function!!
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
Apparently, the list of fields that is returned from the "select *" does not include any of the nchar or nvchar type columns. After this initial "select *" the oracle server only looks at the initial list of columns and gives an "invalid column" error for any fields that are not in this initial list. So the question is: how do I make the hsodbc agent see the nchar/nvchar type fields and include them in the initial list ? I've tried setting the HS_NLS_LANGUAGE and HS_NLS_NCHAR parameters but these seem to have no effect.
Any clue as to what's going on ? 
hsodbc 817 is not capable dealing with nchar/nvarchar columns.
See the manual of supported data types for more info:
Appendix D; page d-2:
ODBC -> Oracle
SQL_BIGINT -> NUMBER(19,0)
SQL_BINARY -> RAW
SQL_CHAR -> CHAR
SQL_DATE -> DATE
SQL_DECIMAL(p,s) -> NUMBER(p,s)
SQL_DOUBLE -> FLOAT(49)
SQL_FLOAT -> FLOAT(49)
SQL_INTEGER -> NUMBER(10)
SQL_LONGVARBINARY -> LONG RAW
SQL_LONGVARCHAR -> LONG
SQL_NUMERIC(p,s) -> NUMBER(p,s)
SQL_REAL -> FLOAT(23)
SQL_SMALLINT -> NUMBER(5)
SQL_TIME -> DATE
SQL_TIMESTAMP -> DATE
SQL_TINYINT -> NUMBER(3)
SQL_VARCHAR -> VARCHAR 
THANKS for pointing this out. Could I install the 9.2 version of hsodbc to get around this limitation ? 
hsodbc V9.2 maps it to characters; if you need NATIONAL CHARACTER set support, you must use TG4MSQL.
Regards,
Klaus 
Sorry, my last posting missed some copy/paste info.
Here now all the details:
A describe of the SQL server table works with hsodbc release 9.2; a select will fail due to the same reason as mentioned for 817. The dataype is not supported.
If you check out an odbc trace initiated by hsodbc, you'll see, that the ODBC datatypes are SQL_WVARCHAR and SQL_WCHAR. Both are not in the supported datatype list of hsodbc V92.
The only way to get national character set columns out of SQL Server is with TG4MSQL.

Categories

Resources