Connecting to FoxPro Databases

Questions and answers regarding general SQL and backend databases

Connecting to FoxPro Databases

Postby peter_bon » Wed Apr 18, 2018 3:26 pm

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
peter_bon
 
Posts: 5
Joined: Wed Apr 18, 2018 2:58 pm

Re: Connecting to FoxPro Databases

Postby kwpsd » Wed Apr 18, 2018 7:46 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Connecting to FoxPro Databases

Postby peter_bon » Thu Apr 19, 2018 9:15 am

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
peter_bon
 
Posts: 5
Joined: Wed Apr 18, 2018 2:58 pm

Re: Connecting to FoxPro Databases

Postby peter_bon » Fri Apr 20, 2018 1:44 pm

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
peter_bon
 
Posts: 5
Joined: Wed Apr 18, 2018 2:58 pm

Re: Connecting to FoxPro Databases

Postby rgansevles » Fri May 11, 2018 11:21 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Connecting to FoxPro Databases

Postby peter_bon » Sun May 13, 2018 9:14 am

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
peter_bon
 
Posts: 5
Joined: Wed Apr 18, 2018 2:58 pm

Re: Connecting to FoxPro Databases

Postby kwpsd » Mon May 14, 2018 7:46 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Connecting to FoxPro Databases

Postby peter_bon » Tue May 15, 2018 5:38 am

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
peter_bon
 
Posts: 5
Joined: Wed Apr 18, 2018 2:58 pm

Re: Connecting to FoxPro Databases

Postby rgansevles » Tue May 15, 2018 10:24 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests