ADO Connection to Oracle 10g

[Rick Lipkin]

To All

I am trying to connect to an Oracle 10g database using the following code which fails every time .. any assistance would be appreciated.

Looking on MSDN there is some confusion as to "SOURCE" being the server name .. in this example the EFIS is that actual name of the database that resides on the server name EQCAIX3 ..

Rick Lipkin

xPROVIDER := "MSDAORA"
xSOURCE := "EFIS"
xUSERID := "SUNTRACK"
xPASSWORD := "goefis10"


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

cSQL := "SELECT * FROM EFIS_UST_RELEASES"
TRY
oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';User Id='+xUSERID+';Password='+xPASSWORD )
* oRs:Open(cSQL, "Provider=madaora;Data Source=efis;Persist Security Info=False;User ID=suntrack;Password=goefis10" )
CATCH oErr
MsgInfo( "Error in Opening EFIS_UST_RELEASES table" )
CLOSE DATABASES
QUIT
END TRY

[Enrico Maria Giordano]

I don't know it this can be of any help but try:

Code: Select all  Expand view

cSQL := "SELECT * FROM [EFIS_UST_RELEASES]"

EMG

[Rick Lipkin]

Enrico

Sorry .. that did not work .. I think it has something to do with the 'source' parameter .. i am missing something from my side on the identification .. server name perhaps, sciema prefix .. something like that.

Rick

[nageswaragunupudi]

For Source we should use the TNS Name of the Server/Database. It is the name used in TNS Confuration of the Oracle Client on the PC client. It is not the physical servername or the database name given by the DBA on the server.

If you open connection object separately, ( i advise not mix with opening recordset) and examine the connection object's error object, you narrow down the problem and know where exactly the error is.

I suggest something like this

Code: Select all  Expand view

cConnStr := 'Provider=MSDAORA.1;Data Source=<TNSnameofServer>;User ID= .... ;Password- ..'
oCon := TOLEAuto():New('ADODB.Connection')
oCon:ConnectionString := cConnStr
TRY
  oCon:Open()
CATCH
  ShowAdoError(oCon)
  Return .f.
END

// now open any number of recordsets using the above conn object
oRs   := TOLEAuto():New('ADODB.RecordSet')
TRY
  oRs:Open( <sql>, oCon, <other params > )
CATCH
  ShowAdoError(oCon)
END
//-------------
STATIC FUNCTION ShowAdoError

LOCAL   nAdoErrors   := 0
LOCAL   oAdoErr

      nAdoErrors   :=  oCon:Errors:Count()
      IF nAdoErrors > 0
         oAdoErr      := oCon:Errors(nAdoErrors-1)
         msginfo( oAdoErr:Description + CRLF + oAdoErr:Source )
      ELSE
         msginfo( 'Not Oracle Error' )
      ENDIF

RETURN nil


Once we successfully open the connection object, we can use the same connection object to open hundreds of recordsets or use execute commands. Opening RecSet with connection string each time will open new connection each time with two disadvantages (1) Server will have too many connections open and 32 bit windows server will reach its limit soon, denying service to next users(2) isolating the error becomes difficult.

[nageswaragunupudi]

Schema prefix is to be used with table names as <schema>.<table>. It is always a safe and good programming practice to use schema prefix for all tables in oracle

[Rick Lipkin]

nageswaragunupudi

WOW ... thanks for the great answer .. I will try your code in the morning. I was using the DNS name of the server for the source .. I do have a tnsnames.ora file .. I think the tns name is 'efis.world' .. The schema is 'suntrack' .. so my guess will be to use :

xPROVIDER := "MSDAORA"
xSOURCE := "EFIS.world"
xUSERID := "me"
xPASSWORD := "mypassword"


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

cSQL := "SELECT * FROM Suntrack.EFIS_UST_RELEASES"

TRY
oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';User Id='+xUSERID+';Password='+xPASSWORD )
* oRs:Open(cSQL, "Provider=madaora;Data Source=efis;Persist Security Info=False;User ID=suntrack;Password=goefis10" )
CATCH oErr
MsgInfo( "Error in Opening EFIS_UST_RELEASES table" )
CLOSE DATABASES
QUIT
END TRY

[nageswaragunupudi]

Data Source = 'efis.world'.

For some reasons i did not like the .world extention. Had some issues with it. All our client installations have tns names without the .world.

Please try efis.world first as the data source name. you should be able to connect.

I once again recommend opening the connection object only in the beginning of the program. Check the validity of connection, check if it is open ( oCon:State == 1 ) and then proceed with rest of the work. At the end of program, close the connection with oCon:Close.

For opening recordsets, use oCon object in the place of the connection string each time.

This apporach has a lot more advantages than I could explain in the earlier post.

We do see many asp page sources with total connection string while opening recordsets. That is a 3-tier architecture and a different environment of stateless connections and disconnected recordsets.

For client-server programming ( 2-tier ) what I proposed is the best. You may consider if you like.

[Rick Lipkin]

nageswaragunupudi

Was curious to try your code .. your error message was much more informative than just 'e_fail' ..

I got this message that

'Oracle client and networking componants not found. These componants are supplied by Oracle Corp and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these componants are installed. Microsoft OLE DB Provider for Oracle'

I was hoping MSDAORA would have everything I need .. I do have the thick client of Oracle 6i on my machine .. but I don't believe this is what I need .. further research on the client is the first order of business in the morning ..

Many Thanks

Rick Lipkin

ps .. I took your code on the connection and used it .. your 'catch' error was much better .. I did try both 'efis' and 'efis.world' .. got the same result.

[nageswaragunupudi]

You have to install oracle 10g client that comes along with oracle server software. After installing, you should configure TNS names. Only then our software runs on your PC.

( note: we need the client software on the user pc for every RDMS. For ADS we put the DLLs in the appn directory. Windows comes with MSSQL client software preloaded. )

Now you need to install Oracle 10g client on every PC in your office that uses your software and configure on every PC the tns names to match what you use in your software. For reasons of safety you shd ensure removal of tools like SQL plus from all those PCs (except yours)

[Rick Lipkin]

nageswaragunupudi

I gathered as much about the client .. was hoping to avoid any complicated setups .. We currently have a 'very ugly' fat client Oracle forms 6i client .. over 1200 some .fmx forms that get depolyed to over 100 servers .

I saw on the Oracle website the Quick 10 or 11i client download .. would this work ( do you think ) or do I need to just go back to the Oracle 10g disk and install the client files ??

Rick Lipkin

[nageswaragunupudi]

I saw on the Oracle website the Quick 10 or 11i client download .. would this work ( do you think ) or do I need to just go back to the Oracle 10g disk and install the client files ??

I did not try the thin clients. But I had enough information from oracle colleagues not to try. Till you read more ( I shall also now look into this seriously) and do some experiements I do not advise you to go for the so called thin clients now.

Please do install oracle 10g client by all means from the package provided by the oracle inc. It does not really take too much space, but it works well and you can develop your applications. One big advantage is during developent of complex applications you need not consider any problems from the client.

was hoping to avoid any complicated setups

It is not really complicated, though not as simple as others. Well we have to do some extra work to get the real power of Oracle which still has no parallel. ( not talking about DB2)

We currently have a 'very ugly' fat client Oracle forms 6i client .. over 1200 some .fmx forms that get depolyed to over 100 servers

It is high time we get rid of the old style forms development and deployment ( unless you use the new tools to make it webbased). But sure if you develop applications in fwh+xharbour we dont need the forms.
You dont have to install oracle developer and forms. Just install the client.

[Rick Lipkin]

nageswaragunupudi

We are actually running Oracle version 9.2.0.6 .. not 10g as I origionally thought. I have the Ora9i cd and have installed the client portion of the 3 disk cd .. still getting the version 7.3.3 error

Any thoughts or pointers ??

RIck

[nageswaragunupudi]

Please check the PATH and make sure it points to the location of the new isntallation. For example the path on this pc is "PATH=C:\oracle\product\10.2.0\client_1\bin;"

[Rick Lipkin]

nageswaragunupudi

The very first statement in the path is where I installed ( what I hoped was the client ) Ora9i .. c:\ora9i\bin .. Perhaps I installed the wrong thing .. would you be so kind as to find me a link to download the proper client .. I noticed in your code .. you have c:\oracle\client .... I do not have any of those paths ..

I would be grateful !!

Thanks
Rick Lipkin

[nageswaragunupudi]

Can you please send me the directory structure of your ora9i directory? you can save it as text file by going to c:\ora9i ( DOS PROMPT ) and issue command "DIR /S > ORADIR.TXT". Please send to my personal email nageswaragunupudi@gmail.com