VFP8 AutoInc field type not supported in views? 
Author Message
 VFP8 AutoInc field type not supported in views?

I just saw this on the Wiki:

http://www.*-*-*.com/ ~AutoIncFieldType

Looks like a showstopper for me. Anyone know of a workaround that doesn't
involve committing the record to find the new value?

We were all fired up to get rid of our PK generation code in favor of the
AutoInc field type, but it looks like this will put the kibosh on that.

Thanks to the gentlemen that posted this on the Wiki, it'll probably save
the VFP community many hours of frustration.

--
William Fields
MCSD - Microsoft Visual FoxPro
MCP - Win2k Pro
US Bankruptcy Court
Phoenix, AZ

Growing old is mandatory
Growing up is optional



Sun, 09 Oct 2005 02:04:26 GMT  
 VFP8 AutoInc field type not supported in views?
Hi, William Fields!
You wrote  on Tue, 22 Apr 2003 11:04:26 -0700:

 WF> I just saw this on the Wiki:

 WF> http://fox.wikis.com/wc.dll?Wiki~AutoIncFieldType

 WF> Looks like a showstopper for me. Anyone know of a workaround that
 WF> doesn't involve committing the record to find the new value?

If you need newly generated PK before real tableupdate (before writing back
your changes to base table) - your choice will definetly be to NewID style
SP, not to this AutoInc approach.

 WF> We were all fired up to get rid of our PK generation code in favor of
 WF> the AutoInc field type, but it looks like this will put the kibosh on
 WF> that.

Hmm, who and when told you that these end-user enhancement will solve all of
your problems? If you'll work with "real" SQL servers, you may know about
the problems that come from being sometimes hard to get newly generated PK
from the backend table back to client application, that need it _before_
commiting and requering cursor (view or SPT). So if you need more freedom -
don't use AutoInc at all.

 WF> Thanks to the gentlemen that posted this on the Wiki, it'll probably
 WF> save the VFP community many hours of frustration.

IMHO these enhancement is not very useful for develoreps - it is more for
ex-Access users and newbees that may ask "Why there is no AutoInc datatype
in VFP if it is in Acceess"... There is no Auto Increment feature in Oracle
DBMS as well, but no one complain about it and no one really suffer from it
:)

WBR, Igor



Sun, 09 Oct 2005 17:49:33 GMT  
 VFP8 AutoInc field type not supported in views?
William - it is a showstopper, as is.
But the entire concept, imnso, is wrong, as posted on the wiki.

to do an insert via a view - do you feel that's the 'right way' to let the
RI kick in?
I certainly don't - but perhaps I've been spoil't on sql server for so
long...

you should not be worrying about the primary key value at all - and the
author on that wiki site IS trying to worry about the primary key.

please let me know your thoughts?
mondo regards [Bill]

--
=============================[remove the dot bob to reply]
William Sanders, Electronic Filing Group. MSDN ISV - VFP/SQL .  mySql/ Sql /
Oracle with VFP - YUP!!
GoTo China and teach C/SaPPdEV on occasion.  http://window.to/vfoxpro

Quote:
> I just saw this on the Wiki:

> http://fox.wikis.com/wc.dll?Wiki~AutoIncFieldType

> Looks like a showstopper for me. Anyone know of a workaround that doesn't
> involve committing the record to find the new value?

> We were all fired up to get rid of our PK generation code in favor of the
> AutoInc field type, but it looks like this will put the kibosh on that.

> Thanks to the gentlemen that posted this on the Wiki, it'll probably save
> the VFP community many hours of frustration.

> --
> William Fields
> MCSD - Microsoft Visual FoxPro
> MCP - Win2k Pro
> US Bankruptcy Court
> Phoenix, AZ

> Growing old is mandatory
> Growing up is optional



Mon, 10 Oct 2005 18:39:54 GMT  
 VFP8 AutoInc field type not supported in views?
I had a big long explanation why it should work in local views, but I just
noticed that this behavior is documented.

From the "Autoincrementing Field Values in Tables" help topic:

"Local Views
Views do not "inherit" autoincrementing behavior of the base table, and the
fields in the view that represent autoincrementing fields in the base table
are read/write. Autoincrementing occurs in the base table when the row or
rows are updated in the base table. You must use the REQUERY( ) function if
you want to refresh the view with the autoincrement value previously
generated."

So I guess it's a moot point and wasted time on Microsoft's part for a
feature that 99% of us won't use.

One reason I would want to know what the autoinc value is up front is in a
parent/child scenario where you need to know the parent ID to use in the
child records before you commit the parent/child records, both wrapped in a
transaction. Is there a better way to approach this scenario?

--
William Fields
MCSD - Microsoft Visual FoxPro
MCP - Win2k Pro
US Bankruptcy Court
Phoenix, AZ

Growing old is mandatory
Growing up is optional



Quote:
> William - it is a showstopper, as is.
> But the entire concept, imnso, is wrong, as posted on the wiki.

> to do an insert via a view - do you feel that's the 'right way' to let the
> RI kick in?
> I certainly don't - but perhaps I've been spoil't on sql server for so
> long...

> you should not be worrying about the primary key value at all - and the
> author on that wiki site IS trying to worry about the primary key.

> please let me know your thoughts?
> mondo regards [Bill]

> --
> =============================[remove the dot bob to reply]
> William Sanders, Electronic Filing Group. MSDN ISV - VFP/SQL .  mySql/ Sql
/
> Oracle with VFP - YUP!!
> GoTo China and teach C/SaPPdEV on occasion.  http://window.to/vfoxpro


> > I just saw this on the Wiki:

> > http://fox.wikis.com/wc.dll?Wiki~AutoIncFieldType

> > Looks like a showstopper for me. Anyone know of a workaround that
doesn't
> > involve committing the record to find the new value?

> > We were all fired up to get rid of our PK generation code in favor of
the
> > AutoInc field type, but it looks like this will put the kibosh on that.

> > Thanks to the gentlemen that posted this on the Wiki, it'll probably
save
> > the VFP community many hours of frustration.

> > --
> > William Fields
> > MCSD - Microsoft Visual FoxPro
> > MCP - Win2k Pro
> > US Bankruptcy Court
> > Phoenix, AZ

> > Growing old is mandatory
> > Growing up is optional



Tue, 11 Oct 2005 00:07:09 GMT  
 VFP8 AutoInc field type not supported in views?
Hello William!

Quote:
> "Local Views
> Views do not "inherit" autoincrementing behavior of the base table, and
the
> fields in the view that represent autoincrementing fields in the base
table
> are read/write. Autoincrementing occurs in the base table when the row or
> rows are updated in the base table. You must use the REQUERY( ) function
if
> you want to refresh the view with the autoincrement value previously
> generated."

> So I guess it's a moot point and wasted time on Microsoft's part for a
> feature that 99% of us won't use.

> One reason I would want to know what the autoinc value is up front is in a
> parent/child scenario where you need to know the parent ID to use in the
> child records before you commit the parent/child records, both wrapped in
a
> transaction. Is there a better way to approach this scenario?

Well, I think the only way that remains for this type of task is to use an
newid-Rountine as the default value of the table. I think this is inherited
in views.

Or you can't use transactions and then have to update-table-and-requery...

Perhaps you could do with temporary autoids, say negative numbers, but
anyway, you'd need to know, which IDs the records will finally get. At least
at that point you'd need the next autoinc values...

Bye, Olaf.



Tue, 11 Oct 2005 00:36:06 GMT  
 VFP8 AutoInc field type not supported in views?

Quote:

>Well, I think the only way that remains for this type of task is to use an
>newid-Rountine as the default value of the table. I think this is inherited
>in views.

>Or you can't use transactions and then have to update-table-and-requery...

>Perhaps you could do with temporary autoids, say negative numbers, but
>anyway, you'd need to know, which IDs the records will finally get. At least
>at that point you'd need the next autoinc values...

Your message suggests using a myAutoInc table to generate the autoids which are
then manually replaced into your own keyfield:

newID = myAutoinc()
insert into testTable .....newID.....

regards

anthony shipley
-
Be a lert! Your country needs lerts.



Tue, 11 Oct 2005 12:34:01 GMT  
 VFP8 AutoInc field type not supported in views?

Quote:
> Your message suggests using a myAutoInc table to generate the autoids
which are
> then manually replaced into your own keyfield:

Yes, this means:
I think the Autoinc won't work in transactions, where you need to know
the primary keys that you'd have to use as foreign keys in other tables.
So you'd have to work with the old solution of your own autoinc
generator. So: In transactions you could only work with an own autoinc-
generator, which means the new vfp8 autoinc is useless then. I don't think
you could mix up own myautoinc-values with autoinc-values in the table,
as there has to be a counter which would not increment as your own
myautoinc-value increments...

If you want to use it, you'll have to really insert the new records, of
which
you need to know the primary keys. And that would make a transaction
useless, when you want the user to be able to a) commit all changes with
end transaction or b)  cancel all changes - including new records - with
rollback. You'd have to remember, which records where new and delete
them additional to the rollback. That's not very useful.

The question was: how can you take advantage of autoinc within transactions
using views for data input. That's a {*filter*}e...

My idea of using temporary negative IDs would work in the views. you could
add a record to view A with -1 as primary key and one or several records
in view B with foreign key -1. Having the views in tablebuffering mode (5),
not
only rowbuffering (3).

Because views are local, you could always begin with -1 in each transaction.
Have a counter as a property of the form or grid for that temporary keys.

The problem is: you'd need the real keys, that autoinc would generate, at
the
point you'd want to write the views back to the tables. The tableupdate of
the
table connected to view A would have to cause an update of the corresponding
foreign keys in the view B, before this is written back. Perhaps you could
requery
that view A, after the real primary keys have been generated. Then this
change of
the primary keys in view A would have to trigger an update of the foreign
Keys in
view B, a cascading update like in referential integrity triggers...

Perhaps someone has an idea how this could work...

Bye, Olaf.



Tue, 11 Oct 2005 17:16:42 GMT  
 VFP8 AutoInc field type not supported in views?
Hi all!

Some more thoughts on autoinc:

I'm very thankful for autoinc in vfp8, since this
is a feature I missed since I entered the vfp world.

It's quite understandable, that autoinc will not be inherited
in views based on tables with an autoinc field:
Views are local and would have to increment some
counter within the underlying table, so that in other
views on other computers the generated value would
not be generated again. That would make the view
somehow not local anymore.

But other Client/Server Databases have the same
problem, like MS SQL Seerver or even MySQL.
You'll have to add a record to get it's primary key,
there's no way around it.

MS SQL Server in addition gives you the latest

or in 2000: SELECT NEWID = SCOPE_IDENTITY().
But that's also after the insert and has it's problems
with multi user environments...

The question is: how would such a problem be solved
with such server databases?

Perhaps you'd have to do something like this everytime
you add a new record:

ROLLBACK
INSERT INTO ... && into the table!
REQUERY() && the view
BEGIN TRANSACTION

One problem is: your view has to requery the new
record, so it has to match it's filtering (parameters).
Also not easy. And you'd lose all changes already
made to old records...

Now the new record would be an old one within the
new transaction. But that way you'd have no overall
rollback. You'd have to remember the records which
weren't there, when the initial transaction began. So
you'd have some manual work to simulate the overall
transaction and  especially the overall rollback...

Bye, Olaf.



Tue, 11 Oct 2005 17:40:40 GMT  
 VFP8 AutoInc field type not supported in views?
Hi, Olaf Doschke!
You wrote  on Fri, 25 Apr 2003 11:16:42 +0200:

 OD> The problem is: you'd need the real keys, that autoinc would generate,
 OD> at the
 OD> point you'd want to write the views back to the tables. The tableupdate
 OD> of the
 OD> table connected to view A would have to cause an update of the
 OD> corresponding foreign keys in the view B, before this is written back.
 OD> Perhaps you could requery
 OD> that view A, after the real primary keys have been generated.

You can't requery the table that perticipate in transaction :( That's the
limitation that require more flex solution, then AutoInc can provide.

 OD> Then this change of
 OD> the primary keys in view A would have to trigger an update of the
 OD> foreign Keys in
 OD> view B, a cascading update like in referential integrity triggers...

 OD> Perhaps someone has an idea how this could work...

IMHO there is no way to have AutoInc, Views and Transactions together.

WBR, Igor



Wed, 12 Oct 2005 00:11:57 GMT  
 VFP8 AutoInc field type not supported in views?
Hi, Olaf Doschke!
You wrote  on Fri, 25 Apr 2003 11:40:40 +0200:

 OD> I'm very thankful for autoinc in vfp8, since this
 OD> is a feature I missed since I entered the vfp world.

I agree that it is handy for newbies, or exAccessomans :)

 OD> But other Client/Server Databases have the same
 OD> problem, like MS SQL Seerver or even MySQL.
 OD> You'll have to add a record to get it's primary key,
 OD> there's no way around it.

In ORACLE there is no AutoInc datatype, and at all there is no such a
consept of AutoIncrementing - you have to do it by hand - retreive the new
value from the corresponding sequence, and determine when and how will you
put this value to the field. So can you do with VFP and NewID style SP.

 OD> MS SQL Server in addition gives you the latest

 OD> or in 2000: SELECT NEWID = SCOPE_IDENTITY().
 OD> But that's also after the insert and has it's problems
 OD> with multi user environments...

That's their limitation IMHO :)

 OD> The question is: how would such a problem be solved
 OD> with such server databases?

Moving to another Backend, with more opportunities :))), or do not use
IDENTITY datatype in SQL server, but use GUID there...

WBR, Igor



Wed, 12 Oct 2005 00:25:58 GMT  
 VFP8 AutoInc field type not supported in views?
Hi Igor!

Quote:
> IMHO there is no way to have AutoInc, Views and Transactions together.

I feared so.

Well, at least I have my own autoinc-generator, as you know...

But I'd try the new AutoInc, if I could, because as a built in function
this should be more stable than any selfmade generator.

It's true that one would/should prefer being able to use transactions
vs. a more stable autoinc solution, as newid normally will be
sufficient.

And one could also use GUID in FoxPro, that could also be easily used
within views and/or transactions. It's only quite long for a primary key
field...in respect of data processing and selecting (joining), not in
respect
of ram memory in the range of formerly hard drive space, and usually
up to 100 GB and more of hard drive space...

How many GUIDs can be generated compared to Autoinc numbers
(per second)?

Well, and last not least, one _can_ do without Transactions by cleverly
using Buffering and tableupdate/tablerevert.

Bye, Olaf.



Wed, 12 Oct 2005 05:50:07 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. VFP8 - SELECT DISTINCT SQL nolonger supports memo fields

2. SQL: Queries of this type are not supported (Error 1814)

3. Remote view field defs WILL NOT match table field defs

4. Double Type fields do not index correctly.

5. View from Sybase/Novell ZENWorks - fetches fields as memo not text

6. Combo Box Fields Not visible properly In View Mode(disabled mode)

7. Does VFP7 support type ahead?

8. VFP ODBC driver support for dynamic cursor type???

9. View not updating the key field

10. Updatable view with default field values not updating

11. Will currency type support the Euro?

12. grid value not being updated in local view if field doesn't lose focus

 

 
Powered by phpBB® Forum Software