[firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App
Message received. I'll go away
Hi Homer, hope you haven’t gone completely away just yet. Your question (and
problem) may be off-topic for this list, but I'll try to keep my answer more or
less on-topic.
Your SQL puzzles me, I've never before seen 'select distinct Sum(' in a
subselect, and cannot help thinking that DISTINCT at best is irrelevant since
the subselect has to return 0 or 1 row.
I don't know your Firebird version, but if it is fairly recent, you could try
changing to something like:
WITH MyParam(Param) as
(SELECT CAST(:MyInput AS SMALLINT) FROM RDB$DATABASE),
Tmp(PolNum, Transdate, Sum_Of_ArAmt) AS
(SELECT PolNum, Transdate, sum(ArAmt)
FROM AR
WHERE ArAmt 0
GROUP BY 1, 2)
SELECT M.CARRIERNO, M.CARRIER, M.POLNUM, M.INVOICENO, M.TRANSNO, M.TRANSDATE,
M.TRANSSOURCE, M.TRANSTYPE, M.PREMIUM_BEFORE, M.PREMIUM_AFTER, M.ARAMT,
T.SUM_OF_ARAMT, M.DUEDATE, M .ACCTGPERIOD, M.ACCTGPERIODID, M.BANK,
M.CHECKNO,
M.CHECKDATE, M.ENTEREDBY, M.ENTEREDDATE, M.NOTES
FROM AR M
JOIN TMP T ON T.polnum = M.PolNum and T.transdate = M.transdate
CROSS JOIN MyParam P
WHERE (P.Param = 0)
OR (P.Param = 1 AND T.Sum_Of_ArAmt 0 AND M.ARAMT 0)
OR (P.Param = 2 AND T.Sum_Of_ArAmt = 0 AND M.ARAMT 0)
ORDER BY M.DUEDATE, M.TRANSDATE, M.POLNUM, M.ARAMT
(I've selected the parameter from RDB$DATABASE since RDB$DATABASE contains only
one row)
Then, you wouldn't have to modify your SQL when cxRGs ItemIndex changes, just
set the input parameter to the value of cxRG.ItemIndex and reopen your query,
no need to change the query (the only differences between this and your
original queries, should be that Sum_Of_Aramt is included also for the plain
select and that my query above will not work with versions prior to Firebird
2.0 or similar).
Now, I don't know whether this is of any help with your real problem or not.
With such a query and IBO, I would probably have had to specify either KeyLinks
or InsertSQL/EditSQL to make such a query insertable/updateable, but I've no
knowledge of IBDAC. Neither do I have any experience with queries complaining
when I try to close them.
One off-topic Delphi hint though: Sometimes I've found that deleting a
component and creating a new one fixes strange problems, I guess it is due to
some kind of property I've accidentally set and forgotten all about.
Set
[firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App
I wrote: WITH MyParam(Param) as (SELECT CAST(:MyInput AS SMALLINT) FROM RDB$DATABASE), Tmp(PolNum, Transdate, Sum_Of_ArAmt) AS (SELECT PolNum, Transdate, sum(ArAmt) FROM AR WHERE ArAmt 0 GROUP BY 1, 2) SELECT M.CARRIERNO, M.CARRIER, M.POLNUM, M.INVOICENO, M.TRANSNO, M.TRANSDATE, M.TRANSSOURCE, M.TRANSTYPE, M.PREMIUM_BEFORE, M.PREMIUM_AFTER, M.ARAMT, T.SUM_OF_ARAMT, M.DUEDATE, M .ACCTGPERIOD, M.ACCTGPERIODID, M.BANK, M.CHECKNO, M.CHECKDATE, M.ENTEREDBY, M.ENTEREDDATE, M.NOTES FROM AR M JOIN TMP T ON T.polnum = M.PolNum and T.transdate = M.transdate CROSS JOIN MyParam P WHERE (P.Param = 0) OR (P.Param = 1 AND T.Sum_Of_ArAmt 0 AND M.ARAMT 0) OR (P.Param = 2 AND T.Sum_Of_ArAmt = 0 AND M.ARAMT 0) ORDER BY M.DUEDATE, M.TRANSDATE, M.POLNUM, M.ARAMT Sorry, I forgot that ItemIndex 0 should also include those with no ArAmt 0 for a particular day and Transdate, so you may want to change JOIN TMP to LEFT JOIN TMP. Set
[firebird-support] RE: Firebird configuration for better performance
Hi Alexey, For the database (Using GBAK restore with options): Page Size = 8192 Buffers = 1 In the firebird.conf file: DefaultDBCachePages = 32768 Is this okay? Bests, Steve
Re: [firebird-support] Firebird configuration for better performance
Change your fb to Classic or Superclassic so you can use all cpu cores. If you choose Classic you also need to increase hashmemslots by 10 times at least (prime number). Google for it. Em 08/01/2014 22:39, stevef...@yahoo.com escreveu: Hi, I'm running Firebird 2.5.2 SuperServer on a Windows 7 Professional 64-bit PC. The PC currently has 4 GB of RAM, but we plan to upgrade to 8 GB soon. The database is a small database used for inventory and stock control with about 30 tables, with about 6 - 8 concurrent users, and relatively moderate workload (INSERTS, UPDATES and SELECTS) throughout the day. The database is currently empty (we're just starting out) and the size currently stands at about 6 MB and is due to grow as data and transactions are added. To increase performance, I've changed the default Firebird memory settings as thus: For the database (Using GBAK restore with options): Page Size = 8192 Buffers = 32768 In the firebird.conf file: DefaultDBCachePages = 32768 Can anybody here explain to me: 1) Whether the given PC specification (RAM) will support this database? 2) Is this a good configuration? If not what are the ideal values I should adjust to? Thanks in advance for any enlightenment. Regards, Steve Faleiro
Re: [firebird-support] Data transfer from one table to another
Yes. Em 09/01/2014 04:56, dice_in_trou...@yahoo.com escreveu: Hi all, is it possible to transfer the content of a table to a new one? Thanks,
[firebird-support] RE: Data transfer from one table to another
column numbers and type must match in inserted table from the select insert into Members (number, name) select number, name from NewMembers
[firebird-support] Outdated indices
I have stumbled upon a problem with Selects based on indices which do not seem to be updated as they should. There are no Active Transaction but there are more reads than the actual number of records corresponding to my select conditions. Here's an example : create table Test1(id1 integer not null primary key, IndexedField1 integer not null); create index x_test1_Field1 on test1(IndexedField1); INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (1, 1); INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (2, 2); INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (3, 3); commit; select count(*) from test1 where indexedfield1=2 returns value 1 with 1 indexed read. update test1 t1 set t1.indexedfield1=4 where id1=2; select count(*) from test1 where indexedfield1=2 returns value 0 with 1 indexed read !!! select count(*) from test1 where indexedfield1=5 returns value 0 with 0 indexed read. The problem is that the index seems to keep having old values until there is not a single connection to the database (even if I commit every transaction). This causes me real performance problems on 24/7 systems ... some query wich ran in less than a second now take a minute to go. I know I can rebuild the indices if I set them inactive/active but it is impraticable in a 24/7 system because simultaneous queries raise exceptions. Firebird 2.5.1 and 2.5.2 32 bits and 64 bits Superservers on Windows Thank you for any advice ! Evelyne Girard
Re: [firebird-support] Outdated indices
Hello, Evelyne! Thursday, January 9, 2014, 8:57:13 PM, you wrote: EG The problem is that the index seems to keep having old values EG until there is not a single connection to the database (even if I EG commit every transaction). This causes me real performance EG problems on 24/7 systems ... some query wich ran in less than a second now EG take a minute to go. your problem is versions, because index keep all keys for all versions. If you have active transactions, garbage collection does not work, and obsolete keys and versions are not cleaned. Check gstat -r to see how many versions tables have when you have performance problems. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] RE: Firebird configuration for better performance
Hi Steve, Value from database header overrides default value from conf file. So I believe this is Ok. Regards, Alexey Kovyazin IBSurgeon Hi Alexey, For the database (Using GBAK restore with options): Page Size = 8192 Buffers = 1 In the firebird.conf file: DefaultDBCachePages = 32768 Is this okay? Bests, Steve
RE: [firebird-support] Outdated indices
Thanks a lot for your answer Dimitry, your problem is versions, because index keep all keys for all versions. If you have active transactions, garbage collection does not work, and obsolete keys and versions are not cleaned. Check gstat -r to see how many versions tables have when you have performance problems. Dmitry Kuzmenko, www.ib-aid.comhttp://www.ib-aid.com As I said, I don't have active transactions, this is why I thought the old versions should not be read by my query. I did the gstat and it sure shows something is wrong with my index (it has 2666 more nodes than the actual data (and other indices) on the other hand it has only a 10 versions in total) but I do not see how I can fix this. Maybe I could run a sweep (automatic sweep are deactivated but based on firebirdsql documentation it shouldn't be a problem since we backup every night) ... but it kinds of freeze everything while it runs. And I'm not even sure it explains it reads 1080 old record versions on some queries. Oldest transaction 5018404 Oldest active 134077480 = Oldest snapshot 134077480 Next transaction134077481 Evelyne
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.
[firebird-support] Test no reply
Test. I am not receiving the messages that I send to the list Best Regards | ISMAEL |
