Programmatic DBT to FPT conversion

[IlyaRabyy]

Colleagues,

Is there a way to convert programmatically, on run time, table files in dBase 2K with memo part (.DBT) into VFP format (DBF/CDX/FPT) from VFP without modifying that bloody DBT along the way?

I tried the following (in a FOR...NEXT cycle, but that's not the point):

USE IN (SELECT("TEMP_DBF")) && Just in case
** Switch dBASE compatibility on:
SET COMPATIBLE DB4

TRY
USE (lcSrcDBF) ALIAS TEMP_DBF NOUPDATE SHARED
COPY TO (lcDstDBF) WITH CDX
SELECT 0
USE (lcDstDBF) ALIAS DB_2_VFP EXCLUSIVE
REINDEX
USE IN DB_2_VFP
CATCH TO loErr
** etc.
SET COMPATIBLE OFF

Every time there's .DBT part in the dB2K table - I get the following error message:

Error #1659, The table has memo fields that cannot be converted while open read-only.
in the Such'n'Such method on line #Such-n-Such
Command/line:
USE (lcSrcDBF) ALIAS TEMP_DBF NOUPDATE SHARED

Apparently, SET COMPATIBLE DB4 does not affect VFP's handling of the .DBT files, and I have already the explanation gotten in my other thread related to the dB-to-VFP topic. So, this time around, I need not an explanation but a solution.

One of those I have already tested was

COPY FILE (FORCEEXT(lcSrcDBF, "*")) TO (FORCEEXT(lcDstDBF, "*"))
SET SAFETY OFF
USE (lcDstDBF) ALIAS TEMP_DBF EXCLUSIVE
SET SAFETY ON

and it wasn't exactly satisfactory: it asked whether to convert the memo to VFP and, if answered "Yes" asked whether to replace "soft" line endings with binary ones. Of course, I set CDPDIALOG OFF and these two questions disappeared, but I am still not sure if this is the best, if not the only, solution.

Any ideas are welcome!


Regards,

Ilya

 

[MrDataGuy]

Since I do not have a copy of dBase 2k (II?) loaded (I think I have it on 5.25 disks someplace) and I am not 100% sure what you are asking this is just an idea.

Instead of converting could you do an upgrade. e.g. create a new VFP table (or cursor) and read record by record the old information into the new table? If by chance VFP can not open the older table, then (I assume you have Dbase 2K) open the information with Dbase and write out the information in some format that can be read by Fox.

For my interest how big (# Records etc.) is the Dbase Table(s). Is this a one time time event, or on going?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[GriffMG]

Ilya

I think the policy here is to tone down your language.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Just an idea though, you might be able to open the dbf using an ODBC driver for the table?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Ilya,

There are dozens of utilities available to do this sort of conversion. I can't make any specific recommendation, but you might want to look at DBF to DBF, which is a low-cost shareware program (

http://dbf-to-dbf.smartcode.com/info.html).

If that's no good for you, a Google search should turn up some others.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[IlyaRabyy]

Colleagues,

since there's no possibility I know of to answer to a specific post (if there is - please kindly guide me), I am answering to those questions I can see at the moment.

1. No, Antony, these tables are not that big, less than 10MB in size; and - no, it is not seemingly one-shot-deal: I am doing the new system in VFP9 to eventually replace the old one in dBase 2K (yes, ver. 2.something, for Windows). So it might take a while, and during this while those tables in dB2K system are going to be updated. Besides, for some business-related considerations, we have to have many certain tables in dB2K format (alas!)

2. Sorry, Griff, that wasn't me, that was my frustration spilling over. I posted similar question on some other forum (hence the reference to another thread) couple days ago, and haven't gotten a response since.
Mea culpa!
My apologies, I will be a good boy from now on. (Am I absolved? )

3. Mike, I'd love to get some 3rd-party component and imbed it into my program, but (there's always "but" if sentence starts with "I would", isn't there? :-( )... The company I'm now working for is on somewhat tightened budget. I am almost confident they won't buy any S/W now, not at least till the end of fiscal year (July 1st).

4. Thank you, Griff, for the ODBC idea! I'm gonna try it.


Regards,

Ilya

 

[Mike Lewis]

since there's no possibility I know of to answer to a specific post (if there is - please kindly guide me), I am answering to those questions I can see at the moment.

That's correct. Messages are not "threaded", as in some other boards. The usual procedure is simply to address the person to whom you are replying (just as you have done).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[IlyaRabyy]

OK, I tried to look up the connection string for the Microsoft's dBASE ODBC, found the following:

Driver={Microsoft dBASE Driver *.dbf)};DriverID=277;Dbq=c:\mydbpath;

Fine and dandy, but when I tried (off the VFP's Command window)

scrConnHndl=SQLSTRINGCONNECT("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\C:\IlyaTestSite\dB2K_2_VFP\dB2K_Tables\Descript.DBF",.T.)

VFP erred with "Not a valid path", although your's truly made it valid for sure by copy-pasting the dir name and the file name and re-checked it twice.
Knowing that dB2K treats a dirrectory with DBFs (and whatnot) as a quasi-database, I tried then

scrConnHndl=SQLSTRINGCONNECT("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\C:\IlyaTestSite\dB2K_2_VFP\dB2K_Tables\",.T.)

and

scrConnHndl=SQLSTRINGCONNECT("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\C:\IlyaTestSite\dB2K_2_VFP\dB2K_Tables",.T.)

but the result was the same: "Invalid path".
After clicking on the OK button, VFP presented me with it's Select Directory dialog; selecting the directory seemingly did create the connection (scrConnHndl was >0); however, an attempt

scrCommand=[use Descript.DBF in 0]
scrRet_N=SQLEXEC(scrConnHndl, scrCommand)

as well as

scrCommand=[use Descript.DBF]
scrRet_N=SQLEXEC(scrConnHndl, scrCommand)

did not, seemingly, open that table file (Data Session window remains blank).

Where do I err?

TIA!


Regards,

Ilya

 

[Olaf Doschke]

1. your path contains a double c:\C:
2. You will not see a dbf in the datasession of your vfp session, but if you don't get an error, the dbf will be open.

Bye, Olaf.

 

[IlyaRabyy]

Thank you, Olaf, I didn't see this double "C:\"!
As for the p. 2 - even after p. 1 was corrected, running
scrRet_N=SQLEXEC(scrConnHndl, [USE Descript.DBF])
returns -1 (means "failed"). The same result with
scrRet_N=SQLEXEC(scrConnHndl, [sele * from Descript.DBF], [C_DESCRIPT])

AIW!

Regards,

Ilya

 

[GriffMG]

Did the odbc work?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

If SQLExec returns -1, you know that you get more specific error info via AERROR.

You gotta talk dBase to dBase, that does not include SQL-Select AFAIK.

Bye, Olaf.

 

[Mike Lewis]

You gotta talk dBase to dBase, that does not include SQL-Select AFAIK.

dBASE IV does support SQL SELECT. Within dBASE itself, you have SET SQL ON before you send it, and SET SQL OFF immediately afterwards. I don't know if that applies to ODBC.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Ok, thanks Mike.

If the dbase ODBC driver works like VFPs, you should be able to set that as an odbc option, like you can specify DELETED=ON and EXCLUSIVE=OFF in a VFP ODBC connection string.

Is there an analogon to EXECSCRIPT in dbase? Seems like you need to execute a short script.

Bye, Olaf.

 

[Mike Lewis]

Is there an analogon to EXECSCRIPT in dbase?

Not that I know of. Only the & operator for macro expansion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[IlyaRabyy]

I finally figured out why trying to open a dB2K DBF in VFP via ODBC connection with the USE command kept failing: since it's SQLEXEC(), it takes only a limited set of SQL commands (that is, according to the error description: DELETE, INSERT, PROCEDURE, SELECT or UPDATE) - and USE is not one of them.
Upon finding this, I re-run it with SELECT * FROM <TableName> command - and it did work... kind of: the Memo fields (there were 3 such fields) were dropped!... Which defeats the purpose, since my main and ultimate goal is exactly the seamless (and harmless to the original files) conversion of these Memo type fields from dB2K to VFP. So, effectively, I am back at square 1.

Any other ideas, colleagues?

TIA!

Regards,

Ilya

 

[Mike Lewis]

Ilya,

It might be worth trying something like this:

SELECT LEFT(SomeMemoField, 254) AS SomeField ....

This will convert a memo field to a character field (but only by dropping all but the first 254 characters). I don't know if it will work with dBASE tables, or if it will solve your problem, but it might be worth a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Dan Freeman]

It looks, from what's been posted, like you're using the dBase driver that ships with Windows. (i.e. the Microsoft dBase driver)

That likely won't work with any version of dBase after dBase IV. If you go to dbase.com, you'll note that they offer an ODBC driver for *current* versions of dBase.

The DBF file format began diverging with dBase IV and Foxpro 1.0, and has continued with each vendor introducing new features (and structure changes) along the way. I don't know if the ODBC driver mentioned at dbase.com would help you, but the website does explicitly mention VFP compatibility.

 

[IlyaRabyy]

Mike,

SELECT LEFT(SomeMemoField, 254) AS SomeField didn't work: erred with "Too few parameters, expected 1" message.

Dan,

thank you for the info! Unfortunately (and I have stated it before, I believe), the company I'm working for is on a very tight budget (and ain't we all, given the state of economy now!), so I have very strong doubts anything would be purchased in the foreseeble future.

Any other ideas, colleagues?

(Truth to be said, there is some "oakwooden-headed frontal attack" ;-) (translating loosely from the Russian idiom) solution: copy files from dB2K app's dir to VFP app's dir, suppress CPDIALOG, and open these tables in VFP; but this would be just the last resort, if everything else fails...)


Regards,

Ilya

 

[Dan Freeman]

(Truth to be said, there is some "oakwooden-headed frontal attack" winky smile (translating loosely from the Russian idiom) solution: copy files from dB2K app's dir to VFP app's dir, suppress CPDIALOG, and open these tables in VFP; but this would be just the last resort, if everything else fails...)

Truth be told, when I've been forced to work with data from another app (dBase, Clipper, etc.) my FIRST preferred approach is the one you describe as your last. ;-)

I *always* prefer to copy the data and work only on the copy. Among other things, I can HONESTLY say "it wasn't me!" when the original data gets corrupted (as it somehow always does).