RE: [firebird-support] Re: Full Cross Tab but unknown column headers
By the way, google's query language has a PIVOT syntax: https://developers.google.com/chart/interactive/docs/querylanguage#Pivot Eg. select sum(salary) pivot dept Would be nice if Firebird could do the same, I understand you couldn't use it inside a stored procedure, where your columns need to be known up front, but your could use it to query your selectable stored procedure, which would be very nice ;-)
[firebird-support] Re: Full Cross Tab but unknown column headers
By the way, google's query language has a PIVOT syntax: https://developers.google.com/chart/interactive/docs/querylanguage#Pivot Eg. select sum(salary) pivot dept Would be nice if Firebird could do the same, I understand you couldn't use it inside a stored procedure, where your columns need to be known up front, but your could use it to query your selectable stored procedure, which would be very nice ;-) I agree, Maya, I would even say that if it was possible to return rows from EXECUTE BLOCK without having to specify the RETURNS clause, that it would be a good start (if EXECUTE BLOCK just returned the last executed statement, then Bhavbhuti could possibly use EXECUTE BLOCK ... FOR EXECUTE STATEMENT :MyDynamicallyCreatedStatement DO SUSPEND; ...). However, such a change would also mean that you wouldn't know the field structure of the result before you actually execute the statement (today you know it once the query is prepared), and it wouldn't surprise me (I know nothing about the Firebird code) if that would be a change with undesirable side effects. Set
[firebird-support] RE: COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed
I have: ldd /usr/sbin/fbserver | grep icu libicuuc.so.52 = /usr/lib/i386-linux-gnu/libicuuc.so.52 (0xb75a3000) libicudata.so.52 = /usr/lib/i386-linux-gnu/libicudata.so.52 (0xb5c1a000)
[firebird-support] RE: Update information for www.firebirdfaq.org/faq358/
This is more update and worked for me in Ubutun 14.04 beta 32bits: ldd /usr/sbin/fbserver | grep icu libicuuc.so.52 = /usr/lib/i386-linux-gnu/libicuuc.so.52 (0xb75a3000) libicudata.so.52 = /usr/lib/i386-linux-gnu/libicudata.so.52 (0xb5c1a000)
[firebird-support] How to quot;fixquot; SYSDBA/masterkey login
Hi I want to fix my software usage of SYSDBA/masterkey and would appreciate pointers to fastest/best way to do so. I have +- 100 installations running on WinXp/Win7 and Firebird 2.5.1 or 2.5.2. The software is develop with Delphi XE2/DBX. For a start I just want to copy the rights of SYSDBA to a new user, if possible directly from my application or a batch file. From there I will implement all the tips etc I learned from this group. Thanks for all the info shared in this group. Regards Cornie van Schoor
Re: [firebird-support] How to quot;fixquot; SYSDBA/masterkey login
What about creating a user with the RDB$ADMIN role? Greetings. Walter. On Wed, Jan 8, 2014 at 8:41 AM, corni...@gmail.com wrote: Hi I want to fix my software usage of SYSDBA/masterkey and would appreciate pointers to fastest/best way to do so. I have +- 100 installations running on WinXp/Win7 and Firebird 2.5.1 or 2.5.2. The software is develop with Delphi XE2/DBX. For a start I just want to copy the rights of SYSDBA to a new user, if possible directly from my application or a batch file. From there I will implement all the tips etc I learned from this group. Thanks for all the info shared in this group. Regards Cornie van Schoor
[firebird-support] RE: COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed
Which ICU version need the Firebird version 2.5.2 update 1?
[firebird-support] RE: COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed
In my OS i also have installed the ICU48. But firebird 252 update 1 does not seem to use it. How i can solve this? Which version of icu to install for firebird and how to make firebird using it? Thank you
[firebird-support] RE: COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed
it is a ubuntu 14.04 packaged error: https://bugs.launchpad.net/ubuntu/+source/firebird2.5/+bug/1267128
Re: [firebird-support] How to protect database Firebird in client side
dear rudyhuan...@ymail.com I think this link will help you: http://www.destructor.de/firebird/gsec.htm On Wed, Jan 8, 2014 at 2:21 PM, liviusliv...@poczta.onet.pl liviusliv...@poczta.onet.pl wrote: Hi, Your design is wrong from security POV If you need to protect db from copy - put it on server where only admin have access. Then noone can copy your db. If you put it on client computer you accept that risk at start Regards, Karol Bieniaszewski - Reply message - Od: Virna Constantin costel...@yahoo.com Do: firebird-support@yahoogroups.com firebird-support@yahoogroups.com Temat: [firebird-support] How to protect database Firebird in client side Data: śr., sty 8, 2014 07:43 http://www.firebirdfaq.org/faq344/ can not be a solution? On Monday, January 6, 2014 7:31 PM, rudyhuan...@ymail.com rudyhuan...@ymail.com wrote: Hello Team Support, I installed on a client machine, the ERP.FDB file and the application. Now, I came to know that, one guy in the client place, stole the ERP.FDB and tried several passwords and did not get it, so, he installed a FIREBIRD on his computer and copied the ERP.FDB and opened it with FIREBIRD default password, and stole the entire db design. How to protect from this kind of things ? How to make sure, that the database cannot be copied? Regards Thanks, Rudy Huang
[firebird-support] RE: How to protect database Firebird in client side
Dear All, Thank you for your response. Maybe i will protect my database by adding some role. That makes it difficult to access my database. Thank you Thomas Steinmaurer (UPSCENE) for your suggestion. Regards Thanks, Rudy Huang
[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
Try 'Append' instead of 'Insert'
[firebird-support] Firebird configuration for better performance
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
[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
At 10:37 a.m. 9/01/2014, ho...@agencybusys.com wrote:
I looked through previous topics, and although this seems like a common error,
I didn't find one quite like my situation.
You realise this is a problem with your Delphi client code, don't you? And
that this list is not a Delphi support list.
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.
Insert and Close are Delphi methods. So is Post which, in most data access
components, should be called after the data for an Insert or Update operation
has been assigned.
Actual Error: Dynamic SQL Error SQL error code = -104 Unexpected end of
command - line 1, column 30
This is a Firebird exception, that occurs when client code issues an incomplete
SQL statement, which can come about from mishandling of the single-quoting of
strings. You need an SQL monitoring tool or a Delphi debug tool to see what
this invalid statement is.
I have backed up and restored the database just in case it was some kind of
corruption.
The error is occurring somewhere in your Delphi code. A statement being issued
to the server is terminating early because the parser got to something in a
statement that prevented it from making sense of the whole thing. A very common
cause is mishandling of the quoting of strings, most especially apostrophes.
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;
// Protect against the occurrence of a name like O'Brien or Johnson's Removals:
sCarrier := QuotedStr(sCarrier);
FieldByName('Carrier').AsString := sCarrier;
Try the same with sInvoiceNo if there is any possibility that the string
contains apostrophes.
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.
Moderator Hat ON: This time it was sort-of OK to use this support list because
you didn't seem to recognise that it wasn't a Firebird problem and you *did*
get an exception from the database. Don't make it a habit to use this list for
Delphi problems, though. I don't know where TIBCQuery comes from but most
third-party data access components have their own support lists.
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of The Firebird Book and The Firebird Book Second Edition
http://www.firebird-books.net
__
[firebird-support] Error in inputting content in a table
Hello, We are currently having trouble entering data on one of our tables. We are trying to enter data on specific columns, but after refreshing it, the data is lost. Here is the outline of our table: CREATE TABLE PAYMENT ( PAYMENT_DATE Timestamp DEFAULT current_timestamp NOT NULL, PMID Bigint NOT NULL, LID Integer, CID Integer, AMOUNT CURRENCY, BALANCE CURRENCY, OVER CURRENCY, DEFICIT CURRENCY, PAYMENT_TYPE Varchar(5), DATE_OF_PAYMENT Varchar(15), CONSTRAINT PK_PAYMENT PRIMARY KEY (PMID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON PAYMENT TO SYSDBA WITH GRANT OPTION; Dependencies: SET TERM ^ ; ALTER TRIGGER PAYMENT_BI ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE tmp DECIMAL(18,0); BEGIN IF (NEW.PMID IS NULL) THEN NEW.PMID = GEN_ID(GEN_PAYMENT_ID, 1); ELSE BEGIN tmp = GEN_ID(GEN_PAYMENT_ID, 0); if (tmp new.PMID) then tmp = GEN_ID(GEN_PAYMENT_ID, new.PMID-tmp); END END^ SET TERM ; ^ SET TERM ^ ; ALTER TRIGGER PAYMENT_BI ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE tmp DECIMAL(18,0); BEGIN IF (NEW.PMID IS NULL) THEN NEW.PMID = GEN_ID(GEN_PAYMENT_ID, 1); ELSE BEGIN tmp = GEN_ID(GEN_PAYMENT_ID, 0); if (tmp new.PMID) then tmp = GEN_ID(GEN_PAYMENT_ID, new.PMID-tmp); END END^ SET TERM ; ^ What could be the possible cause for this? the database worked fine in the past days, but after yesterday we cannot enter data to it. Thanks,
Re: [firebird-support] Firebird configuration for better performance
Hi Steve, This database is very small, you should not have any performance problems (except related with lack of indices or bad design of metadata). Usually we recommend to set 1 buffers for SuperServer - our experiments have shown that 1 is the best # of buffers for SuperServer. Regards, Alexey Kovyazin www.ibsurgeon.com 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
[firebird-support] Data transfer from one table to another
Hi all, is it possible to transfer the content of a table to a new one? Thanks,
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] SQL error code = -104 Unexpected end of command, from Delphi App
1. Your insert is failing, but you haven’t provided us with the SQL you are using for an insert. Also, check the refresh SQL property of your DB component. 2. I notice you have an ; at the end of all your SQL. I don’t think that is necessary when entering the SQL into the SQL property of your Delphi component (it isn’t for the IBXpress and FIBPlus components). You might be managing to confuse it somehow with that. 3. You are correct, when passing parameters as variables in Delphi, you don’t need to quote the text to cater for apostrophes. If you were to have built your SQL inline, without passing parameters, THEN you would have had to do that. So, the way your are working is best there, so you don’t have to worry about that. 4. I see your are using ShowMessage() to debug. Not sure if that was just to describe it simply to us, or if your are not using Delphi debugger (F,7 F8) to step through your code. If you aren’t using F7/F8, you’ll find the problem a lot faster if you take some time to learn how to use the debugger, and do away with the showMessage() method. 5. You might find it a lot easier to locate the problem if you use a SQL monitor. Then you’ll see the exact SQL statement that is failing, and can go look at what is character 30 of line 1. I think IBExpert has a SQL monitor, or your Delphi component set might also have a component you can use to create your own SQL monitor. Cheers Maya
