Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Server View with ODBC Connection

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL Server View with ODBC Connection
 Login/Join
 
Sayed
Guru
posted
Hi,

Has anyone tried to connect to a SQL Server view through ODBC Connection?

1 - I can generate metadata for SQL Server tables and Views through the console.

2 - I can sample data for the tables but not for the view using ODBC. The view gives the following error

"(FOC1400) SQLCODE IS 1 (HEX: 00000001) : [22005] [Microsoft][ODBC SQL Server Driver]Invalid character value for c : ast specification
(FOC1407) SQL FETCH CURSOR ERROR. : COMPONENT
"

3 - Above works for both tables and views if I use the iWay SQL Server adaptor.

Seems like SQL Server views are not supported using ODBC.

Thanks,
Sayed

This message has been edited. Last edited by: Kerry,


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Waz
Expert
posted Hide Post
Does the connections work if you take WebFOCUS out of the picture, and use an ODBC test tool.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Sayed
Guru
posted Hide Post
Waz,

Thanks for the suggestion. I tried with the software from Data Direct and it works for the views. Must be WebFOCUS that's causing the problems. I'm on WF 768 here.

Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Waz
Expert
posted Hide Post
What is the SQL generated/SQL used in the fex ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Sayed
Guru
posted Hide Post
I didn't get to writing the report. It bombs when trying to sample data from the server console.

Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
njsden
Virtuoso
posted Hide Post
The question may be silly but why aren't you using the iWay adapter to SQL server instead of forcing yourself to go via ODBC? I'm sure iWay can make a better SQL translation if you use the right adapter which may lead to better optimization anyway.

It is obviously interesting to know why you're getting the errors you mention as whatever happens "behind" ODBC should be transparent to WebFOCUS, shouldn't it?

quote:
It bombs when trying to sample data from the server console


Would you mind posting the whole stack of FOC- and/or SQL- errors you're getting?

This message has been edited. Last edited by: njsden,


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
dhagen
Virtuoso
posted Hide Post
I've seen and solved this before. There are some formats that have to be tweaked when using ODBC and MSSQL. What is the usage and actual for the COMPONENT column? Or just change the actual to something more generic then what it has been set to and you will be good to go.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Sayed
Guru
posted Hide Post
Here is the master generated by ODBC. Tried changing values for Actual.. but no luck when i try to sample data.

 FILENAME=COMPONENT_ODBC, SUFFIX=SQLODBC , $
  SEGMENT=COMPONENT_ODBC, SEGTYPE=S0, $
    FIELDNAME=PNUM_CHI, ALIAS=PNUM_CHI, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=EFFECTIVE_DATE_CHILD, ALIAS=EFFECTIVE_DATE_CHILD, USAGE=HYYMDs, ACTUAL=HYYMDs, $
    FIELDNAME=END_DATE_CHILD, ALIAS=END_DATE_CHILD, USAGE=HYYMDs, ACTUAL=HYYMDs,
      MISSING=ON, $
    FIELDNAME=REVISION_VERSION_CHILD, ALIAS=REVISION_VERSION_CHILD, USAGE=A10V, ACTUAL=A10V,
      MISSING=ON, $
    FIELDNAME=INGREDIENT, ALIAS=INGREDIENT, USAGE=A100V, ACTUAL=A100V,
      MISSING=ON, $
    FIELDNAME=CAS_NUM, ALIAS=CAS_NUM, USAGE=A20V, ACTUAL=A20V,
      MISSING=ON, $
    FIELDNAME=INGREDIENT_PERCENT, ALIAS=INGREDIENT_PERCENT, USAGE=P9.2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=STATE_REQ, ALIAS=STATE_REQ, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $


Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Sayed
Guru
posted Hide Post
Nevermind,... DHAGEN your suggestion worked!!! Smiler

I changed the following on my master and now I can sample data. 

     FIELDNAME=PNUM_CHI, ALIAS=PNUM_CHI, USAGE=A50V, ACTUAL=A50V,
      MISSING=ON, $


In SQL Server the field was defined as follows.. PNUM_CHI(int,null).

Thanks,
Sayed

This message has been edited. Last edited by: Sayed,


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Server View with ODBC Connection

Copyright © 1996-2020 Information Builders