PDA

View Full Version : Select fron a vfp table that is not part of a database



avianmanagement
2017-04-07, 12:29
I am writing some code to create/update lianja tables from my Xcase model.

I need to be able to do select statements from Lianja against the vfp table created by xcase so I can scan though and do the work needed to crate / update table, create indexes in Lianja etc.

Since the tables don't belong to a datatbase what is the best way to do this? I assume I have to have database to use ODBC?

I have tried with a test table ( this one does belong to a database)

select * from c:\1temp\daddress into cursor temp1

and get an error

Fri Apr 7 18:12:12 2017
**** Lianja error ****
DADDRESS.LA10AUTHOR
^
Fatal I/O error writing record 1 to table - errno 22



David Le Mesurier

phabio
2017-04-07, 12:34
Hi David,
in my opinion, the best thing is to create an ODBC link to your xcase table, then create a VT on that table.

With a old FoxPro table, I do that.

ciao
Fabio

avianmanagement
2017-04-07, 12:39
Hi Fabio,

Can you make ODBC links to tables that are not part of a database?

The code I have looked at for the ODBC connection wants the name of the database
David

josipradnik
2017-04-07, 13:00
Hi David,

For free VFP table you need VFP ODBC driver.

Read these posts:
http://www.lianja.com/community/showthread.php?3598-Recommended-VFP-ODBC-Driver
or in [Answers] collection
http://www.lianja.com/community/showthread.php?3109-Answers-VFP-ODBC-Driver

When you install correct driver, you can create VFP ODBC connection

The windows ODBC utility will be located in different locations depending if you are using a 32 bit or 64 bit system.
32 bit: C:\Windows\System32\odbcad32.exe
64 bit: C:\Windows\SysWOW64\odbcad32.exe

Josip

avianmanagement
2017-04-07, 13:05
Thanks Josip,

I'll take a look at that

David

josipradnik
2017-04-07, 13:08
Hi David,

In the link I mentioned there is an interesting post regarding VFPOLEDB.
Don't know what was result, but interesting alternative maybe for quick and dirty work, if you don't have ODBC driver installed yet...

---------
Q:
I know vfpoledb is really just activex.. Are there any issues using vfpoledb to connect to my vfp tables and doing updates... I'm going to be running parallel with our old system... I was told that lianja itself can open vfp tables and update them, however, the indexes don't update...
A:
I have not tried it but if you install the vfpoledb driver then in Lianja;


SET DEBUG ON
vfpda = createObject("OLEDB.Connection")
I'd be interested to see if it loads and what is in debug_client.txt
-----

Josip

avianmanagement
2017-04-07, 13:14
I've just tried to copy one of the tables to a Lianja table using
use c:\1temp\ddent noupdate
copy to c:\1temp\l_ddent with production

and get

Fri Apr 7 21:10:46 2017
**** Lianja error ****
copy to c:\1temp\l_ddent with production
^
ERROR - invalid record length

Hank tells me that the Xcase case tables are in foxpro 2.x rather than VFP type so maybe that is an issue.

David

josipradnik
2017-04-07, 13:39
Fabio worked with 2.x
Memo was the problem. Type General instead, as I understand.

---

I'm try to access FoxPro 2.6 data. From console, if I write:
use E:\cogema\dati\articoli
the table are opened correctly, I can use this way?
I've also try to configure ODBC to access the data through ODBC, then I've created a VT.
The first time, I've opened the table... then, close and open Lianja, the table is showed as "Encrypted".. Why?
A:
Read/Write to FoxPro tables is not supported. You should be able to import it into a Lianja database
'encrypted' will be displayed if there is a problem with the VT and the data cannot be accessed.
Try closing the database and deleting the VT files in the C:\Lianja\data\cogema\ folder, then rebuild the VT.
Q2:
Found! the problem is the MEMO field... now I try to reinsert the general.
----

Josip

avianmanagement
2017-04-07, 14:33
Thanks Josip

There are a lot of memo fields in DDENT,

How would one copy it then if it has memo fields?

David

barrymavin
2017-04-07, 20:26
Hi David,

Send me the table attached to a ticket and I will investigate.

Lianja can read VFP and FP tables so that should not be an issue. It must be something else.

avianmanagement
2017-04-07, 22:57
Thanks Barry,

I've submitted a ticket with the DDENT files.

David

barrymavin
2017-04-08, 03:25
Hi David,

I am able to USE that table and COPY TO another_name to put it in Lianja format.

SQL SELECT is working Ok in 3,.4 with that table too although its not really supported as you need to convert to Lianja format so that the query optimizer can do its work.

I will make 3.4RC available to you in the beta directory but bear in mind this includes the new CodeAssistant and Intellisense.

I will close the ticket.

1459

avianmanagement
2017-04-08, 04:04
Thanks Barry,

I'll download that and try.

The sql stuff I ned to do on this is very simple and I'd rather keep it all in selects into a cursor for what I want to do.

David

barrymavin
2017-04-08, 04:19
Hi David,

Just USE tablename ALIAS whatever then perform your SQL SELECTSs.

Just an FYI, Lianja supports the following XBase file formats natively readonly.

VFP
FoxPro 2.x
Clipper

avianmanagement
2017-04-08, 04:21
Thanks Barry

This will allow me to use Xcase for all my data modelling and have a prg create / update all the tables, indexes etc in Lianja.

David

barrymavin
2017-04-08, 04:28
Hi David,

I've given you prerelease access to the beta directory but you may need to logout and login again to the main site.

avianmanagement
2017-04-08, 04:40
Hi Barry,

I logged out then tried to log in, put wrong password first time and system told me that

Entered correct one ( I think) and get

avianmanagement
2017-04-08, 04:42
Shut down browser and tried again and it worked

barrymavin
2017-04-08, 04:48
Shut down browser and tried again and it worked

Yes probably a caged cookies.

Lianja 3.4RC4 is currently uploading so wait about 5 minutes.

Enjoy.

avianmanagement
2017-04-08, 04:49
Got in and was download the beta, cancelled that now and will wait a while and get RC4

Thanks

avianmanagement
2017-04-08, 07:31
That now works fine.

Thank you