[firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App
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
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
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
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
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.