SQL Insert 1

[rgw]

Must have used the sql insert command thousands of times but clearly doing something silly. Inserting a record with 6 fields but the _tally kept showing 0. Decided to just add one field and still no luck. If I create a new table and use the same code it works fine. If I use the append blank command it works fine on the real table.

Just telling it to insert into stations (code) values (oapp.temp). Oapp.temp is a memory variable from a class. If I try changing oapp.temp to say '998' it still fails. 'Code' field is a simple 3 character field.
Nothing unusual in the real table. No default values and no primary key.
I must be missing something very basic. Any help much appreciated.

 

[atlopes]

[tt]INSERT[/tt] updates [tt]_TALLY[/tt] only if it includes a [tt]SELECT[/tt] clause.

CREATE CURSOR t1 (f1 Integer)
CREATE CURSOR t2 (f2 Integer)

INSERT INTO t2 VALUES (2)
INSERT INTO t2 VALUES (3)
INSERT INTO t2 VALUES (4)

* make sure _TALLY's value is not contaminated by previous operations
_TALLY = 0

INSERT INTO t1 VALUES (1)

MESSAGEBOX(_TALLY)

INSERT INTO t1 (f1) SELECT f2 FROM t2

MESSAGEBOX(_TALLY)

 

[rgw]

Many thanks. Guess I'm used to just checking everything's good with the tableupdate(.T.) command. Probably most of the updates occur with the select command anyway so this never came up.

 

[Olaf Doschke]

https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/8472wd76(v=vs.80)

tells what influences _TALLY.

And since VFP doesn't support [tt]INSERT INTO table VALUES (valuelist1),(valuelist2),...[/tt] you always would have _TALLY = 1, the only way for multiple inserts is via TABLEUPDATE forwarding new records of the buffer to the DBF or remote database or INSERT ... FROM SELECT.

I don't use _TALLY often anyway, as VFP doesn't have SET ROWCOUNT OFF to let triggers or other stored procs not change _TALLY for the highest level commands happening. You always risk getting a wrong number because of triggers doing cascading deletes or updates or doing anything else, even further inserts into totally different tables. Then _TALLY will reflect a count of changes by this and not what you wanted to count.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Of course, there are other ways of determining how many records you have inserted. The easiest is to compare the record count of the target table immedidatley before the update with its value immediately afer (but if this is a physical table [rather than a cursor] and it is shared with other users, you would have to take that into account).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You could roll your own _TALLY about DBFs when strictly applying an insert trigger to all DBFs going through a procedure keeping a count variable for each ALIAS(), ID(), _VFP.ProcessID and transaction the application code will need to reset this to 0 before doing any operation.

But of course that only works for DBFs in DBCs, free DBFs are not counting, and obviously, this doesn't work with remote data.

When counting whats done in buffers, you can iterate with GETNEXTMODFIED() and determine what's in the pipeline via GETFLDSTATE() of the deleted flag and the value of it.

I wouldn't use such complicated solutions, though. When this is all about knowing how much of a buffer was successfully processed, you have the field state functions and GETNEXTMODIFIED to see whether the buffer is cleared or something remained, you also have the error array of TABLEUPDATE().

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[rgw]

Thanks to all. Always impressed and grateful for the help and knowledge of the VFP community.