How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Russell H. Your Own Question

Russell H.
Russell H., Programmer & Coder
Category: Programming
Satisfied Customers: 9963
Experience:  Programmer & Coder
13568747
Type Your Programming Question Here...
Russell H. is online now
A new question is answered every 9 seconds

Below is the code I am using. My problem is I am

Customer Question

Below is the code I am using. My problem is I am transferring data, 200 records at a time from my STORE database to my PAYROLL database. My PAYROLL database has a row_ID is defined as SERIAL which I believe may be my first issue, but I am not sure.
The second issue, there are 673 records in the STORE database, but I only get a total of 4 records placed in my PAYROLL database. It appears to send the last record for each 200 sent. Any ideas on how to insert the whole 673 records into the PAYROLL database from the STORE database?
DISPLAY "Step 1 - Downloading interface records"
CALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
ELSE
DELETE FROM inter WHERE inter.ind IS NULL
LET ct1 = 0 {1 to 200}
LET ct2 = 0 {200/400/600}
LET ct3 = 0 {record count}
LET repeat = 0
LET reread = TRUE
LET query_text = "SELECT * FROM inter WHERE ", where_part CLIPPED
WHILE reread
PREPARE qry_pay1 FROM query_text
DECLARE pay1 CURSOR FOR qry_pay1
FOREACH pay1 INTO t_int.*
IF ct1 = 200 THEN
LET repeat = 1
EXIT foreach
END IF
LET ct3 = ct3 + 1
IF ct3 <= ct2 THEN CONTINUE FOREACH END IF
LET ct1 = ct1 + 1
LET x_int[ct1].* = t_int.*
END FOREACH
# ***** PAYROLL database now that the STORE database has
# ***** updated
CALL dbiStartDatabase("") RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
CALL libErrDialog("Error","Cannot connect to database","stop","OK")
EXIT WHILE
ELSE
BEGIN WORK
FOR i = 1 TO ct1
IF SQLCA.SQLERRD[3] = 0 THEN
IF dbengine = "IFX" THEN
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,0,?)"
ELSE
#dbengine = "MSV"
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,?)"
END IF
PREPARE ins1 FROM sel_stmt
EXECUTE ins1 USING x_int.ind,
x_int.orgn_proj,
x_int.account,
x_int.fringe,
x_int.amount,
x_int.ctrl_no,
x_int.pay_run
LET rec_cnt1 = rec_cnt1 + 1
END IF
END FOR
IF repeat = 1 THEN
# ***** the STORE database
CALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
END IF
LET ct2 = ct2 + 200
LET ct1 = 0
LET ct3 = 0
LET repeat = 0
ELSE
LET reread = FALSE
END IF
END IF #STORE database not found
END WHILE
END IF #payroll database found
Submitted: 12 months ago.
Category: Programming
Customer: replied 12 months ago.
sorry let me send you the code again.. Missing some steps:DISPLAY "Step 1 - Downloading interface records"CALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
ELSE
DELETE FROM inter WHERE inter.ind IS NULL
LET ct1 = 0 {1 to 200}
LET ct2 = 0 {200/400/600}
LET ct3 = 0 {record count}
LET repeat = 0
LET reread = TRUE
LET query_text = "SELECT * FROM inter WHERE ", where_part CLIPPEDWHILE reread
PREPARE qry_pay1 FROM query_text
DECLARE pay1 CURSOR FOR qry_pay1
FOREACH pay1 INTO t_int.*
IF ct1 = 200 THEN
LET repeat = 1
EXIT foreach
END IFLET ct3 = ct3 + 1
IF ct3 <= ct2 THEN CONTINUE FOREACH END IF
LET ct1 = ct1 + 1
LET x_int[ct1].* = t_int.*
END FOREACH
# ***** PAYROLL database now that the STORE database has
# ***** updatedCALL dbiStartDatabase("") RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
CALL libErrDialog("Error","Cannot connect to database","stop","OK")
EXIT WHILE
ELSEBEGIN WORK
FOR i = 1 TO ct1
IF SQLCA.SQLERRD[3] = 0 THEN
IF dbengine = "IFX" THEN
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,0,?)"
ELSE
#dbengine = "MSV"
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,?)"
END IFPREPARE ins1 FROM sel_stmt
EXECUTE ins1 USING x_int[i].ind,
x_int[i].orgn_proj,
x_int[i].account,
x_int[i].fringe,
x_int[i].amount,
x_int[i].ctrl_no,
x_int[i].pay_run
LET rec_cnt1 = rec_cnt1 + 1
END IF
END FORIF repeat = 1 THEN
# ***** the STORE databaseCALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengineIF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
END IFLET ct2 = ct2 + 200
LET ct1 = 0
LET ct3 = 0
LET repeat = 0
ELSE
LET reread = FALSE
END IFEND IF #STORE database not found
END WHILE
END IF #payroll database found
Expert:  Russell H. replied 12 months ago.

Hi, thank you for contacting JustAnswer.com. My name is Russell. I will do my best to provide the right answer to your question.

Obviously your problem is something to do with the modulus of 200: 200/400/600 (and no further since the database in question is over 600 but less than 800 records long.)

There are only a few places, in the code you cited, where the modulus of 200 is mentioned, and here I quote those places, demarcated by -------------------------- :

------------------------------------------------------------------

LET ct1 = 0 {1 to 200}

LET ct2 = 0 {200/400/600}

------------------------------------------------------------------

FOREACH pay1 INTO t_int.*

IF ct1 = 200 THEN

LET repeat = 1

EXIT foreach

------------------------------------------------------------------

NOTE: I think the previous segment is the problem area. It obviously counts only one of 200.

------------------------------------------------------------------

LET ct2 = ct2 + 200

LET ct1 = 0

LET ct3 = 0

LET repeat = 0

ELSE

LET reread = FALSE

-----------------------------------------------------------

LET ct1 = 0 {1 to 200}
LET ct2 = 0 {200/400/600}
LET ct3 = 0 {record count}
LET repeat = 0
LET reread = TRUE
LET query_text = "SELECT * FROM inter WHERE ", where_part CLIPPED

WHILE reread
PREPARE qry_pay1 FROM query_text
DECLARE pay1 CURSOR FOR qry_pay1
FOREACH pay1 INTO t_int.*
IF ct1 = 200 THEN
LET repeat = 1
EXIT foreach
END IF

----------------------------------------------------------------------

sorry let me send you the code again.. Missing some steps:

DISPLAY "Step 1 - Downloading interface records"

CALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
ELSE
DELETE FROM inter WHERE inter.ind IS NULL
LET ct1 = 0 {1 to 200}
LET ct2 = 0 {200/400/600}
LET ct3 = 0 {record count}
LET repeat = 0
LET reread = TRUE
LET query_text = "SELECT * FROM inter WHERE ", where_part CLIPPED

WHILE reread
PREPARE qry_pay1 FROM query_text
DECLARE pay1 CURSOR FOR qry_pay1
FOREACH pay1 INTO t_int.*
IF ct1 = 200 THEN
LET repeat = 1
EXIT foreach
END IF

LET ct3 = ct3 + 1
IF ct3 <= ct2 THEN CONTINUE FOREACH END IF
LET ct1 = ct1 + 1
LET x_int[ct1].* = t_int.*
END FOREACH

# ***** PAYROLL database now that the STORE database has
# ***** updated

CALL dbiStartDatabase("") RETURNING lintProcStatus, dbengine
IF NOT lintProcStatus THEN
CALL libErrDialog("Error","Cannot connect to database","stop","OK")
EXIT WHILE
ELSE

BEGIN WORK
FOR i = 1 TO ct1
IF SQLCA.SQLERRD[3] = 0 THEN
IF dbengine = "IFX" THEN
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,0,?)"
ELSE
#dbengine = "MSV"
LET sel_stmt = "INSERT INTO inter VALUES (?,?,?,?,?,?,?)"
END IF

PREPARE ins1 FROM sel_stmt
EXECUTE ins1 USING x_int.ind,
x_int.orgn_proj,
x_int.account,
x_int.fringe,
x_int.amount,
x_int.ctrl_no,
x_int.pay_run
LET rec_cnt1 = rec_cnt1 + 1
END IF
END FOR

IF repeat = 1 THEN
# ***** the STORE database

CALL dbiStartDatabase(STORE)
RETURNING lintProcStatus, dbengine

IF NOT lintProcStatus THEN
LET lstrProcMessage = "Unable to connect to the STORE database."
CALL libErrDialog("Error", lstrProcMessage, "exclaim", "Close")
EXIT PROGRAM
END IF

LET ct2 = ct2 + 200
LET ct1 = 0
LET ct3 = 0
LET repeat = 0
ELSE
LET reread = FALSE
END IF

END IF #STORE database not found
END WHILE
END IF #payroll database found

------------------------------------------------------------------------

Somewhere in that code something about the selection of data, or the transfer of data, from the source database is only being activated once in the count (or modulus) of 200.

Do you agree, so far?

Customer: replied 12 months ago.
i agree that is what it does now and all i get in the Payroll database is 4 records.
Expert:  Russell H. replied 12 months ago.

4 records, at 200th, 400th, 600th... and one more, possibly 1st record?

Why does this part of the code resemble a select-one-in-200 code mechanism?:

FOREACH pay1 INTO t_int.*

IF ct1 = 200 THEN

LET repeat = 1

EXIT foreach

It's the part that most resembles the root of the problem, though there are other suspect areas.

Customer: replied 12 months ago.
I cannot allow a remote connection. The reason the 200 records are selected at a time. Is that moving 200 records from one database to another is more manageable for IFX or MVS. But i need to be able to do the FOR loop to also INSERT all 200 records. I'm just not sure how to do that in the FOR loop or even if it should be a FOR loop.
Expert:  Russell H. replied 12 months ago.

OK. Somehow you set it up so the record transfer is

1 every 200 records

not

200 records at a time, then another 200, to the end.

A set of FOR loops should do it. Here's an outline:

x = 1

For x=1 to end-of-table in steps of 200

For y=1 to 200 REM in steps of 1 each.

z=x+y

put one record from first db (record # ***** ) in the cache for the transfer

NEXT

transfer records from the cache to the second db.

NEXT

REM end-of-table reached. Transfers in sets of 200 records is done.

Expert:  Russell H. replied 12 months ago.

Actually that code outline is not quite accurate, and may skip a few records.

x = 0

For x=0 to end-of-table in steps of 200

For y=1 to 200 REM in steps of 1 each.

z=x+y

put one record from first db (record # ***** ) in the cache for the transfer

NEXT

transfer records from the cache to the second db.

NEXT

REM end-of-table reached. Transfers in sets of 200 records is done.

Customer: replied 12 months ago.
I guess I'm still not getting it. Can you show me exactly where to put the code you wrote? I'm still just getting 4 records.
Customer: replied 12 months ago.
I'm not understanding and I feel that my answer is not really an answer but more of a teaching basic code structure. Do you believe i should rewrite the FOREACH loop or are you saying i should include the FOR loop with in the FOREACH loop? I have been experimenting with both logic and still not getting all 673 records, just 4, the last record in each 200th record.I needed more of a exact code logic and that i why a paid for detailed level question. Do you believe you could give me a more defective answer to my code issue or do i need to request another Programmer to assist.I was looking to have this issue resolved by Monday Morning which is about 10 hours away.
Expert:  Russell H. replied 12 months ago.

I give up the case, with apologies. I have Opted Out.