VFP8 cursorschema/file structure order issue

I am having problems with file structure field order and cursorschema
definitions. I want to be able to issue select statements without naming all
the fields e.g. select * from table1, but the cursorfill gets an error if the
field order in the table structure doesn't exactly match the order of the
fields in the cursorschema. We have written the cursorschema statements
based on the order of the fields in our test data but some of the client
sites have the fields in a slightly different order due to problems with
converting yonks ago so the system crashes. Why is VFP8 so particular about
the field order anyway?
Is there any way of programmatically changing the order of fields in the
table structure or do I have to go down the route of appending their data
into correctly ordered table structures?

Thanks for any help.
Bernie

Mark
Thu Mar 24 21:51:18 CST 2005

If you use SELECT * in the SelectCmd then the CursorSchema needs to be in
the field order as found in the table. Otherwise, you have to list the
fields in the SELECT statement. The other alternative is to build your
SelectCmd via code in the init of the CA which you could write a builder to
create this code for you, then call the WriteMethod of the CA to place your
generated code into the init.

For an example of this, you can get my free CA Builder by clicking the
FoxPro Tools link at http://www.mctweedle.com Be sure the read through the
comments near the top of the CABuilder PRG file. My builder will not do what
I suggested, but you can certainly get significant help in creating your CA
classes, etc., from this.

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> I am having problems with file structure field order and cursorschema
> definitions. I want to be able to issue select statements without naming
all
> the fields e.g. select * from table1, but the cursorfill gets an error if
the
> field order in the table structure doesn't exactly match the order of the
> fields in the cursorschema. We have written the cursorschema statements
> based on the order of the fields in our test data but some of the client
> sites have the fields in a slightly different order due to problems with
> converting yonks ago so the system crashes. Why is VFP8 so particular
about
> the field order anyway?
> Is there any way of programmatically changing the order of fields in the
> table structure or do I have to go down the route of appending their data
> into correctly ordered table structures?
>
> Thanks for any help.
> Bernie

BernieBeattie
Fri Mar 25 04:31:02 CST 2005

Thanks for that Mark. I did use your cabuilder (and very useful it was too)
to help create all the namelist, fieldlist and cursorschema statements for
our 200 or so tables originally, but this was of course based on our data
structures here. I store these statements in a data dictionary but I need to
use the same statements for all clients. I hadn't appreciated the importance
at the time that the cursorschema had to be in exactly the same order as the
field order. It's a bit of a problem to get round this. I'm going to have
to go down the conversion route of appending client data on to correctly
ordered structures unless anyone knows of a command that allows you to move
fields around in the structure. You can do this by dragging fields during
MODI STRU so I was hoping there was a way to do this programmatically.

Thanks for your help,
Bernie

"Mark McCasland" wrote:

> If you use SELECT * in the SelectCmd then the CursorSchema needs to be in
> the field order as found in the table. Otherwise, you have to list the
> fields in the SELECT statement. The other alternative is to build your
> SelectCmd via code in the init of the CA which you could write a builder to
> create this code for you, then call the WriteMethod of the CA to place your
> generated code into the init.
>
> For an example of this, you can get my free CA Builder by clicking the
> FoxPro Tools link at http://www.mctweedle.com Be sure the read through the
> comments near the top of the CABuilder PRG file. My builder will not do what
> I suggested, but you can certainly get significant help in creating your CA
> classes, etc., from this.
>
> "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
> news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> > I am having problems with file structure field order and cursorschema
> > definitions. I want to be able to issue select statements without naming
> all
> > the fields e.g. select * from table1, but the cursorfill gets an error if
> the
> > field order in the table structure doesn't exactly match the order of the
> > fields in the cursorschema. We have written the cursorschema statements
> > based on the order of the fields in our test data but some of the client
> > sites have the fields in a slightly different order due to problems with
> > converting yonks ago so the system crashes. Why is VFP8 so particular
> about
> > the field order anyway?
> > Is there any way of programmatically changing the order of fields in the
> > table structure or do I have to go down the route of appending their data
> > into correctly ordered table structures?
> >
> > Thanks for any help.
> > Bernie
>
>
>

Anders
Fri Mar 25 08:22:39 CST 2005

This is a multi-part message in MIME format.

------=_NextPart_000_019F_01C5314E.7B59F3F0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit

The Help for CursorSchema in VFP9 says
<<
Note:
In Visual FoxPro 9.0, DEFAULT values and table and field level CHECK
constraints are supported for XML, Native, ADO and ODBC data sources. In
Visual FoxPro 8.0, DEFAULT values and table and field level CHECK
constraints are only supported for an XML data source. For the DEFAULT
values and CHECK constraints to be applied to a cursor, call the CursorFill
method with the lUseSchema parameter set to True (.T.).



Visual FoxPro uses the CursorSchema property at design time to determine the
structure of the cursor to place on the surface of the Data Environment
Designer. Visual FoxPro uses CursorSchema at run time if specified by the
CursorFill method.

Note:
You must make sure that CursorSchema matches and maps to the data
source columns in an acceptable manner.

<<
-Anders

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:585549AF-9C89-45F3-A069-51A3310FA25C@microsoft.com...
> Thanks for that Mark. I did use your cabuilder (and very useful it was
too)
> to help create all the namelist, fieldlist and cursorschema statements for
> our 200 or so tables originally, but this was of course based on our data
> structures here. I store these statements in a data dictionary but I need
to
> use the same statements for all clients. I hadn't appreciated the
importance
> at the time that the cursorschema had to be in exactly the same order as
the
> field order. It's a bit of a problem to get round this. I'm going to
have
> to go down the conversion route of appending client data on to correctly
> ordered structures unless anyone knows of a command that allows you to
move
> fields around in the structure. You can do this by dragging fields during
> MODI STRU so I was hoping there was a way to do this programmatically.
>
> Thanks for your help,
> Bernie
>
> "Mark McCasland" wrote:
>
> > If you use SELECT * in the SelectCmd then the CursorSchema needs to be
in
> > the field order as found in the table. Otherwise, you have to list the
> > fields in the SELECT statement. The other alternative is to build your
> > SelectCmd via code in the init of the CA which you could write a builder
to
> > create this code for you, then call the WriteMethod of the CA to place
your
> > generated code into the init.
> >
> > For an example of this, you can get my free CA Builder by clicking the
> > FoxPro Tools link at http://www.mctweedle.com Be sure the read through
the
> > comments near the top of the CABuilder PRG file. My builder will not do
what
> > I suggested, but you can certainly get significant help in creating your
CA
> > classes, etc., from this.
> >
> > "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in
message
> > news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> > > I am having problems with file structure field order and cursorschema
> > > definitions. I want to be able to issue select statements without
naming
> > all
> > > the fields e.g. select * from table1, but the cursorfill gets an error
if
> > the
> > > field order in the table structure doesn't exactly match the order of
the
> > > fields in the cursorschema. We have written the cursorschema
statements
> > > based on the order of the fields in our test data but some of the
client
> > > sites have the fields in a slightly different order due to problems
with
> > > converting yonks ago so the system crashes. Why is VFP8 so particular
> > about
> > > the field order anyway?
> > > Is there any way of programmatically changing the order of fields in
the
> > > table structure or do I have to go down the route of appending their
data
> > > into correctly ordered table structures?
> > >
> > > Thanks for any help.
> > > Bernie
> >
> >
> >

------=_NextPart_000_019F_01C5314E.7B59F3F0
Content-Type: image/gif;
name="note.gif"
Content-Transfer-Encoding: base64
Content-ID: <019b01c53146$198649b0$0100a8c0@minserver>

R0lGODlhCgAKALMJAP8EAP9dWsbDxtbT1v/PAISGhIyOjAAAAP//zv///wAAAAAAAAAAAAAAAAAA
AAAAACH5BAEAAAkALAAAAAAKAAoAAAQo0Mg5RAAG6X0I3lp3JBlHjORWnEOSIsNRIIVbFkNog/oL
ooegMOiKAAA7

------=_NextPart_000_019F_01C5314E.7B59F3F0--

BernieBeattie
Fri Mar 25 10:31:09 CST 2005

I like the words "acceptable manner" - a bit vague. I have to use the
cursorschema as I need SQL datetimes to become date fields for our
application to save a lot of rewriting. I'm not sure whether your post is
saying that the problem may go away if I use VFP9 instead. I'll give that a
try before going down the conversion route. It's just a bit odd that even
with a native VFP cursoradapter with the all the same fields and sizes but
just in a slightly different order, fails in cursorfill(.T.). I would have
thought this would be "acceptable".

Bernie

"Anders Altberg" wrote:

> The Help for CursorSchema in VFP9 says
> <<
> Note:
> In Visual FoxPro 9.0, DEFAULT values and table and field level CHECK
> constraints are supported for XML, Native, ADO and ODBC data sources. In
> Visual FoxPro 8.0, DEFAULT values and table and field level CHECK
> constraints are only supported for an XML data source. For the DEFAULT
> values and CHECK constraints to be applied to a cursor, call the CursorFill
> method with the lUseSchema parameter set to True (.T.).
>
>
>
> Visual FoxPro uses the CursorSchema property at design time to determine the
> structure of the cursor to place on the surface of the Data Environment
> Designer. Visual FoxPro uses CursorSchema at run time if specified by the
> CursorFill method.
>
> Note:
> You must make sure that CursorSchema matches and maps to the data
> source columns in an acceptable manner.
>
> <<
> -Anders
>
> "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
> news:585549AF-9C89-45F3-A069-51A3310FA25C@microsoft.com...
> > Thanks for that Mark. I did use your cabuilder (and very useful it was
> too)
> > to help create all the namelist, fieldlist and cursorschema statements for
> > our 200 or so tables originally, but this was of course based on our data
> > structures here. I store these statements in a data dictionary but I need
> to
> > use the same statements for all clients. I hadn't appreciated the
> importance
> > at the time that the cursorschema had to be in exactly the same order as
> the
> > field order. It's a bit of a problem to get round this. I'm going to
> have
> > to go down the conversion route of appending client data on to correctly
> > ordered structures unless anyone knows of a command that allows you to
> move
> > fields around in the structure. You can do this by dragging fields during
> > MODI STRU so I was hoping there was a way to do this programmatically.
> >
> > Thanks for your help,
> > Bernie
> >
> > "Mark McCasland" wrote:
> >
> > > If you use SELECT * in the SelectCmd then the CursorSchema needs to be
> in
> > > the field order as found in the table. Otherwise, you have to list the
> > > fields in the SELECT statement. The other alternative is to build your
> > > SelectCmd via code in the init of the CA which you could write a builder
> to
> > > create this code for you, then call the WriteMethod of the CA to place
> your
> > > generated code into the init.
> > >
> > > For an example of this, you can get my free CA Builder by clicking the
> > > FoxPro Tools link at http://www.mctweedle.com Be sure the read through
> the
> > > comments near the top of the CABuilder PRG file. My builder will not do
> what
> > > I suggested, but you can certainly get significant help in creating your
> CA
> > > classes, etc., from this.
> > >
> > > "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in
> message
> > > news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> > > > I am having problems with file structure field order and cursorschema
> > > > definitions. I want to be able to issue select statements without
> naming
> > > all
> > > > the fields e.g. select * from table1, but the cursorfill gets an error
> if
> > > the
> > > > field order in the table structure doesn't exactly match the order of
> the
> > > > fields in the cursorschema. We have written the cursorschema
> statements
> > > > based on the order of the fields in our test data but some of the
> client
> > > > sites have the fields in a slightly different order due to problems
> with
> > > > converting yonks ago so the system crashes. Why is VFP8 so particular
> > > about
> > > > the field order anyway?
> > > > Is there any way of programmatically changing the order of fields in
> the
> > > > table structure or do I have to go down the route of appending their
> data
> > > > into correctly ordered table structures?
> > > >
> > > > Thanks for any help.
> > > > Bernie
> > >
> > >
> > >
>

Mark
Fri Mar 25 22:47:32 CST 2005

Before you go to alot of trouble re-ordering your table structures, keep in
mind that what you see at design time is a convenience for
dragging-and-dropping fields from the CA to the form. You can always
automate with VFP code to extract the fields from your tables using
ADBOBJECTS()/AFIELDS() for local tables and SQLTABLES()/SQLCOLUMNS() for
remote data. Then use TEXT/TEXTMERGE to create your SELECT field_list FROM
<<lcTable>>. Then write that to an INIT hook method to replace the "runtime"
value of the SelectCmd property at runtime. Use AVCXCLASSES to get the list
of classes in your CA classlib, then MODI CLASS ... NOWAIT and
loObject.ReadMethod and WriteMethod to write the method code to these
methods.

This really does not take much code and will save you from a nightmare of a
structure re-ordering.

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:585549AF-9C89-45F3-A069-51A3310FA25C@microsoft.com...
> Thanks for that Mark. I did use your cabuilder (and very useful it was
too)
> to help create all the namelist, fieldlist and cursorschema statements for
> our 200 or so tables originally, but this was of course based on our data
> structures here. I store these statements in a data dictionary but I need
to
> use the same statements for all clients. I hadn't appreciated the
importance
> at the time that the cursorschema had to be in exactly the same order as
the
> field order. It's a bit of a problem to get round this. I'm going to
have
> to go down the conversion route of appending client data on to correctly
> ordered structures unless anyone knows of a command that allows you to
move
> fields around in the structure. You can do this by dragging fields during
> MODI STRU so I was hoping there was a way to do this programmatically.
>
> Thanks for your help,
> Bernie
>
> "Mark McCasland" wrote:
>
> > If you use SELECT * in the SelectCmd then the CursorSchema needs to be
in
> > the field order as found in the table. Otherwise, you have to list the
> > fields in the SELECT statement. The other alternative is to build your
> > SelectCmd via code in the init of the CA which you could write a builder
to
> > create this code for you, then call the WriteMethod of the CA to place
your
> > generated code into the init.
> >
> > For an example of this, you can get my free CA Builder by clicking the
> > FoxPro Tools link at http://www.mctweedle.com Be sure the read through
the
> > comments near the top of the CABuilder PRG file. My builder will not do
what
> > I suggested, but you can certainly get significant help in creating your
CA
> > classes, etc., from this.
> >
> > "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in
message
> > news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> > > I am having problems with file structure field order and cursorschema
> > > definitions. I want to be able to issue select statements without
naming
> > all
> > > the fields e.g. select * from table1, but the cursorfill gets an error
if
> > the
> > > field order in the table structure doesn't exactly match the order of
the
> > > fields in the cursorschema. We have written the cursorschema
statements
> > > based on the order of the fields in our test data but some of the
client
> > > sites have the fields in a slightly different order due to problems
with
> > > converting yonks ago so the system crashes. Why is VFP8 so particular
> > about
> > > the field order anyway?
> > > Is there any way of programmatically changing the order of fields in
the
> > > table structure or do I have to go down the route of appending their
data
> > > into correctly ordered table structures?
> > >
> > > Thanks for any help.
> > > Bernie
> >
> >
> >

Mark
Fri Mar 25 22:48:16 CST 2005

One more thing, give me a couple of days and I'll modify by CABuilder9 to
include this feature.

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:585549AF-9C89-45F3-A069-51A3310FA25C@microsoft.com...
> Thanks for that Mark. I did use your cabuilder (and very useful it was
too)
> to help create all the namelist, fieldlist and cursorschema statements for
> our 200 or so tables originally, but this was of course based on our data
> structures here. I store these statements in a data dictionary but I need
to
> use the same statements for all clients. I hadn't appreciated the
importance
> at the time that the cursorschema had to be in exactly the same order as
the
> field order. It's a bit of a problem to get round this. I'm going to
have
> to go down the conversion route of appending client data on to correctly
> ordered structures unless anyone knows of a command that allows you to
move
> fields around in the structure. You can do this by dragging fields during
> MODI STRU so I was hoping there was a way to do this programmatically.
>
> Thanks for your help,
> Bernie
>
> "Mark McCasland" wrote:
>
> > If you use SELECT * in the SelectCmd then the CursorSchema needs to be
in
> > the field order as found in the table. Otherwise, you have to list the
> > fields in the SELECT statement. The other alternative is to build your
> > SelectCmd via code in the init of the CA which you could write a builder
to
> > create this code for you, then call the WriteMethod of the CA to place
your
> > generated code into the init.
> >
> > For an example of this, you can get my free CA Builder by clicking the
> > FoxPro Tools link at http://www.mctweedle.com Be sure the read through
the
> > comments near the top of the CABuilder PRG file. My builder will not do
what
> > I suggested, but you can certainly get significant help in creating your
CA
> > classes, etc., from this.
> >
> > "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in
message
> > news:76F9A901-FF2D-4BF9-969B-EBE6C55CE3AE@microsoft.com...
> > > I am having problems with file structure field order and cursorschema
> > > definitions. I want to be able to issue select statements without
naming
> > all
> > > the fields e.g. select * from table1, but the cursorfill gets an error
if
> > the
> > > field order in the table structure doesn't exactly match the order of
the
> > > fields in the cursorschema. We have written the cursorschema
statements
> > > based on the order of the fields in our test data but some of the
client
> > > sites have the fields in a slightly different order due to problems
with
> > > converting yonks ago so the system crashes. Why is VFP8 so particular
> > about
> > > the field order anyway?
> > > Is there any way of programmatically changing the order of fields in
the
> > > table structure or do I have to go down the route of appending their
data
> > > into correctly ordered table structures?
> > >
> > > Thanks for any help.
> > > Bernie
> >
> >
> >

Anders
Sat Mar 26 08:14:50 CST 2005

I'll give you a week Mark. <s>
-Anders

"Mark McCasland" <mmccaslaATairmailDOTnet> wrote in message
news:ORkXa8bMFHA.1156@TK2MSFTNGP09.phx.gbl...
> One more thing, give me a couple of days and I'll modify by CABuilder9 to
> include this feature.
>

BernieBeattie
Sat Mar 26 11:41:04 CST 2005

Thanks for all your help Mark. Much appreciated. Will this give me a select
command that matches the cursorschema or the other way about? As I said I
used your cabuilder to give me all the commands I needed but I don't actually
use separate classes per table. I have a data dictionary table which has the
select command, fieldlist, namelist, cursorschema etc. stored in Memo fields,
one record per table, and my class uses this to fill the class properties
prior to cursorfill. A lot of the select statements join other lookup tables
in, but I could probably work out code to replace the 'select table1.*' bit
to select the field names in the same order as the cursorschema instead. Is
that what your code is going to do? Some of the tables have a lot of fields
so I may hit a problem with string length doing this.

Bernie

"Anders Altberg" wrote:

> I'll give you a week Mark. <s>
> -Anders
>
> "Mark McCasland" <mmccaslaATairmailDOTnet> wrote in message
> news:ORkXa8bMFHA.1156@TK2MSFTNGP09.phx.gbl...
> > One more thing, give me a couple of days and I'll modify by CABuilder9 to
> > include this feature.
> >
>
>

Mark
Sat Mar 26 21:42:52 CST 2005

It would change your SelectCmd property from "SELECT * ..." to "SELECT
field_list ..." at runtime. The field_list would be derived from your
CursorSchema property. By expanding the * to a field list, you are no longer
dependent on the column order of the actual source table. For a SelectCmd
with "SELECT table1.* ...", the field list would have to be obtained from
the UpdateNameList property. The property length at runtime is not an issue.
How's that sound? If you or anyone else lurking here have any ideas, please
speak up. Thanks!

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:71225C19-9006-4187-A27C-056605241FD8@microsoft.com...
> Thanks for all your help Mark. Much appreciated. Will this give me a
select
> command that matches the cursorschema or the other way about? As I said I
> used your cabuilder to give me all the commands I needed but I don't
actually
> use separate classes per table. I have a data dictionary table which has
the
> select command, fieldlist, namelist, cursorschema etc. stored in Memo
fields,
> one record per table, and my class uses this to fill the class properties
> prior to cursorfill. A lot of the select statements join other lookup
tables
> in, but I could probably work out code to replace the 'select table1.*'
bit
> to select the field names in the same order as the cursorschema instead.
Is
> that what your code is going to do? Some of the tables have a lot of
fields
> so I may hit a problem with string length doing this.
>
> Bernie
>
> "Anders Altberg" wrote:
>
> > I'll give you a week Mark. <s>
> > -Anders
> >
> > "Mark McCasland" <mmccaslaATairmailDOTnet> wrote in message
> > news:ORkXa8bMFHA.1156@TK2MSFTNGP09.phx.gbl...
> > > One more thing, give me a couple of days and I'll modify by CABuilder9
to
> > > include this feature.
> > >
> >
> >

Mark
Fri Apr 01 15:15:45 CST 2005

I have updated my CABuilder9 download, but now I realize you have VFP8 in
the subject. Regardless you can still download my CABuilder9 files, read the
documentation near the top of the CABuilder9 PRG file, run the builder then
look at the _cabase class and anyother CA subclass it creates in the
classlib to see how it can work. The method of interest for you would be
ExpandSelectStar in the _cabase class.

"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:71225C19-9006-4187-A27C-056605241FD8@microsoft.com...
> Thanks for all your help Mark. Much appreciated. Will this give me a
select
> command that matches the cursorschema or the other way about? As I said I
> used your cabuilder to give me all the commands I needed but I don't
actually
> use separate classes per table. I have a data dictionary table which has
the
> select command, fieldlist, namelist, cursorschema etc. stored in Memo
fields,
> one record per table, and my class uses this to fill the class properties
> prior to cursorfill. A lot of the select statements join other lookup
tables
> in, but I could probably work out code to replace the 'select table1.*'
bit
> to select the field names in the same order as the cursorschema instead.
Is
> that what your code is going to do? Some of the tables have a lot of
fields
> so I may hit a problem with string length doing this.
>
> Bernie
>
> "Anders Altberg" wrote:
>
> > I'll give you a week Mark. <s>
> > -Anders
> >
> > "Mark McCasland" <mmccaslaATairmailDOTnet> wrote in message
> > news:ORkXa8bMFHA.1156@TK2MSFTNGP09.phx.gbl...
> > > One more thing, give me a couple of days and I'll modify by CABuilder9
to
> > > include this feature.
> > >
> >
> >