Update DBF from field auto increment

[Itamar Lins]

Hi!

How do I update another dbf with a field that has auto increment ?

Auto increment does not accept replace.

Best regards,

Itamar M. Lins Jr.

[CV]

Hi Itamar

This is not a solution for your need.

I have had a similar problem in a database where I used a field with type "=", or TIMESTAMP in a database.

There is an error when I need to copy a record from another database that has also a TIMESTAMP with the same name (when I need to change the struct of that table).

There is a workaround for these, but I don't know which one in your case.

A while ago I tested auto-increment fields and decided to NOT use them (are really dangerous): if you need to change the struct of a table containing auto-increment fields, they are RENUMBERED!

Regards

--

Claudio Voskian

Buenos Aires - Argentina

[Gerald Drouillard]

What rdd are you using?  

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/f37d3689-a16d-4515-848e-9229111bda27n%40googlegroups.com.

[Itamar Lins]

Hi! 
Use DBF CDX Harbour default set

Best regards,
Itamar M. Lins Jr.

[Gerald Drouillard]

What database are you using on the backend?  Postgres?

Postgres allows uploading via csv with the recno field.

For tables in PG I use this as the recno field:

sr_recno smallint NOT NULL GENERATED BY DEFAULT AS IDENTITY;

Instead of smallint you can also use int or bigint

Technically, with the sqlrdd, the recno field on a dbappend is populated with a sql call see "getnextrecordnumber()" in sqlrdd2.prg

On Wed, Apr 10, 2024 at 9:57 AM Itamar Lins <itama...@gmail.com> wrote:

Hi!

Tks for response. But, how to, using in SQL daemon engene ? How to SQL daemon engene import data ?

Best regards,
Itamar M. Lins Jr.

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/34f96d7c-8c2a-47fd-b6a1-4bab64549a53n%40googlegroups.com.

[Itamar Lins]

Hi!

->Gerald Drouillard
I use DBF import to DBF.

Best regards,

Itamar M. Lins Jr.

[Itamar Lins]

Hi!

Becouse SQL daemon engene, import field auto incremente, etc... DBF not accept this ? APPEND FROM Auto increment DATA TIME... ?

Best regards,
Itamar M. Lins Jr.

Em quarta-feira, 10 de abril de 2024 às 11:45:09 UTC-3, Gerald Drouillard escreveu:

[poopall]

I dont know why you need auto increment, as you haven't said. 

I personally have found them, an issue when using the auto increment as a reference and a record ID, especially if you want to merge 2 databases from 2 different systems, you may end up with duplicates

All my tables have a systems generated GUID. I would suggest you generate a GUID as key  that references any data tables to each other that way you are almost certain these keys will be unique regardless

I use windows so I use something like this to generate the keys

uuid :=   lower(SUBSTR(CREATEOBJECT( "Scriptlet.TypeLib" ):Guid,2,36))

[Cotswold GB]

I use AutoIncrement, or AutoNumber fields in most databases. 

In fact some SQL databases automatically add such a field. 

But I never use them for a reference or relationship table to 

table. If you get corruption you can never replace lost fields 

with AutoNumeric fields and therefore recover orphaned records. 

I will have a field in a System Master Table that I take, use the 

number, then increment it on after adding a new record in a 

Table. I am then in control of any joined tables that use the 

fields number as the join in a normalised database.

The AutoNumber can be handy in an auditing situation as they 

will indicate any incorrectly deleted records. Ticketing Systems 

that have cash trades could have incorrectly deleted records. 

In those cases the AutoNumber shows up the fraud.

[Itamar Lins]

Hi!
I used it in my dbf only once, to generate automatic numbering in an old dbf, but I already had to stop using it, since using the auto increment field, I will no longer be able to modify the DBF, add any more fields to this same DBF if necessary, because I will not be able to import your data into the new structure.
That's a shame. It would be nice if we had an "alter table" in DBF that accepted this field.

Best regards,
Itamar M. Lins Jr.

[Steve Litt]

Itamar Lins said on Fri, 12 Apr 2024 05:32:18 -0700 (PDT)

>Hi!
>I used it in my dbf only once, to generate automatic numbering in an
>old dbf, but I already had to stop using it, since using the auto
>increment field, I will no longer be able to modify the DBF, add any
>more fields to this same DBF if necessary, because I will not be able
>to import your data into the new structure.
>That's a shame. It would be nice if we had an "alter table" in DBF
>that accepted this field.

Sounds to me like I'm not going to use auto increment for anything.

Question for those who don't use auto-increment: Do you lock the table
before finding the highest primary key, incrementing it, and writing a
new blank record to the DBF, and then updating that record with the
desired data? I'd think this would lessen the race condition window.

Thanks,

SteveT

Steve Litt

Autumn 2023 featured book: Rapid Learning for the 21st Century
http://www.troubleshooters.com/rl21

[Itamar M. Lins Jr. Lins]

Hi!

Addendum of Changelog
...
 * src/rdd/dbf1.c
    * do not copy automatically updated fields when destination area
      is not empty
    * set DBTF_CPYCTR to indicate that counters should be copied from
      source to destination area when original value of automatically
      updated fields are transferred
    ; Now DBF* RDDs in Harbour respects the following rules for record
      transfer operations (COPY TO / APPEND FROM) with automatically
      updated fields:
         - COPY TO transfers original values to destination table and
           finally copy counters from the source table to destination one
           so autoincrement fields in both tables after next append will be
           initialized with the same values regardless of number of copied
           records - even if only single record is copied then counters in
           destination table will inherit next values for new record from
           the source table. Also values of RowVer and ModTime fields are
           copied from source to destination table and not updated during
           transfer operation.
         - APPEND FROM works like COPY TO (original field values and then
           counters are copied to destination table) if the source table
           supports counters and destination table is empty and FLocked()
           or opened in exclusive mode.
           If source table does not support counters for given fields, i.e.
           it is processed by transfer RDD like DELIM or SDF (RDT_TRANSFER)
           or destination table is not empty or opened in shared mode and
           FLock is not set when APPEND FROM is executed then automatically
           updated fields (counters, RowVer, ModTime) are not copied and
           initialized with new values like for each new record added to
           destination table.

...

So I don't use append from, I don't use copy to, because I change some numeric field structures to characters for example.
And I change the order of the fields. It's not an everyday routine, but I may need it after a long time.

Best regards,
Itamar M. Lins Jr.

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/20240412151518.19475eaf%40mydesk.domain.cxm.

[Itamar Lins]

Hi!

More tests!
This way there is no error:
aStruct := {{"autoinc","I:+",4,0}}

This way the error occurs
aStruct := {{"autoinc","+",4,0}}
Attention!
Both ways will create the field normally as auto increment. But the last example does not accept replace when I import the dbf.

Best regards,
Itamar M. Lins Jr.