[firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

I looked through previous topics, and although this seems like a common error, 
I didn't find one quite like my situation. I'm using a TIBCQuery in Delphi 
2007.  SQL.Text := 'Select * From AR ORDER BY DueDate, TransDate, PolNum;'; The 
error occurs when I issue an Insert, or Close command. Actual Error: Dynamic 
SQL Error SQL error code = -104 Unexpected end of command - line 1, column 30 
 I have backed up and restored the database just in case it was some kind of 
corruption. The problem persisted after that. It is possible to display and 
edit data in an DevEx Express Grid. The problem only seems to exist when I try 
to Insert from Delphi. Actual Delphi Code (qAR is the TIBCQuery):
   With DM.qAR do   begin iCarrierNo := FieldByName('CarrierNo').AsInteger; 
sCarrier := FieldByName('Carrier').AsString; sInvoiceNo := 
FieldByName('InvoiceNo').AsString;   ShowMessage('1'); Insert; 
ShowMessage('2');   {Duplicate AR field values}   
FieldByName('CarrierNo').AsInteger := iCarrierNo;   
FieldByName('Carrier').AsString := sCarrier;   
FieldByName('InvoiceNo').AsString :=  sInvoiceNo ; and so on
 It gets as far as the Showmessage('1'), but does not reach Showmessage('2'). 
 

 

 I hope I've given enough info for you to understand what's going on. I've 
tried what I know, which apparently isn't much.
 

 Thanks in advance. 

[firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

I thought I had it fixed by switching to a different TIBCQuery to insert a new 
record. That worked fine. However, the same problem exists when I try to alter 
the SQL in order to "filter" the result set in the grid.
 The exact same error (exactly the same) happens when I close the query to 
alter the SQL. Code below:
 

   try
 if DM.qAR.State in [dsEdit, dsInsert] then DM.qAR.Post;
 DM.qAR.Close;
 Case cxRG.ItemIndex of
   0: begin
DM.qAR.SQL.Text := 'Select * From AR ORDER BY DueDate, TransDate, 
PolNum, ARAMT;';
  end;
   1: begin
DM.qAR.SQL.Text :=
   'Select M.CARRIERNO, M.CARRIER, M.POLNUM, M.INVOICENO, 
M.TRANSNO, M.TRANSDATE, ' +
   'M.TRANSSOURCE, M.TRANSTYPE, M.PREMIUM_BEFORE, M.PREMIUM_AFTER, 
ARAMT, ' +
   '(select distinct Sum(D.ARAMT) from AR D where (D.polnum = 
M.PolNum) and ' +
   '(D.transdate = M.transdate)) as SUM_OF_ARAMT , ' +
   'M.DUEDATE, M.ACCTGPERIOD, M.ACCTGPERIODID, M.BANK, M.CHECKNO,  
M.CHECKDATE, ' +
   'M.ENTEREDBY, M.ENTEREDDATE, M.NOTES ' +
   'From AR M ' +
   'Where (M.aramt <> 0) and (select Sum(D.ARAMT) from AR D ' +
 'where (D.polnum = M.PolNum) and (D.transdate = 
M.transdate)) <> 0 ' +
   'ORDER BY M.DUEDATE, M.TRANSDATE, M.POLNUM; ' ;
  end;
   2: begin
DM.qAR.SQL.Text :=
   'Select M.CARRIERNO, M.CARRIER, M.POLNUM, M.INVOICENO, 
M.TRANSNO, M.TRANSDATE, ' +
   'M.TRANSSOURCE, M.TRANSTYPE, M.PREMIUM_BEFORE, M.PREMIUM_AFTER, 
ARAMT, ' +
   '(select distinct Sum(D.ARAMT) from AR D where (D.polnum = 
M.PolNum) and ' +
   '(D.transdate = M.transdate)) as SUM_OF_ARAMT , ' +
   'M.DUEDATE, M.ACCTGPERIOD, M.ACCTGPERIODID, M.BANK, M.CHECKNO,  
M.CHECKDATE, ' +
   'M.ENTEREDBY, M.ENTEREDDATE, M.NOTES ' +
   'From AR M ' +
   'Where (M.aramt <> 0) and (select Sum(D.ARAMT) from AR D ' +
 'where (D.polnum = M.PolNum) and (D.transdate = 
M.transdate)) = 0 ' +
   'ORDER BY M.DUEDATE, M.TRANSDATE, M.POLNUM; ' ;
  end;
   end; // Case
   finally
 DM.qAR.open;
   end;
 

[firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

Thanks, Steve, but that was one of the first things I tried. Didn't work. I 
solved the insert problem by using a different TIBCQuery component, however now 
I can't close the query without producing the same error. Please see the code 
in my prior post.

Re: [firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

Thanks for the advise. I sincerely appreciate it. In my defense, this Delphi 
application has just shy of 900,000 lines of my code. The Data Module alone has 
115 TIBCQueries. As you might imagine, they all successfully execute Insert, 
Post, Open, and Close commands more times than I can count. None of them 
require an apostrophe from my code. Naturally, that doesn't mean the component 
doesn't have a syntax error somewhere, but one would think that would show up 
in one of the other 114 components. 
 

 I half-way expected to be told to stop bothering this group because I 
mentioned Delphi. I also expect to be told the same thing by folks in Delphi 
groups because I mentioned Firebird. I just thought there might be fewer 
Firebird users in a Delphi group and more Delphi users in a Firebird group. I 
didn't mean to imply that this was a Firebird problem. I knew it wasn't. I only 
hoped that some other Delphi/Firebird user might have solved this problem. I 
also know that it is important to keep inappropriate messages out of the group. 
Message received. I'll go away.:-)

RE: [firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

Thank you, Svein. I like the param idea. As you might be able to tell, this 
piece of the project is still under development. Now is a perfect time for me 
to switch to parameters.
 

 I solved my problem late last night. It was not easy to isolate. I usually set 
break-points and step through the code, but the error was being generated at a 
point that was remote from where the problem was being created. Although the 
Delphi part is off-topic, I'll briefly describe what was happening in case it 
might help someone else who falls into the same trap I did.
 

 I dynamically build a couple of "lookup" queries in the DevEx Quantum Grid's 
onFocusedRecordChanged event. It was not obvious that the event fired, and then 
a false event fired that returned empty values.
 

 'Select * from AR where PK_ID = ' + DM.qAR.FieldByName('PK_ID').AsString ;
 was:   Select * from AR 'where PK_ID =  no value here
 

 This resulted in the Unexpected end of command. It was exactly as Helen 
predicted -- of course. With one line of code, I rejected the false event. All 
is well with Firebird and Delphi (that four letter word). I usually use the 
onAfterScroll even for this kind of thing. This is my first attempt at using 
the grid's equivalent. I didn't expect the second false firing.
 

 Thanks to all who offered help. I hope my experience can help someone in the 
future.