Connecting Ffenics to Oracle

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Connecting Ffenics to Oracle

Postby Graham Smith » Tue Nov 17, 2015 2:46 pm

I have a client who wants me to test connecting to Oracle. It's been quite a while since I messed with SQL and never with Oracle - which has it's own little funny ways.

The person I am working with is not a system IT guy and knows very little about setting up connections. Before I have to work my way up the food chain to someone who does know how to do this. I thought I'd ask here.

The 32bit ODBC shows a Microsoft ODBC for Oracle, but that gives and error that says the client and networking components were not found. Same for the Microsoft OLEDB Provider for Oracle.

The 64bit ODBC shows a Oracle in OraClient 11g_home1 version 11.02.00.04 driver. It has stuff on it I've never encountered including a TNS Service Name. I have an Oracle Instance name, table name, user name, and password. From a little looking on the interweb, I get the impression that there's something that I need to setup in the client, but I don't know what.

Among the list of things that I don't know, which is the first thing I have to learn about?
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Tue Nov 17, 2015 4:15 pm

1) Create your UDL-file (if you don't have one to copy already, simply right click within a folder from Windows Explorer, then New | Text document). Name the file something like 'MyUdl.Udl'

2) Double click the UDL-file and register:
* Provider-tab: e.g "Microsoft OLE DB Provider for ODBC Drivers"
* Connection-tab: 1) Server name = name according to your TNS-instance. 2) User name and password.
This file will not be encrypted, so keep it a secret [-o<

3) Save and test the connection (from the Connection-tab)

4) Within Ff: Application | Database Links | Add
* Link Name: can be set to anythin
* Engine type: OLEDB provider
* Server name: Full path and filename to your UDL-file.
You might remember I whined about the need to register the path here, arguing - if path wasn't specified - that Ff should look for it in A) the app folder B) the program folder. You will understand why this is so important after you decide to relocate that file :evil:
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Tue Nov 17, 2015 4:25 pm

KjetilUrne wrote:* Connection-tab: 1) Server name = name according to your TNS-instance. 2) User name and password.
This file will not be encrypted


You don't have to store the password. In my case (running scheduled jobs), I find it practical to store it. If possible use a user with limited rights.
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Tue Nov 17, 2015 5:26 pm

The OLE DB Provider for ODBC Drivers want's to connect to an ODBC data source rather than a Server.

Also, what's the difference between an Instance Name and a TNS Service Name?
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Tue Nov 17, 2015 5:54 pm

Graham Smith wrote:The OLE DB Provider for ODBC Drivers want's to connect to an ODBC data source rather than a Server.

I didn't get that.


Graham Smith wrote:Also, what's the difference between an Instance Name and a TNS Service Name?

'TNS Service Name' is probably the right description - I was referring to this part of your TNS invocation:
Code: Select all
%'TNS-instance'%=
  (DESCRIPTION= (ADDRESS=   (PROTOCOL=TCP)  (HOST=%hostname%) (PORT=%portno%) )  (CONNECT_DATA= (SERVICE_NAME=%servicename%)))
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Tue Nov 17, 2015 8:28 pm

It looks like there's a lot of information that I don't have yet.

This is what the Connection tab looks like if I specify OLEDB for ODBC Drivers:
Capture.PNG
Capture.PNG (20.65 KiB) Viewed 15096 times
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Wed Nov 18, 2015 5:13 am

I probably created my first udl file 12-13 years ago (on XP probably) and from then on only copied/changed the same source over and over. It could be there is a part I don't remember or newer Windows versions might deal with the creation process in another way. However, the udl file is nothing but a simple script file. What happens if you save the following as graham.udl

Code: Select all
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDAORA.1;Password=graham-pass;User ID=graham-user;Data Source=graham-TNS;Persist Security Info=True


Hopefully "Provider=MSDAORA.1" will be interpreted in the right way for you. Everything else should be editable when you open the file from Windows


Edit:
You might find more here
https://msdn.microsoft.com/en-us/librar ... 1e(v=vs.71).aspx
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Wed Nov 18, 2015 12:54 pm

I think that I found two pieces of the problem.

First, MSDAORA is the MS OLEDB Driver for Oracle rather than the ODBC (which would need to have an ODBC Data Source) setup.

Most importantly, whoever installed the client on the remote server I'm using didn't set everything up. There's no TNSNAMES.ORA file for one thing. So I'm going to need to get the info to set that up. That should fix both the ODBC and OLEDB issues, I hope.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Wed Nov 18, 2015 4:40 pm

Right, you will be 100% dependent upon the tnsnames.ora / Oracle client installation
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Fri Nov 20, 2015 3:05 pm

They only put the 64bit Client on the remote computer I am using. From what I can determine, I'm going to need the 32bit version because I can't get the 64bit version to work.

Anyone know any different.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Wed Nov 25, 2015 2:39 pm

Well, I've got 32bit ODBC and Ffenics can connect to it, but it won't create a form over a table.
ODBC Error: S1C00 Native Error: 0
[Oracle][ODBC]Driver not capable

OLEDB won't even get that far. Back to the drawing board.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby KjetilUrne » Wed Nov 25, 2015 5:52 pm

Graham Smith wrote:Well, I've got 32bit ODBC and Ffenics can connect to it, but it won't create a form over a table.
ODBC Error: S1C00 Native Error: 0
[Oracle][ODBC]Driver not capable

OLEDB won't even get that far. Back to the drawing board.


I don't have any problems creating forms over tables using OLEDB. Could it be that the Oracle driver is more resilient?
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Connecting Ffenics to Oracle

Postby Graham Smith » Wed Nov 25, 2015 6:43 pm

I have the feeling that your setup and mine is different in some very important way. I can connect Ffenics using a 32bit Oracle 11g ODBC User DSN. All settings are at their default. All tables are seen in Ffenics, but when I try and open one, I get the error.

I can connect to the db and table via SQL*Plus or via a freeware ODBC Database Browser, so I know that this is a valid connection.

If I try and connect using the MS ODBC for Oracle, I get the same S1C00 error. There may be something about the way the database is setup. If so then I am probably out of luck because IT isn't going to change anything for me.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest