Insert - SQL Command

[wsjames]

Made a modification to a routine I use to transfer records from one table ("statdtpt") to another ("arcdtpt"). I then, delete redundant records from "statdtpt". I recently opted to use the code below to rid the use of "SCATTER" - which by the way worked fine. It would be simple enough to go back to the old method, but my curious nature is getting the better of me.

The function below works. However, very sporadic. Records are being transferred most of the time (verified), but from time to time, the "pushError" routine (writes error to error table) is called; suggesting a problem. Happens with some regularity.

The calling program sets the passing variable "arcXcde" - numeric and <9,999,999,999. The field "xcde" is defined as n(10).

Things to note:
- Program written using VFP9.0 SP2
- Both table structures are identical (verified several times)
- When I manually run the routine I am unable to get it to fail... Likely meaningless due to sporadic nature of routine.

What am I missing?


FUNCTION xTransferRecords
LPARAMETERS arcXcde

LOCAL rflag
TRY
INSERT INTO arcdtpt ;
SELECT * FROM statdtpt WHERE xcde = arcXcde
DELETE FOR xcde = arcXcde IN statdtpt
rflag = .T.
CATCH
DO pushError WITH 'Unable to Archive DTPT records.', 'archive'
rflag = .F.
ENDTRY
RETURN rflag

 

[Olaf Doschke]

Well, if you want to know what's wrong, then don't do such an "anonymous" CATCH, instead CATCH TO loException and also pass that on to pushError.

Most probably error is the type of arcXcde. Second culprit could be any non matching DBF open with alias statdtpt having another structure. loException will tell you.
Besides that pushError should also take AERROR and ASTACKINFO and log it, sometimes it's very enlightening to know where tha call came from.

Bye, Olaf.

 

[vgulielmus]

Another two possibilities are (1) a different order of the fields in the two tables and (2) a read-only cursor / table arcdtpt

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[wsjames]

Table structure (field order and definitions) is exactly the same. Tables are being opened with read/write permissions. Will use Olaf's recommendation to extract more info related to error. Will report back findings. Thanks Olaf!

 

[Olaf Doschke]

Other values of interest for logging are DBF("arcdtpt"), DBF("statdtpt"). That still addresses the suspicion these aliases are not what you think they are. Knowing the files underlying these alias names, you can also check the readonly state of DBFs, the loException.Message would also indicate such a problem. Instead of extending the parameterization for these infos, you can of course attach the DBF() strings to the message you pass on.

Bye, Olaf.

 

[TamarGranor]

I'd also make sure to use mdot for the variable arcXcde:

INSERT INTO arcdtpt ;
SELECT * FROM statdtpt WHERE xcde = m.arcXcde
DELETE FOR xcde = m.arcXcde IN statdtpt

Tamar

 

[Dan Freeman]

In addition to what the others said, I'd break that into two Try/Catch blocks. If the insert fails you probably shouldn't even try the delete, but currently you barrel on through.

 

[wsjames]

To my surprise, I get the following error message, "Cannot update the cursor ARCDTPT, since it is read-only." Surprising because:
- Tables are not included (or compiled) in the project.
- Table is opened using the same "OpenTable" routine I've always used.
- The program does not modify table attributes.
- With very few exceptions, the routine described in my initial comment works.

A couple things I'm not quite understanding:
- The error refers to the table "ARCDTPT" as a cursor. Is this because its "read-only"?
- The error indicates the table is opened "read-only". Very confusing... Did not have this issue when using the "Scatter" method below.


FUNCTION xTransferRecords
LOCAL rflag
RELEASE xMemvar
SELECT statdtpt
TRY
SCAN FOR xcde = dlcm.xcde
SCATTER MEMO NAME xMemvar
DELETE IN statdtpt
INSERT INTO &arcdtpt_table FROM NAME xMemvar
RELEASE xMemvar
ENDSCAN
rflag = .T.
CATCH
DO pushError WITH 'Unable to Archive DTPT records.', 'archive'
rflag = .F.
ENDTRY
RETURN rflag

 

[Olaf Doschke]

Read-only can have serveral other reasons than being compiled into the executable. The file can be readonly (less probable), the table could be USED with NOUPDATE, you could have done a query INTO CURSOR ARCDTPT and not work on the ARCDTPT dbf (That's why I said DBF("arcdtpt") would be nice to know), is it really the table you think you insert into?

To answer the reason for the error message talking about "cursor":

USE foxcode NOUPDATE
APPEND BLANK

This also talks of the "cursor" FOXCODE, though it is a DBF.

Mostly we and VFP refers to a cursor as a temporary DBF created by CREATE CURSOR or a query INTO CURSOR or with a view query (then this is specifically called view cursor). Cursors are typically refered to things NOT being DBFs directly, though a cursor also is a DBF, but with tmp extension in the TEMP directory (unless in the corner case of a filter cursor, which is inded a filter on the dbf).

Still the error message here makes no difference. There is a very commonly used Cursor not recognized as such, the Cursor class/object of a table in the DE. And perhaps you find something about that term buried deep in the help texts, that cursor also is a general term like alias, for example also in CURSORSETPROP, which actually sets properties of aliases/workareas, no matter if what they are holding.

Bye, Olaf.

 

[Olaf Doschke]

The alternative way you chose only has one thing I'd change: The macro substitution could be a name expression here, which would play a role, if it is the DBF file name and that file some day could be in a path including a space in any part of the path name.

A difference here might really be the arcdtpt_table is really the full dbf file name, but you don't show how this variable is set.

Bye, Olaf.