Instalacion necesaria para conectar a Oracle 11g

[Carles]

Hola,

Alguien me puede decir la mínima instalacion en la maquina cliente para poder conectar a Oracle 11g via ADO ? Existe algun mini-pack, mini-instalacion, mini registro de dll, ... para no tener que instalar todo el kit.

Gracias !

[nnicanor]

Debes instalar el Oracle Instant Client de 32 Bits, tener en cuenta el idioma y configurarlo en el registro en la clave NLS_LANG, yo uso LATIN AMERICAN SPANISH_COLOMBIA.WE8MSWIN1252 usa el correspondiente a tu instalacion, no es necesario crear un DNS basta con la cadena de conexion.

Yo trate de realizar la instalcion minima con un ZIP de instant client pero no me registra correctamente las DLL necesarias, preferi con el instalador completo.

Debes setear las variables de entorno ORACLE_HOME al directorio de tu instalcion de Instant Client.

cCmd :="Provider=OraOLEDB.Oracle;Data Source="+alltrim(cServer)+":"+alltrim(cPort)+"/"+alltrim(cSid)+";User ID="+alltrim(cUser)+";Password="+alltrim(cPassword)+";"

// ??? cCmd

oCn := FW_OpenAdoConnection( cCmd )


Slds

[Carles]

Nicanor,

Gracias por tu tip. La idea era en principio no instalar nada. Parece ser que si tienes instalada el MS Oracle DB provider, (MSDAORA.DLL) que parece ser que se instala por defecto, ya puedes realizar la conexion. De momento no me funciona , pero sigo con la idea de instalacion minima.

Intentaré ver si es posible, sino tendre de ir a aprar con las instalaciones completas desde el kit cliente.


Gracias por tu ayuda !

[nnicanor]

Carles,

Yo probé todas las formas de conexión pero para 11g se necesita el cliente, el cliente de Microsoft funciona con Oracle 8 y 9g, te dejo todas las formas de conectarte.

Code: Select all  Expand view

//----------------------------------------------------------//
//cConnStr  := "Provider=MSDAORA.1;Password=****;User ID=apps;Data Source=PROD;Persist Security Info=True" // Usuando Cliente Microsoft y TNSNAMES.ORA necesita cliente instalado
//cConnStr  := "Provider=MSDAORA.1;Password=****;User ID=apps;Data Source=ERPNET;Persist Security Info=True" // Cliente de Microsoft y Usando DSN  necesita cliente instalado y TNSNAMES.ORA
//cConnStr  := "Provider=OraOLEDB.Oracle;Data Source=192.168.X.YYY:15XX/PROD;User ID=apps;Password=*****;" Conexion Directa necesita Cliente de Oracle
//cConnStr  := 'Provider=OraOLEDB.Oracle;Data Source=ERPNET;User ID=***;Password=apps;'  // Conexion con cliente de Oracle y DSN
//cConnStr  := "Provider=OraOLEDB.Oracle;Data Source=192.168.X.YYY:15XX/XE;Initial Catalog=PROD;User Id=apps;Password=****;" // Conexion Directa
//cConnStr  := 'Provider=MSDAORA.1;Data Source=PROD;User ID=apps;Password=****'    // Conexion con CLiente de Microsoft y TNSNAMES.ORA
//cConnStr  := "Provider=OraOLEDB.Oracle;Data Source=PROD;User Id=apps;Password=apps;OLEDB.NET=True;"     // Cliente de ORacle y TNSNAMES.ORA
//cConnStr  := "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.X.YYY)(PORT=15XX)))(CONNECT_DATA=(SID=PROD)(SERVER=DEDICATED)));User  Id=apps;Password=****;" // Creando el TNSNAMES en linea
//cConnStr  := "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.X.YYY)(PORT=15XX))(CONNECT_DATA=(SERVICE_NAME=PROD)));User Id=apps;Password=apps;"  // Creando el TNSNAMES en linea
// cConnStr := 'Provider=MSDAORA.1;Data Source=PROD;User ID=apps;Password=apps'
cConnStr := 'Provider=OraOLEDB.Oracle;Data Source=PRODU;User ID=apps;Password=apps'

 oCn  := FW_OpenAdoConnection( cCmd )

cSql := "Select * from po_vendors"

 oRs := Fw_Openrecordset(oCn,cSql)

Xbrowse( oRs )


 

[Rick Lipkin]

Carles

I agree with Nicanor .. MsDaora never worked for me and I was trying to connect to 9i at the time I was doing Oracle as I previously mentioned in the English forum.

I worked in State gov for many years and had over 1k desktops that ran my application that connected to Oracle. I had to run a separate setup to load the Oracle Client with my pre-configured TnsNames.ora file and the provider was "OraOledb.oracle"

http://www.oracle.com/technetwork/datab ... 01290.html

To be honest .. I gave up on Oracle ( it is a good database ) because I had no choice but to load a client .. I have switched to Ms Access for small programs and Sql Server for Enterprise apps .. both Sql databases have native clients built into Windows XP thru Windows 8... just compile your .exe and create a shortcut on the desktop.

Rick Lipkin

[Carles]

Nicanor,

Gracias por tu ayuda y... me acabas de tirar un cubo de agua fria, grrrrrr. El objetivo era probar la conexion sin instalacion de ningun cliente pero si tu ya te peleaste, no tiene sentido volver ha hacerlo, para estan los forums -> gracias. Mirare los strings q me has pasado e instalare el cliente. Alguna instalacion especial para XP ?. Las funciones FW_OpenAdoConnection() y FW_OpenRecordSet(), me las puedes enviar para test ?. Gracias Nicanor !


Rick

The problem is that I have to use Oracle 11g and the advice I'm reading I can only get it by installing the client and this for me is "horrible".

I agree that MS SQL Server is a good choice because you do not have to worry about anything, but ... now I have to fight with Oracle 11g.

Thank you very much for your help.

[nnicanor]

Code: Select all  Expand view

#include "ado.ch"


//----------------------------------------------------------------------------//

function FW_OpenAdoConnection( cConnStr )

   local oCn

   oCn   := TOleAuto():New( "ADODB.Connection" )
   oCn:ConnectionString    := cConnStr
   oCn:CursorLocation      := 3            // adUseClient
   TRY
      oCn:Open()
   CATCH
      oCn                  := nil
   END

return oCn

//----------------------------------------------------------------------------//

function FW_OpenRecordSet( oCn, cSql, nLockType )

   local oRs

   DEFAULT nLockType       := 3 // adLockOptimistic

   oRs   := TOleAuto():new( "ADODB.RecordSet" )

   TRY

      WITH OBJECT oRs
          :ActiveConnection    := oCn
          :Source              := cSql
          :LockType            := nLockType
          :CursorLocation      := 3            // adUseClient
          :CacheSize           := 100
       END

      oRs:Open()

   CATCH
      //ShowAdoError( oRs:ActiveConnection )
      oRs                  := nil
   END

return oRs

//--------------------------------------------------//

Static function ShowAdoError( oCon )

   local oErr, nErr, cErr

   nErr   := oCon:Errors:Count()

   if nErr > 0
      oErr   := oCon:Errors[ nErr - 1 ]
      cErr   := oErr:Description
  //    cErr   += CRLF + "Code : " + cValToChar( oErr:NativeError )
  //    cErr   := "Description : "    + oErr:Description
  //    cErr   += CRLF + "SubCode : " + cValToChar( oErr:NativeError )
  //    cErr   += CRLF + "Source : "  + oErr:Source

      MsgInfo( cErr,oErr:Source+' Error' )
   else
      MsgInfo( 'Error inesperado contacte al Administrador del Sistema' )
   endif

return nil


//----------------------------------------------------------//

 

Slds

[Carles]

Nicanor,

Finalmente he tenido de instalar todo el Cliente, son solo 234 Mb . Esto impklica que si hay 10 maquinas cliente, en las 10 se ha de realizar esta instalacion...

- Para testear una conexion, primero he probado con las herramientas del sistema: Herramientas administrativas --> Origenes de Datos (ODBC)

- Para poder realizar el test, es necesario previamente definir un TNS Service Name. Para eso me he tenido que ir a definirlo y por defecto exite un fichero de la instalacion en ..\..\Network\Admin\Sample\tnsnames.ora

- Una vez definido el TNSNames, en el panel de ODBC ya me aparece el nombre de la conexion a Oracle y se realiza correctamente la conexion de test.

- Procedo a crear un DNS de Usuario y la conexión a desde FWH se realiza correctamente


Este procedimiento es correcto, o podemos hacerlo mas facil ?

[Rick Lipkin]

Carles

Using ODBC is just compounding your installation issue ..

1) Install your Oracle Client
2) Run the TnsNames configuration .. this sets up your TnsNames.Ora ( port, instance name and ip address )

Code: Select all  Expand view

# TNSNAMES.ORA Network Configuration File: c:\Ora9i\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
 

3) Just use your regular ADO connection and recordset syntax

Code: Select all  Expand view

xPROVIDER := "OraOledb.oracle"
xSOURCE   := "YourDatabase"
xUSERID   := "YourUserId"
xPASSWORD := "YourPassword"

xConnect  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';User Id='+xUSERID+';Password='+xPASSWORD

cSql := "Select * From [YourTable]"

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

TRY
   oRs:Open(cSQL,xConnect )
CATCH oErr
   MsgInfo( "Error in Opening table" )
      RETURN(.F.)
END TRY

IF oRs:EOF
   SAYING := "Could not find any matching records"
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF
 

Once you have your tnsnames.ora you can easily copy it to each workstation without running the config utility again.

http://wiki.fivetechsoft.com/doku.php?i ... ted_stuffs

Rick Lipkin

[nnicanor]

Carles

Si no quieres usar el tnsnames.ora puedes usar esta cadena de conexion directa, esa informacion la puedes guardar en un ini para que no tengas que recompilar la aplicacion si la direccion IP del servidor cambia.

Code: Select all  Expand view

cConnStr  := "Provider=OraOLEDB.Oracle;Data Source=192.168.X.YYY:15XX/SID;User ID=apps;Password=*****;"
 

15XX Es el puerto por defecto el 1523
SID es el SID de la base de datos por defecto ORCL o PROD
192.168.X.YYY direccion Ip de Servidor de Oracle

[Carles]

Nicanor,

Tu idea me gusta mas de no tener de usar el tns, pero de momento me arroja un error:

OraOLEDBplus11.dll : No se puede encontrar el modulo especificado

Quizas se ha instalado mal el cliente, voy a volver a intentar reinstalarlo,...

Gracias por tu ayuda


Rick

Thanks for your tip, but i'll try to connect via connection string as Nicanor says to me. I'm thinking that's is the best solution, but I've got error at the moment with one dll OraOLEDBpus11.l'll continue walking & ttesting

Thanks for your support

[Rick Lipkin]

Carles

Did a quick Google on your .dll and found this ..

Rick

http://social.msdn.microsoft.com/Forums ... f7983ca16/

If you are getting a message the .dll is not found .. you may have to register it manually by navigating to the folder that contains the .dll and

Code: Select all  Expand view

REGSVR32 OraOLEDBpus11.dll
 

This is what I have found that works...

For a 64-bit Oracle client install on Sql Download and install the ODAC Oracle client driver for 64 bit systems. Create the environment Variable for “TNS_Admin” and for the value use the path to where the tnsnames.ora file is at. For example “C:\app\product\11.2.0\client_1”

Also, in the Variable “Path”, include the directory where the Oracle\bin folder is located. For example…

“C:\app\product\11.2.0\client_1\bin”. If you miss this part, you will get an error saying the OraOLEDBpus11.dll could not be found.

To test…

Create a text file on your desktop called “Test.udl”. Double click on the file and it should open the “Data Link Properties”, move to the “Provider” tab and then select “Oracle Provider for OLE DB” and then next. Enter your connection properties and test.

Hope this is some help to someone that might be trying to do this in the future too.

Brian

Also, if your doing this for “Linked Servers” inside of SQL Server, don’t forget to go the Providers folder under the Linked Servers and open up the properties for “OraOLEDB.Oracle” and check the box “Allow inprocess”


In the case of a 32-bit SQL installation… Install Oracle client drivers….

What a mess, but this is what you need to do…

I did a custom install, installing the Oracle client driver Oracle .Net, and the ODBC and OLE drivers.After the install, you need to open regedit… find the key “Oracle\Inventory”. Change the path from “C:\Program Files(x86)\Oracle\Inventory” to “C:\Program Files\Oracle\Inventory” Then do a cut and paste of the Oracle directory from ““C:\Program Files(x86)\” and paste to ““C:\Program Files\”.

Then from “C:\Windows\SysWOW64\” run the application “odbcad32.exe”. This is where the drivers will be located at.

[Carles]

Rick

Thank u very much . I will try ...

[Rick Lipkin]

Carles

I added this to my post as you were replying .. you may have to register the .dll

Code: Select all  Expand view

REGSVR32 OraOLEDBpus11.dll
 

Rick

[Carles]

Rick,

In the folder ..\client_1\bin exist a lot of files to config. One of them is called selecthome.bat.oraoledb and is a ascii file.

Code: Select all  Expand view

regsvr32 /s C:\oracle\product\11.2.0\client_1\BIN\OraOLEDB11.dll

I'll try to execute it and to register the dll... i'll see...


Thanks