How can I convert a Paradox db to Vfp9 dbf

[manfrid]

I tried to use the odbc for a connection to lcMyfile (patdb.db)

lcdsn="DRIVER=Driver do MICROSOFT Paradox DRIVER (*.DB);" + "DBQ="+lcMyfile

lnConnHandle = SQLSTRINGCONNECT(lcdsn)

IF lnConnHandle > 0
lnResult = SQLTABLES(lnConnHandle, 'TABLE')
IF lnResult > 0
SELECT SQLResult

lcSQLCommand = "SELECT * FROM [" + ALLTRIM (SQLResult.table_name) + "]"

lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')

....


but it does not work!
first I got

lnConnHandle= -1

I am working with vfp9 on XP

any help or better Example

thank you all
manfrid

 

[Mike Lewis]

Manfrid,

Where did your connection string come from? It's a long time since I did Paradox, but the driver name doesn't look right to me. I think you need something like the following:

Driver={Microsoft Paradox Driver (*.db )};

Also, I believe the DBQ should point to the directory containing the DB files, not to a specific file.

On that basis, you might try the following:

lcdsn= ;
  "{Microsoft Paradox Driver (*.db )};DBQ=" + ;
  JUSTPATH(lcMyfile)

But I have no way of testing it, so can't be sure if it's right.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[manfrid]

yes this helped me
I used this

lcdsn="Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 7.X;";
+"DefaultDir=c:\fox\;Dbq=c:\fox\;CollatingSequence=ASCII;"
lnConnHandle = SQLSTRINGCONNECT(lcdsn)

I got lnConnHandle=1 which means it connects.

lnResult = SQLTABLES(lnConnHandle, 'TABLE')

also lnResult=1

lcSELECT SQLResult
SQLCommand = "SELECT * FROM [" + ALLTRIM (SQLResult.table_name) + "]"

lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')

after that I got
lnGetData=-1

I can also send you my Paradox db if this can help
thank you again
manfrid

 

[Mike Lewis]

Manfrid,

You're on the right track now.

The next thing you need to do is to look at the Newdata cursor which you have just created.

You should be able to do this:

SELECT NewData
BROWSE

That should show you your Paradox data. After that, you can store it or do whatever else you need to do with it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[manfrid]

Sorry Mike!
but the last step " lnGetData= -1 " does not function?
any idea?

manfrid

 

[Mike Lewis]

Manfrid,

Sorry, I didn't see the minus in front of the one. That makes all the difference.

First, I assume the following is just a typing error:

[b]lc[/b]SELECT SQLResult   
SQLCommand = "SELECT * FROM [" + ;
  ALLTRIM (SQLResult.table_name) + "]"

The "lc" belongs in front of SQLCommand, not the SELECT.

Also, I see you are using square brackets to delimit the table name. Is that the usual way of doing it in Paradox? It's been a long time since I used Paradox, and don't remember the details.

In general with this type of problem, it helps if you can run the SQL in the database environment. Can you go into Paradox and actually run a command there? If so, do this:

1. Copy the contents of lcSQLCommand to the clipboard:

_CLIPTEXT = lcSQLCommand.

2. Paste that into the Paradox command line. See what error you get.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[manfrid]

Sorry again, in fact I used "Select" but wrote lcSelect (my fault)
now I used this instead

lcSQLCommand = "SELECT * FROM " +ALLTRIM (SQLResult.table_name)

lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')

but got same lnGetData = -1

SQLResult.table_name has got the name of my Paradox-DB correctly

I have no possibilty to go into Paradox.

Thanks for any Help

Manfrid

 

[Mike Lewis]

Manfrid,

OK, the next thing to do is to trap the error.

Change your code to the following:

lcSQLCommand = "SELECT * FROM " + ;
  ALLTRIM (SQLResult.table_name)       
lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')
IF lnGetData < 0
  AERROR(laErrorArray)
  MESSAGEBOX(laErrorArray(3))
ENDIF

That will tell you why Paradox is rejecting the SQL.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[manfrid]

Mike,

now I get this error message:

[Microsoft][ODBC Paradox Driver] The external Table has not the expected Format.

The Paradox Table I have is a Paradox 7 Table.

Manfrid

 

[Mike Lewis]

Manfrid,

Sounds like you've got the wrong version of the driver. The tables are in 7.x format, but maybe you've got an earlier driver. You can check that by going into the ODBC applet in Control Panel and checking the Drivers tab.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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