Need table with record size bigger than 255 3

[alex723]

I have existing VFP9 application where one table needs to have record size increased to 600 fields which is above capacity. Is there any solution to accomplish this? I am not an experienced FoxPro programmer, so I would appreciate more detailed recomendations or links.

 

[JRB-Bldr]

First of all, you should consider if having a single record of 600 fields is really a table in need of normalization.

Not all tables of this kind are just poorly normalized tables, but many (most?) are.

If your table is not fully normalized, then it should be broken into multiple tables for normalization purposes.

Lets assume, for the time being, that your table is normalized to the fullest extent. Then the next solution is to again use multiple tables but this time to extend the field count. Each table utilizing a key field of some sort with which a Relation can be established on a 1-to-1 basis.

Using that approach you can have any number of fields for any given record - just some of those fields are in related Table2 & Table3 & etc.

USE TableC IN 0 ORDER Key1
USE TableB IN 0 ORDER Key1
USE TableA IN 0
SELECT TableA
SET RELATION TO Key1 INTO TableB
SET RELATION TO Key1 INTO TableC Additive

cField1 = TableA.Field1
cField400 = TableB.Field145
cField500 = TableB.Field245
cField600 = TableC.Field90

NOTE - I did this in a hurry so my field number for a given field count might be off, but you can feel free to correct things for me.

Good Luck
JRB-Bldr

 

[cbasoz]

You can use something like SQL server instead.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Alex,

My advice would be to take a hard look at your application, and to ask why one table needs 600 fields. Such a large number suggests a poor database design. Even a couple of hundred fields is suspicious.

Have you got large numbers of repeating fields? For example: SalesJan, SalesFeb, SalesMarch, ....; or perhaps Dept1Total, Dept2Total, .... Things like that?

If so, you should follow JRB-Bldr's advice and try to normalise the stucture. Essentially, you need to think about setting up one-to-many relationships. So, in the first example above, you might have a master customer table, a master months table, and a sales table that has the sales figure for a single customer-month. The sales table would also contain links back to the two master tables.

If you can't find any way to normalise the structure, you could always split the table vertically. For example, if your data is about customers, one table might be, say, the customer's contact details, another might be their credit information, and so on. The point is that each table would be below the 255-field limit.

If all else fails, Cetin's suggestion of moving to a back-end database like SQL Server would avoid the problem. But that would be a drastic move, requiring lots of changes to your application - not to mention a whole lot of new skills to learn.

Ultimately, though, the only sensible solution is to rethink the database design.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[alex723]

Thanks to everybody. What I can see from your comments using back-end database like SQL Server would solve my table limits issue. Now I am confused because this FoxPro application already uses SQL Server 2005 database, not FOXPRO DB. What is wrong that when I add more than 255 fields into this SQL Server database records, my application throws an error reading the table. So my question now would be if this 255 field limit somehow apply to FoxPro code itself.
For example, the application fails with the error executing SQL statement

"SELECT TOP 1 from ResultDB.. TestTable Order BY date_time"

when my TestTable has more than 255 columns. The same statement runs OK when I run it inside SQL Server.

 

[Mike Lewis]

Alex,

What is wrong that when I add more than 255 fields into this SQL Server database records, my application throws an error reading the table. So my question now would be if this 255 field limit somehow apply to FoxPro code itself.

It's true that SQL Server does not have a 255-field limit. But when you retrieve a record from the server and bring it into your application, you are holding it in FoxPro cursor (or remote view, which amounts the same thing). That cursor is itself subject to the 255-field limit, which is why you see the error.

The immediate solution is to only fetch the fields you actually need for the task in hand. So, in your SELECT, you would supply a list of the fields after the TOP 1 clause. Provided there are fewer than 255 fields in that list, the error will go away.

But that's not really an ideal solution. Really, there is something wrong with the database design if you have a table with that many fields. Just because SQL Server allows you to have hundreds of fields in a table, it doesn't mean it's a good thing to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[alex723]

Mike,
Thanks a lot for your explanation. You are probably right regarding a poor database design. This is a legasy application which I was recently assigned to maintain and make some updates such as the adding new columns. This action used to happen previously as a nature of this application until we got to the limit point. My task and goal is to find the easiest solution with less changes of original code. What I have learned from this topic comments that there is one option to split a big table into several smaller and now I may think about your recommendation if I will be able handle it. Unfortunately, I am not a FoxPro programmer ( I do VB6, VB.NET).

Thanks again for your great help.
Alex

 

[cbasoz]

It may or may not be a poor database design (if 600 columns were an indicator of poor design then I guess all those serious database back ends wouldn't support having more than that). A simple example would be an environment modelling grid where 600 columns is less than needed, I would like to have all 1024 columns (or maybe even all those 30000 columns' support in SQL server - were they crazy to introduce support with 30000 sparse columns, I don't think so). It is only VFP's shortcoming to be able to work with 255/254 columns cursors. Of course it may be a poor design too.

You could split it on VFP side through primary key and relate 3 cursors one-to-one if you would do that with VFP.

You are saying that you do VB6 and VB.Net. Then I think (especially after Linq) handling this in .Net is much easier. You could even create an interop dll (kind of an adapter) to be used from VFP if you would still need to handle with VFP.


Cetin Basoz
MS Foxpro MVP, MCP

 

[alex723]

Thanks, Cetin.
I will try split the record on VFP side into 3 cursors.

Also as a FoxPro beginner I ran into a question using Debugger.

When debugging the code such as

Select * From temp_cursor Into Cursor def_cfg

I'd like to watch the content of the cursors similar as other variables or arrays. However, in the Watch window it shows (Expression could not be evaluated).

 

[Mike Lewis]

Alex,

To see the contents of a cursor during debugging, open the "data session" window. You can do that either from the Window menu, or from a button on the standard toolbar, or by typing SET in the command window.

If you need any more details, please post your question in a new thread, as it has nothing to do with table sizes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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