Re: ADO exception with character combination inside string...

"Bo Berglund" <bo.berglund@xxxxxxxxx> wrote in message
news:d0r5h293qm38jfe5nl90v67liuakh605sv@xxxxxxxxxx

We don't know for what we would like to use parameters at all....

For performance and isolation reasons we have put all of our logic
into stored procedures inside the MSSQL database. This means that the
applications (there are several) that operate on the database always
use calls to the stored procedures in order to update data or retrieve
recordsets from it.
We only send the parameters in like this simplified example:

SQL := 'EXEC UpdateOrderByID @OrderID=5123, @Name=''Ericsson 1652K'',
@Description=''My description string''';
qryADO.Execute(SQL);

The primary reason we use parameterized queries versus hard-coding the SQL
in the .pas file is the ability to quickly run/troubleshoot SQL scripts
in/out of the IDE. I know a few thinks it's silly to be concerned about
code like..

SomeQuery.CommandText := 'Select * From Products Where ...'

That is no big deal. This is actual code I had to debug (when the developer
would say "Not my code, it must be Kevin's table design")


with dmBatch.dqInvoice do begin // print invoice
Close;
SQL.Clear;
SQL.Add('Select host_name() "Machine", I.*, C.*, t.Description "Term
Description", v.ShortDesc "ShipVia",');
SQL.Add('z.custtypeid, convert(char(12), invoicedatetime, 101)
"InvoiceDate", e.FirstName, e.LastName,');
SQL.Add('convert(char(12), invoicedatetime, 108) "InvoiceTime",
y.InvoiceCopies, (select l.ldinum from ldi l where l.invoiceid =
i.invoiceid) "LDINum",');
SQL.Add('isnull((select sum(r.Amount) from restitution r where
r.invoiceusedon = i.invoiceid and r.codeid = 30), 0) + isnull((select
sum(r.Amount) from restitution r where r.invoiceusedon = i.invoiceid and
r.codeid = 480), 0) + ');
SQL.Add('isnull((select sum(r.Amount) from restitution r where
r.invoiceusedon = i.invoiceid and r.codeid = 32), 0) "MidwayMoney",');
SQL.Add('(select sum(copies) from invoiceprint w where w.invoiceid =
i.invoiceid) "RePrints", ');
SQL.Add('isnull((select sum(r.Amount) from restitution r where
r.invoiceusedon = i.invoiceid and r.codeid = 33), 0) "GiftUsed",
isnull((select sum(r.Amount) from restitution r where r.invoiceid =
i.invoiceid and r.codeid = 33), 0) "GiftCertificate", ');
SQL.Add('isnull((select sum(r.Amount) from restitution r where
r.invoiceid = i.invoiceid and r.codeid = 31), 0) "RefundCheck",
(convert(varchar(5), i.sourceid) + " " + convert(varchar(5), i.adrefid) + "
0 " +');
SQL.Add('convert(varchar(6), i.BatchNo) + " ") "Office Use",
z.custonhold, (select sum(qty * weight) from invoiceitems m where m.qty > 0
and m.invoiceid = i.invoiceid) "Weight",');
SQL.Add('(select sum(Total) from nraroundup) "NRATotal", (select Total
from nraroundup where nrayear = ' + floattostr(Year) + ') "NRAYTD"');
SQL.Add('from customers z, Shipvias v, Terms t, employees e,
invoicestoprocess o,');
SQL.Add('InvoiceSoldtoShipto C Countries y, Invoices I');
SQL.Add('where c.invoiceid = i.invoiceid and o.invoiceid = i.invoiceid
and');
SQL.Add('t.termid = i.termid and v.shipviaid = i.shipviaid and y.ctryid
= c.shiptoctryid and');
SQL.Add('e.employeenum = i.createdby and z.custnum = c.soldtocustnum
and');
SQL.Add('i.batchno = ' + inttostr(BatchNo));
SQL.Add('order by i.invoiceid');


The above includes the problem the application was having, so if you can
find it, virtual high-five. It took me almost 3 hours to track down the
developer's mis-type.

Now all the above 'logic' is handled in a view, so the client code now looks
like..

{CommandText property at design time}

Select *
From dbo.v_PrintInvoices
Where BatchNo = :BatchNo

In code..(ex. a datamodule)

public
function OpenInvoiceBatch(const BatchNo :Integer) :Integer;
end;

function TdmPrintInvoice.OpenInvoiceBatch(const BatchNo :Integer) :Integer;
var
RecordsFound :Integer;
begin
with qInvoicesToPrint do
begin
RecordsFound := 0; {default, no records found}
FErrorMsg := ''; {error message property of the datamodule}
DisableControls;
try
try
Close;
Parameters.ParamByName('BatchNo').Value := BatchNo;
Open;
RecordsFound := RecordCount;
if (RecordsFound = 0) then
begin
FErrorMsg := 'No invoices found for batch number: ' +
IntToStr(BatchNo);
end;
except
on E:Exception do
begin
RecordsFound := -1;
FErrorMsg := 'Unable to open v_PrintInvoices for batch
number: ' + IntToStr(BatchNo) + ' because of exception: ' + E.Message;
end;
end;
finally
EnableControls;
Result := RecordsFound;
end;
end;
end;

Now in the UI...

{some button click}
var
InvoicesToPrint :Integer;
begin
InvoicesToPrint := dmPrintInvoice(StrToInt(edBatchNo.Text));
if (InvoicesToPrint > 0) then
begin
lbInvoiceCount.Caption := 'Printing ' + IntToStr(InvoicesToPrint) + '
invoice(s)...';
{code to print invoices}
end
else
begin
{we actually have a custom error dialog that sends us an email when an
error occurs, but you get the idea}
ShowMessage(dmPrintInvoice.ErrorMsg);
end;
end;



krf


.

Quantcast