<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>On MSSQL i use a stroed procedure to count a value in a table and
use the following statement in Lazarus</p>
<p>
<blockquote type="cite">
<ol style="color: rgb(0, 0, 0); font-family: monospace; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: pre-wrap; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"><span style="color: rgb(0, 0, 0); font-weight: bold;">procedure</span><span> </span>TForm1<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">BuExecuteClick</span><span style="color: rgb(0, 153, 0);">(</span>Sender<span style="color: rgb(0, 0, 102);">:</span><span> </span>TObject<span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"><span style="color: rgb(0, 0, 0); font-weight: bold;">var</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> SQL<span style="color: rgb(0, 0, 102);">:</span><span> </span><span style="color: rgb(0, 0, 102); font-weight: bold;">string</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"><span style="color: rgb(0, 0, 0); font-weight: bold;">begin</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Memo1<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Clear</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> SQL<span> </span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(255, 0, 0);">''</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> SQL<span> </span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(255, 0, 0);">'EXECUTE [dbo].[GetNextZaehler] :TagNr,:ProduktNr '</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Active</span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">false</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Clear</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">SQL</span><span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Text</span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span>SQL<span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">ParamByName</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'TagNr'</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">AsInteger</span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(204, 102, 204);">10</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">ParamByName</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'ProduktNr'</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">AsInteger</span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(204, 102, 204);">100</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Options</span><span style="color: rgb(0, 0, 102);">:</span><span style="color: rgb(0, 0, 102);">=</span><span> </span><span style="color: rgb(0, 153, 0);">[</span>sqoAutoApplyUpdates<span style="color: rgb(0, 0, 102);">,</span>sqoAutoCommit<span style="color: rgb(0, 153, 0);">]</span><span style="color: rgb(0, 0, 102);">;</span><span> </span><span style="color: rgb(128, 128, 128); font-style: italic;">// <-- AutoApplyUpdates doesnt work !?</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">try</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Open</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">if</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">not</span><span style="color: rgb(0, 153, 0);">(</span>Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 0, 102);">EOF</span><span> </span><span style="color: rgb(0, 0, 102);">and</span><span> </span>Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">BOF</span><span style="color: rgb(0, 153, 0);">)</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">then</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">begin</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Memo1<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Append</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'Wert='</span><span style="color: rgb(0, 0, 102);">+</span>Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">FieldByName</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'StueckZaehler'</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">AsInteger</span><span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">ToString</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">end</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">else</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">begin</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Memo1<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Append</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'Kein Wert'</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">end</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(128, 128, 128); font-style: italic;">//Query.ApplyUpdates; // <-- If i use this it works</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Query<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Close</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">except</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> on E<span> </span><span style="color: rgb(0, 0, 102);">:</span><span> </span>Exception<span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">do</span><span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">begin</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> Memo1<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Append</span><span style="color: rgb(0, 153, 0);">(</span><span style="color: rgb(255, 0, 0);">'BuExecuteClick Exception =>'</span><span> </span><span style="color: rgb(0, 0, 102);">+</span><span> </span>E<span style="color: rgb(0, 0, 102);">.</span><span style="color: rgb(0, 102, 0);">Message</span><span style="color: rgb(0, 153, 0);">)</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">end</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> <span> </span><span style="color: rgb(0, 0, 0); font-weight: bold;">end</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"><span style="color: rgb(0, 0, 0); font-weight: bold;">end</span><span style="color: rgb(0, 0, 102);">;</span></div></li><li style="white-space: nowrap; padding: 0px 5px; background-color: rgb(255, 255, 255); line-height: 16px; border-left: 1px solid rgb(153, 153, 153);"><div style="font: 1em / 1.2em monospace; margin: 0px; padding: 0px; background: none; vertical-align: top;"> </div></li></ol>
</blockquote>
I must extra write an ApplyUpdates, because the
sqoAutoApplyUpdates is ignored by the ExecSQL of the query.</p>
<p>in sqldb.pp the following code is executed</p>
<p>
<blockquote type="cite">procedure TCustomSQLQuery.ExecSQL;<br>
<br>
begin<br>
CheckPrepare;<br>
try<br>
Execute;<br>
// Always retrieve rows affected<br>
FStatement.RowsAffected;<br>
If sqoAutoCommit in Options then<br>
SQLTransaction.Commit;<br>
finally<br>
CheckUnPrepare;<br>
// if not Prepared and (assigned(Database)) and
(assigned(Cursor)) then
SQLConnection.UnPrepareStatement(Cursor);<br>
end;<br>
end;<br>
</blockquote>
I see the autocommit is configured, but sqoAutoApplyUpdates is
missing. It hink it should be</p>
<p>
<blockquote type="cite">procedure TCustomSQLQuery.ExecSQL;<br>
<br>
begin<br>
CheckPrepare;<br>
try<br>
Execute;<br>
// Always retrieve rows affected<br>
FStatement.RowsAffected;<br>
If sqoAutoCommit in Options then<br>
SQLTransaction.Commit;<br>
If (sqoAutoApplyUpdates in Options) then<br>
ApplyUpdates;<br>
finally<br>
CheckUnPrepare;<br>
// if not Prepared and (assigned(Database)) and
(assigned(Cursor)) then
SQLConnection.UnPrepareStatement(Cursor);<br>
end;<br>
end;<br>
</blockquote>
to get the correct behavior. Actual only Post and Delete fire the
ApplyUpdates correct if sqoAutoApplyUpdates is activated. Should i
file a Bug or is the actual behavior by design ?! (and cannot be
changed)<br>
</p>
<p>Andreas<br>
</p>
<p><br>
</p>
</body>
</html>