Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
On 04 Sep 2014 13:31:14 -0700, doyc...@dsoft-bg.com [firebird-support] firebird-support@yahoogroups.com wrote: Yes background process is running to on same application server and uses same data source. I use EJB 2 and CPM for doing database operations. App server is 4.2.3 and jaybird is 2.1.6 How have you determined the problem has to do with locked tables (which takes some effort in Firebird), and not with the connection (or obtaining the connection from the datasource)? What is your datasource configuration and which datasource class do you use? Mark
RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
This is the definition of the table that locks on insert: CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT ( CASHBOXID INTEGER NOT NULL, DATE_TIME NUMERIC( 18, 0) NOT NULL, AMMOUNTNUMERIC( 18, 0), CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (CASHBOXID, DATE_TIME) ); ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX (ID); It is simple insert that just inserts single record. There is no triggers or generators. Primary key value is know before insert is executed. I see two likely ways an insert can fail here: 1) The CASHBOXID doesn’t exist or has been inserted in another transaction that is not yet committed (possible solution: insert into CASH_CASHBOX and CASH_CASHBOX_DAY_AMMOUNT in the same transaction) 2) A record with the same CASHBOXID and DATE_TIME already exists Can be avoided by using a new field populated through a generator as the primary key – but you would then probably have to also implement a ‘duplicate resolution’-button or similar in your program to fix up those cases where there are several records with the same CASHBOXID and DATE_TIME Either of these ought to give you an error message (which would be good to post on this list). Your problem could of course be related to something completely different (like Mark suggests – or something stupid like the user having no insert rights to the table), but if it is the inserts themselves that are the problem, I see no other possible reason for a lock conflict. Set
[firebird-support] RE: Case insensitive search on a memo field
Hi, I'm using Firebird 2.5.3, and I am looking for text in a memo field, eg. I take it you mean BLOB or VARCHAR, I've never heard of memo fields in Firebird? Select * from mytable t where t.mymemo like '%find me%' The will only find find me and not FIND ME or Find me or Find Me etc I'm tempted to do something like Select * from mytable t where lowercase(t.mymemo) like '%find me%' but since this memo field could be enormous, I'm guessing that wold be horribly inefficient. Is there a better way? I think SELECT * FROM MYTABLE WHERE MYMEMO CONTAINING 'FIND ME' is case insensitive. One, probably better, alternative would be to use a case insensitive collation, but I doubt collations can be used in blobs (it should work in a varchar). And you're right, it will be slow since no indexes can be used (hopefully, we're not talking about more than a few million rows in this table?)... HTH, Set
RES: [firebird-support] Case insensitive search on a memo field
You can create a computed index for this: CREATE INDEX idxname ON mytable COMPUTED BY (lowercase(mymemo)) Hope this help you. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: sexta-feira, 5 de setembro de 2014 08:51 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] Case insensitive search on a memo field Hi, I'm using Firebird 2.5.3, and I am looking for text in a emo field, eg. Select * from mytable t where t.mymemo like '%find me%' The will only find find me and not FIND ME or Find me or Find Me etc I'm tempted to do something like Select * from mytable t where lowercase(t.mymemo) like '%find me%' but since this memo field could be enormous, I'm guessing that wold be horribly inefficient. Is there a better way? Thanks Maya
RE: [firebird-support] Case insensitive search on a memo field
Select * from mytable t where t.mymemo like '%find me%' You can create a computed index for this: CREATE INDEX idxname ON mytable COMPUTED BY (lowercase(mymemo)) Such an index does not help for LIKE '%...', it can only be used if the first character is a real character (i.e. not % or _). Moreover, the index will not be used if you use a parameter rather than constant with LIKE. Set
[firebird-support] RE: Case insensitive search on a memo field
Thank you for all the suggestions hopefully, we're not talking about more than a few million rows in this table?... I'd say definitely not more than a million, probably around 15000 for the products tables, 5000 for the customer tables, and 100 000 for the jobs and orders tables. But, each memo could be 5 pages long (or more, theoretically). So, while the insensitive search is quite quick, even though it doesn't use an index, if I have to convert each and every line of all the memos to lower or upper case, it could make the existing search slow. PS. I'm using LIKE 'find me%' or like '%find me%' inside a stored procedure, depending on whether the end user ticks starting with or containing text in my software, so it's not easy to use containing and changing the middle of the SQL, hence I just use LIKE, even though it can't use an index. -Maya
Re: [firebird-support] Case insensitive search on a memo field
I’m using Firebird 2.5.3, and I am looking for text in a emo field, eg. I’m tempted to do something like Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’ but since this memo field could be enormous, I’m guessing that wold be horribly inefficient. I use upper() for that and it's surprisingly fast (lowercase shouldn't be different). What you can do is try to use a case insensitive collation like unicode_ci or unicode_ci_ai, but I don't know if that really speeds things up. Regards Stefan ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Case insensitive search on a memo field
On Fri, Sep 5, 2014 at 8:43 AM, Stefan Heymann li...@stefanheymann.de [firebird-support] firebird-support@yahoogroups.com wrote: I’m using Firebird 2.5.3, and I am looking for text in a emo field, eg. I’m tempted to do something like Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’ but since this memo field could be enormous, I’m guessing that wold be horribly inefficient. I use upper() for that and it's surprisingly fast (lowercase shouldn't be different). What you can do is try to use a case insensitive collation like unicode_ci or unicode_ci_ai, but I don't know if that really speeds things up. As Set said before, no index is going to help unless the text string is at the very beginning of the field. CONTAINING is the best bet - it's case insensitive, but doesn't take wild cards, so if you're looking for %Find%me%, you'll need to use LIKE or SIMILAR and upcase the incoming value. However, you;re going to read every byte of every instance of that blob (memo) field - well, every byte of every instance that does not contain your value, and every byte up to the value if it does exist. What's needed is a full-text index, which Firebird doesn't offer. Lucene and others produce full-text indexes that can be used with Firebird (I think, haven't tried it). Good luck, Ann
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
I'm not sure it is locked table. What I'm looking for is idea or advice how to find what the real problem is. My data source definition is this: connection-factories tx-connection-factory jndi-namePowerProDS/jndi-name xa-transaction/ track-connection-by-tx/ rar-namejaybird-2.1.6.rar/rar-name connection-definitionjavax.sql.DataSource/connection-definition config-property name=Database type=java.lang.Stringlocalhost/3050:powerpro/config-property user-namesysdba/user-name passwordmasterkey/password config-property name=EncodingUNICODE_FSS/config-property prepared-statement-cache-size100/prepared-statement-cache-size min-pool-size10/min-pool-size max-pool-size50/max-pool-size metadata type-mappingEuroPro/type-mapping /metadata /tx-connection-factory /connection-factories
[firebird-support] Re: Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
CASHBOXID is already know. It is a reference to a table that we don't touch in the transaction. This table contains the current total amount of a cash box at the begging of the day. So when I try to load data for a cash box a java code checks is there record for this cash box for current day. If there is no such record it calculates the total current amount and calls the insert that adds a record to this table. Usually inserting record with same value for primary key will create error and probably will not block the execution of the statement. User is sysdba. There is no security restrictions for access to database. End users have no access to database server at all. They all use code that runs in application server to access or modify data. That is why I'm not restricting the access to the database for the moment. I'm not sure it is a lock conflict. But I suspect that something is locked somewhere and that forces the execution of the statement to stop and to lock all other inserts that other connections try to execute. As I said I need ideas or hints what to do in order to find the real problem when this situation happens again. One more thing. When I try to stop application server when this happens all connections that are blocked including the initial one stop the shutdown of the server. When I try to kill firebird process that initially blocked the execution all other processes are unlocked and server is shutdown after that. But in this case I try guess which process is that. When I look in the monitoring tables I can't see the pid of the process of each statement.
Re: [firebird-support] Case insensitive search on a memo field
On 05/09/2014 17:18, Ann Harrison aharri...@ibphoenix.com [firebird-support] wrote: What's needed is a full-text index, which Firebird doesn't offer. Lucene and others produce full-text indexes that can be used with Firebird (I think, haven't tried it). Apparently the Sphinx full-text search was adapted to Firebird in 2008 http://www.firebirdsql.org/en/other-downloads/ ... but perhaps it hasn't been maintained?!?
[firebird-support] .net version multiple variables
I have a report which has multiple datasets. And I make use of a variable from on of the datasets into another. Dataset customers: Select id, name from customers Dataset customer_sales (with master dataset = customers): Select sale_id from sales where customer_id =:id With the .net version of reportman, I get an error message column unknown id If I run the same report from Delphi application the report works ok. Im using this reportmannet_3_0_pre1.zip version any help would be highly appreciated
