Connecting to Oracle 10g XE from VFP

[Stan1243]

I am a VFP(9) Developer. I am completely new to Oracle Databases.
I have some experience with MySQL connectivity and data manipulation though

Question
Using VFP9 Front end.

How do i connect to Oracle 10g Database using ODBC (DSN-Less Connection preferred).

Which ODBC Should I use? (Microsoft ODBC for Oracle / Or Oracle ODBC itself / (which version))
What is the syntax for the connect string.

Will appreciate any help

 

[Olaf Doschke]

You better ask, which ODBC driver is best in a Oracle forum, as that does not depend much on the client attaching to the Oracle DB, but the featureset supported and the ORacle database version.

I also don't know if Oracle is the better vendor of it's ODBC drivers or MS, Oracle should know it's database best and in regard of ODBC perhaps MS has the best knowledge, but I think both vendors where in the comittee defining ODBC.

Well, there are several Oracle forums here at tek-tips.

In regar of how to connect,

http://www.connectionstrings.com

is a good reference in general.

Bye, Olaf.

 

[Mike Lewis]

Which ODBC Should I use? (Microsoft ODBC for Oracle / Or Oracle ODBC itself

Why not try them both? If one of them gives you problems, switch to the other.

In any case, it's not really a FoxPro issue. If you do as Olaf suggests and post your question in an Oracle forum, you'll probably get an informed answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Stan1243]

Mike & olaf,

I still have not got started with the problem.

It was so easy to do the following in VFP/MySQL
lcStringConn="Driver={MySQL ODBC 5.1 Driver};Port=3306"+";Server="+lcServer+";Database="+lcDatabase+";Uid="+lcUser+";Pwd="+lcPassWord

I started with MS ODBC. I finally downloaded the file ODBC msorcl10.dll but when I try to create a new DSN for it. It does not appear in the DSN list of values. And there is no Installer with it.

Oracle site has something call ODAC for the same. but it takes you to its site where you need a username to login to download.

Yes I am Confused as I have spend 2 days...and there is very little help on VFP-Oracle

 

[Mike Lewis]

Stan,

If you are using a connection string, why do you want to find the driver in the "list of DSN values"?

Given that you are familiar with connection strings for MySQL, you can apply the same technique for Oracle. In that case, the driver parameter would be:

Driver={Microsoft ODBC for Oracle} .....

At least, that's what it says at

http://www.connectionstrings.com.

It would be different if you were using a non-Microsoft driver.

Also, you say you need a user name to log into the Oracle download site. Presumably, you (or your company, or your client) is an Oracle customer, so you should be able to obtain the necessary credentials.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Stan1243]

Mike,

I am trying to make a parser using PL/SQL codes which are stripped away from Oracle forms into a text file.
My portion involves parsing all these Procedures & functions and running them against the database. And reporting errors if any. (Like a sort of a pre-compile)

I have the Database XE installed on my PC itself for R&D.

Have you used Oracle with vfp?

Regarding MySql Connect String, I had to download the driver and install. But this is not the case with
:Oracle. I read that you will in addition to the driver, require to Install Oracle Instant Client...with Some changes to ORA/TNS? file etc...

I got the MS ODBC driver for Oracle but I dont know how to use it. Like the one in MYSQL, which appears in the List of Values in the DSN setup.

 

[Olaf Doschke]

The machanism to connect to any database via ODBC is alwys the same, you can use a DSN or a DSNless connection, but drivers must be installed, eg additional to the Oracle XE Server you need the Client installation, which includes an ODBC driver.

For crateing a file dsn, I found instructions here:

http://download.oracle.com/docs/cd/B19306_01/win.102/b14320/prog.htm#i1007399

If you don't see an oracle driver in the list of drivers, you haven't installed it, that's pretty sure.

If you use a 64 bit winds assure you're using the 32bit ODBC Administrator, see here:

http://support.microsoft.com/kb/942976/en-us

Bye, Olaf.

 

[Stan1243]

Olaf,

As right as ever.

The Drivers could not be seen because was using the System32 instead of the %systemdrive%\Windows\SysWoW64 folder.

Once done, I got to see all the drivers. And Yes, there were the drivers from both MS and oracle.
1 Microsoft ODBC for Oracle
2 Oracle in XE

at least one step ahead.

Stage2 ;
The syntax at ConnectString website indicated by mike is

For the current Oracle ODBC Driver from Microsoft
Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

I did the following;

SqlConnectString="Driver={Microsoft ODBC for Oracle};Server=Xe; Uid=myID;Pwd=mypassword;"

lnThisHandle=SQLSTRINGCONNECT(SqlConnectString)

I could not connect?
returned -1

Please advice

 

[Olaf Doschke]

Xe is the product name Server=myServer is asking for the computer name. The same as always, the same as for mysql. If the server is on the same computer you may try local,localhost, 127.0.0.1, simply a dot or not specifying the server at all.

Also, if you get a return value of -1 from sqlconnect or sqlsringconect (or sqlexec by the way), further error info can be retreived via AERROR().

Bye, Olaf.