Open Dbase file in foxpro

[AidenMc]

thread184-1138322
In relation to the above thread, I am attempting to do the same and I am not really getting anywhere with it, has anyone managed to do this successfully.
Again like bill these are live dbase tables and I need to be able to query them from foxpro without changing the structure, any help would be greatly appreicated
Thank you
I am using vfp9 and dbase 5 for dos

 

[Mike Lewis]

Well, as I said to Bill, is there any reason why you can't use ODBC rather than ADO? ODBC is much simpler.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[AidenMc]

Mike,
There is no reason but I dont have too much experience using ODBC. So far in my test program the following code is working
lnHandle = sqlstringconnect('driver=Microsoft dBase Driver (*.dbf);dbq=R:')
if lnHandle < 0
* handle error
RETURN
ENDIF

lnhandle does return a value greater than one but when i try to do anything after that say for example

cSource= SQLGETPROP(lnHandle, "datasource")
=MESSAGEBOX("Current Data Source = "+cSource,0,"Connection Results")

cSource is blank so am I missing how to actually open the table ?

Thanks Aiden

 

[Mike Lewis]

Aiden,

You're on the right track. However, rather than interrogating the DataSource property, I suggest you try to access some data.

Try something like this:

lcCmd = "SELECT * FROM Customers"
lnReply = SQLEXEX(lnHandle, lcCmd, "MyCursor")
IF lnReply > 0
  SELECT MyCursor
  BROWSE
ENDIF

In place of Customers, use the name of any table (DBF) file in the specified directory.

By the way, I notice you specified R: as the data source. Are the files in the root of the R drive? If so, I wonder if you need to specify it as R:\.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[AidenMc]

Thanks Mike,
I will give this a try and yes you are correct R is the location of the dbase files. Just on that using select * from table ( that wont convert the dbase table to foxpro ? ) . I will let you know how I get on,
Thanks Again
Aiden

 

[AidenMc]

Mike,
I think I am still missing a step or else its my location below is my code
lcCmd = "SELECT * FROM $groupfx"
lnHandle = sqlstringconnect('driver=Microsoft dBase Driver (*.dbf);dbq=R:\')
lnReply = SQLEXEC(lnHandle, lcCmd, "MyCursor")
IF lnReply > 0
SELECT MyCursor
BROWSE
ENDIF

When debugging lnHandle is = 1, when i step into the sqlexec line lnreply = -1, sorry to be a pain but do you have any other suggestions , Thanks Aiden

 

[Olaf Doschke]

So you have a file R:\$groupfx.dbf ???

$ is not an allowed char for file names, so there has got to be something wrong. Try

cCmd = "SELECT * FROM groupfx.dbf"
lnHandle = sqlstringconnect('driver=Microsoft dBase Driver (*.dbf);dbq=R:\')
lnReply = SQLEXEC(lnHandle, lcCmd, "MyCursor")
IF lnReply > 0  
    SELECT MyCursor  
    BROWSE
ENDIF

Bye, Olaf.

 

[JRB-Bldr]

Here is an alternate approach which utilizes ODBC - not better, just different.

In your workstation make sure that you have a working DSN to your dBase data.
You can check if it is working either within the DSN itself or by creating a UDL test file (Google for test with udl).

Once you have confirmed that your DSN is working you can move to the next step.

In VFP MODIFY or CREATE a Database.
Then within that VFP Database Create a new Connection which uses the tested DSN.
Now with that Database OPEN you can use the Connection to access the ODBC connection via the DSN.

Something like:

OPEN DATABASE MyDBC
SET DATABASE TO MyDBC

* --- Get Connection Handle To New Connection By Name ---
nConnectionHandle = SQLCONNECT("NewConnection")

* --- Set Your ODBC Operations To Be Synchronous ---
=SQLSETPROP(nConnectionHandle, 'asynchronous', .F.)

* --- Perform Your ODBC SQL Command and Put Result Into Cursor: Result ---
nRet = SQLEXEC(nConnectionHandle,'SELECT * FROM ThisTable','Result')

IF nRet = 1
   SELECT Result
   < do whatever >
ENDIF

* --- Terminate Connection ---
=SQLDisconnect(0)
CLOSE DATABASE

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Aiden,

You said "that wont convert the dbase table to foxpro ?"

You said your aim was to query the data, not to convert it. If you wanted to convert it, it would be much simpler to use COPY TO (but that won't convert any memo fields).

Like Olaf, I question the $ sign before the table name. That's not part of the normal syntax, and would probably generate an error.

If that doesn't help, I suggest the following:

lnReply = SQLEXEC(lnHandle, lcCmd, "MyCursor")
IF lnReply > 0  
    SELECT MyCursor  
    BROWSE
ELSE
    AERROR(laErr)
    MESSAGEBOX(laErr(3))
ENDIF

That will give you the error message returned from dBASE.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[AidenMc]

Guys,
Thanks for all your help Olaf, I tried you sample and it worked without the $, only problem I have left with that is the original programmer in the company, had a naming convention for the tables so they all start with $, but its a minor issue the major one was been able to open a dbase table without changng it to foxpro. JRB I am going to give you code a go aswell.
So thanks to the three of you for all your help it's much appreciated.
Best wishes
Aiden

 

[JRB-Bldr]

One issue with the approach that I have suggested is that that every workstation that executes the code is REQUIRED to have the DSN defined and working.

For some situations that is no problem for other situations where there are LOTS of workstations to maintain, it is a hassle which can be avoided with the more direct approach similar to what Olaf has suggested.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

One more note:

To create your UDL file with which you can test your ODBC connection to your database through your workstation DSN's look at:

http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx

Good Luck,
JRB-Bldr

 

[Mike Lewis]

One issue with the approach that I have suggested is that that every workstation that executes the code is REQUIRED to have the DSN defined and working.

That's true. It's the reason I suggested using a connection string rather than a DSN.

But it doesn't make a big difference. A DSN is really just a connection string which is stored somewhere rather than created on the fly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy