Page 1 of 1

Connecting to FoxPro Databases

PostPosted: Wed Apr 18, 2018 3:26 pm
by peter_bon
Hi,

I receive a data service via subscription that is comprised of FoxPro databases so I have no capability to natively do anything with FoxPro to work with these databases but want to be able to interrogate the data in different ways than what the interface provides for me with the subscription.
Hence, I have ended up with Servoy as the possible solution for meeting my requirements.

There are a number of databases in the subscription and I have no knowledge of the data model so am needing to step through this slowly.
I have been able to connect to any of the databases ok but there is no PK in any of the dbs.
I have read that I need to establish one and Servoy has also thrown up the message saying the PK is missing.

As best as I can determine from the doco, google and some video tutorials I have come across I need to set the PK in the Row Ident column but when I click on that column I do not get an option for PK, only for row_indent ?
I thought I read somewhere else that you set the Row Ident column as Row Ident but if I do that then I get a message advising Row Identifiers should always not be null but the checkbox against that option will not let me check it off ?

Any help appreciated.

Peter

Re: Connecting to FoxPro Databases

PostPosted: Wed Apr 18, 2018 7:46 pm
by kwpsd
Hi, Peter.

Welcome to the Servoy forum.

To configure VFP tables for Servoy (assuming the connection is set up properly):

1, Add a PK column to DBF table as type Integer (AutoInc) and populate
2. On the DBF table setting in Servoy, set Row Ident to row_ident
3. Also, set the Sequence Type to db identity

row_ident is the pk. You cannot un-check the null for the pk, so just ignore the message. After you change the DBF table settings, you will be able to access the data like an SQL table.

I hope this helps!

Re: Connecting to FoxPro Databases

PostPosted: Thu Apr 19, 2018 9:15 am
by peter_bon
Hi Kim,

Thanks for responding and the welcome. Have got past the PK issues and created a form and loaded some of the database fields within and all working well except...when viewing the form in the Browser client it shows the number of records loaded in the dialogue box and I can increase this by moving the slider down. However, only the initial record is displayed. Stepping through the records does not changed the data displayed which is the first record in the database.

If I go through the same steps however using the Servoy Sample Database then it works fine so there is some other issue at play with the FoxPro database I am connecting with. I have tried different clients, re-loaded everything but run out of options.

Any ideas what may be causing this behaviour ?

Peter

Re: Connecting to FoxPro Databases

PostPosted: Fri Apr 20, 2018 1:44 pm
by peter_bon
I have done some more testing around the issue of the last post and have found the following behaviours:

1. If I make a connection to one of my FoxPro dbs that is small in size (164kb) and generate a new form with only a single field on it all works fine and I can step through the records and the field contents change on screen using the Smart Client or the Web Client.

2. If I make a connection to any of the large databases (1,162,915 kB or 102,494 kB) and generate a new form with only a single (or multiple) fields and try and display the contents of the fields nothing shows apart from the very first record that loads. I can select different records in the Smart Client or the Web Client by either using the slider or typing in a new record number but the client view does not refresh and show the newly selected records data.

Can anyone give advice as to how I can view logs or run some additional tests to try and home in on what is going wrong here. Seems like some kind of performance tuning issue ?

Peter

Re: Connecting to FoxPro Databases

PostPosted: Fri May 11, 2018 11:21 am
by rgansevles
Peter,

This kind of behaviour may happen if the columns(s) selected for row_ident are not actually uniquely identifying unique records.
Servoy depends on the key columns (pk or row_ident) to refer to unique records.

If this is set OK, maybe someting else goes wrong, do you see any error in the logs (/path/to/servoy/appication_server/servoy_log.txt)

Rob

Re: Connecting to FoxPro Databases

PostPosted: Sun May 13, 2018 9:14 am
by peter_bon
Thanks for responding Rob, that is also what I have found to cause the issue but I have not been able to resolve it.

If I open the database in excel and load as many records as it will take and then populate a column to uniquely identifying each record all works well, however I don't want to touch the original database so have tried to get Servoy to provide the primary key.

In an attempt to achieve this I have used the "Add" column function in Servoy, set it as an Integer, Row_Indent, Allow Null ticked off and set Sequence Type as Servoy Seq. Servoy crunches away but will never come back to me and sits there with the busy symbol running and I eventually have to kill Servoy in Task Manager to get it to respond.
There is a warning error in the log (see below) but I have read in another thread this message can be ignored.
2018-05-13 10:35:49,413 WARN [main] com.servoy.j2db.util.Debug - Couldn't get db connection schema Driver com.hxtt.sql.dbf.DBFDriver is not a JDBC4.1 (java 1.7) driver, please upgrade [ ]
The driver I am using anyway is that supplied by Servoy within the program download so not sure why this would be out of date.

Any further advice by anyone appreciated.

Peter

Re: Connecting to FoxPro Databases

PostPosted: Mon May 14, 2018 7:46 pm
by kwpsd
Hi, Peter.

In order to connect to a data table, Servoy requires that the table contains a column designated as a primary key. If you are prohibited from adding the AUTO-INC column to the DBF table per my suggestion, you need to find an existing DBF table column that contains unique data (that is, no duplicate data), and designate that column as the primary key (row_ident). Adding a pk via Servoy and the hxtt driver (which you are attempting to do via Servoy) may not work for a variety of reasons. For example, your data service may not permit you rights to modify the DBF table structure. Also, the hxtt jdbc driver distributed with Servoy is not the latest driver...it is an older version that hxtt allows Servoy to distribute for free. Check out the following link for updated drivers: http://www.hxtt.com/dbf.html. Bottom line is that you need to establish a pk column on the DBF table.

Are you certain the data service will not add the AUTO-INC column for you? It's easy to do and does not add much to the table size. Here is how we do it in VFP (Visual FoxPro):

1. USE table_name
2. COPY TO temp_file_name
3. ZAP (delete all records!)
4. MODI STRU table_name (add the AUTO-INC column here)
5. COPY FROM temp_file_name (COPY FROM will automatically populate the AUTO-INC column)

Takes about 5 minutes!

Re: Connecting to FoxPro Databases

PostPosted: Tue May 15, 2018 5:38 am
by peter_bon
Thanks Kim.
I am not able to get the data service provider to add anything.
There is no single DBF table column that contains unique data, only through using the combination of data from four of the columns are you able to identify unique records.
On that basis is there any other way to connect via Servoy ?

Peter

Re: Connecting to FoxPro Databases

PostPosted: Tue May 15, 2018 10:24 pm
by rgansevles
You can mark multiple colums (so your 4 columns) as row-ident, Servoy will use the composite pk as id for records as long as the 4 columns uniquely identify records.

Rob