Mixing
Count of affected records in PDO transaction
0
I have a script that runs a pretty basic PDO transaction and it doe work (selects records and performs the merge based on those results)
So it successfully inserts/updates but I'm trying to get a count of accurate records and it doesn't work, it just returns 1 in powershell.
Starting with an empty database table and running this script, It inserts 1300 records successfully but $count prints '1'.
I'm simply trying to get record counts, success messages and errors for the purpose of an email when the script runs. Am I missing something?
while($arr = odbc_fetch_array($result)){
$stmt = $PDO->prepare("
MERGE INTO ORDER_STATUS as S
USING (VALUES(
CAST(:ORDER_ID as INT),
CAST(:ORDER_STATUS as VARCHAR(45)),
CAST(:is_active as DECIMAL(1,0)),
CAST(:DATE_UPDATED as DATE)
)
)
AS O(order_id, order_status, is_active, date_updated)
ON O.order_id = S.order_id
WHEN MATCHED THEN UPDATE SET order_status = O.order_status, is_active = O.is_active, date_updated = O.date_updated
WHEN NOT MATCHED THEN INSERT(order_id,order_status,is_active,date_updated) VALUES(O.order_id, O.order_status, O.is_active, O.date_updated)
");
$PDO->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {
if(++$i % 1000 == 0) {
$PDO->commit();
$PDO->beginTransaction();
}
$stmt->execute($db2row);
}
$PDO->commit();
$count = $stmt->rowCount();
print_r($count);
}
php sql pdo db2
edited Nov 21 '18 at 15:32
Dave
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
|
show 2 more comments
0
I have a script that runs a pretty basic PDO transaction and it doe work (selects records and performs the merge based on those results)
So it successfully inserts/updates but I'm trying to get a count of accurate records and it doesn't work, it just returns 1 in powershell.
Starting with an empty database table and running this script, It inserts 1300 records successfully but $count prints '1'.
I'm simply trying to get record counts, success messages and errors for the purpose of an email when the script runs. Am I missing something?
while($arr = odbc_fetch_array($result)){
$stmt = $PDO->prepare("
MERGE INTO ORDER_STATUS as S
USING (VALUES(
CAST(:ORDER_ID as INT),
CAST(:ORDER_STATUS as VARCHAR(45)),
CAST(:is_active as DECIMAL(1,0)),
CAST(:DATE_UPDATED as DATE)
)
)
AS O(order_id, order_status, is_active, date_updated)
ON O.order_id = S.order_id
WHEN MATCHED THEN UPDATE SET order_status = O.order_status, is_active = O.is_active, date_updated = O.date_updated
WHEN NOT MATCHED THEN INSERT(order_id,order_status,is_active,date_updated) VALUES(O.order_id, O.order_status, O.is_active, O.date_updated)
");
$PDO->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {
if(++$i % 1000 == 0) {
$PDO->commit();
$PDO->beginTransaction();
}
$stmt->execute($db2row);
}
$PDO->commit();
$count = $stmt->rowCount();
print_r($count);
}
php sql pdo db2
edited Nov 21 '18 at 15:32
Dave
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
I'm almost positive that that value of1you are seeing is correct. Take a look at this question and answer for more information.
– Dave
Nov 21 '18 at 15:24
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45
|
show 2 more comments
0
0
0
0
I have a script that runs a pretty basic PDO transaction and it doe work (selects records and performs the merge based on those results)
So it successfully inserts/updates but I'm trying to get a count of accurate records and it doesn't work, it just returns 1 in powershell.
Starting with an empty database table and running this script, It inserts 1300 records successfully but $count prints '1'.
I'm simply trying to get record counts, success messages and errors for the purpose of an email when the script runs. Am I missing something?
while($arr = odbc_fetch_array($result)){
$stmt = $PDO->prepare("
MERGE INTO ORDER_STATUS as S
USING (VALUES(
CAST(:ORDER_ID as INT),
CAST(:ORDER_STATUS as VARCHAR(45)),
CAST(:is_active as DECIMAL(1,0)),
CAST(:DATE_UPDATED as DATE)
)
)
AS O(order_id, order_status, is_active, date_updated)
ON O.order_id = S.order_id
WHEN MATCHED THEN UPDATE SET order_status = O.order_status, is_active = O.is_active, date_updated = O.date_updated
WHEN NOT MATCHED THEN INSERT(order_id,order_status,is_active,date_updated) VALUES(O.order_id, O.order_status, O.is_active, O.date_updated)
");
$PDO->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {
if(++$i % 1000 == 0) {
$PDO->commit();
$PDO->beginTransaction();
}
$stmt->execute($db2row);
}
$PDO->commit();
$count = $stmt->rowCount();
print_r($count);
}
php sql pdo db2
edited Nov 21 '18 at 15:32
Dave
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
I have a script that runs a pretty basic PDO transaction and it doe work (selects records and performs the merge based on those results)
So it successfully inserts/updates but I'm trying to get a count of accurate records and it doesn't work, it just returns 1 in powershell.
Starting with an empty database table and running this script, It inserts 1300 records successfully but $count prints '1'.
I'm simply trying to get record counts, success messages and errors for the purpose of an email when the script runs. Am I missing something?
while($arr = odbc_fetch_array($result)){
$stmt = $PDO->prepare("
MERGE INTO ORDER_STATUS as S
USING (VALUES(
CAST(:ORDER_ID as INT),
CAST(:ORDER_STATUS as VARCHAR(45)),
CAST(:is_active as DECIMAL(1,0)),
CAST(:DATE_UPDATED as DATE)
)
)
AS O(order_id, order_status, is_active, date_updated)
ON O.order_id = S.order_id
WHEN MATCHED THEN UPDATE SET order_status = O.order_status, is_active = O.is_active, date_updated = O.date_updated
WHEN NOT MATCHED THEN INSERT(order_id,order_status,is_active,date_updated) VALUES(O.order_id, O.order_status, O.is_active, O.date_updated)
");
$PDO->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {
if(++$i % 1000 == 0) {
$PDO->commit();
$PDO->beginTransaction();
}
$stmt->execute($db2row);
}
$PDO->commit();
$count = $stmt->rowCount();
print_r($count);
}
php sql pdo db2
php sql pdo db2
edited Nov 21 '18 at 15:32
Dave
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
edited Nov 21 '18 at 15:32
Dave
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
edited Nov 21 '18 at 15:32
Dave
2,24451725
edited Nov 21 '18 at 15:32
Dave
2,24451725
edited Nov 21 '18 at 15:32
Dave
2,24451725
2,24451725
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
asked Nov 21 '18 at 15:06
Tom N.Tom N.
1,342415
1,342415
I'm almost positive that that value of1you are seeing is correct. Take a look at this question and answer for more information.
– Dave
Nov 21 '18 at 15:24
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45
|
show 2 more comments
I'm almost positive that that value of1you are seeing is correct. Take a look at this question and answer for more information.
– Dave
Nov 21 '18 at 15:24
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45
I'm almost positive that that value of
1 you are seeing is correct. Take a look at this question and answer for more information.– Dave
Nov 21 '18 at 15:24
I'm almost positive that that value of
1 you are seeing is correct. Take a look at this question and answer for more information.– Dave
Nov 21 '18 at 15:24
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414939%2fcount-of-affected-records-in-pdo-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414939%2fcount-of-affected-records-in-pdo-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm almost positive that that value of
1you are seeing is correct. Take a look at this question and answer for more information.– Dave
Nov 21 '18 at 15:24
This is on DB2, not MySQL, but I think I see the point there. So it's just returning the single last affected row? I'm basically trying to get it to report the count of all affected rows. So that initial insert should print 1300 for the count
– Tom N.
Nov 21 '18 at 15:29
With PDO I don't think that the underlying engine matters.
– Dave
Nov 21 '18 at 15:30
Row by row processing is very inefficient. Consider populating a session table with all the required rows (either in a fetch loop if the source-data comes from a different database than Db2, or if same database then use insert-into-select-from). Finally once the session table is populated, use a single Merge statement to do the upsert in a single action, or in a batched-action dependent on volumetrics.
– mao
Nov 21 '18 at 15:49
Ok so @Dave, the way the code is it prints 1, if I move the $count and print_r statement up within the while loop it prints 1, but 1300 times. Is there not just a way I can aggregate that within the loop so that it counts the row prints?
– Tom N.
Nov 21 '18 at 16:45