Connect to Oracle

General discussion forum about all databases other than SqlBase.
chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Connect to Oracle

Post by chrisocm » 27 Jun 2011, 03:21

I'm using TD 2.1 and sqlbase 7.6.0 and I need to know how to modify the sql.ini file to connect to oracle? I heard gupta came with an oracle router which enables ease of connection to oracle dbase without using ODBC or OLEDB. Pls advise. :cry:

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 27 Jun 2011, 12:03

Here's what our SQL.INI looks like:

Code: Select all

[win32client]
clientname=Win32Client

[win32client.dll]
comdll=sqlora32

[oragtwy]
remotedbname=PROD,@PD_SYSTEM.THERE.CA 
remotedbname=DEV,@DV_SYSTEM.THERE.CA 
remotedbname=TEST,@TS_SYSTEM.THERE.CA
fetchrow=20
maperror=OFF
substitute=SYSSQL.,
Cheers and best regards.

BTW: we work with TD2.1PTF1 and our applications access an Oracle 11g database.

jmcnamara

Re: Connect to Oracle

Post by jmcnamara » 27 Jun 2011, 14:31

You don't need an entry in the SQL.ini if you use OLEDB,

Just set SqlUDL = 'Provider=OraOLEDB.Oracle.1' - for 10g or 11g
Set the user name and password just like you do with SQLBase
Call SqlCreateSesson(...)
Then use SqlCreateStatement(...) instead of SqlConnect(...) to get a cursor
Make sure to call SqlDisconnect(...) when you're done with the cursor. Also, SqlFreeSession(...) when you close the app. Gupta does not clean up OLEDB connections when they go out of scope.

RainerE
Germany
Posts: 2053
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Connect to Oracle

Post by RainerE » 27 Jun 2011, 16:29

Hi,

the Oracle router is part of TD2.1. You can choose the router during setup. If so, you will find sqlora32.dll in the TD directory.

Connecting to Oracle using this router and the sql.ini parameters are described in "Connecting Gupta Objects to Databases", which -at least with TD4.2- is part of the TD online books. If this is not part of the TD2.1 online books, I'm sure you will find it at the Unify webpage as part of the online documentation.

Regards,
Rainer

chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Re: Connect to Oracle

Post by chrisocm » 29 Jun 2011, 03:01

Thank you all for the feedback. BTW, the oracle sits on another PC on a network with an IP address. Do I need to specify serverpath in the sql.ini? A 'stupid' question, where do I find where they put the oracle database and it's name (folder). ;-)

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 29 Jun 2011, 12:35

You need a "TNSNAMES.ORA" file.

In our case, the Oracle 7 client is installed in C:\ORANT, and our TNSNAMES.ORA file (just a simple text file) is located in C:\ORANT\NETWORK\ADMIN

Here's what the contents look like, modified for the sample SQL.INI contents I posted earlier:

Code: Select all

PD_SYSTEM.THERE.CA  = 
  (DESCRIPTION =
    (ADDRESS_LIST = 
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = theservercomputernameinourcase)
          (PORT = 1521)
    ) )
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PD_SYSTEM.THERE.CA) ) )
DV_SYSTEM.THERE.CA  = 
  (DESCRIPTION =
    (ADDRESS_LIST = 
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = theservercomputernameinourcase)
          (PORT = 1521)
    ) )
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DV_SYSTEM.THERE.CA) ) )
TS_SYSTEM.THERE.CA  = 
  (DESCRIPTION =
    (ADDRESS_LIST = 
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = theservercomputernameinourcase)
          (PORT = 1521)
    ) )
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TS_SYSTEM.THERE.CA) ) )

chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Re: Connect to Oracle

Post by chrisocm » 04 Jul 2011, 02:23

Charlie

I tried to modify the sql.ini

[win32client.dll]
comdll=sqlapipe
comdll=sqlora32
;comdll=sqlws32

[oragtwy]
substitute=SYSSQL.,
remotedbname=XE,@XE
longbuffer=32767
fetchrow=20
maperror=OFF

and the tnsnames.ora is :

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chrishannah)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

When i tried to connect using sqltalk, 'Connect XE;', it gives me an error,

'Error: 09279 GCI EOR Cannot find specified protocol entry'

Could u help me out ! much appreciated.

RainerE
Germany
Posts: 2053
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Connect to Oracle

Post by RainerE » 05 Jul 2011, 11:11

If you don't need to connect to SQLBase via anonymous pipes, comment out "comdll=sqlapipe".
Otherwise include the corresponding section.

Regards,
Rainer

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 05 Jul 2011, 12:16

Hi,

service_name: it should be something you can ping from another computer.

Can you actually ping XE? If not, you might need to put an IP address for service_name instead of "XE".

I'm no expert at this stuff ... what I've provided so far comes straight from 1994/95, by the developers who were here before me.



Are you able to connect to the Oracle Database on machine "X" with Oracle's SQPlus (install that along with the Oracle client) on machine Y ? You really need to be able to do that before you can get SQLWindows to connect.

chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Re: Connect to Oracle

Post by chrisocm » 20 Jul 2011, 11:13

some updates on the connection to oracle.

the oracle database sits on linux but i'm trying to connect to it using ODBC on a windows based system but were unable to. Is there anything that I not doing right?

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 20 Jul 2011, 19:55

Have you tried intalling the full Oracle client on the Windows computer, so that you can try and connect to the database with Oracle's SQLPlus tool ?

If you can't connect to that database with Oracle's product, you definitely won't be able to connect via ODBC with some other tool.

BTW: you need the Oracle 7 client installed on the Windows computer. Newer Oracle clients will not work with TD2.1

What version of the Oracle client do you have on the Windows computer?

chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Re: Connect to Oracle

Post by chrisocm » 21 Jul 2011, 03:59

Hi Charlie

The client side is using Oracle Developer 6. I tried connecting to the database using RAZOR Sql and it's fine. Using oracle jdbc driver (jdbc oracle thin). I tried to put the coding into CTD2.1 but it gives me the error :

ORA-12154: TNS:could not resolve the connect identifier specified

In ctd2.1:
Set strSessionProp = "Provider=OraOLEDB.Oracle;Drivers=C:\\Program Files\\RazorSQL\\drivers\\oracle\\ojdbc14.jar;C:\\Program
Files\\RazorSQL\\drivers\\oracle\\orai18n.jar;Server=jdbc:oracle:thin:@//10.13.13.1:1521/HIS;Uid=joe;Pwd=zaqwsx"

Set SqlDatabase = strArgArray[1]
Set SqlUser = strArgArray[2]
Set SqlPassword = strArgArray[3]
If SqlCreateSession( hSql, strSessionProp )
....
...

chrisocm
Malaysia
Posts: 71
Joined: 12 May 2017, 16:07
Location: Kuala Lumpur

Re: Connect to Oracle

Post by chrisocm » 21 Jul 2011, 04:43

Hi Charlie,

I tried using SqlTalk to connect to the oracle database and it's fine. but when i tried to select from a file, there is just no response and the hour glass just shown there. My sql.ini is as below

[dbnt1sv]
servername=Server1,sqlapipe
dbname=steelbiz,sqlapipe
dbname=global,sqlapipe
centurydefaultmode=1
dbdir=e:\database
cache=2000
logdir=e:\SqlBase_Logs
oracleouterjoin=1
partitions=0
secureapi=0

[dbnt1sv.dll]
comdll=sqlapipe

[dbnt1sv.apipe]
;debug=15,dbntsrv.dbg

[win32client]
clientname=Win32Client

[win32client.dll]
comdll=sqlapipe
comdll=sqlora32
;comdll=sqlws32

[oragtwy]
remotedbname=HIS,@//10.13.13.1:1521/HIS
substitute=SYSSQL.,
substitute=",
remotedbname=oracle7,@x:orasrv
substitute=SYSSQL.,
substitute='',
longbuffer=32767
fetchrow=20
maperror=OFF

[win32client.apipe]
;debug=15,client.dbg
[win32client.ws32]
;serverpath=server1,localhost/island
;
[dbnt1sv.gui]
MAINWIN=MAX,395,224,885,576
STATWIN=NORM,640,365,1280,730
DBWIN=NORM,640,0,1280,365
PROCWIN=NORM,0,365,640,730
SYSWIN=NORM,0,0,640,365
DISPLEVEL=0
TIMESTAMPS=0
[odbcrtr]
remotedbname=HIS,DSN=HIS
odbctrace=off
odbctracefile=sql.log

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 21 Jul 2011, 12:53

I'm a little confused. From your first post, I assumed you were trying to connect without using OLEDB. But in your second to last post, you're using an OLEDB provider. I can't help you with that. I'm old school: still connecting to the database using the Oracle router the same we've always done since SQLWindows 5.

That said: you never mentionned if, from a command prompt on the windows computer, "PING XE" actually gets any replies. If it doesn't get any replies, then your TNSNAMES.ORA won't work either.

Another one: I've never worked with nor installed Oracle Developer 6. Does it actually provide the same client as what would normally be installed with an "Oracle Client" CD?

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Connect to Oracle

Post by Charlie » 21 Jul 2011, 13:20

Sorry, I should have been writing, all along, "PING chrishannah".

Maybe a silly question: "chrishannah" is the computer name for the linux box, right? Is it serving the database via port 1521 ? If not, then you'll have to put the right port number for your TNSNAMES entries.

Can I ask, what are those other entries in TNSNAMES? (i.e. EXTPROC_CONNECTION_DATA and ORACLR_CONNECTION_DATA)

I can't figure out what "Oracle Developer 6" is. What Oracle Client does it come with? Bear in mind that TD2.1 is only certified to work with the following Oracle clients: 7.3.4, 8.0.4, 8.0.5, 8.1.5, 8.1.6, 8.1.7 ... we've only used 7.3.3 with TD2.1 and know that Oracle client version 9 and up do not work.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests