• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

ODA and ODBC access from thin clients

mpatel

Active Member
I've posted on this subject before, but thought of a different angle.. I need to access the one world data base for ad-hoc queries from a thin client, the client machine has no oracle client and no one world.. We don’t want to fire up the Oracle ODBC connection as this will compromise security etc.. and of course the ODA driver won’t work without a fat client install..
Has anyone managed to get the oda to work on a thin client without a full install ?? or does anyone fancy writing a bespoke odbc driver for one world ??

Manoj
 

Larry_Jones

Legendary Poster
Manoj,

If its a thin client ... why not run your program/process on the terminal server and store just the results on the client?

Perhaps if you tell us what you're trying to accomplish and why we might be able to assist you better. For example, do you need real-time access to the data or would periodic extracts of data loaded to an Access database on a local (to the client) file server do the trick?


Larry Jones
ljones@wagstaff.com
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
 

mpatel

Active Member
Larry,
What I want to do is enable power users to execute ad-hoc queries to the data base using a range of different front ends like ms access, crystal reports etc.. these users only have citrix access to the system, and I don’t want to load any additional software onto the citrix servers as this may well affect existing interactive processes.
I’ve also got a number of other issues related to this, namely things like row level security, decimal data scaling, field descriptions missing and Julian date conversions which are all addressed by the ow oda driver, but the oda only works with a fat client install.
So ideally I’m looking for a stand alone odbc connection which can be installed on a pc and the user can access the data base using the front end of his choice.. Almost like a much improved utb. I’m willing to have one written if anyone is prepared to take the challenge !
The client comes from and AS/400 background moving to HPUX and they have had the pleasure and joy of using AS/400 query for many years and are really looking for a equivalent reporting facility for the ow system.
Manoj
 

Larry_Jones

Legendary Poster
Manoj,

if you are pushing a client down this road you may be doing them a disservice. A custom ODBC driver ala ODA is not that simple a task. Witness JDE's attempts to try to get ODA right. Its also probably not needed (see below).

Is network latency an issue here (e.g. are the thin clients local to the database or remote)? If remote you've pretty much eliminated doing ad-hoc query and reporting from anything other than a terminal or web server.

Regarding the data issues you brought up (data security, decimal scaling, descriptive field names, and julian dates) these can all be dealt with by Oracle (or SQL Server) so that direct data access (ODBC) is both secure and user friendly. These issues have been discussed here before. Below is a partial copy of a response I have made to several people regarding these issues.

==============================================================
. . . we implemented Crystal Reports 8 as both an I.T. and an End User reporting/query tool. Our Enterprise Server is Unix/Oracle. The summary is that we are quite happy with Crystal Reports compared to the alternatives. One alternative we tried was ODA. After a lot of work with it, we found its limitations on table joins and performance to be unacceptable. Cognos was another alternative we discarded because of the cost factor (no free runtime unlike Crystal).

We (IT) did need to undertake some tasks in order for Crystal to meet our requirements for ease-of-use and data security. The specific concerns you need to deal with if you don't use ODA are:

1. Table and column naming. I.T. people after much use may become comfortable with the brief acronyms such as PRODDTA.F4101.IMITM when referring to the Short Item Number. We don't expect our end-users to have to deal with that though.

2. Date Fields. JDE uses a Julian Date format which stores dates as a number - not as a date. It's not obvious that 100129 represents April 8, 2000.

3. Decimal Places. All numeric fields are written without decimal places, so if you query the database a Line Item number will be returned as 1000 instead of 1.000.

4. Data Security. As delivered no database security is defined / setup. If you allow users direct access to the database instead of through JDE not only may they be able to see what should be restricted information - but some desktop tools would allow updates/deletes to take place (Access for one).

To deal with these issues we wrote PL/SQL scripts that:

1. Created views and synonyms for specific tables so that F4101 appears as "Item Master F4101" and IMITM appears as "Short_Item_Number_IMITM". The script pulls this information from the Object Librarian and Data Dictionary tables so the only thing we needed to do was to edit some of the names.

2. We dealt with dates in the same script that generates the views. It looks at the Data Dictionary Data Type and if the column is a Date data type then a custom Oracle Function is used to convert the JDE number into an Oracle Date. From Crystal (or Access or MSQuery ...) it looks like a normal date field now.

3. Numeric fields with decimal places were dealt with similiarly. If decimals exist then the database value was divided by 10 to the 'x' power.

4. To deal with the security issues a script was written to blow through the entire database and for all JDE tables remove public access and grant access only to the JDE Role. By default only the JDE account(s) now have access to any of the JDE tables. We also changed passwords on the default Oracle JDE accounts created when you install OW. The same SQL script generator used in 1 - 3 above also grants read (Select) privileges to different classes or roles of users. Tables that have not had a view defined are inaccessible to a user.

We're pretty happy with the approach and our user's are also. Performance is great - faster than RDA reports in almost all cases. And after having to route all report requests through I.T. users are ecstatic about being able to write their own when they need it. We do however require training in Crystal Reports before we will install the software on their desktop and turn them loose. Still though the I.T. role has changed from being the bottleneck to being the technical advisor - which is how it should be.
==================================================================

I'm not trying to toot my own horn here but just want to point out that your client already possesses the tools needed to safely and easily deliver OneWorld data to "Local" clients using MSQuery, Access, Crystal, etc.

Regards,


Larry Jones
ljones@wagstaff.com
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
 

mpatel

Active Member
Larry,
Thanks for all that, it's going to be very useful in helping the client work out the best way forward.
Manoj
 
Top