Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 317 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
ODBC on Oracle-Existing Column with _ is not accessible
rthsw
Member Posts: 73
Hi there,
i have to connect to an Oracle Database wiht Navision 3.70 do Oracle 9i via MDAC 2.7/2.8 (both tested).
I will resolve a view, i know the Column names. If i only access fields with nomral names, i get the results if i resolve Names with underscore, navision errors (translated by me) The call to member Field is missed. ADODB.Fields gives the following message: An Object, which Name or Ordinalpointer fits, could not be found. ('Der Aufruf zum Member Item ist fehlgeschlagen. ADODB.fields gab die folgende Meldung zurück: Ein Objekt, das dem angeforderten Namen oder dem Ordinalverweis entspricht, kann nicht gefunden werden.'). Excel over MS-Query, or Aqua (an very usefull Oracle-Admintool) shows the column with all Entrys.
My Code:
//The bold-marked field is the blocking Column. If i ommit the bold //marked Line, i get the Records.
<Snip>
CREATE(ADOconnect);
ADOconnect.Open('DSN=BSS;UID=thoene;PWD=Rene');
CREATE(ADOrecset);
SQLstring := 'SELECT ARTIKEL_ID, MANDANT, SERIENNUMMER, REFERENZGEW, MENGE, RESERVIERT '+
'from KND_VIEW.INNER_BOX_V where (ARTIKEL_NR_A <> ''BSS-Dummy'') order by ARTIKEL_NR_A,SERIENNUMMER';
ADOrecset.Open(SQLstring,ADOconnect,OpenMethod::Keyset,LockMethod::Optimistic);
IF GUIALLOWED THEN
Window.UPDATE(1,'FindFirst');
ADOrecset.MoveFirst;
WHILE NOT ADOrecset.EOF DO BEGIN
RecCounter += 1;
TmpInventory."G/L Account" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('ARTIKEL_ID').Value),1,MAXSTRLEN(TmpInventory."G/L Account"));
TmpInventory."Gen. Bus. Posting Group" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('MANDANT').Value),1,MAXSTRLEN(TmpInventory."Gen. Bus. Posting Group"));
TmpInventory."Gen. Prod. Posting Group" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('SERIENNUMMER').Value),1,MAXSTRLEN(TmpInventory."Gen. Prod. Posting Group"));
And another problem: Both the Fields Seriennummer and the Field Mandant are defined as VARCHART(20) in Oracle. Navision Extract the content of the Mandant, but on the field Seriennummer, Navision breaks with Incompatible Fieldtype, with a list of the allowed Fieldtypes, including Varchar.
I hope very strong on the Oracle/ODBC Hackers Outside.
PS: There is absolute shure no Type-error, at last i copied the Fieldname with STRG+C and Paste it to Navision.
i have to connect to an Oracle Database wiht Navision 3.70 do Oracle 9i via MDAC 2.7/2.8 (both tested).
I will resolve a view, i know the Column names. If i only access fields with nomral names, i get the results if i resolve Names with underscore, navision errors (translated by me) The call to member Field is missed. ADODB.Fields gives the following message: An Object, which Name or Ordinalpointer fits, could not be found. ('Der Aufruf zum Member Item ist fehlgeschlagen. ADODB.fields gab die folgende Meldung zurück: Ein Objekt, das dem angeforderten Namen oder dem Ordinalverweis entspricht, kann nicht gefunden werden.'). Excel over MS-Query, or Aqua (an very usefull Oracle-Admintool) shows the column with all Entrys.
My Code:
//The bold-marked field is the blocking Column. If i ommit the bold //marked Line, i get the Records.
<Snip>
CREATE(ADOconnect);
ADOconnect.Open('DSN=BSS;UID=thoene;PWD=Rene');
CREATE(ADOrecset);
SQLstring := 'SELECT ARTIKEL_ID, MANDANT, SERIENNUMMER, REFERENZGEW, MENGE, RESERVIERT '+
'from KND_VIEW.INNER_BOX_V where (ARTIKEL_NR_A <> ''BSS-Dummy'') order by ARTIKEL_NR_A,SERIENNUMMER';
ADOrecset.Open(SQLstring,ADOconnect,OpenMethod::Keyset,LockMethod::Optimistic);
IF GUIALLOWED THEN
Window.UPDATE(1,'FindFirst');
ADOrecset.MoveFirst;
WHILE NOT ADOrecset.EOF DO BEGIN
RecCounter += 1;
TmpInventory."G/L Account" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('ARTIKEL_ID').Value),1,MAXSTRLEN(TmpInventory."G/L Account"));
TmpInventory."Gen. Bus. Posting Group" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('MANDANT').Value),1,MAXSTRLEN(TmpInventory."Gen. Bus. Posting Group"));
TmpInventory."Gen. Prod. Posting Group" :=
COPYSTR(FORMAT(ADOrecset.Fields.Item('SERIENNUMMER').Value),1,MAXSTRLEN(TmpInventory."Gen. Prod. Posting Group"));
And another problem: Both the Fields Seriennummer and the Field Mandant are defined as VARCHART(20) in Oracle. Navision Extract the content of the Mandant, but on the field Seriennummer, Navision breaks with Incompatible Fieldtype, with a list of the allowed Fieldtypes, including Varchar.
I hope very strong on the Oracle/ODBC Hackers Outside.
PS: There is absolute shure no Type-error, at last i copied the Fieldname with STRG+C and Paste it to Navision.
0